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

Define Supabase schema for way-forward items

epic-structured-post-session-report-foundation-task-002 — Create the Supabase database migration for the way_forward_items table including columns for item_id, report_id (foreign key to post_session_reports), assigned_to, description, due_date, status, created_at. Add appropriate RLS policies for coordinator and peer mentor access. Ensure referential integrity with the reports table.

critical priority low complexity database pending database specialist Tier 1

Acceptance Criteria

Migration file creates way_forward_items table with columns: item_id (UUID primary key, default gen_random_uuid()), report_id (UUID not null, foreign key to post_session_reports.report_id on delete cascade), assigned_to (UUID nullable, foreign key to auth.users), description (text not null), due_date (date nullable), status (text not null, CHECK in ('open','in_progress','completed','cancelled'), default 'open'), created_at (timestamptz not null, default now())
Migration includes index on report_id for efficient lookup of items by report
Migration includes index on assigned_to for efficient lookup of items assigned to a user
RLS is enabled on way_forward_items table
RLS SELECT policy allows coordinator to read all items belonging to reports in their organisation
RLS SELECT policy allows peer mentor to read only items where assigned_to = auth.uid() or report.created_by = auth.uid()
RLS INSERT policy allows peer mentor to insert items only on reports they own (report.created_by = auth.uid())
RLS INSERT policy allows coordinator to insert items on any report within their organisation
RLS UPDATE policy allows peer mentor to update items they own or are assigned to
RLS UPDATE policy allows coordinator to update any item within their organisation
RLS DELETE policy allows coordinator to delete items within their organisation; peer mentor cannot delete
Foreign key constraint ensures that deleting a post_session_report cascades to delete its way_forward_items
Migration is idempotent (uses IF NOT EXISTS guards or is safely re-runnable)
Migration rollback script drops the table and all dependent policies cleanly

Technical Requirements

frameworks
Supabase (PostgreSQL migrations)
apis
Supabase REST API (PostgREST)
Supabase Auth (auth.uid())
data models
way_forward_items
post_session_reports
auth.users
performance requirements
Index on report_id to ensure O(log n) lookups when fetching items for a report
Index on assigned_to to support peer mentor filtered queries
Table designed for expected volume of ~10–50 items per report
security requirements
RLS must be enabled before any policies are applied
Coordinator access scoped to own organisation via join to org membership table
Peer mentor access scoped strictly to own reports and own assignments
No policy may grant access across organisation boundaries
assigned_to must reference auth.users to prevent orphaned assignments

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Use Supabase CLI migration workflow: `supabase migration new way_forward_items`. The coordinator org-scope RLS policy requires a join — use a subquery against the organisation_members table (or equivalent) to identify the coordinator's org_id and restrict access accordingly. Avoid using security definer functions unless necessary; prefer straightforward RLS joins for auditability. The status CHECK constraint values should be agreed with the product team before migration is applied to avoid a later schema change.

Ensure the migration references the exact table name used in task-001 for post_session_reports — do not assume the name; verify from the prior migration file.

Testing Requirements

Write SQL-based integration tests using Supabase's local dev environment (supabase start). Test each RLS policy: (1) coordinator can SELECT/INSERT/UPDATE items in own org, (2) coordinator cannot access items in another org, (3) peer mentor can SELECT own items, (4) peer mentor cannot SELECT items not assigned to them or not on their report, (5) peer mentor cannot DELETE any item, (6) cascade delete removes items when parent report is deleted. Verify status CHECK constraint rejects invalid values. Verify NOT NULL constraints reject missing description and report_id.

Run migration rollback and confirm clean teardown.

Component
Way Forward Item Repository
data low
Dependencies (1)
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. epic-structured-post-session-report-foundation-task-001
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