# ADR-080: Hub as Authoritative Session Registry — Cross-Schema Active-State Sync Architecture

## Status

Accepted, 2026-06-27.

## Status History

```yaml
status_history:
  - date: 2026-06-27
    status: Proposed
    changed_by: hkl
    reason: Synthesising hub architecture across ADR-012, ADR-026, ADR-029, ADR-040; documenting end-to-end identity flow and access propagation
    changed_via: adr-kit (360lm)
  - date: 2026-06-27
    status: Accepted
    changed_by: hkl
    reason: Architecture stable; all patterns live and interconnected; single source of truth for hub's role
    changed_via: adr-kit (360lm)
```

## Context

The 360lm platform has 20+ PWAs, each with their own PostgreSQL schema. Authentication, access control, and employee identity must be centralised and consistent across all schemas. The Hub PWA (`/hub/`) is the canonical source of truth for:

1. **Employee identity** — who is logged in (ID, name, role)
2. **PWA registry** — which PWAs exist and are active (ADR-016)
3. **Access rights** — which employees can access which PWAs
4. **Active/inactive status** — when an employee is deactivated, all PWAs must reflect that change

Previously, these concerns were scattered across multiple ADRs (ADR-012: hub as SSO; ADR-026: session bridge; ADR-029: dual-write fallback; ADR-040: cross-schema trigger sync). This ADR synthesises them into a single coherent architecture document, showing how identity flows from Hub login through to cross-schema access revocation.

### Data Flow Diagram

```
┌─────────────────────────────────────────────────────────────────────────┐
│  PHASE 1: LOGIN & SESSION CREATION                                      │
├─────────────────────────────────────────────────────────────────────────┤
│  1. Employee enters /hub/ with no session                               │
│  2. Hub prompts for PIN; verify_pin RPC returns {id, name, role}      │
│  3. Hub writes lm360-session to localStorage (shared across origin)    │
│  4. ?next= redirect sends employee back to requested PWA              │
└─────────────────────────────────────────────────────────────────────────┘
                              ↓
┌─────────────────────────────────────────────────────────────────────────┐
│  PHASE 2: SESSION HANDOFF & ACCESS CHECK                               │
├─────────────────────────────────────────────────────────────────────────┤
│  5. PWA loads; restoreSession() reads lm360-session from localStorage  │
│  6. PWA checks hub.employee_pwa_access via PostgREST (primary)         │
│     OR falls back to hub-access.json if PostgREST down (ADR-029)      │
│  7. If employee lacks access to this PWA, deny and redirect to hub    │
│  8. If access granted, PWA loads with employee context                │
└─────────────────────────────────────────────────────────────────────────┘
                              ↓
┌─────────────────────────────────────────────────────────────────────────┐
│  PHASE 3: CROSS-SCHEMA ACTIVE-STATE PROPAGATION                        │
├─────────────────────────────────────────────────────────────────────────┤
│  9. Admin deactivates employee: UPDATE expense.employees SET active=F  │
│ 10. Trigger trg_sync_custodian_active fires (SECURITY DEFINER)        │
│ 11. Trigger propagates to custodian.profiles.is_active (cascade)      │
│ 12. On next login attempt, deactivated employee is rejected           │
└─────────────────────────────────────────────────────────────────────────┘
```

### Key Tables

| Table | Schema | Purpose | Authority |
|---|---|---|---|
| `expense.employees` | expense | Canonical employee record (ID, name, role, active flag) | expense schema owner |
| `hub.pwa_registry` | hub | All registered PWAs (id, label, icon, url, is_live, access_group, etc.) | hub owner (Admin PWA) |
| `hub.employee_pwa_access` | hub | Which employees can access which PWAs (employee_id, access_group) | hub owner (Admin PWA) |
| `custodian.profiles` | custodian | Custodian-specific profile data; mirrors expense.employees.active via trigger | custodian owner (trigger-synced) |
| `[other_schema].employees` (if exists) | various | Schema-specific employee view or copy; may be trigger-synced from expense | per-schema owner |

## Decision

### 1. Hub Is the Authoritative Session Source

Hub PWA is the single entry point for all employee authentication. No PWA implements its own login screen or PIN verification. All PWAs redirect to `/hub/?next=<current-path>` if no valid session exists (ADR-012, ADR-001).

Session format (stored in `lm360-session` localStorage key):
```json
{
  "empId": "...",
  "name": "...",
  "role": "...",
  "loginAt": "<ISO 8601 timestamp>",
  "ts": <epoch ms>
}
```

Session is readable by all same-origin PWAs; writing to a session key requires Hub's signature (ADR-026).

### 2. Hub PWA Registry Controls Access

`hub.pwa_registry` table (updated via Admin PWA) defines:
- Which PWAs are "live" (`is_live = true`)
- Which access groups each PWA requires (`access_group`)
- Metadata (icon, label, sort order, notes)

`hub.employee_pwa_access` table (updated via Admin PWA) defines:
- Which employees have which access groups
- When access was granted and by whom

A PWA checks access by:
1. Reading `hub.employee_pwa_access` for the current employee (via PostgREST, primary)
2. Falling back to `hub-access.json` if PostgREST unreachable (ADR-029)
3. Matching the employee's access groups against the PWA's required group
4. Denying access if the employee lacks the required group (fail-closed)

**Management users (harish, pramod)** automatically get access to all live PWAs — no per-user access rows needed.

### 3. Offline Access Control — Dual-Write Pattern

When Admin PWA modifies `hub.employee_pwa_access`, it writes to TWO places:

**Primary (always):**
```sql
UPDATE hub.employee_pwa_access SET ... WHERE employee_id = ? AND access_group = ?;
```

**Fallback (same transaction):**
```
Write entire table snapshot to /var/www/360lm/hub-access.json on disk
Format: {empId: {access_groups: [...]}, ...}
```

If PostgREST is down (DB restart, migration, container crash), PWAs fetch `hub-access.json` from same origin instead. This ensures PWAs can still check access during routine infrastructure events.

**Fail-closed principle:** If both PostgREST and static JSON are unreachable, deny access. Do NOT default to "allow all" during outages.

### 4. Cross-Schema Active-State Sync — SECURITY DEFINER Trigger

When an employee is deactivated in the canonical `expense.employees` table, the active state must propagate to all dependent schemas (custodian, finance, etc.) instantly and atomically.

Mechanism:
```sql
CREATE OR REPLACE FUNCTION fn_sync_custodian_active()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  IF OLD.active IS DISTINCT FROM NEW.active THEN
    UPDATE custodian.profiles 
    SET is_active = NEW.active 
    WHERE employee_id = NEW.id;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_sync_custodian_active
AFTER UPDATE ON expense.employees
FOR EACH ROW
EXECUTE FUNCTION fn_sync_custodian_active();
```

**Why SECURITY DEFINER:** The trigger is fired by web_anon (via PostgREST) when Admin PWA calls the update. web_anon has no direct write permission on `custodian.profiles.is_active`. `SECURITY DEFINER` runs the trigger function as the schema owner, which has the necessary permission (see ADR-007).

**Atomicity:** The trigger fires within the same transaction as the original UPDATE, ensuring that `expense.employees.active` and `custodian.profiles.is_active` are always in sync.

**Cascading:** Future schemas (e.g., hr.employees, sales.employees) that introduce their own active flags should also have SECURITY DEFINER triggers synced from expense.employees.

### 5. New PWA Onboarding Checklist

When a new PWA is added to the platform:

1. **Register the PWA** in `hub.pwa_registry`:
   ```sql
   INSERT INTO hub.pwa_registry (label, icon, url, access_group, is_live, sort_order)
   VALUES ('Display Name', 'icon-name', '/path/', 'group_id', true, 99);
   ```

2. **Grant access to employees:**
   ```sql
   INSERT INTO hub.employee_pwa_access (employee_id, access_group, granted_by, granted_at)
   VALUES (...) ON CONFLICT (employee_id, access_group) DO UPDATE SET granted_at = NOW();
   ```

3. **Implement session check in new PWA** (copy from existing PWA):
   ```javascript
   function restoreSession() {
     const session = JSON.parse(localStorage.getItem('lm360-session') || '{}');
     if (!session.empId || !session.ts || isSessionExpired(session)) {
       const nextPath = encodeURIComponent(window.location.pathname + window.location.search);
       window.location.href = `/hub/?next=${nextPath}`;
       return null;
     }
     return session;
   }
   
   async function checkAccess(session, requiredAccessGroup) {
     try {
       const res = await fetch(`/db/rpc/my_pwa_access?access_group=${requiredAccessGroup}`, {
         headers: { 'Authorization': `Bearer ${session.token}` } // if using bearer token
       });
       if (!res.ok) throw new Error('No access');
       return true;
     } catch {
       // Fallback to hub-access.json
       const fallback = await fetch('/hub-access.json').then(r => r.json());
       return fallback[session.empId]?.access_groups?.includes(requiredAccessGroup) ?? false;
     }
   }
   ```

4. **Add to hub-access.json** dual-write (Admin PWA handles this automatically).

5. **Link safe-bottom.css** (ADR-002):
   ```html
   <link rel="stylesheet" href="/shared/safe-bottom.css">
   ```

## Implementation Notes

### Session Key Versioning

If the session format ever changes (e.g., adding a new field), version the key:
- Old: `lm360-session` (legacy, phased out)
- New: `lm360-session-v2` (current)

PWAs read the new key first; if missing, try the old key with a deprecation warning. After all PWAs are deployed, remove the old key fallback.

### Trigger Edge Cases

**Rehire scenario:** An employee was manually deactivated in custodian (e.g., completed a custodian assignment). They are later rehired as a regular employee. When expense.employees is activated, the trigger auto-activates custodian.profiles.is_active. This may or may not be desired — Admin PWA should provide a "review active flags" screen for such edge cases.

**Cascading to multiple schemas:** If finance.users or hr.employees also need active-state sync, add separate triggers with the same pattern. Each trigger function is minimal and independent.

### Performance Considerations

- **Trigger latency:** Single-row UPDATE in trigger is negligible (microseconds). PostgREST round-trip dominates.
- **Session storage size:** Keep `lm360-session` under 1 KB to avoid localStorage blocking main thread.
- **hub-access.json polling:** Fetch once per session, not on every page load. Cache in memory or IndexedDB with TTL.

## Alternatives Considered

- **Each PWA owns employee identity (no centralized source).** Rejected: 20 duplicate employee tables; inconsistent data; no way to enforce global deactivation (ADR-032 supersedes this).
- **Centralised auth service separate from Hub.** Rejected: over-engineered; Hub already provides auth; adds a new service dependency.
- **Application-layer dual-write for cross-schema sync instead of trigger.** Rejected: future code paths that update expense.employees (HR automation, bulk scripts) must independently remember to update dependent schemas; trigger is enforced automatically (ADR-040).
- **Polling service to sync active states between schemas.** Rejected: lag (minutes) between deactivation and enforcement; unacceptable for access control.
- **URL-based session tokens instead of localStorage.** Rejected: tokens appear in browser history and logs; `localStorage` keeps session private to the origin (ADR-026).

## Consequences

### Positive

- **Single source of truth:** Hub is the canonical source for employee identity and access. No conflicting data across schemas.
- **Instant propagation:** Cross-schema sync is atomic (trigger) or near-instant (fall-through after DB update).
- **Offline resilience:** hub-access.json allows access checks during PostgREST downtime (ADR-029).
- **Enforced by DB, not application code:** Triggers cannot be bypassed; future code paths automatically inherit sync behavior.
- **One login, all PWAs:** Employee logs into Hub once; all PWAs read the same session without re-authentication.
- **Clear ownership:** Hub owns authentication and access policy; each PWA owns its data and domain logic.

### Negative / Trade-offs

- **Hub is a single point of failure for authentication:** If `/hub/` is down, no new logins can occur. Existing sessions continue to work (they are stored locally), so the impact is limited to new login requests.
- **Session format is a shared contract:** Changing the `lm360-session` format requires coordinating all PWAs. Mitigated by versioning the key (lm360-session-v2) and providing fallback reads.
- **hub-access.json drift:** If Admin PWA's dual-write fails partially (DB write succeeds, disk write fails), the fallback becomes stale. Mitigated by health checks comparing DB and disk timestamps.
- **rehire edge case:** Manually deactivated custodian profiles are auto-reactivated when the employee is rehired as a regular employee. Requires Admin UX review (ADR-040).

### Risks and Mitigations

| Risk | Mitigation |
|---|---|
| Hub down → no new logins | Existing sessions work offline; hub downtime only blocks new logins. Deploy with high availability. |
| Trigger silently fails → active state not synced | Test trigger before deployment; add monitoring on trigger execution; review trigger logs after employee deactivation. |
| Session key collision → new PWA overwrites existing session | All session keys documented in ADR-026; check before adding a new key. |
| hub-access.json stale → offline access check uses outdated data | Admin PWA regenerates JSON immediately after every access change; primary (PostgREST) is checked first when available. |
| SECURITY DEFINER privilege escalation → trigger grants too much | Trigger function only has UPDATE on the specific column (is_active), not the entire custodian.profiles table. Audit ADR-007. |

## Related Decisions

- **ADR-012** — Hub as the SSO gateway. This ADR extends that by documenting the full registry and access control architecture.
- **ADR-016** — New PWAs must register in hub.pwa_registry. This ADR provides the context for why and how.
- **ADR-026** — Session handoff uses localStorage. This ADR documents what is being handed off and how other PWAs consume it.
- **ADR-029** — Dual-write to DB and hub-access.json. This ADR explains the offline fallback pattern and its role in access control.
- **ADR-040** — Cross-schema active-state sync uses SECURITY DEFINER trigger. This ADR documents the broader deactivation flow.
- **ADR-007** — Trigger functions writing privileged tables must use SECURITY DEFINER. This ADR applies the principle here.
- **ADR-032** — expense.employees is the canonical employee identity table. This ADR builds on that canonical role.
- **ADR-001** — All PWAs pass ?next= when redirecting to Hub. This ADR documents the ?next= mechanism.
- **ADR-028** — Feature ownership follows domain. This ADR formalizes Hub's ownership of auth/access, not feature logic.

## References

- `memory/project_arch.md` — Hub PWA registry and architecture overview
- `docs/adr/ADR-012-hub-as-sso-gateway.md` — Hub as auth gateway pattern
- `docs/adr/ADR-026-cross-pwa-session-bridge-via-localstorage.md` — Session handoff pattern
- `docs/adr/ADR-029-dual-write-offline-access-control.md` — Offline fallback pattern
- `docs/adr/ADR-040-cross-schema-active-state-sync-trigger.md` — Trigger-based sync pattern
- `hub/index.html` — Hub session write logic and PWA registry fetch
- `admin/index.html` — Admin PWA's saveEmpAccess() dual-write and pwa_registry CRUD
- `shared/safe-bottom.css` — Shared stylesheet for all PWAs
- `pwa_dev_style.md` — PWA development conventions (session handling, access checks)