Create database trigger to refresh materialized views
epic-activity-statistics-dashboard-data-foundation-task-007 — Implement a PostgreSQL trigger function that calls REFRESH MATERIALIZED VIEW CONCURRENTLY on mv_peer_mentor_stats and mv_activity_time_series after each INSERT into the activities table. The trigger must fire AFTER INSERT, be DEFERRABLE INITIALLY DEFERRED within a transaction, and include a debounce guard (skip refresh if last refresh was under 10 seconds ago) to prevent thundering-herd during bulk proxy registrations. Include migration and rollback scripts.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 3 - 413 tasks
Can start after Tier 2 completes
Implementation Notes
Use a dedicated `stats_refresh_log` table with a single row (upsert pattern) rather than a sequence or pg_sleep to implement the debounce — this survives connection resets. The trigger body should be a PL/pgSQL function that: (1) checks `NOW() - last_refreshed_at < INTERVAL '10 seconds'` from stats_refresh_log, (2) if within window, returns NEW immediately, (3) otherwise acquires `SELECT ... FOR UPDATE SKIP LOCKED` on the log row (if locked, another refresh is in progress — skip), (4) calls REFRESH MATERIALIZED VIEW CONCURRENTLY for both views inside an EXCEPTION block, (5) updates the log timestamp. Use DEFERRABLE INITIALLY DEFERRED at the CONSTRAINT TRIGGER level so the function runs once per transaction at commit time, which naturally handles bulk inserts.
The migration must be idempotent (`CREATE OR REPLACE FUNCTION`, `DROP TRIGGER IF EXISTS` before `CREATE`). Test the rollback script in CI before merging.
Testing Requirements
Write pgTAP or plain SQL test scripts covering: (1) single INSERT triggers one refresh and updates stats_refresh_log; (2) two rapid INSERTs within 10 seconds result in only one refresh; (3) bulk INSERT (100 rows) in one transaction triggers exactly one refresh; (4) refresh failure (e.g., drop view temporarily) does not cause INSERT to fail; (5) rollback script cleanly removes all objects. Run tests in a Supabase local dev instance (`supabase start`). Additionally, include a Dart integration test that inserts a row via the Supabase client and asserts the materialized view count changes within 2 seconds.
Materialized views over large activity tables may have refresh latency exceeding the 2-second SLA under high insert load, causing stale data to appear on the dashboard immediately after a peer mentor registers an activity.
Mitigation & Contingency
Mitigation: Design the materialized view refresh trigger to run asynchronously via a Supabase Edge Function rather than a synchronous trigger, and set a maximum staleness tolerance of 5 seconds documented in the feature spec. Add a CONCURRENTLY refresh strategy so reads are never blocked.
Contingency: If refresh latency cannot meet SLA, fall back to a regular (non-materialized) view for the dashboard and accept slightly higher query cost per request. Revisit materialized approach once Supabase pg_cron or background workers are available.
The aggregation counting rules for the dashboard may diverge from those used in the Bufdir export pipeline (e.g., which activity types count, how duplicate registrations are handled), creating a reconciliation burden for coordinators at reporting time.
Mitigation & Contingency
Mitigation: Run the BufDir Alignment Validator against a shared reference dataset before any view is merged to main. Encode the counting rules as a shared Supabase function called by both the stats views and the export query builder so there is a single source of truth.
Contingency: If divergence is discovered post-launch, ship a visible banner on the dashboard stating that numbers are indicative and may differ from the export until the reconciliation fix is deployed. Prioritize the fix as a P0 defect.
Multi-chapter coordinators (up to 5 chapters per NHF requirement) require RLS policies that filter on an array of chapter IDs, which is more complex than single-value RLS and could be misconfigured, leaking data across chapters or blocking legitimate access.
Mitigation & Contingency
Mitigation: Write integration tests that verify cross-chapter isolation for a coordinator assigned to chapters A and B cannot see data from chapter C. Use parameterized RLS policies with auth.uid()-based chapter lookup to avoid hardcoded values.
Contingency: If RLS misconfiguration is detected in testing, temporarily restrict coordinator queries to single-chapter scope (coordinator's primary chapter) and ship multi-chapter support as a fast-follow patch once RLS logic is verified.