Why My ERP Cash Book Is a Derived Postgres UNION View

The carwash owner asked one question at closing every night: how much cash should be in the drawer right now? For the first three weeks of the JID Carwash ERP I answered it the obvious way. I had a CashLedgerEntry table. Every payment, every expense, every employee cash advance wrote a row into it, and the running balance was a column I kept nudging up and down. It felt clean. It was a trap.

The bug that ended the written table was mundane. A payment settled, the ledger row committed, and then the surrounding transaction that voided the payment rolled back a moment too late. The payment was gone; the ledger row that said plus 45,000 IDR was not. The drawer now claimed money that never existed. Two sources of truth had disagreed, and the one the owner trusted was wrong.

One source of truth, or none

The core mistake was keeping the same fact in two places: once as the event that caused the cash movement (the payment, the expense, the kasbon), and again as a denormalized ledger row describing it. Any time a fact lives twice, you sign up to keep both copies in sync forever, across every code path, every failure, every future feature. In a POS that handles cash under load, that sync will eventually break. When it breaks around money, you do not get a warning, you get an angry owner.

-- The original design: a written ledger the app kept in sync by hand.
INSERT INTO cash_ledger_entry (branch_id, entry_type, delta_idr, occurred_at)
VALUES (3, 'PAYMENT', 45000, now());   -- committed here

-- ...meanwhile the payment that justified it got voided,
-- and its transaction rolled back a beat too late.
-- The ledger row survives. The payment does not.
-- Drawer now reports 45,000 IDR that no event supports.

So I deleted the table. The cash book became a view: a query with a name, holding no data of its own, recomputed from the source events every time someone reads it. This is the read-model idea from event sourcing and CQRS, scaled down to a single Postgres view. The events (payments, expenses, kasbon) are the write model and the only durable truth. The cash book is a projection over them. If a payment never truly happened, it never appears in the projection, because there is nothing to project.

What the view actually looks like

The cash book UNIONs together every table that moves cash, normalizing each into the same six-column shape: a source id, an entry type, the branch it belongs to, when it occurred, a signed integer delta in rupiah, and a human memo. Money is always integer IDR, never a float, so a positive delta is cash in and a negative delta is cash out. Payments add, expenses and kasbon subtract. The running balance is just a window SUM over occurred_at, computed at read time.

-- cash_ledger_entry is a VIEW, not a table.
-- Every row is derived; nothing here is ever INSERTed.
CREATE VIEW cash_ledger_entry AS
  SELECT p.id          AS source_id,
         'PAYMENT'     AS entry_type,
         p.branch_id   AS branch_id,
         p.settled_at  AS occurred_at,
         p.amount_idr  AS delta_idr,      -- integer IDR, cash in
         p.reference   AS memo
    FROM payment p
   WHERE p.status = 'SETTLED'

  UNION ALL

  SELECT e.id, 'EXPENSE', e.branch_id, e.paid_at,
         -e.amount_idr,                   -- cash leaves the drawer
         e.category
    FROM expense e

  UNION ALL

  SELECT k.id, 'KASBON', k.branch_id, k.disbursed_at,
         -k.amount_idr,                   -- employee cash advance out
         k.employee_name
    FROM kasbon k
   WHERE k.disbursed_at IS NOT NULL;

Making a derived view fast in Postgres

The obvious worry is performance: a view is recomputed on every read, so does it get slow? In practice the view itself holds no data and needs no index. What matters is that each source table under it is indexed for the exact filter the API always sends. Every cash book request scopes by branch_id and a date range, the same IDOR-safe branch scoping used across every list query in the codebase, so I index each base table on (branch_id, occurred-time) and let the planner push that predicate down.

Postgres turns the UNION ALL into an Append node, then pulls the outer WHERE clause into each branch of the append (a rewrite pganalyze calls subquery pull-up). Each branch becomes its own index range scan bounded by branch and date, instead of a full scan of three tables. The one rule that keeps this working: the columns on each side of the UNION ALL must line up in type exactly. A stray implicit cast blocks the pull-up and you fall back to scanning everything.

-- The view needs no index. The base tables carry them.
CREATE INDEX idx_payment_branch_settled
  ON payment (branch_id, settled_at)
  WHERE status = 'SETTLED';        -- partial: mirrors the view's filter

CREATE INDEX idx_expense_branch_paid
  ON expense (branch_id, paid_at);

CREATE INDEX idx_kasbon_branch_disbursed
  ON kasbon (branch_id, disbursed_at)
  WHERE disbursed_at IS NOT NULL;

-- The API always sends branch + date range, so the planner
-- pushes the predicate INTO each Append branch:
EXPLAIN ANALYZE
SELECT * FROM cash_ledger_entry
 WHERE branch_id = 3
   AND occurred_at >= DATE '2026-07-01'
 ORDER BY occurred_at DESC
 LIMIT 50;
-- Append over three index scans; ~2 ms on a full day of one branch.

Derived view versus written table

When I would reach for a materialized view

A plain view recomputes on every read, and that is exactly what I want for a cash book that must never lie. But if the ledger fed a heavy month-long analytics dashboard aggregating across all branches at once, the recompute would stop being free. That is the moment for a materialized view: the same query, but its result stored and refreshed on a schedule, trading a little staleness for a fast scan. The rule I follow is simple.

• Correctness-critical and read live, like a drawer balance at closing: plain view, always fresh.
• Expensive aggregate that tolerates minutes of lag, like a monthly cross-branch report: materialized view, refreshed on a schedule.
• Never a hand-synced denormalized table for money. That is the drift trap I already paid for once.