Business Process Automation in Node.js and Postgres: Approval Flows

Business process automation in Node.js and Postgres works best when every approval is a database row with one explicit state, and every change to that state goes into an event log. Add a scheduled job that finds requests past their deadline and moves them up the chain. After that, approvals stop disappearing into inboxes.

You probably know the symptoms already. A purchase request sits in a manager's email for nine days. A vendor asks why onboarding stalled. Finance wants to know who signed off on a refund, and all anyone can find is a forwarded thread with three possible approvers in it.

This post builds the fix: a request, review, approve or reject flow stored as a state machine in Postgres, plus an SLA job that escalates anything stuck.

Why do approvals get lost in the first place?

Email and chat have no state. A message is read or unread. It cannot be "in review, due Thursday, owned by the finance lead."

Many in-house tools add a second problem. They store status as a pile of booleans like is_reviewed, is_approved and is_rejected. Nothing stops a row from being approved and rejected at the same time. Nothing records who flipped the flag or when. And nothing notices when a request has not moved for a week.

A state machine fixes all three.

One column holds the current state, a fixed set of rules says which moves are legal, and a log stores every move.

What does it cost to get this wrong?

The obvious cost is delay: blocked purchase orders, late vendor payments, new hires waiting on equipment. The quieter cost shows up months later.

When an auditor or a customer asks who approved something, you need a timestamped answer with a name on it. Rebuilding that from inboxes takes days and is often incomplete. Teams that bolt states onto a boolean schema after launch also end up migrating live data while requests are mid-approval. That is usually where duplicate approvals and double-processed requests come from.

Who does not need to build this?

If you handle a few approvals a week with a single approver, skip the code. A shared form feeding a spreadsheet, or the approval feature in a tool you already use, will do the job. Build this when you have several approvers, deadlines that matter, or a need to prove the history.

How do you model business process automation in Node.js and Postgres?

Start with two tables. One holds each request and its current state. The other is an append-only log of every transition.

CREATE TABLE approval_requests (
  id               bigserial PRIMARY KEY,
  title            text NOT NULL,
  requested_by     text NOT NULL,
  assignee         text NOT NULL,
  state            text NOT NULL DEFAULT 'submitted'
                   CHECK (state IN ('submitted', 'in_review', 'escalated', 'approved', 'rejected')),
  due_at           timestamptz NOT NULL,
  escalation_level int NOT NULL DEFAULT 0,
  created_at       timestamptz NOT NULL DEFAULT now()
)

The CHECK constraint means Postgres itself refuses an unknown state, even if a bug in your app tries to write one.

CREATE TABLE approval_events (
  id          bigserial PRIMARY KEY,
  request_id  bigint NOT NULL REFERENCES approval_requests(id),
  from_state  text,
  to_state    text NOT NULL,
  actor       text NOT NULL,
  note        text,
  created_at  timestamptz NOT NULL DEFAULT now()
)

Never update or delete rows in approval_events. To enforce that, revoke UPDATE and DELETE on the table from your application's database role.

Which transitions should be allowed?

Keep the rules in one place, as plain data. Anyone reviewing the code can read the whole process in a few lines.

export const TRANSITIONS = {
  submitted: { start_review: 'in_review', escalate: 'escalated' },
  in_review: { approve: 'approved', reject: 'rejected', escalate: 'escalated' },
  escalated: { approve: 'approved', reject: 'rejected', escalate: 'escalated' },
  approved: {},
  rejected: {},
}

approved and rejected are terminal. Once a request lands there, no action can move it, which is exactly what an auditor wants to see.

How do you stop two people approving at once?

Lock the row inside a transaction before you check its state. SELECT ... FOR UPDATE makes a second approver wait until the first one commits. They then see the new state and get a clear error instead of a duplicate approval.

import pg from 'pg'
import { TRANSITIONS } from './transitions.js'

export const pool = new pg.Pool()

export async function transition(requestId, action, actor, note = null) {
  const client = await pool.connect()
  try {
    await client.query('BEGIN')
    const { rows } = await client.query(
      'SELECT state FROM approval_requests WHERE id = $1 FOR UPDATE',
      [requestId]
    )
    if (!rows.length) throw new Error('Request not found')

    const from = rows[0].state
    const to = TRANSITIONS[from]?.[action]
    if (!to) throw new Error(`Cannot ${action} a request that is ${from}`)

    await client.query(
      'UPDATE approval_requests SET state = $1 WHERE id = $2',
      [to, requestId]
    )
    await client.query(
      `INSERT INTO approval_events (request_id, from_state, to_state, actor, note)
       VALUES ($1, $2, $3, $4, $5)`,
      [requestId, from, to, actor, note]
    )
    await client.query('COMMIT')
    return to
  } catch (err) {
    await client.query('ROLLBACK')
    throw err
  } finally {
    client.release()
  }
}

The state change and the audit row commit together or not at all. You can never end up with an approved request and no record of who approved it. In production, also confirm that actor is the current assignee (or holds an admin role) before allowing approve or reject.

How does the SLA escalation job work?

A scheduled job runs every minute. It finds open requests whose due_at has passed, reassigns each one to the next person in the chain and gives it a fresh deadline.

CREATE INDEX approval_open_due_idx ON approval_requests (due_at)
  WHERE state IN ('submitted', 'in_review', 'escalated')

That partial index keeps the scan fast because it only covers open requests.

import cron from 'node-cron'
import { pool } from './transition.js'

const ESCALATION_CHAIN = ['team_lead', 'department_head', 'ops_director']

export async function escalateOverdue() {
  const client = await pool.connect()
  try {
    await client.query('BEGIN')
    const { rows } = await client.query(`
      SELECT id, state, escalation_level
      FROM approval_requests
      WHERE state IN ('submitted', 'in_review', 'escalated')
        AND due_at < now()
      ORDER BY due_at
      LIMIT 50
      FOR UPDATE SKIP LOCKED
    `)
    for (const r of rows) {
      const level = r.escalation_level + 1
      const next = ESCALATION_CHAIN[Math.min(level, ESCALATION_CHAIN.length) - 1]
      await client.query(
        `UPDATE approval_requests
         SET state = 'escalated', assignee = $1, escalation_level = $2,
             due_at = now() + interval '24 hours'
         WHERE id = $3`,
        [next, level, r.id]
      )
      await client.query(
        `INSERT INTO approval_events (request_id, from_state, to_state, actor, note)
         VALUES ($1, $2, 'escalated', 'system:sla', $3)`,
        [r.id, r.state, `Escalated to ${next}, level ${level}`]
      )
    }
    await client.query('COMMIT')
    return rows
  } catch (err) {
    await client.query('ROLLBACK')
    throw err
  } finally {
    client.release()
  }
}

cron.schedule('* * * * *', () => escalateOverdue().catch(console.error))

FOR UPDATE SKIP LOCKED is what makes this safe on several servers at once. Each instance grabs a different batch of overdue rows, so no request gets escalated twice. The system:sla actor makes the audit log show plainly which moves a person made and which ones the clock made.

Send emails or Slack messages after COMMIT, using the returned rows. If you notify inside the transaction and it rolls back, someone hears about an escalation that never happened. For stronger guarantees, write notifications to an outbox table in the same transaction and let a separate worker send them.

What should you decide before going live?

These rules cover most of the questions that come up in the first month.

• If SLAs count business hours, calculate due_at with a working calendar that skips weekends and holidays. Otherwise a 24 hour deadline set on Friday evening escalates on Saturday.
• If the approver depends on amount or department, store the escalation chain on the request when it is created. Org charts change, and you want the chain that applied at the time.
• If a request needs two sign-offs, add an approvals table with one row per required approver and move to approved only when all are in. Do not invent extra states for this.
• If people already approve from email, keep that habit. Put signed, single-use approve and reject links in the notification, and have both call the same transition() function.
• If you would rather not run cron in Node, pg_cron can call the same SQL from inside Postgres on one schedule.

What is the next step?

Map your current approval process on paper first: every state, who owns it, and how long it may sit there. If that takes more than one page, the process needs simplifying before it needs code.

For the bigger picture on which operations workflows to automate first, read the guide on workflow automation for operations teams. If you want a second opinion on your approval flow or schema, Geminate Solutions will review it for free and reply within 24 hours.