Supabase Stats Database Views
Component Detail
Description
Backend Supabase SQL views or materialized views that pre-aggregate activity counts, hour totals, and reimbursement amounts grouped by peer mentor, chapter, and time period. Row-level security policies enforce coordinator chapter scope at the database layer, preventing over-fetching. Materialized views are refreshed on activity insert via Supabase triggers.
supabase-stats-views
Summaries
The Supabase Stats Database Views component delivers the analytical backbone that makes coordinator reporting trustworthy, fast, and secure. By pre-aggregating activity counts, hours logged, and reimbursement amounts directly at the database layer, the platform avoids expensive real-time computation on every dashboard load — keeping the mobile experience responsive even as data volumes grow. Critically, row-level security policies enforce that coordinators can only access data within their own chapter, eliminating a significant compliance and data privacy risk. This investment in database-layer aggregation and access control directly supports organizational trust, reduces operational overhead from manual report generation, and scales without proportional cost increases as the peer mentor network grows.
Supabase Stats Database Views is a high-complexity backend deliverable requiring SQL expertise in Supabase-specific view and trigger authoring, row-level security policy configuration, and materialized view refresh orchestration. It has implicit dependencies on the finalized activity, reimbursement, and chapter table schemas — any schema migration after views are created will require view rebuilds. Timeline risk is meaningful: materialized view refresh via Supabase triggers requires testing under concurrent insert load to validate correctness and latency. QA must verify that RLS policies correctly scope coordinator queries to their chapter and that chart granularity views (daily, weekly, monthly) return accurate date-bucketed aggregates across all TimeWindow ranges.
Deployment requires database migration scripts and trigger registration, which must be coordinated with the backend deployment pipeline.
Supabase Stats Database Views exposes five query interfaces targeting PostgreSQL views hosted on Supabase: peer_mentor_stats_view (per-user aggregates within a date range), coordinator_chapter_stats_view (chapter-scoped aggregates for coordinator dashboards), and three chart granularity views — activity_chart_daily_view, activity_chart_weekly_view, and activity_chart_monthly_view — all parameterized by user_id and date range. Materialized views are refreshed via Supabase database triggers on activity INSERT events, so read performance is O(1) regardless of activity table size. Row-level security policies are applied at the view level using auth.uid() and chapter membership joins, preventing over-fetching without client-side filtering. When consumed from the Dart repository layer, responses are deserialized into StatsSnapshot and PeerMentorStatRow via their fromJson factories.
Schema changes to the activity or reimbursement tables require coordinated view rebuilds and trigger re-registration.
Responsibilities
- Expose pre-aggregated activity totals per peer mentor and chapter
- Apply row-level security to enforce coordinator chapter scoping
- Refresh materialized data on new activity inserts via database triggers
- Provide chart-granularity breakdowns by day, week, and month
Interfaces
peer_mentor_stats_view(user_id, start_date, end_date)
coordinator_chapter_stats_view(chapter_ids, start_date, end_date)
activity_chart_daily_view(user_id, start_date, end_date)
activity_chart_weekly_view(user_id, start_date, end_date)
activity_chart_monthly_view(user_id, start_date, end_date)
Used Integrations (1)
External integrations and APIs this component relies on