A Commission and Split-Payment System for a Brokerage

A brokerage commission system has two unforgivable bugs: a split that does not add up to the gross commission, and one agent seeing another's pay. Fix both at the database. Store money as integer minor units so rounding never drifts, enforce that every deal's splits sum to its gross, scope access so pay is visible only to the right people, and test that no cent is ever lost.

This is general engineering guidance, not tax or payout advice; confirm how commissions are taxed and paid out with your adviser. If you would rather have it built and tested for you, see how RAITHub would build this below.

Why is commission such a bug magnet?

Because the money splits several ways and the arithmetic has to be exact. A single sale's gross commission is divided between the listing side and the selling side, then between the brokerage and each agent on tiered plans, sometimes with a referral cut and a team lead override on top. Every one of those is a percentage, and percentages of money produce fractions. If you round each share independently with floating-point maths, the parts stop adding up to the whole, and someone is paid a cent too much or too little, every deal, forever.

How do you store money so nothing drifts?

As integers, in the smallest currency unit, never as floats. A commission of 2,500.00 is stored as 250000 cents. All arithmetic stays in integers, and you only format to a decimal for display. This is the same rule that keeps a ledger correct in double-entry ledger database design, and it is the single most important decision in a commission system.

How do you guarantee the splits always sum to the gross?

Allocate the remainder, do not round each share. Compute each party's share with integer division, track the cents left over, and assign the remainder deterministically (largest-share-first, or to the brokerage) so the sum of the splits is exactly the gross. Then let the database enforce it: a constraint or a trigger that rejects any deal whose splits do not total its gross commission.

-- Money as bigint minor units; splits reference the deal's gross.
CREATE TABLE deals (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  brokerage_id uuid NOT NULL,                 -- tenant key
  gross_cents  bigint NOT NULL CHECK (gross_cents >= 0)
);

CREATE TABLE commission_splits (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  deal_id      uuid NOT NULL REFERENCES deals(id),
  payee_id     uuid NOT NULL,
  amount_cents bigint NOT NULL CHECK (amount_cents >= 0),
  created_at   timestamptz NOT NULL DEFAULT now()
);

-- The splits for a deal MUST sum to its gross. This trigger is the backstop.
CREATE OR REPLACE FUNCTION check_splits_balance() RETURNS trigger AS $$
DECLARE total bigint; gross bigint;
BEGIN
  SELECT coalesce(sum(amount_cents), 0) INTO total
    FROM commission_splits WHERE deal_id = NEW.deal_id;
  SELECT gross_cents INTO gross FROM deals WHERE id = NEW.deal_id;
  IF total <> gross THEN
    RAISE EXCEPTION 'splits (%) do not equal gross (%)', total, gross;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Run the balance check at the end of the transaction that writes all the splits, so a half-written set never commits. The allocation-of-remainder logic lives in application code; the trigger guarantees the result, whatever the code does.

Who should be allowed to see commission?

Treat pay like any sensitive, money-adjacent data: least privilege, enforced in the database. An agent sees their own splits; a team lead sees their team's; the brokerage admin sees all of theirs; no one sees another brokerage's anything. Scope every query to the brokerage (the tenant) and back it with PostgreSQL row-level security, so a missed filter in code cannot leak another firm's or another agent's pay. The role model is in designing SaaS authorization, and the tenant isolation pattern in row-level security for multi-tenant Postgres.

How do you keep an auditable record of every change?

Make the splits effectively append-only. A correction is a new reversing entry, not an edit, so history is never rewritten, exactly as in a ledger. Record who changed what and when in an append-only audit log (designing an audit log customers trust), because the first time a payout is disputed, the record of how the number was reached is what settles it.

How do you test that no cent is lost?

Test	What it proves	Layer
Splits sum to gross	Every allocation totals the gross commission exactly, including the awkward remainders	Unit + DB (trigger)
Property-based rounding	For thousands of random gross amounts and plans, the parts always sum to the whole	Unit
Access matrix	Each role sees only the pay it is entitled to; cross-brokerage reads return 404	API
Corrections	A fix posts a reversing entry; history is never edited	API
Payout idempotency	A retried payout does not pay twice	API

// tests/commission/splits-balance.spec.ts
import { test, expect } from 'vitest'
import { allocate } from '@/lib/commission'

test('any gross and plan: splits sum to the gross', () => {
  for (let i = 0; i < 10000; i++) {
    const gross = Math.floor(Math.random() * 10_000_000) // random cents
    const splits = allocate(gross, [{ pct: 50 }, { pct: 30 }, { pct: 20 }])
    const total = splits.reduce((s, x) => s + x.amount_cents, 0)
    expect(total).toBe(gross) // no cent created or lost
  }
})

Buy, build or hire?

Option	What you get	Choose this when
A spreadsheet	Flexible, manual, error-prone	Very few deals and one person reconciling by hand
A brokerage platform with commissions built in	Standard split rules out of the box	Your plans fit the vendor's model and you want their whole suite
A custom commission system	Your exact plans, integer money, a balancing ledger and strict access control	Split rules are complex or central, and errors or leaks are unacceptable
A managed QA team on your build	Balancing, rounding and access suites gated in CI	You have the system but "every split balances" isn't proven on every build

How long does it take to build yourself?

The allocation logic, the integer-money schema and the balancing trigger are roughly 2 to 3 days for an engineer comfortable with money in PostgreSQL; access control, corrections and payouts add a couple of weeks. The main risk of doing it yourself is floating-point money: it looks right in testing and loses cents in production, one deal at a time, until an agent notices and trust is gone.

Why RAITHub for this

RAITHub moves money correctly in integer minor units with database-enforced limits on Sundor Skin (146 tables, row-level security, 530+ tests) and runs Stripe rent collection on PropDesk (1,024 tests). The money, role and ledger approach sits inside the API and backend development service and SaaS development. See the real-estate industry page.

When you don't need us

• Your deals are few and simple. A reviewed spreadsheet may be enough.
• A brokerage platform's built-in commissions fit your plans. Use it.
• You need tax or payout rulings. Those are for your adviser, not a development partner.

How RAITHub would build this

• Scope: a commission data model with integer money, remainder-safe allocation, a database-enforced balance check, append-only corrections, an audit log, and role- and tenant-scoped access with row-level security.
• Timeline: 6 to 12 weeks for a backend-heavy money system, delivered in phases; a narrow first slice can fit a 4 to 6-week fixed scope.
• What you receive: the balancing, rounding and access suites gated in CI, runbooks, the product on accounts you own, IP assigned to you and an NDA as standard.
• Ways to buy it: a fixed-scope build, a dedicated monthly team, or a QA plan if you only need the money test suite.
• Next step: a free 15-minute technical audit, then a fixed written quote.

See the QA as a Service page, or book the free 15-minute audit. This is general guidance; confirm tax and payout rules with your adviser.

Documentation checked on 10 October 2026.

Frequently asked questions

How do you make commission splits always add up to the gross?

Compute each share with integer arithmetic, track the leftover cents, and assign the remainder deterministically so the parts sum to the whole exactly. Then enforce it in the database with a constraint or trigger that rejects any deal whose splits do not total its gross commission.

Why store commission money as integers?

Because floating-point maths introduces tiny rounding errors that make splits stop adding up. Storing amounts in the smallest currency unit as integers and formatting only for display keeps every calculation exact, which is essential when the parts must equal the whole.

How do you stop one agent from seeing another agent's pay?

Scope every query to the brokerage and the role, and back it with PostgreSQL row-level security so a missed filter in code cannot leak pay. An agent sees their own splits, a team lead their team's, a brokerage admin all of theirs, and no one sees another brokerage's data.

How do you correct a commission mistake?

Post a reversing entry and a new correct one, rather than editing the original. Keeping the splits append-only means the history of how a payout was reached is never rewritten, which is what settles a dispute later.

Is this security testing a certified penetration test?

No. The access-control tests here check that the system enforces who can see and change commission, as application-level testing against OWASP guidance. They are not a CREST- or PCI-certified penetration test and produce no compliance attestation; use a certified vendor if you need one.