Database

Prolifto uses PostgreSQL 17 with Drizzle ORM. The schema lives in packages/db/src/schemas; the client, queries, and migrations live alongside it.

Layout

packages/db/
  src/
    index.ts          # database client
    schemas/          # table definitions
    queries/          # reusable queries
  migrations/         # committed SQL migrations
  drizzle.config.ts   # Drizzle Kit configuration
  .env                # DATABASE_URL for Drizzle commands

Commands

Command	What it does
`pnpm db:dev:up`	Start the development PostgreSQL container
`pnpm db:generate`	Generate a migration from schema changes
`pnpm db:migrate`	Apply committed migrations
`pnpm db:push`	Push the schema directly (local only; never production)
`pnpm db:studio`	Open Drizzle Studio
`pnpm db:test:up`	Start the test database
`pnpm db:test:reset`	Recreate the test database
`pnpm db:backup`	Back up the development database
`pnpm db:restore`	Restore a backup

Drizzle commands run from packages/db and read packages/db/.env.

Migration workflow

Edit the schema in packages/db/src/schemas.
Run pnpm db:generate and give the migration a descriptive name.
Review the generated SQL in packages/db/migrations.
Apply it locally with pnpm db:migrate.
Run the app and the tests.
Commit the schema change and the migration together.

Rules:

Never edit a migration that has already been applied outside your branch.
Do not use db:push for changes that need a migration history.
Never run db:push against production.
Keep tenant tables indexed on organizationId and the columns you filter by.

Production migrations

Production commands read DATABASE_URL from Vercel and are guarded:

pnpm db:prod:check     # read-only comparison
pnpm db:prod:backup    # verified backup first
pnpm db:prod:migrate   # prompts for confirmation

db:prod:migrate requires typing MIGRATE. Use --dry-run to inspect the command or --yes for a deliberate non-interactive run. Always run db:prod:check and db:prod:backup before migrating, then check again after.

See Backups and restore.

Multi-tenant query rules

Every tenant-owned table has an organizationId. Queries must filter by the organization resolved from the request context.

const products = await db
  .select()
  .from(productsTable)
  .where(
    and(
      eq(productsTable.organizationId, context.organization.id),
      eq(productsTable.deleted, false)
    )
  );

Never trust an organization ID from user input. Resolve it from the session or API key in the server context, then scope the query.

Soft deletes

Most business tables use deleted and deletedAt rather than immediate deletion. List queries exclude deleted rows by default; archive views query them explicitly. Permanent deletion is a separate, permissioned operation.

Transactions

Use transactions for multi-step writes that must stay consistent:

Creating an order (order, items, coupon usage, invoice, payment).
Confirming payment (payment, order, invoice, transaction, receipt).
Inventory adjustments (variant stock and movement record).
Returns (return record, stock restoration, refund state).

Keep transactions short and avoid external network calls inside them.

Tests and the test database

Integration tests use a separate database on port 5433:

pnpm db:test:up
pnpm test

Set TEST_DATABASE_URL if you use a different connection string. Tests should clean up or isolate the data they create, and should not depend on seed data.

Backups

Local development backups are compressed pg_dump archives in backups/, git-ignored and retained for 14 days by default. See Backups and restore for the full workflow, including production.

On this page