database-backends.md

Database backends

PostgreSQL is the primary backend for larger deployments. SQLite is a supported secondary alternative for simple, self-contained open-source deployments. SQLite is not an automatic failover database. Both expose the same collection, record, filter, and permission APIs. PostgreSQL native behavior defines query semantics; SQLite supplies shims where needed and does not constrain PostgreSQL behavior.

Shared model and separate query adapters

Both backends store collection schemas as metadata and records in the shared records table with org_id, collection_id, and JSON data. Creating a collection does not create a database table.

The shared query layer validates field references, combines API filters with permission predicates, and handles aggregates and recurrence post-filters. Kysely composes those expressions and generates dialect-specific SQL and bound parameters. packages/server/src/db/query/adapters/ supplies PostgreSQL JSONB and SQLite JSON expressions, including proposed-record permission checks. The adapter is selected from the datasource configured for each Prisma client.

prisma-kysely generates database types from the same Prisma schema during prisma:generate and prisma:generate:postgres. The generated files live in packages/server/src/db/generated/ and are not committed. We use its Prisma 6-compatible 2.x release and Kysely 0.28, shared with BetterAuth's dependency. Generated types describe physical tables; user-defined JSON fields still require runtime collection-schema validation.

query/kysely.ts configures Kysely for compilation only. Prisma continues to own execution, connections, result decoding, and transactions. Execution helpers accept a Prisma client or transaction client and pass Kysely's SQL and parameters separately to Prisma's raw-query methods. There is no placeholder rewriting or second connection pool. Values and JSON paths are bound parameters; dynamic identifiers are validated before Kysely quotes them. Never put user input in sql.raw, sql.lit, or SQL template text. The only literal generated by the query layer is an internal numeric GROUP BY position, avoiding repeated bound paths being treated as different expressions by PostgreSQL.

PostgreSQL scalar equality uses JSONB containment:

SELECT id, data FROM records
WHERE org_id = $1 AND collection_id = $2
  AND data @> CAST($3 AS jsonb);
-- $3 = '{"status":"open"}'

SQLite evaluates the same filter with json_extract(data, ?) = ?, binding $.status and the requested value. Equality values must match the declared scalar type; a number field takes 42, not "42". Metadata fields use their native columns. Untyped json fields use extraction comparisons rather than treating scalar equality as document containment.

Comparison operands are validated against the declared field type for equality, ranges, every in member, and both between bounds. Aggregate having filters use the output type of the group column or numeric metric. Numeric operands must be finite numbers, boolean operands must be booleans, and text operands must be strings; values are not coerced. json field comparisons currently target PostgreSQL's extracted text, so their operands are strings. Null retains SQL's unknown-comparison behavior. Unknown fields (including inherited JavaScript property names) and empty operator objects are rejected as invalid queries.

contains uses PostgreSQL's native LIKE with an escaped literal substring. SQLite uses instr to shim case-sensitive matching instead of its ASCII case-insensitive LIKE. Both preserve null/missing behavior under negation. PostgreSQL remains authoritative; SQLite does not emulate arbitrary PostgreSQL collation configurations.

Numeric range comparisons, sorting, and aggregates use typed expressions. JSON-field sorting places null/missing values last ascending and first descending on both backends. Use is_null to match null or missing fields; equality with null does not match a row. Negated comparisons retain SQL's unknown result for missing/null values, including in permission predicates. Recurrence expansion remains shared application logic after SQL candidate selection.

PostgreSQL indexes and deployment

Generate and apply the PostgreSQL schema:

bun run --cwd packages/server prisma:push:postgres
bun run build:server:postgres

Set DATABASE_URL to the target PostgreSQL database as usual. The generated Prisma schema includes records_data_gin_idx, a GIN index on data with jsonb_path_ops, alongside the existing organization/collection index. This indexes supported JSONB containment queries; it is not a general index for all JSON operations. Ranges, ordering, substring searches, and aggregation may still scan candidate records and need workload-specific indexes.

The database planner decides whether to use GIN, the scope index, or a scan. The schema deployment process creates the indexes. Server startup opens the configured database and query adapter.

SQLite uses the canonical Prisma schema and SQLite migrations. It does not get the PostgreSQL GIN index. Its performance characteristics differ, but access control is enforced on both backends.

Supported collection schema edits

Collections support adding fields, removing fields, and editing descriptions. An existing field keeps its declared type, including enum/ref definitions. Description edits accept the same type.

Removing a field preserves its stored JSON values. A field name can be reused once all records are clear of that name, keeping existing values consistent with their declared types.

Backend contract tests

The same integration tests exercise records, filters, counts, aggregation, permissions, writes, schema restrictions, recurrence expansion, and team notification fanout and scheduling on both backends:

bun run --cwd packages/server prisma:generate
bun test packages/server/test/query-backends.test.ts

TEST_POSTGRES_URL=postgresql://user:password@localhost/testdb bun run test:postgres

The PostgreSQL runner creates a unique schema in the supplied test database, applies the generated schema, runs the contract tests, drops its schema, and restores the SQLite Prisma client. It also verifies that the generated equality predicate can use the GIN index. Run it separately from SQLite tests because Prisma client generation selects one provider at a time. These checks establish correctness and index eligibility, not a production performance benchmark.