ADR-0010: Postgres: a database per user, and the kernel's uid is the credential

Status: Accepted (2026-10-14). Supersedes the 2026-10 revisions of ADR-0001 (SQLite, one file per project) and ADR-0002 (a ledger per file). Implemented 2026-10-15.

Context

ADR-0001 started on PostgreSQL -- a database per user, owned by a per-user role, CONNECT revoked from PUBLIC -- and moved to SQLite because, at this scale, a database server was a liability and one file per user was the more literal reading of "a user's data is theirs". The guarantee it kept was that isolation is enforced by the kernel (uid and mode bits), not by application code.

What the SQLite layout costs has since become concrete:

• node:sqlite is synchronous, so every query blocks the event loop of the process that runs it -- every one of a user's projects at once, since they share one service host.
• Types are advisory: timestamps are text, booleans are 0/1, a strict table is the best on offer.
• There are no schemas, so "a project's tables" is a file, and the platform's own state is another file beside it, each with its own backup and copy semantics.
• The operator's direction for the platform (Postgres, Bun, Astro, a domain-shaped codebase) makes a real database server the platform's default rather than a liability: prospit runs one for the platform anyway, with a 1 GB budget.

The guarantee has to survive the move. It does, because Postgres can take its identity from the kernel too.

Decision

• One Postgres cluster, on the hub, reachable only by unix socket. No TCP listener at all.
• Peer authentication, and no passwords anywhere. Postgres asks the kernel (SO_PEERCRED) which uid is on the other end of the socket and maps it to exactly one role: the API's uid to cs_api, cs-<user> to the user's role. A process can only be the role its uid is. There is no secret to leak, rotate or put in sops -- the credential is still uid, exactly as ADR-0001 required, enforced by the kernel and checked by the server.
• A database per user, u_<user>, owned by the user's role, with CONNECT revoked from PUBLIC. Another tenant's role cannot open it at all (verified: permission denied for database). The platform's own state is cs_control, owned by cs_api. The API has no access to user databases, as it never had to user files.
• A schema per project, p_<slug> with - mapped to _ (inn-check -> p_inn_check), each with its own schema_migrations ledger. The public schema is dropped from user databases so nothing lands outside a project. The service host sets search_path to the project's schema per request, so project SQL names its tables unqualified, as it does today.
• Creating roles and databases is a separate role, cs_provision (CREATEROLE, CREATEDB, not superuser), used only by the provisioning oneshot. Nothing that serves requests can create a role.
• Limits per tenant role: CONNECTION LIMIT, statement_timeout, idle_in_transaction_session_timeout, temp_file_limit. The service host's pool is smaller than its role's connection limit. Postgres has no per-database disk quota; size is watched and alerted on instead (nixos2 ADR-0016).
• Identifiers are UUIDv7 everywhere: id uuid primary key default uuidv7() (built into Postgres 18). Time-ordered, so inserts append to the index like a sequence would; not enumerable like a sequence is; one format for platform and project tables alike. Secrets (session, invite and API tokens) stay separate random values -- a UUIDv7 is guessable to the millisecond and is an identifier, never a credential.
• Types are real: timestamptz (UTC), boolean, jsonb only where the shape is genuinely open. ADR-0002's rule stands: migration FILES live with their owner, the RUNNER is the platform's. The runner is back to what ADR-0002 first described -- a ledger per schema, an advisory lock, one transaction per migration -- and its checksum ignores comments, so a comment in an applied migration can be corrected without failing the integrity check.
• Migrations still run as the user's uid at service start, before anything is served (ADR-0009), so every object in a user's database is owned by the user's role from its creation.
• Cutover imports everything: the control database and every project's data.sqlite, into the new schemas, once; then the SQLite files are deleted after a verified backup.

Consequences

• Queries are asynchronous: a slow query no longer stalls every project a user has.
• ADR-0006 stands unchanged: a user's code must run as its own uid in its own process, because the uid IS the role.
• Isolation is enforced by the server on a kernel-verified identity. test-isolation (deleted with provision.ts, ADR-0001 Consequences) comes back as a check against the real layout: each tenant role opens its own database and is refused every other.
• A user's data is a pg_dump of their database, not a directory copy. Hand-over and backup are per user (nixos2 ADR-0016: per-database dumps).
• A user placed off the hub (nixos2 ADR-0015) cannot reach a unix socket on the hub. Not supported in this pass; it needs a bridge like the service socket's, and it is in the backlog with off-hub model access.
• One server is now a dependency of every request that touches data. It is local to the API and to every hub-placed service host, and it is supervised and health-checked by the fleet.

Alternatives rejected

• One database, a schema per user. Every role shares one catalog and can list the others' objects; CONNECT cannot separate them. Weaker for no gain.
• Row-level security in shared tables. Puts isolation in every query (ADR-0001, again).
• Passwords per role (scram) over TCP. A secret per tenant to provision and leak, for a server that only ever has local clients.
• Staying on SQLite. Workable, but synchronous and typeless, and against the platform's direction.