Commissions module architecture

Database schema, calculation engine, and statement lifecycle.

Schema: the 9 tables

All in packages/db/src/schema/commissions/. No RLS — these are MSP data, not tenant data.

`commission_reps`

External sales reps or linked MSP staff. Source: commission-reps.ts.

sql

commission_reps (
  id                         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id                    text REFERENCES "user"(id) ON DELETE SET NULL,
  name                       varchar(255) NOT NULL,
  email                      varchar(255) NOT NULL,
  phone                      varchar(50),
  tax_id                     varchar(100),
  country                    varchar(2),
  payment_method             varchar(20),
  payment_details            jsonb,
  default_recurring_percent  numeric(5,2),
  default_recurring_fixed_cents integer,
  default_one_time_amount_cents  integer,
  default_basis              varchar(10),
  manager_id                 uuid REFERENCES commission_reps(id) ON DELETE SET NULL,
  override_percent           numeric(5,2) NOT NULL DEFAULT 0,
  ref_code                   varchar(50) UNIQUE,
  status                     varchar(20) NOT NULL DEFAULT 'active',
  notes                      text,
  created_at                 timestamp NOT NULL DEFAULT now(),
  updated_at                 timestamp NOT NULL DEFAULT now(),

  UNIQUE INDEX on email,
  UNIQUE INDEX on user_id WHERE user_id IS NOT NULL,
  INDEX on status,
  INDEX on manager_id
)

Key columns:

user_id: optional link to a system user. Allows creating the rep before inviting them to the portal. null for external reps without an account yet.
manager_id: self-FK for the MLM hierarchy. null = top-level rep.
override_percent: % this rep earns on the commissions of their direct and indirect reports.
ref_code: unique slug auto-generated when creating the rep (e.g., maria-gonzalez). Used in landing URLs.
default_*: default terms applied when creating assignments (cascade resolver).

`commission_assignments`

Link between a tenant and the reps who earn commission for them. Source: commission-assignments.ts.

sql

commission_assignments (
  id                         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id                  uuid NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
  recurring_percent          numeric(5,2) NOT NULL DEFAULT 0,
  recurring_fixed_cents      integer NOT NULL DEFAULT 0,
  one_time_amount_cents      integer NOT NULL DEFAULT 0,
  basis                      varchar(10) NOT NULL DEFAULT 'gross',
  starts_at                  date NOT NULL,
  ends_at                    date,
  one_time_paid_at           date,
  recurring_percent_base     numeric(5,2),
  recurring_percent_phones   numeric(5,2),
  recurring_percent_lines    numeric(5,2),
  recurring_percent_overage  numeric(5,2),
  status                     varchar(20) NOT NULL DEFAULT 'active',
  notes                      text,
  created_at                 timestamp NOT NULL DEFAULT now(),
  updated_at                 timestamp NOT NULL DEFAULT now(),

  INDEX on (tenant_id, status),
  CHECK recurring_percent > 0 OR recurring_fixed_cents > 0 OR one_time_amount_cents > 0
)

Key columns:

recurring_percent: % applied to the tenant's total revenue (legacy/fallback mode when there are no per-component percentages).
recurring_percent_base/phones/lines/overage: % per component (F-CMS-3). If any is set, the calculator enters breakdown mode. If all are null, uses recurring_percent on the total.
one_time_paid_at: set when the statement containing the bonus is finalized. Prevents duplication.
basis: gross (on gross revenue) or net (deducting Stripe fees).
Has no commission_rep_id — reps are in commission_participants.

`commission_participants`

N-ary splits: the reps participating in an assignment with their percentage. Source: commission-participants.ts.

sql

commission_participants (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  assignment_id       uuid NOT NULL REFERENCES commission_assignments(id) ON DELETE CASCADE,
  commission_rep_id   uuid NOT NULL REFERENCES commission_reps(id) ON DELETE RESTRICT,
  share_percent       numeric(5,2) NOT NULL,
  role                varchar(30),
  created_at          timestamp NOT NULL DEFAULT now(),
  updated_at          timestamp NOT NULL DEFAULT now(),

  UNIQUE on (assignment_id, commission_rep_id),
  INDEX on assignment_id,
  INDEX on commission_rep_id,
  CHECK share_percent > 0 AND share_percent <= 100
)

Key columns:

share_percent: percentage of the assignment's total commission that belongs to this rep. The sum of all participants in an assignment must be exactly 100 (validated by a DEFERRABLE trigger constraint in the DB).
role: free-form informational text. Doesn't affect the calculation. Typical values: 'AE', 'SDR', 'partner', 'primary'.

`revenue_events`

Income ledger per tenant. One row per payment event. Source: revenue-events.ts.

sql

revenue_events (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id             uuid NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
  period                varchar(7) NOT NULL,          -- 'YYYY-MM'
  original_currency     varchar(3) NOT NULL,          -- ISO 4217
  original_amount_cents integer NOT NULL,             -- can be negative (refunds)
  fx_rate               numeric(18,8) NOT NULL DEFAULT 1,
  amount_usd_cents      integer NOT NULL,             -- snapshot: round(original * fx_rate)
  source                varchar(20) NOT NULL,         -- 'stripe_invoice' | 'wire' | 'check' | 'manual' | 'other'
  source_ref            text NOT NULL,
  component             varchar(20),                  -- 'base' | 'phones' | 'lines' | 'overage' | 'other' | null
  recorded_by           text REFERENCES "user"(id) ON DELETE SET NULL,
  notes                 text,
  created_at            timestamp NOT NULL DEFAULT now(),

  UNIQUE on (source, source_ref),
  INDEX on (tenant_id, period),
  INDEX on period,
  INDEX on component
)

Key columns:

source_ref: unique identifier per event. For Stripe: ${invoice.id}:${line.id} (one event per invoice line from F-CMS-3). For refunds: ${charge.id}.refund. For manual: manual:${uuid}.
UNIQUE(source, source_ref): guarantees idempotency. A webhook replay is an ON CONFLICT DO NOTHING.
component: revenue component label. null for pre-F-CMS-3 events (legacy). The calculator treats null as a fallback to the global %.
Events with source='stripe_invoice' are immutable via the API (error STRIPE_IMMUTABLE).

`commission_settings`

Global configuration singleton. Source: commission-settings.ts.

sql

commission_settings (
  id                           integer PRIMARY KEY DEFAULT 1,
  default_basis                varchar(10) NOT NULL DEFAULT 'gross',
  default_currency             varchar(3) NOT NULL DEFAULT 'USD',
  stripe_fee_percent           numeric(5,2) NOT NULL DEFAULT 2.9,
  stripe_fee_fixed_cents       integer NOT NULL DEFAULT 30,
  default_recurring_percent    numeric(5,2),
  default_recurring_fixed_cents integer,
  default_one_time_amount_cents integer,
  one_time_qualification_months integer NOT NULL DEFAULT 3,
  cron_day_of_month            integer NOT NULL DEFAULT 1,
  cron_hour                    integer NOT NULL DEFAULT 3,
  auto_finalize                boolean NOT NULL DEFAULT false,
  auto_email_on_finalize       boolean NOT NULL DEFAULT false,
  updated_at                   timestamp NOT NULL DEFAULT now(),

  CHECK id = 1,
  CHECK cron_day_of_month BETWEEN 1 AND 28,
  CHECK cron_hour BETWEEN 0 AND 23
)

id=1 is the only possible row (CHECK constraint). The service guarantees it always exists via seed migration and is only updated, never inserted. Each cron tick re-reads this table — changing cronDayOfMonth or cronHour takes effect without restarting the server.

`commission_statements`

Commission statement for a rep for a monthly period. Source: commission-statements.ts.

sql

commission_statements (
  id                      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  commission_rep_id       uuid NOT NULL REFERENCES commission_reps(id) ON DELETE RESTRICT,
  period_start            date NOT NULL,
  period_end              date NOT NULL,
  status                  varchar(20) NOT NULL DEFAULT 'draft',
  subtotal_recurring_cents integer NOT NULL DEFAULT 0,
  subtotal_one_time_cents  integer NOT NULL DEFAULT 0,
  subtotal_override_cents  integer NOT NULL DEFAULT 0,
  total_cents              integer NOT NULL DEFAULT 0,
  currency                 varchar(3) NOT NULL DEFAULT 'USD',
  finalized_at             timestamp,
  paid_at                  timestamp,
  payment_method           varchar(50),
  payment_reference        text,
  payment_notes            text,
  created_at               timestamp NOT NULL DEFAULT now(),
  updated_at               timestamp NOT NULL DEFAULT now(),

  UNIQUE on (commission_rep_id, period_start),
  INDEX on (status, period_start)
)

UNIQUE(commission_rep_id, period_start): one statement per rep per period. The materialize can replace drafts but doesn't touch finalized/paid.

`commission_line_items`

A calculated line within a statement. Immutable snapshot. Source: commission-line-items.ts.

sql

commission_line_items (
  id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  statement_id         uuid NOT NULL REFERENCES commission_statements(id) ON DELETE CASCADE,
  assignment_id        uuid NOT NULL REFERENCES commission_assignments(id) ON DELETE RESTRICT,
  tenant_id            uuid NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
  kind                 varchar(30) NOT NULL,    -- 'recurring_percent' | 'recurring_fixed' | 'one_time' | 'override'
  basis                varchar(10) NOT NULL,    -- 'gross' | 'net'
  revenue_usd_cents    integer NOT NULL,
  rate_applied         numeric(5,2),            -- null for fixed and one_time
  component            varchar(20),             -- 'base' | 'phones' | 'lines' | 'overage' | 'other' | null
  amount_cents         integer NOT NULL,
  parent_line_item_id  uuid REFERENCES commission_line_items(id) ON DELETE CASCADE,
  override_of_rep_id   uuid REFERENCES commission_reps(id),
  created_at           timestamp NOT NULL DEFAULT now(),

  INDEX on statement_id,
  INDEX on assignment_id,
  INDEX on parent_line_item_id
)

Key columns:

kind='override': manager's commission line item. parent_line_item_id points to the base line item. If the base is deleted (re-materialize of a draft), overrides are deleted in cascade.
override_of_rep_id: rep whose commission was the base (informational for UI/PDF).
component: inherits the component from the base line item in override line items.

`commission_attribution_log`

Immutable audit log of attribution changes. Source: commission-attribution-log.ts.

sql

commission_attribution_log (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  lead_id     uuid REFERENCES leads(id) ON DELETE SET NULL,
  tenant_id   uuid REFERENCES tenants(id) ON DELETE SET NULL,
  action      varchar(40) NOT NULL,
  old_rep_id  uuid REFERENCES commission_reps(id),
  new_rep_id  uuid REFERENCES commission_reps(id),
  changed_by  text REFERENCES "user"(id) ON DELETE SET NULL,
  notes       text,
  created_at  timestamp NOT NULL DEFAULT now()
)

action values: lead_ref_code_set, lead_admin_set, lead_converted, admin_reassigned.

`seller_invitations`

Magic-link invitations for the seller portal. Source: seller-invitations.ts.

sql

seller_invitations (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  commission_rep_id   uuid NOT NULL REFERENCES commission_reps(id) ON DELETE CASCADE,
  token               varchar(64) NOT NULL UNIQUE,   -- 32 random bytes hex
  email               varchar(255) NOT NULL,         -- snapshot of rep.email at invite time
  expires_at          timestamp NOT NULL,            -- now() + 7 days
  used_at             timestamp,                     -- set when token is consumed
  invited_by          text REFERENCES "user"(id) ON DELETE SET NULL,
  created_at          timestamp NOT NULL DEFAULT now(),

  UNIQUE on token,
  INDEX on commission_rep_id,
  INDEX on expires_at WHERE used_at IS NULL
)

Invitation states:

active: used_at IS NULL AND expires_at > now()
expired: used_at IS NULL AND expires_at <= now()
used: used_at IS NOT NULL

Re-inviting invalidates pending ones (UPDATE used_at = now()) before creating the new one — there can't be two valid tokens at the same time.

Calculator algorithm

apps/api/src/modules/commissions/commission-calculator.ts — pure function, no side effects.

Preview mode

Returns the CalculatedStatement[] array without touching the DB. Used by commissions.preview.calculate and the preview PDF endpoint.

Materialize mode

Opens a transaction and persists the calculated statements:

If a draft statement exists for (repId, periodStart): deletes its line items and replaces them.
If it exists in finalized or paid: skip (immutable).
If it doesn't exist: insert new.
Returns { created, replaced, skipped, statementIds }.

Detailed steps

1. Fetch active assignments whose range [startsAt, endsAt ?? ∞]
   intersects [periodStart, periodEnd].
   Optional filters: commissionRepId, tenantId.

2. For each assignment A:

   a. Fetch its commission_participants. Verify SUM(sharePercent) == 100.

   b. Sum revenue_events.amountUsdCents for the tenant in period=YYYY-MM.
      Refunds (negative) are summed into the net.

   c. Count revenue_events with source='stripe_invoice' for the net formula.

   d. Calculate netRevenue:
      - basis='gross': netRevenue = totalGross
      - basis='net':
          netRevenue = round(totalGross × (1 - stripeFeePercent/100))
                       - (stripeInvoiceCount × stripeFeeFixedCents)

   e. Bucket revenue_events by component.
      If ANY of the 4 recurringPercent per component is set on A:
        Breakdown mode — generate up to 4 recurring_percent line items,
        one per component with revenue > 0.
        componentNet(c) = round(eventsByComponent(c) × netRevenue / totalGross)
        Events with component=NULL or 'other' → fallback to global recurringPercent.
      If ALL are null:
        Legacy mode — 1 recurring_percent line item with global % on total netRevenue.

   f. If recurringFixedCents > 0 → 1 recurring_fixed line item.

   g. If one-time qualified this period (vesting check) → 1 one_time line item.

   h. For each participant P (applying sharePercent/100 to each line item of A):
      i.  Generate prorated base line items.
      ii. Override walk: walk up the managerId chain.
          For each manager M with overridePercent > 0:
            Generate 1 override line item per base line item of P.
            amount = round(baseAmount × overridePercent / 100).
            If amount == 0: skip.
          Stop if a cycle is detected (TS walk with Set of visited nodes).

3. Group all line items by commissionRepId → CalculatedStatement[]:
   subtotalRecurringCents = sum(recurring_percent, recurring_fixed)
   subtotalOneTimeCents = sum(one_time)
   subtotalOverrideCents = sum(override)
   totalCents = subtotalRecurringCents + subtotalOneTimeCents + subtotalOverrideCents

4. Skip statements with totalCents == 0.

Insert order in materialize

Override line items have an FK to parent_line_item_id. The service:

Inserts base line items first.
Saves a { tempId → realId } mapping of the inserted items.
Inserts override line items with parentLineItemId = realId resolved from the mapping.

Statement lifecycle

mermaid

stateDiagram-v2
    [*] --> draft: materialize\n(manual or cron)
    draft --> draft: re-materialize\n(replaces line items)
    draft --> finalized: finalizeStatement\n(lock oneTimePaidAt)
    draft --> [*]: deleteStatement\n(cascade line items)
    finalized --> paid: markStatementPaid\n(paymentMethod required)
    finalized --> finalized: IMMUTABLE
    paid --> paid: IMMUTABLE

Transitions and rules

Transition	Precondition	Side effects
`draft` → `draft` (re-materialize)	Statement in `draft`	Deletes and reinserts line items
`draft` → `finalized`	Status == `draft`	Sets `finalizedAt = now()`. Sets `oneTimePaidAt = periodStart` on assignments whose bonuses appear in this statement
`finalized` → `paid`	Status == `finalized`	Sets `paidAt = now()`, saves `paymentMethod`, `paymentReference`, `paymentNotes`
Delete	Status == `draft`	CASCADE deletes line items

Attempting finalize/markPaid/delete with wrong status returns error CONFLICT (409).

Multi-currency

Each revenue_event stores three currency-related fields:

Field	Description
`originalCurrency`	ISO 4217 of the event's currency (e.g., `USD`, `MXN`, `CAD`)
`originalAmountCents`	Amount in the original currency
`fxRate`	Exchange rate at the time of the event (immutable snapshot)
`amountUsdCents`	`round(originalAmountCents × fxRate)` — the truth the calculator uses

For Stripe events in USD: fxRate = 1. For other currencies: queries https://api.exchangerate.host/${date} and caches in Redis for 24h (fx:${date}:${from}:${to}). If the FX API fails, the event is queued in BullMQ commissions-stripe-retry for retry (up to 5 attempts with exponential backoff).

The calculator always operates on amountUsdCents. Statements and line items are in USD.

Cycle detection in the hierarchy

Defense in depth:

DB Trigger trg_no_manager_cycle — BEFORE INSERT OR UPDATE OF manager_id on commission_reps. Walks Postgres upward until finding the cycle or reaching the root (manager_id IS NULL). If it detects a cycle, RAISE EXCEPTION.
TS Walk in commission-reps.service.ts — before the UPDATE, walks up the manager chain with a Set of visited nodes. If the rep's ID appears in the chain, throws CommissionsError('CYCLE') with a clear message before reaching the DB.

The router converts CYCLE to HTTP 400 with message: "Cycle detected in commission_reps manager hierarchy".

Stripe webhook: revenue events

The invoice.paid handler from the stripe module is extended with a side effect:

Resolve tenantId via stripe_customer_id in the tenants table.
For each invoice line (invoice.lines.data):
- Classify component by comparing line.price.id with the price IDs in the plans table.
- Create a revenue_event with sourceRef = ${invoice.id}:${line.id}.
If the invoice has no lines: create a single event with component='other' and sourceRef = invoice.id.

charge.refunded: creates a negative event with component='other' (refunds are not broken down by component). sourceRef = ${charge.id}.refund.

The side effect fails silently (doesn't block the 200 to Stripe). Failures are queued in BullMQ commissions-stripe-retry.

Commissions module architecture ​

Schema: the 9 tables ​

commission_reps ​

commission_assignments ​

commission_participants ​

revenue_events ​

commission_settings ​

commission_statements ​

commission_line_items ​

commission_attribution_log ​

seller_invitations ​

Calculator algorithm ​

Preview mode ​

Materialize mode ​

Detailed steps ​

Insert order in materialize ​

Statement lifecycle ​

Transitions and rules ​

Multi-currency ​

Cycle detection in the hierarchy ​

Stripe webhook: revenue events ​

Commissions module architecture

Schema: the 9 tables

`commission_reps`

`commission_assignments`

`commission_participants`

`revenue_events`

`commission_settings`

`commission_statements`

`commission_line_items`

`commission_attribution_log`

`seller_invitations`

Calculator algorithm

Preview mode

Materialize mode

Detailed steps

Insert order in materialize

Statement lifecycle

Transitions and rules

Multi-currency

Cycle detection in the hierarchy

Stripe webhook: revenue events