Approvals

approval_steps

approval_requests

approval_decisions

approval_delegations

Enums

approval_status

approval_decision

Row-Level Security

All five tables are scoped to the current user's organization via get_user_org_id(). Any org member can select, insert, and update rows. Only admins (checked via is_admin()) can delete chains, steps, requests, decisions, or delegations.

Dependencies

• identity -- organizations, users, get_user_org_id(), is_admin(), set_updated_at()

Example Queries

List pending approval requests assigned to the current user (via steps or delegation):

SELECT
  ar.id,
  ar.entity_type,
  ar.entity_id,
  ar.submitted_at,
  u.full_name AS requester_name
FROM approval_requests ar
JOIN approval_chains ac ON ac.id = ar.chain_id
JOIN approval_steps ast ON ast.chain_id = ac.id
LEFT JOIN users u ON u.id = ar.requester_id
WHERE ar.status = 'pending'
  AND ast.approver_id = '<current_user_id>'
ORDER BY ar.submitted_at ASC;

Full decision history for a specific request:

SELECT
  ad.decision,
  ad.comment,
  ad.decided_at,
  u.full_name AS decided_by_name,
  ast.position AS step_position
FROM approval_decisions ad
LEFT JOIN users u ON u.id = ad.decided_by
LEFT JOIN approval_steps ast ON ast.id = ad.step_id
WHERE ad.request_id = '<request_id>'
ORDER BY ad.decided_at ASC;

Active delegations for a user:

SELECT
  d.id,
  d.delegator_id,
  u_from.full_name AS delegator_name,
  u_to.full_name   AS delegate_name,
  ac.name           AS chain_name,
  d.starts_at,
  d.ends_at,
  d.reason
FROM approval_delegations d
LEFT JOIN users u_from ON u_from.id = d.delegator_id
LEFT JOIN users u_to   ON u_to.id   = d.delegate_id
LEFT JOIN approval_chains ac ON ac.id = d.chain_id
WHERE d.is_active = true
  AND d.starts_at <= now()
  AND (d.ends_at IS NULL OR d.ends_at > now())
ORDER BY d.starts_at DESC;

Approval chain with all steps, ordered by position:

SELECT
  ac.name AS chain_name,
  ast.position,
  COALESCE(u.full_name, ast.approver_role) AS approver,
  ast.is_required
FROM approval_chains ac
JOIN approval_steps ast ON ast.chain_id = ac.id
LEFT JOIN users u ON u.id = ast.approver_id
WHERE ac.id = '<chain_id>'
ORDER BY ast.position ASC;

Summary of request counts by status for each chain:

SELECT
  ac.name AS chain_name,
  ar.status,
  count(*) AS request_count
FROM approval_requests ar
JOIN approval_chains ac ON ac.id = ar.chain_id
GROUP BY ac.name, ar.status
ORDER BY ac.name, ar.status;

Requests resolved in the last 30 days with time-to-resolution:

SELECT
  ar.id,
  ar.entity_type,
  ar.status,
  ar.submitted_at,
  ar.resolved_at,
  ar.resolved_at - ar.submitted_at AS resolution_time
FROM approval_requests ar
WHERE ar.resolved_at >= now() - interval '30 days'
ORDER BY ar.resolved_at DESC;