Implement ProxyAuditLogger service in Dart

epic-coordinator-proxy-registration-foundation-task-006 — Implement the ProxyAuditLogger class as an infrastructure service that writes immutable audit records to the proxy_audit_log table after every proxy registration event. Expose methods: logCreated(), logBulkCreated(), logUpdated(). Called internally by ProxyActivityRepository after successful inserts. Include payload snapshot serialization of the activity data at time of registration for dispute resolution and NHF/HLF audit trail requirements.

critical priority low complexity infrastructure pending backend specialist Tier 3

Acceptance Criteria

ProxyAuditLogger class is implemented as a Dart service with logCreated(ProxyActivityRecord), logBulkCreated(List<ProxyActivityRecord>), and logUpdated(ProxyActivityRecord) methods

Each method writes exactly one row to proxy_audit_log per activity record with event_type set to 'created', 'bulk_created', or 'updated' respectively

The payload_snapshot field stores a complete JSON serialization of the ProxyActivityRecord at the moment of the call — not a reference, but a full copy

logBulkCreated writes one audit row per mentor entry (N mentors = N rows), not one row per batch

logUpdated captures the new state of the record after update, including coordinator_id and attributed_mentor_id

The service does NOT perform any read, update, or delete on the audit log table — only INSERT

All methods are async and propagate Supabase exceptions to the caller without swallowing errors

ProxyAuditLogger is injected as a dependency into ProxyActivityRepository and called after each successful insert/update

Audit log rows include: event_type, proxy_activity_id, coordinator_id, attributed_mentor_id, timestamp, payload_snapshot

No test can successfully call UPDATE or DELETE on proxy_audit_log due to RLS policy enforcement (verified by integration test in task-010)

Technical Requirements

frameworks

Flutter

Dart

apis

Supabase REST/PostgREST (insert only on proxy_audit_log table)

data models

ProxyActivityRecord

ProxyAuditLogEntry

proxy_audit_log (Supabase table)

performance requirements

Each logCreated/logUpdated call must complete within 500ms under normal network conditions

logBulkCreated must use a single Supabase batch insert (not N sequential inserts) to stay within 2s for up to 50 mentor entries

security requirements

RLS policy on proxy_audit_log must deny UPDATE and DELETE for all roles including authenticated coordinators

INSERT allowed only for authenticated coordinators acting on their own org's data

payload_snapshot must not include fields outside the ProxyActivityRecord schema (no PII beyond what is already stored in the main record)

All Supabase calls must use the authenticated client with the user's JWT — no service role key client-side

Execution Context

Execution Tier

Tier 3

Tier 3 - 413 tasks

Can start after Tier 2 completes

View Full Execution Plan

Implementation Notes

Define a ProxyAuditLogEntry model with fields: id (uuid), proxy_activity_id (uuid), event_type (enum: created | bulk_created | updated), coordinator_id (uuid), attributed_mentor_id (uuid), timestamp (DateTime), payload_snapshot (Map). Serialize ProxyActivityRecord to JSON using a toJson() method before storing — do not store a Dart object reference. Use Supabase's .from('proxy_audit_log').insert([...]) for bulk writes to keep round-trips minimal. This service should have zero business logic — it is purely an append-only writer.

Register it via Riverpod provider so ProxyActivityRepository can receive it via constructor injection. The RLS policy is the authoritative immutability guarantee; the service layer simply never calls update/delete — do not add application-level guards that could create a false sense of security.

Testing Requirements

Unit tests (task-010) must verify: (1) logCreated produces exactly one audit row with event_type='created', (2) logBulkCreated produces one row per mentor with event_type='bulk_created', (3) logUpdated produces one row with event_type='updated', (4) payload_snapshot equals the full serialized activity at call time, (5) RLS blocks UPDATE and DELETE on audit table. Use flutter_test with a Supabase mock client for unit isolation. Integration tests against a real Supabase test project should confirm immutability at the database level.

Component

Proxy Audit Logger

infrastructure low

Dependencies (2)

Implement the ProxyActivityRepository class using Supabase client. Provide methods: insertProxyActivity(coordinatorId, attributedMentorId, activityData), insertBulkProxyActivities(list), fetchProxyActivitiesByCoordinator(), fetchProxyActivitiesByMentor(). Ensure dual-attribution fields are always persisted and never swapped. Apply org-scoped RLS via Supabase authenticated session. epic-coordinator-proxy-registration-foundation-task-004 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. epic-coordinator-proxy-registration-foundation-task-005

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