Phase 1, Data Schema & Sync Map (the build blueprint)

How to read this

Each "entity" below is one table in the database. For each, there's a plain-language purpose, then a field list. The custom system is the single source of truth for everything here; GoHighLevel (GHL) holds its own copy of a few facts and we sync a minimal set both ways (see "The GHL sync map").

Money is stored to the cent, in USD. Dates are stored with a timezone. "Link to X" means the field holds the id of a record in another table (how tables connect).

---

The big picture

One partner refers people. Some of those referrals become paying clients. Each payment a client makes earns the partner 10%. Those 10% amounts get paid out to the partner, over and over, for the life of the client. In record terms:

partner ─┬─ agreement            (their one signed contract)
         │
         └─ referral ─── (converts) ─── client_payment ─── commission ─┐
              (a person       (the referred          (each payment      │
               they               person becomes      the client         │
               introduced)        a client)           makes)             │
                                                                         ▼
                                                                      payout ──► Stripe ──► partner's bank
                                                              (one payout can bundle
                                                               several commissions;
                                                               Melanie releases it)

• A partner has exactly one agreement (their signed reciprocal referral contract).
• A partner has many referrals (each person they introduce to Melanie).
• A referral, once it converts into a paying client, has many client_payments (monthly fees, renewals, expansions, every payment that client makes).
• Each client_payment creates one commission (10% owed to the partner). Clawbacks are negative entries in the same commission ledger.
• Commissions get bundled into payouts (the actual Stripe transfers). One payout can cover several commissions, because Melanie releases them on her own schedule.

Phase 1 is Direction A only: partners refer clients IN, Accelerate CMO pays 10% OUT. The contract's reverse direction (Accelerate CMO refers out and collects 10% from a partner) is deliberately not modeled here.

---

Source-of-truth split (recap)

• Custom database (this schema) owns: partners, agreements, referrals/attribution, client payments, commissions, payouts, everything the partner sees and all money logic.
• GHL owns: the sales/referral pipeline, the underlying contact records, and nurture email campaigns.
• QuickBooks Online owns: client invoicing and payment collection, it is the source of the "invoice paid / payment received" signal (money IN).
• Stripe: partner payouts (money OUT).
• Synced (minimal, one owner per fact): see "The sync map" at the end.

---

Entity 1, partners

Purpose: the referral partner (the person the contract calls "Client"). Their identity, current status, portal login, and how they get paid.

---

Entity 2, agreements

Purpose: the one signed reciprocal referral agreement per partner, signed through SignWell. Holds the legal record and the exact values captured at signing.

---

Entity 3, referrals

Purpose: a person a partner introduces to Accelerate CMO, tracked from introduction through (hopefully) becoming a paying client. This is where attribution and the 180-day window live. When a referral converts, this same record carries the client relationship; each payment hangs off it.

Note on renewals/expansions: the contract pays 10% of Lifetime Value including renewals, so a renewal or expanded scope is simply another client_payment on the same referral, not a new referral. (Edge case to confirm: if a partner separately re-refers the same company for a distinct new engagement later, do we treat it as a new referral? Flagged in open questions.)

---

Entity 4, client_payments

Purpose: each payment a converted referral (the client) actually makes to Accelerate CMO. This is the event that earns the partner their 10%. It exists whether the client paid digitally or by check.

---

Entity 5, commissions (the commission + clawback ledger)

Purpose: the running ledger of what the partner is owed. One positive entry per received client payment (10% of its gross). Clawbacks are negative entries. Grouping entries into a payout is how they get paid.

Clawback rule (Section 3A): if a client is refunded or the engagement is terminated within 60 days of that client's start date, Accelerate CMO may deduct the corresponding referral fee from any future payout. In data terms: create a negative clawback entry; it nets against the partner's next payout. If the partner has no upcoming payouts to net against, the balance goes negative and carries forward (flagged in open questions for how Melanie wants to handle a partner who's already been paid).

---

Entity 6, payouts

Purpose: an actual transfer of money to a partner via Stripe. Bundles one or more payable commission entries. Melanie releases these on her own schedule (or sets auto-release); the contract allows 60 days from each client payment.

---

Entity 7, event_log

Purpose: an append-only record of everything that happened (webhooks received, statuses changed, syncs to/from GHL, payouts released). Needed because this is a money system, and the contract even references "real-time tracking of referral activity." Also the backbone for debugging syncs.

---

The GHL sync map (exact)

Only these facts cross the boundary. One owner per fact; no fragile full mirroring.

Into the custom app: 1. [GHL] Partner reaches "Made a Move (attended workshop / Made Intro)" → GHL workflow fires an outbound webhook → custom app creates the partners record (pre-filled from GHL standard fields) and starts onboarding (sends the SignWell agreement, which opens in a new tab). The single wire that kicks off the partner experience. Trigger = entering the stage; manual jumps count. 1b. [GHL] Melanie marks a lead's "Referring Partner" field → GHL workflow webhook → custom app matches the value to an active partner and creates + acknowledges the referrals record (the field-set IS the acknowledgment; the 180-day clock starts that day). No manual referral entry exists in the admin screen. 2. [QBO] A client invoice is paid → QuickBooks Online "invoice paid" webhook → custom app records a client_payments row as received for the matching referral, making the commission payable. Checks QBO can't auto-detect are recorded by Melanie in QBO (never in the admin screen), which fires the same webhook, one door for all money in. 3. [GHL] A referred deal closes (won/lost + close date) → custom app converts or expires the referral and records whether the close fell inside or outside the 180-day window.

Out to GHL: 1. Partner signs + becomes active → tag the GHL contact Active Referral Partner; the card moves to Signed Referral Agreement at contract completion, then on to Nurturing at portal activation (enrolls the nurture campaign). Stage ids are wired from her live pipeline. 2. Partner status change (paused / ended) → update the tag. 3. Payout-setup status → tag Payout Setup Incomplete / Payout Setup Complete as the partner's Stripe onboarding changes.

QBO ↔ GHL: a paid QBO invoice also marks the corresponding GHL deal paid, so Melanie's sales view stays current.

GHL custom fields needed: GHL has zero custom fields today. The build creates five: Referring Partner (a DROPDOWN kept in sync with active partners, setting it is also what creates the referral, per rev 4), Referral Acknowledged Date, Referral Window Expires, Referral Window Outcome, and Referral Last Payment Date. Pending the custom-field edit scope on the API token (one-time setup).

Not synced: the referral / commission / payout ledger. It lives only in the custom database (GHL's affiliate manager is deliberately not used, see the architecture memory and map).

GHL contact fields available for pre-fill

Confirmed live 2026-07-17: her GHL has zero custom fields, so the handoff pre-fills a partner record from GHL's standard contact fields only: firstName, lastName, email, phone, companyName, address1 / city / state / postalCode / country, source, tags, assignedTo, website. Everything partner-specific (payout details, tax status, signed-contract values) is collected by the custom tool at intake/signing, not read from GHL.

---

Key contract rules encoded (traceability)

---

← All documents