Schema Design with Claude Code

One Schema File, Three Tables

The reference build's entire data model lives in db/schema.ts, defined with Drizzle's pgTable function. Three tables -- businesses, reviews, responses -- cover the whole MVP from lesson 1's spec. Before looking at the code, it is worth restating the scope decision from lesson 1 explicitly, because it shapes every column choice below: this schema is single-business-per-account. One owner, one business, enforced at the database level, not just assumed in application code.

Enums First

Drizzle's pgEnum maps directly to Postgres native enum types, which is stricter than a plain text column with an application-level check -- the database itself rejects an invalid value:

export const reviewSourceEnum = pgEnum("review_source", [
  "google", "yelp", "facebook",
]);

export const reviewStatusEnum = pgEnum("review_status", [
  "new", "responded", "flagged", "archived",
]);

export const subscriptionTierEnum = pgEnum("subscription_tier", [
  "free", "pro",
]);

export const subscriptionStatusEnum = pgEnum("subscription_status", [
  "active", "trialing", "past_due", "canceled",
  "incomplete", "incomplete_expired", "unpaid", "paused", "none",
]);

The subscription_status enum is worth pausing on -- those nine values are not invented for this tutorial, they are Stripe's own subscription status values, mirrored exactly so the webhook handler in a later lesson can write Stripe's status straight into this column with no translation layer. Ask Claude Code to mirror a third-party API's own enum rather than designing your own parallel one; a translation layer between the two is just a place for bugs to hide.

businesses: the Single-Tenant Boundary

export const businesses = pgTable("businesses", {
  id: uuid("id").primaryKey().defaultRandom(),
  ownerId: uuid("owner_id").notNull(),
  name: text("name").notNull(),
  googlePlaceId: text("google_place_id"),
  yelpBusinessId: text("yelp_business_id"),
  facebookPageId: text("facebook_page_id"),
  subscriptionTier: subscriptionTierEnum("subscription_tier").notNull().default("free"),
  subscriptionStatus: subscriptionStatusEnum("subscription_status").notNull().default("none"),
  stripeCustomerId: text("stripe_customer_id"),
  stripeSubscriptionId: text("stripe_subscription_id"),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
}, (table) => [
  uniqueIndex("businesses_owner_id_idx").on(table.ownerId),
  uniqueIndex("businesses_stripe_customer_id_idx").on(table.stripeCustomerId),
]);

ownerId is a uuid that stores a Supabase auth.users.id -- but notice there is no references() foreign key pointing at it. Drizzle manages your application schema; Supabase's auth schema is managed separately and Drizzle has no visibility into it (the same reason lesson 5's admin client exists -- to read auth data Drizzle cannot reach). The real enforcement of "single business per account" is the uniqueIndex on ownerId: the database itself refuses a second business row for the same owner. That single line of schema is what makes single-tenancy a guarantee, not a convention someone could accidentally violate by skipping an application-level check.

Add a unique index on businesses.owner_id so the database enforces one business per account, not just application code.

reviews: Dedup at the Database Layer

export const reviews = pgTable("reviews", {
  id: uuid("id").primaryKey().defaultRandom(),
  businessId: uuid("business_id").notNull().references(() => businesses.id, { onDelete: "cascade" }),
  source: reviewSourceEnum("source").notNull(),
  externalId: text("external_id").notNull(),
  authorName: text("author_name").notNull(),
  rating: integer("rating").notNull(),
  body: text("body").notNull(),
  status: reviewStatusEnum("status").notNull().default("new"),
  postedAt: timestamp("posted_at", { withTimezone: true }).notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (table) => [
  uniqueIndex("reviews_business_source_external_idx").on(table.businessId, table.source, table.externalId),
  index("reviews_business_id_idx").on(table.businessId),
  index("reviews_status_idx").on(table.status),
  index("reviews_posted_at_idx").on(table.postedAt),
]);

Two design choices to notice. First, externalId stores the review's id on its source platform (Google, Yelp, or Facebook), and the compound unique index on (businessId, source, externalId) is what lets ingestion be safely re-run -- a real poller re-fetching overlapping date ranges will hit the same review twice, and the database silently no-ops the duplicate instead of the application having to check first. Second, three separate single-column indexes on businessId, status, and postedAt exist because lesson 7's query layer filters and sorts by exactly those three columns -- indexes follow real query patterns, not a guess at what might be useful.

responses: Modeling a Field You Don't Use Yet

export const responses = pgTable("responses", {
  id: uuid("id").primaryKey().defaultRandom(),
  reviewId: uuid("review_id").notNull().references(() => reviews.id, { onDelete: "cascade" }),
  body: text("body").notNull(),
  publishedAt: timestamp("published_at", { withTimezone: true }),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
}, (table) => [
  index("responses_review_id_idx").on(table.reviewId),
]);

Recall from lesson 1: this MVP does not push responses back to Google, Yelp, or Facebook's own APIs -- those require their own approval gates. publishedAt is nullable and unused by any write path in this build, but it is in the schema anyway, with a comment explaining why: it makes the UI and data flow realistic now, and it is exactly the column a future publish-back integration would start writing to. This is a useful general pattern -- model a field for a deferred feature when it costs nothing and clarifies intent, but do not build the logic that fills it until the feature is actually in scope.

Foreign Keys and Cascade Deletes

Both reviews.businessId and responses.reviewId use onDelete: "cascade". Deleting a business deletes its reviews; deleting a review deletes its responses. This matters for a single-tenant app specifically because account deletion is a real, expected flow -- a business owner closing their account should not leave orphaned rows behind, and cascading at the database level means application code never has to remember to clean up child rows manually.

Generating and Reviewing the Migration

None of the SQL in drizzle/0000_lying_shocker.sql was hand-written -- it is drizzle-kit generate's output from the schema above, and the file name is drizzle-kit's own auto-generated label. A second migration, 0001_fearless_archangel.sql, is a single line:

ALTER TYPE "public"."subscription_status" ADD VALUE 'paused' BEFORE 'none';

That tiny migration is itself a worked example of incremental schema evolution from lesson 4's workflow: the paused status was added to the enum after the fact, as its own reviewable migration, not folded silently into the first one. Always read a generated migration before running db:migrate against it -- drizzle-kit is reliable, but the only person who knows whether a generated DROP COLUMN is actually safe for your data is you.

Lesson 7 builds the query and mutation functions that read and write this exact schema.