Database schema design for an approval system must preserve three things simultaneously: the current work queue, the business record that was actually reviewed and an audit history that cannot be rewritten by ordinary workflow updates. A single table with status, approver and comments appears convenient, but it becomes ambiguous when requests are edited, approvals run in parallel, delegates act, a decision is withdrawn or a policy changes. The schema should separate stable request identity, versioned submitted data, workflow steps, assignments, decisions and evidence.
This guide uses a relational model because constraints, transactions and joins are valuable for workflow integrity. The examples fit PostgreSQL concepts but apply broadly. The design should be driven by business rules and assurance needs: who may submit, which version is under review, how many approvals are required, whether the requester may approve, what happens after rejection and how records are retained. Database constraints enforce local invariants; transaction logic and domain services enforce rules that span multiple rows and time.
Define approval semantics before creating tables
Write the workflow as state transitions and decisions. Identify request types, submitters, approver groups, ordering, quorum, rejection behavior, resubmission, cancellation, expiry, delegation, escalation and separation of duties. Decide whether approval authorizes an action or merely records advice. Name the business object affected and whether the approval stores a snapshot or references mutable live data. If an approver can see different values later, the evidence is weak. Record policy version and evaluated attributes with the submission.
Distinguish workflow status from business outcome. A request can be pending while an external action is already executing, approved while fulfillment later fails, or cancelled after an earlier approval. Model fulfillment or execution separately when it has its own lifecycle. Define retention and deletion for comments, files and personal data. Decide which fields may be corrected without creating a new review version. These choices determine foreign keys and uniqueness more reliably than starting from a generic flowchart.
| Concept | Cardinality | Invariant |
|---|---|---|
| Request | One stable identity | Tenant, type and requester do not silently change |
| Request version | One or more per request | Submitted payload and policy context are immutable |
| Approval step | One or more per active version | Order, quorum and activation are explicit |
| Decision | Zero or more per step | Actor is eligible and decision targets one version |
| Audit event | Many per aggregate | Append-only event records the actor and cause |
Model request identity and immutable versions
The request table holds a generated identifier, tenant identifier, request type, requester, current version, status, created time and closed time. Keep searchable business keys in typed columns and apply unique constraints within the tenant where required. The request_version table stores submission number, immutable payload, summary, policy version, submitted actor and timestamp. Structured columns should hold frequently filtered and constrained values; JSON can preserve flexible type-specific details, but it should not replace tenant keys, status, money or dates that require consistent validation.
When a draft is submitted, create a new version and its workflow plan in one transaction, then atomically point the request to that version. Decisions always reference the version they evaluated. Editing material fields creates another version and invalidates or supersedes prior pending work according to policy. Do not overwrite the original payload. A content hash can help detect accidental mutation but is not a substitute for access controls and audit. Store timestamps in UTC with an unambiguous type and format values for users at the boundary.
Represent ordered, parallel and quorum approvals
An approval_step row identifies the request version, sequence or stage, status, activation time, due time, quorum, policy rule and completion time. Parallel steps can share a stage number; ordered stages activate after prior completion. For dynamic workflows, persist the resolved plan rather than recomputing historical requirements from current organization data. An assignment table links a step to eligible users, groups or roles and records delegation source, validity and reason. Eligibility evaluation should be explainable and time-bound.
Quorum rules require care. Count distinct valid decisions from eligible actors and define whether rejection immediately ends the step. Prevent one person from satisfying multiple required roles unless policy permits it. Separation-of-duty rules may depend on requester, amount owner or prior action and often belong in transactional service logic supported by stored attributes. Keep current step status for efficient queues, but derive it through a single controlled transition function and verify it against decisions in tests and reconciliation.
| Table | Important columns | Key constraints |
|---|---|---|
| approval_request | id, tenantid, type, requesterid, currentversionid, status | Tenant-scoped business key; valid status |
| request_version | requestid, versionno, payload, policyversion, submittedat | Unique request/version; immutable after submit |
| approval_step | versionid, stage, quorum, status, dueat | Unique plan position; positive quorum |
| step_assignment | stepid, subjecttype, subjectid, validfrom, valid_to | No duplicate active assignment |
| approval_decision | stepid, versionid, actorid, outcome, decidedat | Idempotency key; valid outcome; one active decision policy |
| audit_event | aggregateid, eventtype, actorid, occurredat, details | Append-only access path |
Store decisions as evidence, not mutable fields
A decision row records the targeted version and step, actor, represented role, outcome, reason, timestamp, authentication context and idempotency key. Treat it as append-only. If policy allows withdrawal, append a withdrawal or superseding decision linked to the original rather than updating history. Store the evaluated business summary or hash where useful. Validate that the step is active, the version is current for that workflow, the actor is eligible and separation rules pass inside one transaction.
Protect against double decisions and concurrent completion. Lock the relevant step or use serializable transaction logic while inserting a decision and calculating the transition. PostgreSQL row locks prevent conflicting writers, but transactions should remain short and never wait for user input. Use a consistent lock order across request, version and step rows to reduce deadlocks, and retry serialization or deadlock failures safely through an idempotent command. A uniqueness constraint is the final guard against duplicated requests.
Keep comments and attachments connected but separate
Comments have different visibility and mutability from decisions. Store comment ID, request or version, optional step, author, audience, body, created time and edited or deleted markers. If edits are allowed, retain revisions where assurance requires them. Do not place a decisive justification only in an unstructured comment; the decision should carry its required reason. Mentions and notifications should reference the comment but have their own delivery state.
Store attachment metadata in the database and file bytes in controlled object storage. Record tenant, request version, uploader, filename, media type, size, checksum, storage key, scan state and retention class. Never trust client media type or filename. Scan before broad access, use signed short-lived retrieval and enforce authorization on the parent request. A replacement should create a new attachment record. Deletion needs an event and storage lifecycle confirmation, especially when legal retention applies.
Design the audit trail and outbox
Audit events record security and business transitions: draft created, submitted, plan resolved, assignment changed, decision recorded, escalated, cancelled, fulfilled and accessed by privileged support. Include actor, service identity, tenant, aggregate, event type, time, correlation ID, source and structured details. Keep events append-only and restrict direct writes. Avoid sensitive payload duplication; link to versioned data. Database audit and application events serve different purposes and can coexist.
Use a transactional outbox when workflow changes must publish notifications or commands. Insert the domain change and outbox message in the same database transaction. A worker delivers messages with retries and marks publication state. Consumers must be idempotent because at-least-once delivery can repeat. This avoids the gap where an approval commits but its notification or fulfillment command is lost. Reconciliation should find stale outbox rows, active steps without assignments and requests whose cached status disagrees with decisions.
Support queues, reporting and retention
Operational queries need indexes on tenant, status, assignee, due time, request type and current version. Use partial indexes for active work where supported, and inspect plans with realistic volume. Keep a materialized or denormalized queue projection if permission and quorum joins become expensive, but rebuild it from authoritative tables. Reporting should distinguish submitted, decided and fulfilled timestamps. Measure decision duration by stage and exclude paused intervals only through explicit state records.
Partitioning may help very large audit and event tables, but it does not replace indexes or tenant authorization. Define archive and purge by business and legal policy. Deleting a tenant must reconcile primary rows, object storage, search, analytics and backups. Preserve aggregate evidence where retention is required while minimizing personal data. Test schema migrations with live-size tables and concurrent traffic. Approval systems are especially sensitive to long locks because a blocked transition can create duplicate human action.
Validate the schema with adversarial scenarios
Test two approvers deciding simultaneously, a request edited during review, a delegate expiring, duplicate API submission, out-of-order event, withdrawn approval, requester attempting self-approval and a fulfillment timeout after commit. Assert both current state and complete history. Use property tests for allowed transitions and database tests for constraints. Simulate deadlock or serialization retry. Verify tenant predicates on every query path, including reports and support tools.
Review the data model with business owners, security, operations and reporting users. Give them reconstructed timelines and ask whether each decision can be explained. Document state definitions and ownership. Generate diagrams from migration-controlled schema where possible, but keep domain rationale beside the code. A professional approval system should answer who approved what version, under which policy and authority, when, with which evidence, and what happened afterward without reading application logs as the primary source.
Key takeaways
- Separate stable request identity from immutable submitted versions.
- Persist the resolved workflow plan, assignments and policy context.
- Record decisions and audit events append-only with idempotency.
- Use transactions, locks and constraints to protect concurrent transitions.
- Reconcile queues, outbox messages, attachments and derived status.
Frequently asked questions
Should the whole request be stored as JSON?
Use JSON for flexible type-specific details, but keep tenant, identifiers, status, money, dates and frequently queried fields typed and constrained. Validate payload schema per request type and version it.
Can audit history be implemented only with database triggers?
Triggers can capture row changes but may lack business intent and actor context. Combine protected application domain events with database-level controls where needed, and test that every transition produces complete evidence.
How are parallel approvals completed safely?
Insert each decision idempotently, lock or serialize the step transition, recalculate quorum from valid distinct decisions and update status in the same transaction. Define rejection and withdrawal rules explicitly.
Conclusion
A durable approval schema makes business authority inspectable. Version the submitted record, persist the approval plan, constrain assignments, append decisions and publish side effects through an outbox. Protect transitions with short idempotent transactions and test adversarial concurrency and tenant access. The resulting database supports fast work queues while preserving the evidence needed to explain every approval long after the interface and organization have changed.