Document data-foundation layer and Bufdir alignment contract

epic-activity-statistics-dashboard-data-foundation-task-014 — Write developer documentation covering: (1) the SQL view schema with column descriptions and Bufdir field mappings; (2) how to run the Bufdir Alignment Validator and interpret drift reports; (3) the RLS policy model with a diagram showing coordinator/peer-mentor access boundaries; (4) the Dart model-to-view column mapping table; (5) instructions for adding a new stats metric end-to-end (SQL view → model → repository → validator). Store as STATS_DATA_LAYER.md in the project docs directory.

medium priority low complexity documentation pending documentor Tier 6

Acceptance Criteria

STATS_DATA_LAYER.md exists in the project docs directory and is committed to version control

Section 1 lists every column in the SQL stats view with: column name, data type, nullable flag, human-readable description, and corresponding Bufdir reporting field name (or 'N/A')

Section 2 provides a step-by-step CLI command sequence for running the Bufdir Alignment Validator locally, including prerequisite setup

Section 2 explains each drift report field: field_name, expected_value, actual_value, severity, and recommended_action

Section 3 contains an ASCII or Mermaid diagram showing RLS policy boundaries: which Supabase roles can SELECT/INSERT/UPDATE which view rows, scoped to organization_id and chapter_id

Section 4 contains a two-column table mapping every Dart model field to its SQL view column, including type conversion notes where relevant

Section 5 is a numbered walkthrough (at least 6 steps) for adding a new metric end-to-end, from SQL view ALTER through to validator registration

All code blocks specify the correct language tag (sql, dart, bash) for syntax highlighting

Document passes a Markdown lint check with no errors

A senior developer unfamiliar with the stats layer can follow the guide to add a new metric without asking clarifying questions

Technical Requirements

frameworks

Markdown

Mermaid (diagram syntax)

apis

Supabase SQL editor / migrations

Bufdir Alignment Validator CLI

data models

StatsView (SQL)

StatsSnapshot (Dart)

BufdirAlignmentReport

performance requirements

Document must load and render in standard Markdown viewers (GitHub, VS Code) without plugins beyond Mermaid

security requirements

No real Supabase URLs, anon keys, or service-role keys in the document — use placeholder values

RLS diagram must accurately reflect the least-privilege principle: peer mentors see only own rows

Execution Context

Execution Tier

Tier 6

Tier 6 - 158 tasks

Can start after Tier 5 completes

View Full Execution Plan

Implementation Notes

Use Mermaid `graph LR` or `erDiagram` for the RLS diagram — erDiagram is well-supported on GitHub and clearly shows table relationships. For the Bufdir field mapping, cross-reference the Bufdir reporting specification document (Word/Excel shared by the organizations) to ensure field names match exactly — this is a compliance document, not just developer notes. In Section 5, include the command to regenerate Dart freezed/json_serializable code after model changes, since developers often forget this step. Keep the document under 600 lines to stay maintainable; link to external migration files rather than inlining large SQL blocks.

Testing Requirements

Documentation review checklist: (1) Have a developer who did not write the stats layer attempt to add a dummy metric following Section 5 only — record any blocking questions. (2) Verify all SQL snippets execute without syntax errors against a local Supabase instance. (3) Run markdownlint with default rules. (4) Verify Mermaid diagram renders correctly on GitHub.

No automated test suite required, but the walkthrough must be validated by at least one peer review.

Component

Supabase Stats Database Views

infrastructure high

Dependencies (3)

Build the Bufdir Alignment Validator service that compares aggregated totals from the stats views against an independent SQL query that replicates the Bufdir export pipeline logic. The validator runs as a background check and emits a BufdirAlignmentResult(delta, percentageDrift) object. If any numeric field drifts by more than 0.01 %, it logs a structured warning via the app analytics service. This validator is the automated guardrail ensuring zero reconciliation delta between the dashboard and Bufdir export. epic-activity-statistics-dashboard-data-foundation-task-012 Write integration tests that run against a Supabase local dev instance (supabase start) and verify: fetchSnapshot returns correct totals for a seeded dataset; RLS prevents cross-chapter access when authenticated as a restricted coordinator; date-range filters correctly exclude out-of-window rows; and empty results are handled without exceptions. Seed SQL scripts must insert activities across two chapters and two peer mentors to validate scoping. epic-activity-statistics-dashboard-data-foundation-task-011 Build the Stats Repository Dart class that queries the Supabase materialized views. It must expose: fetchSnapshot(TimeWindow, chapterIds) → StatsSnapshot, fetchPeerMentorRows(TimeWindow, chapterId) → List<PeerMentorStatRow>, and fetchChartPoints(TimeWindow, chapterId, granularity) → List<ChartDataPoint>. All methods accept a DateTimeRange (from TimeWindowService) and a List<String> chapterIds (from ChapterScopeResolver). Use Supabase postgrest-dart client with .gte/.lte date filters. Return typed models from task-001. epic-activity-statistics-dashboard-data-foundation-task-008

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