# ADR-029: Employee Access Control Is Dual-Written to DB and hub-access.json

## Status

Accepted, 2026-06-22.

## Status History

```yaml
status_history:
  - date: 2026-06-22
    status: Proposed
    changed_by: hkl
    reason: Formalising dual-write pattern used by Admin PWA saveEmpAccess() for offline auth
    changed_via: adr-kit (360lm)
  - date: 2026-06-22
    status: Accepted
    changed_by: hkl
    reason: Pattern live; hub-access.json used as PostgREST fallback in all PWAs
    changed_via: adr-kit (360lm)
```

## Context

PWAs check employee access rights on load — which PWAs an employee can enter, which roles they hold. The primary source is the `hub.employee_access` table (via PostgREST). However, if PostgREST is down (restart, DB migration, container crash), PWAs cannot check access and either block all employees or skip the check entirely (both unacceptable). The VPS filesystem is always available even when PostgREST is down. Admin PWA already writes access changes to DB; adding a second write to a JSON file on disk costs negligible overhead and provides a reliable fallback.

## Decision

Employee access control is dual-written by Admin PWA's `saveEmpAccess()` function:
1. **Primary write:** `hub.employee_access` table via PostgREST (authoritative, supports full query/filter).
2. **Fallback write:** `/var/www/360lm/hub-access.json` on VPS disk (flat JSON, same-origin served, used when PostgREST is unreachable).

PWA access check order:
1. Try PostgREST `hub.employee_access` — use if reachable.
2. On network error or 5xx, fetch `/hub-access.json` from same origin — parse and check.
3. If both unreachable, deny access (fail closed, not open).

`hub-access.json` format mirrors the PostgREST response shape so PWA code handles both identically. Admin PWA must write both on any access change; writing only one is a bug.

**Decision Maker:** hkl

## Alternatives Considered

- **Single source (DB only), block access when PostgREST is down.** Rejected: employees are blocked during routine DB maintenance or container restarts — unacceptable for field operations where work cannot wait.
- **Single source (DB only), skip access check when PostgREST is down.** Rejected: allows any user to enter any PWA during downtime — security hole.
- **Cookie-based signed access tokens issued at login.** Rejected: requires a token-issuing service, signature verification, and token refresh logic — significant infrastructure for what is achieved by a static JSON file on disk.
- **IndexedDB cache of access rights per employee device.** Rejected: must be explicitly populated per device; employees using a new device or incognito session have no cache; requires a cache invalidation mechanism after admin changes access.

## Consequences

**Positive:**
- Employees can access their permitted PWAs during PostgREST downtime.
- Fail-closed (deny if both sources unreachable) — no security regression during outages.
- No extra service required — JSON file served by existing Nginx/Traefik as a static file.

**Negative / Trade-offs:**
- hub-access.json can drift from DB if a write partially fails (PostgREST write succeeds, disk write fails, or vice versa).
- JSON file must be regenerated whenever access schema changes — it is not auto-synced.
- Admin PWA's `saveEmpAccess()` is the single choke point for both writes; if the function is patched and one write is removed, the fallback silently becomes stale.

**Risks and mitigations:**
- Drift between DB and JSON: mitigated by Admin PWA always writing both in the same request handler; a health check can compare last-modified times.
- Stale JSON serving old access after an employee is deactivated: mitigated by Admin PWA immediately regenerating JSON on deactivation; PostgREST (primary) is the authority and is checked first when available.

## Related Decisions

- ADR-012 (Hub as SSO gateway) — Hub validates the session; this ADR covers post-session access checks.
- ADR-020 (offline-first) — dual-write pattern extends offline resilience to access control, not just data.
- ADR-007 (SECURITY DEFINER triggers) — access table writes from web_anon require SECURITY DEFINER if via trigger.

## References

- `memory/dbt_hub.md` — saveEmpAccess() dual-write implementation note
- `memory/dbt_admin.md` — Admin PWA as the writer of hub-access.json
- `hub/index.html` — fallback fetch of hub-access.json on PostgREST failure