DreamPro Coaching — Coach Seat Schema (Phase 1)

The concrete, ready-to-implement data model + brief-generator contract for the Coach Seat of The DreamPro Coaching System (Section 2). The coach is a seat, held by an AI persona coach by default and swappable to / supervised by a human — built on the platform's shipped swappable-role pattern (migration 234 / 235, PLATFORM_ROLE_SEATS_SETUP.md).

Status: Spec / not yet applied. SQL below is a draft — do not drop it into supabase/migrations/ until greenlit (CI auto-applies). When greenlit, number it after the current head and after the channel-economics migrations (DREAMPRO_COACHING_CHANNEL_SCHEMA.md) because coaching_seats.coaching_membership_id references coaching_memberships.

0. What we reuse vs. build

Reuse unchanged — the entire swappable-holder pattern:

The role-shape: holder XOR (content_manager_role's exactly_one_holder), scope flags + daily_action_budget, the decisions reference the role, not the holder invariant.
The advisory → executed → reversed decision lifecycle. This is the "AI drafts, human approves" mechanic — executed_at IS NULL = a draft awaiting approval; stamping executed_at = sent.
The invite/claim handoff (content_manager_invites shape) for AI→human swap.
The brief-loop cron architecture + the complete() LLM cost-logged shim + recordHeartbeat/CRON_REGISTRY contract.
ai_personas (the AI coach), jq_bridge analysis (Throughline trends — the brief's input), Workout Window check-ins (adherence — the other input).

Build (this doc):

coaching_seats (per client, + supervisor_user_id) — §1
coaching_seat_decisions (the AI-draft lifecycle) — §2
coaching_seat_goals — §3
coaching_seat_invites (AI→human handoff) — §4
The coach-brief generator contract (cron) — §5
Approved-feedback delivery to the client — §6

The one structural difference from the platform roles: they are singleton + super-admin-only. Coaching has one seat per client (potentially thousands) and adds supervisor_user_id so a human coach oversees many AI-held seats without holding each — the entire leverage of the supervised tier (§7).

1. `coaching_seats`

create table if not exists coaching_seats (
  id uuid primary key default gen_random_uuid(),

  -- The client this seat coaches. One active seat per membership.
  coaching_membership_id uuid not null unique
    references coaching_memberships(id) on delete cascade,

  title text not null default 'Coach',

  -- Exactly one holder (the platform-role invariant). The AI persona coach is
  -- the default holder; a human coach can claim the seat (§4).
  held_by_persona_id uuid references ai_personas(id) on delete set null,
  held_by_user_id    uuid references auth.users(id) on delete set null,

  -- NEW vs platform roles: the human coach overseeing an AI-held seat. Distinct
  -- from the holder, so one human supervises many AI seats (the supervised tier).
  -- Null when nobody supervises (pure autonomous) or when a human holds the seat.
  supervisor_user_id uuid references auth.users(id) on delete set null,

  -- Power scope. All false = advisory-only: the AI drafts, nothing auto-sends.
  can_nudge          boolean not null default false,  -- send encouragement / check-in nudges
  can_adjust_program boolean not null default false,  -- propose program/step pacing changes

  -- Cap on autonomous (auto-executed) actions per day. 0 = advisory regardless of
  -- scope flags (matches platform-role semantics exactly).
  daily_action_budget integer not null default 0,

  status text not null default 'active'
    check (status in ('active', 'paused')),

  assigned_at timestamptz not null default now(),
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now(),

  constraint coaching_seat_exactly_one_holder check (
    (held_by_persona_id is not null)::int + (held_by_user_id is not null)::int = 1
  )
);

create index if not exists idx_coaching_seats_persona
  on coaching_seats (held_by_persona_id) where held_by_persona_id is not null;
create index if not exists idx_coaching_seats_holder_user
  on coaching_seats (held_by_user_id) where held_by_user_id is not null;
create index if not exists idx_coaching_seats_supervisor
  on coaching_seats (supervisor_user_id) where supervisor_user_id is not null;
-- The brief loop scans active, AI-held seats; index that hot path.
create index if not exists idx_coaching_seats_active_ai
  on coaching_seats (status) where status = 'active' and held_by_persona_id is not null;

drop trigger if exists set_coaching_seats_updated_at on coaching_seats;
create trigger set_coaching_seats_updated_at
  before update on coaching_seats
  for each row execute function public.set_updated_at();

Seat creation: when a coaching_memberships row is created (client onboards via a coach's code), create the seat in the same transaction, default holder = the coach's configured AI persona (or a platform default coaching persona), and set supervisor_user_id = the coach's user_id if the coach runs the supervised tier.

Default-holder resolution (coaching-onboarding.ts defaultCoachPersonaId()): resolves a DELIBERATE coaching persona — never a random one. Order: DREAMPRO_COACH_PERSONA_ID (explicit, validated active) → DREAMPRO_COACH_PERSONA_NAME (first active persona with that display name) → null = human-held (the coach holds the seat directly). Set one of the envs once a dedicated coaching persona exists to put every new client on the AI tier by default; until then real clients get a human-held seat (correct) rather than a stranger persona "signing" their feedback.

2. `coaching_seat_decisions` — the AI-draft lifecycle

The heart of the seat. The AI coach writes drafts here (executed_at IS NULL); a human approves (or autonomous mode auto-executes within budget); delivery to the client stamps delivered_at.

create table if not exists coaching_seat_decisions (
  id uuid primary key default gen_random_uuid(),
  seat_id uuid not null references coaching_seats(id) on delete cascade,

  -- Actor at time-of-decision, separate from the seat's current holder so a swap
  -- never rewrites attribution on history (the platform-role invariant).
  by_persona_id uuid references ai_personas(id) on delete set null,
  by_user_id    uuid references auth.users(id) on delete set null,

  -- 'brief'             — the daily between-session review (always drafted)
  -- 'nudge'             — encouragement / check-in prompt to the client
  -- 'program_adjustment'— proposed pacing/step change
  -- 'note'              — free-form coach note (internal, not delivered)
  -- 'seat_transferred'  — holder swap (rationale carries the why)
  action_kind text not null check (action_kind in (
    'brief', 'nudge', 'program_adjustment', 'note', 'seat_transferred'
  )),

  -- Optional pointer at what the action concerns.
  target_kind text,                       -- 'dream' | 'dream_step' | 'checkin' | null
  target_id   uuid,

  -- What the AI wrote, and (if a human edited before sending) the final text.
  draft_body  text,
  final_body  text,                       -- null until edited/approved; delivery uses coalesce(final_body, draft_body)

  -- Snapshot of the signals the brief read (Throughline trends + WW adherence +
  -- program progress) so a decision is auditable + reproducible after the fact.
  input_snapshot jsonb,

  -- Lifecycle (mirrors content_manager_decisions, + coaching delivery stamps):
  --   executed_at NULL              → draft, awaiting approval
  --   executed_at set               → approved (by human) or auto-executed (Tier 1)
  --   delivered_at set              → actually sent to the client
  --   reversed_at set               → retracted
  executed_at  timestamptz,
  delivered_at timestamptz,
  approved_by_user_id uuid references auth.users(id) on delete set null,
  reversed_at  timestamptz,
  reversed_by_user_id uuid references auth.users(id) on delete set null,

  -- True iff the autonomous (Tier 1) executor sent it without human approval.
  auto_executed boolean not null default false,

  created_at timestamptz not null default now()
);

create index if not exists idx_coaching_seat_decisions_seat_recent
  on coaching_seat_decisions (seat_id, created_at desc);
-- The review queue: drafts awaiting approval, newest first.
create index if not exists idx_coaching_seat_decisions_pending
  on coaching_seat_decisions (seat_id, created_at desc)
  where executed_at is null and reversed_at is null;
create index if not exists idx_coaching_seat_decisions_target
  on coaching_seat_decisions (target_kind, target_id) where target_id is not null;

-- Daily-brief lookup. NOTE: a UNIQUE index on (seat_id, created_at::date) is
-- NOT possible — timestamptz::date is not IMMUTABLE (timezone-dependent) and
-- Postgres rejects it in an index expression (error 42P17). One-brief-per-seat-
-- per-day idempotency is enforced in the service (hasBriefToday) before insert.
create index if not exists idx_coaching_seat_decisions_brief
  on coaching_seat_decisions (seat_id, created_at desc)
  where action_kind = 'brief';

3. `coaching_seat_goals`

Coach (AI or human) works toward goals the coach/partner sets for the client.

create table if not exists coaching_seat_goals (
  id uuid primary key default gen_random_uuid(),
  seat_id uuid not null references coaching_seats(id) on delete cascade,

  goal text not null,                     -- "Finish Week 4", "3 check-ins/week"
  set_by_user_id uuid references auth.users(id) on delete set null,
  target_date date,
  status text not null default 'active'
    check (status in ('active', 'met', 'missed', 'paused')),

  -- Structured progress so the dashboard can render % without parsing goal text.
  progress_metric text,                   -- 'checkins_this_week' | 'steps_completed'
  progress_value  numeric,
  progress_target numeric,

  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_coaching_seat_goals_active
  on coaching_seat_goals (seat_id, status, created_at desc) where status = 'active';

drop trigger if exists set_coaching_seat_goals_updated_at on coaching_seat_goals;
create trigger set_coaching_seat_goals_updated_at
  before update on coaching_seat_goals
  for each row execute function public.set_updated_at();

4. `coaching_seat_invites` — AI → human handoff

Faithful copy of content_manager_invites, bound to a seat. Super-admin / coach / channel-partner mints a one-time URL; a human claims it; on claim the seat's held_by_persona_id nullifies and held_by_user_id is set atomically (log a seat_transferred decision). Prior AI decisions stay attributed to the persona.

create table if not exists coaching_seat_invites (
  id uuid primary key default gen_random_uuid(),
  seat_id uuid not null references coaching_seats(id) on delete cascade,

  invite_token text not null,             -- 24-byte URL-safe random
  minted_by_user_id uuid references auth.users(id) on delete set null,
  minted_at timestamptz not null default now(),

  used_at timestamptz,                    -- single-use; 410 Gone after
  used_by_user_id uuid references auth.users(id) on delete set null,
  expires_at timestamptz,                 -- null = no expiry
  rationale text,                         -- shown on the landing page

  created_at timestamptz not null default now()
);

create unique index if not exists idx_coaching_seat_invites_active_token
  on coaching_seat_invites (invite_token) where used_at is null;
create index if not exists idx_coaching_seat_invites_seat_recent
  on coaching_seat_invites (seat_id, minted_at desc);

5. The coach-brief generator (cron)

A daily tick — runCoachBriefLoop — over active seats. Per CLAUDE.md it must recordHeartbeat on every fire (success + error), register in CRON_REGISTRY with an isCronEnabled('coaching_brief_loop') guard, and add a CRON_LABELS entry. Same key slug across all three surfaces.

Per seat (active; AI-held, or human-held with AI-assist opted in):

Gather inputs (the snapshot stored on the decision):
- Throughline trends — the client's jq_bridge analysis for the coach connection (mood / energy / sleep trajectory, consistency). Trends only, never raw entries.
- Workout Window adherence — check-in streak, missed windows in the window, last check-in date (workout_window_checkins / _chains).
- Program progress — the client's DreamPro clone: progress %, next step.
Generate via the complete() shim (feature_key: 'coaching.brief', so cost lands in /dashboard/admin/llm-spend). System prompt carries the wellness-lane guardrail (§ guardrail below). Output = a brief decision (+ optionally a nudge / program_adjustment) with executed_at = NULL and input_snapshot.
Mode dispatch (resolved from holder + supervisor + budget — §7):
- Tier 1 (autonomous AI): if held_by_persona_id set, scope flag on, and auto_executed-count today < daily_action_budget → auto-execute: stamp executed_at, auto_executed = true, then deliver (§6) and stamp delivered_at.
- Tier 2 (supervised): leave advisory. It surfaces in the supervisor's review queue (the at-risk roster).
- Tier 3 (human-held): if AI-assist is off, skip generation; else draft as an assist suggestion, never auto-send.
Idempotency: the uq_coaching_seat_daily_brief index makes a redelivered tick a no-op for the day.

Guardrail (bake into the system prompt): the AI coach operates strictly in the wellness / accountability lane — pacing, adherence, encouragement, obstacle problem-solving ("you skipped three windows — what's in the way?"). It must never give prescriptive medical, injury-diagnosis, or nutrition-pathology advice, and must defer to a human/professional for anything outside that lane. Tier-1 autonomous sends require this guardrail; when uncertain, default a seat to Tier 2.

Scale note: unlike the 5 singleton platform seats, this loop can face thousands of seats. Batch per coach/cohort, cap generations per tick, and log() what was deferred (no silent truncation). Respect daily_action_budget per seat.

6. Delivering approved feedback to the client

On execute (Tier 1) or human approval (Tier 2/3), the message (coalesce(final_body, draft_body)) is delivered to the client and delivered_at stamped. Reuse before building:

encouragement_drops (already a client-facing micro-message surface) for nudge decisions — cleanest reuse.
The client's coaching view renders a simple feed of delivered decisions (read straight from coaching_seat_decisions where delivered_at is not null).
program_adjustment decisions, once approved, can patch the client's clone steps (pacing) via the existing DreamPro step endpoints.

No new client-message table is required for v1; the decision row is the record.

7. Mode resolution (the three tiers)

Derived, not stored — compute from the seat:

holder = persona ? 'ai' : 'human'
if holder == 'ai':
  if daily_action_budget > 0 and (can_nudge or can_adjust_program):  → Tier 1 (autonomous)
  else:                                                              → Tier 2 (supervised, drafts await approval)
else (human holds seat):                                            → Tier 3 (human writes; AI-assist optional)

supervisor_user_id determines who sees the Tier-2 review queue for an AI-held seat (one human → many seats). A human can hold some seats (Tier 3) while supervising many others (Tier 2) — that mix is the coach's leverage.

8. RLS posture

Platform roles are super-admin-only; coaching needs scoped reads, so enable RLS and mediate writes through service-role endpoints (Stripe webhook / cron / coach APIs), with app-layer authorization:

Coach (seat holder or supervisor_user_id) reads/manages the seats they hold or supervise + those seats' decisions/goals.
Channel partner reads seats under their coaches (via coaching_memberships).
Client reads only delivered decisions for their own seat (never drafts, never note decisions, never input_snapshot).
All generation/approval/delivery goes through backend routes; no client-direct writes. Add helper predicates (coach_owns_seat(uid, seat_id), supervises_seat(uid, seat_id)) alongside the existing is_super_admin().

DreamPro Coaching — Coach Seat Schema (Phase 1)

0. What we reuse vs. build

1. coaching_seats

2. coaching_seat_decisions — the AI-draft lifecycle

3. coaching_seat_goals

4. coaching_seat_invites — AI → human handoff