Database

One driver, one client per invocation

The kit talks to Postgres through postgres.js and Drizzle, and nothing else. There is no second driver for edge cases, no serverless HTTP client alongside the TCP one, and no adapter layer papering over the difference — which means there is exactly one place where connection semantics, transactions and error shapes are decided.

createDatabase(url) returns a client and a matching close. A client is built by the database middleware for an HTTP request, or at the top of a queue consumer, a workflow step or a cron task, and is closed in waitUntil or a finally. Nothing is shared at module scope, because an isolate may be discarded between any two requests and a connection held across that boundary is a leak you cannot see.

One way to resolve the connection string

The URL is resolved by a single expression: a preview override if one is set, otherwise Hyperdrive's connection string, otherwise DATABASE_URL. Production goes through Hyperdrive to Neon; local development uses Hyperdrive's local connection string against Docker; tests point at a throwaway Postgres on another port. Same code path, three environments — the configuration differs, the resolution logic does not.

Hyperdrive is the pool, so each client's own maximum is small. It is a transaction-mode pooler, which means LISTEN and NOTIFY, advisory locks and prepared statements are unavailable — none of which appear on the request path. Realtime goes through the Durable Object hub instead of NOTIFY, and single-flight locking goes through KV instead of advisory locks.

Schema conventions that survive a second author

One file per table. A shared helper supplies the tenant foreign key — a UUID, cascading on delete, and always the first column of any composite index — and another supplies created_at and updated_at as timestamptz. Both helpers exist because the applications this kit was extracted from had mixed timestamp with timestamptz, and the resulting hour-off bug is tedious to find.

Closed sets are Postgres enums, with values appended and never reordered, because a migration cannot use an enum value it adds in the same statement. Flexible metadata is jsonb, typed from a zod schema in the shared package rather than from an interface written twice. Encrypted columns are plain text and are only ever written through the crypto helper.

Vectors are ordinary rows

Embeddings live in a vector column with an HNSW index using cosine distance, matching the operator the retrieval queries order by. The extension is created by the migration script before migrations run, not by a migration file, so the same command works against a managed Neon branch and the local pgvector image.

The dimension is a constant in the shared package and is part of the column type. Changing it is a new table and a re-embed, never an ALTER — which is why the source text of every document is kept even though the API never returns it. Query vectors are bound parameters and cast, never concatenated into SQL.

Migrations: role first, tables, then grants

The workflow is: edit the schema, generate the SQL, read the SQL, apply it. Applying it runs three things in order, and the order is the point.

db-roles --phase=role   # create the application role
migrate                 # create the extension, then run the migrations
db-roles --phase=grants # grant DML, revoke the auth-infrastructure tables

The role comes first because a security policy naming that role cannot be created before the role exists. Grants come last because a REVOKE can only name tables that exist. Both halves are idempotent, so re-running the command is safe.

The migration script rewrites a pooled Neon host to the direct host before connecting. A pooled backend can carry a stale read-only session setting that blocks DDL, and the resulting failure reads as a permissions problem rather than a routing one. Migrations are forward-only: undoing a schema change means writing the compensating migration.

Row-level security, shipped inert, with an honest threat model

Runtime isolation is the tenant predicate on every query, and that never changes. Alongside it, every tenant table carries an RLS policy, an application role exists that cannot log in, and a scoping helper is ready to wrap work in a transaction that sets the tenant as a scoped session setting. All of it is present, tested and switched off by default, behind TENANT_SCOPE_MODE.

A catalog-driven test compares the live database against the schema and fails the build if a tenant table has no policy, or an untenanted table has no recorded reason for being exempt. So the scaffolding cannot rot while it waits.

It is off by default because the well-known RLS pattern assumes session-mode pooling, and Hyperdrive resets connections between uses. The setting therefore has to be applied inside a transaction on every request, and the effect on latency and on the pooler's query cache is something to measure on real infrastructure before turning on — not to assume.