Add org_field_configs Supabase table and seed data

epic-structured-post-session-report-foundation-task-006 — Create the database migration for the org_field_configs table with columns: config_id, org_id, feature_key (e.g. 'post_session_report'), config_jsonb, version, updated_at. Add RLS policies so only org admins can write and org members can read. Seed baseline field config records for each organisation covering the structured post-session report fields (health_status, course_interest, assistive_device_situation, way_forward).

critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

Migration creates org_field_configs table with columns: config_id (UUID primary key, default gen_random_uuid()), org_id (UUID not null, foreign key to organisations table), feature_key (text not null), config_jsonb (jsonb not null), version (integer not null default 1), updated_at (timestamptz not null default now())

Unique constraint on (org_id, feature_key) to prevent duplicate configs per feature per org

RLS is enabled on org_field_configs

RLS SELECT policy: any authenticated user who is a member of the org (org_id matches user's org membership) can read

RLS INSERT policy: only users with role 'org_admin' or 'coordinator' within the org can insert

RLS UPDATE policy: only users with role 'org_admin' within the org can update; version is auto-incremented on update via a trigger

RLS DELETE policy: only users with role 'org_admin' can delete

updated_at is updated automatically on row update via a trigger (or explicit ON UPDATE DEFAULT is not available in Postgres — use a trigger)

Seed data inserts one row per organisation per feature_key='post_session_report' with config_jsonb containing an array of field config objects covering: health_status (select, required, visible, options: [good, average, poor]), course_interest (boolean, visible), assistive_device_situation (text, visible), way_forward (text, required, visible)

Each seed row's config_jsonb validates against a documented JSON schema (inline comment or separate schema file)

Seed data is inserted using INSERT ... ON CONFLICT (org_id, feature_key) DO NOTHING so re-running migration is safe

Migration rollback drops the table, triggers, and policies cleanly

Seed script is separate from the structural migration (two separate migration files) to allow resetting seed data independently

Technical Requirements

frameworks

Supabase (PostgreSQL migrations, Supabase CLI)

apis

Supabase REST API (PostgREST)

data models

org_field_configs

organisations

FieldConfig (JSON schema shape)

performance requirements

Unique index on (org_id, feature_key) serves both constraint enforcement and fast point-lookups by the OrgFieldConfigLoader

config_jsonb column should have a GIN index if full-text or containment queries on the JSONB are anticipated; omit if only full-document reads are needed

security requirements

RLS must prevent cross-org config access — an org member must never read another org's field config

config_jsonb should not contain user PII — field configs are structural definitions only

Org admin write access must be verified via role check in RLS, not application-layer logic

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Use two migration files: (1) structural migration for the table, indexes, triggers, and RLS; (2) seed migration for INSERT statements. This separation is important because seed data may need to be reset or updated independently of the schema. The updated_at trigger can reuse a shared trigger function (e.g., set_updated_at()) if one already exists in the codebase — check before creating a duplicate. The config_jsonb seed values should match exactly the shape expected by OrgFieldConfigLoader's JSONB parser (task-005) — coordinate with the task-005 implementor to agree on the JSON structure before seeding.

For health_status options, use lowercase English values ('good', 'average', 'poor') as the stored values with labels handled in the app's localisation layer. The organisations table foreign key column name must be verified against the actual organisations table schema from task-001.

Testing Requirements

SQL integration tests using local Supabase: (1) org member can SELECT config for own org, (2) org member cannot SELECT config for another org, (3) org_admin can INSERT new config, (4) non-admin org member cannot INSERT, (5) org_admin can UPDATE config and version increments, (6) updated_at trigger fires on UPDATE, (7) duplicate (org_id, feature_key) INSERT is rejected by unique constraint, (8) ON CONFLICT DO NOTHING in seed script is idempotent — running seed twice produces same row count. Validate that seed data config_jsonb is parseable and contains expected keys for all four report fields. Verify migration rollback leaves no residual tables or triggers.

Component

Organisation Field Config Loader

infrastructure medium

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