Coach Onboarding — Data Model & State Machine Spec

---

1. What this document is

The brief sets the experience and the sketches set the visual + interaction design. This document translates both into concrete database schema, state machines, routes, and access control. Everything here is implementation-ready and must hold to the locked identity model in apps/nextjs-apps/slainte-app-browser/.cursor/rules/identity-model.mdc and the RBAC model in apps/nextjs-apps/slainte-app-browser/.cursor/rules/rbac-and-roles.mdc.

This spec is the source of truth for the schema and state machines. If the brief or the sketches imply something this spec does not cover, the spec is wrong — update the spec before implementing.

This spec does not cover:

• —The full active coach workspace product (Home/Athletes/Calendar/Messages/Sessions/Earnings as standalone products). Sketch 5 indicates the shape but not the full surface area; that is a downstream project.
• —Celtic-knot badge ornament work or category-specific badge visuals beyond the foundation badge.
• —The public-website badge displays.
• —Document expiry automation (the schema supports it; the cron job is deferred).
• —Signed PDF generation. Deliberately deferred to a dedicated PDF-infrastructure sprint between Pass 1 and Pass 2 of this work. See §9.2 and §12.
• —staff_profiles table. The role-vs-profile model below permits the ops side to be gated by role alone for now; a future staff_profiles aspect is its own project once Sláinte has WPS-compliant employed staff to model. Out of scope here.
• —Engage in-portal notifications. Net-new infrastructure; not in Pass 1. Induction feedback uses email only in Pass 1. See §9.4.

These are explicitly flagged at the end of the spec as Future Work.

---

2. Where this fits in the identity model

2.1 Aspect placement

A coach is a contact with a coach aspect. The aspect is a Layer 3 sibling profile table named coach_profiles, with id FK to contact_profiles.id ON DELETE CASCADE, following the locked convention from identity-model.mdc and the shape used by athlete_profiles, billing_profiles, sponsor_profiles, vendor_profiles, and affiliate_profiles.

Presence of the coach_profiles row is the signal that the contact is being onboarded as, or currently is, a Sláinte coach. The row is created the moment ops invites the coach in (intake step), and persists across the full lifecycle including any future offboarding (the row is archived, not deleted).

The coach aspect lives alongside any other aspect rows the contact may have. A contact who is both a coach and an athlete has both a coach_profiles row and an athlete_profiles row. The two aspects are read independently.

No new contact_type enum value: A coach is not in the contact_type enum. The hard rule from identity-model.mdc is that the enum is for pure role labels with no attached data. Coaching has substantial attached data (it gets its own Layer 3 profile), so it does not go in the enum. Reads for "is this person a coach" check for the presence of a coach_profiles row, never the role tag. This matches the pattern for athlete_profiles.

2.2 Roles vs profiles — the access model

This spec follows Sláinte's locked principle that roles grant shell access; profiles grant feature activation inside the shell.

auth_accounts.role answers what room the user is allowed into. There are seven slugs catalogued in rbac-and-roles.mdc and src/lib/auth/roles.ts. No new role slug is introduced for coaches.

Aspect profiles answer what is activated inside that room. A coach who has been invited gets auth_accounts.role = 'slainte_standard' plus a coach_profiles row. The role gives them Engage shell access; the profile activates the coach section of the shell. Without the profile, the coach UI does not render.

This principle has three direct consequences for this spec:

1. —

   Coach-facing routes (/engage/coach/*) are gated by role AND profile presence. The server layout calls requireRole(['slainte_standard']) and additionally calls a new requireCoachProfile() guard that checks for a coach_profiles row tied to the authenticated user's contact. Missing profile → redirect to a "not enrolled" state (per role's getDefaultLandingPath).

2. —

   Ops-facing routes (/engage/staff/coaches/*) are gated by role alone — specifically slainte_admin. The four core team members (Murray, Niall, Michelle, Eben) hold slainte_admin. Other staff with slainte_standard do not access the coach dossier in Pass 1; if that scope ever broadens, it should broaden via role, not by introducing staff_profiles.

3. —

   No role slug. Tempting but wrong: a coach who later picks up an athlete plan or starts referring others or buys a programme shouldn't need to switch roles or accounts. The composition of capabilities is what aspect profiles are for. The seven existing slugs are stable.

2.3 Auth user and contact are created together, always

Migration 083_lock_contact_auth_user_not_null.sql makes contact_profiles.auth_user_id NOT NULL. This is the platform-wide rule: every contact has an auth user, every auth user has a contact. The two are created in the same transaction, never one without the other.

For coach onboarding, this means the ops "Create coach" action creates the auth user AND the contact AND the coach_profile in one atomic operation. The portal invite that follows is the coach's first sign-in, not their first existence. The mechanics are detailed in §7.

This is a change from the first draft of this spec (which proposed creating the contact first and inviting the user later). The discovery report flagged this against migration 083; the rewrite uses resolveIdentity per docs/identity-model-design-2026-05.md.

---

3. Schema additions

All new tables. Migration numbering is conceptual in this document — actual numbers must be assigned from the live state via mcp__supabase__list_migrations before authoring each migration file (see §11 and the MCP migration sync gotcha documented in the team's working memory).

All tables use created_at and updated_at timestamptz columns with DEFAULT now() and the standard set_updated_at_<tablename> trigger calling public.update_updated_at(). All have RLS enabled with policies described in §6 and in the per-table DDL below.

3.1 coach_profiles — the aspect row

The foundation table. One row per coach.

CREATE TABLE public.coach_profiles (
  id                              uuid PRIMARY KEY REFERENCES public.contact_profiles(id) ON DELETE CASCADE,

  -- Identity (public-facing)
  display_name                    text,                                       -- coach's preferred public name; falls back to first_name + last_name if null
  slug                            text UNIQUE,                                -- public profile slug, e.g. "yusuf-al-hashimi"; generated at coach_profile creation from display_name (or first_name + last_name); collisions resolved with -2, -3, ... suffix; immutable once set
  headline                        text,                                       -- single-line public headline ("Masters triathlon, with patience as the method.")
  bio                             text,                                       -- long-form public bio, drafted by ops, editable by coach after activation
  region                          text,                                       -- "Abu Dhabi", "Saudi Arabia", "Online", etc. Drives intake context
  languages                       text[] NOT NULL DEFAULT '{}',

  -- Engagement model
  engagement_type                 text CHECK (engagement_type IN ('independent_freelance','employee')) DEFAULT 'independent_freelance',
  initial_term_start              date,
  initial_term_months             integer DEFAULT 12,
  capacity_athletes_max           integer,
  capacity_hours_per_week         integer,

  -- Commercial (illustrative — full commercial schema TBD with finance work)
  per_session_rate_aed            numeric(10,2),
  programme_revenue_share_pct     numeric(5,2),
  settlement_cadence              text DEFAULT 'monthly' CHECK (settlement_cadence IN ('monthly','per_session','quarterly')),

  -- Onboarding state (see §4 for state machine)
  onboarding_state                text NOT NULL DEFAULT 'invited'
                                    CHECK (onboarding_state IN (
                                      'invited',
                                      'documents_in_progress',
                                      'documents_in_review',
                                      'verification_in_progress',
                                      'package_in_preparation',
                                      'package_sent',
                                      'package_signed',
                                      'induction_in_progress',
                                      'awaiting_activation',
                                      'active',
                                      'suspended',
                                      'offboarded'
                                    )),
  onboarding_substage             text,                                       -- free-text ops note, optional
  invited_at                      timestamptz,                                -- when the portal invite was issued
  activated_at                    timestamptz,                                -- when ops flipped to active
  suspended_at                    timestamptz,                                -- when most recently suspended (null if not currently suspended)
  offboarded_at                   timestamptz,

  -- Discovery / intake references (all FK to contact_profiles.id)
  scouted_by                      uuid REFERENCES public.contact_profiles(id) ON DELETE SET NULL,
  point_of_contact_id             uuid REFERENCES public.contact_profiles(id) ON DELETE SET NULL,
  lead_programme_director_id      uuid REFERENCES public.contact_profiles(id) ON DELETE SET NULL,
  compliance_reviewer_id          uuid REFERENCES public.contact_profiles(id) ON DELETE SET NULL,

  -- Reference number (for the dossier head, signing book, activation moment)
  reference_number                text UNIQUE NOT NULL,                       -- e.g. "SC-2026-04812"; format defined in §3.1.1

  -- Public visibility
  is_publicly_visible             boolean NOT NULL DEFAULT false,             -- gates the /coaches/[slug] public page
  publicly_visible_since          timestamptz,

  last_coach_activity_at          timestamptz,                                -- used by the dossier "last activity" indicator

  created_at                      timestamptz NOT NULL DEFAULT now(),
  updated_at                      timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE public.coach_profiles IS
  'Layer 3 profile: coaching identity. Row presence indicates the contact is being onboarded as or is currently a Sláinte coach. See identity-model.mdc and project-context/coach-onboarding-spec.md.';

CREATE INDEX idx_coach_profiles_state ON public.coach_profiles (onboarding_state);
CREATE INDEX idx_coach_profiles_slug ON public.coach_profiles (slug) WHERE slug IS NOT NULL;
CREATE INDEX idx_coach_profiles_publicly_visible ON public.coach_profiles (is_publicly_visible) WHERE is_publicly_visible = true;

-- updated_at trigger (mirrors the billing_profiles convention from migration 064)
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_trigger WHERE tgname = 'set_updated_at_coach_profiles'
  ) THEN
    CREATE TRIGGER set_updated_at_coach_profiles
      BEFORE UPDATE ON public.coach_profiles
      FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
  END IF;
END $$;

-- RLS (mirrors the billing_profiles convention from migration 064)
ALTER TABLE public.coach_profiles ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "staff_full_coach_profiles" ON public.coach_profiles;
CREATE POLICY "staff_full_coach_profiles" ON public.coach_profiles
  FOR ALL USING (public.is_platform_staff()) WITH CHECK (public.is_platform_staff());

DROP POLICY IF EXISTS "owner_read_own_coach_profile" ON public.coach_profiles;
CREATE POLICY "owner_read_own_coach_profile" ON public.coach_profiles
  FOR SELECT USING (public.owns_contact(id));

DROP POLICY IF EXISTS "owner_update_own_coach_profile" ON public.coach_profiles;
CREATE POLICY "owner_update_own_coach_profile" ON public.coach_profiles
  FOR UPDATE USING (public.owns_contact(id)) WITH CHECK (public.owns_contact(id));

Note that public.is_platform_staff() covers both slainte_standard and slainte_admin. The ops-side route gate (requireRole(['slainte_admin'])) is tighter than the RLS policy on purpose: RLS allows any staff to read for queries surfacing coach data across the platform (e.g. for any future cross-reference), while the dossier UI is admin-only. If we later want to enforce admin-only at the RLS layer, we can add a is_platform_admin() helper and narrow the policy.

3.1.1 Reference number format

SC-YYYY-NNNNN where SC is the prefix (Sláinte Coach), YYYY is the year of intake, NNNNN is a zero-padded sequence per year starting at 00001.

Implementation precedent: the studio booking reference (SL-NNNNNN via studio_booking_ref_seq in 041_studio_resources.sql) is the closest pattern. Use the same approach: lazy per-year Postgres sequences (e.g. coach_reference_seq_2026) created on demand, with a trigger on coach_profiles insert that resolves the current year and increments the right sequence. Or an equivalent: a coach_reference_sequences (year integer PRIMARY KEY, last_number integer NOT NULL DEFAULT 0) table updated atomically inside the insert transaction.

Cursor's call on which implementation; the format and uniqueness are non-negotiable. Document the chosen mechanism in the migration's comment block.

3.2 coach_intake_briefs — the discovery notes that drive personalisation

The intake brief is its own table. Reasons: it has structure, it has revision history (drafts edit before final), and its content gets piped into the welcome-package generation. JSON blob on coach_profiles would lose all of that.

CREATE TABLE public.coach_intake_briefs (
  id                              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  coach_id                        uuid NOT NULL REFERENCES public.coach_profiles(id) ON DELETE CASCADE,

  -- Structured fields
  scouted_by                      uuid REFERENCES public.contact_profiles(id) ON DELETE SET NULL,
  discovery_date                  date,
  discovery_location              text,
  gap_being_filled                text,                                       -- "Masters triathlon · return-from-injury programme depth"
  agreed_scope_summary            text,                                       -- "10 athletes max · 20 hrs/wk · AED 450/session" — denormalised display string

  -- Free-text editorial intents (drive downstream personalisation)
  why_this_coach                  text,
  editorial_intent_for_profile    text,
  first_week_intent               text,
  notes                           text,                                       -- additional free-text ops notes

  -- Versioning
  is_current                      boolean NOT NULL DEFAULT true,
  superseded_by_brief_id          uuid REFERENCES public.coach_intake_briefs(id) ON DELETE SET NULL,

  created_by                      uuid REFERENCES public.contact_profiles(id) ON DELETE SET NULL,
  created_at                      timestamptz NOT NULL DEFAULT now(),
  updated_at                      timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_coach_intake_briefs_one_current_per_coach
  ON public.coach_intake_briefs (coach_id) WHERE is_current = true;

CREATE INDEX idx_coach_intake_briefs_coach_id ON public.coach_intake_briefs (coach_id);

COMMENT ON TABLE public.coach_intake_briefs IS
  'The discovery brief that drives all coach onboarding personalisation. Versioned. One current row per coach. See coach-onboarding-spec.md §3.2.';

-- updated_at trigger + RLS (same pattern as §3.1)
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'set_updated_at_coach_intake_briefs') THEN
    CREATE TRIGGER set_updated_at_coach_intake_briefs
      BEFORE UPDATE ON public.coach_intake_briefs
      FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
  END IF;
END $$;

ALTER TABLE public.coach_intake_briefs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "staff_full_coach_intake_briefs" ON public.coach_intake_briefs
  FOR ALL USING (public.is_platform_staff()) WITH CHECK (public.is_platform_staff());
-- Coaches do not read their own intake briefs — these are ops-internal notes.

When ops edits a brief: insert a new row with is_current = true, set the previous current row to is_current = false and populate its superseded_by_brief_id. Implemented inside a single transaction.

3.3 coach_document_requirements — the schema-level definition of what docs a coach needs

This table holds the set of documents Sláinte might require of any coach. It is not per-coach; it is configuration.

CREATE TABLE public.coach_document_requirements (
  id                              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key                             text UNIQUE NOT NULL,                       -- 'emirates_id', 'abu_dhabi_freelance_licence', etc.
  name                            text NOT NULL,                              -- display name
  why                             text NOT NULL,                              -- editorial explanation in Sláinte voice
  acceptable_proof                text[] NOT NULL DEFAULT '{}',
  required_for_regions            text[] NOT NULL DEFAULT '{}',               -- ['AE'] for UAE-only; empty means universal
  is_active                       boolean NOT NULL DEFAULT true,
  sort_order                      integer NOT NULL DEFAULT 100,
  created_at                      timestamptz NOT NULL DEFAULT now(),
  updated_at                      timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE public.coach_document_requirements IS
  'Configuration: the set of documents Sláinte may require of any coach. Per-coach requirement set is computed at intake from this table + the coach region.';

ALTER TABLE public.coach_document_requirements ENABLE ROW LEVEL SECURITY;

CREATE POLICY "staff_full_coach_document_requirements" ON public.coach_document_requirements
  FOR ALL USING (public.is_platform_staff()) WITH CHECK (public.is_platform_staff());

CREATE POLICY "authenticated_read_coach_document_requirements" ON public.coach_document_requirements
  FOR SELECT USING (auth.uid() IS NOT NULL);
-- Configuration is readable by any authenticated user (coaches read it to render their doc checklist).

3.3.1 Initial seed for coach_document_requirements

INSERT INTO public.coach_document_requirements (key, name, why, acceptable_proof, required_for_regions, sort_order) VALUES
  ('emirates_id',
   'Emirates ID',
   'Confirms your identity and residency status with Sláinte and with the UAE entities we work alongside.',
   ARRAY['Front & back · PDF or JPG', 'Must be current'],
   ARRAY['AE'],
   10),
  ('abu_dhabi_freelance_licence',
   'Abu Dhabi freelancer licence',
   'Required for Sláinte to engage you as an independent coach in the Emirate. We will check the activity covers personal training and triathlon coaching.',
   ARRAY['ADRA-issued PDF', 'Activity list visible'],
   ARRAY['AE'],
   20),
  ('professional_indemnity_insurance',
   'Professional indemnity insurance',
   'Sláinte requires coverage of at least USD 1m for in-person coaching. If your current policy is in renewal, we can hold this step open while it processes.',
   ARRAY['Certificate · PDF', 'USD 1m minimum', '12+ months remaining'],
   ARRAY[]::text[],
   30),
  ('coaching_certification_triathlon',
   'Coaching certification — triathlon',
   'Your World Triathlon Level II cert, as discussed. Higher tiers welcome.',
   ARRAY['PDF or scan', 'Expiry visible'],
   ARRAY[]::text[],
   40),
  ('first_aid_cpr',
   'First aid & CPR',
   'Required for any coach working with athletes in person. Should be from a recognised provider (Red Cross, St John, or equivalent) and within its valid period.',
   ARRAY['Certificate · PDF', 'Recognised provider'],
   ARRAY[]::text[],
   50);

3.4 contact_documents and coach_documents — the document split

Architectural call: documents are attached to the contact, not the coach aspect. Reasoning: an Emirates ID is about the person, not about the coach aspect. The same document may be relevant to billing, athlete-aspect, or future aspects. But the requirement to provide it is per-aspect.

This split is modelled as: documents live on contact (contact_documents), and coach_documents is a join table indicating which contact documents fulfil which coach requirements.

The closest precedent in the codebase is company_compliance_documents from migration 017_company_profile_and_compliance.sql (metadata + storage_path + private bucket). contact_documents does not currently exist; this spec introduces it.

3.4.1 Storage bucket

A new private Supabase Storage bucket is required:

• —Bucket name: contact-documents
• —Access: private (signed-URL access only)
• —MIME types: PDF, JPG, JPEG, PNG, HEIC, DOC, DOCX (mirror the company-compliance-documents allow-list in src/lib/company-profile/actions.ts)
• —Max file size: 10MB per file (match the company-profile constant)
• —Path convention: {contact_id}/{document_id}.{ext}
• —Storage policies: Staff (is_platform_staff()) full access; coach (owns_contact()) read/write on their own contact's folder only

The bucket is created in the same migration as contact_documents (mirrors 006_storage_buckets.sql and 017_company_profile_and_compliance.sql patterns).

3.4.2 contact_documents table

CREATE TABLE public.contact_documents (
  id                              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  contact_id                      uuid NOT NULL REFERENCES public.contact_profiles(id) ON DELETE CASCADE,

  -- The document itself
  storage_path                    text NOT NULL,                              -- Supabase Storage path
  original_filename               text,
  mime_type                       text,
  file_size_bytes                 bigint,

  -- Provenance
  uploaded_by                     uuid REFERENCES public.contact_profiles(id) ON DELETE SET NULL,
  uploaded_at                     timestamptz NOT NULL DEFAULT now(),
  upload_source                   text CHECK (upload_source IN ('coach_self_upload','staff_upload','imported')) DEFAULT 'coach_self_upload',

  -- Document metadata
  document_kind                   text,                                       -- 'identity', 'licence', 'insurance', 'certification', 'first_aid', 'other'
  issuing_authority               text,
  document_reference              text,                                       -- e.g. licence number, cert ID
  valid_from                      date,
  expires_on                      date,                                       -- nullable: not all documents expire
  is_archived                     boolean NOT NULL DEFAULT false,
  archived_at                     timestamptz,
  archived_reason                 text,

  created_at                      timestamptz NOT NULL DEFAULT now(),
  updated_at                      timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE public.contact_documents IS
  'Documents attached to a contact (about the person). Aspect-specific requirements that this document fulfils are recorded in <aspect>_documents join tables (e.g. coach_documents).';

CREATE INDEX idx_contact_documents_contact_id ON public.contact_documents (contact_id);
CREATE INDEX idx_contact_documents_expires_on ON public.contact_documents (expires_on)
  WHERE expires_on IS NOT NULL AND is_archived = false;
CREATE INDEX idx_contact_documents_kind ON public.contact_documents (document_kind);

ALTER TABLE public.contact_documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY "staff_full_contact_documents" ON public.contact_documents
  FOR ALL USING (public.is_platform_staff()) WITH CHECK (public.is_platform_staff());

CREATE POLICY "owner_read_own_contact_documents" ON public.contact_documents
  FOR SELECT USING (public.owns_contact(contact_id));

CREATE POLICY "owner_insert_own_contact_documents" ON public.contact_documents
  FOR INSERT WITH CHECK (public.owns_contact(contact_id));

CREATE POLICY "owner_update_own_contact_documents" ON public.contact_documents
  FOR UPDATE USING (public.owns_contact(contact_id)) WITH CHECK (public.owns_contact(contact_id));

3.4.3 coach_documents table

CREATE TABLE public.coach_documents (
  id                              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  coach_id                        uuid NOT NULL REFERENCES public.coach_profiles(id) ON DELETE CASCADE,
  requirement_id                  uuid NOT NULL REFERENCES public.coach_document_requirements(id) ON DELETE RESTRICT,
  contact_document_id             uuid REFERENCES public.contact_documents(id) ON DELETE SET NULL,

  -- Verification state
  verification_state              text NOT NULL DEFAULT 'awaiting_upload'
                                    CHECK (verification_state IN (
                                      'awaiting_upload',
                                      'uploaded',
                                      'in_review',
                                      'verified',
                                      'rejected'
                                    )),
  verified_by                     uuid REFERENCES public.contact_profiles(id) ON DELETE SET NULL,
  verified_at                     timestamptz,
  rejection_reason                text,

  -- Ops-internal
  assigned_to                     uuid REFERENCES public.contact_profiles(id) ON DELETE SET NULL,
  review_queue_entered_at         timestamptz,

  created_at                      timestamptz NOT NULL DEFAULT now(),
  updated_at                      timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE public.coach_documents IS
  'Per-coach document requirements and their verification state. Each row represents one requirement for one coach. The actual document lives on contact_documents.';

CREATE INDEX idx_coach_documents_coach_id ON public.coach_documents (coach_id);
CREATE INDEX idx_coach_documents_state ON public.coach_documents (verification_state);
CREATE INDEX idx_coach_documents_assigned_to ON public.coach_documents (assigned_to) WHERE assigned_to IS NOT NULL;

ALTER TABLE public.coach_documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY "staff_full_coach_documents" ON public.coach_documents
  FOR ALL USING (public.is_platform_staff()) WITH CHECK (public.is_platform_staff());

CREATE POLICY "owner_read_own_coach_documents" ON public.coach_documents
  FOR SELECT USING (public.owns_contact(coach_id));

CREATE POLICY "owner_update_own_coach_documents" ON public.coach_documents
  FOR UPDATE USING (public.owns_contact(coach_id)) WITH CHECK (public.owns_contact(coach_id));
-- The "update" policy lets the coach link a fresh contact_document_id when re-uploading.
-- It does NOT let the coach change verification_state — that's enforced at the app layer
-- (any verification_state transition is a staff action; coach updates only set contact_document_id and clear rejection_reason).

Initialisation: When coach_profiles is created at intake, a row in coach_documents is inserted for each active requirement that matches the coach's region. All start in awaiting_upload. Implemented in the create_coach action (preferred) or as a deferred Postgres trigger (acceptable).

3.5 coach_packages — the welcome package state

The signing book of Sketch 2. One package per coach (in normal flow; amendments create new rows).

CREATE TABLE public.coach_packages (
  id                              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  coach_id                        uuid NOT NULL REFERENCES public.coach_profiles(id) ON DELETE CASCADE,

  -- Package lifecycle
  package_state                   text NOT NULL DEFAULT 'draft'
                                    CHECK (package_state IN (
                                      'draft',
                                      'in_review',
                                      'sent',
                                      'amendments_requested',
                                      'signed',
                                      'superseded'
                                    )),
  prepared_by                     uuid REFERENCES public.contact_profiles(id) ON DELETE SET NULL,
  reviewed_by                     uuid[] NOT NULL DEFAULT '{}',
  sent_at                         timestamptz,
  signed_at                       timestamptz,

  -- Package contents (immutable snapshot at the moment of signing)
  head_coach_letter               text,                                       -- Markdown
  agreed_terms_snapshot           jsonb,
  contract_template_version       text,
  contract_rendered_html          text,                                       -- THIS IS THE LEGAL RECORD IN PASS 1 (see §9.2)
  profile_snapshot                jsonb,
  first_week_schedule_snapshot    jsonb,

  -- Signed PDF reference (Pass 2 / dedicated PDF sprint; nullable until then)
  signed_pdf_storage_path         text,
  signed_pdf_generated_at         timestamptz,
  digital_signature_metadata      jsonb,                                      -- timestamps, IP, user agent — captured at sign event in Pass 1

  -- Amendments
  amendment_notes                 text,
  superseded_by_package_id        uuid REFERENCES public.coach_packages(id) ON DELETE SET NULL,

  created_at                      timestamptz NOT NULL DEFAULT now(),
  updated_at                      timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_coach_packages_one_active_per_coach
  ON public.coach_packages (coach_id)
  WHERE package_state NOT IN ('superseded');

CREATE INDEX idx_coach_packages_coach_id ON public.coach_packages (coach_id);
CREATE INDEX idx_coach_packages_state ON public.coach_packages (package_state);

COMMENT ON TABLE public.coach_packages IS
  'The welcome package (signing book). Snapshots are immutable once signed. In Pass 1 the contract_rendered_html column is the legal record; the signed_pdf_storage_path column populates in Pass 2 once PDF infrastructure exists.';

ALTER TABLE public.coach_packages ENABLE ROW LEVEL SECURITY;

CREATE POLICY "staff_full_coach_packages" ON public.coach_packages
  FOR ALL USING (public.is_platform_staff()) WITH CHECK (public.is_platform_staff());

CREATE POLICY "owner_read_own_coach_packages_when_visible" ON public.coach_packages
  FOR SELECT USING (
    public.owns_contact(coach_id)
    AND package_state IN ('sent', 'amendments_requested', 'signed')
  );

CREATE POLICY "owner_sign_own_coach_packages" ON public.coach_packages
  FOR UPDATE USING (
    public.owns_contact(coach_id)
    AND package_state = 'sent'
  ) WITH CHECK (
    public.owns_contact(coach_id)
    AND package_state IN ('sent', 'signed')
  );
-- Coaches see their package only once it's sent. They can transition sent → signed only.

The snapshot columns are deliberately denormalised JSON. Once signed, the package is a legal artefact. If the coach's profile bio changes a year later, the signed package must still show the bio as it was at signing. JSON snapshot guarantees that.

3.6 Induction modules and completions

CREATE TABLE public.induction_modules (
  id                              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key                             text UNIQUE NOT NULL,                       -- 'welcome_from_founders', 'athlete_progression', 'slainte_voice', 'safeguarding', 'first_ninety_days'
  title                           text NOT NULL,
  summary                         text NOT NULL,                              -- one-sentence summary on the index card
  body_markdown                   text NOT NULL,
  video_storage_path              text,                                       -- nullable
  video_duration_seconds          integer,
  video_transcript                text,
  estimated_read_minutes          integer NOT NULL DEFAULT 10,
  sort_order                      integer NOT NULL DEFAULT 100,
  is_active                       boolean NOT NULL DEFAULT true,
  last_revised_at                 date,
  created_at                      timestamptz NOT NULL DEFAULT now(),
  updated_at                      timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE public.induction_modules IS
  'Configuration: induction modules. Editorial content authored once by Team Sláinte. Reusable across coaches and (future) other roles.';

ALTER TABLE public.induction_modules ENABLE ROW LEVEL SECURITY;

CREATE POLICY "staff_full_induction_modules" ON public.induction_modules
  FOR ALL USING (public.is_platform_staff()) WITH CHECK (public.is_platform_staff());

CREATE POLICY "authenticated_read_induction_modules" ON public.induction_modules
  FOR SELECT USING (auth.uid() IS NOT NULL AND is_active = true);

CREATE TABLE public.coach_induction_completions (
  id                              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  coach_id                        uuid NOT NULL REFERENCES public.coach_profiles(id) ON DELETE CASCADE,
  module_id                       uuid NOT NULL REFERENCES public.induction_modules(id) ON DELETE RESTRICT,

  read_at                         timestamptz NOT NULL DEFAULT now(),
  feedback_note                   text,                                       -- optional feedback the coach left
  feedback_email_sent_at          timestamptz,                                -- when hello@slainteendurance.com received it (Pass 1)
  feedback_notification_routed_at timestamptz,                                -- when the Engage notification was created (Pass 2+; nullable until then)

  created_at                      timestamptz NOT NULL DEFAULT now(),
  updated_at                      timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_coach_induction_completions_unique
  ON public.coach_induction_completions (coach_id, module_id);

CREATE INDEX idx_coach_induction_completions_coach_id
  ON public.coach_induction_completions (coach_id);

ALTER TABLE public.coach_induction_completions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "staff_full_coach_induction_completions" ON public.coach_induction_completions
  FOR ALL USING (public.is_platform_staff()) WITH CHECK (public.is_platform_staff());

CREATE POLICY "owner_read_own_completions" ON public.coach_induction_completions
  FOR SELECT USING (public.owns_contact(coach_id));

CREATE POLICY "owner_insert_own_completions" ON public.coach_induction_completions
  FOR INSERT WITH CHECK (public.owns_contact(coach_id));

When a coach submits feedback (feedback_note IS NOT NULL), a server-side handler (Next.js server action or edge function — Cursor's call) sends an email to hello@slainteendurance.com via the existing transactional email pattern (_shared/slainte-email.ts + email_templates). Sets feedback_email_sent_at on success. The feedback_notification_routed_at column remains null in Pass 1 and is populated when Engage in-portal notifications land in a future pass.

3.7 Badge system

Forward-compatible from day one. Three categories per the locked taxonomy.

CREATE TYPE coach_badge_category AS ENUM (
  'credential',          -- Credentials and Certifications (Foundation, Advanced, Expert)
  'tenure',              -- Tenure and Experience (New, <5yrs, +5yrs)
  'character'            -- Character (values TBD)
);

CREATE TYPE coach_badge_tier AS ENUM (
  'foundation',
  'advanced',
  'expert',
  'new',
  'mid_tenure',
  'long_tenure',
  'unspecified'          -- placeholder for character badges until values defined
);

CREATE TABLE public.coach_badge_definitions (
  id                              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key                             text UNIQUE NOT NULL,
  name                            text NOT NULL,
  category                        coach_badge_category NOT NULL,
  tier                            coach_badge_tier NOT NULL,
  description                     text NOT NULL,
  criteria_description            text,                                       -- "How to earn this" placeholder
  display_initial                 text NOT NULL DEFAULT 'S',                  -- Playfair initial inside the circle
  display_color_token             text NOT NULL DEFAULT 'gold',
  is_active                       boolean NOT NULL DEFAULT true,
  sort_order                      integer NOT NULL DEFAULT 100,
  created_at                      timestamptz NOT NULL DEFAULT now(),
  updated_at                      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE public.coach_badge_awards (
  id                              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  coach_id                        uuid NOT NULL REFERENCES public.coach_profiles(id) ON DELETE CASCADE,
  badge_definition_id             uuid NOT NULL REFERENCES public.coach_badge_definitions(id) ON DELETE RESTRICT,
  awarded_at                      timestamptz NOT NULL DEFAULT now(),
  awarded_by                      uuid REFERENCES public.contact_profiles(id) ON DELETE SET NULL,
  award_reason                    text,
  revoked_at                      timestamptz,
  revoked_by                      uuid REFERENCES public.contact_profiles(id) ON DELETE SET NULL,
  revocation_reason               text,
  created_at                      timestamptz NOT NULL DEFAULT now(),
  updated_at                      timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_coach_badge_awards_one_active_per_coach_badge
  ON public.coach_badge_awards (coach_id, badge_definition_id)
  WHERE revoked_at IS NULL;

CREATE INDEX idx_coach_badge_awards_coach_id ON public.coach_badge_awards (coach_id);

ALTER TABLE public.coach_badge_definitions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.coach_badge_awards ENABLE ROW LEVEL SECURITY;

CREATE POLICY "staff_full_coach_badge_definitions" ON public.coach_badge_definitions
  FOR ALL USING (public.is_platform_staff()) WITH CHECK (public.is_platform_staff());
CREATE POLICY "authenticated_read_coach_badge_definitions" ON public.coach_badge_definitions
  FOR SELECT USING (auth.uid() IS NOT NULL AND is_active = true);

CREATE POLICY "staff_full_coach_badge_awards" ON public.coach_badge_awards
  FOR ALL USING (public.is_platform_staff()) WITH CHECK (public.is_platform_staff());
CREATE POLICY "owner_read_own_badge_awards" ON public.coach_badge_awards
  FOR SELECT USING (public.owns_contact(coach_id));

-- Seed: the foundation badge (the only one Pass 1 ships)
INSERT INTO public.coach_badge_definitions
  (key, name, category, tier, description, criteria_description, display_initial, display_color_token, sort_order)
VALUES
  ('slainte_certified',
   'Sláinte Certified',
   'credential',
   'foundation',
   'The foundation credential confirming the coach has been verified, vetted, and brought onto the Sláinte bench.',
   'Issued automatically on activation, once all onboarding requirements are met.',
   'S',
   'gold',
   10);

When coach_profiles.onboarding_state transitions to 'active', a row is inserted into coach_badge_awards for the slainte_certified badge. See §9.3.

3.8 coach_onboarding_events — the audit trail

Every meaningful state change writes a row here. Source for:

• —The dossier's activity feed (Sketch 3)
• —The dossier's Audit tab
• —Any future compliance review

CREATE TABLE public.coach_onboarding_events (
  id                              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  coach_id                        uuid NOT NULL REFERENCES public.coach_profiles(id) ON DELETE CASCADE,

  event_type                      text NOT NULL,                              -- see §3.8.1
  event_at                        timestamptz NOT NULL DEFAULT now(),
  actor_id                        uuid REFERENCES public.contact_profiles(id) ON DELETE SET NULL,
  actor_kind                      text CHECK (actor_kind IN ('coach','staff','system')) NOT NULL,

  subject_kind                    text,                                       -- 'coach_profile', 'coach_document', 'coach_package', 'induction_module', 'coach_badge'
  subject_id                      uuid,                                       -- deliberately denormalised, no FK

  summary                         text NOT NULL,                              -- human-readable for the activity feed
  payload                         jsonb NOT NULL DEFAULT '{}',

  created_at                      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_coach_onboarding_events_coach_id ON public.coach_onboarding_events (coach_id);
CREATE INDEX idx_coach_onboarding_events_event_at ON public.coach_onboarding_events (event_at DESC);
CREATE INDEX idx_coach_onboarding_events_event_type ON public.coach_onboarding_events (event_type);

COMMENT ON TABLE public.coach_onboarding_events IS
  'Append-only audit log of every meaningful action across a coach''s onboarding lifecycle. Source for dossier activity feed and audit tab.';

ALTER TABLE public.coach_onboarding_events ENABLE ROW LEVEL SECURITY;

CREATE POLICY "staff_full_coach_onboarding_events" ON public.coach_onboarding_events
  FOR ALL USING (public.is_platform_staff()) WITH CHECK (public.is_platform_staff());

CREATE POLICY "owner_read_own_coach_events" ON public.coach_onboarding_events
  FOR SELECT USING (
    public.owns_contact(coach_id)
    AND actor_kind = 'coach'
  );
-- Coaches see only their own actions (their uploads, reads, etc.). They do not see staff or system events.
-- The full audit log is ops-only.

3.8.1 Event type vocabulary

Closed set. Add new types when needed.

All inserts into events happen as part of the same transaction as the underlying action. If the action rolls back, the event rolls back with it.

---

4. State machines

Two state machines run in parallel: the internal granular state (12 states, tracked in coach_profiles.onboarding_state) and the coach-facing collapsed state (6 stages, derived from the internal state at read time). The coach UI shows the collapsed state; ops UI shows the internal state.

4.1 Internal state machine (the database state)

Lives on coach_profiles.onboarding_state. The 12 states map to the 6 sketches.

invited
  ↓ (coach; first document submitted)
documents_in_progress
  ↓ (coach; all required documents submitted)
documents_in_review
  ↓ (staff; all documents verified)
verification_in_progress       ← optional explicit hold-state, may collapse into documents_in_review
  ↓ (staff; starts package prep)
package_in_preparation
  ↓ (staff; releases package)
package_sent
  ↓ (coach; signs)
package_signed
  ↓ (coach or system; first module read)
induction_in_progress
  ↓ (all modules read OR staff override)
awaiting_activation
  ↓ (staff; flips activation, triggers badge award)
active
  ↔ suspended       (staff; reversible)
  ↓ (staff)
offboarded

Transition rules:

• —Forward-only on the main spine. Backwards moves are explicit ops actions (e.g. requesting amendments moves from package_sent back to package_in_preparation with a new coach_packages row).
• —The transition awaiting_activation → active is the only place where automatic side effects occur (§9.3).
• —suspended ↔ active is reversible without state-machine penalty. Suspension does not re-trigger activation side effects.
• —offboarded is not terminal at the row level. Because coach_profiles.id is the contact_profiles.id, a contact can only have one coach_profiles row ever. The offboarded state is the row's final state; the row persists indefinitely as the institutional record (per the brief's institutional-memory commitment). Re-engagement of a previously-offboarded coach means transitioning the existing row out of offboarded back to an earlier state (typically ) via an explicit ops action that resets , , to null and generates a new reference number for the new engagement period. Pass 1 does not implement this re-engagement transition (it is a Pass 3+ concern; offboarding itself is rare in the first 24 months); the schema supports it without change.

4.2 Coach-facing collapsed state

Derived for display. Function coach_visible_state(internal_state) returns one of:

The brief's Welcome stage (stage 1 of 6) is presented as "complete on invite open" — there is no internal state for it because the act of opening the guide effectively closes that stage. The coach sees it as done from their first arrival.

4.3 State derivation helper

CREATE OR REPLACE FUNCTION public.coach_visible_state(internal_state text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT CASE internal_state
    WHEN 'invited'                  THEN 'documents'
    WHEN 'documents_in_progress'    THEN 'documents'
    WHEN 'documents_in_review'      THEN 'verification'
    WHEN 'verification_in_progress' THEN 'verification'
    WHEN 'package_in_preparation'   THEN 'verification'
    WHEN 'package_sent'             THEN 'package'
    WHEN 'package_signed'           THEN 'induction'
    WHEN 'induction_in_progress'    THEN 'induction'
    WHEN 'awaiting_activation'      THEN 'induction'
    WHEN 'active'                   THEN 'active'
    WHEN 'suspended'                THEN 'paused'
    WHEN 'offboarded'               THEN 'offboarded'
    ELSE 'documents'
  END;
$$;

Used by views and the API layer. Same function can be mirrored in TypeScript on the client; Postgres-side is preferred for query views.

---

5. Routes

All under the existing Next.js 16 app. proxy.ts does session refresh only and routes legacy redirects; there is no PUBLIC_ROUTES array (the spec's first draft assumed otherwise; corrected per discovery). Auth gating lives in server component layouts: requireAuth() in src/app/(private)/layout.tsx and requireRole(...) in nested layouts.

Public pages live under the src/app/(public)/ route group and are accessible without authentication, no proxy work needed.

5.1 Coach-facing routes

All under /engage/coach/onboarding/* until activation, then /engage/coach/* once coach_profiles.onboarding_state = 'active'. All gated by requireRole(['slainte_standard']) plus requireCoachProfile() in the coach shell layout (src/app/(private)/engage/coach/layout.tsx).

5.2 Public coach profile

5.3 Ops-facing routes

Under /engage/staff/coaches/*. Gated by requireRole(['slainte_admin']) in src/app/(private)/engage/staff/coaches/layout.tsx.

Following the existing /engage/staff/contacts/[id]/ convention from discovery item 7, tabs are path segments, not query parameters. This matches the existing staff dossier pattern, gives each tab its own server layout for clean data fetching, and integrates cleanly with palette deep-linking via nav-registry virtual children.

Use RecordDetailTemplate and ObjectHero from the existing staff contacts dossier (src/app/(private)/engage/staff/contacts/[id]/layout.tsx) as the template.

5.4 Nav-registry virtual children

Per the deep-linking pattern documented in docs/engage-redesign/03-navigation-registry.md, the dossier tabs and any coach-side deep-links should be registered as virtual children of their parent items with hideFromSidebar: true so ⌘K can land directly on a specific tab.

Sketch palette entries to register at minimum:

• —/engage/staff/coaches — parent ("Coaches")
• —All seven dossier tab paths from §5.3 — virtual children, hideFromSidebar: true
• —/engage/coach/onboarding/guide — virtual child of the coach section
• —/engage/coach/onboarding/induction/[key] — one per module, virtual children

---

6. RBAC and access control

Per the role-vs-profile principle in §2.2.

6.1 The two gates

6.2 RLS predicates

The DDL in §3 uses two helpers from migration 029:

• —public.is_platform_staff() — true for users with role slainte_admin or slainte_standard. Used in staff_full_* policies for full read/write across coach onboarding tables.
• —public.owns_contact(p_contact_id uuid) — true when the authenticated user's contact id matches the given id. Since coach_profiles uses the contact_profiles id as its own PK, owns_contact(coach_id) works to scope coach-self access.

For Pass 1, RLS allows any platform staff (admin or standard) to read coach data. The UI layer narrows ops dossier access to admin-only via requireRole(['slainte_admin']). If we later want to enforce admin-only at the RLS layer, introduce is_platform_admin() (new helper) and narrow the staff policies. This is a Pass 2+ refinement, not a blocker.

6.3 The requireCoachProfile() helper

New helper to add in src/lib/auth/session.ts (or wherever requireRole and requireAuth live). It should:

1. —Call requireAuth() (existing) to ensure a session.
2. —Use resolveIdentity from docs/identity-model-design-2026-05.md to get the contact for the session's auth user — do not roll new contact-resolution code.
3. —Query public.coach_profiles for a row whose id equals that contact's id.
4. —If the row exists, return it (so calling layouts and pages can use it without a second fetch).
5. —If the row does not exist, redirect via getDefaultLandingPath(session.role) — the user has shell access but no coach activation.

The helper composes with requireRole(['slainte_standard']) already in the coach shell layout. Calling order: requireRole first (catches users without shell access), then requireCoachProfile (catches users with shell access but no coach aspect).

A user whose coach_profiles.onboarding_state = 'offboarded' is treated as not-enrolled for the purpose of this helper — the coach shell does not render. The historical row stays in the database for institutional memory but the workspace becomes inaccessible to the coach.

6.4 Coach self-access summary

A coach holding slainte_standard with a coach_profiles row can:

• —Read their own coach_profiles row (via owner_read_own_coach_profile)
• —Update their own coach_profiles row (via owner_update_own_coach_profile — limited app-side to bio, languages, etc., not state transitions)
• —Read their own coach_documents and the underlying contact_documents they own
• —Write contact_documents for themselves (via the upload action) and update the linked contact_document_id on coach_documents
• —Read their own coach_packages once state is sent, amendments_requested, or

6.5 Public access

Only /coaches/[slug] is publicly readable, and only when coach_profiles.is_publicly_visible = true. The page reads from a server component that performs the visibility check, never exposing the raw coach_profiles row to unauthenticated clients. RLS does not need to permit anonymous reads — the page uses the service-role client for the visibility check, then renders only the public-safe fields.

---

7. The invite and portal-bootstrap flow

The coach never self-applies. Ops creates the coach record from internal scouting and triggers a portal invite via Supabase's inviteUserByEmail. The invite is a password-set link, not an OTP magic link — the term "magic link" is reserved in this spec for the Supabase OTP flow (which we are not using).

7.1 Ops "Create coach" action

/engage/staff/coaches/new is a form requiring:

• —Legal first name (required, written to contact_profiles.first_name)
• —Legal last name (required, written to contact_profiles.last_name)
• —Preferred / display name (optional, written to coach_profiles.display_name)
• —Email (required, written to contact_profiles.email)
• —Phone (optional)
• —Region (AE, etc., drives coach_document_requirements filter)
• —Disciplines (multi-select)
• —Point of contact (defaults to Murray)
• —Lead programme director (Niall or Michelle, required choice)
• —Compliance reviewer (defaults to Eben)

Submission performs, in a single transaction, respecting migration 083 (every contact requires an auth user):

1. —Call resolveIdentity (per docs/identity-model-design-2026-05.md) with parameters indicating: create new auth user with auth_accounts.role = 'slainte_standard', create new contact_profiles row with first_name/last_name/email, link them. This is the auth-user-plus-contact atomic creation that satisfies migration 083.
2. —Create coach_profiles row with id = contact id, onboarding_state = 'invited', invited_at = now(), reference number generated, the four assigned-team FKs populated.
3. —Generate coach_documents rows for each active coach_document_requirements row matching the coach's region.

The whole transaction succeeds or none of it does.

7.2 The invite mechanism

Decision: use Supabase's auth.admin.inviteUserByEmail pattern that the existing sendInvitation flow (src/lib/users/actions.ts) and sendContactInvitation flow (src/lib/contacts/contact-actions.ts) already use. Do not introduce a new signInWithOtp flow for coach onboarding.

Reasons:

• —The pattern is established and tested in the codebase.
• —It produces a callback URL the platform already handles (src/app/(public)/auth/callback/route.ts).
• —It sets a password during callback, so the coach has stable repeat access after the first sign-in (the brief implies the coach uses this portal long-term, not a series of one-time OTP links).
• —It carries an invite_token that lets the callback do additional setup (e.g. record invite_opened event, redirect to the guide).

Adaptations needed for coach onboarding specifically:

• —Use slainte_standard as the role, not slainte_client (the contact-invite default) or slainte_admin (the user-invite default for admin users).
• —The callback handler must, after password set, check for the user's coach_profile state and redirect to /engage/coach/onboarding/guide if it's the first visit, otherwise /engage/coach/onboarding.
• —The callback must insert a coach_onboarding_events row for invite_opened on first callback.

Insert an invite_sent event at the moment the invite is issued (step 6 of §7.1).

7.3 Invite link expiry and re-send

Use the Supabase default invite expiry. If a coach lets the link expire and asks for re-send, ops triggers a fresh invite from the dossier ("Re-send invite" affordance). Counts as a new invite_sent event in the audit log.

7.4 The only email in the flow

Per Eben's "no emails after this" call (brief, Sketch 5 frame), the invite email is the only email in the coach onboarding journey end-to-end. Everything else — notifications about document verification, package release, activation — happens in-portal. The one exception is induction feedback, which routes to hello@slainteendurance.com (§9.4) — but that's outbound from the coach to the team, not inbound to the coach.

---

8. Document expiry handling

The schema supports expiry tracking from day one. contact_documents.expires_on is the field. The dossier's "Future-proofing" tab in the work zone (Sketch 3) reads from this.

Pass 1 (this spec):

• —The schema captures expiry dates
• —The dossier displays them in the Future-proofing tab
• —Ops can manually flag expiring documents and chase the coach
• —No automated suspension

Future (deferred):

• —A cron job runs daily and:
  • —Sends ops a notification 60 and 30 days before any expiry
  • —Auto-transitions coach_profiles.onboarding_state from active → suspended on the day a critical document expires
  • —Sends the coach an in-portal renewal request
• —Renewal is a re-upload through the workspace, which moves the relevant coach_documents row through awaiting_upload → uploaded → in_review, and on verification re-activates the coach automatically

Deferring the cron is fine because at 5–10 coaches over 24 months ops can chase manually with a weekly "expiries in 90 days" view. The query for that view is trivial against contact_documents.expires_on.

---

9. Triggers and computed values

9.1 On coach_documents.verification_state change

When verification_state transitions to verified or rejected, insert a corresponding coach_onboarding_events row.

When all required documents for a coach reach verified, optionally auto-transition coach_profiles.onboarding_state from documents_in_review to verification_in_progress (or skip directly to package_in_preparation — implementation choice; the explicit verification_in_progress state may collapse into the others).

9.2 On coach_packages.package_state = 'signed'

Pass 1: Insert coach_onboarding_events for package_signed. Auto-transition coach_profiles.onboarding_state from package_sent to package_signed. Capture signature metadata (timestamps, IP, user agent) into digital_signature_metadata. The contract_rendered_html column already contains the immutable HTML snapshot; this is the legal record in Pass 1.

Pass 2 / PDF infrastructure sprint: Generate the signed PDF (server-side rendering of the contract template with signature stamp) and store at signed_pdf_storage_path, populating signed_pdf_generated_at. PDF generation is not part of the Pass 1 sign action — it is added as a separate step in a later pass.

Why deferred: PDF generation infrastructure is a substantial cross-cutting concern (invoices, statements, contracts, reports all need it eventually). Doing it as part of coach onboarding would either compromise the PDF infrastructure (rushed under coach-onboarding pressure) or compromise coach onboarding (delayed waiting for PDF infrastructure decisions). The right shape is a dedicated PDF sprint between Pass 1 and Pass 2 of coach onboarding, with coach contracts as its first consumer. The signed HTML snapshot is the legal record until then.

9.3 On coach_profiles.onboarding_state = 'active'

Atomic transaction triggered by the ops activation action:

• —Set activated_at = now()
• —Set is_publicly_visible = true, publicly_visible_since = now()
• —Insert coach_badge_awards row for the slainte_certified badge
• —Insert coach_onboarding_events for coach_activated
• —Insert coach_onboarding_events for badge_awarded (slainte_certified)

The activation moment of Sketch 5 reads coach_profiles.activated_at to know when to show the credential reveal.

9.4 On coach_induction_completions insert with feedback_note IS NOT NULL

Pass 1: Server action sends an email to hello@slainteendurance.com via the existing transactional email pattern (_shared/slainte-email.ts + email_templates table per migration 046). Subject line includes coach name and module title; body includes the feedback content. Set feedback_email_sent_at on success.

Pass 2+ (when Engage notifications exist): Additionally create an Engage in-portal notification for each of the four core team profiles (Murray, Niall, Michelle, Eben). Set feedback_notification_routed_at when notifications complete. Engage notifications are net-new infrastructure not in scope for Pass 1.

---

10. The activity feed query

The dossier's activity feed in Sketch 3 reads from coach_onboarding_events:

SELECT
  e.event_at,
  e.event_type,
  e.actor_kind,
  e.summary,
  e.payload,
  e.actor_id,
  actor.first_name AS actor_first_name,
  actor.last_name AS actor_last_name
FROM public.coach_onboarding_events e
LEFT JOIN public.contact_profiles actor ON actor.id = e.actor_id
WHERE e.coach_id = $1
ORDER BY e.event_at DESC
LIMIT 50;

The summary column is written human-readable at insert time so the feed renders with no further work. Actor avatars are resolved from the joined contact_profiles row in the rendering layer (avatar treatment varies by actor_kind — coach gets the warm-gradient treatment, staff gets their assigned team colour).

---

11. Migration plan

Conceptual sequence. Actual migration numbers must come from mcp__supabase__list_migrations before authoring each file — the repo and live DB can drift (per database-schema.mdc and the team's MCP-vs-CLI sync gotcha). Authoring migrations with assumed numbers risks collisions.

Each migration should be reviewable independently. Where a table references another, sequencing matters; the order below respects dependencies.

1. —coach_profiles table, indexes, set_updated_at_coach_profiles trigger, RLS policies (§3.1)
2. —coach_intake_briefs table, partial unique index, trigger, RLS (§3.2)
3. —contact_documents table, indexes, trigger, RLS; contact-documents storage bucket + storage policies (§3.4)
4. —coach_document_requirements table + seed of the five Pass 1 requirements, trigger, RLS (§3.3)
5. —coach_documents table, indexes, trigger, RLS (§3.4)
6. —coach_packages table, partial unique index, trigger, RLS (§3.5)
7. —induction_modules table + seed of the five Pass 1 modules (placeholder content acceptable for initial seed), trigger, RLS (§3.6)
8. table, unique index, trigger, RLS (§3.6)

Cursor should verify each migration applies cleanly to the current live DB state before authoring the next.

---

12. Pass 1 cut-line

This spec describes the full data model and the route map. Implementation should sequence as follows.

Pass 1 ships (the minimum end-to-end loop)

The goal of Pass 1 is the first real coach progressing from invite → activation, with ops able to manage them, the coach having a credible experience at every stage they touch, and Sláinte having the audit trail and badge to mark the moment.

• —All schema tables and seed data (§3 in full)
• —Ops "Create coach" action at /engage/staff/coaches/new (§7.1)
• —Portal invite flow via inviteUserByEmail (§7.2)
• —The coach welcome workspace (Sketch 1: guide foyer, workspace, progress page)
• —The document upload + verification loop (coach side via contact_documents/coach_documents; ops side via the dossier)
• —The ops dossier (Sketch 3) with at least: dossier head, StudioBlockStepper journey, active work zone Needs-your-attention tab functional, Discovery & intake tab, Documents tab, Audit tab. Other tabs can be stubbed.
• —The activation flow: ops triggers activation → state transition → badge award → coach is slainte_standard + active coach_profiles → can sign into /engage/coach
• —The active workspace at /engage/coach as a stub page with the rendered (the circular Playfair-initial badge from Sketch 5; lives as a small reusable UI component) and a "more coming soon" placeholder for the workspace sections. The badge reads from , so once activation awards the badge appears under the avatar persistently.

Pass 1 deliberately excludes

• —The signing book modal (deferred to Pass 2)
• —The public coach profile page at /coaches/[slug] (deferred to Pass 2)
• —The induction module index and reader (deferred to Pass 2)
• —The dossier's Package, Induction, Correspondence, Notes tabs as functional surfaces (deferred to Pass 2)
• —The activation moment surface (Sketch 5 exhibit 1, full editorial moment — Pass 2)
• —The workspace first-visit tour (Pass 2)
• —Document expiry automation (Pass 3+)
• —Engage in-portal notifications (out of scope; system-wide future work)
• —Signed PDF generation (out of scope for coach onboarding; addressed by dedicated PDF infrastructure sprint between Pass 1 and Pass 2)

PDF infrastructure sprint (between Pass 1 and Pass 2)

Dedicated 2–4 week sprint to build the PDF generation infrastructure properly. Scope:

• —Engine selection (Puppeteer in edge function vs. third-party service like DocRaptor/Documenso vs. server-rendered React-to-PDF)
• —Template architecture for contracts, invoices, statements
• —Signature stamping and legal-grade timestamping for UAE context
• —Storage policy for generated PDFs
• —Integration test with coach contracts as the first consumer (retroactively generates PDFs for any contracts signed in Pass 1)

Coach onboarding Pass 2 picks up the PDF infrastructure as a consumer; it does not need to build it.

Pass 2 adds (post PDF sprint)

• —The signing book modal (Sketch 2) — coach side + ops package-editing workspace
• —The public coach profile page at /coaches/[slug] (Sketch 2 exhibit 2)
• —The induction module index and reader (Sketch 4)
• —The dossier's Package, Induction, Correspondence, Notes tabs as functional surfaces
• —The full activation moment surface (Sketch 5 exhibit 1)
• —The workspace first-visit tour (Sketch 5 exhibit 2 tour overlay)
• —Signed PDF generation integrated into the package signing flow

Pass 3 polishes

• —Document expiry automation (cron job, auto-suspension)
• —Re-engagement after offboarding flow
• —Badge taxonomy expansion (more credential tiers, tenure milestones, character badges once defined)
• —Public website badge displays (their own dedicated project per the team's earlier scoping)
• —The full active coach workspace product (Athletes / Calendar / Messages / Sessions / Earnings as real surfaces)
• —Engage in-portal notifications integrated into induction feedback routing

Pass 1 gives Sláinte a working end-to-end coach onboarding sufficient to onboard the first real coach. Pass 2 elevates it to the full experience the brief describes. The PDF sprint and Pass 3 are long-term operationalisation.

---

13. Open questions deliberately left for implementation

Things the spec stops short of dictating, because they're implementation details Cursor can decide pragmatically:

• —Exact reference-number generation mechanism (lazy per-year sequences vs. atomic table — both work; pattern after studio_booking_ref_seq in 041_studio_resources.sql)
• —Whether to use Postgres triggers or app-level handlers for the side-effect transitions (e.g. activation → badge award) — triggers are cleaner for atomicity, app-level is easier to debug; either is acceptable
• —Whether verification_in_progress is a useful distinct state or should collapse into documents_in_review
• —Notification system architecture for the Pass 2+ in-portal notifications work (out of scope here; will need its own design)
• —Per-team-member RLS granularity beyond is_platform_staff() — only worth introducing if real-world ops needs require it

Cursor should make the call and document its decision in the migration comment block.

---

14. Verification checklist

Before considering this spec complete, validate:

• — Every table in §3 follows identity-model.mdc conventions (<aspect>_profiles naming, FK to contact_profiles, RLS pattern, updated_at trigger)
• — No table adds 'coach' to contact_type enum
• — No new role slug is introduced; coaches receive slainte_standard
• — All RLS policies use is_platform_staff() and owns_contact() per migration 029
• — All routes in §5 are reachable; (public)/coaches/[slug] lives in the public route group with no proxy work
• — The requireCoachProfile() helper exists in src/lib/auth/session.ts
• — calls both and

---

15. What this spec deliberately doesn't say

Anything about:

• —Specific UI component code (the sketches are the visual reference; design tokens live in project-context/SLAINTE-DESIGN-SYSTEM.md and the rebuild-landing prototype)
• —The PDF rendering implementation (deferred to PDF sprint)
• —The full active workspace product surface (Pass 3+ project)
• —Badge ornament design beyond the placeholder circle-with-initial (separate project)
• —Marketing or public-site treatment of the badges (separate project)
• —The Engage in-portal notifications system architecture (out of scope; future cross-cutting work)
• —staff_profiles table or any staff-aspect modelling (out of scope; coaches are contractors, not staff)
• —Per-team-member granular RBAC (Pass 2+ if real ops requires)

These are out of scope on purpose. Update this spec if any of them need to be drawn into scope later.

---

Appendix A — Changelog from first draft (2026-05-29)

The first draft of this spec, written before discovery, made assumptions about the codebase that the discovery report at project-context/coach-onboarding-discovery-report.md corrected. The changes below were applied in the revision authored 2026-05-29 post-discovery.

Architectural reframings:

• —Role-vs-profile access model made explicit. New §2.2 documents the principle that roles grant shell access and aspect profiles grant feature activation. Coaches receive slainte_standard (existing role); the coach UI activates because of coach_profiles presence. No new role slug. This replaces the first draft's silent assumption that coaches needed a dedicated slainte_coach slug.
• —staff_profiles table dropped from scope. Ops side gated by slainte_admin role only. Coaches are independent contractors and not subject to the same HR/WPS treatment that would justify a staff aspect. Future staff_profiles work is its own project once Sláinte has employed staff to model.
• —Atomic auth-user-plus-contact creation enforced per migration 083 (NOT NULL auth_user_id). §7.1 updated: ops "Create coach" uses resolveIdentity to create auth + contact + coach_profile in one transaction; the invite is the coach's first sign-in, not their first existence. First draft incorrectly proposed creating the contact first.

Concrete corrections:

• —contact_profiles.full_name and email_primary corrected to first_name/last_name/email throughout (§3.1, §7.1, §10).
• —is_staff() corrected to public.is_platform_staff() (the actual helper from migration 029).
• —auth.uid() = coach_id RLS pattern corrected to public.owns_contact(coach_id) (the actual helper from migration 029).
• —requireRole('staff') corrected to specific slugs from rbac-and-roles.mdc (['slainte_admin'] for ops dossier, ['slainte_standard'] for coach shell).

Pattern alignments with existing code:

• —Per-table RLS DDL added to §3.1 onward, mirroring the billing_profiles convention from migration 064 (set_updated_at_<table> trigger, staff_full_<table> and owner_read_own_<table> policies).
• —contact_documents storage bucket spec added (§3.4.1), mirroring the company-compliance-documents pattern from migration 017 and company-profile/actions.ts constants.
• —Reference number generation precedent cited from studio_booking_ref_seq (migration 041, SL-NNNNNN).
• —Nav-registry pattern reference corrected to docs/engage-redesign/03-navigation-registry.md (the actual doc) instead of the placeholder file name in the first draft.
• —Magic-link mechanism specified as inviteUserByEmail (matching existing / patterns), not .

Tab URL convention:

• —Dossier tabs changed from query parameters (?tab=documents) to path segments (/documents) to match the existing staff contacts dossier convention (§5.3). Cleaner for server layouts, palette deep-linking, and bookmarkability.

Pass 1 cut-line refinements:

• —§12 reorganised with explicit Pass 1 / PDF sprint / Pass 2 / Pass 3 sequencing.
• —Signed PDF generation explicitly deferred to the PDF infrastructure sprint between Pass 1 and Pass 2. Pass 1 sign action stores contract_rendered_html as the legal record.
• —Engage in-portal notifications explicitly deferred. Pass 1 induction feedback uses email-only to hello@slainteendurance.com.
• —Public coach profile, signing book modal, induction reader all moved from Pass 1 to Pass 2.

Migration numbering:

• —§11 now requires mcp__supabase__list_migrations to be called before authoring each migration, rather than assuming sequential numbering from a known base. Reconciles with the team's documented MCP-vs-CLI sync gotcha.

---

Appendix B — Self-review pass (consistency check)

A full consistency review of this spec was performed after the discovery-driven rewrite. Both the issues caught and fixed, and the issues noted but not fixed (because they require team input or are minor enough to flag rather than resolve), are listed below honestly.

Issues caught and fixed during self-review

B.1 — Terminology drift: "magic link" vs. "portal invite". The first pass of the rewrite used "magic-link invite" in three places (§2.3, §7 heading, §7.3), but §7.2 explicitly says we are NOT using signInWithOtp (the true Supabase magic-link / OTP flow). We use inviteUserByEmail — a password-set invite link. The spec now consistently uses "portal invite," "invite link," or just "invite" throughout, and §7's heading explicitly notes the distinction.

B.2 — Offboarding architecture conflict. §4.1 originally said "offboarded is terminal in the sense that re-engagement creates a fresh coach_profiles row." But coach_profiles.id is the contact's id (PK = FK), so a contact can only have one coach_profiles row ever. A "fresh row" for the same contact would either violate the PK or destroy institutional memory. §4.1 is now corrected: offboarding is the row's final state, the row persists indefinitely, re-engagement is a state transition out of offboarded (with reset timestamps and a new reference number for the new engagement period). Pass 1 does not implement the re-engagement transition — it is Pass 3+ work.

B.3 — requireCoachProfile() pseudocode contradicted its own footnote. §6.3 originally provided TypeScript pseudocode that called resolveContactForAuthUser() and fetchCoachProfileForContact(), then the footnote said "use resolveIdentity rather than rolling new contact-resolution code." The pseudocode rolled new code while the footnote said not to. §6.3 is now a prose description of the helper's responsibilities, explicitly composing with resolveIdentity, with no contradictory pseudocode.

B.4 — Three overlapping package routes were undifferentiated. §5 listed /engage/coach/onboarding/package (coach view modal), /engage/staff/coaches/[slug]/package (ops preview tab), and /engage/staff/coaches/[slug]/package/edit (ops editing workspace) without making their relationship clear. Each now has a row-note explaining its specific role: coach-only with sign action, ops read-only preview rendering the coach's view inline, ops editing workspace with state transitions.

B.5 — Pass 1 verification checklist contained a Pass 2 item. The original §14 checklist had "Signed package stores contract_rendered_html in Pass 1" but Pass 1 has no signing surface — the signing book modal is explicitly Pass 2. Same issue with the induction feedback item. Both checklist items are now reframed as Pass 1 schema commitments that Pass 2 surfaces consume, rather than Pass 1 functional commitments.

B.6 — Active workspace badge underspecified in Pass 1. §12 Pass 1 said "the badge visible" without saying who builds the badge component. The badge needs a small reusable UI component (the circular Playfair-initial treatment from Sketch 5) for it to render. §12 now explicitly includes the badge component as Pass 1 scope, with a note that it reads from coach_badge_awards so activation populates it automatically.

B.7 — Slug generation timing unspecified. coach_profiles.slug was declared UNIQUE but the spec did not say when it gets generated or how collisions are handled. §3.1 now specifies: generated at coach_profile creation from display_name (or first/last name fallback), collisions resolved with numeric suffix, immutable once set.

Issues noted but not fixed (flagged for team input)

B.8 — RLS on coach_documents allows coach UPDATE but the comment claims it can't change verification_state. The §3.4.3 RLS UPDATE policy lets the coach update any column on their own coach_documents row. The comment says "verification_state changes are enforced at the app layer." Strictly true — coaches don't have direct DB access — but a defense-in-depth move would be a row-level trigger or a CHECK in the WITH CHECK clause that prevents non-staff from setting verification_state to verified or rejected. Not fixed because: the practical risk is near-zero (no direct DB exposure), and adding the trigger expands the spec scope. Flagging for team discussion. If accepted, the trigger would: on UPDATE, if auth.uid() is not platform staff and NEW.verification_state differs from OLD.verification_state, raise an exception.

B.9 — Pass 1 ships schema for surfaces that Pass 2 builds. Pass 1 includes the full §3 schema (coach_packages, induction_modules, coach_induction_completions), but the surfaces that exercise those tables (signing book modal, induction reader) are Pass 2. Pass 1 effectively ships unused tables. Not fixed because: it's deliberate. The tables are cheap to ship, Pass 2 needs them to exist, and not landing them in Pass 1 would mean a Pass 2 migration wave that delays Pass 2's UX work. The verification checklist in §14 now makes the schema-vs-surface distinction explicit so this isn't accidentally read as "we shipped Pass 2 features in Pass 1."

B.10 — Activity feed query in §10 may return null actor names. contact_profiles.first_name and last_name are nullable per the existing schema. The query in §10 could return null for either. Not fixed because: it's a rendering-layer concern (the UI should handle null names gracefully and fall back to email or initials), not a query bug. Worth noting for Cursor when implementing the feed component.

Verifications performed and confirmed clean

• —All RLS policies in §3 use public.is_platform_staff() and public.owns_contact(). No auth.uid() = ... direct-comparison patterns remain.
• —slainte_standard and slainte_admin are the only role slugs referenced. No slainte_coach, no bare staff, no slainte_client references.
• —§3.1 onboarding_state CHECK has 12 values; §4.1 names the same 12; §4.2 mapping covers the same 12; §4.3 helper function has 12 WHEN clauses. Aligned.
• —All routes in §5 either have a Pass 1 implementation commitment in §12 or are explicitly listed as Pass 2/Pass 3 deferred.
• —Appendix A changelog items each appear in the body of the spec.
• —All six brief stages map cleanly to coach-visible states in §4.2.
• —The brief's commitments (signing-book moment, Sláinte Certified badge, four-person Team Sláinte presence, institutional memory) each have a schema or route correspondence in the spec.

Open items still requiring product decision (none blocking implementation)

• —§13 lists Cursor's pragmatic implementation choices. None require Eben's input before Pass 1 starts.
• —The staff_profiles future-work flag in §1 and §15 is a deliberate non-commitment.
• —The is_platform_admin() potential helper in §6.2 is flagged as Pass 2+ refinement, not a Pass 1 decision.
• —B.8 (RLS defense-in-depth trigger) is the only open item the team may want to comment on directly.

If the team identifies a contradiction or omission during their review that this self-review missed, that's the trigger to revise this spec before any further implementation work.