Audit trail — as built

The proposal in audit-trail-design.md was accepted with separate Postgres as the store (Firestore deferred), outbox + async forwarder delivery, and regex + known-subject PII detection. This is the implemented behaviour. Last updated 2026-09-02.

1. Flow

server action ─► zod ─► RBAC ─► db (audited client)
                                  │  $transaction: set_config('app.audit_ctx', {actor, tenant, requestId}, local)
                                  │  enrich: containsPii, piiSubjectIds, DataSubject registry
                                  ▼
                       Postgres trigger audit_capture (AFTER INSERT/UPDATE/DELETE, every business table)
                                  │  before/after row JSON, secrets masked, txid, ctx  → "AuditOutbox"
                                  ▼
                       forwarder (POST /api/internal/audit/forward, or on audit-page load)
                                  │  buildEvent(): scope, kind, action, actor, entity, per-field changes, PII flags
                                  ▼  HMAC-signed batch
                       audit service POST /v1/events ─► encrypt PII values per subject key ─► chain hash ─► AuditRecord

Actor resolution (who gets stamped)

The audited client resolves the actor at write time, in this order: an explicit withAuditContext() scope (jobs, route handlers, tests) → the actor stamped by getSession() → the verified session cookie of the current request (lib/audit/request-actor.ts, including the user's school role at the active tenant) → system. The cookie step exists because React's per-request cache is not shared between the auth layer and a server action's write phase; resolving from the cookie means no caller can forget the actor. requestId groups every row written in one transaction.

Integrity

Each AuditRecord carries prevHash and hash = sha256(prevHash + canonicalJson(record)). The hash is computed over the at-rest form (PII values already encrypted, object keys sorted — jsonb does not preserve key order), so GET /v1/verify needs no subject keys and still holds after a key is shredded. Chains are per tenant plus one platform chain; appends are serialised per chain with a row lock on ChainHead.

2. What is recorded

• Data events: data.<Model>.<create|update|delete> with changes[] (field, before, after, pii), entity (model, id, externalId), actor (type, id, role, schoolRole), request id, tenant, scope, retention class, subjects.
• Domain events via appendAudit(): gate denials, SSO refusals/logins/verification, staff/role changes, imports, anonymisation, chain verification.
• Not recorded: reads; updatedAt-only changes; no-op updates; secret hashes (masked to [secret]).

3. Two audiences

• School admin → School portal → Audit Log: scope = TENANT, own tenant only; filter by subject id, event prefix, dates; before/after visible.
• EduSpaze admin → Admin → Audit: all tenants + PLATFORM events; outbox health; ship now; per-chain verification.

4. PII & anonymisation

• Registry DataSubject (student, guardian, staff, contact) with match tokens; rows link to subjects through piiSubjectIds (GIN). Query "everything about X": WHERE '<subjectId>' = ANY("piiSubjectIds") on any flagged table, or GET /v1/events?subjectId= on the audit platform.
• Detection: structured PII fields (policy), related subject references (external ids / row refs), free-text patterns (email, phone, NRIC/FIN, passport, DOB) and known-subject name/id tokens (≥4 chars) within the tenant.
• Anonymise (School → Audit Log, typed confirmation): tokenises PII fields on the subject's row and every referencing row, replaces free text, clears tokens, marks anonymisedAt, emits data.subject_anonymised, ships the outbox, then shreds the subject's key on the audit platform. Audit records stay intact and verifiable; their PII values decrypt to [shredded].

5. Operations

• Scheduler: POST $APP/api/internal/audit/forward with header x-audit-key: $AUDIT_SERVICE_SECRET every minute (Cloud Scheduler in production). The audit pages also flush on load so demos never wait.
• Outbox alerting: watch AuditOutbox rows with sentAt IS NULL older than 10 minutes.
• Verify: Admin → Audit → Verify (per chain) or GET /v1/verify?chain=.
• Health: GET /health (also /healthz; on Cloud Run the Google front end answers /healthz itself with a 404, so probes should use /health).
• Migration of pre-existing AuditEvent rows: replay script pending (backlog BL-05).
• An unreachable audit platform never takes a page down. auditFetch turns a refused connection or a timeout into { ok: false, status: 0, error } — the same shape callers already handle for "not configured" — so the audit console still opens, says *Not answering* with the reason, and tells the operator that changes keep queueing in the outbox. The console is the page somebody opens when something is wrong; it must not be the second thing that is broken. Covered by tests/audit-harness.test.ts.

6. Configuration

Support sessions (BL-16)

A platform admin may open a school portal as that school's admin only while the school allows it (School.supportAccessEnabled / supportAccessUntil, set by a school admin under School Settings). The session is the school owner's login carrying support claims (admin id and name, school, start, expiry, reason), time-boxed to 60 minutes and never past the school's window. Attribution is fixed in one place (lib/audit/session-actor.ts) and used by both the auth layer and the audited client's request-time resolver, so it cannot diverge:

Domain events support.access_changed (school side), support.session_started and support.session_ended (with reason, admin and acted-as user) sit on the tenant chain. The audit log pages mark such rows *support session*. School admins are notified at start and end.

Canonical hashing (2026-09-04)

Both chains hash a canonical encoding of their payload: object keys sorted recursively, so the digest is the same on write and on read back. PostgreSQL jsonb does not preserve key order — it stores keys by length, then bytes — so hashing with a plain JSON.stringify gives a different digest once the row is read again, and any event whose payload had more than one key of differing length failed to verify. lib/audit.ts (the in-app ledger) and services/audit/src/crypto.ts (the audit platform) both canonicalise.

verifyAuditChain also accepts the pre-fix digest, so events written before the change still verify wherever their payload happens to round-trip unchanged (no payload, or a single key). An event written before the fix with a multi-key payload cannot be re-verified: the original key order is not recoverable from jsonb. Those rows exist only in demo data, which the seed rebuilds.

Rule for new domain events: never hash a value that has been through the database without canonicalising it first.