Sendoka
Dashboard

Database migrations

Sendoka uses Drizzle ORM over the Neon HTTP driver. Migrations are SQL files in src/lib/db/migrations/, generated from TypeScript schema in src/lib/db/schema/.

The two workflows

Dev — db:push

Fastest iteration. Syncs schema directly to DB, no SQL file.

ALLOW_DB_PUSH=1 npm run db:push

db:push is gated behind scripts/guard-db-push.mjs: without ALLOW_DB_PUSH=1 in the environment it refuses with an explanation and exits 1 before drizzle-kit ever runs. That's deliberate — DATABASE_URL points at a database shared with production (see the warning below), so a bare push must be an explicit choice, made only when the URL points at a database that's safe to rewrite (e.g. a Neon branch).

Never commit the result. No file is produced. Use this while you're still shaping the column; once you're happy, generate a proper migration.

Prod — db:generate + db:migrate

Creates a SQL migration file. This is what goes to production.

# 1. Edit src/lib/db/schema/*.ts
# 2. Generate the SQL migration from the diff:
npm run db:generate

# 3. Review the file in src/lib/db/migrations/
# 4. Commit both schema + migration
git add src/lib/db/schema src/lib/db/migrations
git commit -m "feat: add foo column to tenants"

# 5. Apply in dev (or remote):
npm run db:migrate

Migrations are not applied automatically on deploy — the Vercel build is just next build. Apply pending migrations to a remote / production database manually:

DATABASE_URL=<target> npm run db:migrate

⚠️ Never run db:push against a shared or production database. It rewrites the live schema without a migration record, so the journal and the actual schema silently desync — db:migrate then skips the "already applied" change, and inserts that expect the new columns start failing with column "…" does not exist. This is why npm run db:push runs through scripts/guard-db-push.mjs, which refuses and exits 1 unless ALLOW_DB_PUSH=1 is set — the override is an acknowledgment that DATABASE_URL points somewhere disposable, not a routine flag.

Deploy ordering — apply before, or with, the deploy

Migrations are not applied by the build, so the deploy and db:migrate are two steps and the order matters: a build that references a column its database has not got 500s on every request that touches it, which is how 0049 froze every registration transition for hours with nothing but a log line to say so.

Two things watch for it, neither of which is an alarm you should be relying on:

  • /api/cron/probe-health records schema_drift in its response and a cron.probe_health.schema_drift warn line. It deliberately keeps drift out of the status it stores, because that column feeds the public status page.
  • /api/health reports checks.schema.state: "drifted" and a schema_drift entry in warnings[]. It does not go 503 for it, for the same reason: on this shared dev/prod instance a deploy ahead of the ledger is routine, and a monitor that pages for something an operator cannot fix from the alert gets muted — taking the outages it was built for with it. If you want to be woken by drift, alert on that warning deliberately.

So: apply the pending migrations in the same window as the deploy, and check /api/health afterwards for warnings: [].

File naming

Drizzle generates names like 0042_reflective_fury.sql. Four-digit sequence + two random words. Don't rename — the meta files track them.

Writing schema

Each table lives in its own file under src/lib/db/schema/. Export from index.ts.

// src/lib/db/schema/widgets.ts
import { pgTable, varchar, timestamp, jsonb } from "drizzle-orm/pg-core";
import { orgs } from "./orgs";
import { tenants } from "./tenants";

export const widgets = pgTable("widgets", {
  id: varchar("id", { length: 32 }).primaryKey(),
  orgId: varchar("org_id", { length: 32 }).notNull().references(() => orgs.id),
  tenantId: varchar("tenant_id", { length: 32 }).references(() => tenants.id), // nullable → platform mode ready
  name: varchar("name", { length: 128 }).notNull(),
  config: jsonb("config").$type<WidgetConfig>().default({}).notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
});

Then:

// src/lib/db/schema/index.ts
export * from "./widgets";

Invariants — don't break these

1. IDs are {prefix}_{nanoid(24)}

Primary keys are string (32 chars, counting prefix_). Never use serial/bigserial. Generate with generateId("prefix") from src/lib/utils/id.ts.

2. created_at uses withTimezone: true

Pagination cursors rely on (created_at, id) tuples. Timezone-aware timestamps make the ordering unambiguous.

3. Multi-statement migrations need sql.transaction([...])

The Neon HTTP driver does not support db.transaction(async (tx) => ...). If your migration inserts seed data across multiple tables, use:

import { sql } from "@/lib/db";
await sql.transaction([
  sql`INSERT INTO orgs ... VALUES ...`,
  sql`INSERT INTO memberships ... VALUES ...`,
]);

4. Platform-mode tables carry nullable tenantId

New tables that hold per-org data should have tenantId as nullable FK to tenants.id. Even if you don't use platform mode yet — adding it later is painful.

Tables currently carrying tenantId: api_keys, domains, webhook_endpoints, messages, suppressions, templates, audiences, contacts, inbound_messages, messaging_pools, webhook_deliveries, usage_counters.

5. Index anything you filter by in list endpoints

Cursor pagination orders by (created_at DESC, id DESC). Add a composite index:

import { index } from "drizzle-orm/pg-core";
// ...
}, (t) => ({
  orgCreatedIdx: index("widgets_org_created_idx").on(t.orgId, t.createdAt, t.id),
}));

Reviewing generated SQL

After npm run db:generate, open the new file and scan for:

  • DROP — any unexpected drops. Might mean you renamed a column instead of adding one. Abort and fix.
  • NOT NULL on existing table — you need a default, or the migration fails on existing rows.
  • Foreign key on large table — adding a FK locks the table for validation. Use NOT VALID + VALIDATE CONSTRAINT for big tables.

Example safe NOT NULL add:

ALTER TABLE messages ADD COLUMN foo text;
UPDATE messages SET foo = 'default' WHERE foo IS NULL;
ALTER TABLE messages ALTER COLUMN foo SET NOT NULL;

If Drizzle generated one atomic ALTER TABLE ... ADD COLUMN foo text NOT NULL DEFAULT 'default', that's fine for small tables but locks large ones. Rewrite by hand if the table is big.

Backfilling data

Standalone scripts under scripts/backfill/. Example:

// scripts/backfill/2026-04-22-tenant-ids.ts
import { db } from "@/lib/db";
import { messages } from "@/lib/db/schema";
import { eq, isNull } from "drizzle-orm";

for (let offset = 0; ; offset += 1000) {
  const batch = await db
    .select()
    .from(messages)
    .where(isNull(messages.tenantId))
    .limit(1000);
  if (batch.length === 0) break;
  // ... compute + update
}

Run with npx tsx scripts/backfill/foo.ts. Keep them in the repo for audit even after they're one-shot.

Rolling back

Drizzle doesn't generate down-migrations. If something is broken:

  1. Revert the schema change in TS.
  2. Generate a new migration (forward fix, not rollback).
  3. Apply it.

Reason: in a team with multiple environments, a "down" migration would desync the drizzle_migrations metadata table. Forward-only is simpler and matches Neon's branching model (use branch snapshots if you really need a point-in-time rollback).

Neon branching for safety

For risky migrations (data backfill, non-trivial schema change), use Neon branches:

# 1. Create a branch from main
neon branches create --name migrate-test

# 2. Point your .env.local at the branch URL
DATABASE_URL=postgresql://.../migrate-test?...

# 3. Run the migration + backfill against the branch
npm run db:migrate
npx tsx scripts/backfill/foo.ts

# 4. Verify queries, then run the same on main and delete the branch

CI check

GitHub Actions (.github/workflows/ci.yml) runs tsc --noEmit which catches column rename mismatches between schema and queries. A migration file that references a column you've since renamed in schema will fail CI.

Common pitfalls

  • db.transaction(async (tx) => ...) — doesn't work on Neon HTTP. Use sql.transaction([...]) or run each statement independently.
  • Forgetting to export from schema/index.ts — Drizzle won't diff against it, no SQL generated.
  • Editing an already-merged migration file — breaks checksums for deployed environments. Always forward-fix with a new file.
  • Running db:push on prod — never. It doesn't produce a migration record and a subsequent db:migrate might try to re-apply the same change and fail. The guard script blocks it by default; don't set ALLOW_DB_PUSH=1 to "make the error go away" without checking what DATABASE_URL points at.