DreamPro Coaching — Channel Economics Schema (Phase 2)

The concrete, ready-to-implement data model for the two-tier (ClickBank-style) channel economics of The DreamPro Coaching System. Covers attribution (who recruited whom, who owns each client), the 3-way revenue split (coach + channel partner + platform), and how it rides the existing Stripe Connect payout rails.

Status (2026-06-14): split engine SHIPPED (migration 370 + coaching-payouts.ts): the coaching_revenue_policies table (3-way policy, resolved coach→partner→default), the creator_earnings extension (two new source_kinds + stripe_invoice_id + coaching_membership_id), the pure computeSplit (sum-invariant verified) + recordCoachingInvoiceSplit (two ledger rows; money guard skips is_seed / AI-persona memberships; idempotent per invoice) + a compute-only previewCoachingSplit at POST /api/coaching/admin/split/preview with a UI preview on /dashboard/coaching.

Update (2026-06-14): the charge→split→payout loop is now wired (gated). The live Stripe webhook auto-records the split on invoice.payment_succeeded (payment.ts; the coaching checkout stamps subscription_data.metadata{plan_key, membership_id} so each recurring charge resolves its membership), and the real Connect transfers run on a daily coaching_payouts cron (index.ts → payoutPendingCoachingEarnings), doubly gated by the cron toggle + env COACHING_PAYOUTS_ENABLED. The partner dashboard also shipped (/dashboard/coaching/partner ← getPartnerOverview() + GET /api/coaching/admin/partner-overview): network-wide override balance (pending/paid), coach roster ranked by override, client counts, platform take. Still follow-on: partner-scoped access (v1 is super-admin + network-wide). The draft SQL below matches what shipped.

0. What we reuse vs. build

Reuse unchanged (from migration 160 / 161):

creator_payout_accounts — one Stripe Connect Express account per user. John and every coach each get one via the existing onboarding flow. No change.
creator_payout_periods — reusable if we ever batch coaching payouts; v1 pays per-invoice (see §5), so period_id stays null on coaching rows.
The earnings ledger creator_earnings — extended, not replaced (§4).

Build (this doc):

Attribution graph — channel_partners, coaches, coaching_referral_codes, coaching_memberships.
3-way split policy — coaching_revenue_policies.
Ledger extension — new source_kinds + stripe_invoice_id + coaching_membership_id on creator_earnings.

The one real mismatch we're solving: creator_earnings was designed for a 2-way split (creator_share_cents vs platform_share_cents, summing to gross_cents on a single row). Coaching is 3-way. We solve it with two ledger rows per invoice (one per Connect payee) and book the platform share once — details in §4–§5.

1. Attribution graph

`channel_partners` — the apex partner (John)

create table if not exists channel_partners (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null unique references profiles(id) on delete restrict,

  -- Brand John commands (operational ownership).
  display_name text not null,
  slug text not null unique,                 -- e.g. 'rowley' → dreampro.io/c/rowley
  logo_url text,
  accent_color text,

  -- Default override the partner earns on every coach + client beneath them,
  -- in basis points (2000 = 20%). Bounded by platform floor in app logic.
  default_override_bps int not null default 2000
    check (default_override_bps >= 0 and default_override_bps <= 10000),

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

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

`coaches` — recruited by a channel partner

create table if not exists coaches (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null unique references profiles(id) on delete restrict,

  -- Who recruited this coach. Immutable in spirit; if a coach ever changes
  -- partners, existing memberships keep their snapshotted partner (see §1.4).
  channel_partner_id uuid not null references channel_partners(id) on delete restrict,

  -- Coach-level branding for their landing + invite.
  display_name text not null,
  slug text not null unique,                 -- dreampro.io/coach/<slug>
  bio text,
  logo_url text,
  accent_color text,

  status text not null default 'pending'
    check (status in ('pending', 'active', 'suspended', 'left')),

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

create index if not exists idx_coaches_partner on coaches (channel_partner_id);

Payout account for a coach/partner = creator_payout_accounts keyed on the same user_id. No new payout-account table.

`coaching_referral_codes` — recruit + signup attribution

Mirrors the Lovio code generator/trigger pattern (migration 270). A coach code signs up clients; a partner code recruits coaches.

create table if not exists coaching_referral_codes (
  code text primary key,                     -- 8-char, no 0/O/1/I/L lookalikes
  owner_kind text not null check (owner_kind in ('channel_partner', 'coach')),
  owner_id uuid not null,                     -- channel_partners.id | coaches.id
  label text,                                 -- "IG bio link", "email blast"
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);
create index if not exists idx_coaching_referral_codes_owner
  on coaching_referral_codes (owner_kind, owner_id);

-- Reuse the proven generator (copy of lovio_gen_referral_code, renamed).
create or replace function coaching_gen_referral_code()
returns text language plpgsql as $$
declare
  alphabet text := 'ABCDEFGHJKMNPQRSTUVWXYZ23456789';
  result text := ''; i int := 0;
begin
  for i in 1..8 loop
    result := result || substr(alphabet, 1 + floor(random() * length(alphabet))::int, 1);
  end loop;
  return result;
end; $$;

`coaching_memberships` — the client ↔ coach ↔ partner edge (the money source)

This is the immutable attribution snapshot taken at signup. Even if the coach later changes partners or the policy changes, a membership keeps the channel_partner_id it was born under — so override payments never silently re-route mid-subscription.

create table if not exists coaching_memberships (
  id uuid primary key default gen_random_uuid(),
  client_user_id uuid not null references profiles(id) on delete cascade,

  coach_id uuid not null references coaches(id) on delete restrict,
  -- Snapshotted at signup from coaches.channel_partner_id (immutable attribution).
  channel_partner_id uuid not null references channel_partners(id) on delete restrict,

  -- The code used at signup, for analytics + audit of which campaign converted.
  referral_code text references coaching_referral_codes(code),

  -- Optional cohort link (a team under the coach) → cohort-scoped Workout Window
  -- squads + private competitions (Phase 3). Reuses the existing teams table.
  cohort_team_id uuid references teams(id) on delete set null,

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

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

  -- One active coach per client in v1. (Drop/relax later for multi-coach.)
  constraint uq_membership_active_client unique (client_user_id)
);
create index if not exists idx_coaching_memberships_coach on coaching_memberships (coach_id);
create index if not exists idx_coaching_memberships_partner on coaching_memberships (channel_partner_id);

2. The 3-way split policy

Fixed structure (platform / coach / partner), so three basis-point columns beat a generic N-party array. Resolution order: coach-specific → partner → platform default. The platform sets a floor; within the remainder the partner (John) flexes coach-vs-partner — that's "operational ownership."

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

  scope text not null check (scope in ('platform_default', 'partner', 'coach')),
  channel_partner_id uuid references channel_partners(id) on delete cascade,
  coach_id uuid references coaches(id) on delete cascade,

  platform_bps int not null check (platform_bps >= 0 and platform_bps <= 10000),
  coach_bps    int not null check (coach_bps    >= 0 and coach_bps    <= 10000),
  partner_bps  int not null check (partner_bps  >= 0 and partner_bps  <= 10000),

  is_active boolean not null default true,
  notes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),

  constraint bps_sum_100 check (platform_bps + coach_bps + partner_bps = 10000),
  -- scope target must match the right FK
  constraint scope_target_ck check (
    (scope = 'platform_default' and channel_partner_id is null and coach_id is null)
    or (scope = 'partner' and channel_partner_id is not null and coach_id is null)
    or (scope = 'coach'   and coach_id is not null)
  )
);

-- Exactly one active policy per scope target.
create unique index if not exists uq_policy_platform_default
  on coaching_revenue_policies ((true)) where scope = 'platform_default' and is_active;
create unique index if not exists uq_policy_partner
  on coaching_revenue_policies (channel_partner_id) where scope = 'partner' and is_active;
create unique index if not exists uq_policy_coach
  on coaching_revenue_policies (coach_id) where scope = 'coach' and is_active;

-- Seed the platform default (illustrative: 18% platform / 62% coach / 20% partner).
insert into coaching_revenue_policies (scope, platform_bps, coach_bps, partner_bps, notes)
values ('platform_default', 1800, 6200, 2000, 'Initial DreamPro Coaching default')
on conflict do nothing;

Platform floor (app-enforced, not a DB constraint): John can move coach_bps ↔ partner_bps but cannot push platform_bps below the platform minimum (e.g. 1500). Enforce in the policy-write endpoint.

Resolution helper (app logic, pseudocode):

policy(coach) =
  active coach-scope policy for coach.id
  ?? active partner-scope policy for coach.channel_partner_id
  ?? active platform_default

3. Subscription plan

No migration needed — subscriptions.plan_key (migration 129) is free text. Add plan_key = 'coaching_subscription'. v1 = one platform price John sets; per-coach custom pricing later via per-coach stripe_price_id.

4. Ledger extension (the 2-way → 3-way fix)

-- Extend the source_kind enum to cover coaching.
alter table creator_earnings drop constraint creator_earnings_source_kind_check;
alter table creator_earnings add constraint creator_earnings_source_kind_check
  check (source_kind in (
    'tip', 'subscription_pool', 'drift_currency', 'sponsorship', 'manual_grant',
    'coaching_subscription',   -- the coach's share of a client invoice
    'coaching_override'        -- the channel partner's override on that invoice
  ));

-- Link coaching earnings back to the invoice + membership for reconciliation.
alter table creator_earnings
  add column if not exists stripe_invoice_id text,
  add column if not exists coaching_membership_id uuid
    references coaching_memberships(id) on delete set null;

create index if not exists idx_creator_earnings_invoice
  on creator_earnings (stripe_invoice_id) where stripe_invoice_id is not null;
create index if not exists idx_creator_earnings_membership
  on creator_earnings (coaching_membership_id) where coaching_membership_id is not null;

Accounting rule — two rows per paid invoice (gross G → Cc/Cp/Pl):

Row	`creator_user_id`	`source_kind`	`gross_cents`	`creator_share_cents`	`platform_share_cents`
A (coach)	coach.user_id	`coaching_subscription`	`G`	`Cc`	`Pl` ← platform booked once, here
B (override)	partner.user_id	`coaching_override`	`G`	`Cp`	`0`

Both rows carry the same stripe_invoice_id, coaching_membership_id, and related_user_id (the client). Reconciliation invariant per invoice: A.creator_share + B.creator_share + A.platform_share == G and B.platform_share == 0. Each row's stripe_transfer_id is stamped when its Connect transfer settles; status walks pending → transferred exactly like tips.

5. Stripe Connect mechanics (per invoice)

Two payees per invoice rules out a single destination charge (one destination only). Use the separate-transfers pattern (already how tips stamp transfers):

Client's coaching_subscription invoice is paid to the platform account (standard subscription; no on_behalf_of).
Webhook invoice.paid (or invoice.payment_succeeded) fires →
- resolve coaching_membership for the customer/subscription,
- resolve policy(coach) (§2), compute Cc, Cp, Pl from amount_paid,
- insert rows A + B (status='pending'), tagged with stripe_invoice_id,
- create two Transfers with a shared transfer_group (e.g. coaching_inv_<invoice_id>) and source_transaction = the invoice charge: one → coach's Connect account, one → John's Connect account,
- stamp each stripe_transfer_id and flip status='transferred'.
charge.refunded / invoice.payment_failed → mark the matching rows reversed and reverse the transfers (Stripe transfer reversal).

Edge cases (mirror the tip handler's posture):

Payee not onboarded (payouts_enabled=false) → record the row but hold the transfer; set status='pending' with a note. A sweep retries once Connect is ready.
Idempotency → unique the work on (stripe_invoice_id, source_kind) so a webhook redelivery can't double-pay. (Add a partial unique index when implementing.)
Membership cancelled but invoice still settles for the period → still pay out (service rendered); cancellation stops future invoices.

6. RLS posture

Follow the creator_* precedent: self-read + super-admin-all, no public write.

A coach reads their own coaches row, their coaching_memberships, and their creator_earnings (already self-read by creator_user_id).
A channel partner reads their channel_partners row, the coaches under them, memberships where channel_partner_id = theirs, and their override earnings.
All writes to policies/codes/memberships go through service-role endpoints (Stripe webhook + admin/partner APIs), never client-direct.

Add helper predicates (is_channel_partner(uid), owns_coach(uid, coach_id)) alongside the existing is_super_admin() to keep RLS readable.