# ADR-014: PostgREST Is the API Layer — No Custom REST Framework

## Status

Accepted, 2026-06-25.

## Status History

```yaml
status_history:
  - date: 2026-06-25
    status: Proposed
    changed_by: hkl
    reason: Formalising foundational API architecture decision
    changed_via: adr-kit (360lm)
  - date: 2026-06-25
    status: Accepted
    changed_by: hkl
    reason: All PWAs use PostgREST; no custom API server exists for application logic
    changed_via: adr-kit (360lm)
```

## Context

360lm needs an API layer between PWA browser clients and PostgreSQL. Writing a custom REST API (Node.js/Express, Python/FastAPI, Go) would require: designing route handlers for every table operation, writing authentication middleware, managing DB connection pools, and maintaining server-side code as a separate codebase. The team has no backend developer. PostgREST auto-generates a REST API directly from the PostgreSQL schema — tables become endpoints, PostgreSQL functions become RPC endpoints, and row-level security + role grants replace custom auth middleware.

## Decision

All standard CRUD operations (SELECT, INSERT, UPDATE, DELETE) on application tables go through PostgREST at `/db/` (production) or `/db/` (dev). Business logic lives in PostgreSQL functions (RPCs) callable via `POST /db/rpc/<function_name>`. No custom Node.js/Python/Go API server is written for application logic. The only exceptions are PWA-specific proxy services (e.g., tour-pg-proxy, slides-proxy) for operations PostgREST cannot express — cross-schema queries, file handling, external API calls (see ADR-010).

**Decision Maker:** hkl

## Alternatives Considered

- **Node.js + Express custom API.** Rejected: requires writing and maintaining route handlers for every operation; connection pooling, error handling, and auth middleware all need implementation; adds a full JS server codebase with no benefit when PostgREST provides all of this from the DB schema.
- **Python FastAPI / Django REST.** Rejected: same custom API problem; adds Python runtime dependency; no developer with Python backend experience on the team.
- **GraphQL (Hasura or PostGraphile).** Rejected: GraphQL query language adds a learning curve; over-flexible for mobile PWA use cases (REST with explicit endpoints is easier to reason about); Hasura adds a managed service dependency. // ponytail: upgrade trigger=complex nested queries across 5+ tables become common
- **Firebase / Supabase (BaaS).** Rejected: vendor lock-in; data sovereignty concern (all data currently on self-hosted VPS); migration cost if vendor changes pricing.

## Consequences

**Positive:**
- Zero API server code to write or maintain — all logic lives in PostgreSQL.
- Schema changes automatically update the API — add a column, it appears in the JSON response.
- Role-based access control via PostgreSQL grants — `web_anon` role limits what the browser can do.
- RPCs (PostgreSQL functions) allow complex business logic without a separate service.
- PostgREST is a single Docker container — easy to update and restart.

**Negative / Trade-offs:**
- Complex queries (cross-schema, custom aggregations) require PostgreSQL functions or a proxy endpoint — can't just write arbitrary SQL from the browser.
- PostgREST's filter syntax (`?column=eq.value`) is less familiar than REST conventions to new developers.
- No middleware layer — rate limiting, logging, and request validation must be done at Traefik level or inside PostgreSQL functions.

**Risks and mitigations:**
- PostgREST exposes too much: mitigated by `web_anon` role with minimal grants — only explicitly granted tables/functions are accessible.
- Schema changes break API consumers: mitigated by versioning views (not raw tables) where the API contract must be stable.

## Related Decisions

- ADR-009 (schema-per-PWA) — PostgREST exposes schemas; schema isolation is what makes per-PWA access control possible.
- ADR-010 (cross-schema via proxy) — where PostgREST falls short, a proxy fills the gap.
- ADR-006 (verify_pin response format) — PostgREST's TABLE array behaviour flows from this decision.

## References

- `memory/project_arch.md` — API base URL, PostgREST version
- `/root/360lm-web/docker-compose.yml` — PostgREST container config
- PostgREST docs: https://postgrest.org