Database

The template uses PostgreSQL with Drizzle ORM over a node-postgres connection pool. The auth and payment schemas are defined in code and pushed to your database with Drizzle Kit.

What it provides

A pooled PostgreSQL client (src/lib/db/index.ts).
Typed Drizzle schemas for auth and payments.
A local schema-push workflow and Drizzle Studio.

Important files

File	Role
`src/lib/db/index.ts`	`pg` `Pool` + Drizzle client (`db`)
`src/lib/db/schema/auth.ts`	`user`, `session`, `account`, `verification`
`src/lib/db/schema/payment.ts`	`subscription`, `payment`, and enums
`drizzle.config.ts`	Drizzle Kit config (schema dir, dialect, credentials)

The client

src/lib/db/index.ts

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export const db = drizzle(pool, { schema: { ...authSchema, ...paymentSchema } });

Import it anywhere with import { db } from "@/lib/db".

Schema overview

Auth (schema/auth.ts) — matches Better Auth's adapter expectations:

user — includes role (default "user"), emailVerified, and the admin-plugin banned / banReason / banExpires fields.
session — userId references user.id with onDelete: "cascade".
account — OAuth/credential accounts, also cascade-deleted with the user.
verification — email verification and reset tokens.

Payments (schema/payment.ts):

subscription — one row per user (userId is unique), with provider, status, and period fields. Indexed on userId and status.
payment — one row per order (orderNo is unique), indexed on userId and orderNo.
Enums: provider (zpay, creem, stripe, polar), plan_interval (one_time, month, year), subscription_status, and order_status.

Both payment tables reference user.id with cascade delete.

Setup

Set `DATABASE_URL`

Point it at a local or hosted PostgreSQL instance in .env.

Push the schema

pnpm exec drizzle-kit push

This applies the schemas in src/lib/db/schema directly to the database.

Inspect data (optional)

pnpm exec drizzle-kit studio

Adding a table or field

Add or edit a table in src/lib/db/schema/* (or a new file).
If it's a new file, make sure it's exported into db's schema in src/lib/db/index.ts.
Run pnpm exec drizzle-kit push.

Keep Better Auth's schema in sync. The user, session, account, and verification tables map to Better Auth's adapter. If you add an auth feature or plugin that expects new columns, update schema/auth.ts to match, then push again — otherwise auth queries will fail.

Local push vs. production migrations

drizzle-kit push is a local/development workflow: it diffs your schema against the database and applies changes directly, which can be destructive. This repository does not contain committed drizzle/ migration files, so there is no reviewed migration history to deploy. Do not treat a migrate step as ready — for production you need to generate and commit migrations (drizzle-kit generate) and run them through a deploy step before relying on them. Until that exists, treat schema changes as manual and back up first.

Production notes

Use a hosted PostgreSQL with connection pooling. The pg Pool keeps connections per instance; in serverless environments prefer a pooled connection string (e.g. a pgBouncer/transaction-pooler endpoint) to avoid exhausting connections.
Keep DATABASE_URL server-only; never expose it to the browser.
Establish a backup and migration process before launch.