Building on PostgreSQL: Why We Chose the Database Over the Framework

The business logic in Nue lives in the database, not the application layer. Triggers enforce financial rules before data persists. Views derive subledger balances from the ledger without maintaining separate state. Constraints prevent invalid records from existing at all. This is an architectural choice — the database is the thing that can be trusted absolutely, so the database is where the rules live.

Modern application development trends strongly toward putting logic in the application layer. Frameworks like Express, Django, and Rails make it easy to write business validation in code, and the application layer is more accessible to most developers than the database layer. The conventional wisdom is that the database is for storage and the application is for logic.

For most categories of software, that’s a reasonable trade-off. For a financial system where the correctness of the data has legal and regulatory implications, the trade-off runs the other way.

What PostgreSQL makes possible

PostgreSQL is a 35-year-old relational database system that is, by most measures, the most capable open-source database in existence.¹ It supports triggers that fire before or after data changes, at the row level or the statement level. It supports check constraints that prevent rows from existing if specified conditions aren’t met. It supports row-level security that enforces access control at the data layer rather than the application layer. It supports views that derive data from base tables without storing it separately.

These aren’t just convenience features. They’re enforcement mechanisms that operate at a layer below any application code. When a BEFORE INSERT trigger on the journal lines table checks whether an entry will balance, that check runs regardless of what application code called the insert. When a check constraint requires that a revenue account entry carry a segment code, that constraint fires regardless of whether the form that created the entry had a validation rule. When a journal line is marked as posted and a trigger prevents modification of posted lines, that protection holds even if a developer writes a direct SQL update against the table.

The trigger architecture for financial rules

Nue’s critical financial invariants are implemented as BEFORE INSERT triggers on the journal entry tables. The logic is simple in structure even when the rules are complex: before any row is committed to the journal, the trigger checks the conditions that must be true for that row to be valid. If the conditions aren’t met, the trigger raises an exception and the row is rejected. The rejection happens before the transaction commits, before any other code has had a chance to act on the assumption that the row exists.

For a journal entry, the conditions include: the entry must balance (debits must equal credits), the period must be open, the accounts referenced must exist and be active, and if the account is a P&L account in a company with required segmentation, the segment codes must be present and valid. These rules can’t be bypassed by application code because the trigger fires at the database level, not the application level. They can’t be bypassed by direct SQL because the trigger fires on any INSERT, regardless of source.²

Views as the subledger model

The subledger architecture in Nue is implemented as database views. The accounts receivable view is a query against the journal lines table, filtered to AR accounts, grouped by customer, showing open items and their aging. It produces the same result every time it’s queried because it’s reading from the same underlying data. It doesn’t maintain state independently, so it cannot drift from the GL.

This is both simpler and more reliable than maintaining a separate AR table synchronized to the GL. The synchronization problem — keeping two tables consistent — is replaced by a derivation — querying one table with a filter. Derivation is always consistent. Synchronization is reliable until something breaks.

The schema-first principle

Building on PostgreSQL with this architecture requires that the schema be designed as carefully as the application code — more carefully, in some respects, because schema decisions are harder to change after data exists. The column types matter. The constraint definitions matter. The trigger logic matters. The index design matters for the query performance of the views that derive subledger data.

This means that every new feature that touches financial data goes through schema design first. What tables does this touch? What constraints are needed? What triggers fire? What views need to be updated? The application code follows from the schema design, not the other way around.

It’s a slower development model in the early stages. It produces a system that’s more correct by construction, without requiring the application layer to compensate for what the database doesn’t enforce.

Sources