Create declaration_audit_log Supabase table with immutability constraints

epic-driver-and-confidentiality-management-foundation-task-009 — Create the declaration_audit_log table with columns for event_type (sent, opened, acknowledged, expired, revoked), declaration_id reference, actor_id, org_id, timestamp, and a JSON metadata column. Add RLS policies preventing any UPDATE or DELETE operations on audit rows. Insert-only access enforced at database level for tamper-evident compliance logging.

critical priority medium complexity database pending database specialist Tier 2

Acceptance Criteria

Table declaration_audit_log exists with columns: id (uuid PK default gen_random_uuid()), event_type (audit_event_type enum or text with CHECK), declaration_id (uuid FK → confidentiality_declarations NOT NULL), actor_id (uuid NOT NULL, references the user who triggered the event), org_id (uuid NOT NULL FK → organizations), occurred_at (timestamptz NOT NULL DEFAULT now()), metadata (jsonb NULL for additional event context)

event_type is constrained to exactly: 'sent', 'opened', 'acknowledged', 'expired', 'revoked' — no other values accepted

RLS is enabled; no UPDATE policy exists for any role — Supabase denies UPDATE by default

No DELETE policy exists for any role including org admins — rows are permanently immutable

A BEFORE UPDATE trigger raises an exception ('audit log rows are immutable') as a secondary enforcement layer beyond RLS

A BEFORE DELETE trigger raises an exception ('audit log rows cannot be deleted') as a secondary enforcement layer

SELECT policy allows authenticated users to read audit rows only within their own org_id

INSERT policy allows authenticated users to insert rows only with their own actor_id and within their org_id

Index on (declaration_id) for fast per-declaration audit trail lookup

Index on (org_id, occurred_at DESC) for org-scoped audit timeline queries

Migration is idempotent

Technical Requirements

frameworks

Supabase Migrations (SQL)

Postgres triggers

apis

Supabase Auth (auth.uid() for RLS actor_id enforcement)

data models

declaration_audit_log

confidentiality_declarations

organizations

performance requirements

Per-declaration audit trail query (all events for one declaration) must return in <100ms with up to 1,000 audit rows

Bulk audit queries by org and date range must use the (org_id, occurred_at) index

security requirements

Immutability must be enforced at both RLS layer (no UPDATE/DELETE policy) and trigger layer (belt-and-suspenders) — neither alone is sufficient

actor_id must be set from auth.uid() in the RLS INSERT policy — client must not be able to supply a different actor_id

metadata JSONB column must never contain unencrypted PII — only event context like template version or IP hash

org_id must be validated against the authenticated user's org in the INSERT RLS policy to prevent cross-org log injection

Execution Context

Execution Tier

Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

View Full Execution Plan

Implementation Notes

Use a Postgres enum type (CREATE TYPE audit_event_type AS ENUM ('sent','opened','acknowledged','expired','revoked')) rather than a CHECK constraint for event_type — enums are more performant and self-documenting. The dual enforcement (RLS + trigger) is deliberate: RLS can be bypassed by service_role, but triggers cannot. For the immutability trigger, create a single function immutable_row_guard() that raises an exception with message 'This table is append-only', and attach it as BEFORE UPDATE OR DELETE ON declaration_audit_log FOR EACH ROW EXECUTE FUNCTION immutable_row_guard(). This function can be reused for any future append-only tables.

The INSERT RLS policy should use a WITH CHECK clause: WITH CHECK (actor_id = auth.uid() AND org_id = get_user_org_id(auth.uid())) — do not rely on the client to supply these values correctly.

Testing Requirements

SQL migration tests: (1) verify all columns exist with correct types; (2) verify INSERT succeeds for a valid row; (3) verify UPDATE raises an exception; (4) verify DELETE raises an exception; (5) verify event_type CHECK rejects an invalid value like 'deleted'; (6) verify RLS blocks cross-org SELECT; (7) verify RLS blocks inserting a row with a different actor_id than auth.uid(); (8) verify index on declaration_id exists. Dart integration test against local Supabase emulator: verify DeclarationAuditLogger (task-010) can insert audit rows and that attempting to update or delete via the Dart client throws a Supabase error.

Component

Declaration Audit Logger

infrastructure medium

Dependencies (1)

Create the confidentiality_declarations table with org-scoped RLS policies. Include columns for declaration content (encrypted), template version reference, driver reference, status (pending/acknowledged/expired), sent_at, acknowledged_at, and soft-delete columns for audit preservation. Add foreign key to declaration_templates. epic-driver-and-confidentiality-management-foundation-task-005

Epic Risks (3)

high impact medium prob security

Row-level security policies for driver assignments and declarations must correctly scope data to the coordinator's chapter without leaking records across organizations. An incorrect RLS predicate could silently return empty result sets or, worse, expose cross-org data, both of which are difficult to detect in unit tests.

Mitigation & Contingency

Mitigation: Write dedicated RLS integration test scenarios with multiple org fixtures asserting both data isolation and correct data visibility. Use Supabase's built-in policy testing utilities and review policies with a second developer.

Contingency: If RLS policies prove too complex to get right quickly, implement application-layer org scoping as a temporary guard while RLS is fixed in a follow-up, with an explicit security review gate before production deployment.

high impact medium prob security

The declaration audit logger must produce tamper-evident records. If the database allows updates or deletes on audit rows, the compliance guarantee is broken. Supabase does not natively prevent row deletion by default.

Mitigation & Contingency

Mitigation: Implement an insert-only RLS policy on the audit table that denies UPDATE and DELETE for all roles including the service role. Add a database trigger that rejects mutation attempts and logs the attempt itself.

Contingency: If immutability cannot be enforced at the database level within the sprint, store audit entries in an append-only Supabase Edge Function log stream as a temporary alternative, with a migration plan to the proper table once constraints are implemented.

medium impact low prob technical

The org-feature-flag-service caches flag values to avoid repeated database reads. If the cache is not invalidated promptly after an admin toggles the flag, coordinators may see stale UI state — either seeing driver features when they should not, or not seeing them when they should.

Mitigation & Contingency

Mitigation: Use a Supabase Realtime subscription to listen for changes on the driver_feature_flag_config table and invalidate the in-memory cache immediately on change. Set a short TTL (60 seconds) as a safety net.

Contingency: If Realtime subscription proves unreliable, expose a manual cache-bust endpoint accessible from the admin toggle action, ensuring the cache is cleared synchronously on every flag change.

Quick Links

All Tasks Execution Plan