Database

How the API talks to PostgreSQL, and the rules that keep it plain.

How it worksCopy Anchor Link

Queries are written in PostgreSQL, with ? markers, and go through a small interface: db.prepare(sql).bind(...).first() / all() / run() and db.batch([...]) (a transaction). The same call shape is used in the code and in the tests.

• src/api/database.ts hands each statement to the connection pool in the same process.
• server/sql.mjs numbers the placeholders (? to $1, $2) and splits migration files. Nothing else in a query is rewritten.
• src/api/schema.ts is generated from the migrated schema (npm run gen:schema, one *Row type per table). test/pg/schema.test.ts fails when the file and the migrations disagree.
• Multi-row writes go through src/api/sqlRows.ts, so a large write is one statement, not one per row.

MigrationsCopy Anchor Link

Migrations are in migrations/, numbered, and run once each in order at start-up, in one transaction.

• Shipped migrations are never edited, because installs have recorded them. An index or a column is a new migration.
• 0001 to 0026 were written for SQLite, when the app ran on Cloudflare D1. They are read through a frozen converter, server/legacy-migrations.mjs, and only those files. Every later migration is plain PostgreSQL, and a test fails if one uses a SQLite type.
• CREATE INDEX CONCURRENTLY cannot run inside the migration transaction. On a large table, say in the migration's comment how long the index takes to build.

Rules the tests enforceCopy Anchor Link

• No SQLite spellings in the app's queries (INSERT OR IGNORE, two-argument MIN/MAX, LIKE for case-insensitive matching): test/core/native-sql.test.ts reads the source.
• A nullable column in an ORDER BY says where its NULLs go (NULLS FIRST or NULLS LAST).
• Every fixed query is prepared against the migrated schema.
• Every route runs a bounded number of statements: test/api/statement-budget.json.

Still openCopy Anchor Link

• Types for the queries that are assembled from parts (search, the thread lists, the contacts filters). The proposal is Kysely on the generated types, one module at a time. Fixed queries stay as SQL: they read well and are tested.
• The smaller *Row interfaces in calendar/types.ts, routes/ and ai/ are still written by hand.

NextCopy Anchor Link

• Development: run the server and tests locally.
• API: the HTTP side.