# Immutable clinical records and the privilege matrix

The repository's architectural decision, rationale and consequences.

# 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](0009-row-level-security.md) 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 [#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:

| group                           | tables                                                                                                                                                                                                                                                       | tenant privileges                                                                    |
| ------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------ |
| mutable (plan §1.2.1)           | `care_service`, `site`, `device` (self), `patient`, `patient_contact`, `wound`, `visit_schedule`, `reminder`, `supply_item`, `prescription`, `share_link`, `object_stage`                                                                                    | `SELECT`, `INSERT`, column-level `UPDATE` on the named columns of `rls-baseline.mjs` |
| insert-only clinical / platform | `org_policy`, `avv_acceptance`, `trail_event`, `sync_mutation`, `id_block` (own active device), `object_stage` writes, `consent`, `wound.classify`, `measurement`, `assessment`, `review`, `plan_decision`, `visit`, `report`, `delivery`, `conflict_record` | `SELECT`, `INSERT` only; no column-level `UPDATE` grant exists                       |
| system only                     | `org_key`, `device_code`, `device_scope`, `session_assurance`, `sync_change`, `trail_link`, `trail_verification`, `kim_outbox`, `report_job`, `export_job`, `delivery_event`, `link_access`                                                                  | `SELECT` only; writes come from the connection role under a named `SystemReason`     |

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](../../packages/api/src/features/uploads/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 [#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 [#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.