Database-Layer Business Rules: Why We Enforce at the Bottom, Not the Top

UI validation can be bypassed. API validation can be bypassed. A sufficiently motivated person with database access can bypass both. Database triggers and constraints cannot — they fire regardless of which path data took to get there. When the rules live at the bottom of the stack, the guarantees they produce are absolute.

Every software application has business rules — conditions that must be true for an operation to be valid. A journal entry must balance. An invoice can’t be issued for a negative amount. A payment can’t be applied to an invoice that’s already closed. An expense can’t be approved by the person who submitted it. These rules exist in every financial system, enforced at some layer of the application stack.

Where in the stack they’re enforced determines how strong the guarantee actually is.

The layers of a software application

Modern web applications are typically structured in layers: a user interface, an API layer, a business logic layer, and a database. Business rules can be enforced at any of these layers. UI validation gives users immediate feedback when they’ve entered something incorrect. API validation prevents invalid requests from reaching the business logic. Business logic validation runs before the data reaches the database.

Each of these layers provides a meaningful level of protection. Each can also be bypassed.

A user who knows the API endpoint can call it directly, skipping the UI validation entirely. A developer who’s working with the data for a legitimate reason — data migration, emergency correction, debugging — can interact with the API layer in ways that bypass application validation. And almost every production database has some history of direct SQL access: migrations run from a terminal, corrections made during an incident, imports that needed to happen faster than the application could handle.¹

When business rules live only in the application layers, direct database access — however infrequent, however well-intentioned — can produce records that violate those rules without the application ever knowing.

What database-layer enforcement looks like

PostgreSQL, along with other mature relational databases, provides enforcement mechanisms that operate at the storage layer itself: constraints, triggers, and rules. These aren’t application features — they’re database features that fire regardless of how a write reaches the database, whether through the application, through a migration script, through a direct SQL statement, or through any other mechanism.

A NOT NULL constraint on a required field means no record can exist without that field, period. A CHECK constraint that requires a journal entry to balance means an unbalanced entry cannot be committed, regardless of what code attempted to insert it. A BEFORE INSERT trigger that validates whether a posting period is open will reject a transaction to a closed period regardless of whether the rejection logic exists in the application layer or not.

In Nue’s architecture, the critical financial invariants live here. Journal entries must balance before they can exist. Segments are required on P&L accounts when the configuration demands it. Actuals are immutable once posted. These aren’t application-level policies. They’re database-level facts enforced by triggers that run before every relevant write, without exception.²

The audit consequence

For financial systems, the significance of database-layer enforcement isn’t just operational — it’s relevant to audit. When auditors assess the reliability of a financial system, one of the key questions is whether the controls over financial data can be bypassed. The answer “our application enforces these rules” is weaker than “the database enforces these rules regardless of what code is running.”

SOC 2 audits and financial statement audits both examine the control environment around financial data. A system where validation lives exclusively in the application layer has a larger attack surface than one where validation is implemented at the database layer. Direct database access, scripted imports, developer tooling — any path that bypasses the application is a potential control gap if the rules don’t exist at the bottom.³

Database-layer enforcement doesn’t eliminate the need for application-layer validation — users still need feedback in real time, and API validation still provides a useful early rejection. But it means that database-layer enforcement is the backstop that can’t be worked around.

The tradeoff

Database-layer enforcement comes with real tradeoffs. Triggers are less visible than application code — a developer inspecting the codebase doesn’t automatically see what the database will enforce unless they look at the schema. Error messages from database rejections are sometimes less user-friendly than application-generated messages. Testing database-level constraints requires a different testing approach than testing application logic.

These are real costs. They’re worth paying for financial systems, where the cost of a control gap is not a failed test but a financial statement error.

Sources