# ADR-009: Each PWA Owns a Dedicated PostgreSQL Schema

## Status

Accepted, 2026-06-25.

## Status History

```yaml
status_history:
  - date: 2026-06-25
    status: Proposed
    changed_by: hkl
    reason: Formalising schema-per-PWA isolation pattern in use across all PWAs
    changed_via: adr-kit (360lm)
  - date: 2026-06-25
    status: Accepted
    changed_by: hkl
    reason: All 20 PWAs follow this pattern; no cross-schema table sharing exists
    changed_via: adr-kit (360lm)
```

## Context

The 360lm platform hosts 20+ PWAs sharing a single PostgreSQL database (`lm360`). Without schema isolation, tables from different PWAs would share the `public` schema, creating naming collisions, unclear ownership, and risk of accidental cross-PWA queries. The hub session and user identity data needs to be accessible to all PWAs, but each PWA's application data should be isolated. PostgREST's schema-based exposure model makes per-schema isolation the natural fit — each schema can have independent grants.

## Decision

Every PWA MUST use a dedicated PostgreSQL schema named after the PWA (e.g., `tour_pg`, `recce`, `vendors`, `finance`, `hub`). All tables, views, functions, and RPC endpoints for a PWA live in its own schema. Cross-PWA data access (e.g., reading hub user info from another PWA's function) is done via explicit schema-qualified queries (`hub.users`), never via shared tables in `public`.

**Decision Maker:** hkl

## Alternatives Considered

- **Single public schema for all PWAs.** Rejected: naming collisions inevitable (every PWA has a `users` or `sessions` concept); no access control boundary between PWAs; impossible to grant PWA-specific PostgREST access.
- **Separate database per PWA.** Rejected: 20 databases on a single VPS is operationally expensive; cross-PWA joins (e.g., hub auth + finance data) become impossible without foreign data wrappers; backup complexity multiplies.
- **Table prefix per PWA (e.g., tour_pg_uploads, recce_visits).** Rejected: all tables still in one namespace; grants must be per-table instead of per-schema; harder to reason about as the table count grows.

## Consequences

**Positive:**
- Clear ownership: `tour_pg.*` belongs to tour-pg, no ambiguity.
- Schema-level grants: `GRANT USAGE ON SCHEMA tour_pg TO web_anon` is one line.
- PostgREST can expose schemas selectively — each schema is an independent API surface.
- DROP SCHEMA CASCADE safely removes all PWA artifacts in one command (dev cleanup).

**Negative / Trade-offs:**
- Cross-schema queries require schema qualification (`schema.table`) — slightly more verbose.
- Migrations must specify the target schema explicitly.
- `search_path` must be set carefully in RPC functions to avoid accidental public schema fallback.

**Risks and mitigations:**
- Developer creates table in `public` by accident: PostgREST exposes it under the wrong schema. Mitigated: dev practice + Playwright tests that check table existence via schema-qualified paths.

## Related Decisions

- ADR-007 (SECURITY DEFINER triggers) — schema isolation affects which role can write cross-schema.

## References

- `memory/project_arch.md` — schema list per PWA
- PostgREST docs: schema isolation and grants