🏠 Overview › ✅ Implementation Tasks ›✅ epic-structured-post-session-report-foundation-task-001

Define Supabase schema for post-session reports

epic-structured-post-session-report-foundation-task-001 — Create the Supabase database migration for the post_session_reports table including columns for report_id, activity_id (foreign key), peer_mentor_id, org_id, status, field_values (JSONB), created_at, updated_at. Add RLS policies to restrict read/write access by org_id and role. Ensure activity_id foreign key constraint is enforced.

critical priority medium complexity database pending database specialist Tier 0

Acceptance Criteria

A versioned SQL migration file exists and applies cleanly to a fresh Supabase instance without errors
post_session_reports table is created with columns: report_id (UUID, primary key, default gen_random_uuid()), activity_id (UUID, not null, foreign key → activities.activity_id on delete cascade), peer_mentor_id (UUID, not null, foreign key → users.user_id), org_id (UUID, not null, foreign key → organizations.org_id), status (TEXT, not null, check constraint: 'draft' | 'submitted' | 'approved'), field_values (JSONB, not null, default '{}'), created_at (TIMESTAMPTZ, default now()), updated_at (TIMESTAMPTZ, default now())
An updated_at trigger function (moddatetime or equivalent) is applied to the table so updated_at auto-updates on every row modification
RLS is enabled on the table; no row is readable or writable without an explicit policy match
RLS SELECT policy: authenticated users can read reports where org_id = their JWT org_id claim AND (their role is coordinator OR admin OR their user_id = peer_mentor_id)
RLS INSERT policy: authenticated users can insert reports where org_id = their JWT org_id claim AND their user_id = peer_mentor_id AND status = 'draft'
RLS UPDATE policy: peer_mentor can update own draft reports (status ∈ {'draft','submitted'}); coordinator/admin can update any report in their org; no user can change org_id or peer_mentor_id via update
RLS DELETE policy: only admin role can delete reports within their org
activity_id foreign key constraint is verified: inserting a report with a non-existent activity_id fails with a constraint violation
An index exists on (org_id, status) for efficient coordinator dashboard queries
An index exists on (peer_mentor_id) for efficient per-peer-mentor report listing
Migration is idempotent: running it twice does not produce errors (use IF NOT EXISTS guards)

Technical Requirements

frameworks
Supabase (PostgreSQL migrations)
apis
Supabase Auth (JWT claims for org_id and role)
Supabase RLS policy engine
data models
post_session_reports
activities (referenced)
users (referenced)
organizations (referenced)
performance requirements
Composite index on (org_id, status) must be present for coordinator dashboard query patterns
Single-column index on peer_mentor_id for peer mentor self-listing
JSONB field_values should have a GIN index if full-text search within field values is expected in a later epic; add a comment noting this as a future optimization
security requirements
RLS must be enabled and enforced — service_role key bypasses RLS; ensure application client uses anon/authenticated key only
org_id on insert must be derived from the authenticated user's JWT claim, not from the client payload, to prevent cross-org injection
status check constraint prevents invalid state values at database level
field_values JSONB must not store access tokens, passwords, or BankID session data — enforce via application layer and document this constraint in migration comments
Audit trail: created_at and updated_at provide basic audit capability; a separate audit_log trigger can be added in a future migration

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Write the migration as a standard Supabase migration file in supabase/migrations/_create_post_session_reports.sql. Follow the existing migration naming convention in the project. For the org_id RLS enforcement pattern, use auth.jwt() -> 'org_id' to extract the claim — verify the JWT claim key matches what BankID/Vipps login populates. If org_id is nested in a custom claims object (e.g., app_metadata.org_id), use auth.jwt() -> 'app_metadata' ->> 'org_id'.

For the role-based RLS, check if the project uses a roles table join or a JWT claim — use whichever pattern is established in existing tables. Add a SQL comment on the field_values column documenting its expected JSON shape (fieldId: value pairs matching the report schema). The migration should also create a corresponding report_drafts table or reuse this table with status='draft' — confirm with the team which draft storage strategy is used (separate table vs. status column) before writing the migration, as this affects the repository implementation in subsequent tasks.

Testing Requirements

Database-level tests using Supabase local development environment (supabase start). Test suite: (1) migration applies cleanly on a fresh schema; (2) inserting a valid report succeeds; (3) inserting with a non-existent activity_id fails with FK violation; (4) inserting with an invalid status value (e.g., 'pending') fails with check constraint violation; (5) RLS: authenticated peer_mentor user can only read their own org's reports; (6) RLS: peer_mentor cannot read another org's reports even with a valid report_id; (7) RLS: coordinator can read all reports within their org; (8) RLS: no unauthenticated request can read any report; (9) updated_at is automatically updated on row modification. Use pgTAP or Supabase's built-in SQL test runner for database-level tests. Flutter integration tests should also mock the Supabase client for above-database tests.

Component
Post-Session Report Repository
data medium
Epic Risks (3)
high impact medium prob security

Supabase RLS policies for multi-org report access may be more complex than anticipated — coordinators need cross-peer-mentor access within their org but not across orgs, and draft reports should be invisible to coordinators until submitted. Misconfigured RLS could expose sensitive health data or block legitimate access.

Mitigation & Contingency

Mitigation: Define and test RLS policies in isolation before writing repository code. Create a dedicated SQL migration file with policy definitions and an automated integration test suite that verifies each role's access boundaries using real Supabase auth tokens.

Contingency: If RLS proves too complex to express declaratively, implement application-level access control in the repository layer with explicit org and role checks, and add a security audit task before the feature goes to production.

high impact medium prob integration

The org field config JSON stored in Supabase may lack a stable, versioned schema contract. If different organisations have drifted to different field-definition formats, org-field-config-loader will fail silently or crash, breaking form rendering for those orgs.

Mitigation & Contingency

Mitigation: Define a canonical JSON Schema for field config and validate all existing org configs against it before implementation begins. Store a schema version field in every config record and handle version migrations explicitly in the loader.

Contingency: If existing configs are too heterogeneous, implement a config normalisation pass in org-field-config-loader that coerces known variants to the canonical format, logging warnings for fields that cannot be normalised so operations can fix them in the admin console.

medium impact low prob technical

TTL-based schema cache invalidation may cause peer mentors to use stale field definitions for up to the TTL window after an admin updates the org config, potentially collecting data against outdated field structures.

Mitigation & Contingency

Mitigation: Set a conservative TTL (e.g. 15 minutes) and expose a manual cache-bust mechanism triggered on app foreground-resume. Document the maximum staleness window in the admin console so org admins know to plan config changes outside active reporting windows.

Contingency: If stale schema causes a data quality incident, add a Supabase Realtime subscription to the org config table that invalidates the cache immediately on any config update.

Quick Links
All Tasks Execution Plan