Immutable clinical records and the privilege matrix

Status: accepted · Date: 2026-10-31

Enforcement: packages/db/src/wundklar-immutability.test.ts, wundklar-isolation.test.ts, sync-trigger.test.ts, rls-catalog.test.ts, scripts/verify-rls.mjs, scripts/check-system-call-sites.mjs.

Wundklar is a clinical record under German documentation law: once an event (an assessment, a visit, a photo, a plan decision) is written, it must be preserved verbatim. Corrections appear as new versions of the same chain, never as in-place edits. ADR-0009 made the Organization boundary the database's problem; this ADR extends the same discipline to what, within an Organization, a tenant handle may change.

Decision

Immutability is a privilege, not a convention. The platform tables of ticket 05 and the clinical tables of tickets 09/10 are split into three groups:

Row-level policies narrow which rows a grant applies to; the grant itself (GRANT UPDATE (col1, col2) ON …) is the hard stop. Deleting the UPDATE grant from a table is enough to make it insert-only, even if an older policy survives.

Correction = new version, not UPDATE. Clinical events (assessment, measurement) carry supersedes_id and correction_reason; a correction writes a new row referring to the previous one, with its own recorded_by/recorded_at and its own trail event. The old row stays as evidence. There is no code path and no privilege anywhere that can rewrite the old row.

Same-Organization at the database level. Every clinical parent carries UNIQUE (organization_id, id) and every child references (organization_id, parent_id) through a composite foreign key. Row-level policies narrow visibility; the composite constraint narrows existence: a child pointing at a parent in a different Organization is refused by Postgres even for the superuser, so a bug in a system path cannot leak a cross-org reference.

Serialisation is advisory, not pessimistic. Insert-only tables cannot be locked with SELECT … FOR UPDATE (it needs UPDATE privilege and an UPDATE policy). Where ordering matters — ID blocks per Organization, trail sealing per Organization, link counters per link, plan versions per wound — the server takes pg_advisory_xact_lock(hashtextextended('wk:<scope>:<id>', 0)), the pattern uploads.service.ts already uses. The lock is held for the transaction; readers never block.

The sync change log is written by the database, not the handler. app.sync_log() is a SECURITY DEFINER trigger function with a pinned search_path; tickets 09/10 attach it to each syncable table via CREATE TRIGGER … EXECUTE FUNCTION app.sync_log('<entity-type>'). It writes (organization_id, op, entity_type, entity_id, patient_id, rev, txid) with txid defaulting to pg_current_xact_id(). The handler cannot forget to log a change, cannot log a wrong op, and cannot write sync_change directly (no grant). The pull query uses the pattern where (txid, id) > cursor and txid < pg_snapshot_xmin(pg_current_snapshot()) so a transaction that commits late is never skipped (plan §1.7).

sync_mutation is tenant-INSERT-only. The ledger carries the handler's dispatch outcome under the primary key (organization_id, mutation_id). The tenant policy on INSERT requires received_by = app.user_id(), so the row that proves a mutation was received is written by the receiving User's own session. No tenant UPDATE or DELETE policy exists; rls-baseline.mjs does not grant them.

Overwritten ciphertext stays in the trail. The trail drops *Ct / *_ct fields from before/after. When a concurrent patient.update or patient.contact field-merge overwrites a ciphertext field, the sync dispatcher records the previous value under previousCiphertext in the same hash-chained payload; trail.service refuses any value that is not wk1.<keyRef>.<iv>.<ct> ciphertext. The value stays as readable as its key: erasing it means retiring that key.

System reasons are named. Every path that writes a system-only table names a SystemReason: wundklar.org-provision (writes care_service, org_key), wundklar.org-keys and wundklar.key-rotation (rotations), wundklar.team-admin (device_code, other users' device status), auth.assurance (session_assurance), wundklar.trail-seal (trail_link, trail_verification), wundklar.reports-server, wundklar.exports, wundklar.retention, share-links.public. scripts/check-system-call-sites.mjs prints the file / reason / line count for each.

Consequences

• Later tickets (09, 10) add their tables under one of the three groups without re-opening this question.
• A reviewer who does not understand a policy need only read the grant: "the column is not in rls-baseline" is the final answer.
• Correction flows carry the audit cost of a new version, which is what the regulation asks for.
• The sync pull query is one window-plus-xmin filter: the trigger lets the server offload "what changed" to the database so the handler does not maintain a shadow table.
• verify-rls and check-system-call-sites fail CI when a table slips into the wrong group, so the invariant is enforced on every commit, not just at review time.

Alternatives rejected

• Row-level immutability via policies alone. A WITH CHECK (false) UPDATE policy still needs an UPDATE grant; a future GRANT UPDATE would silently become legal. Removing the grant altogether is the stricter and simpler rule.
• Soft deletes with deleted_at. A deleted row is still the same row; the reader has to filter it out everywhere and a bug in the filter silently resurrects the row. Insert-only + new-version is the audit regulation's own model.
• Handler-written sync log. Three handler bugs ago, two of them would have failed to log; a trigger with a locked-down definer is the only path that cannot be forgotten.