Audit log and GDPR data governance

Summary — Every mutation in the system writes one row to audit_events. Admins can browse this trail at Admin → Audit, and run GDPR data-subject requests (export and erasure) for contacts and submissions at Admin → GDPR Data Requests.

Purpose — Understand what the audit log captures, how to read it, and how the two GDPR data-subject tools actually work: what they export, what they anonymize, and what remains immutable.

Audience — Admin role only. Both /admin/audit and /admin/gdpr sit under the admin route group, which requires admin role (requireRole("admin") in the admin layout).

Prerequisites — None. These are platform-level tools, not tied to a specific module.

Overview

Key concepts:

audit_events — One append-only table. Every mutation across every module writes a row here via logAudit(). This is project rule 10 ("Every mutation writes to audit_events table") enforced at the platform level.
Append-only by trigger — A Postgres BEFORE trigger (migration 031) blocks all UPDATE/DELETE on audit_events unconditionally, except two opt-in maintenance paths that redact only ip_address/metadata.
GDPR requests — gdpr_requests table logs every export/erasure attempt with a status (pending / processing / completed / failed).
Export (Article 15) — Gathers a subject's records into a JSON bundle; does not modify anything.
Erasure (Article 17) — Anonymizes PII in place across multiple tables; keeps row structure for analytics. Soft-delete style — records are never hard-deleted, only redacted.
Retention (Article 5(1)(e)) — A weekly Inngest cron anonymizes old IPs and purges very old audit rows.

Screenshot: Admin → Audit Log table showing Timestamp, Actor, Action, Entity, Entity ID, and Metadata columns with pagination.

The audit log (`/admin/audit`)

What gets logged

Each row in audit_events has: action (dotted string like feedback_report.created), entity (DB table name), entity_id, user_id, metadata (JSONB), ip_address, created_at. logAudit() (src/lib/platform/audit/log-audit.ts) is the single write path — it swallows and logs errors rather than throwing, so a failed audit write never blocks the underlying business mutation.

Viewing the log

Path: Admin → Audit Log (/admin/audit)

The page loads 50 events per page (PAGE_SIZE = 50) via listAuditEvents({ limit, offset }), newest first. Columns:

Column	Source	Notes
Timestamp	`created_at`	Locale-formatted, monospace
Actor	`user_id` resolved against the user list	Falls back to first 8 chars of the user ID, or "system" if `user_id` is null
Action	`action`, formatted by `formatAuditAction()`	`feedback_report.created` → "Feedback Report Created"
Entity	`entity`, formatted by `formatEntity()`	Maps table names to nouns, e.g. `opportunities` → "Opportunity"
Entity ID	`entity_id`	First 8 chars, monospace
Metadata	`metadata` JSON	Raw JSON string, truncated

All columns are client-sortable (click header). Pagination is Previous/Next links, no jump-to-page.

Filtering: listAuditEvents() accepts entity and userId filter options in the repository layer, but the current /admin/audit page does not expose filter inputs in the UI — only pagination and client-side column sort. To filter by entity or actor today, sort the visible page or query the database directly.

Per-entity history

Modules that show a "History" tab (for example, the pipeline opportunity detail page) pull from the same table via listAuditByEntity(entity, entityId) (src/lib/platform/audit/query.ts), which joins users for the actor's email and returns up to 200 events for that one entity — this is how per-record timelines are built without a separate history table per module.

Why the audit log cannot be edited or deleted

Migration 031_audit_immutability.sql attaches a BEFORE UPDATE and BEFORE DELETE trigger to audit_events that fires for every role, including the table owner — RLS is not the mechanism here because the app connects via DATABASE_URL as a role that bypasses RLS.

The trigger's rule:

Outside a maintenance transaction: any UPDATE or DELETE raises audit_events is append-only (% blocked outside the retention maintenance path).
Inside a maintenance transaction (SET LOCAL app.audit_maintenance = 'on'): DELETE is allowed (used by the retention purge), and UPDATE is allowed only if the changed columns are limited to ip_address and/or metadata — any attempt to touch id, action, entity, entity_id, user_id, or created_at raises audit_events is append-only: only ip_address/metadata redaction is permitted during maintenance.

This means the "what happened, to which entity, by whom, and when" skeleton can never be altered by any code path, including GDPR erasure — only the IP and metadata payload can be redacted.

GDPR data requests (`/admin/gdpr`)

Path: Admin → GDPR Data Requests (/admin/gdpr)

The page shows two action cards (Export, Erasure) plus a request history table backed by gdpr_requests.

Article 15 — Data export

Action: Export Data card → choose subject type (Contact or Submission) → paste the subject's UUID → Export Data.

This calls exportContactAction / exportSubmissionAction (both requireRole("admin")), which run exportContactData() / exportSubmissionData() in src/lib/modules/admin/gdpr.ts.

What a contact export contains (GdprExportData):

Field	Source
`subject`	The `contacts` row
`engagements`	`engagement_history` rows for the contact
`attendances`	`consultation_attendees` rows joined to consultation titles
`communications`	`campaign_replies` rows
`activities`	`contact_activities` rows
`auditTrail`	Up to 200 `audit_events` rows for this entity

A submission export contains only subject (the submissions row) and auditTrail — engagements/attendances/communications/activities are always empty arrays for submissions.

The result renders as a "View exported data" <details> block with the raw JSON, plus a Download JSON button that saves it as gdpr-export-{subjectType}-{first8ofid}.json.

Every export request is logged to gdpr_requests (status: completed, result: { recordCount }) and to audit_events as gdpr.data_exported.

Article 17 — Data erasure

Action: Erasure card (red border) → choose subject type → paste UUID → Erase Data → confirm in the dialog ("Permanently erase personal data?").

This calls eraseContactAction / eraseSubmissionAction (requireRole("admin")), which run eraseContactData() / eraseSubmissionData().

Contact erasure anonymizes, in one pass:

Table	Fields redacted
`contacts`	`first_name` → `[Redacted]`, `last_name` → empty, `email` → `redacted@anonymized.invalid`, `phone`/`title`/`role_in_org` → null, `metadata` → `{}`, `is_archived` → true
`engagement_history`	`description` → `[Redacted per GDPR erasure request]`, `metadata` → `{}`
`consultation_attendees`	`name`, `email` redacted, `metadata` → `{}`
`campaign_replies`	`from_address`, `subject` redacted, `body_preview`/`full_body` → null
`contact_activities`	`subject` redacted, `body_preview` → null, `metadata` → `{}`
`campaign_recipients`	`address` → redacted email
`campaign_responses`	`ip_address`, `user_agent` → null
`audit_events` (this entity only)	`ip_address` → null, `metadata` → `{}` — via the maintenance-flag transaction described above

Submission erasure redacts submitter_name, submitter_email, submitter_phone → null, submitter_org → null, description → [Redacted per GDPR erasure request] on the one submissions row.

Both return { anonymized: true, affectedRows: <count> }, shown as a toast: Erased: {n} records anonymized. Every erasure is logged as gdpr.data_erased with the affected row count in metadata.

This action is irreversible — the UI copy states it plainly: "Anonymizes all personal data for the subject. This action is irreversible. The record structure is preserved for analytics but all PII is replaced with [Redacted]."

Request history table

Columns: Date, Type (Export in blue / Erasure in red), Subject (type + first 8 chars of ID), Email, Status (StatusBadge), Result (raw JSON). All columns are client-sortable. Statuses come straight from gdpr_requests.status: pending, processing, completed, failed.

Retention policy (Article 5(1)(e))

Shown as an info box on the GDPR page and implemented as a weekly Inngest cron (gdprRetention, TZ=Europe/Berlin 0 2 * * 0 — Sunday 02:00 CET) in src/lib/platform/workflows/functions/gdpr-retention.ts:

Anonymize IP addresses on audit_events older than 90 days (ip_address = NULL), inside a maintenance transaction.
Purge audit events older than 730 days (2 years) — full row DELETE, also inside a maintenance transaction.
Purge daily snapshots older than 365 days from daily_snapshots (wrapped in a try/catch since the table may not exist in every environment).

The GDPR page displays this as static copy: IP addresses anonymized after 90 days, audit events purged after 2 years, daily snapshots purged after 1 year, cron runs weekly Sunday 02:00 CET.

Roles and permissions

Role	View Audit Log	View GDPR page	Export data	Erase data
admin	✓	✓	✓	✓
lead / reviewer / staff / lgu / public	✗	✗	✗	✗

Both /admin/audit and /admin/gdpr live under the (authenticated)/admin route group, which calls requireRole("admin") in its layout — there is no lower-role path into either tool.

Best practices

Never treat audit_events as a debugging log to clean up. It cannot be edited or deleted except through the two GDPR maintenance paths — this is by design (tamper-evidence), not a bug.
Use the entity/user filters at the database level when investigating, since the current /admin/audit UI only paginates and sorts — it does not expose entity or actor filter inputs.
Run exports before erasures when responding to a data-subject request that asks for both access and deletion — erasure is irreversible and the export is your only record of what existed.
Double-check the subject UUID before confirming an erasure. There is no preview of what will be redacted before you click Erase Data — only the confirmation dialog's generic warning.

Warnings

Erasure is irreversible. There is no "un-erase" — anonymized fields ([Redacted], redacted@anonymized.invalid) are overwritten permanently.

Soft-delete only, everywhere. Erasure sets is_archived = true on the contact but does not remove the row — this matches project rule 16 (soft deletes only, never hard delete). The row remains for referential integrity and analytics; only PII fields are blanked.

audit_events cannot be manually redacted through any UI. The only two code paths that can touch it are the GDPR erasure action (per-entity) and the weekly retention cron (age-based). Any other attempt to update or delete a row raises a Postgres exception.

Troubleshooting

"audit_events is append-only (UPDATE blocked outside the retention maintenance path)"

Symptom: A database-level error when attempting to modify an audit row directly (e.g., via a manual SQL script or migration).

Cause: The immutability trigger from migration 031 blocks all writes outside the two maintenance paths.

Fix: Do not attempt direct writes to audit_events. If PII needs to be removed for a specific subject, use the GDPR Erasure action at /admin/gdpr, which sets the maintenance flag correctly inside a single transaction.

"Database unavailable" on export or erasure

Symptom: The action throws immediately with this message.

Cause: getSql() returned null — the database connection could not be established when the GDPR request record was being created.

Fix: Check database connectivity / environment configuration. Retry once connectivity is confirmed.

GDPR request shows status `failed`

Symptom: A row in the request history table shows failed with a notes value containing an error string.

Cause: The export or erasure threw partway through — the gdpr_requests row is updated to failed with notes = String(error) in the catch block.

Fix: Read the notes column (visible via Result column tooltip or a direct query) for the underlying error, resolve it (e.g., invalid UUID, missing tenant context), and retry the action with the same subject ID.

FAQ

Can I search the audit log by user or entity from the UI? Not currently — the /admin/audit page only paginates (50 per page) and lets you sort by column. The underlying listAuditEvents() function supports entity and userId filters, but no filter controls are wired into the page.

Does erasure delete the contact or submission row? No. It anonymizes PII fields in place and, for contacts, sets is_archived = true. The row structure remains for analytics and referential integrity — this is consistent with the platform's soft-delete-only rule.

What happens to a contact's audit history after erasure? Only ip_address and metadata on that entity's audit_events rows are cleared. The action, entity, entity_id, user_id, and created_at fields are untouched — the fact that an action occurred remains provable, but any PII that was in the metadata is gone.

Who can run a GDPR export or erasure? Admin role only. Both actions call requireRole("admin") before doing anything.

Is there a way to view what will be redacted before confirming an erasure? No preview exists in the current UI — the confirmation dialog shows only generic warning copy, not a field-level preview. Run an export first if you need a record of the pre-erasure state.

Roles and access — Who can do what across the platform.
A dedicated guide to the feedback/bug intake system covering incident escalation is planned: Feedback and friction intake.
A dedicated guide to the ops-control plane (change requests, incidents, feature flags) is planned: Ops control and shift readiness.