pangolin/docs/database.md

Database

Pangolin runs on SQLite or Postgres through Drizzle. The same code runs against either. server/db/index.ts (generated by npm run set:sqlite / npm run set:pg, gitignored) re-exports ./sqlite or ./pg, so application code always imports from @server/db:

import { db, resources, siteResources } from "@server/db";

Exports you'll use: db (may read from replicas), primaryDb (writer), logsDb (audit/log tables, possibly a separate database), safeRead, Transaction (type for db.transaction callbacks), driver, and every table.

Schema files (keep them in sync)

	Postgres	SQLite
Main tables	server/db/pg/schema/schema.ts	server/db/sqlite/schema/schema.ts
Enterprise-only tables	server/db/pg/schema/privateSchema.ts	server/db/sqlite/schema/privateSchema.ts

Every table and column change goes in both dialects, with the same table name, column names, nullability, defaults and foreign keys. The TypeScript row types have to match too, because application code is shared.

Portable types only

The schemas have to stay equivalent across both engines, so don't use Postgres-specific features.

Need	Postgres	SQLite
String	varchar("x") / text("x")	text("x")
Integer	integer("x")	integer("x")
Big int / epoch ms	bigint("x", { mode: "number" })	integer("x")
Auto id	serial("x").primaryKey()	integer("x").primaryKey({ autoIncrement: true })
Boolean	boolean("x")	integer("x", { mode: "boolean" })
Float	real("x")	real("x")
Timestamp	epoch number (bigint mode number, add a comment saying seconds or ms) or ISO string in varchar (e.g. dateCreated)	integer / text to match
JSON	text("x") + JSON.stringify/JSON.parse	text("x")
Enum	varchar("x").$type<"a" | "b">() (TS-only)	text("x").$type<"a" | "b">()
UUID	varchar("x", { length: 36 }).$defaultFn(() => randomUUID())	text("x").$defaultFn(() => randomUUID())

Don't use: timestamp, date, interval, json, jsonb, pgEnum, .array(), uuid columns, defaultNow(), Postgres-only SQL functions in sql\`defaults, generated columns, or DB-specificonConflict tricks that SQLite doesn't support. Generate times in application code (Date.now(), new Date().toISOString(), Math.floor(Date.now() / 1000)`) and match whatever the neighbouring columns use.

Look at a similar existing column in resources / siteResources and copy its pattern across both files.

Migrations

The versioned upgrade scripts live in server/setup/scriptsPg/<version>.ts and server/setup/scriptsSqlite/<version>.ts, registered in migrationsPg.ts / migrationsSqlite.ts. server/migrations/ is drizzle-kit output and is gitignored. The maintainers generate all of these for each release, using drizzle for the schema diff.

Never write or edit migration scripts or SQL yourself (unless the user explicitly asks). Change the schema files, then write migration notes instead.

Migration notes

Notes go in server/setup/migrationNotes/<version>.md, where <version> is the next release that hasn't been tagged yet:

1. Find the latest tag: git tag --sort=-v:refname | head -1 (e.g. 1.26.0).
2. If server/setup/migrationNotes/ already has a file for a version newer than that tag, that's the upcoming release: update that file. Add your section and don't create a second file.
3. Otherwise create a new one for the next minor version (1.26.0 → 1.27.0.md), and tell the user which version you picked so they can rename it if the release is a patch.

The notes are for things drizzle's schema diff can't do by itself. Leave out plain column or table additions with static defaults (drizzle generates those), unless they're needed for context. Write down:

• Data copies: values to move or transform from an old column or table into a new one, and in what order (e.g. "copy resources.sso into resourcePolicies.sso before dropping the column").
• Generated values: existing rows that need values computed in code, such as UUIDs, niceIds, secrets, a default derived from other columns, or a new join-table row per existing entity.
• Renames: drizzle treats a rename as drop + add and loses data, so name the old and new column/table.
• Encryption changes: existing plaintext that now has to be encrypted with the server secret (or the reverse).
• NOT NULL columns without a static default on existing tables: say how to backfill them.
• Config file changes: renamed or moved keys in config.yml / privateConfig.yml that the script should rewrite.
• Anything that differs between SQLite and Postgres.

Template (one section per feature, appended to the version's file):

# <version> migration notes

## <Feature name> (<short description / PR>)

Schema: <tables/columns added, changed, removed; both pg and sqlite>

Steps the migration script must do in code:
1. <e.g. For each row in `siteResources`, generate `niceId` with `getUniqueSiteResourceName(orgId)`.>
2. <e.g. Copy `resources.oldCol` -> `resources.newCol`, then drop `oldCol`.>

Notes: <ordering constraints, SQLite vs Postgres differences, config changes>

Encrypting sensitive data

Any secret Pangolin needs to read back later (OIDC client secrets, provider API keys, target auth tokens, DNS provider credentials, etc.) must be encrypted at rest with the server secret:

import { encrypt, decrypt } from "@server/lib/crypto";
import config from "@server/lib/config";

const key = config.getRawConfig().server.secret!;
const stored = encrypt(plaintext, key);      // write this to the DB
const plaintext2 = decrypt(stored, key);     // when you need it again

Existing examples: server/routers/idp/createOidcIdp.ts, server/routers/target/createTarget.ts, server/routers/aiProvider/types.ts.

• Don't return decrypted secrets from list/get endpoints unless the feature needs it. Prefer write-only fields.
• Values that only need verifying (passwords, pincodes, tokens) are hashed, not encrypted: hashPassword / verifyPassword from @server/auth/password.