Approval Workflow Database Design: Requests, Versions and Review Tasks

An Approve button is easy to build. A request that comes back for correction, changes hands while somebody is reviewing it, and still has an explainable history takes more thought.

That is where I start an approval workflow: with the request, the people responsible for it and the information each decision must preserve. The screens and database should make those rules visible.

For a custom approval application in PostgreSQL, I start with three records: the editable request, a fixed submitted version and an assigned review task. Decisions name the version being reviewed, and a transaction updates the task, request and decision history together. That gives both the database and the interface a clear answer to what happens next.

This follows the enterprise application work described in Four Decisions I Made Before Writing Any Features. That article covers the architectural foundations. Here I want to follow the workflow through the interface, including the parts that do not fit neatly behind an Approve button.

I will use a small equipment-purchase request with one review step. It is an original teaching example, with its own rules and schema. The same questions apply to expense claims, document reviews, access requests and many other internal applications. This is a database and application design walkthrough, rather than configuration instructions for a particular approval product.

View the complete PostgreSQL workflow example on GitHub. It includes the schema, Python transaction commands, a correction-and-reassignment walkthrough, and concurrency tests. Clone the examples repository and follow the README.md in the approval-workflow directory; it uses a disposable local database and requires Docker, Python 3.12 or newer, and uv.

When this approval model is worth building

The useful signal is usually a question the current process cannot answer: which version was approved, who is responsible now, or whether a returned request still needs attention. When those answers are spread across email, a spreadsheet and somebody's memory, a shared application can make the process easier to operate.

I would first check whether an existing tool can enforce the team's rules and connect to its other systems. A custom application makes more sense when the workflow needs specific permissions, integrations or review screens that the existing tools cannot support well. The model below is for that application; it is not a reason to build a workflow engine for every approval.

Write the rules before choosing the fields

Before I draw a database table, I want a process owner to be able to read the rules and correct them.

For this example:

• An employee can create and edit their own draft.
• Submission sends a fixed version to an assigned reviewer.
• The requester cannot approve their own request.
• The reviewer can approve, reject or ask for changes.
• Rejection and requests for changes require an explanation.
• Asking for changes returns the next action to the requester.
• Resubmission creates a new submitted version and a new review task.
• An authorised administrator can reassign open work, with a recorded reason.
• Approval completes this review. It does not mean the equipment has been purchased.

That last rule is easy to overlook. A team might use “approved” to mean permission to buy, a completed purchase, or a confirmed delivery. Those are different business events.

I want those meanings settled before choosing the status badges.

The rules also expose questions that a happy-path screen hides. Does rejection close the request permanently? Can the requester withdraw it during review? Can the same reviewer handle the corrected version? Who deals with an unassigned request?

The example treats rejection as terminal and sends corrected work back to an eligible reviewer. Withdrawal is outside its scope. Those are choices to make with the process owner, not universal approval rules.

A state should tell somebody what happens next

Here is the request lifecycle:

draft ----------------submit----------------> in_review
                                                |
                       +------------------------+------------------+
                       |                        |                  |
                    approve               request changes        reject
                       |                        |                  |
                    approved             changes_requested      rejected
                                                |
                                       edit and resubmit
                                                |
                                            in_review
                                      with a new submission

The return path is part of the core workflow. It should not be an exceptional administrator operation.

I use a transition table to connect those states to both behaviour and interface:

I want this table to describe actual server rules. Hiding a button in React is useful guidance, but the API must reject the same forbidden transition when somebody sends a request directly.

Keep assignment separate from the decision

A request can still be in review after it changes reviewers.

If every assignment change creates a new business status, the state model becomes difficult to understand: “pending manager,” “pending replacement manager,” “pending delegated manager.” Those labels mix two different questions:

• What stage has the request reached?
• Who is responsible for the next action?

I keep the current review task responsible for assignment. The request retains its business state.

This distinction also appears in Camunda's user-task lifecycle, which treats assignment independently from work-state transitions. That is a useful modelling idea even when an application does not need a workflow engine.

A pooled queue needs an explicit ownership rule. Either an eligible reviewer claims the task before deciding it, or the application permits any eligible pool member to decide through a guarded transaction. The interface and API need to agree on which behaviour applies.

The example uses one assigned reviewer. That gives the employee a clear answer to “who has this now?” and keeps the first implementation small.

An inactive reviewer must not leave work invisible. I would make open assignments discoverable to an authorised administrator, provide a reassignment action, and preserve the old assignment in the history. Sending another notification to an unavailable person does not fix ownership.

The employee and reviewer need different screens

The two screens serve different tasks, even though they concern the same request.

The employee is preparing a request and trying to find out what is happening to it. Their screen should make these things easy to see:

• What they are requesting and why.
• Whether their changes have been saved.
• What must be fixed before submission.
• Who has the next action.
• What a reviewer asked them to change.

For returned work, the important part might look like this:

Changes requested

Reviewer note
Please explain why the existing equipment cannot be used.

Your next action
Update the justification and submit a new version.

[Edit request]     [View submitted version]

The reviewer has a different job. They need to decide whether a specific request satisfies the relevant rules.

Their queue should identify the request, requester, submission time and any information that affects prioritisation. Their detail view should bring the justification, supporting documents, submitted version and earlier review notes together.

Equipment request
Submission 2 · Assigned to you

Requested item     Additional monitor
Business reason   Review source material beside the working document

Changed since submission 1
The requester added a justification for the existing equipment.

[Approve]     [Request changes]     [Reject]

The “changed since” summary is useful only if it can be derived reliably. If I have not implemented a trustworthy comparison, I would provide the earlier submission alongside the current one instead of inventing an AI summary and treating it as evidence.

On mobile, the reviewer still needs that context before the actions. A sticky action bar can help, but it should not cover the documents or make a consequential decision easier to trigger accidentally.

Return for correction needs a complete path

“Request changes” cannot just set a status.

It needs to record what is missing, show that explanation to the requester, make the appropriate fields editable, and give the corrected request a route back into review.

In this workflow, requesting changes requires a reason. “Please provide the delivery location” is actionable. “Wrong” usually creates another conversation outside the application.

The reviewer should be able to cancel the return dialog without losing their place. If the network request fails, keep the reason they typed. If the response times out, do not claim the return was rejected by the server; the write might already have happened.

When the employee resubmits, the earlier review stays attached to the earlier submission. A new task points at the new submission.

That lets the next reviewer answer two questions together: what does the request say now, and what happened during the previous review?

That history belongs in the product, where the requester and reviewer can use it.

Separate editable content from submitted evidence

I give those responsibilities three separate records in the database:

The current request can be convenient to query without becoming the only record of the past.

Here is what those relationships look like after a correction:

Request 42                         editable working content + current state
|
+-- Submission 1                   fixed item, justification, document versions
|   `-- Review task 1              closed: changes requested, reason preserved
|
`-- Submission 2                   new fixed snapshot of the corrected request
    `-- Review task 2              open: assigned reviewer + task revision

One request has many submissions. Each submission has one review task in this model, and at most one task for that request can be open. A decision on task 1 stays attached to submission 1 even after the employee changes the working content.

Below is the core schema from the runnable companion. schema.sql also includes a small decision-history table and triggers that reject ordinary updates or deletions of submitted evidence. Identity tables, document storage and notification delivery are outside the example.

CREATE TABLE approval_requests (
    id uuid PRIMARY KEY,
    requester_id uuid NOT NULL,
    state text NOT NULL CHECK (
        state IN ('draft', 'in_review', 'changes_requested',
                  'approved', 'rejected')
    ),
    working_content jsonb NOT NULL CHECK (jsonb_typeof(working_content) = 'object'),
    revision bigint NOT NULL DEFAULT 1 CHECK (revision > 0)
);

CREATE TABLE request_submissions (
    id uuid PRIMARY KEY,
    request_id uuid NOT NULL REFERENCES approval_requests(id),
    submission_number integer NOT NULL CHECK (submission_number > 0),
    content jsonb NOT NULL,
    submitted_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (request_id, submission_number),
    UNIQUE (request_id, id)
);

CREATE TABLE review_tasks (
    id uuid PRIMARY KEY,
    request_id uuid NOT NULL,
    submission_id uuid NOT NULL UNIQUE,
    assignee_id uuid NOT NULL,
    state text NOT NULL CHECK (
        state IN ('open', 'approved', 'changes_requested', 'rejected')
    ),
    revision bigint NOT NULL DEFAULT 1 CHECK (revision > 0),
    decided_at timestamptz,
    FOREIGN KEY (request_id, submission_id)
        REFERENCES request_submissions(request_id, id)
);

CREATE UNIQUE INDEX one_open_review_per_request
    ON review_tasks (request_id)
    WHERE state = 'open';

The composite foreign key prevents a task from pairing one request with another request's submission. The partial unique index enforces this example's rule that a request has at most one open review task.

Neither constraint proves that a reviewer is eligible or that a submission contains acceptable business information. Those rules still belong in the command handling.

The companion rejects ordinary updates and deletes against submissions and history. That does not make it tamper-proof against a database owner. A deployed application also needs restricted runtime privileges and controlled write paths; the audit-trail article covers that boundary in detail.

Document references need the same care. If a submitted version points at a file that can later be replaced under the same identifier, the stored JSON is fixed but the evidence is not. Retain a stable document version for the submitted record.

A role is only the first permission check

“Reviewer” is not sufficient authority to approve every request.

For a decision, the server must establish that the person is currently allowed to review, can access this request, is its assigned reviewer, and is acting on the active task. This example also forbids approving one's own request.

The browser can receive allowed actions to guide its controls. Those actions are a snapshot. The server must check again when the decision arrives.

This matters even with one reviewer. Someone may open a task, step away, and come back after an administrator has reassigned it. Their old tab still contains a perfectly usable Approve button.

The correct response is a conflict or access explanation and the current authorised view. The old screen must not carry old authority into a new assignment.

Reassignment increments the task revision too. Moving a task away from a reviewer and later back to them must not make an old tab current again.

If immediate permission revocation must win against concurrent writes, define how the permission change and decision are serialised too. Checking a role once at login cannot provide that guarantee.

Commit the decision against the version on screen

The client should identify both the submission and the revision of the task it saw:

{
  "decision": "approved",
  "submission_id": "569b53b3-b7fb-4546-820a-135414c922dc",
  "expected_task_revision": 4
}

The actor comes from the authenticated server session. It is not another field the client gets to choose.

Inside the decision transaction, I first lock the parent request, load the task and check the current business and permission rules. Every command that changes this request follows the same lock order. PostgreSQL's row-locking documentation describes how FOR UPDATE coordinates conflicting writers.

The critical task update then has explicit guards. These are bound parameters, not string interpolation:

UPDATE review_tasks
SET state = $1,
    revision = revision + 1,
    decided_at = now()
WHERE id = $2
  AND request_id = $3
  AND assignee_id = $4
  AND submission_id = $5
  AND revision = $6
  AND state = 'open'
RETURNING id, request_id, submission_id, revision;

The handler allowlists the decision before executing this statement. After the visibility and permission checks, an update that returns no row means the decision cannot be applied to the state the client named. The handler rolls back rather than carrying on with the other writes. The companion uses psycopg's %s parameter notation for the same bound values.

On success, that same transaction updates the request state and appends the attributable decision event. The companion deliberately stops there. If the application sends notifications, I would enqueue them in that transaction and deliver them after commit; the outbox article covers that separate concern.

This guards against stale decisions. It does not, by itself, give a client an identical successful response when it retries after a lost acknowledgement. If the application needs that behaviour, the command also needs a defined idempotency contract.

Follow a correction and reassignment through the same records

The companion's workflow.py connects the schema to explicit commands: create, save, submit, decide and reassign. Each command owns a transaction. Changes to an existing request lock the parent row before reading the state they are about to change.

Submission copies the current working content into a new submission and creates its task within that transaction. Resubmission calls the same command from changes_requested; it does not reopen the old task or replace the old content.

Here is the main sequence from example.py, using the connection, content and actor IDs defined there:

request_id = create_request(connection, requester, content)
first = submit_request(connection, request_id, requester, 1, reviewer)

decide_review(
    connection, first["id"], reviewer, first["submission_id"], 1,
    "changes_requested", "Explain why the existing monitor is unsuitable",
)

# Create and submit a correction; submission 1 stays fixed.
revision = save_draft(connection, request_id, requester, 3, corrected)
second = submit_request(connection, request_id, requester, revision, reviewer)

# Reassign this open task, incrementing its revision from 1 to 2.
moved = reassign_review(
    connection, second["id"], administrator, 1, substitute,
    "The original reviewer is unavailable",
)

decide_review(
    connection, moved["id"], substitute, moved["submission_id"],
    moved["revision"], "approved",
)

The request revision is 3 before the correction because the initial submission and return each incremented it. A real UI receives the current revision from the API; it should not predict that number. Request revisions guard edits and submissions. Task revisions guard decisions and reassignment.

The final database contains one approved request, two submitted versions and two closed tasks. The first task records a return for correction. The second records approval of the corrected submission by the replacement reviewer. The earlier content and reason remain available to the interface.

Sending an approval from the original reviewer's old tab is a separate test. It still names task revision 1, so the server refuses it after reassignment. Reassigning the task back to that reviewer increments the revision again; it does not restore the old tab's authority.

The companion tests also issue competing approval and rejection commands through two database connections. Only one can decide the open task. Another test forces the history insert to fail and checks that neither the task nor the request is left marked approved.

These checks exercise PostgreSQL transactions, not a complete authenticated application. The calling server must still establish access to the request, reviewer eligibility and administrator authority. The example uses supplied actor IDs to make the transactions easy to run; those IDs are never a substitute for authentication in a deployed API.

Let the history answer a person's question

An event such as “status updated” leaves too much interpretation to whoever is reading it.

For the product view, I want a sequence like:

Submission 1 sent for review
Reviewer requested a clearer justification
Requester submitted version 2
Open review reassigned, with the reason recorded
Assigned reviewer approved submission 2

The stored events carry actor, request, submission and task identifiers, a timestamp, action and reason. The interface should link each decision to the version it concerns, and show that history only to people allowed to access the request.

Test one request through the whole product

My first acceptance check follows a complete path: create, save, submit, open the review, return for correction, edit, resubmit and approve. Reload between steps so the journey cannot depend on state held only in the browser.

Then I would test the interruptions:

The tests do more than check endpoints. They verify that each person can tell what happened and perform the next allowed action.

Extend the model when the process requires it

The single-review example is a starting point, not a claim that every approval process is simple. Before adding more stages, I would define what the extra complexity means for the people using it:

Those capabilities are not implemented in the companion. In particular, the one-open-task index intentionally prevents parallel review; adding another reviewer column would not supply its missing completion rules.

A workflow engine can help when the application needs those orchestration capabilities. A small, fixed process can also work well with explicit application commands and database transactions. I would choose based on the rules the team actually needs to operate.

The expense management case study shows the wider delivery work around this kind of application, including employee and reviewer interfaces, permissions and validation.

The test I keep coming back to is whether someone can open a request and understand what they need to do, who acts next, and why. When the interface and stored history agree on those answers, the approval workflow starts to feel like a usable application.