The data layer — Drizzle + fusion-db

Every row in the app lives in one Postgres database, reached through Drizzle — a SQL-first, fully typed query builder — and wired up by @tikab-interactive/fusion-db. There is no ORM magic and no global db object: the app builds its own client once, composing the reusable foundation schema with its own domains, and injects it everywhere. This page is how that fits together — the client, the schema, migrations, and writing a query that's safe by construction.

Coming from Django?

Django	Here
`models.py` — a `Model` subclass	`pgTable("…", { … })` in `src/db/schema/*.ts` — a typed object
`MyModel.objects.filter(...)`	`db.select().from(t).where(and(eq(...)))` — a typed SQL builder
`makemigrations` (introspect models)	`drizzle-kit generate` (diff the schema → SQL)
`migrate`	`drizzle-kit migrate` (apply the ordered SQL)
the app registry + `settings.DATABASES`	no singleton — you `createDb(schema, url)` and inject it
`AutoField` / a `UUIDField` PK	a short base62 `text` id, app-generated (never UUIDs)
`get_queryset()` scoping a list	an `ownerId` / visibility `WHERE` on every read (§8)

The big shift: Drizzle is closer to SQL than the Django ORM — you write select, where, orderBy, joins — but the table is a TypeScript value, so every result is typed end to end with no serializer. And there is no settings.DATABASES read at import time anywhere in the framework; the database is a value the app constructs and passes in.

`createDb`: no singleton, the app wires its own

fusion-db is deliberately tiny. Its whole entry point is one createDb function (plus a re-export of the schema) in fusion-db/src/index.ts:

fusion-db/src/index.ts

import { drizzle, type NodePgDatabase } from "drizzle-orm/node-postgres";
import type { Pool } from "pg";
 
/**
 * Build a fusion-db Drizzle client. The CALLER owns the connection string and the
 * schema — fusion-db never reads `process.env` and never holds a singleton, so a
 * dependent can't accidentally connect to the wrong database (a potentially
 * destructive mistake). The app creates the client once and passes it into the
 * fusion package APIs (`createAuth`, the audit recorder, …) by injection.
 *
 * Compose the foundation with your app's tables and let inference flow from the
 * argument so `db.query.<table>` stays fully typed:
 *
 *   import * as schema from "./db/schema"; // re-exports fusion-db/foundation + app tables
 *   export const db = createDb(schema, env.DATABASE_URL);
 *
 * The returned client exposes the underlying `pg` Pool as `db.$client` (e.g. for
 * `db.$client.end()` on graceful shutdown).
 */
export function createDb<TSchema extends Record<string, unknown>>(
	schema: TSchema,
	connectionString: string,
): NodePgDatabase<TSchema> & { $client: Pool } {
	return drizzle(connectionString, { schema });
}
 
export * from "./schema";

That's it — fusion-db never reads process.env and never holds a module-level db. The caller owns the connection string and the schema. The app builds the one client it uses in example/src/lib/db.ts:

example/src/lib/db.ts

import "#/env";
 
import { createDb } from "@tikab-interactive/fusion-db";
 
import * as schema from "#/db/schema";
 
/**
 * The app's single fusion-db client. fusion-db 2.0.0 owns no singleton — the app
 * creates the client once (composing the foundation schema with its own domains
 * and the connection string it owns) and injects it into the fusion package APIs
 * (`createAuth`, and the mailer/audit/settings helpers as they get wired in).
 *
 * Built WITH the composed schema (foundation + ProcessN + ProtokollN) so the
 * relational query API (`db.query.user.findFirst`) stays typed across the whole
 * domain. Server-only: it opens a Postgres pool, so it must never reach the
 * client bundle — import it from server files (auth, *-server, scripts) only.
 */
export const db = createDb(schema, process.env.DATABASE_URL!);
 
export type Db = typeof db;

The schema flows in as a type argument, so inference carries through: db.query.user, db.query.carolaThread, and every column type are known across the whole composed domain. The underlying pg pool is reachable as db.$client (e.g. db.$client.end() on shutdown).

Why no singleton — three concrete payoffs, each called out in the source comments:

No destructive accidents. A package that imported a db singleton would carry a connection string with it; a dependent could "accidentally connect to the wrong database (a potentially destructive mistake)." Here the connection only exists where the app makes it.
No import-time side effects. Importing a schema module opens no socket. That's what lets the same src/db/schema/*.ts files be imported by drizzle-kit (a CLI), by the browser stub, and by the server without any of them connecting.
Testable & multi-tenant by construction. A test or a per-tenant request can build a db against a different URL and inject it — there is no global to monkey-patch. The fusion packages take the client as an argument (createAuth, the audit recorder, the settings reader), never reach for an ambient one.

Loading diagram...

The gotcha: import tables from `/schema`, not the barrel

fusion-db has two entry points, and the difference is load-bearing:

@tikab-interactive/fusion-db (the barrel) re-exports createDb — which imports drizzle-orm/node-postgres and, transitively, the pg Node driver.
@tikab-interactive/fusion-db/foundation (fusion-db/src/schema) is plain table definitions — no pg, browser-safe.

App schema modules pull tables from the foundation subpath for exactly this reason — e.g. example/src/db/schema/carola.ts opens with:

example/src/db/schema/carola.ts

import { user } from "@tikab-interactive/fusion-db/foundation";

Importing user from the barrel instead would drag pg into whatever bundle that schema file lands in. The browser is protected anyway by a "browser" export condition: client-side, @tikab-interactive/fusion-db resolves to fusion-db/src/browser-stub.ts, whose createDb is a loud throw —

fusion-db/src/browser-stub.ts

/**
 * Browser-side stub for @tikab-interactive/fusion-db. The real client (`createDb`)
 * pulls in the `pg` Node driver, which needs Buffer/events APIs that don't exist in
 * browsers. Any client-side import resolves to this stub via the package's "browser"
 * export condition; server-only code (route loaders, startup wiring) hits the real
 * implementation because the bundler picks the "node"/default condition in SSR.
 *
 * The schema (plain Drizzle table definitions) is browser-safe and re-exported as-is
 * — only the client factory is replaced with a loud throw.
 */
const SERVER_ONLY =
	"@tikab-interactive/fusion-db is server-only: create the database client with createDb() in server-only startup code, never in a browser bundle.";
 
export function createDb(): never {
	throw new Error(SERVER_ONLY);
}
 
export * from "./schema";

So db itself must only be imported from server files (auth.ts, *-server.ts, scripts/*.ts); the table objects are safe anywhere. See Conventions → keep db-using code behind a server boundary and the request lifecycle for where those calls actually run.

The schema

A schema module is a set of pgTable(name, columns, extras?) definitions plus optional relations(...). The foundation lives in fusion-db (fusion-db/src/schema/foundation.ts): the four Better Auth tables (user, session, account, verification) plus the cross-app tables (mailbox, auditLog, appConfig, apiToken). App domains live in the app (example/src/db/schema/*.ts) and import { user } from the foundation — the dependency only ever points app → foundation, never back.

example/src/db/schema/index.ts is the single composed module — the one source both createDb and drizzle-kit read:

example/src/db/schema/index.ts

export * from "@tikab-interactive/fusion-db/foundation";
export * from "./project";
export * from "./agent";
export * from "./carola";
export * from "./chat-document";
export * from "./host";
export * from "./nyhetn";
export * from "./processn";
export * from "./protokolln";
export * from "./search-index";
export * from "./wikin";

IDs: short base62, never UUIDs

The house rule: a first-class object a user references (a URL, a rename, a delete) gets a short, readable, app-generated base62 id (example/src/lib/short-id.ts), and that id is the primary key — not a UUID-PK with a pretty alias bolted on. carolaThread is the canonical case:

example/src/db/schema/carola.ts

export const carolaThread = pgTable(
	"carola_thread",
	{
		// The thread id IS a short, human-readable base62 code (see `lib/short-id.ts`),
		// client-generated. It is the primary key AND the only id a user ever sees in the URL
		// (`/c/$id`, `/project/$key/c/$id`). No UUIDs — short ids are the house rule.
		id: text("id").primaryKey(),
		ownerId: text("owner_id")
			.notNull()
			.references(() => user.id, { onDelete: "cascade" }),
		scope: text("scope", { enum: ["general", "portfolio", "project"] })
			.notNull()
			.default("general"),
		// For a `project` thread: which building (route key) + its display name, so opening an old
		// thread restores its EXACT scope (§7 — the header follows the conversation). Nullable: only
		// project threads carry them; written once at first save alongside `scope`.
		scopeKey: text("scope_key"),
		scopeLabel: text("scope_label"),
		title: text("title").notNull().default("Ny tråd"),
		// Once the user RENAMES the thread, lock the title — `saveCarolaThread` stops re-deriving it
		// from the first message on subsequent saves.
		titleEdited: boolean("title_edited").notNull().default(false),
		// Pinned / favourite — toggled from the conversation header's actions menu.
		starred: boolean("starred").notNull().default(false),
		// The conversation's ACTIVE device (fusion-ai-host). Once Carola resolves a request to one of
		// the user's machines, it sticks here so follow-ups ("now rename them") target the SAME machine
		// without re-asking — conversational resolution, never a mode the user sets. Nullable: most
		// threads never touch a device. Written server-side by the device tools; the persistence upsert
		// above never clears it (its conflict-update sets only messages/title), so it survives saves.
		activeDeviceId: text("active_device_id"),
		messages: jsonb("messages").$type<unknown[]>().notNull().default([]),
		createdAt: timestamp("created_at").notNull().defaultNow(),
		updatedAt: timestamp("updated_at").notNull().defaultNow(),
	},
	(t) => [index("carola_thread_owner_idx").on(t.ownerId, t.scope, t.updatedAt)],
);
 
export type CarolaThread = typeof carolaThread.$inferSelect;

The one place a uuid() is fine is an opaque internal row never used as an identifier — chat_document_chunk.id stays a uuid().defaultRandom() because chunks are only ever queried by documentId, never referenced by a user. The rule governs user-referenced ids, not every column.

The patterns to recognise

Reading the schema files, the same handful of moves recur:

owner_id FKs + cascade. Owned data references user.id with { onDelete: "cascade" }, so deleting a user takes their threads, documents, and private search projections with them. Where a row should outlive the user, the FK uses { onDelete: "set null" } (e.g. auditLog.actorUserId — history survives, with actorName denormalized so the name persists).
$type<>() for typed JSON. jsonb("messages").$type<unknown[]>() (or a richer shape) tells Drizzle the TypeScript type of an otherwise-opaque jsonb column — typed reads/writes, no DB-level change.
Enums, two ways. A small fixed set that may grow can be an inline text enum (text("scope", { enum: [...] })) — adding a value is code-only, no migration. A first-class Postgres pgEnum (chatDocumentStatus, searchEntityType) gives a real DB type and is the choice when other tooling reads the column. The foundation's mailbox.kind comment spells the trade-off out: a text enum means "adding a kind is code-only — no migration."
Indexes & composite keys are the third pgTable argument: index(...).on(...), uniqueIndex(...), unique(...).on(...), and join tables with primaryKey({ columns: [a, b] }) instead of a surrogate id (see chatDocumentAttachment). Specialised DDL Drizzle can't model — a weighted Swedish tsvector generated column, a gin_trgm_ops operator class, a pgvector hnsw index — is hand-appended to the migration (the search_index and chat_document_chunk comments flag exactly that).
relations(...) declares the FK graph the relational query API (db.query.x.findFirst({ with: { … } })) traverses — e.g. processnProjectRelations wires a project to its members and roles.

A worked Django-port example with int PKs, a CommonModel mixin, M2M join tables, and denormalized permission columns is pulsn-apps/src/processn/schema.ts; the ProcessN domain page walks it.

Migrations

Drizzle migrations are generated from the schema diff, then applied in order — the same two-step rhythm as Django, but you don't hand-write the forward SQL.

bun run db:generate   # drizzle-kit generate — diff schema vs last snapshot → a new NNNN_*.sql
bun run db:migrate    # drizzle-kit migrate — apply pending files in order

example/drizzle.config.ts loads the env and points the tooling at the composed schema and the output folder:

example/drizzle.config.ts

import { config } from "dotenv";
import { defineConfig } from "drizzle-kit";
 
// Migrations live in the APP (Fusion plan §6.4). drizzle-kit does NOT read tsconfig
// `paths`, so it can't resolve the `@tikab-interactive/pulsn-apps/*` aliases the app uses
// at runtime. `drizzle-schema.ts` is a drizzle-kit-only barrel pointing at the package's
// REAL source files (relative) + the example-local domains; fusion-db's foundation resolves
// from the installed package in node_modules.
config({ path: [".env.local", ".env"] });
 
export default defineConfig({
	out: "./drizzle",
	schema: "./drizzle-schema.ts",
	dialect: "postgresql",
	dbCredentials: {
		url: process.env.DATABASE_URL!,
	},
});

drizzle-kit doesn't read tsconfig paths. It resolves @tikab-interactive/* from node_modules, not your path aliases — which is why app-side tables (agent, carola, chat-document, search-index) are defined in the app rather than in a linked package: the migration generator can only see them there. The schema comments call this out repeatedly.

Each generate writes two things into example/drizzle/:

the migration — 0017_salty_blonde_phantom.sql, statements separated by --> statement-breakpoint;
a snapshot — meta/0017_snapshot.json, the full schema state after that migration, plus an entry in meta/_journal.json. The next generate diffs your live schema against the latest snapshot to compute the next migration. Don't hand-edit snapshots — they're the diff baseline.

Loading diagram...

When you hand-author a migration

generate needs a TTY for some prompts (e.g. "is this a rename or a drop+add?"), which doesn't fit a scripted/agent flow, and some DDL it can't express at all. So a few migrations in example/drizzle/ are hand-written:

Extensions. 0001_enable_postgis.sql is one line — CREATE EXTENSION IF NOT EXISTS postgis;. Drizzle never emits CREATE EXTENSION.
Raw DDL Drizzle can't model — the search_index Swedish tsvector generated column and pg_trgm GIN index are appended by hand to that domain's migration.
Deliberate destructive reworks — 0016_carola_files_store.sql drops and recreates the chat-document tables (uuid → text ids) on purpose, with a comment noting the dev DB is reseeded and air-gapped deploys ship fresh, so there's no data to preserve.

Hand-authored or generated, every file still gets a snapshot + journal entry so the chain stays linear.

Extensions and Azure. Any extension a migration creates must be allow-listed for the managed database, or migrate fails. Azure Postgres only loads extensions named in azure.extensions (set in Bicep) — the app uses POSTGIS,VECTOR (and BTREE_GIST when Hatchet is deployed). A CREATE EXTENSION migration that isn't allow-listed is the classic silent break — drizzle hides the underlying error. Details on Deploy.

Writing queries

Queries are the typed builder: select / insert / update / delete, with where(and(eq(col, value), …)) from drizzle-orm. Columns and results are typed off the table, so a typo'd column or a wrong-typed value is a compile error, not a runtime one.

Owner-scope every read and write (§8)

The boundary rule: a query for user-owned data filters by ownerId in the WHERE, on both reads and writes — never "fetch then check in JS." example/src/lib/carola-threads-server.ts is the clean reference; every handler is owner-scoped. The list:

example/src/lib/carola-threads-server.ts

/** List the user's threads, newest first. With `scope` → just that scope (the home's
 *  Allmänt history bar); without → ALL conversations (the ChattN full history). */
export const listCarolaThreads = createServerFn({ method: "GET" })
	.inputValidator(z.object({ scope: z.enum(["general", "portfolio", "project"]).optional() }).parse)
	.handler(async ({ data }) => {
		const session = await requireSession();
		const scope = data.scope;
		const rows = await db
			.select({
				id: carolaThread.id,
				title: carolaThread.title,
				scope: carolaThread.scope,
				scopeKey: carolaThread.scopeKey,
				scopeLabel: carolaThread.scopeLabel,
				starred: carolaThread.starred,
				updatedAt: carolaThread.updatedAt,
			})
			.from(carolaThread)
			.where(
				scope
					? and(eq(carolaThread.ownerId, session.user.id), eq(carolaThread.scope, scope))
					: eq(carolaThread.ownerId, session.user.id),
			)
			.orderBy(desc(carolaThread.updatedAt))
			.limit(50);
		return {
			threads: rows.map((r) => ({
				id: r.id,
				title: r.title,
				scope: r.scope,
				scopeKey: r.scopeKey,
				scopeLabel: r.scopeLabel,
				starred: r.starred,
				updatedAt: r.updatedAt.toISOString(),
			})),
		};
	});

The same eq(carolaThread.ownerId, session.user.id) rides on the update and delete paths too, so a guessed id can't touch another user's row — the filter, not a prior read, is the guard. The upsert even pushes it into the conflict clause: