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.

npm run db:push

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

Production deploy applies pending migrations automatically (see deployment).

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.