Audit log is append-only (tamper-resistant)

GDPR Article 5(2) accountability requires that compliance records be trustworthy — a mutable audit log provides no assurance because any entry could have been silently modified after the fact. CWE-472 (External Control of Assumed-Immutable Web Parameter) and CWE-778 (Insufficient Logging) both apply when audit records can be retroactively altered. NIST AU-9 explicitly requires protection of audit information from unauthorized modification and deletion. An audit log that a rogue service account or a poorly-reviewed migration can UPDATE or DELETE is worthless as legal evidence — it cannot prove what actually happened, only what the log currently says.

Enforce immutability at the database level — application-layer checks alone are insufficient because any service with write access can bypass them:

-- Option 1: PostgreSQL RULE (blocks UPDATE and DELETE at the SQL parser level)
CREATE RULE audit_log_no_update AS ON UPDATE TO audit_log DO INSTEAD NOTHING;
CREATE RULE audit_log_no_delete AS ON DELETE TO audit_log DO INSTEAD NOTHING;

-- Option 2: Row Level Security (permits only INSERT for the application role)
ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY audit_log_insert_only ON audit_log
  FOR INSERT TO app_role WITH CHECK (true);
-- No SELECT/UPDATE/DELETE policies for app_role

After adding the constraint, search all migrations in supabase/migrations/ for any UPDATE audit_log or DELETE FROM audit_log statements and remove them. Treat any future PR that adds such a statement as a critical security review item.

• ID: compliance-consent-engine.compliance-audit-trail.append-only-log

• Severity: high

• What to look for: Check whether the application or any migration ever UPDATEs or DELETEs rows in the audit log table. An audit log that can be retroactively modified provides no compliance value — it cannot be used as legal evidence. Look for: UPDATE statements targeting the audit log table in application code or migrations, no database-level constraint preventing UPDATE/DELETE, or the audit log being stored in a regular table with no special access controls. Alternatively, check if the audit log is written to an immutable store (append-only Postgres table with row-level security preventing DELETE, an immutable S3 bucket, or a managed audit service like AWS CloudTrail).

• Pass criteria: No UPDATE or DELETE operations exist on the audit log table in application code or migrations. Either a database-level rule (PostgreSQL RULE, RLS policy, or trigger) prevents modification, or the log is written to an inherently immutable store. Count all SQL statements or ORM calls targeting the audit log and enumerate which are INSERT vs. UPDATE/DELETE — no more than 0 should be UPDATE or DELETE.

• Fail criteria: The application code or migrations include UPDATE/DELETE operations on the audit log table. Or the audit log has no protection against modification by any database user with write access. A soft-delete (setting deleted_at) on audit log rows does not count as pass.

• Skip (N/A) when: No audit log table exists (this will likely fail the previous check).

• Detail on fail: "audit_log table has no UPDATE/DELETE protection — any service-role query can modify or erase entries" or "Migration 014 soft-deletes audit_log rows: UPDATE audit_log SET deleted_at = NOW()"

• Remediation: Add database-level protection:

  -- Option 1: PostgreSQL RULE (prevents UPDATE/DELETE at SQL level)
  CREATE RULE audit_log_no_update AS ON UPDATE TO audit_log DO INSTEAD NOTHING;
  CREATE RULE audit_log_no_delete AS ON DELETE TO audit_log DO INSTEAD NOTHING;
  
  -- Option 2: Row Level Security (only allows INSERT for the application role)
  ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;
  CREATE POLICY audit_log_insert_only ON audit_log
    FOR INSERT TO app_role WITH CHECK (true);
  -- No SELECT/UPDATE/DELETE policies for app_role — only INSERT is permitted
  

  In application code, treat any attempt to modify audit log rows as a critical bug, not a feature.