# ADR-033: Expense Approval RPC Atomically Deducts Impress in One DB Transaction

## Status

Accepted, 2026-06-22.

## Status History

```yaml
status_history:
  - date: 2026-06-22
    status: Proposed
    changed_by: hkl
    reason: Formalising atomic approval+deduction pattern in approve_sheet RPC
    changed_via: adr-kit (360lm)
  - date: 2026-06-22
    status: Accepted
    changed_by: hkl
    reason: approve_sheet RPC live and in use; impress balance always consistent with approval state
    changed_via: adr-kit (360lm)
```

## Context

Expense sheet approval has two effects: the sheet status changes to `approved`, and the impress (petty cash) balance is debited by the sheet total. In an application-layer two-call approach, the first call (approve status) can succeed while the second (debit impress) fails due to network error, concurrent update conflict, or PostgREST error. This leaves the sheet as `approved` but the impress balance unaffected — the books show more cash than actually exists. The reverse failure (impress debited, status not updated) is equally bad. Both failure modes are silent in the UI without extensive client-side retry logic.

## Decision

`approve_sheet(sheet_id, approver_id)` is a single PostgreSQL RPC that performs both operations inside one `BEGIN … COMMIT` transaction:
1. Update `expense.sheets SET status = 'approved', approved_by = approver_id`.
2. Debit `expense.impress SET balance = balance - sheet.total WHERE balance >= sheet.total` (fails if insufficient balance).
3. Insert `expense.impress_log` entry for audit trail.

If any step fails, the entire transaction rolls back. The RPC is `SECURITY DEFINER` so `web_anon` can write to the impress table (see ADR-007).

Application code calls one `POST /rpc/approve_sheet` — no split fetch sequence. There is no concept of "approved but not deducted" or "deducted but not approved."

**Decision Maker:** hkl

## Alternatives Considered

- **Two separate fetch() calls (approve status, then debit impress).** Rejected: partial failure creates inconsistent state with no automatic rollback; requires client-side retry and idempotency logic that adds complexity without eliminating the race window; tested and observed to create balance drift during slow-network testing.
- **Client-side balance check before calling approve, then two-call sequence.** Rejected: balance can change between check and debit (race condition with concurrent approvals); still does not protect against the first call succeeding and second failing.
- **Deferred reconciliation (nightly job syncs impress from approved sheets).** Rejected: balance is incorrect between approval and reconciliation; supervisors use the live balance to judge whether to approve further sheets; stale balance leads to over-approval.

## Consequences

**Positive:**
- Impress balance is always consistent with approval state — no partial states possible.
- Concurrent approvals are serialised at DB level (row lock on impress row).
- Insufficient balance causes the entire approval to fail with a DB error that the UI can display.
- Single RPC means one network call, one error surface, simpler client code.

**Negative / Trade-offs:**
- The RPC bundles approval logic and financial logic — changing either requires modifying and redeploying the RPC.
- `SECURITY DEFINER` requires careful review on any RPC change (see ADR-007).
- If the impress table structure changes (e.g. multi-currency), the RPC must be updated.

**Risks and mitigations:**
- Deadlock between concurrent approve_sheet calls: mitigated by consistent lock order (sheets row first, then impress row); PG will detect and roll back one of the concurrent transactions.
- Admin bypasses RPC and updates sheet status directly via PostgREST: mitigated by restricting direct UPDATE on expense.sheets to superuser; web_anon only has RPC access for approval.

## Related Decisions

- ADR-007 (SECURITY DEFINER trigger functions) — approve_sheet RPC uses SECURITY DEFINER for the same reason: writing privileged tables from web_anon.
- ADR-034 (HR salary cross-write to Finance) — same atomicity principle applied to cross-schema salary payment.
- ADR-009 (per-PWA schema isolation) — impress table lives in expense schema, same as sheets; no cross-schema complication here.

## References

- `memory/dbt_expense.md` — approve_sheet RPC, impress auto-deduct on approval
- `memory/finance_structure.md` — impress fund structure and balance rules
- `expense/index.html` — POST /rpc/approve_sheet call site