How I built a billing system with a real double-entry ledger in Node + PostgreSQL

Jimmy A. Magbanua — Tue, 23 Jun 2026 04:39:12 +0000

Most billing code has the same bug hiding in it: it stores the account balance as a number and mutates it — balance += amount. It works fine, right up until a payment gets entered twice, or you have to fix last month's reading, and suddenly nobody can explain how an account reached its current total.

I learned the right way to do this building a real utility billing system (a client project that fell through — so I turned the engine into a product). The one decision that made it trustworthy: treat money as an append-only ledger and derive every balance from it. Never store a number you can recompute.

If you're building anything that touches money — invoicing, subscriptions, metered/usage billing, wallets — this is the part worth getting right.

The trap: the `balance` column

Most billing code starts like this:

// a bill is created
account.balance += bill.total
// a payment comes in
account.balance -= payment.amount

It works on day one. Then reality arrives:

A payment was entered twice. You subtract it again — now the balance is wrong and you can't see why.
A reading was mis-keyed last month. Fixing it means recomputing everything after it… by hand.
An auditor asks "how did this account get to ₱2,317.50?" and your answer is a single number with no history.

The problem is that balance is derived state stored as if it were source data. Once you overwrite it, the truth is gone.

The fix: append-only ledger + recompute

Borrow the 500-year-old idea: a ledger. Every financial event is an immutable row with a debit or a credit. You never edit or delete a ledger row. The balance is just a running sum.

Here's the core table (Prisma, but it's the same idea in any ORM):

model LedgerEntry {
  id             Int      @id @default(autoincrement())
  consumerId     Int
  entryDate      DateTime
  particulars    String
  debit          Decimal  @default(0) @db.Decimal(12, 2)
  credit         Decimal  @default(0) @db.Decimal(12, 2)
  runningBalance Decimal  @db.Decimal(12, 2)
  // what produced this row, so it's traceable + reversible
  referenceType  String   // 'bill' | 'payment' | 'adjustment'
  referenceId    Int
  posted         Boolean  @default(false)
}

Two rules make this powerful:

A row is either a debit or a credit, never both. (Enforce it — more on that below.)
runningBalance is derived. You can throw it away and rebuild it from the debits and credits at any time.

That second rule is the superpower. Because balance is derived, correcting history becomes trivial: fix the source rows, then recompute.

Draft-first posting (so corrections are safe)

Before anything hits the ledger as "real," it's a draft. Readings, payments, and adjustments are all created with posted: false. They don't affect balances yet. When the operator is ready, they post a batch — and only then do the entries count.

Why bother? Because posting is now reversible. Un-post a batch, fix the mistake, re-post. Each post/un-post just flips a flag and triggers a recompute.

async function postBatch(tx, { billIds, paymentIds }) {
  for (const id of billIds) {
    await tx.bill.update({ where: { id }, data: { status: 'POSTED' } })
    await tx.ledgerEntry.updateMany({
      where: { referenceType: 'bill', referenceId: id },
      data: { posted: true },
    })
  }
  // ...same for payments...

  // rebuild derived state for every affected account
  for (const consumerId of affected) await recompute(tx, consumerId)
}

The recompute engine

This is the heart of it. Given an account, throw away the derived state and rebuild it from posted entries only, in order:

async function recompute(tx, consumerId) {
  const entries = await tx.ledgerEntry.findMany({
    where: { consumerId, posted: true },
    orderBy: [{ entryDate: 'asc' }, { id: 'asc' }],
  })

  let balance = money(0)
  for (const e of entries) {
    balance = balance.plus(e.debit).minus(e.credit)
    await tx.ledgerEntry.update({
      where: { id: e.id },
      data: { runningBalance: round2(balance) },
    })
  }
  // ...also re-derive each bill's amountPaid/amountDue here (next section)...
}

That's it. Drafts are invisible (they're posted: false), so the recompute always reflects committed reality. Mis-entered something three months ago? Fix the row, recompute, and every downstream balance is correct again — no manual cascade.

FIFO settlement: which bill did a payment pay?

A payment isn't just "−₱500 off the balance." For reporting (aging, collections), you need to know which bills it settled. The clean rule is FIFO: a payment pays the oldest unpaid bill first, then the next.

So recompute also walks the bills oldest-first and applies the period's payments:

let cash = totalPostedPayments
for (const bill of billsOldestFirst) {
  const due = bill.total.minus(bill.amountPaid)
  const applied = Decimal.min(cash, due)
  bill.amountPaid = bill.amountPaid.plus(applied)
  bill.amountDue  = bill.total.minus(bill.amountPaid)
  bill.status = bill.amountDue.isZero() ? 'PAID'
              : bill.amountPaid.gt(0)   ? 'PARTIALLY_PAID' : 'POSTED'
  cash = cash.minus(applied)
}

Because this is recomputed (not incrementally mutated), it's always consistent — even after an un-post/re-post.

Never use floats for money

0.1 + 0.2 === 0.30000000000000004. That tiny error compounds across thousands of transactions until your books don't tie out. Use a decimal library and a fixed-precision DB column.

const Decimal = require('decimal.js')
const money  = (v) => new Decimal(v || 0)
const round2 = (v) => money(v).toDecimalPlaces(2, Decimal.ROUND_HALF_UP)

Store as Decimal(12,2) in Postgres, compute with decimal.js, round once at the boundary. Your totals will reconcile to the centavo.

Progressive tariffs done right

Metered billing usually isn't flat — it's tiered (the first N units cost one rate, the next tier another). Model the tiers as data, then compute cumulatively:

function chargeFor(consumption, brackets) {
  let charge = money(0)
  for (const b of brackets.sort((a, z) => a.min - z.min)) {
    const max = b.max ?? Infinity
    if (consumption < b.min) break
    const units = Math.min(consumption, max) - (b.min - 1)
    if (units > 0) charge = charge.plus(b.minCharge).plus(money(units).times(b.rate))
  }
  return round2(charge)
}

Two things that saved me later:

Effective-date your tariffs. Rates change. Keep old ones.
Snapshot the tariff onto each bill. A bill from March should always reproduce March's math, even after April's rate change. Don't recompute old bills against today's rates.

Reports fall out for free

Here's the payoff. Because everything is in one ordered ledger, reports are just queries over it — not separate bookkeeping:

Statement / AR ledger: the entries themselves (opening → charges → payments → closing).
Accounts receivable: SUM(debit) - SUM(credit) per account, posted only.
Aging: walk unpaid bills by age bucket (Current / 31–60 / 61–90 / …) using the FIFO settlement you already computed.
Revenue leakage: consumption trends per account — dormant, sharp drops, never-billed.

No parallel "summary" tables to keep in sync. One source of truth.

Let the database enforce the invariants

Application bugs happen. Make the impossible states impossible at the DB level with CHECK constraints, so a bad write fails loudly instead of silently corrupting the books:

ALTER TABLE ledger_entries
  ADD CONSTRAINT chk_nonneg   CHECK (debit >= 0 AND credit >= 0),
  ADD CONSTRAINT chk_one_side CHECK (NOT (debit > 0 AND credit > 0));

ALTER TABLE bills
  ADD CONSTRAINT chk_due CHECK (amount_due = total_amount - amount_paid);

These caught real bugs during development that tests had missed.

Takeaways

If you build anything that handles money:

Don't store balance — derive it. Append-only ledger, recompute on demand.
Make writes reversible with draft-first posting.
Settle FIFO so you know which charge got paid.
Decimals, never floats.
Effective-date and snapshot your rates.
Push invariants into the database.

It's a little more upfront work than balance += amount, but it's the difference between a tool you bill a few customers with and one you can trust with years of financial records.

I packaged this engine into a product — **BillFlow, a self-hostable utility & metered billing system (Vue 3 + Node + PostgreSQL) with the full ledger/posting design above, plus AR aging, reports, and an ops layer. If it'd save you the build, it's here: https://magbanuajimmy.gumroad.com/l/billflow. Either way, I hope the ledger pattern is useful in your own projects — happy to answer questions in the comments.

DEV Community: Jimmy A. Magbanua