Database (Next.js) - GenerateSaaS Docs

Work with the @repo/database Drizzle + Postgres schema, migrations, and generated auth tables.

@repo/database is the single backend data layer: Drizzle ORM over Postgres, shared by the Hono API and every backend package. Frontends never touch it directly.

packages/database/src/index.ts validates DATABASE_URL with Zod at module load (throwing on a bad value), then exports the ready db client, the merged schema, per-table row types, and the affectedRowCount helper. The self-hosted postgres driver additionally exports pool (the underlying pg client); the neon and supabase drivers do not.

Choosing a provider

All three providers are Drizzle over Postgres, so your application code is identical no matter which you pick. The CLI sets the driver and writes DATABASE_URL for you during init; switching providers later means swapping that one connection string.

Provider	Hosting	Env var	When to use
`postgres`	Self-hosted	`DATABASE_URL`	You run your own Postgres (Docker, VPS, RDS) and want full control. Default key
`neon`	Managed	`DATABASE_URL`	Serverless Postgres that scales to zero - pairs well with serverless deploys
`supabase`	Managed	`DATABASE_URL`	Managed Postgres with the wider Supabase platform around it

Self-hosted PostgreSQL

The default. Any standard Postgres instance works - local Docker, a VPS, or managed Postgres like RDS.

Provider key: postgres
Env var: DATABASE_URL - init seeds postgres://postgres:postgres@localhost:5432/saas for the local Docker service.
You own provisioning, backups, and connection limits.

Neon

Managed serverless Postgres that scales to zero. A natural fit when the rest of the stack is serverless.

Provider key: neon
Env var: DATABASE_URL - your Neon connection string.

Supabase

Managed Postgres with the broader Supabase platform (dashboard, extensions) around it. The schema and queries here only use the Postgres database.

Provider key: supabase
Env var: DATABASE_URL - your Supabase connection string.

Because every option is plain Postgres, affectedRowCount(result) (below) is the only place driver differences surface - use it instead of result.rowCount so the same code runs on all three.

Usage

Import the client and tables anywhere on the backend:

import { db, schema, users } from "@repo/database";

In delete/update cleanup helpers, read the affected-row count with affectedRowCount(result), not result.rowCount (see the Callout under Choosing a provider).

Tables

The merged schema combines two files. Auth tables live in the generated db/auth.ts; application tables live in db/schema.ts.

Table	Purpose
`users`	Accounts + profile, role/ban state, and billing columns
`accounts`	OAuth/credential links per user
`verifications`	Email/token verification records
`passkeys`	WebAuthn passkey credentials
`organizations`	Tenants (multi-tenant mode) + their billing columns
`members`	User-to-organization membership and role
`invitations`	Pending org invites
`twoFactors`	TOTP secrets and backup codes
`apikeys`	Programmatic API keys with rate-limit metadata
`subscriptions`	Billing subscription state
`auditLogs`	Append-only actor/action/entity trail
`billingLogs`	Credit and money movement ledger
`ownedProducts`	One-time product ownership + quantity
`notifications`	Per-user in-app notifications
`announcements`	Broadcast messages with channel flags
`processedWebhookEvents`	Webhook idempotency record

Feature-flag-backed tables

Tables are always created; turning a flag off hides the feature and stops the reads - it does not drop the schema.

Table	Read only when
`apikeys`	`config.apiKeys.enabled` is `true` (default on)
`notifications`	`config.notifications.enabled` is `true` (default on)
`organizations` / `members` / `invitations`	`config.tenancy.multiTenant` is `true`

Exported row types

Most tables export an $inferSelect row type. Two have no exported type - subscriptions (read via Better Auth) and processedWebhookEvents.

User, Account, Verification, Passkey
Organization, Member, Invitation, TwoFactor
ApiKey
AuditLog, BillingLog, OwnedProductRow (note the Row suffix)
Notification, Announcement

Billing columns

users and organizations both carry billing columns mirroring billingAdditionalFields from @repo/config: credits (numeric, default "0"), plan, planStartedAt, planExpiresAt, autoTopUpEnabled, autoTopUpThreshold, autoTopUpAmount, and creditsLastGrantedAt.

Which entity holds the live balance follows config.tenancy.billingScope - "user" by default, "organization" when org-scoped. See Organizations and Credits.

Add or change a column

For application tables, edit the schema then generate and apply a migration.

Edit the table in packages/database/src/db/schema.ts.

Generate SQL: pnpm --filter @repo/database generate

Apply the migration: pnpm --filter @repo/database migrate

packages/database/src/db/auth.ts is generated by Better Auth - never edit it by hand. To add a column on an auth table, declare it in additionalFields inside packages/auth/src/config.ts, then run pnpm --filter @repo/database auth:generate. The CUSTOM-marked numeric credits columns and extra indexes are hand-applied tweaks - re-apply them after each regeneration.

Migrations and reset

Drizzle Kit reads both schema files and writes timestamp-prefixed migrations to packages/database/drizzle/. Run these from anywhere in the repo:

# Generate SQL from schema changes, then apply pending migrations
pnpm --filter @repo/database generate
pnpm --filter @repo/database migrate

# Local iteration: sync schema directly, no migration file
pnpm --filter @repo/database push

# DESTRUCTIVE: drop and recreate the public schema (wipes all data)
pnpm --filter @repo/database reset

reset destroys all data - it backs the demo-deploy flow. Never run it against a production database.

Frequently asked questions

Why is there no Subscription row type? The subscriptions table exists, but its rows are read through Better Auth's billing plugin rather than a raw Drizzle select, so no $inferSelect type is exported.

Why are credits columns numeric instead of integer? To support fractional credits. affectedRowCount and the numeric precision (16, 4) keep credit math consistent across engines and drivers.

push or generate + migrate? Use push for fast local iteration where migration history doesn't matter. Use generate + migrate for any shared or production database so changes are tracked and reproducible.

Database