# ADR-074: PostgREST Multi-Schema Routing via Accept-Profile Header

## Status

Accepted, 2026-06-27.

## Status History

```yaml
status_history:
  - date: 2026-06-27
    status: Proposed
    changed_by: hkl
    reason: Formalising PostgREST Accept-Profile / Content-Profile header pattern established across all PWAs
    changed_via: adr-kit (360lm)
  - date: 2026-06-27
    status: Accepted
    changed_by: hkl
    reason: All PWAs use this pattern; single PostgREST instance serves all schemas via headers
    changed_via: adr-kit (360lm)
```

## Context

The 360lm platform runs a single PostgREST instance at `/db/` serving 20+ PWA schemas (`expense`, `finance`, `custodian`, `recce`, `vehicle`, `hr`, `sales`, `hub`, `activity`, `dispatch`, `production`, `stores`, `installation`, and others). Without an explicit schema routing mechanism, PostgREST defaults all requests to the `public` schema. To serve multiple schemas from one instance, PostgREST supports the HTTP `Accept-Profile` header (for reads) and `Content-Profile` header (for writes), allowing the browser client to specify which schema a request targets. Each PWA must send these headers with every fetch call to ensure isolation and correctness.

**Context from related decisions:**
- ADR-009 established schema-per-PWA isolation
- ADR-014 established PostgREST as the API layer
- ADR-010 established that cross-schema reads use views in the PWA's own schema, not header-switching mid-request
- ADR-007 established SECURITY DEFINER for privileged writes

## Decision

1. **Single PostgREST instance** serves all 20+ PWA schemas via HTTP headers — not separate instances per PWA.

2. **Every PWA fetch MUST include two headers:**
   - `Accept-Profile: <pwa_schema>` — for all HTTP requests (GET, HEAD, OPTIONS)
   - `Content-Profile: <pwa_schema>` — for mutation requests (POST, PATCH, DELETE, UPSERT)

3. **Cross-schema reads** (e.g., expense PWA reading hub.employees) use a PostgreSQL VIEW in the PWA's own schema that wraps the cross-schema data — NOT switching `Accept-Profile` mid-request.

4. **No separate PostgREST instance per PWA** — operational overhead is eliminated by using headers.

5. **The `web_anon` role** is the anonymous PostgREST connection role; all RPCs needing elevated access use `SECURITY DEFINER` (see ADR-007).

**Decision Maker:** hkl

## Implementation Notes

### Fetch Header Pattern

Every fetch in a PWA must include the schema headers. Example from expense PWA:

```javascript
const pwaSchema = 'expense';

// GET request
const response = await fetch(`/db/employees`, {
  headers: {
    'Accept-Profile': pwaSchema,
    'Content-Type': 'application/json'
  }
});

// POST request (INSERT)
const response = await fetch(`/db/employees`, {
  method: 'POST',
  headers: {
    'Accept-Profile': pwaSchema,
    'Content-Profile': pwaSchema,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({ name: 'John', email: 'john@example.com' })
});

// PATCH request (UPDATE)
const response = await fetch(`/db/employees?id=eq.5`, {
  method: 'PATCH',
  headers: {
    'Accept-Profile': pwaSchema,
    'Content-Profile': pwaSchema,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({ name: 'Jane' })
});

// DELETE request
const response = await fetch(`/db/employees?id=eq.5`, {
  method: 'DELETE',
  headers: {
    'Accept-Profile': pwaSchema,
    'Content-Profile': pwaSchema,
    'Content-Type': 'application/json'
  }
});
```

### Cross-Schema Reads via Views

When expense PWA needs to read employee master data from hub.employees, do NOT switch schemas mid-request:

**WRONG:**
```javascript
// BAD: switching Accept-Profile mid-request
const hubData = await fetch('/db/employees', {
  headers: { 'Accept-Profile': 'hub' }
});
```

**CORRECT:**
Create a view in the expense schema that wraps the hub data:

```sql
-- In expense schema init SQL
CREATE VIEW expense.hub_employees AS
SELECT id, name, email, role
FROM hub.employees;
```

Then fetch normally through expense schema:
```javascript
const response = await fetch('/db/hub_employees', {
  headers: { 'Accept-Profile': 'expense' }
});
```

### PostgREST Configuration

The PostgREST instance must include all PWA schemas in `PGRST_DB_SCHEMAS`:

```bash
PGRST_DB_SCHEMAS="expense,finance,custodian,recce,vehicle,hr,sales,hub,activity,dispatch,production,stores,installation,vrs,vendors,tour_pg,tour_planner,creditcard,counters,learn"
```

(See `/root/360lm-web/docker-compose.yml` for current list.)

### Error Handling

If a PWA sends a request WITHOUT the `Accept-Profile` header, PostgREST defaults to the `public` schema. If `public` is not exposed (recommended), the request will return `404 Not Found` for endpoints. Always include both headers to avoid silent failures.

## Alternatives Considered

- **Separate PostgREST instance per PWA.** Rejected: operational overhead (20+ containers, 20+ ports, 20+ configuration sets); DNS/routing complexity; memory inefficiency; no benefit over header-based routing.
- **URL-based schema routing (e.g., `/db/expense/employees`).** Rejected: requires PostgREST-level routing or reverse-proxy rewrite; more complex than HTTP headers; not a standard HTTP pattern.
- **Hardcoded schema in browser (client-side selection).** Not applicable; headers are the standard HTTP mechanism for this.
- **Allow browsers to switch schemas mid-request via Proxy Pattern.** Rejected: violates ADR-010 (cross-schema reads use views, not header-switching); introduces ambiguity about data origin.

## Consequences

### Positive

- **Single operational deployment** — one PostgREST container instead of 20+, easier to update and restart.
- **Minimal overhead** — HTTP headers are lightweight and standardized.
- **Schema isolation preserved** — each PWA's fetch is bound to its schema; no accidental cross-schema leakage.
- **Easy to audit** — header-based routing is explicit in every fetch call; code review can verify schema names.
- **PostgREST-native** — `Accept-Profile` is the standard HTTP header for PostgREST multi-schema support (per RFC 9110 + PostgREST docs).

### Negative / Trade-offs

- **Every fetch must include headers** — developers must remember to add them; forgetting defaults to `public` (if exposed) with silent failure if not.
- **Cannot switch schemas mid-request** — cross-schema reads MUST use views, not header-switching (see ADR-010).
- **Error messages are generic** — if schema name is misspelled, PostgREST returns `404` for all endpoints (unclear whether the endpoint doesn't exist or schema doesn't exist).

### Risks and Mitigations

- **Developers forget to add headers** — code review + Playwright tests that verify headers are sent. Can also add a fetch wrapper function to all PWAs that auto-injects headers.
- **Schema name typos** → silent 404 → data doesn't load → mitigated by centralizing PWA schema name in a constant and using that constant in all fetch calls.
- **Mixing Accept-Profile and Content-Profile** → requests fail — mitigated by fetch wrapper that ensures both headers are always sent together.

## Related Decisions

- [ADR-009](ADR-009-each-pwa-owns-its-db-schema.md) — Establishes schema-per-PWA isolation
- [ADR-014](ADR-014-postgrest-as-api-layer.md) — Establishes PostgREST as the API layer
- [ADR-010](ADR-010-cross-schema-data-via-proxy-not-postgrest.md) — Cross-schema reads use views, not header-switching
- [ADR-007](ADR-007-pg-trigger-security-definer.md) — Trigger functions use SECURITY DEFINER for privileged writes

## References

- PostgREST schema isolation docs: https://postgrest.org/en/v12/how_tos/embedding.html#schema-isolation
- PostgREST Accept-Profile header: https://postgrest.org/en/v12/references/api/schemas.html
- HTTP RFC 9110 Accept-Profile header: https://www.rfc-editor.org/rfc/rfc9110#name-accept
- `memory/project_arch.md` — PostgREST configuration and schema list
- `/root/360lm-web/docker-compose.yml` — PGRST_DB_SCHEMAS environment variable