Database

PostgreSQL with Drizzle ORM. Enabled when DATABASE_URL is set.

DATABASE_URL=postgresql://user:password@host:port/database
DB_PREFIX=your_prefix  # optional, prefixes table names

Commands

bun run db:generate    # generate Drizzle schema from code
bun run db:migrate     # run migrations
bun run db:push        # push schema directly (dev shortcut)
bun run db:studio      # open Drizzle Studio (visual DB browser)
bun run db:reset       # drop + regenerate + migrate + push
bun run db:seed        # seed with test data

Schema

The main schema lives at src/server/db/schema.ts. It defines tables for users, accounts, sessions, posts, payments, teams, projects, API keys, and more.

Payload CMS has its own schema management — it handles migrations for its collections separately. Both share the same database.

Upgrading an existing database

Adding the `customer` table

customers is one row per paying customer per payment provider, written only by upsertCustomer in src/server/services/payment-service.ts from the Lemon Squeezy and Stripe webhook handlers. It is separate from payments, which records events, and from users, which records accounts on one deployment. email is the key that joins a customer across Shipkit deployments sharing a database; (provider, provider_customer_id) is the key that makes a replayed webhook idempotent.

status_at is when the event that set status happened. Providers redeliver and do not promise order, so without it a redelivered order_created arriving after order_refunded flips the customer back to paid. An incoming status is refused when its event is provably older than the stored one.

live_paid_at is the column that separates a sale from a test card. A test-mode order writes its row like any other, so status can read "paid" on an order no money reached; only a live payment sets live_paid_at. Ask customerRecordIsLiveBuyer from src/lib/payments/test-mode.ts rather than reading status yourself.

Fresh installs get it from db:push. An existing database needs this, with shipkit replaced by your DB_PREFIX:

CREATE TABLE "shipkit_customer" (
  "id" serial PRIMARY KEY NOT NULL,
  "email" varchar(255),
  "provider" varchar(50) NOT NULL,
  "provider_customer_id" varchar(255) NOT NULL,
  "product" text,
  "plan" text,
  "status" varchar(50),
  "status_at" timestamp with time zone,
  "first_paid_at" timestamp with time zone,
  "last_paid_at" timestamp with time zone,
  "live_paid_at" timestamp with time zone,
  "source_app" varchar(255),
  "created_at" timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
  "updated_at" timestamp with time zone
);
CREATE UNIQUE INDEX "customer_provider_customer_id_idx"
  ON "shipkit_customer" USING btree ("provider","provider_customer_id");
CREATE INDEX "customer_email_idx" ON "shipkit_customer" USING btree ("email");

Reversing it is DROP TABLE "shipkit_customer";. Nothing reads the table yet, so dropping it loses recorded customers and breaks nothing else.

If you created this table before live_paid_at and status_at existed, add them:

ALTER TABLE "shipkit_customer"
  ADD COLUMN "live_paid_at" timestamp with time zone,
  ADD COLUMN "status_at" timestamp with time zone;

Additive, no defaults, no backfill, no index, one statement so it is one transaction. Reversing it is the matching DROP COLUMN pair. Run it before deploying the code that writes these columns: until they exist every upsert fails with 42703, which upsertCustomer turns into a null return and an error-level log, so the symptom is an empty customer table rather than a failed webhook.

The unique index is load-bearing. Without it, the ON CONFLICT (provider, provider_customer_id) clause in upsertCustomer has no arbiter, so Postgres rejects the statement outright with 42P10 rather than inserting anything. That error is absorbed into the function's null return, so the symptom is an empty customer table and a logged query failure on every webhook, not duplicate rows.

Clearing the `users.email_verified` default

users.email_verified no longer defaults to CURRENT_TIMESTAMP. It defaults to NULL, so a row that never says anything about verification reads as unverified. This matters because isAdmin() grants admin on an ADMIN_EMAIL / ADMIN_DOMAINS match only for an account whose email is verified; under the old default, any insert that omitted the column produced an account that satisfied that check.

Fresh installs get the new column from db:push. A database created before this change keeps the old default until you run bun run db:push, which emits:

ALTER TABLE "your_prefix_user" ALTER COLUMN "email_verified" DROP DEFAULT;

Existing rows are untouched. If some were created by an insert that omitted the column, they are marked verified without anyone having proved ownership, and clearing them is a judgment call:

-- Review before running. This unverifies every account that has no sign-in on record.
SELECT id, email, email_verified FROM "your_prefix_user" WHERE email_verified IS NOT NULL;

Upgrading an existing database: `temporary_link.used_at`

Temporary links are single-use. The first successful read stamps used_at, and every read after that returns nothing, so a link that is polled no longer renews itself. That needs one nullable column:

ALTER TABLE "your_prefix_temporary_link" ADD COLUMN "used_at" timestamp with time zone;

bun run db:push emits it for you. Run it before deploying the code: the service reads and writes used_at on every link read, so the new build errors against a database that lacks the column. The column is nullable with no default, so links created before the upgrade are treated as unused and work once more.

Usage

import { db } from "@/server/db";
import { users } from "@/server/db/schema";
import { eq } from "drizzle-orm";

const user = await db.query.users.findFirst({
  where: eq(users.email, "user@example.com"),
});

Hosting

Neon — serverless Postgres, good free tier
Supabase — Postgres + extras
Railway — simple managed Postgres

Key Files

File	Purpose
`src/server/db/index.ts`	Database connection
`src/server/db/schema.ts`	Drizzle schema definitions
`drizzle.config.ts`	Drizzle Kit config
`src/config/features-config.ts`	`DATABASE_ENABLED` flag