Capstone: Evolving a Live Schema

Prisma Intermediate/Advanced

Chapter 10 · Capstone: Evolving a Live Schema Without Downtime

The blog API from Prisma Fundamentals is live. Posts are either published or not, stored in a published Boolean. The editors now want a third state, archived: hidden from the front page but still reachable by link. The job is to replace published with a status field — DRAFT, PUBLISHED or ARCHIVED — while the site keeps running, several copies of the API are serving traffic, and no reader sees an error.

This capstone follows Chapter 8's expand-and-contract plan, and uses something from every chapter along the way.

Release	Schema	Code	Chapters used
1. Expand	Add optional `status`, plus an index	Unchanged	7, 8, 9
2. Dual write	—	Writes set both fields, in one place	1, 5, 6
3. Backfill	Data migration fills old rows	—	3, 8
4. Switch reads	`status` becomes required	Reads use `status`; new stats	2, 4, 5
5. Stop old writes	—	Only `status` written	6
6. Contract	Drop `published`	—	8, 9

Release 1: Expand

// schema.prisma
enum PostStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
}

model Post {
  // ...existing fields, including published Boolean @default(false)
  status PostStatus?                          // optional for now

  @@index([authorId])
  @@index([status, createdAt(sort: Desc)])   // the front page will filter on status (Chapter 7)
}

npx prisma migrate dev --name add-post-status

-- The generated migration: purely additive
CREATE TYPE "PostStatus" AS ENUM ('DRAFT', 'PUBLISHED', 'ARCHIVED');
ALTER TABLE "Post" ADD COLUMN "status" "PostStatus";
CREATE INDEX "Post_status_createdAt_idx" ON "Post"("status", "createdAt" DESC);

Old code ignores the new column; nothing it does can fail. It ships through the pipeline from Chapters 8 and 9: CI checks the migration exists, the release step runs migrate deploy once, then the new version starts. On a large table, edit the index line to CREATE INDEX CONCURRENTLY before committing (Chapter 8).

Release 2: Dual Write

Every write must now keep the two fields in agreement. Scattering that across routes would be easy to get wrong, so the rule lives in one place: model methods from Chapter 6.

// lib/extensions/postStatus.ts
import { Prisma } from "../../generated/prisma/client";

export type Status = "DRAFT" | "PUBLISHED" | "ARCHIVED";

// During the transition, both representations are written together
export function statusFields(status: Status) {
  return { status, published: status === "PUBLISHED" };
}

export const postStatus = Prisma.defineExtension((client) =>
  client.$extends({
    name: "postStatus",
    model: {
      post: {
        async setStatus(id: number, status: Status, expectedVersion: number) {
          const result = await client.post.updateMany({
            where: { id, version: expectedVersion },                         // Chapter 1
            data:  { ...statusFields(status), version: { increment: 1 } },
          });
          if (result.count === 0) throw new Error("Post changed or not found");
        },
      },
    },
  }),
);

Routes that create or publish posts now call statusFields(...) or setStatus(...). The optimistic-concurrency check from Chapter 1 stops two editors publishing and archiving the same post at once.

// test/postStatus.int.test.ts  (Chapter 5)
it("keeps published and status in agreement", async () => {
  const author = await base.user.create({ data: { email: "a@test.local" } });
  const post = await base.post.create({
    data: { title: "T", slug: "t", authorId: author.id, ...statusFields("DRAFT") },
  });

  await prisma.post.setStatus(post.id, "PUBLISHED", post.version);
  expect(await base.post.findUnique({ where: { id: post.id } }))
    .toMatchObject({ status: "PUBLISHED", published: true, version: 1 });

  await prisma.post.setStatus(post.id, "ARCHIVED", 1);
  expect(await base.post.findUnique({ where: { id: post.id } }))
    .toMatchObject({ status: "ARCHIVED", published: false });
});

Archived posts during the transition

Old code still reads published, and an archived post has published: false, so old copies of the app treat it as a draft — hidden. That's acceptable here, because archived posts should leave the front page anyway. Always check what the old code will make of each new state: if it would have exposed something private, you'd hold back the new state until Release 4.

Release 3: Backfill

Posts written before Release 2 still have status = NULL. A data migration (Chapter 8) fills them in, using SQL (Chapter 3):

npx prisma migrate dev --name backfill-post-status --create-only

-- migration.sql
UPDATE "Post"
SET "status" = CASE WHEN "published" THEN 'PUBLISHED'::"PostStatus"
                     ELSE 'DRAFT'::"PostStatus" END
WHERE "status" IS NULL;

The WHERE "status" IS NULL makes it safe to re-run and leaves alone any post already written by Release 2. Before moving on, verify the result — this is the check that makes the next step safe:

const [missing, mismatched] = await prisma.$transaction([
  prisma.post.count({ where: { status: null } }),
  prisma.$queryRaw<{ n: number }[]>`
    SELECT COUNT(*)::int AS n FROM "Post"
    WHERE ("status" = 'PUBLISHED') <> "published"
  `,
]);
console.log({ missing, mismatched: mismatched[0].n });   // both must be 0

Release 4: Switch Reads

With every row filled in, status can become required:

status PostStatus @default(DRAFT) // no longer optional

-- Generated migration
ALTER TABLE "Post" ALTER COLUMN "status" SET NOT NULL,
ALTER COLUMN "status" SET DEFAULT 'DRAFT';

The default matters: old copies still running during this deploy create posts without setting status, and the default keeps those inserts working. Reads now switch over:

// Front page: published only (uses the new index)
prisma.post.findMany({
  where:   { status: "PUBLISHED" },
  orderBy: { createdAt: "desc" },
  take:    20,
  select:  { title: true, slug: true, url: true },   // url from Chapter 6's result extension
});

// A single post: published or archived are both reachable by link
prisma.post.findFirst({ where: { slug, status: { in: ["PUBLISHED", "ARCHIVED"] } } });

// Stats (Chapter 2): posts per status in one query
prisma.post.groupBy({ by: ["status"], _count: { _all: true } });

Update the seed scripts (Chapter 4) to set status too, including a few archived posts, so development data exercises the new state.

Release 5: Stop Old Writes

Once no running copy reads published, statusFields stops writing it — one line, because the rule lived in one place:

export function statusFields(status: Status) {
  return { status };
}

Before shipping, search the whole codebase — including raw SQL strings, seed scripts, tests and admin scripts — for published. Raw SQL is invisible to TypeScript, so the compiler won't point it out.

Release 6: Contract

Finally, after at least one full release with nothing reading or writing the old column, remove it from the schema:

npx prisma migrate dev --name drop-post-published

ALTER TABLE "Post" DROP COLUMN "published";

The only step you can't undo

Every earlier release could be rolled back by redeploying the previous version. Dropping a column destroys data. Take a backup first, and make sure the release before this one has run long enough to be trusted. There's no prize for contracting quickly.

Looking Back

Chapter	Role in the capstone
1. Transactions	Version check in `setStatus`; consistent verification reads
2. Aggregation	Posts per status with `groupBy`
3. Raw SQL	The backfill and the mismatch check
4. Seeding	Sample data with archived posts
5. Testing	Integration test proving both fields agree
6. Extensions	`setStatus` and the single source of the write rule
7. Performance	The `status, createdAt` index for the front page
8. Schema evolution	The whole expand-and-contract sequence
9. Deploying	One migration step per release, before the new code starts

Hands-On Exercises

Exercise 1

Midway through (after Release 2), a bug is found and Release 2 must be rolled back to Release 1's code. What happens to posts archived in the meantime, and what must you check before redeploying Release 2?

📄 View solution

Exercise 2

The posts table has 20 million rows. Replace Release 3's single UPDATE with a batched backfill script that's safe to stop and restart, and report progress.

📄 View solution

Exercise 3

Apply the same approach to a new change: split User.name into firstName and lastName. Write the release plan and the backfill SQL, and point out where this is harder than the status change.

📄 View solution

Chapter 10 Quick Reference

Expand → dual write → backfill → switch reads → stop old writes → contract, each a separate release
Keep the dual-write rule in one function or extension, so stopping it is a one-line change
Ask what the old code will do with each new state before introducing it
Backfills are re-runnable and verified with counts before the next step
Give a newly required column a default so old code's inserts keep working
Search raw SQL and scripts, not just TypeScript, before removing a field
Dropping the column is the only irreversible step: back up and wait

Course complete

Together with Prisma Fundamentals, this course has taken the blog API from its first model to a schema that can change safely while in use. The same habits — measure, keep rules in one place, change in small reversible steps — apply well beyond Prisma.