Add audit logging for all HLF website sync operations

epic-peer-mentor-pause-management-automated-expiry-task-009 — Instrument HLFWebsiteSyncService to write a structured audit log entry for every sync attempt, including mentor ID, sync direction (remove/restore), Dynamics portal response code, retry count, timestamp, and outcome (success/failure). Logs must be queryable for compliance audits and incident investigation, and must be retained according to HLF data retention policy.

high priority low complexity backend pending backend specialist Tier 5

Acceptance Criteria

A hlf_sync_audit_log table is created in Supabase with columns: id (uuid), organisation_id (uuid), mentor_id (uuid), operation (enum: remove/restore), dynamics_response_code (integer nullable), retry_count (integer), outcome (enum: success/failure/dead_lettered), error_message (text nullable), triggered_by_event (text), created_at (timestamptz)

Every sync attempt — including intermediate retry attempts — writes a row to hlf_sync_audit_log

On success, outcome='success' and dynamics_response_code reflects the actual HTTP 2xx code returned

On retryable failure, outcome='failure' with the HTTP error code and error_message populated

On dead-letter, outcome='dead_lettered' and retry_count reflects the total attempts made

triggered_by_event column records the source event (e.g., 'status_change:paused', 'status_change:expired_cert', 'manual_coordinator_requeue')

Audit log rows are immutable — no UPDATE or DELETE permitted via RLS; only INSERT allowed from service role

Audit log is queryable by organisation_id, mentor_id, outcome, and date range for compliance review

Audit log entries must be retained for a minimum of 5 years per HLF data retention policy (enforced via Supabase table retention policy or documented manual archival process)

No PII beyond mentor UUID is stored in the audit log — mentor name and email are never written to this table

Technical Requirements

frameworks

Supabase Edge Functions (Deno)

Supabase PostgreSQL

apis

Supabase PostgreSQL direct insert (service role) from Edge Function

data models

assignment

performance requirements

Audit log insert must not add more than 50ms to the sync operation critical path — use fire-and-continue pattern if latency is a concern

Audit log table must have indexes on (mentor_id, created_at) and (outcome, created_at) for efficient compliance queries

security requirements

RLS policy: INSERT allowed for service role only; SELECT allowed for coordinator role filtered by organisation_id; no UPDATE or DELETE for any role

Audit log must be append-only — any attempt to modify or delete a row must fail at the database level

Table does not store PII beyond mentor UUID; no names, emails, phone numbers, or personnummer

Execution Context

Execution Tier

Tier 5

Tier 5 - 253 tasks

Can start after Tier 4 completes

View Full Execution Plan

Implementation Notes

Implement audit logging as a simple fire-and-forget insert at the end of each sync attempt within the Edge Function. Do not let audit log insert failures propagate back to the queue processor — catch the insert error, log it to stderr (Edge Function logs), and continue. Use a dedicated service role Supabase client within the Edge Function for audit log inserts so RLS policies do not interfere. The hlf_sync_audit_log is distinct from the generic bufdir_export_audit_log — do not reuse that table; the schema and retention policies differ.

For compliance reporting, consider adding a Supabase database function get_hlf_sync_audit_summary(org_id, from_date, to_date) that returns aggregated counts by outcome to simplify coordinator compliance views.

Testing Requirements

Unit tests: mock the Supabase insert call; assert audit log row is constructed with correct fields for each scenario (success, retryable failure, dead-letter). Integration tests against Supabase test instance: run a full sync sequence (success case, then forced failure case); query hlf_sync_audit_log and assert all expected rows exist with correct field values. Test RLS: attempt UPDATE and DELETE as coordinator role — both must be rejected with a permission error. Test immutability constraint at database level.

Component

HLF Website Sync Service

service high

Dependencies (1)

Add robust retry logic to HLFWebsiteSyncService with configurable max attempts and exponential backoff. On final retry exhaustion, record the failed sync operation in a dead-letter queue and dispatch a failure alert to the coordinator or system administrator. Ensure partial failures do not block subsequent sync operations for other mentors. epic-peer-mentor-pause-management-automated-expiry-task-008

Epic Risks (4)

high impact medium prob technical

The nightly expiry checker may run multiple times due to scheduler retries or infrastructure issues, causing duplicate auto-transitions and duplicate coordinator notifications that erode trust in the notification system.

Mitigation & Contingency

Mitigation: Implement idempotency via a unique constraint on (mentor_id, threshold_day, certification_expiry_date) in the cert_expiry_reminders table. Auto-transitions should be wrapped in a Postgres RPC that checks current status before applying, making repeated invocations safe.

Contingency: Add a compensation query in the reconciliation log that detects duplicate log entries for the same certification period and alerts the operations team for manual review within 24 hours.

high impact medium prob integration

The HLF Dynamics portal API may have eventual-consistency behaviour or rate limits that cause website listing updates to lag behind status changes, leaving expired mentors visible on the public website for an unacceptable window.

Mitigation & Contingency

Mitigation: Design the sync service to be triggered immediately on status transitions (event-driven via database webhook) in addition to the nightly batch run. Implement a reconciliation job that verifies sync state against app state and re-triggers any divergent records.

Contingency: If real-time sync cannot be guaranteed, implement a manual 'force sync' action in the coordinator dashboard so coordinators can trigger an immediate re-sync for urgent cases. Document the expected sync lag in coordinator onboarding materials.

medium impact medium prob scope

Stakeholder requests to extend the expiry checker to handle additional certification types, grace periods, or organisation-specific threshold configurations may significantly increase scope beyond what is designed here, delaying delivery.

Mitigation & Contingency

Mitigation: Parameterise threshold day values (30, 14, 7) via configuration repository rather than hard-coding them, enabling per-organisation customisation without code changes. Document that grace period logic and additional cert types are out of scope for this epic and require a dedicated follow-up.

Contingency: Deliver the feature with hard-coded HLF-standard thresholds first and introduce the configuration repository as a follow-up task in the next sprint, using a feature flag to enable per-org threshold overrides.

high impact low prob security

Dynamics portal API credentials stored as environment secrets in Supabase Edge Function configuration may be rotated or invalidated by HLF IT without notice, causing silent sync failures that go undetected for multiple days.

Mitigation & Contingency

Mitigation: Implement credential health-check calls on each scheduler run and emit an immediate alert on auth failure rather than only alerting after N consecutive failures. Document the credential rotation procedure with HLF IT and establish a rotation notification protocol.

Contingency: Maintain a break-glass manual sync script accessible to HLF administrators that can re-execute the Dynamics sync with newly provided credentials while the automated system is restored.

Quick Links

All Tasks Execution Plan