The manager chain — recursive CTE deep dive + approval routing semantics

The full machinery behind "who approves this request" — one of the most question-rich corners of zoth.

The query (verbatim, internal/db/sqlc/queries.sql)

WITH RECURSIVE manager_chain AS (
  SELECT u.id, u.email, u.manager_id, 1 AS level
  FROM users u WHERE u.id = $1          -- seed: the requester
  UNION ALL
  SELECT u.id, u.email, u.manager_id, mc.level + 1
  FROM users u
  INNER JOIN manager_chain mc ON u.id = mc.manager_id   -- climb
  WHERE mc.manager_id IS NOT NULL AND mc.level < 25     -- cycle cap
)
SELECT id, email FROM manager_chain WHERE level > 1 ORDER BY level ASC;

Mechanics to narrate: the seed (requester), the recursive term (join users as the parent of the current frontier), UNION ALL accumulates levels, iteration stops when the frontier is empty (top of the chain / NULL manager). Postgres executes this as repeated index loops on idx_users_manager_id — each level is one indexed lookup.

The level < 25 cap — three reasons in one: (1) cycle defense: IdP data can contain A→B→A; without a cap the CTE recurses until stack/limit death — Postgres would actually error at max_recursive_depth or run unbounded with UNION ALL; the cap makes worst case 25 iterations; (2) sanity: 25 levels of management is deeper than any real org; (3) query cost bound: even pathological data costs microseconds.

What ResolveApprovers returns and what RouteApproval does with it: the chain ordered by level ascending; the first entry (closest manager) is inserted as the PENDING approvals row. Deeper managers are not queued as fallbacks in the current implementation — a gap you should know: the escalation story ("if manager doesn't act in 24 h, escalate to level 2") is not built; it would be an expiry-based sweep over pending approvals (a natural extension, and a great answer to "what would you build next").

The UNROUTED semantics — have a position

If the chain is empty (no manager on file — self-managed founder, or broken IdP data): RouteApproval logs and returns; the request sits PENDING with no approvals row. Consequences:

• checkDecisionAuthority: no pending approval found → returns (false, nil) — meaning "unrouted" — and any tenant member may decide, with the decision audited as authority: "UNROUTED".
• This is fail-open for workflow availability: an org mis-sync never strands requests.

Know the counter-argument (it's the interview follow-up): a stricter system fails closed — an admin queue catches unrouted requests, because "anyone can approve" is a weak security property for HIGH/CRITICAL sensitivity resources. Your defensible position: "unrouted + sensitivity gating — I'd fail-open for LOW/MEDIUM (availability wins) and fail-closed for HIGH/CRITICAL (park until an admin routes). That's a one-condition change in checkDecisionAuthority, and I'd implement it before any enterprise deployment." Volunteering the exact fix = bulletproof.

The surrounding facts

• First user synced into a tenant becomes ADMIN (identity.Service: CountByTenant == 0 → RoleAdmin) — bootstrap authority.
• Self-approval blocked: decide rejects when the decision-maker is the requester (audit findings C-2/C-3 remediation).
• RAG auto-approve is Track 1, human routing is Track 2 (Day 20 of the plan / RAG days): when auto-approve fires, decision_source='RAG_AUTO' is recorded — the approvals row documents what kind of authority acted, which is exactly what a compliance auditor asks ("was this a human or a model?").
• The manager tree is maintained by WorkOS webhook sync (user created/updated → manager_id upserted) — the IdP is the source of truth for the org chart; zoth is a projection. Drift in the org chart = drift in approval routing — one more reason drift/reconciliation thinking matters (Day 50).