Create materialized views with date-range partitioning

epic-activity-statistics-dashboard-data-foundation-task-005 — Convert the SQL views from task-004 into materialized views (mv_peer_mentor_stats, mv_chapter_stats) with REFRESH CONCURRENTLY support. Add GIN indexes on chapter_id and date columns. Create a materialized view for chart data (mv_activity_time_series) with daily buckets suitable for bar-chart rendering. Include EXPLAIN ANALYZE targets: queries on 12-month windows must execute under 200 ms on a 100k-activity dataset.

critical priority high complexity database pending database specialist Tier 2

Acceptance Criteria

mv_peer_mentor_stats and mv_chapter_stats are created as MATERIALIZED VIEW (not plain VIEW), each with a unique index required for REFRESH CONCURRENTLY

REFRESH CONCURRENTLY mv_peer_mentor_stats and REFRESH CONCURRENTLY mv_chapter_stats execute without error after data changes

GIN or B-tree index on chapter_id column exists on both materialised views and is used by EXPLAIN ANALYZE for chapter-filtered queries

B-tree index on the date/timestamp column exists on mv_activity_time_series and is used by EXPLAIN ANALYZE for date-range queries

mv_activity_time_series returns columns: bucket_date (date, daily granularity), chapter_id, activity_count, total_hours, suitable for bar-chart x=date y=count or y=hours rendering

EXPLAIN ANALYZE on a 12-month date-range query against all three materialized views shows actual execution time under 200 ms on a seeded 100k-activity dataset

Migration file includes both up (CREATE MATERIALIZED VIEW + indexes + REFRESH) and down (DROP MATERIALIZED VIEW CASCADE) scripts

A Supabase scheduled function or pg_cron job is configured to REFRESH CONCURRENTLY all three views at least once per hour

Technical Requirements

apis

Supabase — pg_cron extension for scheduled refresh

Supabase PostgREST — materialized views exposed as REST endpoints

data models

activities

peer_mentors

chapters

mv_peer_mentor_stats

mv_chapter_stats

mv_activity_time_series

performance requirements

12-month range query under 200 ms on 100k activities (EXPLAIN ANALYZE actual time target)

REFRESH CONCURRENTLY must complete without table locks — verified by checking pg_locks during refresh

Index scan ratio on chapter_id filter must be > 95% (seq scan only acceptable for full-table reads)

security requirements

Materialized views must inherit the same GRANT SELECT permissions as the plain views from task-004

REFRESH operations must be executable only by the service role (postgres), not the authenticated client role

Execution Context

Execution Tier

Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

View Full Execution Plan

Implementation Notes

REFRESH CONCURRENTLY requires a unique index on each materialized view — add CREATE UNIQUE INDEX ON mv_peer_mentor_stats (peer_mentor_id, chapter_id) and equivalent for mv_chapter_stats (chapter_id). For mv_activity_time_series use DATE_TRUNC('day', recorded_at AT TIME ZONE 'UTC') as the bucket_date to ensure consistent daily boundaries. GIN indexes are best suited for array/JSONB columns; for scalar chapter_id and date columns use standard B-tree indexes. Enable pg_cron via Supabase dashboard (Database → Extensions) and schedule: SELECT cron.schedule('refresh-stats-views', '0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_peer_mentor_stats; REFRESH MATERIALIZED VIEW CONCURRENTLY mv_chapter_stats; REFRESH MATERIALIZED VIEW CONCURRENTLY mv_activity_time_series').

Document the staleness window (up to 1 hour) in a code comment so the UI layer can display a 'last updated' timestamp to coordinators.

Testing Requirements

Performance tests: (1) seed the database with 100k synthetic activity rows spanning 2 years across 20 chapters and 100 peer mentors; (2) run EXPLAIN ANALYZE on a 12-month range query for each materialized view and assert actual time < 200 ms; (3) verify REFRESH CONCURRENTLY completes without error and without exclusive locks (query pg_locks); (4) verify index usage with EXPLAIN (format json) — confirm Index Scan node present for chapter_id filter. Regression: after REFRESH, verify totals in materialized views match the equivalent plain view query on the same dataset. Migration: run up/down/up in a clean database to confirm idempotency.

Component

Supabase Stats Database Views

infrastructure high

Dependencies (1)

Write the Supabase SQL views that pre-aggregate activity data: (1) peer_mentor_stats_view — activity count, total hours, and reimbursement amount grouped by peer_mentor_id and chapter_id; (2) chapter_stats_view — same metrics rolled up to chapter level. SQL must use the identical aggregation logic as the Bufdir export pipeline (same rounding, same category mapping) to ensure zero reconciliation delta. Include migration file with up/down scripts. epic-activity-statistics-dashboard-data-foundation-task-004

Epic Risks (3)

medium impact medium prob technical

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.

high impact medium prob integration

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.

high impact low prob security

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.

Quick Links

All Tasks Execution Plan