Author Supabase pre-aggregated stats views migration

epic-coordinator-statistics-dashboard-foundation-task-002 — Write the database migration that creates pre-aggregated Supabase views keyed by org_id + coordinator_id + month. Views must replace real-time aggregation of activity rows with sub-100ms indexed lookups. Include indexes on the composite key, period filtering columns, and activity_type columns. Sequence and test the migration against a staging Supabase instance.

critical priority high complexity database pending database specialist Tier 1

Acceptance Criteria

Migration file follows Supabase migration naming convention (timestamp_create_stats_views.sql) and is placed in supabase/migrations/

View coordinator_monthly_stats is created with columns: org_id, coordinator_id, activity_month (DATE truncated to first of month), activity_type, activity_count, total_duration_minutes

Composite index created on coordinator_monthly_stats(org_id, coordinator_id, activity_month) for primary lookup pattern

Index created on coordinator_monthly_stats(activity_month) for period range filtering

Index created on coordinator_monthly_stats(activity_type) for type filtering

View peer_mentor_monthly_stats created with same column structure but keyed by org_id, mentor_id, activity_month

Row Level Security (RLS) policies on underlying activity tables are respected by the views — no RLS bypass

EXPLAIN ANALYZE on a coordinator_monthly_stats query filtered by org_id + coordinator_id + month range shows Index Scan (not Seq Scan) on the staging database

Query latency for a coordinator with 500 activity rows across 12 months is under 100ms on staging Supabase

Migration is idempotent: running it twice does not produce errors (use CREATE OR REPLACE VIEW and CREATE INDEX IF NOT EXISTS)

Rollback migration file included that drops the views and indexes in correct dependency order

Migration tested on staging Supabase instance with real activity data and results documented in a PR comment

Technical Requirements

frameworks

Supabase

PostgreSQL

apis

Supabase Management API (migration deployment)

data models

StatsRow

coordinator_monthly_stats view

peer_mentor_monthly_stats view

performance requirements

Sub-100ms query latency for single coordinator monthly stats lookup on staging with 500+ rows

View refresh must not lock activity table for more than 100ms — use standard view (not materialised) unless profiling justifies materialised view

security requirements

Views must not bypass RLS — use SECURITY INVOKER (default) not SECURITY DEFINER

org_id must always be part of the composite key to prevent cross-organisation data leakage at the database layer

Verify with a test Supabase role that has coordinator permissions cannot SELECT rows from another org

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Use a standard PostgreSQL view (not materialised) initially — Supabase handles query planning well and a materialised view requires a refresh strategy that adds operational complexity. The view query should use DATE_TRUNC('month', occurred_at) to group by month. Use COALESCE for activity_count and total_duration_minutes to return 0 instead of NULL for missing months if a time series is needed. For the composite index, column order matters: put org_id first (highest cardinality filter), then coordinator_id, then activity_month.

Ensure the index is a B-tree index (default). If query latency exceeds 100ms after indexing, consider a materialised view with a pg_cron refresh job — document this as a follow-up task. The migration must be reviewed by the database lead before merging to main.

Testing Requirements

Manual testing on staging Supabase instance. Run EXPLAIN ANALYZE on target query patterns and capture output. Seed staging with synthetic activity data (minimum 3 coordinators, 2 orgs, 12 months each) using a seed SQL script committed alongside the migration. Verify RLS by connecting with a coordinator-role JWT and confirming only own-org rows are returned.

Verify idempotency by running the migration SQL twice and confirming no errors. Document all test results in the PR description. After migration is merged, run flutter integration tests that call the repository layer and assert returned data matches seeded values.

Component

Stats Repository

data medium

Dependencies (1)

Create all typed Dart model classes that represent the stats data contract consumed by higher tiers. This includes StatsRow, PeriodStats, ActivityTypeStats, CoordinatorStats, and PeerMentorStats models with proper JSON serialization, equality, and copyWith methods. These models form the stable data contract for all dashboard widgets. epic-coordinator-statistics-dashboard-foundation-task-001

Epic Risks (3)

high impact medium prob technical

Pre-aggregated Supabase views may still be slow for orgs with very large activity datasets (NHF with 1,400 chapters). If the view query plan performs sequential scans, dashboard load times could exceed acceptable thresholds and degrade the perceived value of the feature.

Mitigation & Contingency

Mitigation: Design views with composite indexes on (org_id, coordinator_id, month) from the start. Run EXPLAIN ANALYZE during development against a seeded dataset of realistic scale. Add materialized view refresh strategy if needed.

Contingency: If live view performance is insufficient, convert to materialized views refreshed on a schedule or on activity-write triggers. Expose the refresh delay transparently in the UI with a 'last updated' timestamp.

high impact low prob security

Supabase RLS policies for the stats views may not be configured correctly during initial migration, potentially allowing cross-coordinator data leakage before the RoleAccessValidator layer is reached. This is a security and compliance risk.

Mitigation & Contingency

Mitigation: Write RLS integration tests as part of this epic that explicitly verify a coordinator JWT cannot read another coordinator's stats rows. Apply RLS policies in the migration script itself, not as a manual step.

Contingency: If an RLS gap is discovered post-deployment, immediately disable the stats screen via a feature flag, apply the corrected RLS migration, and re-enable after verification. Log and audit all queries that ran during the gap window.

medium impact medium prob integration

Cache invalidation logic may not be triggered correctly when a new activity is registered by a peer mentor or when an expense approval is granted. Stale data could cause coordinators to make decisions based on outdated KPIs, undermining trust in the dashboard.

Mitigation & Contingency

Mitigation: Define explicit invalidation event contracts with the activity registration and expense approval pipelines. Implement an event bus subscription within StatsCacheManager. Document the invalidation contract in code.

Contingency: If event-driven invalidation proves unreliable, add a manual 'Refresh' pull-to-refresh gesture on the dashboard and reduce TTL to 5 minutes as a fallback degradation strategy.

Quick Links

All Tasks Execution Plan