Database

Onekit uses PostgreSQL 17 locally and Drizzle ORM. The schema holds Better Auth’s user, session, account, and verification tables and the CRM’s workspace tables. The CRM tables are synced to every signed-in device through Zero, which needs logical replication — see Sync below.

Local database

bun run db:up
bun run db:migrate

Set DATABASE_URL=postgresql://onekit:onekit@localhost:54329/onekit in your untracked .env before migrating. Docker Compose binds PostgreSQL to the loopback address on port 54329 and stores data in a named volume. bun run db:down stops the service and keeps data. Credentials in compose.yaml are local defaults only.

Use bun run db:studio to browse the configured database. Database commands always act on DATABASE_URL; verify its target before running migrations against another environment. No deployment automatically runs migrations.

Sync: logical replication and zero-cache

Every CRM record, list, board, record page, favorite, saved view, pipeline and timeline is read and written through Zero. Each device keeps a synced copy of what is on screen, so a list opens from local data, an edit shows at once, and a change made anywhere arrives everywhere without a refresh. zero-cache, a separate service, replicates the CRM tables out of PostgreSQL and serves the clients over WebSockets. For the two decisions only the app may make, it calls back into it: what a named query means for this session (/api/zero/query), and whether a write is allowed (/api/zero/mutate). Writes run the same validation, permission checks, plan limits and activity logging as the REST routes, inside the same tenant transaction, so row-level security applies to them too.

REST remains for what a synced window cannot answer: counts and totals, the Calculate footer’s aggregates, rows past the 2,000-row synced window of a very large list, search with its per-object counts, CSV import and export, and /api/me (plan limits, role, workspace).

zero-cache needs three things from the database, and bun run zero:check checks each one, with the fix spelled out when one fails. Migrations run the same check:

1. Logical replication — wal_level = logical. The bundled Compose PostgreSQL already sets it.
2. The onekit_zero publication, which the migrations create. It publishes the CRM tables only, never the auth tables.
3. A direct connection as a role that can replicate. zero-cache holds a replication slot, which a connection pooler cannot carry; give it that URL as ZERO_UPSTREAM_DB. The role must also read every row through row-level security, because the replica holds every workspace and each synced query filters to the session’s own. Migration 0017 grants exactly that, read-only, to a role named onekit_zero. Run bun run db:migrate with ZERO_DB_PASSWORD set and it creates that role (scripts/db/setup-zero-role.ts), with replication rights and two databases of its own for zero-cache’s bookkeeping. Any other role needs BYPASSRLS.

Change the schema

1. Edit src/server/db/schema.ts.
2. Run bun run db:generate and review the generated SQL and snapshot in drizzle/.
3. Run bun run db:migrate against your local database.
4. Commit both the schema and migration artifacts together.
5. For a column on a synced CRM table, add it to src/zero/schema.ts too; tests/zero-schema.test.ts fails until the two agree. A new synced table also joins the onekit_zero publication in a migration.

Generation works without a database connection or DATABASE_URL. Migration requires a reachable PostgreSQL instance and records completed migrations in Drizzle’s migration journal. Run migrations once as an explicit release step against a backed-up production database before relying on new schema fields. Avoid destructive column changes while an older app revision still uses them.

Adding a Better Auth plugin can require additional tables or columns; regenerate its schema using the Better Auth tooling, reconcile that schema with this file, and generate a migration before enabling the plugin. See Better Auth’s database schema and Drizzle migrations.

Query from server code

import { eq } from 'drizzle-orm'
import { getDatabase } from '@/server/db'
import { user } from '@/server/db/schema'

const records = await getDatabase()
  .select({ id: user.id, name: user.name })
  .from(user)
  .where(eq(user.id, authenticatedUserId))

Authorize the request before querying private data. getDatabase() lazily creates a pooled client; importing the module does not connect to PostgreSQL. The default pool maximum is 10 connections per server process. Configure DATABASE_POOL_MAX from 1–100 and account for the number of application replicas and your database connection limit. Because each request to a workspace-scoped route runs inside one transaction (below), that maximum now bounds concurrent requests rather than concurrent queries.

Querying the CRM tables

The eight workspace tables — company, contact, pipeline, stage, deal, activity, favorite, view — carry a row-level policy keyed to a transaction-local setting. Use the database on the session context, never getDatabase(), for those. withSession hands every handler an open transaction that has already named the workspace, so this is both the scoped client and the only one the policies will answer:

import { scopedWhere } from '@/server/crm/scope'
import { contact } from '@/server/db/schema'
import { jsonResponse, withSession } from '@/server/http'

export const GET = (request: Request) =>
  withSession(request, async ({ database, scope }) =>
    jsonResponse(await database.select().from(contact).where(scopedWhere(contact, scope))),
  )

A query over one of those tables on the pool instead returns zero rows, not every row — which is the right way round for a mistake, and is why it must be an obvious failure rather than a silent one. The same applies to code that runs outside a request, such as a Better Auth database hook: wrap it in withTenant(database, organizationId, …) from @/server/tenant. See Row-level security.

Use the database provider’s TLS connection settings in DATABASE_URL, usually ?sslmode=require. Onekit does not disable certificate verification. Providers that sign with their own certificate authority, such as Supabase, need that CA trusted: set DATABASE_SSL_CA to the PEM certificate the provider publishes (Supabase: project settings, Database, SSL certificate). Without it Node reports SELF_SIGNED_CERT_IN_CHAIN. For deployment, supply that URL through the SST DatabaseUrl secret. The starter does not provision an Aurora cluster or mutate an external database during build/deploy.

/api/health reports web-process liveness only. It deliberately does not initialize auth or probe the database; it should not be treated as proof that sign-in is working.