ERP Approval Matrix Design: Stop Hardcoding Approval Chains

The first approval feature I shipped for ANCoraPRO, the ERP I built for a real client on React, NestJS, and PostgreSQL, had the approval chain written straight into the service code: supervisor, then finance manager, then director. It worked perfectly for exactly four months — until the client restructured, added a procurement department, and wanted invoices above a certain amount to need two finance signatures. Every one of those changes was a code deployment.

The rewrite taught me the lesson this post is about: approval logic is business configuration, not application code. The deliverable is an approval matrix — rules stored as data, keyed on amount bands, document types, roles, and departments — that an admin can edit from a screen. Here is how I design that now, schema included, plus the edge cases that will find you in production whether you design for them or not.

Why Hardcoded Chains Always Lose

Hardcoding feels faster on week one, and for a two-step chain it genuinely is. But organizations change approvers far more often than developers expect — promotions, resignations, restructures, audit findings, new spending policies. Compare the two approaches honestly and the matrix wins everywhere except initial effort:

Aspect	Hardcoded chain	Configurable matrix
Changing an approver or threshold	Code change, review, deployment — days, and a developer in the loop forever.	Admin edits a rule row on a screen — minutes, finance owns it.
Organizational restructure	Rewrite of every affected branch; high regression risk in untouched flows.	Deactivate old rules, insert new ones with a validity date; old documents keep their history.
Audit answering — who could approve what, when	Read git history and pray the deploy dates line up with the question.	Query rule rows with valid_from and valid_until; the answer is data.
Initial build effort	Low — an if-chain and two role checks.	Higher — rule tables, resolution engine, admin UI. Pays back on the first reorg.

The Four Dimensions of a Real Approval Matrix

Every approval requirement I have gathered from finance teams decomposes into four dimensions. Model these explicitly and almost any policy fits without schema changes:

Document type

Purchase orders, AP invoices, expense claims, leave requests. Each type gets its own rule set — a PO matrix and an expense matrix rarely share thresholds.

Amount band

Half-open ranges in IDR: zero up to 10 million, 10 to 100 million, 100 million and above. Half-open intervals (min inclusive, max exclusive) prevent the classic boundary double-match.

Department or cost center

Procurement approves differently than marketing. A NULL department on a rule means it applies company-wide, and specific-department rules override it.

Approver step definition

Each matching rule yields ordered steps. A step points to a role, a named user, or a relationship like manager-of-requester, plus a quorum for N-of-M approvals.

Microsoft's Dynamics 365 workflow documentation describes exactly this shape in its expense report example: a 7,000 dollar report routes through two approvers, an 11,000 dollar one adds a third. The thresholds and participants are workflow configuration, not compiled logic — that is the standard the big systems set, and a custom ERP should not do worse.

A Schema That Holds Up: Rules, Steps, and Resolution

Two tables carry the whole design: approval_rule holds the matching dimensions, approval_rule_step holds the ordered approvers for each rule. Resolution picks the most specific active rule — department-specific beats company-wide, then explicit priority breaks ties:

-- Rules are data, not code
CREATE TABLE approval_rule (
  id             BIGSERIAL PRIMARY KEY,
  document_type  TEXT NOT NULL,      -- 'PO' | 'AP_INVOICE' | 'EXPENSE'
  department_id  BIGINT REFERENCES department(id),  -- NULL = any
  amount_min     NUMERIC(18,0) NOT NULL DEFAULT 0,  -- IDR, inclusive
  amount_max     NUMERIC(18,0),                     -- NULL = no cap
  priority       INT NOT NULL DEFAULT 100, -- lower wins on overlap
  is_active      BOOLEAN NOT NULL DEFAULT true,
  valid_from     DATE NOT NULL,
  valid_until    DATE                       -- NULL = open ended
);

CREATE TABLE approval_rule_step (
  id              BIGSERIAL PRIMARY KEY,
  rule_id         BIGINT NOT NULL REFERENCES approval_rule(id),
  step_order      INT NOT NULL,            -- 1, 2, 3...
  approver_type   TEXT NOT NULL,  -- 'ROLE' | 'USER' | 'MANAGER_OF'
  approver_ref    BIGINT NOT NULL,
  quorum          INT NOT NULL DEFAULT 1,  -- N-of-M approvers
  UNIQUE (rule_id, step_order)
);

-- Resolution: most specific active rule wins
SELECT r.* FROM approval_rule r
WHERE r.document_type = $1
  AND (r.department_id = $2 OR r.department_id IS NULL)
  AND $3 >= r.amount_min
  AND ($3 < r.amount_max OR r.amount_max IS NULL)
  AND r.is_active
  AND CURRENT_DATE BETWEEN r.valid_from
      AND COALESCE(r.valid_until, CURRENT_DATE)
ORDER BY (r.department_id IS NULL), r.priority
LIMIT 1;

The resolution query runs once, at submission time. That moment matters: the engine snapshots the resolved steps into an approval_instance table attached to the document. The document then works through its own frozen copy of the chain. If an admin edits the matrix tomorrow, in-flight documents are untouched — only new submissions see the new rules.

Pro tip: snapshot-at-submission is the single most important behavior to get right. The alternative — re-resolving rules on every approval click — means an admin edit can silently re-route documents that are mid-chain, and your audit trail can no longer explain why a document went to whoever it went to.

The Edge Cases That Will Find You

These five scenarios are not exotic. Each one appeared within the first year of my ERP running in production, and each needs an explicit design decision:

Approver is also the requester — a manager submitting their own purchase request must not self-approve. Skip to the next step or escalate one level up; pick one policy and enforce it in the engine.
Absent approvers — people go on leave. You need delegation with an expiry date, set by the approver or by an admin, and the audit trail must record acted-as-delegate, not impersonation.
Overlapping rules — two rules match the same document. Deterministic ordering (specificity, then priority, then lowest id) plus an admin-screen warning when a new rule overlaps an existing one.
No rule matches — a document type and amount combination nobody configured. Fail closed: block submission with a clear message, and alert the admin. Auto-approving on a configuration gap is how unauthorized spending happens.
Amount edited mid-flight — an invoice corrected upward may now belong in a higher band. The safe policy: any change to the amount voids current approvals and re-resolves from step one.

Whatever you decide for each edge case, write the decision into the engine as code and into the client's SOP as one sentence each. The expensive failure mode is not picking a wrong policy — it is two screens silently implementing different policies.

Rolling Out a Matrix Without Chaos

Migrating a live ERP from hardcoded chains to a matrix is a data migration plus a behavior change, and the sequencing matters:

Transcribe the current hardcoded behavior into rule rows first, verbatim — same approvers, same thresholds. Ship the engine running the same policy as before.
Run dual-resolution in staging: for two weeks of real documents, log what the old code and the new engine each would have decided, and diff the outputs to zero.
Cut over with feature-flag scope: one document type first (expenses are lowest risk), then POs, then AP invoices.
Hand finance the admin screen only after cutover is stable, with rule changes requiring a second admin to activate — four-eyes on the matrix itself.
Schedule a quarterly matrix review with finance. Stale rules pointing at departed employees are the matrix equivalent of dead code.

The Takeaway

An approval matrix is the difference between an ERP the client operates and an ERP the developer babysits. Rules as data, snapshot at submission, fail-closed resolution, and delegation with expiry — those four decisions cover ninety percent of what finance will ever ask for, and they turn org-chart churn from a deployment into a form edit.

If you are starting a NestJS build today: resist the if-statement. The two-table schema above costs maybe a week including the admin screen. The hardcoded version costs a week too — just spread over every reorganization for the life of the system.

Sources and further reading