🏠 Overview › ✅ Implementation Tasks ›✅ epic-annual-impact-summary-foundation-task-001

Create Supabase annual_summaries table migration

epic-annual-impact-summary-foundation-task-001 — Write and apply a Supabase database migration that creates the annual_summaries table with columns for peer_mentor_id, year, period_start, period_end, computed_data (JSONB), status, created_at, updated_at, and appropriate RLS policies ensuring users can only read their own summaries.

critical priority medium complexity database pending database specialist Tier 0

Acceptance Criteria

Migration file exists at supabase/migrations/<timestamp>_create_annual_summaries.sql and applies cleanly with `supabase db push`
Table annual_summaries exists with columns: id (uuid, primary key, default gen_random_uuid()), peer_mentor_id (uuid, not null, FK → auth.users.id), year (smallint, not null), period_start (date, not null), period_end (date, not null), computed_data (jsonb, not null, default '{}'), status (text, not null, default 'pending'), created_at (timestamptz, not null, default now()), updated_at (timestamptz, not null, default now())
A CHECK constraint on status accepts only values: 'pending', 'computing', 'ready', 'error'
A UNIQUE constraint on (peer_mentor_id, year, period_start) prevents duplicate summaries for the same mentor and period
RLS is enabled on the table
RLS SELECT policy: authenticated users can only select rows where peer_mentor_id = auth.uid()
RLS INSERT policy: authenticated users can only insert rows where peer_mentor_id = auth.uid()
RLS UPDATE policy: authenticated users can only update rows where peer_mentor_id = auth.uid(); service role can update any row (for background computation jobs)
RLS DELETE policy: no user-level delete permitted; service role only
An index on (peer_mentor_id, year) exists for efficient per-user queries
A trigger or default ensures updated_at is set to now() on every UPDATE
Migration is idempotent: re-running it does not error (use IF NOT EXISTS where appropriate)

Technical Requirements

frameworks
Supabase
apis
Supabase Migrations CLI
PostgreSQL RLS
data models
AnnualSummary
AnnualSummaryStatus
performance requirements
Index on (peer_mentor_id, year) ensures queries for a user's annual summaries complete in O(log n) time
computed_data JSONB column should have a GIN index if full-text or key-based search is planned in future
security requirements
RLS must be enabled before any policy is created — never disable RLS on this table
Service role access is restricted to UPDATE and DELETE only — not exposed to client SDK
peer_mentor_id must reference auth.users (not a custom users table) to ensure Supabase auth integration
No PII should be stored directly in computed_data — only aggregated/anonymised statistics

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Use `supabase migration new create_annual_summaries` to generate the timestamped file. The updated_at trigger can reuse an existing moddatetime extension trigger if already present in the project, or define a new one: `CREATE TRIGGER set_updated_at BEFORE UPDATE ON annual_summaries FOR EACH ROW EXECUTE FUNCTION moddatetime(updated_at)`. For the service role UPDATE policy, use `USING (true)` with `WITH CHECK (true)` and `TO service_role`. Confirm the auth.users FK is ON DELETE CASCADE so that deleting a user also removes their summaries.

The JSONB column computed_data should be documented with a JSON schema comment or a separate schema file — this is the data contract for the Dart domain model in task-002.

Testing Requirements

Write Supabase local development tests using the Supabase CLI and pgTAP or manual SQL assertions: (1) Insert a row with a valid peer_mentor_id matching the current auth.uid() — expect success. (2) Insert a row with a different peer_mentor_id — expect RLS rejection (error code 42501). (3) Select rows as user A — expect only user A's rows returned even if user B's rows exist. (4) Attempt to insert a duplicate (peer_mentor_id, year, period_start) — expect unique constraint violation.

(5) Insert a row with status = 'invalid' — expect CHECK constraint violation. (6) Verify updated_at changes after an UPDATE. Run migrations in a local Supabase instance before deploying to staging.

Component
Annual Summary Repository
data medium
Epic Risks (3)
medium impact medium prob dependency

Rive animation files may not be available at implementation time, blocking the wrapped-animation-controller from being fully tested. If asset delivery is delayed, the controller cannot be validated for memory-leak-free disposal.

Mitigation & Contingency

Mitigation: Implement the animation controller with stub/placeholder AnimationController instances first so the lifecycle and disposal logic can be unit-tested independently of Rive assets. Define a named animation registry interface early so UI components can reference animations by name without coupling to specific Rive files.

Contingency: If Rive assets are not delivered before Epic 3 begins, replace Rive animations with Flutter implicit animations (AnimatedOpacity, ScaleTransition) as a drop-in and schedule Rive integration as a follow-on task once assets arrive.

high impact medium prob technical

The annual_summaries Supabase RPC aggregating 12 months of activity records per mentor may exceed acceptable query latency (>2s) for mentors with high activity volumes such as the HLF mentor with 380 registrations cited in workshop notes.

Mitigation & Contingency

Mitigation: Design the RPC to materialise summary results into the annual_summaries table via a scheduled edge function rather than computing on demand. The repository reads pre-computed rows, keeping query latency constant regardless of activity volume.

Contingency: If on-demand queries are required for real-time period switching, add a PostgreSQL partial index on (mentor_id, activity_date) and implement a client-side loading skeleton so slow queries degrade gracefully rather than blocking the UI.

medium impact low prob technical

iOS requires photo library permission before saving a screenshot to the gallery. If the permission prompt is triggered at an unexpected point in the share flow, the UX breaks and users may deny permission permanently, making gallery save unavailable.

Mitigation & Contingency

Mitigation: Trigger the permission request only when the user explicitly chooses 'Save to gallery' in the share overlay, not on screen load. Implement a pre-prompt explanation screen following Apple HIG so users understand why the permission is needed before the system dialog appears.

Contingency: If permission is denied, gracefully fall back to clipboard copy and system share sheet options which do not require photo library access, and surface a non-blocking snackbar explaining the limitation.

Quick Links
All Tasks Execution Plan