Authoring the schema and shipping the init migration

This is the lesson where the invoicing data model stops being a sketch and becomes real database structure: six tables, shaped so every tenant-owned row is scoped to its organization, money and timestamps carry the right column types, and the database itself enforces the domain through enums, uniqueness constraints, and a non-negative check. You will author the schema and its relations, generate one migration, read the SQL it emits, and apply it to the empty Postgres your Docker container is already running.

When you are done, pnpm db:migrate will have created organizations, users, org_members, customers, invoices, and invoice_lines on a fresh database in a single reviewed step. Open Studio and every foreign key, every tenant-scoped unique, the total >= 0 check, and the three indexes will be sitting there exactly as you declared them. Run pnpm db:generate a second time and it reports no changes — the snapshot Drizzle keeps and your schema file agree, which is the signal that the source of truth and the database are in sync. The inspector still shows empty banners; you are building the structure now, and the data that fills it comes in the next lesson.

Your mission

From this point on, the schema is the source of truth for the entire project. Every row type, every query you write in the lessons that follow, and everything built on this data later in the course — CRUD, auth, billing — derives from db/schema.ts. That has one immediate consequence worth internalizing: you never hand-write a row type. You declare the table, and the row shape comes back from $inferSelect, so the types can never drift from the columns. There is also a structural rule that outlives this project and that you should treat as non-negotiable from here on: every tenant-owned table carries organizationId as a NOT NULL foreign key. The single exception is users, which is global across organizations and reached through the org_members junction table rather than owned by any one tenant.

The work is to author the six tables and their relations, then run the generate-read-migrate loop against the empty database. The starter hands you the tooling you build on, not the schema itself: drizzle.config.ts points Drizzle Kit at the unpooled URL, the schema path, and snake-case casing; src/db/index.ts exports the db client and a dbUnpooled alias with the schema and relations already wired; and the db:generate / db:migrate / db:studio scripts wrap their tools through dotenv-cli because Drizzle Kit and tsx do not read .env on their own. Those are settled. Your job is the contents of db/schema.ts and db/relations.ts, and the migration they produce.

A handful of decisions shape the schema, each one carried in from the chapters on Drizzle. Primary keys are UUIDv7, generated by Postgres through default(sql`uuidv7()`), because they sort by creation time yet stay unguessable. Every timestamp is timestamp({ withTimezone: true }) and every money column is numeric({ precision: 12, scale: 2 }) — never a float for currency. The memberRole and invoiceStatus values are named pgEnums so the database, not just the application, refuses an invalid status. Foreign-key ON DELETE is a decision you make per edge rather than a default you copy across the board: an owned child that loses all meaning without its parent cascades, while a referenced entity the schema cannot make sense of without — a customer, an invoice author — restricts, so it can never vanish out from under the rows pointing at it. And every uniqueness scope and every index is tenant-aware: an invoice number is unique within an organization, not globally, and the composite index column order matches the list query’s where plus orderBy direction so the planner can actually use it.

Two things are out of scope. You are not writing the reads — the cursor-paginated list and the detail load come in the next two lessons — and you are not seeding any data, so the inspector’s banners stay at zero after you finish here. Two traps to steer clear of, both habits that feel faster and cost you later. The first is reaching for drizzle-kit push because the database is empty and pushing the schema straight in seems harmless; the generate-and-commit loop is the muscle this course is building, and push stays the prototype-only escape hatch you met in the migrations chapter. The second is migrating without reading the emitted SQL first — the whole point of generating a file you can review is that you review it before it touches a database.

pnpm db:migrate runs cleanly on the empty database and leaves exactly one row in __drizzle_migrations.

tested

After migrating, all six tables exist with their foreign keys (each carrying its intended ON DELETE), tenant-scoped uniques, the total >= 0 check, and the three named indexes with their DESC ordering.

tested

A second pnpm db:generate immediately after reports no changes — the snapshot is in sync with the schema.

untested

The two invoices → users edges resolve distinctly, so a nested read of an invoice’s author and its organization’s members does not cross-wire membership with authorship.

untested

The exported row types are derived from the schema, so a query that selects an invoice returns the typed shape queries.ts and the inspector consume — nothing downstream hand-types a row.

untested

Coding time

Author db/schema.ts and db/relations.ts against the reference signatures from the project overview and the Lesson 3 tests. Then run pnpm db:generate --name init_schema, open the 0000_init_schema.sql it writes, read it end to end, and only then run pnpm db:migrate. Try the whole loop yourself before opening the walkthrough — the reading-the-SQL step in particular is the habit, and it only sticks if you do it once unaided.

Reference solution and walkthrough

src/db/schema.ts

The file is built in the order the foreign keys demand: a table can only reference another that already exists, so organizations and users come first, then the junction and the children that point back at them. The two pgEnums sit at the top because the columns that use them need them in scope.

Start with the enums and the two root tables. The named enums ('member_role', 'invoice_status') are what let the database reject an out-of-domain value; the column types here — uuid keyed by uuidv7(), text for names and slugs — are the vocabulary from the schema-authoring chapter, named at the call site rather than re-explained. Note the ...timestamps spread: that shared group from db/columns.ts adds the createdAt column to every table from one definition, so the cursor’s sort key is identical everywhere it appears.

src/db/schema.ts

import { sql } from 'drizzle-orm';
import {
  check,
  index,
  integer,
  numeric,
  pgEnum,
  pgTable,
  primaryKey,
  text,
  timestamp,
  unique,
  uuid,
} from 'drizzle-orm/pg-core';

import { timestamps } from '@/db/columns';

export const memberRole = pgEnum('member_role', ['owner', 'admin', 'member']);

export const invoiceStatus = pgEnum('invoice_status', [
  'draft',
  'sent',
  'paid',
  'overdue',
]);

export const organizations = pgTable('organizations', {
  id: uuid().primaryKey().default(sql`uuidv7()`),
  name: text().notNull(),
  slug: text().notNull().unique('organizations_slug_unique'),
  ...timestamps,
});

export type Organization = typeof organizations.$inferSelect;
export type NewOrganization = typeof organizations.$inferInsert;

export const users = pgTable('users', {
  id: uuid().primaryKey().default(sql`uuidv7()`),
  email: text().notNull().unique('users_email_unique'),
  name: text().notNull(),
  ...timestamps,
});

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

users is a deliberate stub. The authentication chapters later in the course own the real users table and its companions through the Better Auth Drizzle adapter; here you give it just enough — an id, a unique email, a name — so org_members.user_id and invoices.created_by have something to point at. When that work lands it drops this stub for the generated table, and because the foreign-key targets stay the same, that migration is additive rather than destructive.

Each table is immediately followed by its $inferSelect and $inferInsert type exports. These are the canonical row types for the whole project: Organization is “a row as it comes back from a select”, NewOrganization is “the shape you pass to an insert”. This is the mechanism behind requirement 5 — nothing downstream hand-types a row, because the moment you change a column the types that flow from these exports change with it, and any caller that no longer matches stops compiling.

Now the junction table. org_members is the many-to-many between organizations and users, and it has no surrogate id — its identity is the pair of foreign keys, declared as a composite primary key in the table’s second-argument callback. Both edges cascade: a membership is meaningless once either its organization or its user is gone, so deleting either parent should take the membership row with it.

src/db/schema.ts (continued)

export const orgMembers = pgTable(
  'org_members',
  {
    organizationId: uuid()
      .notNull()
      .references(() => organizations.id, { onDelete: 'cascade' }),
    userId: uuid()
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    role: memberRole().notNull(),
    ...timestamps,
  },
  (t) => [primaryKey({ columns: [t.organizationId, t.userId] })],
);

export type OrgMember = typeof orgMembers.$inferSelect;
export type NewOrgMember = typeof orgMembers.$inferInsert;

customers is the first table to carry the structural rule directly: organizationId is a NOT NULL foreign key, and it cascades because a customer belongs to exactly one organization and has no reason to exist after that organization is deleted. The uniqueness constraint is where tenant-awareness shows up for the first time — a customer email is unique within an organization, not across the whole table, so the constraint covers (organizationId, email) rather than email alone. Two different tenants can each have a billing@acme.test; the same tenant cannot.

src/db/schema.ts (continued)

export const customers = pgTable(
  'customers',
  {
    id: uuid().primaryKey().default(sql`uuidv7()`),
    organizationId: uuid()
      .notNull()
      .references(() => organizations.id, { onDelete: 'cascade' }),
    name: text().notNull(),
    email: text().notNull(),
    ...timestamps,
  },
  (t) => [unique('customers_org_email_unique').on(t.organizationId, t.email)],
);

export type Customer = typeof customers.$inferSelect;
export type NewCustomer = typeof customers.$inferInsert;

invoices is the richest table in the schema and the one to slow down on. It carries all three flavors of foreign key, the enum-backed status, two money columns, the tenant-scoped unique, the check constraint, and the three indexes — so it is worth stepping through part by part. The block below is src/db/schema.ts (continued), verbatim.

export const invoices = pgTable(
  'invoices',
  {
    id: uuid().primaryKey().default(sql`uuidv7()`),
    organizationId: uuid()
      .notNull()
      .references(() => organizations.id, { onDelete: 'cascade' }),
    customerId: uuid()
      .notNull()
      .references(() => customers.id, { onDelete: 'restrict' }),
    createdBy: uuid()
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),
    number: text().notNull(),
    status: invoiceStatus().notNull().default('draft'),
    total: numeric({ precision: 12, scale: 2 }).notNull(),
    currency: text().notNull().default('USD'),
    issuedAt: timestamp({ withTimezone: true }).notNull(),
    dueAt: timestamp({ withTimezone: true }).notNull(),
    ...timestamps,
  },
  (t) => [
    unique('invoices_org_number_unique').on(t.organizationId, t.number),
    check('invoices_total_nonneg', sql`${t.total} >= 0`),
    index('idx_invoices_org_status_created_at_id').on(
      t.organizationId,
      t.status,
      t.createdAt.desc(),
      t.id.desc(),
    ),
    index('idx_invoices_org_created_at_id').on(
      t.organizationId,
      t.createdAt.desc(),
      t.id.desc(),
    ),
    index('idx_invoices_customer_id').on(t.customerId),
  ],
);

export type Invoice = typeof invoices.$inferSelect;
export type NewInvoice = typeof invoices.$inferInsert;

The three .references() edges, each with its own onDelete: organizationId cascades, customerId and createdBy restrict. The org owns the invoice, but the customer and the author are entities the invoice merely points at — deleting a customer who still has invoices is refused outright, because a dangling customer is corruption, not convenience.

export const invoices = pgTable(
  'invoices',
  {
    id: uuid().primaryKey().default(sql`uuidv7()`),
    organizationId: uuid()
      .notNull()
      .references(() => organizations.id, { onDelete: 'cascade' }),
    customerId: uuid()
      .notNull()
      .references(() => customers.id, { onDelete: 'restrict' }),
    createdBy: uuid()
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),
    number: text().notNull(),
    status: invoiceStatus().notNull().default('draft'),
    total: numeric({ precision: 12, scale: 2 }).notNull(),
    currency: text().notNull().default('USD'),
    issuedAt: timestamp({ withTimezone: true }).notNull(),
    dueAt: timestamp({ withTimezone: true }).notNull(),
    ...timestamps,
  },
  (t) => [
    unique('invoices_org_number_unique').on(t.organizationId, t.number),
    check('invoices_total_nonneg', sql`${t.total} >= 0`),
    index('idx_invoices_org_status_created_at_id').on(
      t.organizationId,
      t.status,
      t.createdAt.desc(),
      t.id.desc(),
    ),
    index('idx_invoices_org_created_at_id').on(
      t.organizationId,
      t.createdAt.desc(),
      t.id.desc(),
    ),
    index('idx_invoices_customer_id').on(t.customerId),
  ],
);

export type Invoice = typeof invoices.$inferSelect;
export type NewInvoice = typeof invoices.$inferInsert;

The status column. The enum makes the database reject any status outside the four the domain allows, and a new invoice starts life as a 'draft'.

export const invoices = pgTable(
  'invoices',
  {
    id: uuid().primaryKey().default(sql`uuidv7()`),
    organizationId: uuid()
      .notNull()
      .references(() => organizations.id, { onDelete: 'cascade' }),
    customerId: uuid()
      .notNull()
      .references(() => customers.id, { onDelete: 'restrict' }),
    createdBy: uuid()
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),
    number: text().notNull(),
    status: invoiceStatus().notNull().default('draft'),
    total: numeric({ precision: 12, scale: 2 }).notNull(),
    currency: text().notNull().default('USD'),
    issuedAt: timestamp({ withTimezone: true }).notNull(),
    dueAt: timestamp({ withTimezone: true }).notNull(),
    ...timestamps,
  },
  (t) => [
    unique('invoices_org_number_unique').on(t.organizationId, t.number),
    check('invoices_total_nonneg', sql`${t.total} >= 0`),
    index('idx_invoices_org_status_created_at_id').on(
      t.organizationId,
      t.status,
      t.createdAt.desc(),
      t.id.desc(),
    ),
    index('idx_invoices_org_created_at_id').on(
      t.organizationId,
      t.createdAt.desc(),
      t.id.desc(),
    ),
    index('idx_invoices_customer_id').on(t.customerId),
  ],
);

export type Invoice = typeof invoices.$inferSelect;
export type NewInvoice = typeof invoices.$inferInsert;

The money columns: numeric({ precision: 12, scale: 2 }) for total and a 'USD' default for currency. Fixed-precision decimal, never a float, so cents never drift.

export const invoices = pgTable(
  'invoices',
  {
    id: uuid().primaryKey().default(sql`uuidv7()`),
    organizationId: uuid()
      .notNull()
      .references(() => organizations.id, { onDelete: 'cascade' }),
    customerId: uuid()
      .notNull()
      .references(() => customers.id, { onDelete: 'restrict' }),
    createdBy: uuid()
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),
    number: text().notNull(),
    status: invoiceStatus().notNull().default('draft'),
    total: numeric({ precision: 12, scale: 2 }).notNull(),
    currency: text().notNull().default('USD'),
    issuedAt: timestamp({ withTimezone: true }).notNull(),
    dueAt: timestamp({ withTimezone: true }).notNull(),
    ...timestamps,
  },
  (t) => [
    unique('invoices_org_number_unique').on(t.organizationId, t.number),
    check('invoices_total_nonneg', sql`${t.total} >= 0`),
    index('idx_invoices_org_status_created_at_id').on(
      t.organizationId,
      t.status,
      t.createdAt.desc(),
      t.id.desc(),
    ),
    index('idx_invoices_org_created_at_id').on(
      t.organizationId,
      t.createdAt.desc(),
      t.id.desc(),
    ),
    index('idx_invoices_customer_id').on(t.customerId),
  ],
);

export type Invoice = typeof invoices.$inferSelect;
export type NewInvoice = typeof invoices.$inferInsert;

unique('invoices_org_number_unique') scopes the invoice number to (organizationId, number) — unique per tenant, the same tenant-scoping pattern as customers.

export const invoices = pgTable(
  'invoices',
  {
    id: uuid().primaryKey().default(sql`uuidv7()`),
    organizationId: uuid()
      .notNull()
      .references(() => organizations.id, { onDelete: 'cascade' }),
    customerId: uuid()
      .notNull()
      .references(() => customers.id, { onDelete: 'restrict' }),
    createdBy: uuid()
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),
    number: text().notNull(),
    status: invoiceStatus().notNull().default('draft'),
    total: numeric({ precision: 12, scale: 2 }).notNull(),
    currency: text().notNull().default('USD'),
    issuedAt: timestamp({ withTimezone: true }).notNull(),
    dueAt: timestamp({ withTimezone: true }).notNull(),
    ...timestamps,
  },
  (t) => [
    unique('invoices_org_number_unique').on(t.organizationId, t.number),
    check('invoices_total_nonneg', sql`${t.total} >= 0`),
    index('idx_invoices_org_status_created_at_id').on(
      t.organizationId,
      t.status,
      t.createdAt.desc(),
      t.id.desc(),
    ),
    index('idx_invoices_org_created_at_id').on(
      t.organizationId,
      t.createdAt.desc(),
      t.id.desc(),
    ),
    index('idx_invoices_customer_id').on(t.customerId),
  ],
);

export type Invoice = typeof invoices.$inferSelect;
export type NewInvoice = typeof invoices.$inferInsert;

The check constraint refuses a negative total at the database level, so the invariant holds no matter which code path writes the row.

export const invoices = pgTable(
  'invoices',
  {
    id: uuid().primaryKey().default(sql`uuidv7()`),
    organizationId: uuid()
      .notNull()
      .references(() => organizations.id, { onDelete: 'cascade' }),
    customerId: uuid()
      .notNull()
      .references(() => customers.id, { onDelete: 'restrict' }),
    createdBy: uuid()
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),
    number: text().notNull(),
    status: invoiceStatus().notNull().default('draft'),
    total: numeric({ precision: 12, scale: 2 }).notNull(),
    currency: text().notNull().default('USD'),
    issuedAt: timestamp({ withTimezone: true }).notNull(),
    dueAt: timestamp({ withTimezone: true }).notNull(),
    ...timestamps,
  },
  (t) => [
    unique('invoices_org_number_unique').on(t.organizationId, t.number),
    check('invoices_total_nonneg', sql`${t.total} >= 0`),
    index('idx_invoices_org_status_created_at_id').on(
      t.organizationId,
      t.status,
      t.createdAt.desc(),
      t.id.desc(),
    ),
    index('idx_invoices_org_created_at_id').on(
      t.organizationId,
      t.createdAt.desc(),
      t.id.desc(),
    ),
    index('idx_invoices_customer_id').on(t.customerId),
  ],
);

export type Invoice = typeof invoices.$inferSelect;
export type NewInvoice = typeof invoices.$inferInsert;

The three indexes and their column order. The composite ones mirror the list query’s where + orderBy: tenant first, then the keyset (createdAt, id) descending; idx_invoices_customer_id serves the detail join. Whether the planner actually uses them is proven against a live EXPLAIN plan in the next lesson — flagged here, not proven here.

← Previous 1 / 1 Next →

The ON DELETE split is the decision to name once and then apply by reflex. The four owned-children edges — org_members to both parents, customers to its org, invoices to its org, and invoice_lines to its invoice — all cascade, because each child is part of its parent and has no independent existence. The two referenced-entity edges — invoices.customerId and invoices.createdBy — restrict, because a customer and an author are things an invoice points at, and Postgres should refuse to delete one while an invoice still references it. The rationale lives in the foreign-key lesson of the Drizzle schema chapter; what matters here is that you decided each edge rather than defaulting all of them to the same action.

The last table, invoice_lines, closes the chain. Each line cascades from its invoice, and the line’s position within its invoice is unique — (invoiceId, position) — so two lines on the same invoice can never claim the same slot.

src/db/schema.ts (continued)

export const invoiceLines = pgTable(
  'invoice_lines',
  {
    id: uuid().primaryKey().default(sql`uuidv7()`),
    invoiceId: uuid()
      .notNull()
      .references(() => invoices.id, { onDelete: 'cascade' }),
    description: text().notNull(),
    quantity: numeric({ precision: 12, scale: 2 }).notNull(),
    unitPrice: numeric({ precision: 12, scale: 2 }).notNull(),
    position: integer().notNull(),
    ...timestamps,
  },
  (t) => [
    unique('invoice_lines_invoice_position_unique').on(t.invoiceId, t.position),
  ],
);

export type InvoiceLine = typeof invoiceLines.$inferSelect;
export type NewInvoiceLine = typeof invoiceLines.$inferInsert;

src/db/relations.ts

The constraints above teach Postgres about the foreign keys. This file teaches Drizzle’s relational query API about them, so that db.query.invoices.findFirst({ with: { customer: true, lines: true } }) knows how to assemble a nested read in the lessons ahead. You declare a relations() per table, naming each edge and pointing its fields/references at the columns that join.

The one place this needs care is the pair of edges from invoices to users. An invoice touches users twice: once because its organization has members, and once because the invoice has an author through createdBy. Left undisambiguated, the query API cannot tell which edge a nested with means. The fix is a matching relationName: 'createdByUser' tag on both sides of the author edge — on usersRelations.invoices and on invoicesRelations.createdByUser — which pins them together as one named relationship, distinct from membership. That is what makes requirement 4 hold: a read of an invoice’s author never cross-wires with the org’s member list.

src/db/relations.ts

import { relations } from 'drizzle-orm';

import {
  customers,
  invoiceLines,
  invoices,
  organizations,
  orgMembers,
  users,
} from '@/db/schema';

export const organizationsRelations = relations(organizations, ({ many }) => ({
  members: many(orgMembers),
  customers: many(customers),
  invoices: many(invoices),
}));

export const usersRelations = relations(users, ({ many }) => ({
  members: many(orgMembers),
  invoices: many(invoices, { relationName: 'createdByUser' }),
}));

export const orgMembersRelations = relations(orgMembers, ({ one }) => ({
  organization: one(organizations, {
    fields: [orgMembers.organizationId],
    references: [organizations.id],
  }),
  user: one(users, {
    fields: [orgMembers.userId],
    references: [users.id],
  }),
}));

export const customersRelations = relations(customers, ({ one, many }) => ({
  organization: one(organizations, {
    fields: [customers.organizationId],
    references: [organizations.id],
  }),
  invoices: many(invoices),
}));

export const invoicesRelations = relations(invoices, ({ one, many }) => ({
  organization: one(organizations, {
    fields: [invoices.organizationId],
    references: [organizations.id],
  }),
  customer: one(customers, {
    fields: [invoices.customerId],
    references: [customers.id],
  }),
  createdByUser: one(users, {
    relationName: 'createdByUser',
    fields: [invoices.createdBy],
    references: [users.id],
  }),
  lines: many(invoiceLines),
}));

export const invoiceLinesRelations = relations(invoiceLines, ({ one }) => ({
  invoice: one(invoices, {
    fields: [invoiceLines.invoiceId],
    references: [invoices.id],
  }),
}));

These consts are not passed to drizzle() as a separate option. The provided db/index.ts spreads them straight into the schema object — drizzle(client, { schema: { ...tables, ...relations } }) — which is the shape this version of Drizzle expects, and what wires up db.query.<table> with the relation graph.

Watch video

CJ on Syntax walks a multi-table Postgres schema end to end — foreign keys, composite keys, indexes, relations, and the generated migration — if you want to watch the same loop you are building (45 min).

Generate, read, then migrate

With both files written, generate the migration and give it a name so the file reads as intent rather than a hash:

Terminal

pnpm db:generate --name init_schema

That writes drizzle/0000_init_schema.sql. Open it before you do anything else. The structure is predictable: two CREATE TYPE ... AS ENUM for the enums, six CREATE TABLE with their inline primary keys, unique constraints, and the check, then a block of ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY that wires the foreign keys, then three CREATE INDEX. Here is the part worth reading closely — the invoices table, its foreign keys, and its indexes:

drizzle/0000_init_schema.sql (excerpt)

CREATE TABLE "invoices" (
  "id" uuid PRIMARY KEY DEFAULT uuidv7() NOT NULL,
  "organization_id" uuid NOT NULL,
  "customer_id" uuid NOT NULL,
  "created_by" uuid NOT NULL,
  "number" text NOT NULL,
  "status" "invoice_status" DEFAULT 'draft' NOT NULL,
  "total" numeric(12, 2) NOT NULL,
  "currency" text DEFAULT 'USD' NOT NULL,
  "issued_at" timestamp with time zone NOT NULL,
  "due_at" timestamp with time zone NOT NULL,
  "created_at" timestamp (3) with time zone DEFAULT now() NOT NULL,
  CONSTRAINT "invoices_org_number_unique" UNIQUE("organization_id","number"),
  CONSTRAINT "invoices_total_nonneg" CHECK ("invoices"."total" >= 0)
);
--> statement-breakpoint
ALTER TABLE "invoices" ADD CONSTRAINT "invoices_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "invoices" ADD CONSTRAINT "invoices_customer_id_customers_id_fk" FOREIGN KEY ("customer_id") REFERENCES "public"."customers"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "invoices" ADD CONSTRAINT "invoices_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_invoices_org_status_created_at_id" ON "invoices" USING btree ("organization_id","status","created_at" DESC NULLS LAST,"id" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "idx_invoices_org_created_at_id" ON "invoices" USING btree ("organization_id","created_at" DESC NULLS LAST,"id" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "idx_invoices_customer_id" ON "invoices" USING btree ("customer_id");

Two details carry the whole lesson. The first is the ON DELETE cascade on the organization edge sitting next to ON DELETE restrict on the customer and author edges — your per-edge decision, rendered as DDL. The second is DESC NULLS LAST on the created_at and id columns of the composite indexes: that descending order is what lets the planner walk the index in the same direction the list query reads, instead of sorting after the fact. Reading this file is not ceremony — it is the last checkpoint before DDL hits a database, and on a real project it is the diff a teammate reviews in the pull request.

Once you have read it and it matches your intent, apply it:

Terminal

pnpm db:migrate

That runs the file against the database and records it in a drizzle.__drizzle_migrations ledger. One file applied once leaves exactly one row in that ledger — the invariant requirement 1 checks.

Caution

drizzle.config.ts points Drizzle Kit at the unpooled URL, while the app’s runtime queries go through the pooled DATABASE_URL in db/index.ts. Locally they are the same connection string and dbUnpooled is just an alias for db, so nothing here behaves differently yet. The rule it stages becomes real when the deployment chapters later in the course swap in Neon: long-running DDL belongs on a direct, unpooled connection, never a pooled one. The practical takeaway for the rest of this project — any migrate or seed script you write reaches for dbUnpooled, never db.

Note

You generated a file and applied it rather than pushing the schema straight in with drizzle-kit push, even though the database was empty and pushing would have worked. The generate-and-commit loop is the production workflow: the migration is a reviewable artifact you commit alongside the code. push stays the prototype-only escape hatch from the migrations chapter — fine for a throwaway spike, wrong for anything you ship.

Drizzle — Indexes & Constraints

orm.drizzle.team

Reference for the foreign keys, unique constraints, check, and indexes you declare on these tables.

Drizzle — Relations

orm.drizzle.team

The relations() API, including the relationName tag that disambiguates the two invoices → users edges.

Drizzle Kit — Overview

orm.drizzle.team

The generate-and-migrate workflow behind pnpm db:generate and pnpm db:migrate.

Moment of truth

Requirement 1 asserts there is exactly one row in the migration ledger, and that is only true on a database migrated once from empty. If you experimented — migrated, tweaked, re-generated, migrated again — the ledger has accumulated extra rows and that test will fail correctly. So before you run the suite, give yourself a clean slate: drop and re-create the database (the quickest path locally is docker compose down -v then docker compose up -d, which discards the volume and brings back an empty Postgres), then run pnpm db:migrate exactly once.

With the database freshly migrated, run the lesson’s tests:

Terminal

pnpm test:lesson 3

The suite introspects the live database through information_schema and pg_catalog — the same way an auditor confirms what actually shipped — so it asserts real Postgres state, not your source files. Expect the two requirement groups to pass:

Expected output

 ✓ tests/lessons/Lesson 3.test.ts (7)
   ✓ Requirement 1 — clean migrate leaves exactly one migration row (1)
     ✓ records exactly one applied migration in __drizzle_migrations
   ✓ Requirement 2 — six tables with their FKs, uniques, check, and indexes (6)
     ✓ creates exactly the six tables in the public schema
     ✓ points every foreign key at the right ON DELETE action
     ✓ scopes the uniqueness constraints to the tenant where the domain demands
     ✓ enforces the non-negative invoice total with a check constraint
     ✓ creates the three query-justified indexes with the right columns and DESC ordering

 Test Files  1 passed (1)
      Tests  7 passed (7)

The tests cover requirements 1 and 2 — the migration ledger, the six tables, the foreign-key ON DELETE actions, the tenant-scoped uniques, the check, and the three indexes with their column order and DESC ordering. Confirm the rest by hand, ticking each as you go:

A second pnpm db:generate immediately after migrating reports no changes — the schema and the stored snapshot agree (requirement 3).

untested

pnpm db:studio shows all six tables, and each invoices foreign key, the tenant-scoped uniques, the total >= 0 check, and the three indexes are present (requirement 2, seen with your own eyes in Studio).

untested

Reading drizzle/0000_init_schema.sql, each foreign key carries its intended ON DELETE (cascade for owned children, restrict for the customer and author edges), each tenant-scoped unique covers the right columns, the check guards total >= 0, and the three indexes carry DESC NULLS LAST on their keyset columns.

untested

The relations resolve the two invoices → users edges distinctly — the author edge tagged relationName: 'createdByUser' on both sides — so a nested read of an invoice’s author and its org’s members does not cross-wire (requirement 4).

untested