How to Model Approval Workflows in a Database Without Boolean Chaos
A supplier application has been approved.
Then its bank details change.
Is the supplier still approved?
The answer sounds like a business-policy question, but it quickly becomes a database design problem. If the schema contains only is_approved = true, the database cannot explain what was approved, who approved it, which version they reviewed, whether another reviewer rejected it first, or whether a later change invalidated the decision.
This is where many apparently simple workflow databases become surprisingly fragile.
Approval systems appear everywhere: expense claims, vendor onboarding, insurance applications, research submissions, content publishing, account verification, purchasing, hiring, loan processing, compliance reviews, and internal operations.
The central modeling lesson is simple: an approval is usually not an attribute of a record. It is a decision about a particular state of that record.
Once that distinction becomes clear, the database schema becomes much easier to reason about.
The innocent-looking schema that causes trouble
Imagine a procurement application with a suppliers table:
suppliers(id, name, bank_account, tax_number, is_approved, approved_by, approved_at)
This looks perfectly reasonable when the workflow is described as:
- A supplier enters its information.
- A manager reviews it.
- The manager approves or rejects the supplier.
The design works until reality arrives.
Suppose supplier 418 is approved on Monday:
id: 418
name: North Harbor Components
bank_account: ACCT-7712
tax_number: TX-90381
is_approved: true
approved_by: 27
approved_at: 2026-09-03 10:42
On Thursday, someone changes the bank account to ACCT-9948.
The database still says the supplier is approved.
But what exactly did employee 27 approve?
Did they approve North Harbor Components as an organization? The original bank details? The tax information? Every future modification to the supplier record?
The boolean cannot tell us.
The problem is not that the database lacks another status value. The problem is that the schema has combined two different facts:
- the current contents of the supplier record;
- a historical decision made about some version of those contents.
Investigate the workflow before designing the status column
When researching an approval workflow, asking stakeholders “what statuses do you need?” is often too shallow.
A better investigation traces actual decisions.
Take several real or realistic cases and ask:
- What information was visible when the reviewer made the decision?
- Can that information change afterward?
- Can more than one person review the same item?
- Can reviewers disagree?
- Can approval expire?
- Can a rejection later become an approval?
- Can an approved item be edited?
- Does editing require another review?
- Are some changes important enough to invalidate approval while others are not?
- Do reviewers approve the entire record or only part of it?
Those questions reveal the real data model more reliably than a list of UI statuses.
For example, procurement staff might initially say that suppliers are simply “pending,” “approved,” or “rejected.” Studying real cases may reveal something richer:
A compliance reviewer checks the tax registration. A finance reviewer checks payment information. Both decisions are required. A supplier can correct rejected information and resubmit. Changing payment details after approval triggers another finance review but does not require compliance to review the tax registration again.
That is no longer a three-status problem.
It is a model involving submissions, review scopes, decisions, revisions, and current eligibility.
Model the decision, not just the result
A stronger schema begins by making a review decision a first-class record.
For example:
suppliers(id, name, current_submission_id)
supplier_submissions(id, supplier_id, submitted_at, submitted_by)
supplier_reviews(id, submission_id, review_type, reviewer_id, decision, decided_at, reason)
Now the database can preserve records such as:
submission_id: 812
review_type: compliance
reviewer_id: 14
decision: approved
decided_at: 2026-09-03 09:18
and:
submission_id: 812
review_type: finance
reviewer_id: 27
decision: approved
decided_at: 2026-09-03 10:42
The meaning is much more precise.
The reviewer did not approve “supplier 418 forever.” They approved submission 812 for a particular review purpose.
If the supplier later changes information and creates submission 829, the old decisions remain historically valid while no longer determining the status of the new submission.
The important question: what exactly is being approved?
This is often the hardest part of the schema design.
Consider three possible interpretations.
Model A: approve the entity.
A reviewer approves supplier_id = 418.
This works when approval truly applies to the identity itself and later attribute changes do not matter.
Model B: approve a submission.
A reviewer approves submission_id = 812.
This is useful when users submit a bundle of information for review and modifying that bundle should require reconsideration.
Model C: approve a specific subject or scope.
A finance reviewer approves the payment profile while a compliance reviewer approves the legal identity.
This fits systems where different parts of a record have separate governance rules.
None of these is universally correct.
The right choice comes from investigating what the organization believes remains valid after data changes.
This is a useful principle for database research more broadly: when a business says something is “approved,” identify the object of the approval before adding an approval field.
Why a giant status column usually becomes misleading
A common response to workflow complexity is to add more status values.
The supplier might eventually have:
draft, submitted, compliance_pending, finance_pending, compliance_approved, finance_approved, approved, rejected, changes_requested, resubmitted, suspended.
The problem is not the existence of statuses. Status fields can be useful.
The problem appears when one status field is expected to encode several independent facts.
Suppose compliance has approved a submission but finance has rejected it. What is the supplier’s status?
rejected loses the compliance decision.
compliance_approved_finance_rejected preserves more information but starts turning combinations of facts into vocabulary.
Add security review, regional approval, and legal review, and the number of possible combinations grows rapidly.
A better relational database design stores the independent facts independently and derives the overall workflow state when needed.
The application can still display “Pending finance review” to the user. That does not mean the database needs one column containing the entire history and meaning of the workflow.
Current state can be derived from historical decisions
This raises a practical question: if decisions are stored separately, how does the system know whether something is currently approved?
Often, current state is a conclusion produced from several records.
Imagine the business rule:
A supplier submission is eligible only when both compliance and finance have approved the current submission.
The database can determine current eligibility by examining the decisions attached to the current submission.
Conceptually:
submission 829
compliance → approved
finance → pending
overall result → not yet approved
The application may cache or materialize an overall status for convenience, but it should remain clear which records are authoritative.
This distinction between stored facts and derived state prevents many schema problems.
“Reviewer 14 approved compliance for submission 829 at 11:06” is a fact.
“Supplier 418 is currently fully approved” may be a derived conclusion based on several facts and business rules.
Do not overwrite rejection with approval
Another weak approach appears when a review row itself is updated repeatedly:
supplier_reviews(submission_id, reviewer_id, status, reason)
A reviewer rejects the submission on Monday. After corrections, the same row is changed to approved on Wednesday.
The current screen looks correct, but the database has forgotten Monday.
That can matter when investigating questions such as:
- Why did onboarding take nine days?
- Which requirement caused repeated rejection?
- Was approval granted before or after a particular correction?
- How often are applications returned for missing documents?
For systems where decision history matters, represent new decisions as new records rather than repeatedly replacing the old one.
For example:
review_decisions(id, submission_id, review_type, reviewer_id, decision, reason, created_at)
Records might show:
901 | 829 | finance | 27 | rejected | bank proof missing | Sep 5
944 | 829 | finance | 27 | approved | documents verified | Sep 7
Whether both decisions should belong to the same submission depends on your workflow. If correcting documents constitutes a new submission, the second decision should reference that new submission instead.
The important point is that the database architecture should preserve the sequence needed to explain what happened.
Changing data should force you to define invalidation rules
One of the most useful experiments when reviewing an approval schema is to take an approved record and modify one field at a time.
Change the supplier’s display name.
Should approval survive?
Change its bank account.
Should finance approval survive?
Change its tax identifier.
Should compliance approval survive?
Change an internal note.
Should anything happen at all?
This exercise exposes rules that rarely appear in the first requirements document.
It may lead to a schema where different pieces of information live in separate versioned subjects:
supplier_legal_profiles
supplier_payment_profiles
legal_profile_reviews
payment_profile_reviews
That design is more complex, so it should not be adopted automatically. But when independent review lifecycles genuinely exist, separating them can produce a database schema that matches the business far better than one enormous supplier record.
Approvals also need actors, authority, and context
Recording reviewer_id answers who clicked the button, but mature approval systems often need another question answered:
Why was that person allowed to make the decision?
Suppose an employee approved a purchase because they were the regional finance manager at the time. Six months later, they move to another department.
If the application simply joins the historical approval to the employee’s current role, the historical explanation may become misleading.
Depending on the importance of the workflow, the decision record may need contextual information such as:
reviewer_id
authority_role
decision
decided_at
reason
Some systems should reference an assignment or authorization record rather than duplicating role text. Others intentionally preserve a snapshot of the authority used at decision time.
This is another place where drawing the relationships before implementation helps. An ER diagram modeling tool can make it easier to see whether users, submissions, review assignments, and decisions are being modeled as distinct concepts instead of accumulating in one table.
Exceptions should usually be data too
Real approval workflows almost always develop exceptions.
A director may override a rejection. A compliance team may approve an application conditionally. An emergency purchase may bypass the normal sequence.
The dangerous implementation is hidden procedural logic:
If the user is an administrator, just set approved to true.
That produces the correct immediate result while making the database harder to explain later.
A stronger model records the exceptional decision explicitly:
decision_type: override
overrides_decision_id: 901
authorized_by: 6
reason: emergency supplier required for plant repair
Not every application needs that degree of formality. But if exceptions affect money, access, compliance, publication, safety, or contractual obligations, invisible overrides are expensive technical debt.
How to review an approval workflow before building it
Before finalizing this kind of database design, test the model with a small set of scenarios rather than only reviewing the happy path.
- Approve an unchanged submission. Confirm that the simplest case remains simple.
- Reject and resubmit. Check whether the database preserves both the original decision and the corrected attempt.
- Have two reviewers disagree. Determine whether those are independent facts or whether one decision supersedes another.
- Edit something after approval. Decide precisely which decisions remain valid.
- Replace the reviewer. Verify that the historical record still explains the original decision correctly.
- Apply an exception. Make sure the exception is represented explicitly enough for future investigation.
This kind of scenario testing is useful far beyond approval systems. It is a practical form of database research: instead of asking whether the ER diagram looks tidy, you ask whether the schema can truthfully represent difficult events.
Resources on database design and data modeling can help with structural techniques, but the decisive work often happens before the tables are finalized: identifying which facts must survive when the workflow changes.
A workflow database should be able to explain itself
The best approval schema is not necessarily the one with the most tables, the most historical records, or the most sophisticated event model.
It is the one whose complexity matches the consequences of the decisions being stored.
A lightweight internal checklist might genuinely need only a status and timestamp. A financial authorization system may need submissions, review assignments, immutable decisions, overrides, versioned subjects, and explicit authority.
The mistake is assuming both systems can be modeled with the same is_approved column.
When reviewing an approval workflow, ask one final question:
If someone challenged this decision six months from now, could the database explain exactly what was reviewed, who decided, what they decided, and what happened afterward?
If the answer is no, the schema is probably storing the outcome while losing the decision itself.
And in real systems, the decision is often the more valuable data.
Recent Comments