Book every money movement as balanced double-entry postings

How to do it

A single-entry list of charges says what came in, but not what you owe. Double entry shows both sides. A prepaid annual plan debits cash and credits deferred revenue. Each month of service then moves one twelfth from deferred revenue to revenue. Bought credits credit a credits liability, and their use moves it to revenue. Keep a short chart of accounts, such as cash, provider balance, receivables, deferred revenue, credits liability, revenue, fees, refunds and tax payable. Store amounts as signed integers in minor units, with debits positive and credits negative. A balance is the sum of an account's postings. Each row in Record every money movement in an append-only ledger becomes one journal entry here. So does each row in Keep credits in a ledger instead of a balance you overwrite. Corrections are new entries that reverse old ones.

Examples

SQL example

CREATE TABLE journal_entries (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at timestamptz NOT NULL,
  source_id   text NOT NULL UNIQUE,
  memo        text NOT NULL
);
CREATE TABLE postings (
  entry_id     bigint NOT NULL REFERENCES journal_entries (id),
  account      text NOT NULL,
  currency     char(3) NOT NULL,
  amount_minor bigint NOT NULL
);
GRANT SELECT, INSERT ON journal_entries, postings TO app_role;

TypeScript example

type Posting = { account: string; currency: string; amountMinor: bigint }

export async function postEntry(db, sourceId: string, memo: string, postings: Posting[]) {
  const sums = new Map<string, bigint>()
  for (const p of postings) sums.set(p.currency, (sums.get(p.currency) ?? 0n) + p.amountMinor)
  if ([...sums.values()].some(sum => sum !== 0n)) throw new Error(`Unbalanced entry ${sourceId}`)
  await db.transaction(async tx => {
    const entryId = await tx.insertEntry(sourceId, memo)
    for (const p of postings) await tx.insertPosting(entryId, p)
  })
}

Python example

from collections import defaultdict

def post_entry(db, source_id: str, memo: str, postings: list[dict]) -> None:
    sums = defaultdict(int)
    for p in postings:
        sums[p["currency"]] += p["amount_minor"]
    if any(total != 0 for total in sums.values()):
        raise ValueError(f"Unbalanced entry {source_id}")
    with db.transaction() as tx:
        entry_id = tx.insert_entry(source_id, memo)
        for p in postings:
            tx.insert_posting(entry_id, p)

How to check

A database constraint or a test proves that every journal entry sums to zero per currency, and an unbalanced insert fails. As the app role, an UPDATE and a DELETE on the postings table both fail. The sum of all postings in every currency is zero.

Related rules

• PAY-21 Record every money movement in an append-only ledger in Payments and billing
• USG-6 Keep credits in a ledger instead of a balance you overwrite in Usage-based billing
• PRC-19 Recognise revenue as it is earned in Pricing, plans and entitlements
• PRC-22 Export balanced journals to the accounting system each period in Pricing, plans and entitlements