Build tracks · build-plan/01-ledger-tracking-payouts.md

01 — Ledger, Tracking, and Payouts

Build plan for PRD phases 1 (ledger), 2 (tracking + SDK), and 5 (payouts). Covers the commission ledger and engine, click/conversion attribution, the Stripe-webhook conversion path, @affiliate-coach/sdk, and Stripe Connect Express payouts. Portal, admin UI, and collateral are other sections; this one defines the tables and contracts they read.

Sources: PRD.md v0.1 (authoritative), PRODUCT.md (authoritative where more specific), PRD-Activation-Platform.md §7.4–7.6, §9, §10 (shape only, not scope), and vera-wellness 0137_stripe_connect.sql plus the webhook route at app/api/stripe/webhook/route.ts for the house pattern.

Conventions carried from vera-wellness: migrations are NNNN_snake_name.sql, four digits, sequential, lowercase SQL, public. qualified, a header comment that says why before any DDL, comment on column for anything a future reader could misread, RLS enabled on every table, and "RLS on with no policies" as the service-role-only gate. Money tables get the column-privilege treatment, not just row policies.

0. Decisions that shape everything below

0.1 Tenancy: workspace_id on every top-level table, now

Recommendation: add workspace_id uuid not null references workspaces(id) to every table that is not a pure child of another workspace-scoped row, in the first migration, with exactly one seeded workspace and no workspace UI.

What "now" costs: one column per table, one seed row, and the service layer reading WORKSPACE_ID from config instead of omitting it. Roughly an hour across the whole schema. No roles, no members UI, no workspace switcher — the Activation PRD's eight roles (§9) are explicitly not built; workspace_members exists with two roles (owner, admin) because the admin app needs a gate.

What "later" would cost: backfilling every table under load, rewriting every RLS policy (they all key on workspace in a multi-tenant world), rebuilding every unique index (referral codes, idempotency keys, customer locks are all per workspace once there is more than one), and re-deriving every composite FK that keeps a child row from crossing tenants. That is the "rewrite" PRODUCT.md forbids.

The alternative — one Supabase project per tenant — is the cleaner isolation story and is still available later for a customer who demands it. But it does not fit the Activation PRD's "agency manager across client workspaces" or a partner identity shared across merchants, and it multiplies migration and webhook operations by tenant count. Recommended as an option for enterprise tenants, not the default.

Cost of being wrong each way:

Asymmetric. Add it now.

Two mechanical rules that make the column mean something:

1. Composite FKs across the money path. affiliates carries unique (workspace_id, id), and children reference (workspace_id, affiliate_id) → affiliates(workspace_id, id). Same for programs, products, conversions. A commission row therefore cannot name an affiliate in one workspace and a conversion in another; Postgres refuses the insert. This costs nothing and is the only way the column is a boundary rather than a label.
2. No default on workspace_id. A default hides the bug where a write path forgot to scope. Every insert names its workspace explicitly.

0.2 "Append-only" means money is immutable, state advances

PRD §7.1 says nothing is updated in place; §7.2 shows a state machine. Both hold with this reading: on commissions, every column describing money or lineage (amount_cents, conversion_id, affiliate_id, level, plan_level_id, kind, reverses_commission_id) is frozen after insert by trigger. The state column and its paired timestamps advance along the legal edges only, also by trigger. Every transition writes a row to commission_events. A reversal is a new negative row. Balance is always sum(amount_cents) over a filter; nothing is ever recomputed from a mutated row.

0.3 Conversions are strictly positive; refunds are not conversions

A refund is not a negative conversion. It is a reversal of commission rows, recorded on the commission side with its Stripe source carried along. This keeps conversions.amount_cents > 0 as a plain check constraint and keeps the "no commission without a positive conversion" rule one FK deep.

0.4 Two Stripe endpoints, not one

Conversion events (checkout.session.completed, invoice.paid, charge.refunded, charge.dispute.*) arrive on the revenue account as events on your account. Payout-side events (account.updated for Express onboarding, payout.failed on the connected account) arrive as events on connected accounts. Stripe delivers these to different endpoint registrations. Build two routes, each refusing the other's shape (§4.2).

0.5 One payout account per workspace, regardless of product count

Express accounts belong to the platform account that created them. Transfers to an affiliate come from that account's balance. HealthyApp, GetBooked, and WhoHire may each collect revenue on a different Stripe account; the affiliate is still paid from one. So: workspaces names the payout Stripe account; products names the revenue account whose webhooks it accepts. Funding the payout account from product revenue is an operational transfer outside this system, surfaced as a number ("liability outstanding vs. payout balance") in the admin, not automated in v1.

1. Data model

Notation. Types are Postgres. ws = workspace_id uuid not null references public.workspaces(id). RLS posture: S = service role only (RLS on, no policies); A-own = affiliate reads rows where the affiliate is themself; W = workspace members (owner/admin) read and, where noted, write. Nothing under S is ever granted to authenticated or anon. Timestamps are timestamptz. Money is bigint cents, always.

1.1 Tenancy and configuration

workspaces — the tenant boundary. One row in v1.

RLS: W read. Writes service role. Column privileges: payout_stripe_account_id withheld from authenticated (0137 §5 pattern — revoke table grant, re-grant columns individually).

workspace_members — (ws, user_id uuid references auth.users, role text check (role in ('owner','admin')), created_at), pk (workspace_id, user_id). RLS: a member reads their own workspace's rows. This is the admin gate; the Activation PRD's finer roles are a later migration that widens the check.

products — a merchant product that emits clicks and conversions.

RLS: W read/write (service role for Stripe columns). stripe_* columns withheld from authenticated via column grants.

product_api_keys — bearer keys for /v1/clicks and /v1/conversions.

(id, ws, product_id, key_prefix text, key_hash text unique, scopes text[] not null check (scopes <@ array['track','convert']), created_by, created_at, last_used_at, revoked_at). The plaintext is shown once at creation and never stored; lookup is by key_hash = sha256(presented). RLS: S. Admin sees key_prefix and dates through a security-definer function, same shape as org_connect_state() in 0137.

1.2 Programs and plan levels

programs — the commission plan for a product.

RLS: W read/write. Affiliates read id, name, product_id, status, cookie_window_days of programs they are enrolled in, via a view.

plan_levels — the rates. Versioned by effective range so a rate change is a new row, never an update (PRD §5.1 "changing rates never rewrites history").

Constraints:

-- THE DEPTH CAP. Two levels is a two-tier affiliate program; three is an
-- MLM in the FTC's framing and brings comp-plan counsel review, income-
-- claim exposure, and state registration questions with it (PRD §2.1).
-- Raising this is a legal decision with a lawyer's name on it, not a
-- settings change. The engine's MAX_DEPTH constant says the same thing
-- in TypeScript; this constraint is what makes a misconfigured program
-- physically unable to produce a third level.
alter table public.plan_levels
  add constraint plan_levels_level_check check (level between 1 and 2);

-- One current rate per (program, level). Postgres range exclusion keeps
-- effective windows from overlapping, so "which rate was in force at
-- occurred_at" always has exactly one answer.
create extension if not exists btree_gist;
alter table public.plan_levels
  add constraint plan_levels_no_overlap exclude using gist (
    program_id with =,
    level      with =,
    tstzrange(effective_from, coalesce(effective_to, 'infinity')) with &&
  );

RLS: W read/write (writes through a function that closes the prior row and opens the new one in one transaction). Affiliates read current levels of their enrolled programs via a view exposing level, kind, value_bp, flat_cents, basis, recurring_months, hold_days.

program_terms — (id, ws, program_id, version int, body_md text, published_at), unique (program_id, version). Compliance req #4.

1.3 Affiliates and the tree

affiliates — one row per person, globally within a workspace.

Constraints and triggers:

-- No self-sponsorship.
alter table public.affiliates
  add constraint affiliates_not_own_sponsor check (sponsor_id is distinct from id);

-- Cycle rejection at the database (PRD §5.2). At depth two a cycle is only
-- ever A→B→A, but the trigger walks the chain anyway so the guarantee does
-- not depend on anyone remembering that.
create or replace function public.affiliates_reject_cycle() returns trigger ...
  -- walk new.sponsor_id → its sponsor → … (max 64 hops, guard against runaway);
  -- raise exception 'sponsor cycle' if new.id is encountered.
create trigger affiliates_reject_cycle before insert or update of sponsor_id
  on public.affiliates for each row execute function public.affiliates_reject_cycle();

Indexes: (workspace_id, sponsor_id) — the downline rollup is this one index; (workspace_id, email_hash); (stripe_account_id) where not null.

RLS: A-own read on a column-restricted view (display_name, status, payout_status, payouts_enabled, created_at; never sponsor_id of others, never email_hash, never stripe_account_id). Downline is not a row policy on this table — it is my_downline() (security definer) returning display_name, joined_at, products[], activity_bucket for rows where sponsor_id = my affiliate id. W read all columns except stripe_account_id (tail only via function). Writes service role.

affiliate_identifiers — fingerprints for self-referral detection. (ws, affiliate_id, kind text check (in ('email_hash','card_fingerprint','stripe_customer')), value text, source text, created_at), pk (workspace_id, affiliate_id, kind, value), index (workspace_id, kind, value). Written when the affiliate is also a customer of any product (their cus_… and card fingerprint from their own charges), and at signup for email. RLS: S.

affiliate_programs — enrollment.

(id, ws, affiliate_id, program_id, status check (in ('pending','active','suspended')), terms_version_id references program_terms, terms_accepted_at not null, created_at), unique (affiliate_id, program_id), composite FKs on both parents. RLS: A-own read; W read/write.

referral_codes — (ws, code citext, affiliate_id, created_at), pk (workspace_id, code), unique (workspace_id, affiliate_id) in v1 (one code per person; relax later), check (code ~ '^[A-Z0-9][A-Z0-9_-]{2,23}$'). Immutable: before update trigger raises. Never deleted; anonymization leaves the code pointing at the anonymized row so old links keep resolving to "no affiliate" rather than 404 (PRD §6.1 "a dead link is worse than an ugly one"). RLS: A-own read; S write.

1.4 Tracking (high-write, partitioned)

clicks — partitioned by month on occurred_at from the first migration. Never updated, never deleted inside retention.

primary key (id, occurred_at) — Postgres requires the partition key in the PK. Indexes: (workspace_id, affiliate_id, occurred_at desc), (workspace_id, product_id, occurred_at desc), (ip_hash, occurred_at) where ip_hash is not null (volume-from-one-IP flag).

Partitions: clicks_YYYYMM, created three months ahead by a scheduled function ensure_partitions(); a clicks_default partition catches anything with no home so a late write never fails. Same for conversions.

RLS: A-own read via (affiliate_id = my id) on a view that excludes ip_hash, ua_hash; S write. No authenticated INSERT/UPDATE/DELETE grants at all.

Volume justification: clicks are the only table where 100k+ rows/month is plausible early. conversions is 1–3% of that, but the PRD names both as partitioned and the cost of doing it on day one is one composite FK, so both are.

conversions — partitioned by month on occurred_at. Strictly positive sales. Immutable after insert.

primary key (id, occurred_at); unique (workspace_id, product_id, idempotency_key, occurred_at) — note the partition key must be in it, so the idempotency check also runs as an explicit select before insert, not only on conflict (§3.4). unique (stripe_invoice_id, occurred_at) where stripe_invoice_id is not null. Indexes: (workspace_id, affiliate_id, occurred_at desc), (workspace_id, customer_attribution_id, occurred_at), (stripe_charge_id) where not null.

RLS: A-own read on a view (id, occurred_at, product_id, event, amount_cents, payment_ordinal — never customer_ref, hashes, or decision); W read all; S write. before update or delete trigger raises 'conversions are immutable'.

customer_attribution — the lock (PRD §6.5).

unique (workspace_id, product_id, customer_ref) where released_at is null. Composite FK on affiliate. RLS: A-own read of their own locks (count and dates only); W read; S write. The insert is on conflict do nothing followed by a read — the first writer wins and everyone else sees who did.

1.5 The ledger

commissions — append-only money. Every row names a conversion.

Constraints — the two the task asks to see spelled out, plus the sign rules:

-- NO MONEY WITHOUT A CONVERSION. The FK is the rule (PRD §5.3, §11.2).
-- `conversions.amount_cents > 0` is checked on that table, so by
-- construction every commission row traces to a positive payment. There
-- is no commission table column for "recruitment" and no code path that
-- inserts here without a conversion — a recruiting link only ever writes
-- affiliates.sponsor_id.
alter table public.commissions
  add constraint commissions_conversion_fk
    foreign key (conversion_id, conversion_occurred_at)
    references public.conversions (id, occurred_at);

-- Adjustments and reversals point at a prior row; earn rows point at none.
alter table public.commissions add constraint commissions_kind_shape check (
     (kind = 'earn'       and amount_cents > 0 and reverses_commission_id is null and adjusts_commission_id is null)
  or (kind = 'reversal'   and amount_cents < 0 and reverses_commission_id is not null and adjusts_commission_id is null)
  or (kind = 'adjustment' and amount_cents <> 0 and adjusts_commission_id is not null and reason is not null and created_by is not null)
);

-- Flagged rows carry a reason; nothing else does.
alter table public.commissions add constraint commissions_flag_reason check (
  (state = 'flagged') = (flag_reason is not null)
);

-- Paid rows name their payout; nothing else does.
alter table public.commissions add constraint commissions_payout_link check (
  (state = 'paid') = (payout_id is not null)
);

The immutability and state-machine trigger:

-- Money and lineage never change. State moves forward along §7.2's edges
-- and nowhere else. A violation is an exception, not a silent no-op, so a
-- bug in a job surfaces in the log the first time it runs.
create or replace function public.commissions_guard_update() returns trigger
language plpgsql as $$
begin
  if new.amount_cents <> old.amount_cents or new.conversion_id <> old.conversion_id
     or new.affiliate_id <> old.affiliate_id or new.level <> old.level
     or new.plan_level_id <> old.plan_level_id or new.kind <> old.kind
     or new.reverses_commission_id is distinct from old.reverses_commission_id
     or new.adjusts_commission_id  is distinct from old.adjusts_commission_id
     or new.created_at <> old.created_at then
    raise exception 'commissions are append-only (id %)', old.id;
  end if;
  if new.state = old.state then return new; end if;
  if not (
       (old.state = 'flagged'  and new.state in ('pending','voided'))
    or (old.state = 'pending'  and new.state in ('approved','voided','flagged'))
    or (old.state = 'approved' and new.state in ('paid','voided','flagged'))
    or (old.state = 'paid'     and new.state = 'reversed')
  ) then
    raise exception 'illegal commission transition % → % (id %)', old.state, new.state, old.id;
  end if;
  return new;
end $$;
create trigger commissions_guard before update on public.commissions
  for each row execute function public.commissions_guard_update();
create rule commissions_no_delete as on delete to public.commissions do instead nothing;

Notes on the edges: pending → flagged and approved → flagged exist because a fraud rule (chargeback velocity) can fire after earning. paid → reversed is the only exit from paid, and it does not move money — the negative reversal row does. A reversal row is born approved (it nets at the next payout) and itself moves approved → paid when netted.

Indexes: (workspace_id, affiliate_id, state), (workspace_id, state, hold_until) where state = 'pending' (the approval sweep), (conversion_id), (payout_id) where payout_id is not null, (reverses_commission_id) where not null.

RLS: A-own read of a view exposing id, conversion_id, level, kind, amount_cents, state, hold_until, created_at, approved_at, paid_at — not flag_reason text beyond a boolean is_under_review, not the sponsor's rows. (An L2 row has affiliate_id = sponsor; the recruit never sees it. An L1 row has affiliate_id = recruit; the sponsor never sees it. This is PRD §9.4's "never their earnings" enforced at the row.) W read all. S write. Revoke all authenticated write grants explicitly.

commission_events — every transition, append-only. (id bigserial, ws, commission_id, from_state, to_state, actor uuid, source text check (in ('engine','hold_sweep','payout_run','webhook','admin')), note text, created_at). RLS: A-own read for their own commissions' events (state columns only); W read; S write.

fraud_flags — exceptions-queue items (PRD §7.4). (id, ws, kind text check (in ('self_referral','duplicate_customer','chargeback_velocity', 'refund_rate','click_velocity','ip_velocity','sponsor_suspended')), affiliate_id, conversion_id, commission_id, evidence jsonb not null, status check (in ('open','approved','rejected')), resolved_by, resolved_at, resolution_note, created_at). Index (workspace_id, status, created_at). RLS: W read/write (status, resolution columns only); S insert.

1.6 Payouts

payout_runs — one batch. (id, ws, scheduled_for date, started_at, finished_at, status check (in ('draft','running','completed','failed')), total_cents bigint, affiliate_count int, created_by, notes). RLS: W.

payouts — one per affiliate per run.

unique (payout_run_id, affiliate_id). RLS: A-own read (all columns except stripe_destination_account, shown as tail); W read; S write.

payout_items — (payout_id, commission_id, amount_cents), pk (payout_id, commission_id), unique (commission_id) — a commission row is in at most one payout, ever. RLS mirrors payouts.

1.7 Webhooks, keys, audit

stripe_webhook_events — 0137 §4, extended for two endpoints.

create table public.stripe_webhook_events (
  id            text primary key,          -- evt_… ; the whole idempotency story
  workspace_id  uuid not null references public.workspaces(id),
  endpoint      text not null check (endpoint in ('platform','connect')),
  type          text not null,
  -- Which Stripe account the event *happened on*. For the platform
  -- endpoint this is the revenue account we verified the signature for;
  -- for the connect endpoint it is event.account (the Express account).
  stripe_account_id text,
  product_id    uuid,                       -- resolved for platform events
  received_at   timestamptz not null default now(),
  handled_at    timestamptz,
  -- 'ok' | 'ignored' | 'unmatched' | 'connect_event_rejected' |
  -- 'platform_event_rejected' | 'duplicate_conversion' | error text.
  result        text,
  payload       jsonb                      -- retained 90 days by a sweep
);
alter table public.stripe_webhook_events enable row level security;  -- no policies

The insert of id happens before any handler runs, exactly as 0137 and the vera-wellness route do; a duplicate delivery hits the PK, loses the race, and returns 200 without touching anything.

api_requests — idempotency memo for /v1/conversions. (ws, product_id, idempotency_key, request_hash bytea, response_status int, response_body jsonb, created_at), pk (workspace_id, product_id, idempotency_key). Replays return the stored response; a replay with a different body returns 409 (§3.4). RLS: S. Swept after 30 days.

audit_log — (id bigserial, ws, actor uuid, actor_kind check (in ('admin','affiliate','system','webhook')), action text, object_table text, object_id text, before jsonb, after jsonb, reason text, ip_hash bytea, created_at). Append-only (delete rule as on commissions). Actions in this scope: reparent_affiliate, suspend_affiliate, release_customer_lock, commission_adjust, flag_resolve, payout_run_release, payout_mark_manual, api_key_create, api_key_revoke, plan_level_change. RLS: W read; S write.

1.8 Grants summary

After the initial migration: revoke all on all tables in schema public from anon, authenticated; then grant select on the named views only, and insert/update on nothing in this scope. Every write path in this section is the service role (API routes, webhook routes, scheduled functions). This is stricter than vera-wellness's default and is deliberate: there is no affiliate-facing write in phases 1/2/5 that is not a Stripe or API call.

2. Commission engine

Lives in packages/core/src/engine/. Pure: no I/O, no clock, no database. The caller loads inputs, the engine returns rows and an explanation, the caller writes both in one transaction.

2.1 Signature

export const MAX_DEPTH = 2 as const;
// Two levels is a two-tier affiliate program. Three is an MLM in the FTC's
// framing (PRD §2.1). plan_levels.level has a CHECK (1..2) that mirrors
// this; changing either without the other is a bug, and changing both is a
// legal decision that needs counsel's name in the commit message.

export type ConversionInput = {
  id: string;
  occurredAt: Date;
  programId: string;
  affiliateId: string | null;          // null ⇒ engine returns [] with reason
  customerAttributionId: string | null;
  paymentOrdinal: number;              // 1 = first commissionable payment
  amountCents: bigint;                 // > 0, already net of tax/discount
  event: 'purchase' | 'subscription_created' | 'subscription_renewed';
  customerEmailHash: Uint8Array | null;
  paymentFingerprint: string | null;
};

export type ProgramInput = {
  id: string;
  status: 'draft' | 'active' | 'closed';
  l2RequiresSponsorEnrollment: boolean;
  planLevels: PlanLevel[];             // ALL versions; engine picks by occurredAt
};

export type PlanLevel = {
  id: string; level: 1 | 2; kind: 'percent' | 'flat';
  valueBp: number; flatCents: bigint | null;
  basis: 'first_payment' | 'recurring' | 'lifetime';
  recurringMonths: number | null; holdDays: number;
  effectiveFrom: Date; effectiveTo: Date | null;
};

export type ChainMember = {
  affiliateId: string;
  status: 'pending' | 'active' | 'suspended' | 'closed';
  enrolledInProgram: 'active' | 'suspended' | 'none';
  identifiers: { emailHashes: Uint8Array[]; cardFingerprints: string[] };
};

/** [0] is the converting affiliate (L1); [1], if present, their sponsor (L2). Never longer. */
export type SponsorChain = readonly [ChainMember] | readonly [ChainMember, ChainMember];

export type CommissionDraft = {
  affiliateId: string; level: 1 | 2; planLevelId: string;
  kind: 'earn'; amountCents: bigint;
  state: 'pending' | 'flagged'; flagReason: FlagReason | null;
  holdUntil: Date;
};

export type EngineResult = {
  commissions: CommissionDraft[];      // length 0, 1, or 2 — never more
  decision: {
    planLevelsConsidered: { level: 1 | 2; planLevelId: string | null; reason: string }[];
    skipped: { level: 1 | 2; reason: SkipReason }[];
    flags: FlagReason[];
  };
};

export function computeCommissions(
  conversion: ConversionInput,
  program: ProgramInput,
  chain: SponsorChain,
): EngineResult;

Rounding: percent → floor(amountCents * valueBp / 10_000). Floor, never round, so the sum of commissions never exceeds the basis by a cent across levels. BigInt arithmetic throughout.

Hold: holdUntil = occurredAt + planLevel.holdDays days, computed from the conversion's time, not from when the webhook arrived.

2.2 Rules, in evaluation order

1. If conversion.affiliateId is null → [], reason unattributed.
2. If chain.length > MAX_DEPTH → throw. This is unreachable by construction (the loader builds the chain from one sponsor_id hop); the throw is the engine refusing to be the place the cap is lost.
3. For level in 1..min(MAX_DEPTH, chain.length):
   • Pick the PlanLevel with that level whose [effectiveFrom, effectiveTo) contains occurredAt. None → skip, no_rate_in_force. (A program with only an L1 row simply never pays L2.)
   • Basis check against paymentOrdinal: first_payment pays ordinal 1 only; recurring pays ordinal ≤ recurringMonths; lifetime always. Fail → skip, basis_exhausted.
   • Member status: suspended → row created flagged, sponsor_suspended (L2) or affiliate_suspended (L1). closed or pending → skip, member_inactive. Flagged rather than skipped because a suspension under investigation should hold the money visibly, not make it vanish; the admin voids or releases.
   • Enrollment: L1 member must be enrolledInProgram = 'active', else skip not_enrolled (a conversion through a code whose owner is not in this program is recorded, unattributed for money, and surfaced as a flag by the API layer). L2 member needs enrollment only when program.l2RequiresSponsorEnrollment; otherwise the sponsor earns on a product they do not promote (PRD §5.2 assumption, §15 Q4).
   • Self-referral: if customerEmailHash ∈ member.identifiers.emailHashes or paymentFingerprint ∈ member.identifiers.cardFingerprints → row flagged, self_referral. Checked at both levels: a sponsor buying through their recruit's link is the two-tier variant of the same thing.
   • Amount: percent/flat as above. If the result is 0n (tiny basis) → skip rounds_to_zero; never insert a zero row.
4. program.status === 'closed' does not block: existing attributions keep paying recurring under the rates in force. draft → [], program_not_active (the API layer should have refused earlier).

2.3 Edge cases, decided

2.4 Test matrix

Unit tests in packages/core/src/engine/__tests__/, table-driven. Every cell is one test; the file is the spec.

Dimensions:

• Chain: [active], [active, active], [active, suspended], [suspended, active], [active, closed], [active, pending], [active, active-not-enrolled] × l2Requires ∈ {true,false}
• Basis × ordinal: first_payment × {1, 2}; recurring 12 × {1, 12, 13}; lifetime × {1, 60}
• Kind: percent (2500bp on 9900 → 2475; 500bp on 9900 → 495; 500bp on 19 → 0 ⇒ skipped), flat (5000 on 9900 → 5000; flat L2 on tiny basis)
• Rate versions: two L1 rows, occurredAt before/at/after the boundary (at = new row, since ranges are [from, to)); gap with no row in force
• Self-referral: email match at L1; email match at L2; fingerprint match at L1; both absent
• Unattributed: affiliateId: null → []
• Program status: draft, active, closed
• Invariants (property tests, fast-check): commissions.length ≤ 2; levels are distinct; sum(amount) ≤ amountCents for percent-only plans; every draft's planLevelId is a row whose range contains occurredAt; deterministic (same inputs twice → deep-equal output).
• Replay harness: a fixture of 200 synthetic conversions over 18 months across 3 affiliates with one rate change and one suspension, with a golden-file of expected ledger totals per affiliate. This is what gets re-run against production data later when someone disputes a number.

Phase 1 gate: 100% branch coverage on engine/, property tests green, golden file checked in.

3. Attribution

3.1 /r/[code] — the redirect route on the product's domain

Shipped by the SDK, mounted at app/r/[code]/route.ts in each product.

Request: GET /r/{code}?to=/pricing&c=newsletter

1. Validate code against ^[A-Za-z0-9][A-Za-z0-9_-]{2,23}$; fail → 302 to default_destination with no cookie.
2. Validate to: must start with /, must not start with //, must not contain \, @, or a scheme; ≤ 512 chars. Fail → use default. This is the open-redirect guard and is not optional.
3. c (campaign): ≤ 64 chars, [A-Za-z0-9_-], else dropped.
4. Server-side POST {API}/v1/clicks with the product key (§3.2), 800 ms timeout, no retry.
5. Set cookies on the product's domain, then 302 to to:
   • ac_click = <click_id> — HttpOnly; Secure; SameSite=Lax; Path=/; Max-Age=<cookie_window_days*86400>. HttpOnly because checkout reads it server-side (getClickId() uses cookies()), and nothing on the client needs it.
   • ac_ref = <code> — same attributes. The degradation path: if step 4 times out or 5xx's, ac_click is not set but ac_ref still is, and the conversion falls back to ref_code attribution (§3.4). The click is lost; the sale is not.
   • A later click overwrites both (last click wins, PRD §6.5).
6. Response headers: Cache-Control: no-store, Referrer-Policy: strict-origin-when-cross-origin.

Known bot UAs (Googlebot, facebookexternalhit, Slackbot, link preview fetchers): still redirect, still call the API, API marks is_bot = true. Dropping them server-side would hide volume anomalies.

3.2 POST /v1/clicks

Authorization: Bearer ac_live_…           (scope: track)
{ "code": "JWHIST", "destination": "/pricing", "campaign": "newsletter",
  "ip": "203.0.113.9", "user_agent": "…", "referer": "https://instagram.com/…",
  "country": "US" }
→ 200 { "click_id": "uuid", "affiliate_id": "uuid", "cookie_window_days": 90 }
→ 404 { "error": "unknown_code" }           (route still redirects, sets no ac_click)
→ 403 { "error": "affiliate_inactive" }     (suspended/closed; route redirects, no cookie)

Server: resolve key → (workspace_id, product_id); resolve code → affiliate; check affiliate status = 'active' and an active enrollment in an active program for this product (else 403 — a click for a program the person is not in should not plant a cookie); hash IP with the day's salt; insert clicks; return. p95 target < 60 ms on the Postgres write; the route is on the hot path of every affiliate link.

3.3 Cookie spec (summary)

Domain: unset (host-only on the product domain). If a product runs app.example.com and example.com separately, set Domain=example.com via SDK option — document it; do not guess.

3.4 POST /v1/conversions

POST https://api.tellatribe.com/v1/conversions
Authorization: Bearer ac_live_…           (scope: convert)
Idempotency-Key: <merchant event id>       (required, ≤ 255 chars)

{ "click_id": "uuid",                      // preferred
  "ref_code": "JWHIST",                    // fallback
  "customer_ref": "cus_…",                 // required
  "customer_email": "a@b.com",             // optional; hashed on receipt, never stored raw
  "payment_fingerprint": "fp_…",           // optional
  "event": "subscription_created",         // purchase | subscription_created | subscription_renewed
  "amount_cents": 9900,                    // required, > 0
  "gross_cents": 10692,                    // optional
  "currency": "USD",
  "occurred_at": "2026-08-14T18:22:11Z" }

→ 201 { "conversion_id", "attributed": true, "affiliate_id", "attribution_method": "click_id",
        "commissions": [{ "level": 1, "amount_cents": 2475, "state": "pending", "hold_until": "…" },
                        { "level": 2, "amount_cents": 495,  "state": "pending", "hold_until": "…" }],
        "flags": [] }
→ 201 { …, "attributed": false, "reason": "outside_window" | "unknown_click" | "unknown_code" | "no_reference" }
→ 200 <stored response>                    (idempotent replay, identical body)
→ 409 { "error": "idempotency_conflict" }   (same key, different body)
→ 422 { "error": "amount_must_be_positive" } etc.

Processing, in one transaction per request:

1. Idempotency memo first. select from api_requests by (workspace, product, key). Hit with same request_hash → return stored response. Hit with different hash → 409. Miss → insert the memo row (response_status null) so a concurrent duplicate blocks on the PK and then finds the finished response. (Explicit select-then-insert because the conversions unique index includes the partition key and cannot alone serve as a cross-month idempotency check.)
2. Resolve the program: the one active (or closed, for recurring) program for this product whose plan levels have a row in force at occurred_at. Multiple active programs per product is not v1; the API refuses with 422 ambiguous_program so it never silently picks.
3. Customer lock lookup: customer_attribution by (workspace, product, customer_ref) where released_at is null.
   • Found → affiliate_id = lock's affiliate; attribution_method = 'customer_lock'; payment_ordinal = count(prior conversions on this lock) + 1. Ignore click_id/ref_code for money. If the request's click/code resolves to a different affiliate → also open a fraud_flags row duplicate_customer with both affiliate ids as evidence; the conversion's commissions still go to the locked affiliate in pending (the lock is right; the second claim is what's suspicious). PRD §6.5.
   • Not found → first conversion. Resolve the candidate: a. click_id present → load click (must belong to this product and workspace; else unknown_click). Window: occurred_at − click.occurred_at ≤ cookie_window_days else outside_window. Candidate affiliate = click.affiliate_id, method click_id. b. else ref_code present → resolve code; candidate = its affiliate, method ref_code, click_id null. No window check (there is no click time); the cookie's own expiry was the window. c. neither → no_reference. Candidate must be active with an active enrollment in the program, else unattributed with not_enrolled and a fraud_flags-free note. Insert customer_attribution with on conflict do nothing; re-read; if another request won the race, treat as Found above. payment_ordinal = 1.
4. Load the chain: candidate affiliate row + its sponsor_id row (one join) + identifiers for both. That is the whole tree walk.
5. Insert the conversion (affiliate_id may be null).
6. Run the engine (§2) and insert its rows into commissions, each with a commission_events row null → state, source engine. Insert fraud_flags for every flagged draft.
7. Velocity flags (not engine; needs history): click-to-conversion under 5 s and the affiliate has ≥ 3 such in 24 h → click_velocity flag on this conversion and move its rows pending → flagged.
8. Write conversions.decision, finish the api_requests memo, commit. Return 201.

Self-referral and duplicate detection both happen here, at conversion time (PRD §2.4), not at payout.

3.5 Last-click semantics, stated once

Last click wins because the cookie is overwritten on each /r/ hit. The server does not search for "the most recent click by this visitor" — it has no visitor identity and should not build one. The click id presented is the click that wins, provided it is inside the window. After the first conversion the lock makes later clicks irrelevant for that customer.

4. Stripe webhook path

4.1 Events consumed

Platform endpoint (/api/stripe/platform/[productSlug]), events on the revenue account:

Connect endpoint (/api/stripe/connect), events on connected (Express) accounts:

4.2 Scoping — how the HealthyApp topology trap is made impossible

The owner's HealthyApp Stripe account carries two kinds of traffic: platform subscriptions (us charging the coaching business; organizations. stripe_customer_id / stripe_subscription_id) and Connect Standard charges (the coach charging their clients). Affiliate commissions attach to the first and never the second. Four independent guards; any one is sufficient, all four are built:

1. Endpoint registration. The tellatribe platform endpoint is registered in the revenue account's dashboard under Events on your account, never Events on connected accounts. Stripe will therefore never deliver a connected-account event to it. (The Connect endpoint in §4.1 is registered the other way round, on the payout account.)
2. event.account check. The platform handler rejects any event with an account field present, records result = 'connect_event_rejected', returns 200. This is the vera-wellness route's invariant #1 mirrored: there, platform events are rejected on a Connect handler; here, Connect events are rejected on a platform handler.
3. Per-product signing secret and account pin. The route is /[productSlug]; the signature is verified with that product's secret (products.stripe_webhook_secret_ref), and products.stripe_account_id must equal the account the endpoint belongs to (checked once at setup and recorded; a mismatch at setup refuses to save). An event signed by another account's secret fails verification.
4. Carrier shape. The click id is read from metadata.ac_click_id first (§4.3). HealthyApp's Connect checkout already sets client_reference_id to a client profile uuid (lib/billing/connect-checkout.ts:213) — a UUID that would pass a naive format check. The handler therefore (a) only falls back to client_reference_id when metadata.ac_product equals this product's slug, and (b) requires the click to exist in clicks for this product. An unknown UUID is unmatched, not a conversion.

Additionally the platform handler never reads metadata.client_id or metadata.org_id and never writes anything HealthyApp owns. Products call tellatribe; it never calls them (PRD §1).

4.3 How the click id travels through Stripe

At checkout, the product passes both of these on the Checkout Session (HealthyApp's platform checkout already writes metadata[org_id] and subscription_data[metadata][org_id], so this is two more lines in the same form):

metadata[ac_click_id]                    = <ac_click cookie>
metadata[ac_product]                     = "healthyapp"
subscription_data[metadata][ac_click_id] = <ac_click cookie>     // subscription mode only
subscription_data[metadata][ac_product]  = "healthyapp"
client_reference_id                      = <ac_click cookie>     // PRD §6.4 form; accepted as fallback

If only ac_ref is present (click API was down at click time), pass metadata[ac_ref] = <code>; the webhook attributes by ref_code.

subscription_data.metadata is what makes recurring work: Stripe copies it onto the Subscription, and every invoice.paid for that subscription can reach it via invoice.subscription. Without it, month 2 depends entirely on the customer lock — which is fine, but the metadata makes the lock reconstructible if it is ever lost.

getClickId() in the SDK returns { clickId, refCode } from the cookies so the checkout code writes the right one.

4.4 Refunds and disputes → reversal

Reversal of conversion C with refund fraction f = refunded_delta / gross_cents (dispute: f = 1):

For each earn row E on C that is not already fully reversed:

• reversal_cents = −floor(E.amount_cents × f), capped so cumulative reversals on E never exceed −E.amount_cents.
• E in pending/flagged/approved and f = 1 → E → voided (commission_events source webhook). No negative row; nothing was paid.
• E in pending/approved and f < 1 → insert reversal row R (reverses_commission_id = E, state approved, same plan_level_id, same level). E stays as is. Net at payout.
• E in paid → insert R in approved; E → reversed. R nets against the affiliate's next payout. If their balance goes negative it sits (PRD §7.3) until an admin adjustment writes it off.

"Every level" (PRD §7.3) is automatic because the loop is over all earn rows on the conversion, L1 and L2 alike. Reversal rows are written in the same transaction as the webhook event's handled_at.

4.5 Event ledger — the 0137 pattern, explicitly

Order of operations in both webhook routes, copied from app/api/stripe/webhook/route.ts:

1. Read raw body. Verify signature against the per-product (or Connect) secret. No secret configured → 500 and process nothing; there is no dev bypass (0137's companion stripe-signature.ts reasoning).
2. insert into stripe_webhook_events (id, workspace_id, endpoint, type, stripe_account_id, product_id, payload) — before any handler. PK violation → this is a redelivery; return 200 { duplicate: true }.
3. Scope guards (§4.2). Rejected → result set, 200.
4. Handler in one DB transaction. Inside it the conversions idempotency key (stripe:in_…) is a second, independent guard: two different Stripe events describing the same invoice (e.g. invoice.paid and a hypothetical future invoice.payment_succeeded someone adds) cannot create two conversions.
5. update stripe_webhook_events set handled_at = now(), result = ….
6. Always 200 once the signature verified, including on internal failure, with result = the error text. A non-2xx makes Stripe retry a bug for three days (the house route's reasoning, verbatim in spirit). A scheduled webhook_replay picks up rows with result like 'error:%' and handled_at is null after a deploy, re-runs them from payload, and is the only path that processes an event id twice.

5. Payouts

5.1 Stripe Connect Express onboarding

On the payout Stripe account (workspaces.payout_stripe_account_id).

1. Affiliate taps Set up payouts. Server action (service role): POST /v1/accounts with type=express, country from the affiliate's choice, capabilities[transfers][requested]=true, business_type= individual default, metadata[affiliate_id], metadata[workspace_id]. Store acct_… on affiliates.stripe_account_id, payout_status = 'pending'. Idempotency key acct-create:{affiliate_id} so a double tap creates one account.
2. POST /v1/account_links with type=account_onboarding, refresh_url = /settings/payouts?refresh=1, return_url = /settings/payouts?return=1. Redirect. Links expire in minutes; refresh_url mints a new one.
3. Return page does not trust the return: it calls GET /v1/accounts/ {acct} and writes the real state, then relies on account.updated for subsequent changes (0137's "ask Stripe rather than assume", payout_last_synced_at).
4. payouts_enabled is the one boolean the payout run reads. Stripe collects identity, bank, and W-9/W-8 inside the hosted flow and handles 1099-NEC filing; we show tax status from account.requirements and the workspace's tax_reporting_threshold_cents as "Stripe files a 1099-NEC once your yearly payouts pass $X" — the X is read from the config column, never typed into a template.
5. Express dashboard login link (POST /v1/accounts/{acct}/login_links) is exposed as Manage bank details so bank changes never pass through tellatribe.

No PayPal in this scope; payouts.rail = 'paypal'|'csv' rows are created by the run for affiliates with payout_status <> 'active' only when an admin flips them to a manual rail, and completed by hand with a reference.

5.2 Payout run algorithm

Scheduled on the 1st (payout_schedule = 'monthly_1st') or on demand. Always two phases so the admin sees the batch before money moves (PRD §10 "review the batch total before releasing").

Phase A — assemble (payout_runs.status = 'draft')

-- Per affiliate: everything approved and not yet in a payout.
select affiliate_id,
       sum(amount_cents) filter (where amount_cents > 0) as gross_cents,
       sum(amount_cents) filter (where amount_cents < 0) as reversal_cents,
       sum(amount_cents)                                   as net_cents,
       count(*)                                            as item_count,
       array_agg(id)                                       as commission_ids
  from public.commissions
 where workspace_id = $1 and state = 'approved' and payout_id is null
 group by affiliate_id;

Then, per affiliate, in order:

1. net_cents ≤ 0 → skip; nothing changes; the rows stay approved and the negative carries forward. Recorded in the run's notes as carried_negative.
2. net_cents < workspaces.min_payout_cents → skip, below_minimum. Rows stay approved; balance rolls forward.
3. affiliates.payouts_enabled = false or status <> 'active' → skip, not_onboarded / inactive; enqueue the "finish payout setup" notification (PRD §8 step 4). Rows stay approved.
4. Otherwise create payouts row (status = 'pending', net_cents, stripe_destination_account snapshot) and payout_items for every commission id, and update commissions set state = 'paid', payout_id = $p, paid_at = now() where id = any($ids) and state = 'approved' and payout_id is null — asserting the row count equals item_count, else roll back that affiliate (a concurrent reversal landed; they go in the next run). commission_events source payout_run.

Rows are marked paid at assembly, not at send, so a second run started by accident cannot select them. If the admin discards the draft, the run's payouts are deleted and the rows return to approved (payout_id = null, paid_at = null) — the one place paid → approved is allowed, and only via the discard_payout_run() function, which also writes audit_log. (Implement as a security-definer function that disables the guard trigger for its own transaction via set local; do not loosen the trigger.)

Phase B — release (running → completed|failed)

Admin reviews total and count, clicks release. For each pending payout:

POST /v1/transfers
  amount           = net_cents
  currency         = usd
  destination      = stripe_destination_account
  transfer_group   = payout.id
  metadata[payout_id], metadata[affiliate_id], metadata[workspace_id]
Idempotency-Key: payout:{payout.id}

• 2xx → status = 'sent', stripe_transfer_id, sent_at.
• 4xx (account restricted, insufficient platform balance, invalid destination) → status = 'failed', failure_code/message; every payout_items commission returns to approved, payout_id = null (commission_events source payout_run, note = failure code). PRD §8 step 6: a bad bank detail costs a retry, not a reconstruction. insufficient_funds additionally halts the run (payout_runs.status = 'failed') — continuing would fail every remaining affiliate for the same reason. The exceptions queue gets one item per failed payout.
• Network timeout → re-issue with the same idempotency key before deciding; Stripe returns the original result. Never mark failed on a timeout alone.

Run finishes completed when every payout is sent or failed. total_cents = sum of sent.

Statement: statement_period_* bounds the commission rows' created_at; the per-payout statement page lists payout_items joined to commission → conversion (product, date, level, amount) — PRD §8's traceability chain, one query.

5.3 Failure handling after send

• transfer.reversed on the payout account (platform reversed it) → payout returned, exceptions item, rows stay paid — a human decides whether to re-send or to book adjustment rows. Automatic flipping of paid rows after money has left is how ledgers lie.
• payout.failed on the Express account (their bank bounced) → §4.1; the balance is in their Stripe account; they fix the bank in the Express dashboard and Stripe retries. No ledger change.
• Transfer succeeded but payouts write failed (crash between Stripe and DB) → the idempotency key makes the next release attempt return the same transfer; the reconciler job (transfers.list by transfer_group prefix, daily) catches any pending payout with an existing transfer.

5.4 What payout and payout_items carry

Spelled out in §1.6. The short version: a payout is one Stripe Transfer to one affiliate for one run, with gross/reversal/net, the destination account snapshot, the transfer id, and status; a payout_item is one commission row's membership in that payout with its signed amount. Sum of items = net. unique (commission_id) on items is the "a dollar is paid once" invariant.

6. SDK surface — @affiliate-coach/sdk

Three calls, matching PRD §12. Package in packages/sdk, ESM + CJS, zero runtime deps beyond fetch. Peer: next >= 15 for the /next entry only.

// 1. The redirect route — app/r/[code]/route.ts
export { GET } from '@affiliate-coach/sdk/next'
// Reads AFFILIATE_COACH_API_KEY (scope: track) and optional
// AFFILIATE_COACH_API_URL from env. Implements §3.1 exactly. Optional
// escape hatch for a non-default cookie domain or allowlist:
//   import { createRedirectHandler } from '@affiliate-coach/sdk/next'
//   export const GET = createRedirectHandler({ cookieDomain: 'example.com' })

// 2. At checkout — server component / server action / route handler
import { getClickId } from '@affiliate-coach/sdk/next'
const { clickId, refCode } = await getClickId()   // reads cookies(); both may be null
// then on the Checkout Session: metadata.ac_click_id = clickId ?? undefined,
// metadata.ac_ref = refCode ?? undefined, metadata.ac_product = '<slug>',
// subscription_data.metadata likewise, client_reference_id = clickId ?? undefined.
// The SDK exports `stripeCheckoutFields({ clickId, refCode, product })`
// returning that flat record so the four lines are one spread.

// 3. Manual conversion — only when not on the Stripe path
import { AffiliateCoach } from '@affiliate-coach/sdk'
const ac = new AffiliateCoach({ apiKey: process.env.AFFILIATE_COACH_API_KEY! })
await ac.convert({
  clickId, refCode,                  // one of these
  customerRef: 'cus_…',
  customerEmail: 'a@b.com',          // optional, hashed by the API
  event: 'subscription_created',
  amountCents: 9900,
  idempotencyKey: 'order_8812',      // required
  occurredAt: new Date(),            // optional, default now
})
// Returns the §3.4 body. Throws AffiliateCoachError with .status and .code on 4xx/5xx
// except 200/201. Retries 5xx and network errors 3× with jitter; never retries 4xx.

Non-goals for the SDK: no client-side script, no pixel, no React component. getClickId is server-only and throws if imported in a client bundle.

Versioning: Accept: application/vnd.affiliate-coach.v1+json is sent; the API path carries /v1/. A key's last_used_at updates on each call.

7. Build sequence for this scope

Sizes: S ≤ 1 day, M 2–4 days, L 1–2 weeks. Gates are bold: nothing after a gate starts until the gate passes.

Parallelism: 8–9 run alongside 4–7. 14 and 18 are small and can be written with 6. Everything else is serial through the gates. Rough total for this scope: 8–10 engineer-weeks, of which Phase 1 (items 1–11) is about half — as the PRD intends.

8. Open decisions

Resolved Oct 3, 2026 — see BUILD-PLAN.md §5. Q1 net; Q2 LOCK at first conversion (snapshot the L1 and L2 plan_level_id on customer_attribution at lock time and evaluate recurring payments against the snapshot; the engine takes the snapshot as input); Q3 proportional; Q7 workspace minimum; Q8 a NEW tellatribe Stripe account is the payout account (add a monthly funding transfer from each product's revenue account to the payout run, and a ledger-vs-funding reconciliation line); Q9 flag only; Q10 13 months. Also new: a fee row kind (negative, references a publish_job) for the X per-post charge, netted in the payout run like a reversal, available only while the affiliate's pending+approved balance is positive.

Stated as questions, each with the default this plan assumes.

1. Commission basis: net of tax and discounts, or gross? Default: amount_paid − tax after discounts. Paying commission on sales tax is paying commission on money you never kept. Change = one line in the webhook amount derivation; the API caller already chooses.
2. Terms evaluated at each payment, or locked at first conversion? This plan evaluates the plan level in force at each occurred_at (PRD §5.1 literal reading). The alternative — snapshot plan_level_id per level on customer_attribution so an affiliate's recruit pays under the terms that existed when they were recruited — is friendlier to affiliates and harder to explain in the ledger. Default: evaluate. Schema supports either without change (add two nullable columns to the lock if the answer flips).
3. Partial refunds: proportional or full reversal? Default: proportional, floored, per level. Full reversal on any refund is simpler and harsher. Dispute is always full.
4. Dispute won after reversal: automatic reinstatement? Default: no — an exceptions item; admin books adjustment rows if warranted. Automatic reinstatement needs a fourth kind and a second guard path; not worth it until it happens twice.
5. Multiple active programs per product (e.g. launch + evergreen)? PRD §5.1 says one product may have several programs over time. Default: the API refuses ambiguity (422). If simultaneous programs are wanted, the referral code or link must carry the program, which changes the link anatomy.
6. Does a suspended sponsor's L2 get flagged or skipped? Default: flagged (money visible, admin decides). Skipping is cleaner if suspensions are always terminal.
7. Minimum payout per workspace or per program? Default: workspace ($50), because the affiliate is one person paid once. PRD §10 lists it under program configuration; if that stands, move the column and take the max across programs at run time.
8. Which Stripe account is the payout account in v1? Default: the HealthyApp revenue account, because it already has the balance the commissions came from and avoids a funding step. Commercially each workspace brings its own (§0.5). Needs Jonathan's confirmation and that account's Connect settings to allow Express.
9. Chargeback-velocity response: flag only, or auto-suspend? Default: flag the affiliate's open rows and open an exceptions item; never auto-suspend. PRD §7.4 says flags are "not a punishment".
10. Click retention. Default: raw clicks partitions kept 13 months, then dropped (the conversion carries everything the ledger needs). Needs a decision before the first partition ages out, not before launch.
11. ac_ref fallback cookie — keep or drop? It is what makes a click-API outage lose stats instead of sales. Default: keep. If the concern is that a code-only attribution is weaker evidence in a dispute, attribution_ method = 'ref_code' already labels it.

Rendered from build-plan/01-ledger-tracking-payouts.md. Edit the source, not this page.