Author Supabase SQL views for activity aggregation

epic-activity-statistics-dashboard-data-foundation-task-004 — 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.

critical priority high complexity database pending database specialist Tier 1

Acceptance Criteria

peer_mentor_stats_view returns columns: peer_mentor_id, peer_mentor_name, chapter_id, activity_count (integer), total_hours (numeric, 2 decimal places), reimbursement_amount (numeric, 2 decimal places)

chapter_stats_view returns columns: chapter_id, chapter_name, activity_count (integer), total_hours (numeric, 2 decimal places), reimbursement_amount (numeric, 2 decimal places), active_peer_mentor_count (integer)

Both views accept date-range filtering via WHERE clause on the activity's recorded_at timestamp column (views do not hardcode a date range)

Aggregation logic (rounding rules, category-to-hours mapping, reimbursement rate application) is byte-for-byte identical to the Bufdir export pipeline SQL — verified by running both queries on the same dataset and comparing totals with zero delta

Migration file supabase/migrations/YYYYMMDDHHMMSS_stats_views.sql contains both CREATE OR REPLACE VIEW statements and a down-migration that drops both views

Migration runs successfully on a clean Supabase project with no manual intervention

EXPLAIN on each view (without date-range filter) shows a seq scan cost within acceptable bounds for up to 100k activity rows (document baseline cost in migration comments)

Both views are accessible to the authenticated role used by the Flutter app (no permission errors on SELECT)

Technical Requirements

apis

Supabase PostgREST — views exposed as REST endpoints

data models

activities

peer_mentors

chapters

reimbursements

activity_categories

performance requirements

View queries with a 1-year date range filter must return in under 500 ms on a 100k-activity dataset (baseline before materialisation in task-005)

Column types must match the Dart model field types exactly to avoid implicit casting overhead in PostgREST serialisation

security requirements

Views must not expose PII columns beyond peer_mentor_id and peer_mentor_name; no contact details, addresses, or health data

GRANT SELECT on views must be scoped to the authenticated role only — not the anon role

Down-migration must cleanly revoke grants before dropping views

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Locate migration files in supabase/migrations/ following the existing naming convention. Before writing the SQL, extract and review the exact aggregation logic from the Bufdir export pipeline (likely in a Supabase Edge Function or existing SQL file) — do not replicate from memory. Key areas of divergence to watch: (a) rounding — use ROUND(value::numeric, 2) not TRUNC; (b) category exclusions — some activity categories may be excluded from Bufdir totals; (c) reimbursement rates — may vary by organisation or activity type and reference a lookup table. Use CREATE OR REPLACE VIEW (not CREATE VIEW) so the migration is re-runnable.

Column aliases must exactly match the snake_case field names in the Dart PeerMentorStatRow and StatsSnapshot models from task-001 to eliminate any mapping layer. Add inline SQL comments explaining each aggregation formula and its Bufdir reference.

Testing Requirements

SQL-level regression tests using pgTAP (if available on Supabase) or a seed-data script: (1) insert a known set of activities (e.g., 10 activities for peer mentor A across 2 chapters, with known hours and reimbursement amounts) and assert view output matches hand-calculated expected values; (2) insert an activity outside the date-range filter and assert it is excluded from aggregated totals; (3) run the same dataset through the Bufdir export query and assert zero delta on all numeric columns; (4) verify that a SELECT by the anon role is denied (permission error). Run migration up/down/up in CI to confirm idempotency.

Component

Supabase Stats Database Views

infrastructure high

Dependencies (1)

Create the typed Dart data models required by the statistics dashboard: StatsSnapshot (aggregate totals for a time window), PeerMentorStatRow (per-mentor row data), ChartDataPoint (x/y pair for chart rendering), and TimeWindow enum (week, month, quarter, year, custom). All models must be immutable, support JSON serialisation via fromJson/toJson, and include equality/hashCode. Field names must exactly mirror the Supabase view column names to eliminate mapping bugs. epic-activity-statistics-dashboard-data-foundation-task-001

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