Skip to content

Approval System

A flexible, multi-tier approval system for FarmCove Delta that supports configurable approval workflows per project for different record types (transactions, contracts, documents, etc.).

Table of Contents

  1. Overview
  2. Core Concepts
  3. Architecture
  4. Configuration Guide
  5. Approval Flows
  6. Business Rules
  7. Permissions
  8. Database Schema
  9. Views Reference
  10. Category Seed Data
  11. User Interface

Overview

The approval system enables project administrators to define approval workflows that records must pass through before being finalized. Key features include:

  • Per-Project Configuration: Each project can have different approval rules
  • Type/Subtype Granularity: Configure approvals for specific record types (e.g., only Invoices, not Expenses)
  • Multi-Tier Workflows: Sequential approval levels with different approvers at each tier
  • Conditional Tiers: Tiers can be skipped based on conditions (e.g., amount thresholds)
  • Entity-Based Approvers: Approvers are project team members (entities with user accounts)
  • Full Audit Trail: Every approval action is tracked with timestamps and notes
  • Query/Discussion: Approvers can query records and communicate with submitters
  • Soft Approvals: Automatic review workflow for records with processing issues but no formal approval configuration

Architecture Diagram

+---------------------------------------------------------------------------+
|                              PROJECT                                       |
|                        "Summer Film 2024"                                  |
|                     approvals_enabled = true                               |
+---------------------------------------------------------------------------+
                                    |
                                    v
+---------------------------------------------------------------------------+
|                      APPROVAL CONFIGURATION                                |
|              Type: transactions / Subtype: Invoice                         |
+---------------------------------------------------------------------------+
                                    |
            +-----------------------+-----------------------+
            v                       v                       v
+-------------------+   +-------------------+   +-------------------+
|      TIER 1       |   |      TIER 2       |   |      TIER 3       |
|  amount > 100     |   |  amount > 1000    |   |  amount > 5000    |
|                   |   |                   |   |                   |
|  Approvers:       |   |  Approvers:       |   |  Approvers:       |
|  - John (Entity)  |   |  - Finance Dir    |   |  - CFO            |
|  - Jane (Entity)  |   |    (Entity)       |   |    (Entity)       |
+-------------------+   +-------------------+   +-------------------+

Core Concepts

Approval Configuration

Defines that a specific type/subtype combination requires approvals for a project. For example:

  • Project "Summer Film 2024" + Type "transactions" + Subtype "Invoice" = Approval required
  • Project "Summer Film 2024" + Type "transactions" + Subtype "Expense" = No configuration = No approval needed

Tiers

Sequential approval levels within a configuration. Each tier:

  • Has a tier number (1, 2, 3...) determining order
  • Can have optional conditions that determine if approval is needed
  • Has one or more approvers assigned
  • Only ONE approval per tier is sufficient (other approvers are marked as skipped)

Conditions

Optional rules per tier that determine if approval is needed. Stored as JSONB:

{
  "logic": "ANY",
  "rules": [
    {
      "field": "amount",
      "operator": "gt",
      "value": "1000"
    }
  ]
}
  • logic: "ANY" (OR) or "ALL" (AND)
  • field: "amount" | "entity" | "country" | "sensitive_data"
  • operator: "gt" | "gte" | "lt" | "lte" | "eq" | "neq" | "in" | "not_in" | "is_set"

Multi-value operators (in / not_in) store their selections comma-joined in the rule's value (e.g. "GB,IN"); the evaluator splits on commas (an optional value_list array takes precedence when present).

If conditions are not met, the tier is automatically skipped.

Country conditions (country)

country is a multi-select field (valueType 'country', operators in / not_in) over the reference countries list; rules store ISO country codes. The ConditionBuilder pins a special "No country set" option (NO_COUNTRY_OPTION.value = __no_country__, from src/constants/common.ts) at the top of the list so records without a country can be explicitly routed instead of falling through unrouted.

At evaluation time the matched value comes from the record, not the rule: submitTransactionForApproval resolves the direction-aware counterparty entity (inbound → entity_id, outbound → customer_entity_id), reads its address country (entities.address_id → addresses.country_code), and injects it as conditionData.country — falling back to the __no_country__ sentinel when the transaction has no entity, the entity has no address, or the address has no country. A tier whose in rule includes "No country set" therefore matches those records.

Flag fields (sensitive_data)

sensitive_data is a flag field (valueType 'flag', operator is_set): a self-contained predicate with no user-entered operator/value — the ConditionBuilder renders only a legend, and the rule's value is '' and ignored. Its truth is supplied at evaluation time, not stored in the rule: a tier with a sensitive_data rule activates only when the record is sensitive. submitForApproval derives this as getRecordRequiredPermissions(recordType, recordId).length > 0 (the record's effective required-permissions, cascade-aware) and threads it as the isSensitiveRecord arg through getApplicableTiers → evaluateTierConditions, which injects it into the rule-evaluation data (flag applied last so the server value always wins).

To add another condition field, extend TransactionConditionField + its field-config and ConditionOperator + its operator-config in src/constants/approval.ts, AND add the operator to the explicit z.enum([...]) in conditionRuleSchema (src/schemas/approvalConfiguration.schema.ts) or validation rejects the rule.

Multi-value fields store comma-joined strings, not value_list

entity and country conditions use the in / not_in operators and are edited via MultiSelect. The builder stores the selection comma-joined in the rule's value (e.g. "GB,IN"), NOT in the optional value_list array. The evaluator's getRuleValueList (utils/approval.ts) therefore splits value on commas, preferring value_list only when present. Selectable values (entity UUIDs, ISO country codes, the __no_country__ sentinel) never contain commas, so the split is safe. When adding a new multi-value field, rely on this comma convention — don't reach for value_list.

Condition builder value inputs by valueType

ConditionBuilder renders the value control from the field's valueType: currency → CurrencyInput (project currency symbol via project.currency_code), entity/country → MultiSelect (or SearchableSelect for a single entity), flag → legend only, else a number Input. The amount field is currency, not number.

Pending-changes (amber highlight) is driven explicitly via savedConditions threaded from TierEditor (the tier's last-saved conditions). valueHasPendingChanges(rule, index) returns false when the rule matches its saved counterpart, true when it differs, and undefined (fall back to the control's own detection) when there's no saved counterpart. This is required because the tier editor is not remounted on save (see optimistic save below), so the inputs' built-in compare-against-mount detection can never clear itself. CurrencyInput in particular MUST be given an explicit hasPendingChanges — it reformats its display value on focus, which would otherwise trip a false amber. All condition controls also carry bg-background so they render white (not transparent) on load.

Tier save is an optimistic cache update (no refetch remount)

useSaveApprovalTiersMutation does NOT invalidate the tierDetails query on success for an edited tier. A refetch swaps the query-data identity and remounts the whole open tier editor — a visible "old values flash" and a reset of every field's pending-changes baseline. Instead the mutation optimistically patches the tierDetails cache in place (onMutate → setQueryData, matched by approval_tier_id), rolls back on error (onError → the snapshot), and only refetches tierDetails in onSettled when a new (temp- id) tier was created — that case genuinely needs the server-assigned id. Callers (TypeTabContent, SubtypeConfigCard) build the optimistic view row with tierEditorStateToDetailsView(tier, identity) and pass it as optimisticTiers. This matches the optimistic onMutate/setQueryData/onError-rollback pattern the other approval config mutations in that hook already use.

Approvers

Entities (project team members) assigned to approve at each tier:

  • Must have a user account (user_id IS NOT NULL)
  • Must have an active project relationship
  • One approval per tier is sufficient

Approval Request

Created when a record is submitted for approval. Tracks:

  • The record being approved (type + ID)
  • Current tier being evaluated
  • Overall status (Pending, Approved, Rejected, Queried)
  • Submitter information

Approval Instance

Individual approval assignments per approver. Each instance tracks:

  • Which approver is assigned
  • Whether the tier condition was met
  • The approver's decision and any notes
  • Timestamps for all actions

Soft Approval

A special approval flow for records that have processing issues but no formal approval configuration. Instead of auto-approving, the system creates a soft approval request for review:

  • Trigger: Record has pendingIssuesCount > 0 but no matching approval configuration
  • Approvers: Users with the approval:soft:view permission (project owners automatically have this)
  • Single Tier: Soft approvals have only one tier (tier_number = 1)
  • No Configuration: approval_configuration_id is NULL, is_soft_approval is TRUE
  • Tab Label: Displayed as "Issues Review" in the approval UI

This ensures records with problems are reviewed by designated users rather than being auto-approved.

The soft-approval path is also where a sensitive record lands when no tier can route it (every applicable tier is emptied by the sensitivity clearance filter, or by self-approval prevention). The soft pool gets the same self-approval + clearance filters applied. See "Unroutable sensitive records" under Business Rules for what happens when even the soft pool holds no clearance-holding approver.

Configuration Guide

Step 1: Enable Approvals for Project

Set projects.approvals_enabled = true for the project.

Step 2: Enable Type (Optional)

Create an approval_configuration with subtype_category_id = NULL to enable the type:

  • project_id: Your project
  • type_category_id: Category for 'transactions'
  • subtype_category_id: NULL (type-level enablement)

Step 3: Create Subtype Configuration

Create an approval_configuration for each subtype that needs approvals:

  • project_id: Your project
  • type_category_id: Category for 'transactions'
  • subtype_category_id: Category for 'Invoice'

Step 4: Add Tiers

Create approval_tiers for each approval level:

-- Tier 1: Manager approval for amounts > $100
INSERT INTO approval_tiers (approval_configuration_id, tier_number, name, conditions)
VALUES (config_id, 1, 'Manager Approval',
  '{"logic": "ANY", "rules": [{"field": "amount", "operator": "gt", "value": "100"}]}');

-- Tier 2: Finance Director for amounts > $1000
INSERT INTO approval_tiers (approval_configuration_id, tier_number, name, conditions)
VALUES (config_id, 2, 'Finance Director',
  '{"logic": "ANY", "rules": [{"field": "amount", "operator": "gt", "value": "1000"}]}');

Step 5: Assign Approvers

Add approval_tier_approvers for each tier:

INSERT INTO approval_tier_approvers (approval_tier_id, entity_id)
VALUES
  (tier1_id, john_entity_id),
  (tier1_id, jane_entity_id),
  (tier2_id, finance_director_entity_id);

Approval Flows

Example 1: $3000 Invoice - Full Approval Flow

Setup:

  • Tier 1: amount > $100 -> John, Jane
  • Tier 2: amount > $1000 -> Finance Director
  • Tier 3: amount > $5000 -> CFO

Flow:

  1. Invoice created ($3000) -> Transaction status: "Processed"
  2. System finds approval configuration -> Creates approval_request
  3. Tier 1 Evaluation: $3000 > $100? YES
  4. Creates instances for John and Jane (status: Pending)
  5. Transaction status -> "Submitted for Approval"
  6. John Approves
  7. John's instance -> Approved
  8. Jane's instance -> Skipped (reason: "Approved by another approver")
  9. Move to Tier 2
  10. Tier 2 Evaluation: $3000 > $1000? YES
  11. Creates instance for Finance Director (status: Pending)
  12. Finance Director Approves
  13. Instance -> Approved
  14. Move to Tier 3
  15. Tier 3 Evaluation: $3000 > $5000? NO
  16. Creates instance for CFO (status: Skipped, condition_met: false)
  17. No more tiers -> Request status: Approved
  18. Transaction status -> "Approved"

Example 2: $50 Invoice - Auto-Approved

Flow:

  1. Invoice created ($50)
  2. System finds approval configuration
  3. All Tiers Evaluated:
  4. Tier 1: $50 > $100? NO -> Auto-skip
  5. Tier 2: $50 > $1000? NO -> Auto-skip
  6. Tier 3: $50 > $5000? NO -> Auto-skip
  7. All tiers skipped -> Request status: Approved (no human intervention)

Example 3: Query Flow

  1. John queries the transaction (creates message with approval_request_id)
  2. John's instance status -> "Queried"
  3. Request status -> "Queried"
  4. Message thread continues until clarified
  5. John approves after clarification -> Flow continues normally

Example 4: Rejection Flow

  1. Finance Director rejects at Tier 2
  2. Instance status -> "Rejected" with decision_note
  3. Request status -> "Rejected" with reject_reason
  4. All remaining instances -> "Skipped" (reason: "Request rejected")
  5. Transaction status -> "Rejected"

Example 5: Higher Tier Early Approval

CFO (Tier 3 approver) can see and approve items still at Tier 1:

  • Creates/updates CFO's instance -> "Approved"
  • All lower tier instances -> "Skipped" (reason: "Approved by higher tier approver")
  • Request -> "Approved"

Example 6: Soft Approval - Transaction with Processing Issues

Scenario: An Expense transaction is created but has no approval configuration. However, AI processing flagged an issue (e.g., missing justification).

Flow:

  1. Transaction created -> AI processing completes with 1 pending issue
  2. System checks for approval configuration -> None found
  3. System checks pendingIssuesCount -> 1 issue found
  4. System creates soft approval:
  5. Creates approval_request with is_soft_approval = true, approval_configuration_id = NULL
  6. Finds users with approval:soft:view permission
  7. Creates approval_instance for each (tier_number = 1, approval_tier_id = NULL)
  8. Transaction status -> "In Approval"
  9. Users see item in "Issues Review" tab
  10. User reviews the processing issue and approves/rejects/queries
  11. On approval -> Transaction status: "Approved"

Key Differences from Regular Approval:

  • No approval_configuration record exists
  • Triggered by processing issues, not configuration
  • Single tier only (no multi-tier workflow)
  • Approvers determined by permission, not tier assignment

Business Rules

Hierarchical Enable Logic

Approvals must be enabled at three levels:

1. Project Level: projects.approvals_enabled = true?
   +-- NO -> No approvals for anything in this project
   +-- YES -> Continue

2. Type Level: approval_configuration (project, type, NULL) exists and is_active?
   +-- NO -> No approvals for this type
   +-- YES -> Continue

3. Subtype Level: approval_configuration (project, type, subtype) exists?
   +-- NO -> No approval needed for this subtype
   +-- YES -> Apply tiers from this configuration

Tier Evaluation Rules

  • Tiers evaluated in order (1, 2, 3...)
  • If conditions = NULL/empty -> Tier ALWAYS requires approval
  • If conditions not met -> Tier auto-skipped
  • Multiple conditions with logic: "ANY" -> OR (any triggers tier)
  • Multiple conditions with logic: "ALL" -> AND (all must match)

Approver Rules

  • Approvers are entities with user_id IS NOT NULL
  • Must have active project_relationship on the project
  • ONE approval per tier is sufficient
  • Other approvers marked as skipped when one approves

Sensitive-data clearance = "can the approver see the record"

Wherever this document says an approver "holds the required clearance" for a sensitive record, the check is: can that user see the record? — evaluated by the record_is_visible_to_user DB function, which applies the exact per-rule-scope semantics of the RLS predicate row_is_visible_to_caller: an organisation-level sensitive rule is satisfied by the user's organisation-scope clearance, a project-level rule by their project-scope clearance, each rule independently. Non-sensitive records (empty effective required permissions) skip the check entirely; entity-only approvers (no linked user) never clear a sensitive record.

This single definition backs the tier-walk filter (filterApproversByClearance), the soft-pool filter, tier re-evaluation, the decision-time gate (approverHasClearance), and the reconcile's hasEligibleApproverForRecord. Do NOT re-introduce the older approach of flattening every rule's required_permissions into one union and comparing it against project-scope clearance only — that wrongly excluded approvers whose clearance comes from an organisation-scope grant (e.g. organisation:owner holding sensitive_data:organisation:view) even though RLS showed them the record.

Self-Approval Prevention

An approver can never approve a record they are a party to — this is automatic and requires no tier condition.

  • At submission, the submitting service supplies ApprovalSubmissionContext.excludedApproverEntityIds: every entity that is a party to the record. For a transaction (getTransactionPartyEntityIds in utils/transaction.ts) this is entity_id, customer_entity_id, and the reimbursement_relationship / expense_relationship entity — so it covers supplier/customer invoices AND the person a reimbursement/expense is for (whose entity lives on the relationship, not entity_id).
  • submitForApproval drops any tier approver whose entity_id is in that set (filterOutExcludedApprovers) and records them as a Skipped instance with skipped_reason = 'Skipped — this approver is named on the transaction and cannot approve their own record'. This runs BEFORE the sensitivity clearance filter.
  • If a tier's only approver is a party, the whole tier is skipped and routing auto-advances to the next tier (mirrors the clearance-empty-tier behaviour). If NO tier can route, the request falls through to soft approval — where the party is likewise excluded from the soft-approver pool.
  • Because exclusion is applied to the right approvers at submission, a manual entity-condition rule is NOT needed to prevent self-approval. (The entity condition still exists for other routing purposes.)

Higher Tier Access

  • Tier N approvers can see ALL items from Tier 1 to N
  • Early approval by higher tier skips all lower tiers

Instances are created up-front; tiers are re-evaluated on each approval

All tiers' instances are created at submission (so a higher-tier approver can approve early — see above). Because the record can change while an approval is in flight, each approval re-evaluates the tiers above the approver against the record's current state instead of trusting the frozen submission-time decision (reevaluateUpcomingTiers in approval/base.ts):

  • The record's current inputs are resolved via the buildEvaluationInputs processor (registered per record type; for transactions getTransactionApprovalEvaluationInputs rebuilds conditionData + party set from the live transaction). Sensitivity is re-read via getRecordRequiredPermissions.
  • For each upcoming tier, eligibility = condition met AND ≥1 approver who is not a party AND holds any required clearance. Newly-eligible tiers that were Skipped are revived to Pending; newly-ineligible Pending instances are Skipped. Tiers with an already-decided/queried instance are left untouched.
  • Example this fixes: tier 2 was skipped at submission because the record was sensitive and its approver lacked clearance; the tier-1 approver removes the sensitive flag and approves — tier 2 is revived and becomes active instead of the request auto-approving past it.

Eligibility gate (decision time)

submitApprovalDecision re-checks the acting instance before recording an approval (assertInstanceEligibleToApprove): the tier's condition must currently hold, the approver must not be a party, and they must hold any required clearance — otherwise the decision is rejected. This stops a later-tier approver from approving a tier whose conditions aren't met, while still allowing the intentional "higher tier approves early" flow for eligible tiers. (Reject/query are not gated.)

The clearance check applies to soft (null-tier) instances too, so an uncleared instance-holder can never approve — this is the second layer that backs the unroutable-sensitive flow below.

Unroutable sensitive records (chain cannot clear the record)

A sensitive record whose proper approval channel cannot clear it does not silently auto-approve, and raises the sensitive_no_eligible_approver issue (unroutableSensitive: true on the submission result) in any of these cases:

  • Clearance-caused fallback — a config's tiers matched but the CLEARANCE filter emptied at least one of them (causedByClearance on the sensitivity fallback), so the record reaches the soft pool because of sensitivity. The issue is raised even when cleared soft reviewers exist — the soft pool is a stop-gap, not the configured chain; the issue is the signal to the soft approver (usually the owner) to add a clearance-holding approver to the chain or remove the sensitive flag. The soft request is created from the cleared subset when one exists, else from the full self-approval-filtered pool.
  • No cleared soft reviewer (no config) — the record entered the soft path directly (no formal config) and not even the soft pool holds a cleared reviewer: the request is created from the full self-approval-filtered pool so the failure stays visible.
  • Empty soft pool — there are no soft approvers at all: no request can be created without reviewers, so the record's status is left unchanged and the result carries submitted: false.

Routing soft purely for non-sensitivity reasons — no config with pending issues, tier conditions unmet, or tiers emptied by self-approval prevention alone — never raises the issue.

In both cases submitTransactionForApproval raises a sensitive_no_eligible_approver processing issue (under a service-role context, since processing_issues has no authenticated INSERT grant; the issue's first-class project_id column scopes the reconcile sweep). This gives two layers that together prevent approval while no eligible approver exists:

  1. Issue gate (0b) — the issue is non-ignorable (can_ignore = false on its processing_issue_templates row), so it is included in getTransactionNonIgnorableIssueKeys and blocks the approve decision until resolved.
  2. Live clearance assert (0c) — even without the issue, assertInstanceEligibleToApprove re-checks clearance live at decision time, so an uncleared soft instance-holder can never approve. The moment they are granted clearance, they can.

Reconcile and re-route. reconcileUnroutableSensitiveApprovals(projectId, actingUserId) (approval/server.ts) runs TWO sweeps under one service-role client (the acting user may lack RLS access to other users' issues/requests; the actor is recorded via explicit params, never auth.uid()). Each record/request is processed in its own try/catch so one failure never aborts the reconcile.

Sweep 1 — issue-driven. It sweeps every pending issue whose key is in APPROVAL_RECONCILE_ISSUE_KEYS (constants/approval.ts) — the set of keys whose resolution condition is "an eligible approver now exists", currently just sensitive_no_eligible_approver. The sweep is record-type-agnostic: it derives the record type per issue row (issue.processable_type) and dispatches to that type's resubmitForApproval processor via the processor registry, so any record type with a registered processor is handled (only transactions today; the missing-processor branch logs and continues). For each record it re-checks routing eligibility via hasEligibleApproverForRecord — the TIER verdict is authoritative when a config applies (a cleared soft reviewer does NOT resolve the issue; the record must become clearable by its own chain), the soft pool counts only when no config exists, and it short-circuits to eligible when the record is no longer sensitive or no tier's conditions apply. When a record is now routable it:

  1. cancels the open (Pending/Queried) SOFT request via cancelApprovalRequest — a no-op for the case that never created one; a FORMAL request is never cancelled (it means a concurrent sweep already re-routed the record),
  2. resolves the blocking issue (resolveProcessingIssue, resolver = the acting user) — so the audit trail reads naturally: issue resolved alongside the cancellation, before the re-submission, and
  3. re-submits the record via the resubmitForApproval processor so it re-routes through the proper chain (normal tiers if sensitivity was lifted; the tier chain or cleared soft pool if an approver was granted clearance).

Self-healing is preserved by a re-raise: if the resubmit fails (or no processor is registered for the record type) after the issue was resolved, the SAME issue is re-inserted from the fetched row, returning the record to the reconcile pool for the next trigger event to retry. A resolve failure skips the resubmit entirely, leaving the pending issue to drive the retry.

Sweep 2 — stale clearance-skipped requests. A record can be sensitive under multiple rules (e.g. a project-scope rule on its supplier project_relationship AND an organisation-scope rule on its supplier entity), lifted one rule at a time (the "Mark Not sensitive" button lifts them in one batch call, but Manage Access lifts a single rule). An early lift can resolve the sensitive_no_eligible_approver issue while the record is still (partially) sensitive — because an org-clearance approver can now see it, hasEligibleApproverForRecord returns true — and re-route the record to a formal request whose lower tier is stamped Skipped with a clearance reason (that tier's approver lacks the still-required clearance). The next lift then finds no pending issue, so Sweep 1 is a no-op, and the formal request keeps that lower tier permanently mis-routed — reevaluateUpcomingTiers only re-checks tiers ABOVE an acting approver at decision time, never a tier below the active one. Sweep 2 closes that gap. The two sweeps have independent data sources: a failed Sweep-1 issues fetch does not abort Sweep 2 (Sweep 1 just processes nothing; the returned envelope still carries the fetch error). Sweep 2 calls getOpenClearanceSkippedRequests(projectId) (approval/base.ts) — open (Pending), non-soft approval_requests in the project that carry ≥1 instance matching isClearanceSkippedInstance (utils/approval.ts): status = Skipped with skipped_reason in CLEARANCE_SKIP_REASONS (CLEARANCE_SKIPPED_REASON, CLEARANCE_LOST_SKIPPED_REASON, CLEARANCE_LOST_NO_USER_SKIPPED_REASON, all in constants/approval.ts — note these strings are also user-visible timeline copy; changing them without migrating stamped rows makes old rows invisible to the sweep). For each candidate, guarded:

  • Deduped against Sweep 1 — a record already handled above (whatever the outcome; a failed one was re-raised for the next reconcile) is skipped, never double-touched.
  • Never-engaged only — skipped if ANY instance is Approved/Rejected/Queried; someone interacted with the chain and re-routing would destroy in-flight work. This is a deliberate residual: a stale clearance-skipped tier sitting below an engaged tier stays skipped — the request can still complete via its active tier, and unwinding decided/queried work would be worse than the gap.
  • No-churn change guard — clearanceRoutingWouldChange(request, instances) (approval/base.ts) returns true only when the routing outcome would actually differ: the record is no longer sensitive at all (empty effective required permissions), OR a clearance-skipped tier would now route — each skipped tier is checked against its CURRENT active config roster (getApprovalTierDetails, falling back to the tier's COMPLETE frozen instance group — not just its clearance-skipped rows — when the tier was deactivated/removed since submission), so both remediations work: lifting a rule that hid the record from an existing approver AND adding a clearance-holding approver to the skipped tier. Non-party (via buildEvaluationInputs), linked-user approvers only; visibility via record_is_visible_to_user. Unchanged → the request is left in place (logged), so repeated reconciles never spam approvers with identical cancel/resubmit cycles.
  • Resubmit-processor check — the record type's resubmitForApproval processor is resolved BEFORE cancelling; a type without one is skipped WITHOUT cancelling (cancelling first would orphan the record: request gone, nothing recreated, no issue to re-raise).
  • Pre-cancel re-check — the request's row + instances are re-fetched fresh immediately before the cancel and both guards re-applied (still Pending, still never-engaged), shrinking the snapshot→cancel race to a few milliseconds. The cancel itself is unconditional, so that residual TOCTOU window is accepted pending an atomic conditional-cancel RPC.

getOpenClearanceSkippedRequests paginates every fetch to completion and chunks the bulk in filter (CLEARANCE_SWEEP_PAGE_SIZE ≤ PostgREST max_rows, CLEARANCE_SWEEP_ID_CHUNK_SIZE for URL length) — the engaged guard is only safe on a provably complete instance set; a silently truncated response that dropped a decided/queried row would let the sweep cancel an engaged request.

When all guards pass it cancels the mis-routed formal request (reason: "the required sensitive-data clearance changed") and re-submits via the same resubmitForApproval processor. Unlike Sweep 1 there is NO issue row to re-raise on a transient failure — the record would be left In Approval with its old request cancelled and no replacement — so a resubmit failure is logged CRITICAL (the record needs manual re-submission).

Sweep 2 is trigger-agnostic by design: any reconcile trigger (a tier-config save, a permission grant, a sensitivity lift) may re-route stale requests other than the one the acting admin had in mind. That is the invariant — clearance-skipped tiers revive whenever they become routable — not a bug.

The re-submit (both sweeps) runs through resubmitTransactionForApproval (registered on the processor registry so approval/server never imports transaction/server directly). It no-ops on terminal (Cancelled/Rejected) or already-Approved records, and delegates to the idempotent submitTransactionForApproval, so a raced reconcile is harmless — an existing Pending/Queried request short-circuits and issue dedup prevents a duplicate.

Reconcile triggers (all best-effort, non-fatal, logged; a missing acting user is a no-op):

  • Permission grants on the project — createProjectRelationshipWithPermissions, grantAccessToExistingRelationship, and updateRelationshipPermissions in project/server.ts (a grant may newly confer the sensitive-data clearance a record needs).
  • Approval tier-config saves — upsertApprovalTiers in approval/server.ts (a config change may add a cleared tier approver).
  • Sensitivity lifted at any cascade level — unmarkRecordSensitive in sensitive/server.ts, which maps the unmarked record to the affected project(s): a transaction → its project; a project_relationship → its project_id; an entity → the projects where the entity participates, pre-filtered to those actually carrying either a pending issue of this key (Sweep 1) or an open clearance-skipped formal request (Sweep 2, via getOpenClearanceSkippedRequests) — a per-rule lift resolves the issue while the record is still sensitive, so the follow-up lift's project has no pending issue but does have a stale request. Because eligibility is recomputed from the record's effective required permissions (getRecordRequiredPermissions, cascade-aware), lifting sensitivity at any ancestor level counts.

activated_at and timeline dates

Because all instances are created at submission, created_at is the submission time for every tier. approval_instances.activated_at records when a tier actually becomes the reached/active tier: set at submission for the first routable tier, and on each approval (as decision_at + 1s, so the activation sorts strictly after the approval that triggered it) as the request advances or a tier is revived. Skipped/not-yet-reached instances stay NULL. v_approval_timeline's assigned event dates from COALESCE(activated_at, created_at), so each tier's assignment shows when it was reached, not the submission moment.

Submitter Rules

  • For transactions: created_by_user_id if not internal user
  • If internal user: use submission_address field
  • The submitter (who sent the record) CAN be an approver. This is distinct from a party (supplier/customer/reimbursement person) — a party is always excluded (see "Self-Approval Prevention" above).

Query/Discussion

  • Uses messages table with approval_request_id
  • Only submitter and assigned approvers can participate
  • Query pauses approval at current tier until resolved
  • Sends are atomic and stale-guarded. Every query/reply (UI mutation, Sana inbound, email action) goes through the send_approval_message_atomic RPC (SECURITY INVOKER — see docs/architecture/database/VIEWS_AND_FUNCTIONS.md): the request row is locked, validated to still be the record's current writable request in the required state (query: Pending → Queried; reply: Queried → Pending), and the message insert + status transition commit together or not at all. A stale id (the request was cancelled and the record re-submitted, e.g. after a document replacement) returns a typed code — APPROVAL_REQUEST_SUPERSEDED with the successor's id — as data, never a thrown Error (production Flight masks Error instances crossing Server Actions). The client (useSendApprovalQueryMutation / useSendApprovalReplyMutation) runs with retry: 0 (sends are non-idempotent), and on a stale code invalidates the record's approval queries and asks the user to act again against the refreshed state — never auto-replays the message. Post-commit side-effects (notifications, record-status sync) are logged on failure but do not fail the send: the message is already durably committed.
  • One response per query generation. An emailed query reaches several addresses, so both response paths are bound to the query "generation" — the root query message the authorisation was minted against. A reply passes it as send_approval_message_atomic's trailing p_expected_root_query_message_id; the document-replacement path (which resolves a query by replacing a document, not by sending a message) calls claim_approval_query_response with the same id. Both perform the Queried → Pending transition under the same row lock, so a reply and an upload are mutually exclusive — whichever lands first claims the query, and the loser gets APPROVAL_REQUEST_INVALID_STATE. An authorisation pinned to a superseded generation gets STALE_QUERY_GENERATION and writes nothing, so a dormant or in-flight reply link can never answer a LATER query. Passing NULL opts out of the guard, which is why the in-app reply (always acting on the live thread) is unaffected.
  • Replies from unmatched addresses are attributed, not misfiled. An inbound email reply whose address matches no platform user is recorded under the reserved external-respondent@farmcove.internal internal account (never the record submitter), with the real address on messages.metadata.external_responder_email. v_approval_messages and v_approval_timeline expose it as the derived external_responder_email column, populated only for messages sent by that reserved account.

Service-level query email fan-out

sendApprovalQuery (services/approval/server.ts) is the single convergence point — the in-app send and the email approval-review action both reach it — and it emails one notification per address, each with its own authorisation. A shared token would let anyone on the CC line answer, replace documents, and be recorded as the record submitter.

  1. Recipient resolution (only when sendViaEmail). The To address is the UI override, else the submitter's own address; the CC is treated as one address. Each distinct address is resolved by resolveApprovalQueryRecipients (utils/approval.ts) — a pure function fed pre-fetched reads: the submitter's own address binds to the submitter; an address matching exactly one project user binds to that user; anything else (no match, or an address several users share) binds to External Respondent. Addresses dedupe case-insensitively, so a CC repeating the To folds into one send. The reads (submitter row, getProjectUserByEmail per address, the reserved account) run under a narrow service-role elevation — mirroring getNotificationRecipientReads — because the acting approver can see neither another user's users row nor the project's user list under their own RLS. Scope comes from the COMMITTED request's project_id, never caller params. A read failure drops that address from the send list and is logged; it is never downgraded to "external".

  2. Revocation before minting. Once the query message commits and before any token is minted, revokeQueryResponseTokensForRequest expires every unused, unexpired query_response token whose metadata.approval_request_id matches. This is fail-closed: if the revocation cannot be proven to have run, the round sends no email at all (WhatsApp/in-app still go out), because two answerable generations must never coexist. Tokens minted before the top-level key existed are not matched and age out naturally.

  3. The sends. When the To binds to the submitter (the common case) there is ONE sendNotification carrying every selected channel and the submitter-bound token — byte-identical to the pre-fan-out behaviour. When it binds elsewhere, the submitter's send keeps only WhatsApp (and is skipped entirely for an internal submitter whose external address is an email, which sendNotification would otherwise convert back into a duplicate email), and every email address gets its own channels: [EMAIL] send. A known project user is addressed via emailTo with recipientUserId set to that user; an external recipient rides the existing internal-recipient external-address override (recipientUserId = the reserved account, submittedByAddress = the address). Each send is isolated in its own try/catch and the round ends with one aggregate summary log — token URLs and plaintext tokens are never logged.

  4. Token metadata. Every minted query_response token carries root_query_message_id (the generation it may answer), approval_request_id at the top level (what revocation filters on), and — for reserved-account bindings — recipient_email, the exact address emailed.

Responding

handleQueryResponseActionFromToken derives the whole authorisation from the token row: the generation, and whether the token is bound to the reserved account. A reply threads the generation into send_approval_message_atomic and, for a reserved-account binding, stamps external_responder_email onto the committed message — the only attribution such a reply carries. The record-creator notification is suppressed when that address IS the submitter's own email-type submitted_by_address (phone-type addresses are never compared), so a submitter is never notified of their own reply.

The upload path derives the transaction, attachment, project and organisation server-side from the request's record and ignores the browser's copies (mismatches are logged), then routes through the consolidated replacement core below. A token carrying no generation does NOT skip the claim: the core derives the current generation server-side and fails closed if none exists (the pre-generation tokens all expired at the hardening cutover, so the old skip branch was removed as dead).

Queried document replacement (consolidated core)

Every flow that replaces a queried record's document — the approval-view re-upload, the issue-resolution and source-document-card re-uploads, the Sana upload reply, the email-token upload, and the duplicate-card "Use as replacement" — drives ONE server function: executeQueriedDocumentReplacement (services/approval/server.ts). Its sequence, and why the order is load-bearing:

  1. Resolve the processor for the record type from the approval processor registry (replaceDocument) — before anything is consumed, so an unsupported type refuses without touching the query.
  2. Resolve the generation: the caller-supplied root_query_message_id, else derived server-side as the latest root query (created_at DESC, id DESC). Unresolvable ⇒ typed refusal, zero writes.
  3. Claim via claim_approval_query_response (CAS Queried→Pending under the request lock). This is what makes an upload mutually exclusive with a concurrent reply on every route — previously only the email route claimed.
  4. Replace through the record's processor (attachment CAS is the point of no return; full reprocessing enqueued with a bounded in-request retry, a final failure reported as the REPROCESS_NOT_QUEUED partial, never a rollback).
  5. On failure before the attachment CAS, restore the EXACT claimed generation (restore_claimed_approval_query — ABA-safe). On success, cancel the claimed request server-side (failure = REQUEST_CANCEL_FAILED partial, never a rollback). Cancellation is the standard post-replacement state on all routes; the timeline shows it explicitly, and reprocessing re-submits for approval.

Refusals are typed data (ok:false = zero writes); partials ride ok:true and surface as warning toasts. The transaction processor's payload accepts either uploaded bytes or sourceAttachmentId (attachment-row reuse — a new row copying the source's descriptive fields and storage pointer, no storage copy).

Duplicate "Use as replacement" (applyDuplicateDocumentAsQueryReplacement)

The duplicate-detection card's action composes the core with a void of the duplicate, staged so every step is either refused cleanly, compensated, or resumable:

  • Stage 0 (caller RLS, zero writes): the issue must be current + pending + the potential-duplicate key and belong to the duplicate; the match must carry the high label AND a finite numeric score ≥ HIGH_CONFIDENCE_THRESHOLD (a bare < 85 fails open on undefined — never reintroduce it); same project, distinct ids; dedicated permission transaction:duplicate:replace (seeded to the reject-holding roles + compliance reviewer); the original's current request must be Queried. A duplicate already cancelled WITHOUT this operation's marker refuses ALREADY_ACTIONED — a fresh fence would CAS against the observed Cancelled status and overwrite a foreign void's reason.
  • Stage 1 (fence): one exact-status CAS cancels the duplicate writing cancel_reason marker Used as replacement for transaction <code> [dup-replace:<issueId>:<matchedId>] AND duplicate_of_transaction_id together. That PAIR is fence ownership — only this CAS writes both atomically; the plain void action refuses terminal rows precisely so a crafted reason can never be stamped onto a cancelled row.
  • Stage 2: the core (claim → replace with sourceAttachmentId → cancel). A refusal compensates the fence: a marker-matched un-void restoring EVERY captured prior value (status, cancel_reason, duplicate link, updated_by_user_id), re-queuing processing if the duplicate had (or gained) live jobs. A FAILED compensation reports REPLACEMENT_IN_FLIGHT — the leftover fenced state is exactly what a retry resumes.
  • Stage 3: the duplicate's void cascades (approval cancel, reconciliations, job settlement, issue resolve + sibling ignore) — idempotent, individually tallied partials, never rolled back.
  • Resume: a retry that finds its own marker+link routes here BEFORE any fence call. Request still Queried ⇒ continue from stage 2 (claiming the request open NOW). Swap already committed — detected by storage identity (hash, else bucket-normalised path; NEVER attachment-id equality, the swap copies rows) ⇒ stage 3 only. Request Pending without the swap ⇒ REPLACEMENT_IN_FLIGHT (indistinguishable from a concurrent reply; manual recovery).

Accepted residuals (signed off at review): a crash between claim and compensate leaves the request Pending until manually re-queried (same window as the pre-existing email flow); a reprocess-enqueue failure after retries falls back to the manual reprocess action; a job created AND driven terminal entirely inside the fence window escapes both requeue signals (seconds-wide; manual reprocess recovers).

Rejection

  • Any approver can reject at any tier
  • Rejection ends the approval process
  • All remaining instances marked as skipped
  • reject_reason stored on both approval_request and the record

Permissions

Permission Key Description Scope
approval_rule:view View approval configurations project
approval_rule:create Create approval configurations project
approval_rule:edit Edit approval configurations project
approval_rule:delete Delete approval configurations project
approval:view View approval requests project
approval:approve Approve/reject/query records project
approval:soft:view View and action records with processing issues when no formal approval config project

Note: The approval:soft:view permission is automatically granted to users with the project:owner role, ensuring at least one approver exists for soft approvals.

Database Schema

See database/APPROVAL_SYSTEM.md for complete table definitions.

Tables

Table Purpose
approval_configurations Enable approvals for type/subtype per project
approval_tiers Define approval levels with conditions
approval_tier_approvers Assign approvers to tiers
approval_requests Track records through approval workflow
approval_instances Track each approver's decision (audit trail)

Enum

  • approval_status: Pending, Approved, Rejected, Skipped, Queried

Changes to Existing Tables

  • projects: Added approvals_enabled column
  • transactions: Added reject_reason column
  • messages: Added approval_request_id column

Views Reference

Configuration Views

View Purpose Filter By
v_project_approval_types Types enabled per project (toggle switches) project_id
v_project_approval_subtypes Subtypes with config status project_id, type_code
v_approval_configuration_summary Config overview with tier/approver counts project_id
v_approval_tier_details Detailed tier view with approvers approval_configuration_id

Request/Instance Views

View Purpose Filter By
v_user_pending_approvals User's pending approval items user_id, entity_id
v_approval_request_status Request progress with tier statistics record_type, record_id
v_approval_request_timeline Full audit trail for a request approval_request_id
v_approval_messages Discussion thread messages approval_request_id
v_soft_approval_approvers Users who can approve soft approvals project_id

Note: Views now include an is_soft_approval flag to distinguish soft approvals from regular approvals.

v_user_pending_approvals's approver/representative-tier selection is frozen-instance-driven, never live-config-driven. The formal branch's representative row comes from the request's OWN approval_instances (a DISTINCT ON (approval_request_id, user_id) CTE: Pending/Queried instances at or above current_tier_number, current tier preferred → assignment='mine', else lowest future tier → 'lower_tier', deterministic created_at, id tiebreak; max_tier_number reports the instance's frozen tier). This applies to how the user's representative instance/tier is chosen; the total_tiers column is a separate display-only count that still reflects the LIVE configuration (count(*) of active approval_tiers for the request's configuration), not the frozen instances. An earlier definition derived the user's tier from the LIVE config (approval_tier_approvers → max(tier_number)) and joined instances at that live tier — editing a configuration's tier structure while requests were in flight (tier inserted, approvers renumbered) made the join miss, silently hiding those requests from the approver's inbox AND the approve-button gating query (both read this view; one production incident hid 36 of an approver's 40 pending items). Never reintroduce a live-config tier JOIN for instance matching here — the write path (submitApprovalDecision) is frozen-instance-keyed, and the read path must agree with it.

View Usage Examples

Get pending approvals for current user:

SELECT * FROM v_user_pending_approvals
WHERE user_id = auth.uid()
AND can_approve = true;

Get approval status for a transaction:

SELECT * FROM v_approval_request_status
WHERE record_type = 'transactions'
AND record_id = 'uuid-of-transaction';

Get full timeline for an approval request:

SELECT * FROM v_approval_request_timeline
WHERE approval_request_id = 'uuid-of-request'
ORDER BY event_at ASC;

Timeline ordering invariant — order tier events by TIER NUMBER, not by timestamp. Two facts make timestamp-based ordering fail: (1) every approval instance is created at submission time, so every assigned event for a request shares the same created_at down to the millisecond; and (2) tiers are NOT necessarily decided in tier order — a low tier can be approved AFTER a higher tier was condition-skipped at submission time, so the decision timestamps run backwards relative to the tier numbers. Sorting the timeline by event_at (even with tier number and phase as tiebreakers) therefore renders out of order: a later-decided low tier drops to the bottom, and Submitted interleaves among the tiers that were skipped at submission time.

The ApprovalTimeline component (packages/app/src/components/approvals/ApprovalDetailsContent/components/ApprovalTimeline) owns the deterministic ordering. It groups per tier and computes a single numeric sort key:

  • Tier events → tier_number + phase_offset (assigned 0.1 before decision 0.2). This orders strictly by tier number, independent of the collided / out-of-order timestamps. Every tier's assignment renders immediately before its own decision.
  • Submitted → 0, always first.
  • Query / reply (request-level, mid-flow) → slotted into the band of the tier that was pending at the event's real timestamp: the lowest tier whose decision is missing or happened strictly after the event. The query renders INSIDE that tier's band (pending_tier + 0.15), i.e. after the tier's "Assigned" row and before its decision — a query almost always arrives after its tier is assigned but before it decides. Comparing only against decision times (assignments all collide at submission and always precede a later query) makes this correct even when tiers are decided out of order: a query at 11:20 with tier 1 approved at 11:23 and tier 2 skipped at 11:18 lands in tier 1's pending band, not misplaced after tier 1.

Do NOT re-solve ordering by adding a secondary ORDER BY on the raw view, by comparing decision event types, or by anchoring the "assigned" event to its tier's decision timestamp (a previous fix did this and broke when tiers were decided out of tier order). Order by tier number.

Two supporting facts: (1) getApprovalTimeline (services/approval/base.ts) orders rows by event_at ASC only, so rows sharing a timestamp arrive in undefined DB order — never depend on their relative order. (2) A single tier can hold BOTH an approve/reject row AND same-tier skipped rows in the same request: approving/ rejecting auto-skips the tier's other pending approvers (skipped_reason = 'Another approver approved/rejected this tier'). Those same-tier skips are hidden in the timeline (only full-tier skips show); the tiersWithApprovalDecision set drives that filtering.

Category Seed Data

The approval system uses the category system for type/subtype definitions:

Category Group: approval_types

Code Name Parent Metadata
transactions Transactions NULL {"table_name": "transactions"}
Expense Expense transactions is_media=T, is_accounting=T, is_personal_accounting=T
Invoice Invoice transactions is_media=T, is_accounting=T, is_personal_accounting=T
Payroll Invoice Payroll Invoice transactions is_media=T, is_accounting=F, is_personal_accounting=F
Reimbursement Reimbursement transactions is_media=T, is_accounting=F, is_personal_accounting=F
Unknown Unknown transactions is_media=T, is_accounting=T, is_personal_accounting=T

User Interface

The approval system provides three main pages for managing approvals, located under Management > Approvals.

Page Icon Purpose
Pending CircleDashed Items awaiting approval
Queries CircleHelp Discussion threads
History History Completed approval actions

Pending Page

Displays approval items assigned to the user.

Features:

  • Tabs: Grouped by approval type (uses tab_label from view, pluralized via pluralize_type())
  • Badge counts: Number of pending items per tab
  • Assignment column: "Mine" or "Lower Tier" badges for filtering
  • Default filter: Shows only user's directly assigned items ("Mine")
  • Actions: ApprovalButtons component (Approve/Reject/Query)

Data source: v_user_pending_approvals

Queries Page

Displays approval queries for discussion.

Features:

  • Tabs: Grouped by approval type
  • Can Reply column: Indicates if user can respond to the query
  • Thread view: Expandable discussion threads

Data source: v_user_approval_queries

History Page

Displays user's completed approval decisions.

Features:

  • Tabs: Grouped by approval type
  • Action column: Badge showing Approved/Rejected/Skipped status
  • Decision date and notes: Full audit trail of user's decisions

Data source: v_user_approval_history

ApprovalButtons Component

Reusable action buttons component located at src/components/approvals/ApprovalButtons/.

Features:

  • Uses ButtonGroup with dropdown for multiple actions
  • Priority order for main action: Approve > Query > Reject
  • Icons: CircleCheck (Approve), CircleHelp (Query), CircleX (Reject)
  • Supports loading states and size variants