PlanetScale to Convex in two days, with Claude Code driving

The patient

Harvey's Seed Labeler is an internal tool for Harvey's Seed. Staff enter seed lots and blends, and the app prints the regulatory tag that goes on the bag: purity, germination, noxious weeds, lot number, the works. Since last spring it also generates planting maps and dates for the Midwest from each blend's composition. It's a compliance tool with a handful of users. It doesn't need to scale; it needs to be right and to stay cheap to run.

The stack before: Next.js 15 App Router, tRPC 11, Prisma 5 on PlanetScale MySQL through the serverless driver adapter, NextAuth with the Prisma adapter and three custom OIDC providers (I wrote up that auth layer in What is Auth?). 22 tRPC procedures. Zero tests on any of them.

That last fact is where the migration actually started.

Step zero: write the tests first

This is the part of the migration that had nothing to do with Convex and everything to do with why it didn't break anything.

The app had 22 tRPC procedures, every one of them about to have its database swapped out from underneath it, and none of them had a test. That's a problem, and it's worth being precise about why. A migration changes the implementation of something while promising the behavior stays the same. If you don't have a record of what the behavior was, you can't check the promise. You can click around the app afterward and it'll probably look fine, but "probably" is doing a lot of work in that sentence, and the failures that get through are the quiet ones: a field that comes back undefined instead of null, a delete that leaves a related row behind, a list that's in a different order. The ADR I wrote that morning has the rule in one line: "A migration with no regression net is a guess."

A regression net is a set of tests that pin down current behavior so you'll know if it changes. It doesn't matter whether the current behavior is good. It matters that it's recorded.

Integration tests, not unit tests

Unit tests wouldn't have helped here. The thing I needed to protect was exactly the part a unit test mocks away: what happens when the procedure talks to the real database. So the suite is integration tests. Each test calls a tRPC procedure the same way the app does, with a real session, and the procedure talks to a real database. Then the test reads the database back and checks what's there. No mocks in the data path. If the query is wrong, the test is wrong.

The database is a throwaway one. The test runner starts a Docker container with an empty database, the tests fill it and check it, and the container is thrown away when they're done. Every test starts by wiping every table, so tests can't leak into each other. When the app was on MySQL the container was MySQL. Now it's Convex's open-source backend, which they publish as a Docker image. Same idea, different image.

The guard

"Every test starts by wiping every table" should make you nervous. It made me nervous. Earlier this year a Prisma command that resets the database ran against the wrong branch because .env was pointing somewhere nobody expected, and the dev data was gone. Database URLs live in environment variables, environment variables come from files and shells, and files and shells drift.

So the harness has one rule, and it's in one file: nothing gets to open a database unless the URL is unmistakably the throwaway container. Here's the whole check. Read it top to bottom; every throw is a specific way of being wrong.

// __tests__/integration/helpers/db-guard.mjs
const ALLOWED_CONVEX_HOSTS = new Set(["convex", "localhost", "127.0.0.1"]);

export function assertSafeConvexUrl(raw, env = process.env) {
  // 1. Did someone *mean* to run the integration suite? Only the test
  //    runner script sets this flag. Plain `jest` from your shell fails here.
  if (env.INTEGRATION_DB !== "1") {
    throw new Error(
      "[integration] refusing to touch a Convex deployment: INTEGRATION_DB=1 is not set (run via pnpm test:integration)",
    );
  }
  // 2. No URL? Refuse. Never fall back to a default that might be real.
  if (!raw) {
    throw new Error(
      "[integration] refusing to touch a Convex deployment: CONVEX_SELF_HOSTED_URL is not set",
    );
  }
  // 3. Every hosted Convex deployment lives on one of these two domains.
  //    Reject them on sight, before even parsing the URL.
  if (/convex\.cloud|convex\.site/i.test(raw)) {
    throw new Error(
      "[integration] refusing to touch a Convex deployment: URL looks like a cloud deployment",
    );
  }
  let url;
  try {
    url = new URL(raw);
  } catch {
    throw new Error(
      "[integration] refusing to touch a Convex deployment: CONVEX_SELF_HOSTED_URL is not a valid URL",
    );
  }
  // 4. The container speaks plain http. Every real deployment is https.
  if (url.protocol !== "http:") {
    throw new Error(
      `[integration] refusing to touch a Convex deployment: expected http://, got ${url.protocol}`,
    );
  }
  // 5. And the host has to be the container itself, by one of its three names.
  if (!ALLOWED_CONVEX_HOSTS.has(url.hostname)) {
    throw new Error(
      `[integration] refusing to touch a Convex deployment: host "${url.hostname}" is not a test container`,
    );
  }
  return raw;
}

Four details that aren't obvious from the code:

• Checks three and five overlap on purpose. The host allowlist already excludes convex.cloud. The regex is there so that if someone later loosens the allowlist, the cloud domains are still refused, and so the error message says exactly what it saw.
• It returns the URL. That lets callers write const url = assertSafeConvexUrl(...), which means the guard sits in the path to the value instead of being a separate line someone could delete.
• It takes env as a parameter with process.env as the default. That's so the guard can itself be tested by passing a fake environment, without messing with the real one.
• It has no imports. Two different loaders run this file: Jest, and a plain Node script that deploys the functions to the container. A dependency-free file is the only thing both accept without configuration, and it also means nothing can change its behavior underneath it.

It runs in three places: in the deploy script before anything is pushed to the container, when the test client is first created, and inside the function that wipes the tables, every single time, even though the module already checked once. That last one is redundant. Redundant is the point.

There's also a check the guard can't do, because it runs on the client side. The table-wiping function lives in the Convex deployment, and it throws unless the deployment has a flag called IS_TEST set. Only the deploy script sets that flag, and only after the guard has passed. So even a script that bypasses the helpers and builds its own client can't wipe a deployment that wasn't set up as a test deployment.

Writing tests is an audit

Writing a test for a procedure means reading it closely enough to say what it should do. Do that for 22 procedures written over a couple of years and you'll find things. Before a single line of migration code existed, the suite turned up:

• Six procedures with no session check, including the ones that delete products and seeds. Anyone who knew the URL could delete catalog rows without logging in.
• Orphaned rows. PlanetScale's flavor of MySQL doesn't enforce foreign keys, so deleting a product left behind the rows that linked it to a mix. A third of those link rows on the dev branch pointed at products that no longer existed.
• A mix picker that was lying. A blend component showed as 10% of a mix when it was really 50%, because the picker let you choose one seed out of a product that had several.

Three real bugs, none of them related to the migration. Each got fixed in the same commit as the test that found it, so the test is the proof and the fix is next to it. The suite was 99 cases when it landed and 143 by the end.

What a good migration test looks like

Most of what this app does is create mixes. A mix is a custom seed blend a customer ordered: a name, a lot number, a total bag weight, and a list of components with how many pounds of each go in. A component is either a product already in the catalog or, when the customer wants a plain variety that isn't cataloged as a product yet, a bare seed that the app wraps in a new product on the fly. That second case matters because it means "create a mix" can also create products.

Here's the test for that. Read the comment first, then the code. Notice what it doesn't mention: there's no Prisma in it, no Convex in it, no SQL. It only knows about the tRPC caller and the shape of what comes back.

// __tests__/integration/mix.router.test.ts
it("creates the mix with both ProductMix idioms (existing product + wrapper product around a seed)", async () => {
  const user = await createUser();
  const product = await createProductRow(user.id); // Kentucky bluegrass
  const turnip = await createSeedRow(user.id, { name: NAMES.TURNIP });
  const caller = userCaller(user);

  const mix = await caller.mix.create(
    mixCreateInput([
      { weight: 60, productId: product.id },
      // productId 0 is what the form sends; a product that does not exist means "wrap the seed".
      {
        weight: 40,
        productId: 0,
        seedId: turnip.id,
        name: "Wrapper",
        lotNumber: "W-1",
      },
    ]),
  );

  expect(mix).toMatchObject({
    id: 1,
    name: "Test Mix",
    lotNumber: "M-1",
    totalWeight: 8,
    createdById: user.id,
  });
  expect(mix.createdAt).toBeInstanceOf(Date);
  expect(mix).not.toHaveProperty("ProductMix");
  expect(await count("products")).toBe(2);
  const wrapper = await findByLegacyId("products", 2);
  expect(wrapper).toMatchObject({
    name: "Wrapper",
    lotNumber: "W-1",
    createdById: user.id,
  });
  expect(
    (await caller.product.getById({ id: 2 }))?.seed.map((s) => s.id),
  ).toEqual([turnip.id]);
  expect(await count("seeds")).toBe(2); // no seed copies
  const components = (await caller.mix.getByIdWithArtifact({ id: mix.id }))!
    .ProductMix;
  expect(
    components.map((pm) => [pm.productId, pm.weight, pm.createdById]),
  ).toEqual([
    [product.id, 60, user.id],
    [2, 40, user.id],
  ]);
  // ...then checks the planting maps that creating a mix also generates, omitted here.
});

Set up. A user and a caller that acts as them, the way a logged-in session would. One product in the catalog (a bluegrass) and one bare seed (a turnip). That's the smallest world in which both kinds of component exist.

Act. Call mix.create once, with two components: 60 pounds of the existing product, and 40 pounds of the turnip with productId: 0. The zero is what the web form actually sends when the user picked a seed instead of a product, and the comment says so, because a future reader would otherwise assume it's a typo.

Check, from the outside in. The first assertions look at what the caller got back: the mix has id 1 because the database was empty, the fields echo the input, createdAt is a real Date, and the components are not included in the response, because the form that calls this doesn't need them. Then the test looks past the response at what actually happened in the database. There are two products now, not one, and the new one is the wrapper with the name and lot number from the form. That wrapper contains exactly the turnip. There are still two seeds, so the app linked the existing seed rather than copying it. And reading the mix back shows two components, in order, with the right weights and the right owner.

Every one of those checks guards a specific way this could go wrong. The one worth singling out is count("seeds") being 2. Nothing in the response would tell you if the app had quietly duplicated the turnip. You only find that by reading the table. That's the whole reason these are integration tests and not unit tests.

That test was written against MySQL and Prisma. When the router moved to Convex, the assertions didn't change; only the helpers underneath it, count and findByLegacyId, were re-pointed at the new container. Because it pins behavior instead of implementation, it didn't care which database was behind the caller, and that's exactly what made it useful. When I ported the mix router and this went green, I knew the wrapper-product path was right. When another test went red, I knew which behavior I'd broken, in a sentence.

The rule generalizes: before you change how something works, write down what it does, in a form that can tell you when you're wrong. Then you're allowed to change it.

What Convex actually is

Here is the mental model that took me about a day to stop fighting.

With Prisma, the database is a dumb store on the other end of a connection string. Your server process holds a client, the client turns findMany into SQL, the SQL goes over a pooled connection, rows come back, the client shapes them. All the intelligence is in your process. On a serverless host that's where the pain is too: cold starts pay for the engine, connection limits are your problem, and a single interactive transaction holds a connection hostage.

With Convex, the functions live in the database. You write a directory of TypeScript files, each exporting query, mutation or action definitions, and npx convex deploy pushes them to a deployment. Your application then calls those functions by typed reference, over HTTPS or a WebSocket, and never sends a query string anywhere. There's no connection pool because there's no connection. There's no engine binary in your bundle because the engine is the service. npx convex dev watches the directory, pushes on save, and regenerates a typed api object, which is the closest thing to prisma generate, except the deploy is the codegen.

The three function kinds have sharply different rules, and the rules are the whole design:

• Queries read. They run in Convex's own V8 runtime with no network access and no nondeterministic APIs. That restriction is what makes two things possible: the result is cached by arguments, and the backend records exactly which documents and index ranges the query touched, so it knows when to recompute. A client that subscribes to a query gets pushed the new result whenever any of those documents change. That's the reactivity Convex is known for, and you get it without writing invalidation code.
• Mutations write. Each mutation is one serializable transaction, automatically, from the first line of the handler to the last. Same determinism rules. If two mutations conflict, the loser is retried, which is only safe because the handler couldn't have had side effects outside the database.
• Actions are for everything else: calling a third-party API, running Node code, anything with fetch. They're not transactional, so they read and write by calling queries and mutations.

Then there are the limits, and these shape schemas in a way SQL never did. Three of them matter day to day:

• A single document is at most 1 MiB.
• A single query or mutation may read at most 16 MiB of documents.
• A single query or mutation may scan at most 32,000 documents, whether or not it returns them.

A MiB is a mebibyte, 1,048,576 bytes, about 5% more than a megabyte; for this purpose read it as one. A phone photo is two to four of them, a page of plain text about four thousandths of one.

Those sound generous until you measure your own app, so I did. Here's what one call actually weighs in the labeler, measured today against the dev deployment as the JSON the function returns:

• One seed row, the record behind one line on a tag: about 525 bytes. The whole seed catalog, 68 rows with each one's creator and noxious-weed list attached, is 36 KB. That's a thirtieth of a document limit and a four-hundred-and-fiftieth of a read limit. You could read it four hundred times in one call.
• One product row: about 240 bytes. All 103 of them, 25 KB.
• The full read behind the tag printer, mix.getById with every component, product, seed and weed walked: between 74 bytes for an empty mix and 1.8 KB for a four-component one. The entire thing the app does most often fits in two kilobytes.
• The mix list page's data, 28 mixes with their status metadata: 26 KB.

None of that will ever touch a limit. Then there are the planting-map artifacts, which are not rows so much as files stored in rows: three SVG maps, a CSV and two JSON blobs per mix. Measured today they run from 100 KB to 330 KB each, a quarter of a megabyte on average. One of them is fine; a document is allowed a megabyte. Sixty of them in one query is not: that's the 16 MiB read limit, and the naive port of the mix list page, which used Prisma's include: { plantingArtifact: true } and pulled every body just to show a status column, would have crossed it somewhere around sixty mixes. The dev deployment has twenty-eight. It would have worked in development, passed every test, and died in production the first spring the catalog grew.

The research note flagged exactly that before any code was written, and the fix is a schema decision, not a query tweak. More on it below. The point for now: SQL let me write a query that got slower every month and never told me. Convex draws the line at design time, and the line is high enough that a compliance tool with a few hundred rows will never see it, as long as big things live apart from small ones.

One more thing that's easy to miss coming from Prisma: every public Convex function is an internet-reachable endpoint. With Prisma your database was, at least in theory, behind your server. With Convex the functions are the server's edge, so every one of them has to decide who's calling. Convex's native answer is ctx.auth.getUserIdentity() with a JWT from your identity provider. The labeler's answer was a shared secret, because the browser never talks to Convex at all, and I'll show why.

Prisma to Convex, construct by construct

The schema

Prisma's schema is a DSL that Prisma both generates a client from and migrates a database toward. Here are the two catalog models at the center of the labeler, as they were on PlanetScale:

// prisma/schema.prisma (before)
datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"   // Vitess has no foreign keys; Prisma emulates them
}

model Product {
  id          Int          @id @default(autoincrement())
  name        String
  lotNumber   String
  seed        Seed[]       // implicit join table _ProductToSeed
  createdAt   DateTime     @default(now())
  updatedAt   DateTime     @updatedAt
  createdBy   User         @relation(fields: [createdById], references: [id])
  createdById String
  Tag         Tag?         @relation(fields: [tagId], references: [id], onDelete: Cascade)
  tagId       Int?
  ProductMix  ProductMix[]

  @@index([name])
}

model ProductMix {
  id        Int      @id @default(autoincrement())
  weight    Float
  product   Product  @relation(fields: [productId], references: [id], onDelete: Cascade)
  productId Int
  mix       Mix[]    // implicit join table _MixToProductMix
  // ...
}

Convex's schema is TypeScript: a defineSchema of defineTables built from validators. Same two tables after:

// convex/schema.ts (after)
const timestamps = { createdAt: v.number(), updatedAt: v.number() };
const catalogRow = { legacyId: v.number(), createdById: v.string(), ...timestamps };

products: defineTable({
  name: v.string(),
  lotNumber: v.string(),
  tagId: v.optional(v.id("tags")),
  ...catalogRow,
})
  .index("by_legacyId", ["legacyId"])
  .index("by_name", ["name"])
  .index("by_tagId", ["tagId"]),

productMixes: defineTable({
  weight: v.number(),
  productId: v.id("products"),
  ...catalogRow,
})
  .index("by_legacyId", ["legacyId"])
  .index("by_productId", ["productId"]),

// Prisma's implicit _ProductToSeed becomes a table you can see and index.
productSeeds: defineTable({
  productId: v.id("products"),
  seedId: v.id("seeds"),
})
  .index("by_productId", ["productId"])
  .index("by_seedId", ["seedId"]),

Everything that looks like a one-to-one translation hides a nuance:

• Ids. Every Convex document gets a system _id, an opaque string that encodes its table, plus a _creationTime. There's no autoincrement(). The labeler's URLs are /admin/mixes/32 and sixteen tRPC inputs are z.number(), so every catalog table carries a legacyId with an index, and new rows mint the next number from a counters table. Twenty-three lines reimplement AUTO_INCREMENT:

// convex/legacyId.ts
// A mutation is one transaction, so two concurrent inserts can't get the
// same number. Imported rows keep their MySQL ids; the migration seeds each
// counter above the imported maximum.
export async function nextLegacyId(
  ctx: MutationCtx,
  table: TableNames,
): Promise<number> {
  const counter = await ctx.db
    .query("counters")
    .withIndex("by_table", (q) => q.eq("table", table))
    .unique();
  if (!counter) {
    await ctx.db.insert("counters", { table, next: 2 });
    return 1;
  }
  await ctx.db.patch(counter._id, { next: counter.next + 1 });
  return counter.next;
}

• Nullable means absent. Prisma's String? is a column that holds NULL. Convex's v.optional(v.string()) is a field that isn't there. The routers' contract, and the tests, say origin: null, so a row-shaping layer on the Convex side writes doc.origin ?? null for every optional field. Boring, but it's the difference between the suite going green and forty assertions changing.

• Dates are numbers. DateTime becomes v.number() holding milliseconds, which is what Convex recommends and what _creationTime already is. There's no @updatedAt trigger, so every mutation sets updatedAt by hand, and the tRPC facade revives the known timestamp keys back into Date objects on the way out.

• References are typed but not enforced. v.id("products") guarantees a value is a document id from that table. It does not guarantee the document exists, and there is no onDelete: Cascade. I got this wrong in my head at first. Convex doesn't give you foreign keys any more than Vitess did; what it gives you is that the delete and the cascade run in one transaction, so they can't drift apart. The 123 orphaned join rows PlanetScale had been quietly carrying couldn't come across because the import mutation resolved every parent through by_legacyId and refused rows whose parent wasn't there, not because the schema stopped them.

• There are no migration files. Changing schema.prisma produces a SQL migration you sequence and apply. Changing convex/schema.ts and pushing makes Convex validate every existing document against the new shape and refuse the push if any don't fit. Adding a field means adding it as v.optional, backfilling with a mutation, then tightening. No shadow database, no migrate dev, no deploy request. For a two-person team that's most of the ceremony gone, and it's also how the wipe I mentioned earlier becomes impossible.

• Indexes are the query language. Prisma lets you where on any column and the database plans it, badly or well. Convex makes you declare an index for every access path you want to be fast, and withIndex is the primary way to read. There is a .filter(), but it runs after the index range and scans what it reads, against the same 32,000-document ceiling. Ordering is by the index you used, or _creationTime. Sorting by anything else happens in JavaScript after collect(). That's why there's a byLegacyIdAsc helper sprinkled through the Convex code: the tests expect Prisma's default insertion order and there's no ORDER BY id.

Reading

This is the change that reshaped the most code. Prisma's include and select describe a graph and the client figures out the joins. Convex has no joins. You write them, in the function, with ctx.db.get. Here's the labeler's biggest read, the shape the tag printer consumes, on both sides. Before:

// src/server/api/routers/mix.ts (before)
return ctx.db.mix.findUnique({
  where: { id: input.id },
  select: {
    id: true,
    name: true,
    totalWeight: true,
    lotNumber: true,
    ProductMix: {
      select: {
        weight: true,
        product: {
          select: {
            name: true,
            lotNumber: true,
            seed: {
              select: {
                name: true,
                weight: true,
                seedPurity: true,
                // ...thirteen more seed columns...
                noxiousWeed: {
                  select: { name: true, seedsPerPound: true, id: true },
                },
              },
            },
          },
        },
      },
    },
  },
});

After, the same graph is a Convex query that walks it explicitly:

// convex/mixes.ts (after)
export const getById = query({
  args: { secret: v.string(), legacyId: v.number() },
  handler: async (ctx, { secret, legacyId }) => {
    assertServiceSecret(secret);
    const mix = await findMix(ctx, legacyId);
    if (!mix) return null;
    const rows = await productMixesOfMix(ctx, mix._id);
    return {
      id: mix.legacyId,
      name: mix.name ?? null,
      totalWeight: mix.totalWeight,
      lotNumber: mix.lotNumber ?? null,
      ProductMix: await withProducts(ctx, rows, async (pm, product) => ({
        weight: pm.weight,
        product: {
          name: product.name,
          lotNumber: product.lotNumber,
          seed: await Promise.all(
            (await seedsOfProduct(ctx, product._id)).map(async (seed) =>
              seedSelected(
                seed,
                (await weedsOfSeed(ctx, seed._id)).map(weedSelected),
              ),
            ),
          ),
        },
      })),
    };
  },
});

And the piece Prisma used to do for free, a many-to-many traversal, is a twelve-line helper:

// convex/lib.ts
export async function seedsOfProduct(
  ctx: Ctx,
  productId: Id<"products">,
): Promise<Doc<"seeds">[]> {
  const links = await ctx.db
    .query("productSeeds")
    .withIndex("by_productId", (q) => q.eq("productId", productId))
    .collect();
  const seeds = await Promise.all(links.map((link) => ctx.db.get(link.seedId)));
  return byLegacyIdAsc(
    seeds.filter((seed): seed is Doc<"seeds"> => seed !== null),
  );
}

Three things to notice. First, the whole graph is read inside one query call, so it's consistent: a mutation that lands halfway through can't give you a mix with a product that's already gone. Prisma's findUnique with nested selects is also a single statement, but a chain of separate findMany calls in a tRPC procedure is not, and the labeler had a few of those. Second, the null filter in the helper is the honest cost of no referential integrity, and it's cheap. Third, there's no count, no sum, no groupBy. For a 725-row catalog you collect() and count in JS. For anything big Convex has an aggregate component that maintains counts in a side table, and you should reach for it before you hit the scan ceiling, not after.

That ceiling is what forced the one real schema change. mix.getAllWithArtifacts used include: { plantingArtifact: true } and returned every artifact's six SVG, CSV and JSON bodies, a quarter of a megabyte per mix on average. A naive port works until roughly sixty mixes, then every list load fails the 16 MiB read limit. So the bodies moved to their own table, plantingArtifactBodies, keyed by artifact, and the list query reads only the metadata rows. The download routes read one body at a time. In Prisma terms it's the difference between include and select, except Convex made the choice for me at design time instead of letting the page get slow for a year.

Writing

Prisma's nested writes are elegant and slightly magical. Creating a mix with its components, where each component either references an existing product or wraps a bare seed in a new one, was one call:

// src/server/api/routers/mix.ts (before)
const mix = await ctx.db.mix.create({
  data: {
    name: input.name,
    lotNumber: input.lotNumber,
    totalWeight: input.totalWeight,
    createdBy: { connect: { id: ctx.session.user.id } },
    ProductMix: {
      create: input.productMix?.map((productMix) => ({
        weight: productMix.weight,
        product: {
          connectOrCreate: {
            where: { id: productMix.productId ?? -1 },
            create: {
              name: productMix.name ?? "",
              lotNumber: productMix.lotNumber ?? "",
              seed: { connect: { id: productMix.seedId ?? -1 } },
              createdBy: { connect: { id: ctx.session!.user.id } },
            },
          },
        },
        createdBy: { connect: { id: ctx.session!.user.id } },
      })),
    },
  },
});

In Convex the same write is that connectOrCreate unrolled into plain inserts, in a loop, inside the mutation that creates the mix:

// convex/mixes.ts (after)
async function createComponents(ctx, mix, actorId, entries, now) {
  for (const entry of entries) {
    let product =
      typeof entry.productId === "number" && entry.productId > 0
        ? await findProduct(ctx, entry.productId)
        : null;
    if (!product) {
      const seed =
        typeof entry.seedId === "number"
          ? await findSeed(ctx, entry.seedId)
          : null;
      if (!seed) notFound(`Seed ${entry.seedId ?? -1} not found`);
      const legacyId = await nextLegacyId(ctx, "products");
      const productId = await ctx.db.insert("products", {
        name: entry.name ?? "",
        lotNumber: entry.lotNumber ?? "",
        legacyId,
        createdById: actorId,
        createdAt: now,
        updatedAt: now,
      });
      await ctx.db.insert("productSeeds", { productId, seedId: seed._id });
      product = await ctx.db.get(productId);
    }
    const legacyId = await nextLegacyId(ctx, "productMixes");
    const productMixId = await ctx.db.insert("productMixes", {
      weight: entry.weight,
      productId: product._id,
      legacyId,
      createdById: actorId,
      createdAt: now,
      updatedAt: now,
    });
    await ctx.db.insert("mixProductMixes", { mixId: mix._id, productMixId });
  }
}

It's longer. It's also the first time I could read, top to bottom, exactly what a "create mix" does to the database. And the transaction guarantee is stronger than what I had. Prisma's nested write is atomic, yes, but the real procedure did three things: assert the components are valid, create the mix, then generate planting artifacts and upsert them. Those were three separate round trips with no transaction around them. On Convex, the validation and the create are one mutation, and if any insert throws, none of them happened.

The writing primitives are tiny. ctx.db.insert(table, doc) returns the new _id. patch(id, fields) merges. replace(id, doc) overwrites. delete(id). There's no upsert, so the artifact writer is a patch-or-insert on the metadata row followed by a replace-or-insert on the body row, in one mutation, which is exactly the pair of upsert calls it replaced but now actually atomic. There's no updateMany or deleteMany with a where; you query the rows and loop.

Cascades, likewise, are just deletes you write. The old code had a cascade.ts module that tried to do by hand what relationMode = "prisma" wouldn't, and got the implicit join tables wrong. Now:

// convex/lib.ts
// Deletes seeds with their product / weed links; weed rows nothing links to
// any more go too. Explicit join tables make it plain deletes.
export async function deleteSeeds(ctx: MutationCtx, seeds: Doc<"seeds">[]) {
  const weedIds = new Set<Id<"noxiousWeeds">>();
  for (const seed of seeds) {
    const productLinks = await ctx.db
      .query("productSeeds")
      .withIndex("by_seedId", (q) => q.eq("seedId", seed._id))
      .collect();
    await Promise.all(productLinks.map((link) => ctx.db.delete(link._id)));
    const weedLinks = await ctx.db
      .query("seedNoxiousWeeds")
      .withIndex("by_seedId", (q) => q.eq("seedId", seed._id))
      .collect();
    for (const link of weedLinks) weedIds.add(link.noxiousWeedId);
    await Promise.all(weedLinks.map((link) => ctx.db.delete(link._id)));
    await ctx.db.delete(seed._id);
  }
  for (const weedId of weedIds) {
    const remaining = await ctx.db
      .query("seedNoxiousWeeds")
      .withIndex("by_noxiousWeedId", (q) => q.eq("noxiousWeedId", weedId))
      .first();
    if (!remaining) await ctx.db.delete(weedId);
  }
}

The determinism rule bit once, and it's worth knowing before you plan. The planting-map generator is a pile of d3 and svgo that reads files off disk and takes a second or two. It cannot run inside a mutation, and I didn't want it in an action either, because that means shipping the whole pipeline bundle to Convex's Node runtime for no benefit. So generation stays on the Next.js server, where it always was, and the result goes in through a mutation. The rule of thumb: if it's fetch, filesystem, or slow, it's not a mutation.

Errors

Prisma throws PrismaClientKnownRequestError with codes: P2025 for a missing row, P2002 for a unique violation. The labeler's routers caught those and translated them. Convex has ConvexError, which carries whatever data you give it across the wire to the caller, and nothing else. So the Convex side throws a two-field payload and the tRPC facade maps the code onto a TRPCError:

// convex/lib.ts
export function notFound(message: string): never {
  throw new ConvexError({ code: "NOT_FOUND", message });
}

// src/server/api/convex.ts
function translate(error: unknown): unknown {
  if (error instanceof ConvexError) {
    const { code, message } = error.data as { code?: string; message?: string };
    if (code && MAPPED_CODES.has(code)) {
      return new TRPCError({ code, message: message ?? code, cause: error });
    }
  }
  return error;
}

Thirteen of the 143 tests had pinned Prisma error codes. They were re-pinned to the tRPC code in the same diff, which is what they should have asserted in the first place.

Who's allowed to call it

Prisma's db object lived in the tRPC context and nothing outside the server could reach it. A Convex function is a URL. So the labeler's design has two rules, and the type system enforces the second.

The browser never talks to Convex. Every function takes a secret argument that only the Next.js server knows, plus the NextAuth user id as actorId. No second identity system, no JWT minting, and the three OIDC providers didn't change. The check is the entire file:

// convex/service.ts
// Convex endpoints are reachable from the internet. Under the tRPC facade
// the only legitimate caller is the Next.js server, which proves it here.
export function assertServiceSecret(secret: string): void {
  const expected = process.env.CONVEX_SERVICE_SECRET;
  if (!expected || secret !== expected) {
    throw new ConvexError("unauthorized: bad service secret");
  }
}

And the facade on the Next.js side injects that secret while removing it from every call signature, so a router literally can't pass it by hand:

// src/server/api/convex.ts
// One client per request, built lazily. `Omit<..., "secret">` means the
// routers never see the secret; the store adds it to every call.
export interface ConvexStore {
  query<F extends FunctionReference<"query">>(
    fn: F,
    args: Omit<FunctionArgs<F>, "secret">,
  ): Promise<FunctionReturnType<F>>;
  mutation<F extends FunctionReference<"mutation">>(
    fn: F,
    args: Omit<FunctionArgs<F>, "secret">,
  ): Promise<FunctionReturnType<F>>;
}

The store is built per request because the HTTP client is stateful. That's the one place the Convex docs' guidance for Next.js differs from what you'd assume: the server-side client is plain request/response, not reactive. If I ever want live-updating tables, the path is to let client components subscribe with Convex's useQuery directly, with a JWT, and the functions written for the facade are reusable as-is. I didn't need it, so I didn't build it.

With that, here's the smallest router on either side of the line, in full. Before:

// src/server/api/routers/seed.ts (before)
export const seedRouter = createTRPCRouter({
  getAll: publicProcedure.query(async ({ ctx }) => {
    return ctx.db.seed.findMany({
      include: {
        createdBy: { select: { name: true, email: true } },
        noxiousWeed: true,
      },
    });
  }),
  delete: publicProcedure
    .input(z.object({ id: z.number() }))
    .mutation(async ({ ctx, input }) => {
      return ctx.db.seed.delete({ where: { id: input.id } });
    }),
});

After:

// src/server/api/routers/seed.ts (after)
export const seedRouter = createTRPCRouter({
  getAll: protectedProcedure.query(async ({ ctx }) => {
    return reviveDates(await ctx.store.query(api.seeds.list, {}));
  }),
  /** Deletes the seed with its product / weed links and any weed nothing links to any more. */
  delete: protectedProcedure
    .input(z.object({ id: z.number() }))
    .mutation(async ({ ctx, input }) => {
      return reviveDates(
        await ctx.store.mutation(api.seeds.remove, { legacyId: input.id }),
      );
    }),
});

The include graph moved into the Convex function, the cascade became a sentence in a doc comment that the function actually honors, and publicProcedure became protectedProcedure, which is the unguarded-delete bug getting fixed. The React Server Components and the client hooks didn't change at all.

The NextAuth adapter is the same trick: it takes the same store the routers use and implements the Auth.js Adapter interface over fourteen small Convex functions, replacing PrismaAdapter(db) and nothing else.

Why this is an upgrade for a two-person shop

I want to be precise here, because "Convex is better" is the kind of claim that's only true for a shape of team.

What went away, concretely:

• A migration workflow. prisma migrate dev, a shadow database, PlanetScale deploy requests, and the branch-refresh script that kept dev in sync with prod. Now a schema change is a push, and a data change is a mutation I can run from the dashboard or the CLI.
• Connection management. The serverless driver adapter, driverAdapters as a preview feature, and the connection-string-in-.env hazard that wiped a branch. The server holds one URL and one secret; nothing else can reach the data.
• The engine in the bundle. Prisma's query engine rode along in every Docker image and every Vercel function, and prisma generate ran on every install. Gone.
• A separate SQL client, log viewer, and backup job. The Convex dashboard has a data browser, a function runner, per-function logs, and snapshot export. For a team that doesn't have an ops person, one screen is the feature.
• Half-transactions. Every multi-step write in the app was a sequence of Prisma calls with no transaction around them. Now every one of them is a mutation, and the correctness came for free.

What I'd tell someone about to do the same, honestly:

• You will write your joins. Every include becomes a helper. It's more code and it's the same code each time, so it stops hurting after the second router, but it's real.
• You will learn the limits before they teach you. Read the limits page first. Design list queries to return metadata, not blobs. Put anything large in its own table.
• You lose ad-hoc SQL. The dashboard's data browser filters and sorts, but if someone wants "every mix with more than three components created last quarter," that's a function you write, not a query you type. For a compliance tool with a handful of users, that's fine. For a reporting-heavy app it would be the deciding factor.
• It's a vendor's model, not a standard. The backend is open source and the test suite runs against it in Docker, so I'm not locked in on data. I am locked in on the shape of every function. I'm fine with that at this scale; I'd think harder at fifty tables.

What made this specific migration cheap was the tRPC layer. Because routers kept their procedures, inputs and response shapes, and only what's behind ctx changed, the whole store swap happened underneath a green test suite. If you don't have that seam, build it first.

The money, briefly

At this size every option that isn't PlanetScale is $0 a month, so the saving is the same whichever way you jump: $39 per database at PlanetScale's floor. The monthly line didn't pick a winner. The research note's estimate did the picking, and it said fifteen to twenty-five person-days for Convex against one to three for a MySQL-compatible swap. With Claude Code driving, the first Convex commit went in at 15:37 and all five routers were on Convex by 16:09. Once labor cost what it actually cost, the path I wanted and the cheapest path were the same path. I chose the Starter tier over Free so that an overage bills cents instead of failing a mutation.

Strangler on the branch, big bang in production

On the feature branch, the routers moved one at a time, with MySQL still serving whatever hadn't moved yet, and the suite ran green after each one. The planting-map pipeline never found out Prisma was gone: it dereferences exactly two findUnique shapes, so a fourteen-line reader serves them from Convex and the pipeline keeps believing.

The data move was a mutation, not Convex's import command. A read-only dump of PlanetScale, then a set of internal Convex functions that insert rows in their legacy shape and resolve every reference through the legacyId index inside the same transaction, so the schema never had to be loosened for a separate linking step. The rehearsal on real production data: 725 rows across 17 tables, 123 orphaned join rows dropped and listed, 27 artifact bodies verified byte for byte.

Then production, inside a read-only window: deploy the functions, dump, import, set two environment variables on Vercel, redeploy. Rollback was the previous Vercel deployment plus an untouched PlanetScale. Nobody needed it. The next day, Prisma, PlanetScale, DATABASE_URL, the prisma/ directory and ten scripts that opened MySQL directly came out of the repo, and a last raw dump of all 17 tables went into cold storage.

What Claude Code did, and didn't

The timeline, from the commit log:

• Tuesday 11:50. Integration suite for every tRPC procedure.
• 12:18. Research note: schema mapping, limits, alternatives.
• 15:37. Convex schema, test backend in the compose stack, plan brief.
• 16:09. All five routers served by Convex.
• 16:21. NextAuth storage and the catalog readers off Prisma.
• 16:51. Production data rehearsed on a cloud deployment. Cut over that evening.
• Wednesday 12:37. Prisma and PlanetScale gone from the repo.
• Thursday 14:34. Merged to main.

134 files changed, about 10,800 lines added and 4,600 removed. 53 Convex functions, 41 of them app-facing, replacing the Prisma calls behind 22 tRPC procedures. 954 unit tests and 143 integration tests green at the end.

What I did: approved the plan, ran npx convex login, edited the env files, clicked through the app on the dev deployment before cutover, and insisted on the tests coming first. That last one paid for everything else.

Where it needed correcting: when it retired two old verifier assertions it told me they were "covered by the integration suite." One was, one wasn't, and I only found out because I asked to see the tests it supposedly wrote. It checked, admitted the gap, and wrote them. Trust, then verify, then keep the receipts.

Next up is Farmcycle, which has 45 Prisma models on the same relationMode = "prisma" MySQL setup. Everything above is the plan.