# ADR-010: Cross-Schema or Privileged Data Access Goes Through the Proxy, Not PostgREST Directly

## Status

Accepted, 2026-06-25.

## Status History

```yaml
status_history:
  - date: 2026-06-25
    status: Proposed
    changed_by: hkl
    reason: Formalising pattern established during tour-pg counter search (Phase 4.16)
    changed_via: adr-kit (360lm)
  - date: 2026-06-25
    status: Accepted
    changed_by: hkl
    reason: Pattern used in tour-pg-proxy; consistent with proxy architecture
    changed_via: adr-kit (360lm)
```

## Context

PostgREST exposes one schema per API surface. When a PWA needs data from a different schema (e.g., tour-pg needing to search `counters.counter_v`), PostgREST cannot serve it without reconfiguring the exposed schema or granting cross-schema access to `web_anon`. Reconfiguring PostgREST's exposed schema for one PWA's needs is a shared-infra change that affects all PWAs. Similarly, browser clients cannot directly query PostgREST on the counters schema because it would require a separate PostgREST instance or a schema switch — both are heavy solutions. The tour-pg proxy (`/opt/tour-pg-proxy/server.js`) already has a pool connection to the database and can run arbitrary SQL against any schema directly.

## Decision

When a PWA needs data from a schema it does not own, or needs a query that PostgREST cannot express (cross-schema joins, custom SQL, array-contains filters), the query MUST be implemented as a new endpoint in the PWA's dedicated proxy service, using the proxy's direct database pool connection. Do NOT reconfigure PostgREST's exposed schema or add cross-schema grants to `web_anon` to solve individual PWA query needs.

**Decision Maker:** hkl

## Alternatives Considered

- **Add the foreign schema to PostgREST's exposed schemas list.** Rejected: changes shared infra for all PWAs; every table in the schema becomes exposed — violates least-privilege principle.
- **Grant web_anon SELECT on the foreign schema's tables.** Rejected: expands web_anon privileges beyond the PWA's own schema; any PostgREST endpoint can then query those tables — uncontrolled exposure.
- **Duplicate data from the foreign schema into the PWA's schema via triggers or materialised views.** Rejected: data duplication, sync lag, double storage; the source of truth becomes unclear.
- **Create a cross-schema SQL function in the PWA's schema that queries the foreign schema.** Acceptable for simple cases, but requires SECURITY DEFINER (see ADR-007) and still exposes the function via PostgREST — prefer proxy endpoint for new work.

## Consequences

**Positive:**
- No PostgREST reconfiguration needed — shared infra untouched.
- Proxy endpoint can use full SQL expressiveness (ILIKE, array operators, JOINs across schemas).
- Authentication handled at proxy layer (session check before query).
- Easy to add rate limiting, input validation, and logging in one place.

**Negative / Trade-offs:**
- Each cross-schema query needs a new proxy endpoint — slightly more code than a PostgREST RPC.
- Proxy must be deployed and running; adds an operational dependency.
- PWA proxy image must be rebuilt and redeployed when new endpoints are added.

**Risks and mitigations:**
- SQL injection in proxy endpoint parameters: mitigated by using parameterised queries (`pool.query(sql, params)`) — never string-concatenated SQL.
- Proxy down = cross-schema queries unavailable: mitigated by Docker restart policy + health checks.

## Related Decisions

- ADR-009 (schema-per-PWA) — establishes the isolation that makes this decision necessary.
- ADR-007 (SECURITY DEFINER) — alternative for simple cross-schema reads via SQL functions.

## References

- `memory/feedback_cross_pwa_safety.md` — Cross-PWA Safety Rule
- `/opt/tour-pg-proxy/server.js` — reference implementation (`GET /db/counters-search`)
- tour-pg Phase 4.16 — first use of this pattern for `counters.counter_v` search