Chapter 15 · Views, Routines, and Database Automation
Choosing Between Database Logic and Application Logic
The database and application are not competing locations for all business logic. Each is strongest at different responsibilities. A maintainable system assigns every rule an explicit owner and keeps the enforcement path testable.
Learning outcomes
Assign each rule to an explicit and testable owner
Compare constraints, views, routines, triggers, and application services by responsibility.
Place atomic data invariants close to the data without overloading the database with orchestration.
Identify portability, deployment, observability, and team-ownership tradeoffs.
Design a hybrid workflow with database enforcement and application coordination.
Create a governance checklist for database-resident logic.
Start with the property, not the technology
Ask what must remain true, who owns the rule, which resources it touches, and what must happen atomically. Only then choose the implementation location.
The goal is a clear enforcement path, not maximizing either database logic or application logic.
Decision matrix
| Requirement characteristic | Database is usually stronger | Application is usually stronger |
|---|---|---|
| Must hold for every writer | Constraints, foreign keys, unique indexes | Only if all writes are guaranteed through one service |
| Atomic with row changes | Transaction, trigger, stored routine | Application transaction when using one database connection |
| External API, email, or queue | Outbox row only | Actual network call, retry, timeout, circuit breaker |
| Reusable relational projection | View or materialized view | API response composition across many systems |
| Complex workflow and user interaction | Persist state and invariants | Orchestrate steps, approvals, and compensation |
| Cross-database portability | Portable constraints and SQL subset | Domain code behind repository interfaces |
| High data locality | Set-based SQL close to data | Row-by-row logic is usually worse |
| Rich telemetry and debugging | Database logs and catalog inspection | Tracing, structured logs, feature flags, and debuggers |
A practical placement hierarchy
-
Use declarative schema features first: types,
NOT NULL,CHECK, keys, foreign keys, and exclusion/unique rules. - Use views for stable read contracts: centralize relational shape without implying stored results.
- Use materialization for measured read bottlenecks: add a freshness and refresh contract.
- Use routines for cohesive data-local operations: type the interface and define side effects.
- Use triggers only for narrow automatic behavior: audit, transition guards, and view adaptation.
- Use application services for orchestration: external resources, user workflows, policy variation, and broad observability.
Hybrid capstone: cancelling an order
The application authenticates the caller, authorizes the action, chooses a request identifier, and coordinates external notifications. The database atomically enforces the state transition, records the command receipt, updates the order, and writes an outbox event.
Only the durable outbox row is part of the database transaction; the network call happens after commit.
Database foundation
DROP TABLE IF EXISTS command_receipt;DROP TABLE IF EXISTS outbox_event;DROP TABLE IF EXISTS cancellation_audit;CREATE TABLE command_receipt ( request_id TEXT PRIMARY KEY, command_name TEXT NOT NULL, aggregate_id INTEGER NOT NULL, completed_at TEXT NOT NULL) STRICT;CREATE TABLE cancellation_audit ( audit_id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, previous_status TEXT NOT NULL, request_id TEXT NOT NULL UNIQUE, actor_id TEXT NOT NULL, reason TEXT NOT NULL, recorded_at TEXT NOT NULL) STRICT;CREATE TABLE outbox_event ( event_id INTEGER PRIMARY KEY, event_type TEXT NOT NULL, aggregate_id INTEGER NOT NULL, request_id TEXT NOT NULL UNIQUE, payload_json TEXT NOT NULL CHECK (json_valid(payload_json)), created_at TEXT NOT NULL, published_at TEXT) STRICT;Explicit atomic command
This transaction is intentionally explicit rather than hidden in a trigger. It needs actor, request, and reason context from the application, and the caller must distinguish “duplicate request” from “invalid state.”
BEGIN IMMEDIATE;INSERT INTO command_receipt (request_id, command_name, aggregate_id, completed_at)VALUES (:request_id, 'cancel_order', :order_id, datetime('now'))ON CONFLICT (request_id) DO NOTHING;-- Continue only when the receipt was inserted.UPDATE sales_orderSET status = 'cancelled', updated_at = datetime('now'), version = version + 1WHERE order_id = :order_id AND status IN ('draft', 'submitted') AND changes() = 1;INSERT INTO cancellation_audit (order_id, previous_status, request_id, actor_id, reason, recorded_at)SELECT :order_id, :previous_status, :request_id, :actor_id, :reason, datetime('now')WHERE changes() = 1;INSERT INTO outbox_event (event_type, aggregate_id, request_id, payload_json, created_at)SELECT 'order.cancelled', :order_id, :request_id, json_object('order_id', :order_id, 'reason', :reason), datetime('now')WHERE changes() = 1;COMMIT;
In production, check affected-row counts after each step and
roll back on an invalid transition. The compact SQLite example
demonstrates placement, but application code should not rely on
a long chain of changes() calls without explicit
verification.
Application responsibilities
| Before transaction | Inside transaction | After commit |
|---|---|---|
| Authenticate actor | Supply actor/request context | Return stable command result |
| Authorize cancellation policy | Execute conditional state transition | Publish outbox events asynchronously |
| Validate request shape | Write receipt, audit, and outbox atomically | Retry publication with backoff |
| Select idempotency key | Detect duplicate request | Record metrics and traces |
| Avoid external calls | Never wait on network services | Notify users and downstream systems |
Anti-patterns
Everything in stored code
Workflow, external calls, and product policy become hard to deploy, observe, and port.
All rules in one service
Other tools, migrations, or direct writers can violate invariants; concurrency bugs reappear.
Trigger surprise
A simple update performs undocumented cascading work and changes latency unpredictably.
Duplicated rule
The same rule exists in several services and a trigger with slightly different semantics.
Network in transaction
Locks remain held while an unreliable dependency is contacted.
No owner
Database objects persist without tests, monitoring, migration policy, or responsible team.
Govern database logic like code
| Control | Required practice |
|---|---|
| Source control | Every view, routine, trigger, and grant is represented in migrations. |
| Review | Database and application owners review semantics, performance, and rollout. |
| Testing | Contract, failure, concurrency, privilege, and rollback tests run automatically. |
| Versioning | Breaking interfaces use expand-and-contract or versioned names. |
| Observability | Track routine duration/errors, trigger amplification, refresh lag, and outbox backlog. |
| Inventory | Catalog object owner, consumers, purpose, dependencies, and retirement date. |
| Recovery | Document rollback or forward-fix strategy before production deployment. |
Architecture exercise
Place each requirement in the most appropriate mechanism, then justify the atomicity boundary:
- Quantity must be positive.
- Every order must reference an existing customer.
- A dashboard needs daily totals that may be ten minutes old.
- Changing an order status must create an audit row in the same commit.
- A cancellation sends email and informs a warehouse API.
- Five applications need the same customer-order read shape.
Review the placement
- Where should the two row-integrity requirements live?
- What mechanism fits the bounded-staleness dashboard?
- How can audit remain atomic without sending email inside the transaction?
- What should expose the shared read shape?
Review the answers
Use CHECK and foreign-key constraints.
Use a materialized summary with a ten-minute freshness SLO.
Write audit and an outbox row atomically; publish email/API events after commit.
Use a documented view, or a materialized view if measurement proves necessary.
Chapter summary
- Place universal data invariants in declarative database constraints.
- Use views and materialized views as explicit read contracts with performance and freshness semantics.
- Use routines for cohesive typed operations, and triggers only for narrow automatic behavior.
- Keep external orchestration, retries, user workflow, and rich telemetry in application services.
- Govern database-resident logic with ownership, tests, versioning, observability, and recovery plans.
Chapter 16 continues with security, reliability, and governance: roles and least privilege, SQL injection prevention, backup and recovery objectives, encryption and auditing, retention, privacy, lineage, and data quality.