Create Supabase schema for proxy audit log table

epic-coordinator-proxy-registration-foundation-task-005 — Write and apply Supabase migration to create the proxy_audit_log table with fields: id, event_type (ENUM: created, updated, deleted, bulk_created), coordinator_id, attributed_mentor_id, proxy_activity_id (FK nullable for bulk), org_id, payload_snapshot (JSONB), created_at. Table must be append-only — no UPDATE or DELETE RLS permissions granted to any role. Index on coordinator_id and attributed_mentor_id for fast audit queries.

critical priority low complexity database pending database specialist Tier 2

Acceptance Criteria

Migration creates the proxy_audit_log table with exact columns: id (UUID PK DEFAULT gen_random_uuid()), event_type (TEXT NOT NULL CHECK IN ('created','updated','deleted','bulk_created')), coordinator_id (UUID NOT NULL), attributed_mentor_id (UUID NOT NULL), proxy_activity_id (UUID NULLABLE FK → proxy_activities ON DELETE SET NULL), org_id (UUID NOT NULL), payload_snapshot (JSONB NOT NULL), created_at (TIMESTAMPTZ NOT NULL DEFAULT now())

RLS is enabled. Only one policy exists: INSERT for authenticated users where coordinator_id = auth.uid(). No UPDATE policy. No DELETE policy. No service_role bypass except for explicitly documented server-side audit functions

A Postgres trigger on proxy_activities is created that automatically writes to proxy_audit_log on INSERT (event_type='created'), UPDATE (event_type='updated'), and DELETE (event_type='deleted'). The trigger captures a JSONB snapshot of NEW (for insert/update) or OLD (for delete) into payload_snapshot

Indexes exist on coordinator_id, attributed_mentor_id, (org_id, created_at DESC), and (coordinator_id, attributed_mentor_id) composite

Table has no updated_at column — audit log rows are immutable by design and the absence of updated_at enforces this intent

For bulk_created events, proxy_activity_id is NULL and the payload_snapshot contains an array of all activity IDs in the batch

Rollback migration removes the trigger first, then the table

Technical Requirements

frameworks

Supabase

apis

Supabase Migrations CLI

Supabase RLS

PostgreSQL trigger functions

data models

proxy_audit_log

proxy_activities

performance requirements

Composite index on (org_id, created_at DESC) enables efficient time-range audit queries for compliance reviews

JSONB payload_snapshot should be limited to essential fields only (not full row dump) to control table growth — document the exact fields captured in a migration comment

For high-volume deployments, consider table partitioning by created_at year — add a comment noting this as a future consideration

security requirements

Append-only enforcement is critical for audit integrity — the absence of UPDATE/DELETE RLS policies is intentional and must be preserved in all future migrations

The trigger function must execute with SECURITY DEFINER to ensure it can write to the audit log regardless of the calling user's permissions

payload_snapshot must not store sensitive PII beyond what is necessary for audit purposes — exclude fields like notes (free-text) if they may contain personal health information, per GDPR principles relevant to Norwegian compliance context

coordinator_id in the audit log must be captured from the database session (session_user / auth.uid()) not from the application payload, to prevent spoofing

Execution Context

Execution Tier

Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

View Full Execution Plan

Implementation Notes

The trigger approach is strongly preferred over application-layer audit logging for this table — a trigger guarantees audit entries even if the application crashes mid-operation or if direct database access is ever used. Write the trigger function in PL/pgSQL and name it audit_proxy_activity_changes(). For the bulk_created case, the trigger on proxy_activities will fire per-row for bulk inserts — to produce a single bulk_created log entry, consider using a statement-level trigger that aggregates all affected rows. If statement-level triggers complicate the implementation, per-row triggers with event_type='created' for each bulk row are acceptable as a simpler alternative, documented as a deliberate tradeoff.

The payload_snapshot for 'created' and 'updated' events should capture: activity_type, date, duration_minutes, is_recurring, template_id — exclude notes for GDPR reasons. Document this exclusion in the migration comment.

Testing Requirements

SQL/pgTAP tests: (1) INSERT on proxy_activities automatically creates a proxy_audit_log row with event_type='created' and correct coordinator_id and attributed_mentor_id, (2) UPDATE on proxy_activities creates event_type='updated' row with NEW snapshot, (3) DELETE creates event_type='deleted' row with OLD snapshot, (4) direct UPDATE on proxy_audit_log is rejected by RLS, (5) direct DELETE on proxy_audit_log is rejected by RLS, (6) proxy_activity_id in audit log is SET NULL when the referenced proxy_activity is deleted (FK SET NULL behavior), (7) bulk insert trigger creates event_type='bulk_created' with array of IDs in payload. All tests run against local Supabase.

Component

Proxy Audit Logger

infrastructure low

Dependencies (1)

Write and apply Supabase migration to create the proxy_activities table with dual-attribution columns: coordinator_id (the registering coordinator) and attributed_mentor_id (the peer mentor credited in Bufdir reporting). Include org_id, activity_type, date, duration, notes, is_recurring, template_id (FK), and audit fields. Add RLS policies enforcing org-scoped coordinator write permissions and read permissions. epic-coordinator-proxy-registration-foundation-task-003

Epic Risks (3)

high impact medium prob security

Supabase RLS policies for org-scoped proxy access may be difficult to express correctly, especially for coordinators with multi-chapter access. An overly permissive policy could allow cross-org proxy registrations, corrupting Bufdir reporting; an overly restrictive policy could block legitimate coordinators from registering.

Mitigation & Contingency

Mitigation: Write integration tests covering all access boundary cases (same org, cross-org, multi-chapter coordinator) before merging any RLS migration. Use parameterised RLS test helpers already established by the auth feature.

Contingency: If RLS proves insufficient, add a server-side Edge Function validation layer that re-checks org membership before persisting any proxy record, providing defence in depth.

medium impact low prob technical

Adding new tables and foreign key constraints to an existing production Supabase database risks migration failures or locking issues if the database already contains active sessions during deployment.

Mitigation & Contingency

Mitigation: Use additive-only migrations (no DROP or ALTER on existing tables). Test full migration sequence in a staging Supabase project before production deployment. Schedule during low-traffic window.

Contingency: Maintain a rollback migration script. If the migration fails, the feature remains unreachable behind a feature flag while the schema issue is resolved.

high impact medium prob security

Audit log entries must be immutable for compliance, but Supabase RLS by default allows row owners to update their own rows. If audit records are accidentally mutable, dispute resolution and accountability guarantees are invalidated.

Mitigation & Contingency

Mitigation: Configure the proxy_audit_log table with an RLS policy that allows INSERT for coordinators but denies UPDATE and DELETE for all roles including service_role, enforced at the database level.

Contingency: If RLS cannot fully prevent updates, create a database trigger that reverts any UPDATE to the audit table and logs the attempt as a security event.

Quick Links

All Tasks Execution Plan