# ADR-040: Cross-Schema Active-State Sync Uses SECURITY DEFINER Trigger, Not Application Dual-Write

## Status

Accepted, 2026-06-22.

## Status History

```yaml
status_history:
  - date: 2026-06-22
    status: Proposed
    changed_by: hkl
    reason: Formalising trg_sync_custodian_active trigger for cross-schema deactivation propagation
    changed_via: adr-kit (360lm)
  - date: 2026-06-22
    status: Accepted
    changed_by: hkl
    reason: Trigger live; expense.employees deactivation immediately reflected in custodian.profiles
    changed_via: adr-kit (360lm)
```

## Context

When an employee is deactivated in `expense.employees` (the canonical employee table — see ADR-032), their associated `custodian.profiles.is_active` was a separate column with no automatic link. An admin deactivating an employee in the Admin PWA would call `UPDATE expense.employees SET active = false` — but the custodian profile would remain active, allowing the deactivated employee to still log in to the Custodian PWA. The fix required propagating the active state change to `custodian.profiles` automatically. Two approaches were evaluated: application-layer dual-write (Admin PWA calls two UPDATE endpoints) and DB trigger.

## Decision

A DB trigger `trg_sync_custodian_active` fires AFTER UPDATE on `expense.employees` when `active` changes. The trigger function `fn_sync_custodian_active()` is declared `SECURITY DEFINER` and propagates the change to `custodian.profiles.is_active` for the matching employee.

The trigger approach was chosen over application dual-write because:
- All paths that update `expense.employees.active` (Admin PWA, direct DB admin access, future HR PWA) automatically propagate to custodian without each caller knowing about the dependency.
- A missed dual-write in one call path (e.g. a future admin script) would silently leave custodian active; the trigger cannot be bypassed.

**Known edge case:** a manually-deactivated custodian who is later rehired as an employee will get their custodian profile auto-restored by this trigger. Admin must review whether the custodian role should also be restored or if the profile should remain deactivated. This is flagged as a UX decision to address in a future Admin screen.

**Decision Maker:** hkl

## Alternatives Considered

- **Application-layer dual-write (Admin PWA updates both tables).** Rejected: future code paths that update expense.employees.active (bulk imports, HR automation) must independently know to also update custodian.profiles — this creates a hidden contract that is not enforced; one missed call creates a security bypass.
- **Polling job that syncs active states periodically.** Rejected: sync lag (up to polling interval) means a deactivated employee can still log into Custodian for minutes; unacceptable for an access-control propagation.
- **Application checks expense.employees.active at every Custodian login (no separate custodian.profiles.is_active).** Rejected: would require removing the custodian-specific is_active column and all code that reads it; custodian.profiles.is_active has custodian-specific semantics (e.g. a custodian may be active as employee but inactive as custodian); the columns serve different purposes.

## Consequences

**Positive:**
- Deactivation propagates instantly to custodian without Admin PWA knowing about the dependency.
- Future paths that update expense.employees (HR PWA, admin scripts) automatically propagate.
- Cannot be bypassed — trigger fires for any UPDATE on expense.employees.active.

**Negative / Trade-offs:**
- Trigger adds latency to expense.employees UPDATE — acceptable (single row update to custodian.profiles is negligible).
- `SECURITY DEFINER` on the trigger requires careful audit before deployment (see ADR-007).
- The rehire edge case (auto-restoring a manually-deactivated custodian) requires an Admin UX resolution.

**Risks and mitigations:**
- Trigger fires unintentionally on non-active-change updates (e.g. name change): mitigated by `IF OLD.active IS DISTINCT FROM NEW.active THEN` guard in trigger body.
- SECURITY DEFINER privilege escalation: mitigated by trigger function having minimal grants — only the specific UPDATE on custodian.profiles.is_active; no broader custodian schema access.

## Related Decisions

- ADR-007 (SECURITY DEFINER trigger functions) — this trigger requires SECURITY DEFINER to write to custodian.profiles from web_anon context.
- ADR-032 (expense.employees cross-schema FK anchor) — the trigger fires on the canonical employee table and propagates to dependent schemas.
- ADR-039 (two-mode reactivation) — the edge case of rehire auto-restore interacts with the limited-mode reactivation flow.

## References

- `memory/dbt_custodian.md` — trg_sync_custodian_active, fn_sync_custodian_active, rehire edge case note
- `expense/index.html` — employee deactivation path that fires the trigger
- `finance/custodian/index.html` — is_active check on custodian login