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`:

```ts
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-specific `onConflict` 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, `niceId`s, 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):

```md
# <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:

```ts
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`.