# Why My ERP Cash Book Is a Derived Postgres UNION View

> How I killed a drift-prone ledger table and rebuilt the JID Carwash cash book as a derived Postgres UNION view over payments, expenses, and kasbon.

URL: https://www.matthewswong.com/en/blog/derived-cash-book-union-view-postgres/

- Author: Matthews Wong (https://www.matthewswong.com/en/about/)
- Published: 2026-07-14
- Updated: 2026-07-14
- Category: Backend

> **Key takeaway:** In the JID Carwash ERP the cash book is never a stored table. It is a Postgres read model: a UNION ALL view that merges settled payments, expenses, and kasbon advances into one time-ordered ledger, computed on every read. A written balance drifts the moment a single insert fails or a rollback lands late. A derived view cannot lie about money, because it recomputes the truth on each query.

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.

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.

> UNION ALL, not UNION. Plain UNION forces Postgres to sort every row and strip duplicates, which is pure wasted work here because a payment id and an expense id can never collide across entry types. On a busy branch that sort is the difference between a snappy closing screen and a spinner. Only reach for UNION when duplicates are genuinely possible and unwanted.

## 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.

> Match your partial index predicate to your view predicate. The view filters payments to status SETTLED, so the payment index is partial on the same condition. The index then holds only rows the view can ever return, stays smaller, and the planner uses it without hesitation. This one alignment is what let the view stay a plain view instead of forcing a materialized one.

## Derived view versus written table

| Concern | Written ledger table | Derived UNION view |
| --- | --- | --- |
| Can drift from source | Yes, on any failed sync or late rollback | No, it is the source |
| Write path complexity | Every event must also write a ledger row | Events just write themselves |
| Read cost | Cheap single scan | Slightly higher, index-bounded per branch |
| New cash source | Backfill plus new sync code | Add one UNION ALL branch |

## 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.

> This is the same principle behind Product.stockQty in the ERP: the column is a cache, and SUM over stock movements is the real answer. The cash book took the idea to its conclusion and dropped the cache entirely. Where money and inventory are concerned, prefer the derivation you can always recompute over the stored number you have to defend.

## Sources

- PostgreSQL Documentation: Materialized Views
- pganalyze: Speed up Postgres queries with UNIONs and subquery pull-up
- Microsoft Azure Architecture Center: Event Sourcing pattern

## Source links

- https://www.postgresql.org/docs/current/rules-materializedviews.html
- https://pganalyze.com/blog/5mins-postgres-UNION-subquery-pull-up-performance
- https://learn.microsoft.com/en-us/azure/architecture/patterns/event-sourcing

## FAQ

### Why make the cash book a view instead of a table?

A written ledger table stores the same fact twice: once as the event that moved cash and once as the ledger row describing it. The two copies drift apart whenever a sync fails or a transaction rolls back late. A view derives the ledger from the events on every read, so it can never disagree with its own source of truth.

### Does a UNION ALL view get slow as data grows?

Not if the base tables are indexed for the query you actually run. The view holds no data itself. Because every request scopes by branch and date range, indexing each source table on (branch_id, timestamp) lets Postgres push that filter into each branch of the Append and do bounded index scans instead of full table scans.

### How do you index a Postgres view?

You do not index a regular view directly, because it stores nothing. You index the base tables underneath it to match the view's filters and the query's predicates. Partial indexes that mirror the view's WHERE clause, such as status equals SETTLED, keep the index small and guarantee the planner uses it.

### Is this the same as event sourcing?

It borrows the core idea. The payment, expense, and kasbon tables act as the write model and durable truth, and the cash book view is a read-model projection over them, in the spirit of CQRS. It is not full event sourcing because the sources are current-state tables, not an append-only event log, but the derived read-model discipline is identical.

### When should I use a materialized view instead?

Use a materialized view when the query is expensive and can tolerate some staleness, such as a monthly report aggregating across all branches. Keep a plain view when correctness must be live, like a drawer balance at closing. Never fall back to a hand-synced denormalized table for money, which is the drift problem you were trying to escape.

---

- HTML version: https://www.matthewswong.com/en/blog/derived-cash-book-union-view-postgres/
- Bahasa Indonesia: https://www.matthewswong.com/id/blog/derived-cash-book-union-view-postgres/
- Site index for agents: https://www.matthewswong.com/llms.txt
- Sitemap: https://www.matthewswong.com/sitemap.xml
- API description (OpenAPI): https://www.matthewswong.com/openapi.json