05 — Data Model & Event Spec

Audience: Data Lead, Engineering Use: Source of truth for Supabase schema feeding the client dashboard (§04) and SROI rollup (§07).

Tables (logical model)

Naming: snake_case. Every table has id uuid pk default gen_random_uuid(), created_at timestamptz default now(), updated_at timestamptz.

Core

client_orgs

• name text not null
• account_mgr_id uuid references staff(id)
• sector text
• headcount int

cohorts

• client_org_id uuid references client_orgs(id) on delete cascade
• name text not null (e.g. "Q1 2026 — Frontline Managers")
• curriculum_key text not null (e.g. sprint-1-manager-mental-health)
• start_date date not null
• state text not null check (state in ('pre-engagement','live','wrapping','archived'))
• expected_participants int

participants

• cohort_id uuid references cohorts(id) on delete cascade
• pseudonym text not null (NEVER store real name; PII lives in client HR system)
• external_ref text (opaque ID from client HRIS for join, hashed)
• nudge_channel text check (nudge_channel in ('slack','teams','email','sms','calendar'))
• coach_id uuid references coaches(id)

coaches

• pseudonym text not null
• lived_experience_tags text[]
• sector_tags text[]
• capacity_per_week int

Delivery events

workshop_attendance

• cohort_id uuid
• participant_id uuid
• workshop_date date
• attended boolean not null

coaching_sessions

• participant_id uuid
• coach_id uuid
• scheduled_at timestamptz
• delivered boolean not null default false
• delivered_at timestamptz
• duration_min int
• note_id uuid references coaching_notes(id)

coaching_notes (RLS: coach-only + admin)

• participant_id uuid
• coach_id uuid
• note_body text (encrypted at rest)
• share_anonymised boolean default false
• participant_consent boolean default false
• anonymised_quote text (curator-edited, ≤30 words)

nudges_sent

• participant_id uuid
• sent_at timestamptz
• channel text
• template_key text (e.g. s1-nudge-04-conversation-prompt)
• engaged_at timestamptz (null if no open/click)

Measurement

pulse_responses

• participant_id uuid
• pulse_key text (e.g. s1-day30, s4-confidence-quarterly)
• submitted_at timestamptz
• responses jsonb (per-question answers; question keys from pulse instrument)
• anonymised boolean default true

sroi_rollups (read-only output)

• cohort_id uuid
• snapshot_date date
• row1_ratio numeric (e.g. 4.20 → £1 returns £4.20)
• top_drivers text[]
• calculation_jsonb jsonb (full working for audit)

Access control

client_org_members — maps client users to orgs for dashboard auth.

• client_org_id uuid
• auth_user_id uuid references auth.users(id)
• role text check (role in ('hr_sponsor','exec_sponsor'))

user_roles — staff roles only (per project memory + user-roles knowledge).

• user_id uuid references auth.users(id)
• role app_role (enum: admin, staff, coach)

has_role(user_id, role) SECURITY DEFINER function used in every staff-scoped RLS policy.

Event spec (write paths)

RLS principles

• Coaches see only their own participants' rows.
• HR sponsors see only their org's cohorts, and only aggregated views (no row-level access to coaching_notes, pulse_responses individual rows — only via aggregated views with n ≥ 5 filter).
• Exec sponsors see only Row 1 + Row 2 aggregates.
• Staff (has_role(auth.uid(), 'staff')) bypass for ops.
• Admin (has_role(auth.uid(), 'admin')) full read; writes still go through server functions.

Aggregated views (the only way client roles read sensitive tables)

• v_cohort_engagement — counts and percentages from attendance/sessions/nudges, joined by cohort.
• v_cohort_sentiment_deltas — mean confidence deltas, with row-level filter rejecting cohorts where pulse n<5.
• v_cohort_stories — coaching_notes filtered to share_anonymised AND participant_consent rows only, exposing anonymised_quote only.

Anonymisation enforcement

Three layers, all required:

1. Schema: no PII columns on participants or pulse_responses.
2. RLS: client roles cannot query base tables — only views.
3. View logic: small-n suppression baked into view SQL with having count(*) >= 5.

If any layer is removed, the next layer still holds. This is non-negotiable.

Migration plan (when build starts)

Phase the migration:

1. Core (client_orgs, cohorts, participants, coaches, user_roles, client_org_members) + RLS.
2. Delivery events tables + write paths from coach tooling.
3. Measurement tables + pulse form server-fn writes.
4. Aggregated views + dashboard server-fn reads.
5. SROI rollup job.

Do not start with measurement before core RLS is in place.