🏠 Overview › ✅ Implementation Tasks ›✅ epic-bufdir-reporting-export-foundation-task-001

Create bufdir_export_audit_log database table

epic-bufdir-reporting-export-foundation-task-001 — Design and implement the bufdir_export_audit_log table with append-only semantics in Supabase. Define columns for export_id, org_id, triggered_by, export_format, period_start, period_end, status, error_message, file_path, created_at. Add CHECK constraints and ensure no UPDATE or DELETE is permitted via triggers or RLS to guarantee immutability.

critical priority medium complexity database pending database specialist Tier 0

Acceptance Criteria

Migration file exists (e.g. supabase/migrations/YYYYMMDDHHMMSS_create_bufdir_export_audit_log.sql) and applies cleanly against a fresh Supabase instance via `supabase db push`
export_id column is UUID PRIMARY KEY with DEFAULT gen_random_uuid()
org_id column is UUID NOT NULL with a FOREIGN KEY referencing the organisations table (or equivalent)
triggered_by column is UUID NOT NULL referencing auth.users(id)
export_format column has CHECK constraint limiting values to ('xlsx', 'csv', 'pdf')
status column has CHECK constraint limiting values to ('initiated', 'processing', 'completed', 'failed', 'partial_failure')
period_start and period_end are DATE NOT NULL with CHECK constraint enforcing period_start <= period_end
created_at is TIMESTAMPTZ NOT NULL DEFAULT now() — no updated_at column exists on this table
A BEFORE UPDATE trigger raises an exception with message 'Audit log records are immutable' for any UPDATE attempt by any role
A BEFORE DELETE trigger raises an exception with message 'Audit log records are immutable' for any DELETE attempt by any role
Attempt to UPDATE a row via Supabase client returns a PostgreSQL error (verified in a migration smoke test or manual test script)
Attempt to DELETE a row returns a PostgreSQL error (same verification)
Index exists on (org_id, created_at DESC) to support paginated history queries

Technical Requirements

frameworks
Supabase migrations (SQL)
PostgreSQL 15+
apis
Supabase Management API (for applying migration)
supabase CLI (supabase db push / supabase db diff)
data models
bufdir_export_audit_log
organisations (foreign key target)
auth.users (foreign key target)
performance requirements
Index on (org_id, created_at DESC) must be created in the same migration
Table must handle at least 10,000 rows per organisation without query degradation on history list queries
security requirements
No service_role bypass for immutability triggers — triggers must fire for ALL roles including service_role
created_at must be server-set (DEFAULT now()) — client must not be able to supply a custom timestamp
org_id foreign key ensures orphaned audit records are structurally impossible

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Use PostgreSQL trigger functions rather than RLS for immutability because RLS can be bypassed by service_role in Supabase — triggers cannot be bypassed. Define the trigger function in the same migration file as the table. Keep the trigger function body minimal: check TG_OP and raise an exception. For the CHECK constraint on status, use an explicit list rather than an enum type to make future additions easier via a new migration without requiring type alteration.

Do not add an updated_at column — its absence is a semantic signal that the table is append-only. The index on (org_id, created_at DESC) should be CONCURRENTLY in production but can be non-concurrent in the migration since the table is new. Consider adding a partial index on (org_id, status) WHERE status IN ('initiated', 'processing') to speed up in-progress export lookups.

Testing Requirements

Write a SQL smoke-test script (or a Dart integration test using the Supabase client) covering: (1) successful INSERT returns the new export_id; (2) UPDATE attempt throws and leaves the row unchanged; (3) DELETE attempt throws; (4) INSERT with invalid export_format value ('json') is rejected by CHECK constraint; (5) INSERT with period_start > period_end is rejected; (6) INSERT with a non-existent org_id is rejected by FK constraint. Run these tests against a local Supabase instance (`supabase start`) in CI.

Component
Export Audit Log Repository
data low
Epic Risks (2)
high impact medium prob security

RLS policies for the audit log and schema config tables must correctly handle multi-chapter membership hierarchies (up to 1,400 local chapters for NHF). Incorrect policies could either over-expose data across organisations or prevent legitimate coordinator access, both of which are serious compliance failures.

Mitigation & Contingency

Mitigation: Design RLS policies using the existing org hierarchy resolver pattern. Write integration tests that verify cross-organisation isolation with representative fixture data covering NHF's multi-level hierarchy before merging.

Contingency: If RLS policies prove too complex to express safely in Postgres, implement a Supabase Edge Function as a data access proxy that enforces isolation in application code, with RLS serving as a secondary defence layer.

medium impact medium prob scope

Bufdir's column schema is expected to evolve as Norse Digital Products negotiates a simplified digital reporting format. If the schema config versioning model is too rigid, applying Bufdir schema updates without a code deployment could fail, forcing emergency releases.

Mitigation & Contingency

Mitigation: Design the schema config table to store the full JSON column mapping as a JSONB field with a version number. Provide an admin API to upsert new versions without any schema migration required.

Contingency: If the versioning model is insufficient for a Bufdir schema change, fall back to a code deployment with the updated default schema, using the database config only for org-specific overrides.

Quick Links
All Tasks Execution Plan