Approval System Tables

The approval system provides configurable multi-tier approval workflows per project for different record types (transactions, contracts, documents, etc.). See APPROVAL_SYSTEM.md for comprehensive architecture documentation, flow examples, and business rules.

Approval Configurations (approval_configurations)

Purpose: Defines that a specific type/subtype combination requires approvals for a project

Use Case Example: Enable approval workflows for all "Invoice" transactions in "Summer Film 2024" project.

Column	Type	Description
id	UUID	Unique identifier
project_id	UUID	FK to projects
organisation_id	UUID	FK to organisations (for RLS, denormalized from project)
scope	scope_type	'project' (future: 'organisation')
type_category_id	UUID	FK to categories (parent type, e.g., 'transactions')
subtype_category_id	UUID	FK to categories (child subtype, e.g., 'Invoice') - nullable
is_active	BOOLEAN	Whether this configuration is active (default: true)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who created this configuration (default: auth.uid())
updated_by_user_id	UUID	User who last updated this configuration (default: auth.uid())

Constraints:

• UNIQUE (project_id, type_category_id, subtype_category_id)
• subtype_category_id.parent_id must equal type_category_id (validated by trigger)

Indexes:

• idx_approval_configurations_project_id on project_id
• idx_approval_configurations_organisation_id on organisation_id
• idx_approval_configurations_type_category_id on type_category_id
• idx_approval_configurations_subtype_category_id on subtype_category_id

Approval Tiers (approval_tiers)

Purpose: Defines sequential approval levels within a configuration with optional conditions

Use Case Example: Create Tier 1 (Manager approval for amounts > $100), Tier 2 (Finance Director for amounts > $1000), Tier 3 (CFO for amounts > $5000).

Column	Type	Description
id	UUID	Unique identifier
approval_configuration_id	UUID	FK to approval_configurations
tier_number	INTEGER	Tier order (1, 2, 3...) - evaluated sequentially
name	TEXT	Optional display name for the tier
conditions	JSONB	Conditional rules for when this tier applies (see below)
is_active	BOOLEAN	Whether this tier is active (default: true)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who created this tier (default: auth.uid())
updated_by_user_id	UUID	User who last updated this tier (default: auth.uid())

Conditions JSONB Structure:

{
  "logic": "ANY",
  "rules": [
    {
      "field": "amount",
      "operator": "gt",
      "value": "1000"
    },
    {
      "field": "entity_name",
      "operator": "in",
      "value_list": ["Test LTD", "Acme Corp"]
    }
  ]
}

• logic: "ANY" (OR) or "ALL" (AND) - how multiple rules combine
• field: Field to evaluate ("amount", "entity_name")
• operator: "gt" | "gte" | "lt" | "lte" | "eq" | "neq" | "in" | "not_in"
• value: Single value for comparison operators
• value_list: Array for "in" / "not_in" operators
• If conditions is NULL or empty, tier ALWAYS requires approval

Constraints:

• UNIQUE (approval_configuration_id, tier_number)

Indexes:

• idx_approval_tiers_approval_configuration_id on approval_configuration_id

Approval Tier Approvers (approval_tier_approvers)

Purpose: Links entities (approvers) to approval tiers

Use Case Example: Assign John Smith and Jane Doe as Tier 1 approvers, Finance Director as Tier 2 approver.

Column	Type	Description
id	UUID	Unique identifier
approval_tier_id	UUID	FK to approval_tiers
entity_id	UUID	FK to entities (must have user_id for notifications)
is_active	BOOLEAN	Whether this approver assignment is active (default: true)
created_at	TIMESTAMPTZ	Creation timestamp
created_by_user_id	UUID	User who created this assignment (default: auth.uid())

Validation (application layer):

• Entity must have user_id IS NOT NULL
• Entity must have active project_relationship for the project

Constraints:

• UNIQUE (approval_tier_id, entity_id)

Indexes:

• idx_approval_tier_approvers_approval_tier_id on approval_tier_id
• idx_approval_tier_approvers_entity_id on entity_id

Approval Requests (approval_requests)

Purpose: Tracks a record submitted for approval (one per record per configuration, or soft approvals for records with issues but no config)

Use Case Example: When an Invoice transaction is created and needs approval, an approval_request is created to track it through the workflow. For soft approvals (records with processing issues but no formal approval config), approval_configuration_id is NULL.

Column	Type	Description
id	UUID	Unique identifier
approval_configuration_id	UUID (nullable)	FK to approval_configurations. NULL for soft approvals (records with issues but no formal config)
record_type	TEXT	Table name (e.g., 'transactions')
record_id	UUID	ID of the record being approved
project_id	UUID	FK to projects (denormalized for queries)
organisation_id	UUID	FK to organisations (denormalized for RLS)
record_created_by_user_id	UUID	User who created the original record (denormalized for RLS - allows record creators to reply to queries)
current_tier_number	INTEGER	Current tier being evaluated
status	approval_status	Pending, Approved, Rejected, Queried
submitted_by_user_id	UUID	User who submitted (NULL if system/internal)
submitted_by_address	TEXT	Email/phone if internal user submitted
notes	TEXT	Notes from submitter when submitting the record for approval
final_decision_at	TIMESTAMPTZ	When final decision was made
final_decision_by_user_id	UUID	User who made final decision
reject_reason	TEXT	Reason if rejected
reference	TEXT	Human-readable reference for the record (e.g., transaction_code). Populated at submission
escalated_at	TIMESTAMPTZ	When urgent escalation was triggered (NULL if not escalated)
escalated_by_user_id	UUID	FK to users - User who triggered the urgent escalation (NULL if not escalated)
is_soft_approval	BOOLEAN	True if this approval was created due to processing issues without formal approval configuration
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Constraints:

• UNIQUE (approval_configuration_id, record_type, record_id) WHERE status <> 'Skipped' AND approval_configuration_id IS NOT NULL - for regular approvals
• UNIQUE (record_type, record_id) WHERE status <> 'Skipped' AND approval_configuration_id IS NULL - for soft approvals

Indexes:

• idx_approval_requests_record on (record_type, record_id) - lookup by record
• idx_approval_requests_project_status on (project_id, status) - project dashboard
• idx_approval_requests_approval_configuration_id on approval_configuration_id
• idx_approval_requests_organisation_id on organisation_id
• idx_approval_requests_record_created_by_user_id on record_created_by_user_id - RLS for record creators
• idx_approval_requests_escalated_by on escalated_by_user_id - FK index for escalation tracking

Approval Instances (approval_instances)

Purpose: Individual approval assignments tracking each approver's decision (full audit trail)

Use Case Example: When Tier 1 has two approvers (John and Jane), two approval_instances are created. When John approves, his instance is marked 'Approved' and Jane's is 'Skipped'.

Column	Type	Description
id	UUID	Unique identifier
approval_request_id	UUID	FK to approval_requests
approval_tier_id	UUID (nullable)	FK to approval_tiers. NULL for soft approvals
tier_number	INTEGER	Denormalized for efficient queries
entity_id	UUID	FK to entities (the approver)
user_id	UUID	Denormalized for "my approvals" queries
status	approval_status	Pending, Approved, Rejected, Skipped, Cancelled, Queried
condition_met	BOOLEAN	Whether tier condition was met for this record
decision_at	TIMESTAMPTZ	When decision was made
decision_note	TEXT	Optional note with decision
skipped_reason	TEXT	Why auto-skipped (condition not met, another approver, etc.)
skipped_at	TIMESTAMPTZ	When instance was auto-skipped
cancel_reason	TEXT	Why cancelled (document replaced, duplicate transaction, etc.)
cancelled_at	TIMESTAMPTZ	When instance was cancelled due to user action
activated_at	TIMESTAMPTZ	When this instance's tier became the reached/active tier. Set at submission for the first routable tier and on each approval as tiers advance / are re-evaluated. NULL until reached; timeline falls back to created_at
created_at	TIMESTAMPTZ	Creation timestamp (submission time — ALL tiers' instances are created up-front)
updated_at	TIMESTAMPTZ	Last update timestamp

Constraints:

• UNIQUE (approval_request_id, approval_tier_id, entity_id)

Indexes:

• idx_approval_instances_user_status on (user_id, status) - "My pending approvals"
• idx_approval_instances_request on (approval_request_id, tier_number) - request timeline
• idx_approval_instances_approval_tier_id on approval_tier_id
• idx_approval_instances_entity_id on entity_id

Changes to Existing Tables

projects table:

• Added approvals_enabled (BOOLEAN, default: false) - Master switch to enable/disable approvals for the entire project

transactions table:

• Added reject_reason (TEXT) - Reason for rejection when status is 'Rejected' (from approval workflow)

messages table:

• Added approval_request_id (UUID, FK to approval_requests) - Links message to approval request for query/discussion threads
• Added index idx_messages_approval_request_id (partial, where approval_request_id IS NOT NULL)

Approval System Views

The following views support the approval system (see Views & Functions for full documentation):

• v_approval_types - Approval types and subtypes from category hierarchy (types are level=1, subtypes are level=2)
• v_approval_configuration_summary - Configuration overview with tier/approver counts. Includes type-level configs where subtype_category_id IS NULL
• v_approval_tier_details - Detailed tier view with approvers (configuration editor). Each approver's entity_name is computed via format_entity_display_name(entity_id) so it matches the app's display-name logic (full COALESCE(preferred_name, first_name) || ' ' || last_name), rather than a name that drops the surname when a preferred name is set
• v_user_pending_approvals - User's pending approval items with type/subtype info for tabs. Includes tab_label, total_tiers, can_approve, assignment ('mine' | 'lower_tier'), is_soft_approval flag, transaction-specific fields (transaction_reference_number, transaction_date, transaction_due_date, transaction_total, transaction_currency_code, transaction_ai_justification, transaction_billing_entity_name, transaction_type, transaction_direction, transaction_subtotal, transaction_tax_total, transaction_submission_note), and record_summary (generic searchable text per record type for "All" tab filtering) via LEFT JOIN to transactions table when record_type='transactions'. Transaction-backed rows whose transaction has reached a terminal Cancelled / Rejected status are excluded in both UNION branches — a defensive backstop so a voided transaction never lingers in Pending Approvals even if its approval request was not cancelled. The pending_issues_count column is computed per row via a LATERAL call to the SECURITY DEFINER count_pending_processing_issues(record_type, record_id) helper (the old pending_issues CTE aggregated v_pending_processing_issues across the whole DB under processing_issues per-row RLS — a ~4 s N+1; see VIEWS_AND_FUNCTIONS.md).
• v_user_approval_history - User's completed approval instances (Approved, Rejected, Skipped). Includes type/subtype for tabs and is_soft_approval flag
• v_user_approval_queries - Approval queries with user_id for filtering. Returns one row per (query, user who can reply). Excludes query poster. Includes is_soft_approval flag
• v_approval_request_status - Request progress summary with tier statistics. Includes is_soft_approval flag
• v_approval_timeline - Full audit trail for a record (filtered by record_type + record_id, spans all approval requests for that record). Combines four event sources via UNION ALL: (A) submitted from approval_requests, (B) instance events from approval_instances, (C) query_sent and (D) query_replied from messages (source = 'Approval Message', parent_id distinguishes original vs reply). For section B, every instance emits an assigned row at COALESCE(activated_at, created_at) — since all tiers' instances are created up-front at submission, created_at is the submission time for every tier, so the assigned event dates from activated_at (when the tier was actually reached) and only falls back to created_at for historical rows that predate the column; decided instances (Approved/Rejected/Skipped/Queried/Cancelled) additionally emit a decision row at COALESCE(decision_at, skipped_at, cancelled_at, created_at) — so a decided instance contributes two rows, while a Pending instance contributes one. The skipped_at term ensures condition-skipped instances (which have no decision_at/cancelled_at) resolve to their actual skip time; the created_at term is a final fallback guarding against any historical row that lacks every decision timestamp (otherwise a NULL event_at renders as a 1970 epoch date in the UI). event_type values: submitted, assigned, approved, rejected, skipped, queried, cancelled, query_sent, query_replied
• v_approval_messages - Discussion thread messages for a request (UI only). Filters by source='Approval Message' to exclude WhatsApp/Email delivery records. Includes parent_id, root_message_id, and origin_message_id for threading and tracing to source channel messages. sender_name is resolved from entity first, falling back to users table when entity_id is null (for approver queries)
• external_responder_email (both views) - Derived scalar carrying the address that actually answered an emailed query. An email reply from an address that matches no platform user is recorded under the reserved external-respondent@farmcove.internal internal account, with the real address on messages.metadata.external_responder_email. Both views expose it via a flat LEFT JOIN public.users pinned on BOTH id = m.sender_user_id AND lower(email) = 'external-respondent@farmcove.internal', so the metadata key can only surface for messages actually sent by that account — metadata carried by any other sender is ignored. In v_approval_timeline every UNION arm emits the column; only the query_sent / query_replied message arms can populate it, the submitted and instance arms emit NULL::text. The raw metadata is never exposed as its own column
• v_soft_approval_approvers - Users who can approve soft approvals based on approval:soft:view permission. Returns project_id, entity_id, user_id, organisation_id for each eligible user

For the approval permission catalogue see Permissions.