Skip to content

dev-documentation.md

Latest commit

 

History

History
610 lines (455 loc) · 35.1 KB

dev-documentation.md

File metadata and controls

610 lines (455 loc) · 35.1 KB
Raw

0. Developer Documentation

This file serves the developer(s) and coding AI. It's goals are:

  1. ensure consistent coding
  2. facilitate introducing new devs / AI's

1. Styling

Current situation

Primarily css modules are used. Dynamic styling is usually implemented via inline styles.

For some general styles a global .css file is used.

Good alternative?

Use css directly, without modules, as mentioned here: https://medium.com/@rapPayne/stop-writing-react-local-styles-use-the-component-classname-pattern-9a820c6447de

  1. create a .css file with the same name as the (single) component file
  2. give the base class the same name as the component (important: prevent name conflicts?)
  3. give the base div of the component the base class
  4. use css nesting to style all the component's elements in the component's .css file
  5. add dynamic styling by dynamically changing classes (there may be cases where inline does not work?)

Is this better than using css modules? Migrating would be a chore, so let's not do it now.

One upside is: inside css modules nesting is not possible for classes.

2. SQL Source Of Truth And Sync

Why This Exists

This project uses SQL files in multiple locations:

  • backend/db/init/
  • backend-dev/db/init/
  • src/sql/
  • backend/db/ and backend-dev/db/ for shared generator/test SQL assets

To avoid manual drift, backend/db/init/ is the single source of truth.

Source Of Truth

Edit SQL files only in:

  • backend/db/init/
  • backend/db/ for these shared files:
    • generate_apflora_seed_sql.mjs
    • generate_qcs_sql.mjs
    • test_history_tables.sql
    • test_history_tables_smoke.sql
    • test_history_tables_full_coverage.sql

Do not manually edit mirrored copies in backend-dev/db/init/ or src/sql/. Do not manually edit mirrored copies in backend-dev/db/ for the shared files listed above.

Sync Commands

Sync files

npm run sync-sql

This does:

  1. Mirrors all files from backend/db/init/ to backend-dev/db/init/.
  2. Mirrors selected shared files from backend/db/ to backend-dev/db/:
    • generate_apflora_seed_sql.mjs
    • generate_qcs_sql.mjs
    • test_history_tables.sql
    • test_history_tables_smoke.sql
    • test_history_tables_full_coverage.sql
  3. Copies selected files into src/sql/:
    • 01_immutableDate.sql -> immutableDate.sql
    • 04_createTables.sql -> createTables.sql
    • 07_triggers.sql -> triggers.sql
    • 08_syncIgnoreDuplicateInsertTriggers.sql -> syncIgnoreDuplicateInsertTriggers.sql

Check for drift

npm run sync-sql:check
  • Exit code 0: everything is in sync.
  • Exit code 1: one or more mirrored files are out of sync.

Pre-Commit Hook

A git pre-commit hook is configured to run:

npm run sync-sql:check

If files are out of sync, the commit is blocked.

One-time setup after clone

npm install
npm run hooks:install

hooks:install sets:

  • git config core.hooksPath .githooks

Typical Workflow

  1. Edit SQL in backend/db/init/.
  2. If editing Apflora taxonomy CSVs in seed-data/apflora/, regenerate the seed file:
node backend/db/generate_apflora_seed_sql.mjs

This rewrites:

  • backend/db/init/11a_seedApfloraTaxonomies.sql
  1. Run npm run sync-sql.
  2. Commit changes.

If commit fails on SQL sync check:

  1. Run npm run sync-sql.
  2. Re-stage updated files.
  3. Commit again.

Generated Seed Files

  • backend/db/init/09_seedQcs.sql Regenerate with: node backend/db/generate_qcs_sql.mjs
  • backend/db/init/11a_seedApfloraTaxonomies.sql Regenerate with: node backend/db/generate_apflora_seed_sql.mjs
  • backend/db/init/11b_seedApfloraExampleData.sql Regenerate with: npm run apflora:generate

Apflora Example Data Import

Goal: real apflora.ch example data in the dev backend, to explore editing, presentation and analysis and to find missing features compared to apf2.

Imported per repeatable command (source: local pg_dump of the apf2 backend-dev database, default ../apf2/backend-dev/db/apflora.backup, override with APF2_DUMP):

  • project apflora, owned by the demo account (as with the demo project)
  • subprojects: Abies alba, Aldrovanda vesiculosa, Pulsatilla vulgaris
  • place levels: 1 = Populationen (apf2: pop), 2 = Teil-Populationen (apf2: tpop)
  • checks on level 2 (apf2: tpopkontr, without Freiwilligen-Kontrollen; Ausgangszustand included), with counts as check_taxa (apf2: tpopkontrzaehl)
  • actions on level 2 (apf2: tpopmassn; apf2's inline count columns like anz_pflanzen are kept as plain fields, not mapped to action_taxa)
  • historizations (apf2: ap_history, pop_history, tpop_history — one snapshot per (year, row), usually for the years in which a report was written) as subprojects_history/places_history versions (see below)
  • yearly place reports (apf2: popber/tpopber → check_reports, tpopmassnber/popmassnber → action_reports) — the source of the report's B and C tables
  • not imported: Beobachtungen, Berichte (ap-level), Freiwilligen-Kontrollen

Pipeline (run from the repo root):

npm run apflora:extract    # apf2 dump -> seed-data/apflora/apf2-example.json
npm run apflora:generate   # JSON -> backend/db/init/11b_seedApfloraExampleData.sql
npm run sync-sql
cd backend-dev && docker compose down -v && docker compose up -d --build

The extracted JSON and the generated SQL file are local-only (gitignored): they contain unpublished apf2 data, and the apf2 repo/dump is expected to exist whenever the import is (re)run. All generated ids are deterministic (md5-derived, uuidv7-shaped), making the seed idempotent (ON CONFLICT DO NOTHING); row-count asserts at the end of the file fail init loudly if anything is missing. Clones without these files simply seed without the apflora example project.

The file must run after 11a_seedApfloraTaxonomies.sql (it links into the seeded DB-TAXREF (2017) taxa) and before 12_writePermissionTriggers.sql (which rejects non-JWT writes). Note: the docker entrypoint sorts init files with en_US.utf8 collation, where underscores are ignored in comparisons — that is why the files are numbered 11a/11b rather than sharing an 11_ prefix.

Known Gaps Found While Importing (ps vs apf2)

Potential missing features, surfaced by mapping apf2 data into ps:

  1. Counting method lost: apf2 tpopkontrzaehl.methode (geschätzt/gezählt) has no counterpart on check_taxa / check_quantities.
  2. Subset counts double-count: apf2 einheiten like "davon blühende Pflanzen" are subsets of "Pflanzen total". ps units are flat — summing check_taxa per check mixes totals with subsets.
  3. No person directory: apf2 links adresse records (Bearbeiter, EK-Kontrolleur). ps fields are free text — no consistency or autocomplete.
  4. No control planning: apf2 ekfrequenz (interval in years + start year + abweichend) drives "fällige Kontrollen". ps has no equivalent; only the start year/flag were imported as fields.
  5. field_sorts has no level column, but fields for places exist per level — one sort order per table is shared across levels.
  6. apf2 keeps jahr and datum separately; ps checks/actions only have date (rows with only a year were seeded with January 1st of that year).
  7. Massnahmen counts are inline in apf2 (anz_pflanzen, anz_triebe, zieleinheit_* as columns) while ps offers action_quantities/action_taxa — the import keeps them as plain fields because the unit semantics ("Pflanzen total" vs. planted plants) don't map 1:1.

Reports For Past Years (Historization)

Reports are per year (subproject_reports.year). Rows that carry their own date — checks, actions, goals — are simply filtered by the report year. Rows without a date — subprojects and places (status, apber_relevant, start_year) — may have changed since, so their state for a past report year is read from historization.

How the state of a year is calculated

  • Historizations are dated Dec 31, 12:00 UTC of the year they describe (sys_period lower bound). Each version runs until the next historization of the same row; the last one ends at Jan 1 of the seed's generating year, where the live rows' trigger-maintained sys_period takes over.
  • A report for year Y reads, per row, the last version whose sys_period contains the end of Y (Dec 31 12:00 UTC) — i.e. the last historization before the end of the report year. Rows without a valid version (not yet existing, or deleted back then) drop out of that year.
  • Consequently only years with historizations exist as report years — plus the current one, which the live rows cover.
  • Nothing is synthesized: the import only maps the historizations apf2 actually contains.

Where it is implemented

  • Import: extract_apflora_example.mjs pulls ap/pop/tpop_history into apf2-example.json (key histories per row); generate_apflora_example_sql.mjs maps them to places_history/subprojects_history rows, creating the yearly partitions it needs (the history tables are partitioned by updated_at) and setting REPLICA IDENTITY FULL on all partitions — the seed's idempotent DELETEs require it under the Electric publication, because history tables have no primary key.

  • Runtime: history tables are server-only (not synced to clients). Reports query them directly via PostgREST — src/components/shared/reportVersions.ts fetches live + history versions of the art's subprojects/places (online only) and caches them with react-query (staleTime: Infinity — history is immutable, so repeatedly opened reports are served from the cache). asOfYear() picks the valid version per row.

  • Consumers: the data-driven report tables (reportComponents.tsx) are 1:1 ports of apf2's jber_abc SQL (apf2/sql/apflora/functions/jber_abc.sql), so the printed numbers equal apf2's AP-Bericht: A (Grundmengen) counts pop/tpop statuses from the as-of rows (tpops under non-potential pops, bekannt_seit vs the AP start year decides vor/nach AP, places without bekannt_seit never count); B (Bestandesentwicklung) counts the yearly check_reports; C (Zwischenbilanz) counts actions plus the latest action_reports beurteilung per place. The place-based chart series (buildData) compute from the as-of rows; dated series (checks/actions) stay on the local, year-filtered data. Like apf2's chart functions (tpop_kontrolliert_for_jber, pop_nach_status_for_jber, ap_ausw_pop_menge), report charts only show the historized years up to the report year — plus the current year when it is the report year. The place series follow tpop_kontrolliert_for_jber exactly: per year the historization snapshot without potential places (status 300) and without bekannt_seit, tpops only under a qualifying pop and if report-relevant, and erloschen tpops (status 101/202) only in the first year of that status. The kontrolliert series counts check_reports (apf2: tpopber),

    The Populationen nach Status chart builds apf2's six A-table series (ursprünglich / angesiedelt vor/nach Beginn AP / erloschen vor/nach / Ansaatversuch, with apf2's colors) per historization year, using the start year of the subproject's version of that year — pop_nach_status_for_jber ported 1:1. A ProgrammInfo building block shows Start Programm (the subproject's start year), Erste Massnahme and Erste Kontrolle (the earliest action and check years), like apf2's report header. The Triebe total chart ports ap_ausw_pop_menge: per historization year, every qualifying tpop carries its latest zaehlung of the zielrelevant unit up to that year (lookback), plus the year's anpflanzung planting when it had no zaehlung; series per population, ursprünglich ones translucent green, angesiedelt orange. not checks. While offline or before the versions arrive, the current local state is used as fallback.

  • Demo: the 2020 Aldrovanda report (seeded in 11d_seedApfloraReport.sql) is calculated from the 2020 historizations; compare with the 2025 report to see the difference.

Files Involved

  • Sync script: scripts/sync-sql.mjs
  • Hook installer: scripts/install-git-hooks.mjs
  • Hook file: .githooks/pre-commit
  • NPM scripts: package.json

3. Login

Email and Password

Register

Log in

Change password

Forgot Password

OAuth, Social sign-on (SSO)

Add password after registering with OAuth

4. App Admin Access

App-admin access is configured via frontend env variable:

  • VITE_APP_ADMIN_EMAILS

Format:

  • Comma-separated email list
  • Example: VITE_APP_ADMIN_EMAILS=alex@gabriel-software.ch,alex.barbalex@gmail.com

Behavior:

  • Emails are trimmed and compared case-insensitively.
  • If the variable is empty or missing, nobody is treated as app admin.
  • Do not hardcode admin emails in routes/components. Use src/modules/appAdmins.ts.

5. Authentication Flow

Registration

Users register with email, password, and an auto-derived display name (the local part of the email address). The name field is required by the backend and is set automatically — the frontend derives it from the email before calling signUp.email().

After successful registration the user is taken to the sign-in form and shown a success message advising them to check their email.

Email Verification Grace Period

New users can log in immediately after registration without verifying their email. They have 1 hour from account creation to complete verification before they are automatically signed out.

How it works

  1. Grace window — defined in src/modules/emailVerificationGrace.ts:

    • EMAIL_VERIFICATION_GRACE_MS = 60 * 60 * 1000 (1 hour)
    • getVerificationDeadlineMs(user) — returns user.createdAt + 1 hr or null if already verified or missing
    • isVerificationGraceExpired(user, nowMs?) — returns true when deadline has passed

  2. Route guard — src/routes/data/route.tsx beforeLoad checks isVerificationGraceExpired. If expired, it redirects to /auth?verificationExpired=true, which shows an error message on the auth page.

  3. In-app banner — src/components/EmailVerificationBanner.tsx, mounted in src/components/LayoutProtected/index.tsx:

    • Visible whenever the session user has emailVerified: false and is still within the grace window.
    • Shows a live countdown (HH:MM:SS) to forced logout.
    • Resend button — POSTs {email, type: 'email-verification'} to /auth/email-otp/send-verification-otp.
    • OTP input + Verify button — POSTs {email, otp} to /auth/email-otp/verify-email; reloads the page on success.
    • When the countdown reaches zero, signOut() is called automatically and the user is redirected to /auth?verificationExpired=true.

Relevant files

File Purpose
src/modules/emailVerificationGrace.ts Grace window helpers & expiry check
src/components/EmailVerificationBanner.tsx Countdown banner with resend + OTP verify
src/components/EmailVerificationBanner.module.css Banner styles
src/routes/data/route.tsx Route guard that enforces expiry
src/routes/_layout.auth.tsx Adds verificationExpired search param to auth route
src/components/Auth.tsx Shows grace-expired error and post-signup success message
backend-dev/auth/auth.mjs requireEmailVerification controlled by REQUIRE_EMAIL_VERIFICATION env var

Dev environment note

In backend-dev, email verification is disabled by default (REQUIRE_EMAIL_VERIFICATION env var defaults to false). This means new users can log in without any OTP step.

To test the full OTP flow locally when Mailgun is not configured, check the auth container logs — the OTP is printed there:

docker compose logs auth --tail 50

To enable verification in dev, set REQUIRE_EMAIL_VERIFICATION=true in the dev environment and restart the auth container.

Removing a User

A user can delete their own account from the user form (src/formsAndLists/user/index.tsx) in the Data section.

What happens

  1. A confirmation dialog (src/formsAndLists/user/DeleteAccountDialog.tsx) warns the user that deletion is permanent and irreversible, and hints that they can export their data first.
  2. On confirmation, a DELETE FROM users WHERE user_id = $1 is sent directly to the PostgREST API (constants.getPostgrestUri()). Referential integrity (cascade deletes) removes all related data server-side.
  3. After a successful delete, clearLocalSyncedData() is called to wipe local PGlite state, IndexedDB databases, browser caches and persisted localStorage.
  4. The browser is redirected to /.

Relevant files

File Purpose
src/formsAndLists/user/index.tsx "Delete account" button in the Data section
src/formsAndLists/user/DeleteAccountDialog.tsx Confirmation dialog; executes the delete + wipe
src/modules/clearLocalSyncedData.ts Clears all local sync state and caches

6 User Roles

The user-facing documentation of this model lives in docs/docsMd/user-roles_{de,en,fr,it}.md — it is the source of truth for the roles model. Keep it correct and translated in all four languages.

Rules

  1. User owns own user row, related accounts, projects and other data
  2. Roles are (from high to low): owner, designer, writer, reader
  3. Projects, subprojects and places have ..._users tables to set a user's role
  4. A users role always includes all the lower roles. They are not separately set, only a single role is set
  5. (Only) Owners can set designer roles
  6. (Only) Owners and designers can set writer and reader roles
  7. Only triggers set owner roles, users can't
  8. When a role is set, it's effect extends down all relations (n-sides) - even if (which should not happen) it has not been set in a ..._users table in between.
  9. Setting lower rights at a lower level is not expected. Example: When a user has reader role on project, all its data can be synced without checking lower levels
  10. Higher rights can be given at lower levels, their effect extending down as well. Example: A reader who shall be writer on a subproject needs the reader role on its project to sync in parent data
  11. Setting lower roles at higher levels after having set higher ones lower down will nuke higher roles at lower levels. That's a problem we will have to live with? Will have to inform users if this happens in projects/subprojects
  12. Owners are recognized by the 'owner' role given (the trigger that sets the owner roles uses above definition of what a user owns)
  13. Only the owner of an account (accounts.user_id) can create projects in that account. Enforced in enforce_projects_write (backend) and checkWritePermission (client)

Implementation

  1. Read above rules to understand why and how to implement

  2. We do not need sql to migrate existing implementations. After this rebuild we will rebuild the (experimental) production server from scratch

  3. account_id is currently part of many tables. In most cases it is redundant and should be removed. Keep it in: accounts and projects. Ensure there is no code left referencing it

  4. Roles: Change user_roles_enum to be ('reader', 'writer', 'designer', 'owner'). Add them in this order to make this the official, sortable and comparable order (https://www.postgresql.org/docs/current/datatype-enum.html#DATATYPE-ENUM-ORDERING)

  5. Ensure code using user_roles_enum is updated, i.e. /home/alex/Documents/GitHub/ps/src/modules/constants.ts.userRoleOptions, /home/alex/Documents/GitHub/ps/backend/db/init/10_seedGeneralTestData.sql (manager role no more needed as trigger will set owner), comments in createTables sql files, /home/alex/Documents/GitHub/ps/src/components/Tree/Project/Editing.tsx.userMayDesign, /home/alex/Documents/GitHub/ps/src/formsAndLists/project/DesigningButton.tsx.userMayDesign. Ensure the previous roles are no more used anywhere and replaced in a meaningful way

  6. Ensure a user can have only a single role in a ..._users table (combination of parent table id and user_id must be unique)

  7. Build on update, on insert and on delete of ..._users tables triggers to upsert or remove roles in lower (n-side) ..._users tables

  8. On insert triggers in projects set this users role to 'owner'

  9. On insert triggers in subprojects and places tables fetch and set this users roles from the parent (next up in the hierarchy) ..._users table

  10. Ensure triggers dont cascade recursively: use pg_trigger_depth() (https://www.postgresql.org/docs/9.2/functions-info.html) to only run on WHEN (pg_trigger_depth() < 1). See: https://stackoverflow.com/a/14262289/712005 and https://dba.stackexchange.com/a/163152/51861. Beware: this will not work in casee where the spreading trigger should react to a different trigger. Which is what we want: only run when a user (with the needed rights) changes rights. The trigger thus has to update ALL lower level ..._users tables

  11. Ensure these triggers do not run on sync (using current_setting('electric.syncing', true) as for instance in observation_imports_label_creation_trigger)

  12. Add subqueries (https://electric-sql.com/docs/guides/shapes#subqueries-experimental) to shape params in /home/alex/Documents/GitHub/ps/src/modules/startSyncing.ts to ensure only allowed rows are synced in (user has reader or higher role in the relevant parent table which is projects, subprojects or places set in the respective xxx_users table). Keep an eye on whether these subqueries are reasonable or if we need to create user-hidden xxx_users tables fed by triggers

  13. Alter app side write operations to respect roles and surface when writer or higher role is missing

  14. Alter postgrest API requests to send an authorization header that is checked on the server. Return meaningful messages if authorization fails. App-side roll back operation. Done: JWT Bearer token sent on all PostgREST writes; JWT errors invalidate the token cache and notify the user; permission-denied (42501) errors revert the optimistic change in PGlite, remove the queued operation, and show a notification. See src/modules/fetchPostgrestToken.ts, executeOperation.ts, observeOperations.ts.

  15. Alter postgrest API to ensure user may run this write operation according to the rules above. If not return a meaningful message which is surfaced in the ui and rolls back the operation that caused it Done: backend/db/init/12_writePermissionTriggers.sql adds BEFORE triggers (WHEN pg_trigger_depth() < 1) on all project/subproject/place-scoped tables. Each trigger reads the JWT user_id via get_jwt_user_id(), checks the role hierarchy via user_can_write_project / user_can_write_subproject / user_can_write_place, and raises a 42501 exception with a descriptive message + hint if access is denied. *_users tables require designer+ via user_can_manage_*_roles helpers. ElectricSQL sync is skipped via is_electric_sync(). The app-side 42501 handler in observeOperations.ts surfaces the hint text and reverts + removes the operation.

  16. Alter electric-sql endpoint to accept only authorized requests: https://electric-sql.com/docs/guides/auth#proxy-auth

    • Added GET /auth/electric/check to both auth servers: stateless HS256 JWT verify using PGRST_JWT_SECRET, no DB lookup
    • Dev Caddyfile: forward_auth @notOptions localhost:3003 { uri /auth/electric/check } on the localhost:3001 Electric proxy
    • Prod backend/caddy/Caddyfile: same forward_auth on both sync.xn--arten-frdern-bjb.app and sync.promote-species.app
    • startSyncing.ts: fetches the PostgREST JWT via fetchPostgrestToken() at startup and passes it as Authorization: Bearer header to every shape subscription via the Object.fromEntries shape map

  17. Critical for speed: Updates on role changes high up in the hierarchy: should happen batched

  18. Critical for speed: Sync subqueries

  19. Critical for speed: Write checks, especially when data is imported. Batch imports!

  20. Most critical for speed: Ensure that changing a role does not lead to re-syncing already synced rows other than ..._users or for users not involved. This rules out the array-column per role approach!

You can now log in at the dev backend as test@test.ch / test-test and see all the seeded data (email verification is disabled in dev; accounts older than the 1-hour grace period need email_verified = true in the db)

Target Model For The User-Roles Rebuild (agreed 2026-08-22)

The current model conflates two concepts in the global users table: login identity and project collaborators. The rebuild separates them. See also docs/docsMd/user-roles_{de,en,fr,it}.md — the user-facing doc must be rewritten after the rebuild.

Principles

  1. Login identity is global. A person logs in once; login emails are globally unique. The users table serves auth only (email, email_verified, name) and is never exposed in project UIs or synced to other users' devices beyond their own row.
  2. Collaborator identity is project-scoped. Each project keeps its own directory of people (project_users), keyed by email. Emails may overlap across projects; within a project an email identifies exactly one collaborator.
  3. People and roles are separate. project_users lists people; *_roles tables assign roles at three symmetric scopes (project, subproject, place level 1/2).
  4. Accounts own projects and finance the app. Only the account owner may create projects in it (rules 13, enforced in enforce_projects_write and checkWritePermission).
  5. Owner roles are set only by triggers (rule 7).
  6. The role enum stays: ('read-specific', 'read-all', 'write-specific', 'write-all', 'design', 'own'). One role per user per scope, explicit via UNIQUE constraint.

Schema

-- auth only (better-auth user table); NOT referenced by project data anymore
users (user_id, email UNIQUE, email_verified, name, ...)

-- billing unit; owns projects. email = billing contact
-- (may default to the owner's auth email)
accounts (account_id, user_id -> users, email, type, ...)

-- per-project directory of collaborators
project_users (
  project_user_id uuid PRIMARY KEY,
  project_id uuid NOT NULL REFERENCES projects,
  email text NOT NULL,            -- trimmed, lowercased on write
  auth_user_id uuid DEFAULT NULL REFERENCES users,  -- stamped on claim (see below)
  UNIQUE (project_id, email)
)

-- role assignments: same shape at every scope
project_roles    (project_role_id    PRIMARY KEY, project_id,    project_user_id, role, UNIQUE (project_user_id, project_id))
subproject_roles (subproject_role_id PRIMARY KEY, subproject_id, project_user_id, role, UNIQUE (project_user_id, subproject_id))
place_roles      (place_role_id      PRIMARY KEY, place_id,      project_user_id, role, UNIQUE (project_user_id, place_id))

-- all three role tables:
--   project_user_id REFERENCES project_users ON DELETE CASCADE
--   role user_roles_enum NOT NULL

Uuid primary keys everywhere — the optimistic-write/operation-queue/revert machinery keys on row ids; email is a join key, never a primary key.

No name column: collaborators are identified and displayed by email (labels are email-based today too); display names for logged-in users come from the auth users table. If ever needed, a nullable name can be added later (non-breaking).

Triggers

All guarded with WHEN (pg_trigger_depth() < 1) and skipped when electric.syncing (as today):

  1. projects insert owner trigger: insert a project_users row with the account owner's auth email (claim it immediately: set auth_user_id) plus a project_roles row with role own.
  2. Email normalization on project_users writes: trim(lower(email)), so every email comparison (login match, sync shapes) is exact.
  3. Role cascade (replaces items 7/9): setting a general role copies it into the lower *_roles tables; setting a -specific role removes lower rows; inserting a subproject/place copies roles from the parent scope. Symmetric because all role tables share the same shape.
  4. Label triggers: denormalize email (role) labels onto the *_roles rows from the directory, so list views don't join. Editing a person's email in the directory refreshes labels everywhere (replaces users_*_label_trigger).

Access resolution (login -> roles)

  1. On login (or app start), match the authenticated email against project_users.email (normalized). Recommended: stamp auth_user_id onto matched directory rows (claim), making the identity link a stored fact instead of a repeated string match. Claiming also survives later email mismatches until re-claimed.
  2. Sync shapes use the same resolution, e.g.:
    • project_users: WHERE project_id IN (SELECT ... matched for this auth user) — the auth user's visible projects
    • *_roles / project data: via project_user_id IN (SELECT project_user_id FROM project_users WHERE auth_user_id = $1) (or email match pre-claim)
  3. Server-side permission triggers (12_writePermissionTriggers.sql) and the client-side checkWritePermission.ts resolve email -> project_user_id -> role and check the role at the target scope or an ancestor. projects INSERT stays account-owner-only.

App-side consequences

  • User pickers inside a project list only that project's project_users (directory). "Add user" creates a directory row with just the email — no global users row is created for collaborators.
  • The three role forms (project/subproject/place) edit *_roles rows; operations queue/revert targets their PKs.
  • The users route/list shrinks to account/auth concerns (own user, own accounts).
  • Role options: all six roles remain visible (Owner renders for owner rows); only triggers can set own.

Concept mapping (old -> new)

current rebuild
users (global, exposed) users = auth only; project_users = per-project directory
project_users (user_id + role) project_users (directory) + project_roles
subproject_users subproject_roles
place_users place_roles
place_users.label (from users) label triggers off project_users
AddUserButton inserts into users AddUserButton inserts into project_users
login = users.user_id login -> claim (project_users.auth_user_id)

Open decisions

  1. Claim-on-login: stamp auth_user_id (recommended) vs pure email matching on every resolution. Decide handling of auth-email changes (re-claim flow).
  2. accounts.email: explicit billing-contact column (may differ from the owner's login email) vs derived. Recommended: explicit, defaulting to the owner's email.
  3. Pending-invite state: do directory rows need a state column (invited/active) before the person first logs in?

7 Documentation

Goals

  • we want the user to find documentation on the /docs route
  • we will also link to docs from many pages inside the app
  • a docs url should be: /docs/{doc title slugified to lowercase, spaces to -}
  • this route needs no login nor database thus it will always load very fast
  • for navigation we have a symbol-button in the app header (home: left of enter, data: left of online). It links to /docs
  • metadata for docs is stored in an array of objects in a metadata.ts file with these keys: 1. id: {doc title slugified to lowercase, spaces to -} 2. label (= title) 3. order 4. isTechnical
  • the menu list is on the left (similar to the nav tree in /data). It can be filtered by: all, contentual, technical (these three are mutually exclusive - only one can be choosen, default is all), text (label)
  • docs are rendered on the right
  • under 1000px view width ony the menu list is rendered (/docs) or the doc (/docs/doc-id)
  • give the 1000px some wiggle to prevent the ui from jumping back and forth near this width value
  • Doc sources live in two subfolders of docs/:
    • docs/docsMd/ — docs written in Markdown (converted to HTML by the build script)
    • docs/docsHtml/ — docs written directly in HTML (copied as-is by the build script)
  • A build script (scripts/build-docs.mjs, run via npm run docs:build) combines both source folders into docs/docs/ — do not edit files in docs/docs/ manually
  • The app imports the pre-built .html fragments — no runtime Markdown parsing, docs render instantly
  • Shared assets (metadata, CSS) live directly in docs/
  • there exists standard css to style docs similarly and simplify their creation. Things prestyled could be: ol, ul, p, h1, h2, h3. We can add to this later when we use it
  • docs will not be added in the ui but by devs in dev mode. Thus they need not editing functionality
  • TODO: links in docs should always open in a new tab
  • docs need to exist in de, en, fr and it. They will be written in either de or en. the writer creates the four files, adds the language to the file name. The metadata contains the label in four versions, fallback is label_de (later could be added to the docs:build script?)

Implementation

Step 2 — Build script and standard docs CSS

Create scripts/build-docs.mjs:

  • Clears docs/docs/.
  • Reads every docs/docsMd/*.md file, converts each to an HTML fragment using marked with a custom renderer that adds target="_blank" to every <a> tag, writes to docs/docs/{id}.html.
  • Copies every docs/docsHtml/*.html file into docs/docs/, post-processing each to add target="_blank" to every <a> tag that doesn't already have a target attribute.

Convention: when writing HTML docs in docs/docsHtml/, always add target="_blank" to links manually so the source is readable without running the script.

Add to package.json scripts:

"docs:build": "node scripts/build-docs.mjs"

Create docs/docs.css with pre-styled base elements:

.doc h1 { … }
.doc h2 { … }
.doc p  { … }
.doc ol, .doc ul { … }
/* etc. */

Use a .doc wrapper class so styles are scoped and don't bleed into the app shell.

Verify: running npm run docs:build with empty docs/docsMd/ and docs/docsHtml/ directories exits without errors and docs/docs/ is empty.

Step 10 — Additional docs

For each new doc:

  1. Write the source in docs/docsMd/{id}_de.md (Markdown) or docs/docsHtml/{id}_de.html (HTML)
  2. Add docs/docsHtml/{id}_en.html, docs/docsHtml/{id}_fr.html and docs/docsHtml/{id}_it.html
  3. Add an entry to docs/metadata.ts
  4. Run npm run docs:build
  5. Commit the source file, the generated docs/docs/{id}.html, and the updated docs/metadata.ts

Things to document