Database

Two drivers, one client per request

Each deployment picks its driver with one setting, DATABASE_DRIVER. neon uses Neon's serverless driver: every query is one HTTPS round trip, and a transaction opens a WebSocket for its own duration. postgres uses postgres.js through Hyperdrive and reaches any Postgres. A fresh copy of the kit deploys on neon. A copy that never set the variable stays on postgres, so upgrading changes nothing.

Application code never sees which one it is on. openDatabase(env) returns a client and a matching close, typed as the base both drivers share. It 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.

Where the two drivers differ, and how that is contained

Almost all data access goes through Drizzle's query builder, which returns the same thing on both. The one real difference is a raw execute(): postgres.js returns the rows as an array, Neon returns an object with a rows field. Code reads a raw result through two helpers, rows() and affected(), and a check in the test gate fails any code that indexes or casts a raw result instead. Raw values read the same under both — arrays, bigints, timestamps as text — because the client normalises them; error codes go through one helper; and session state outside a transaction is refused, because the Neon driver runs each query on a fresh connection.

That makes app code driver-agnostic by construction, and one small conformance suite proves it: the test gate runs the whole suite on postgres against Docker, then the conformance suite again on neon through a local Neon proxy, so the path a fresh copy deploys on is tested on every commit. Nothing in that suite may be skipped for one driver. Either way, neither pooler allows LISTEN/NOTIFY, advisory locks or prepared statements on the request path: realtime goes through the Durable Object hub and single-flight locking through KV.

Schema conventions: tenant keys, timestamps, enums

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.

pgvector: embeddings 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, then 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 and ready to switch on

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 both poolers the kit deploys through (Hyperdrive, Neon's) reset 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.