DEV Community

Bhargav
Bhargav

Posted on • Originally published at bhargav.dev

Silent Schema Drift: How a text-to-jsonb cast broke production

#postgressql #payloadcms #typescript #json


A Payload CMS seed script, a production Postgres database, and a schema that had quietly diverged for months. Here's what broke and how to fix it.

TLDR: Payload's push mode ran against production, hit columns that had drifted from text to jsonb in the schema definition, and Postgres refused the automatic cast. Fix is USING content::jsonb in the ALTER. Real fix is never running push against prod and writing explicit migrations.

The Error

Running a seed script against my production Postgres database, every attempt to initialize Payload CMS failed:

DrizzleQueryError: Failed query: ALTER TABLE "series" ALTER COLUMN "content" SET DATA TYPE jsonb;
error: column "content" cannot be cast automatically to type jsonb
hint: You might need to specify "USING content::jsonb".
Enter fullscreen mode Exit fullscreen mode

The same error kept repeating for different tables [series, documents, notebooks_blocks, projects, work_experience], each time a different column.

What Is jsonb?

Postgres stores JSON in two ways:

  • json: stores the raw text string exactly as written. Fast writes, slow reads.
  • jsonb: parses the JSON and stores it in a decomposed binary format. Slightly slower writes, faster reads, and supports indexing.

Both hold the same data. For application data, you almost always want jsonb.

text is just a plain string. Postgres has no idea whether it contains JSON, it's just characters. There's no automatic conversion from text to jsonb because Postgres can't guarantee your strings are valid JSON without checking every row.

What Is Schema Drift?

Schema drift is when your application's schema definition (what the code expects the database to look like) and the actual live database diverge over time.

In this case:

  • The production database was created when Payload and Drizzle stored Lexical editor fields as text columns.
  • At some point, I updated the schema definition in code to use jsonb for those fields.
  • The production database was never migrated to match.
  • The application kept working because Postgres is flexible enough to return text values to the application layer, which then parses them.

The drift was silent. The site ran fine, no errors in production logs, nothing to indicate a problem, until I added a few collections to Payload and pushed the schema.

Why It Surfaced Now

Payload CMS v3 with the Drizzle adapter has two modes for keeping the DB in sync:

  1. Push mode (pushDevSchema): on startup, Drizzle compares its schema definition against the live DB and tries to ALTER any mismatched columns. Default in development.
  2. Migration mode: you write explicit SQL migration files and run them with payload migrate. Use this in production.

Running the seed script (payload run scripts/seed-resume.ts) called getPayload(), which triggered pushDevSchema. That kicked off the ALTER attempts and is where they failed.

payload migrate had run fine earlier and reported Done. The migration only added new tables. It never touched the existing content columns because I never wrote a migration for them. Drizzle push, on the other hand, noticed all schema drift and tried to fix everything at once.

Why Postgres Refuses the Automatic Cast

When you run:

ALTER TABLE series ALTER COLUMN content SET DATA TYPE jsonb;
Enter fullscreen mode Exit fullscreen mode

Postgres needs to convert every existing row from text to jsonb, but it can't confirm your text values are valid JSON. If even one row contains plain text, parsing it as JSON would fail. You have to tell it explicitly to attempt the cast.

The fix is the USING clause:

ALTER TABLE series ALTER COLUMN content TYPE jsonb USING content::jsonb;
Enter fullscreen mode Exit fullscreen mode

This casts every value using the ::jsonb operator. If all values are valid JSON, it succeeds. If any aren't, it fails at the specific bad row — which is useful, because it forces you to confront bad data directly.

Ways to Fix Schema Drift

Before any of this: take a backup.

pg_dump $DATABASE_URL > backup_$(date +%Y%m%d).sql

Schema-altering operations are not forgiving. Do this first.

Option 1: USING cast

For each affected column, run the ALTER with an explicit cast:

ALTER TABLE "series" ALTER COLUMN "content" TYPE jsonb USING content::jsonb;
Enter fullscreen mode Exit fullscreen mode

Use this when you're confident all existing values are valid JSON.

If a column has a DEFAULT value, drop it first:

ALTER TABLE "documents" ALTER COLUMN "parsed_content" DROP DEFAULT;
ALTER TABLE "documents" ALTER COLUMN "parsed_content" TYPE jsonb USING parsed_content::jsonb;
Enter fullscreen mode Exit fullscreen mode

Option 2: Null out the bad rows first

If some rows contain invalid JSON:

UPDATE notebooks_blocks
SET content = NULL
WHERE content IS NOT NULL
  AND content NOT LIKE '{%'
  AND content NOT LIKE '[%';

ALTER TABLE notebooks_blocks ALTER COLUMN content TYPE jsonb USING content::jsonb;
Enter fullscreen mode Exit fullscreen mode

Use this when you don't need the non-JSON rows.

Note: The LIKE '{%' filter is a heuristic. A valid JSON string like "hello" starts with ", not { or [. For stricter filtering, use content ~ '^[\[{"]' or reach for jsonb_typeof after a try-cast.

Option 3: Skip the column for now

If rows contain real data you can't afford to lose, leave the column as text and come back when you have a migration plan for the content.

Option 4: Write a proper migration

The right long-term fix is a hand-written migration file:

// migrations/20260427_fix_jsonb_drift.ts
import type { MigrateUpArgs } from "@payloadcms/db-postgres";
import { sql } from "@payloadcms/db-postgres";

export async function up({ db }: MigrateUpArgs): Promise<void> {
  await db.execute(sql.raw(`
    ALTER TABLE "series" ALTER COLUMN "content" TYPE jsonb USING content::jsonb;
    ALTER TABLE "projects" ALTER COLUMN "content" TYPE jsonb USING content::jsonb;
    ALTER TABLE "work_experience" ALTER COLUMN "content" TYPE jsonb USING content::jsonb;
  `));
}
Enter fullscreen mode Exit fullscreen mode

Run it with:

node --env-file=.env.local node_modules/payload/bin.js migrate
Enter fullscreen mode Exit fullscreen mode

This way the fix lives in version control, is reproducible, and is documented alongside the code that caused the drift.

Disable Push Mode in Production

The root cause of this surfacing at all was Drizzle push running against a production database. Push mode is a development convenience and not safe for prod.

In payload.config.ts, disable it outside development:

db: postgresAdapter({
  pool: { connectionString: process.env.DATABASE_URL },
  push: process.env.NODE_ENV === 'development',
}),
Enter fullscreen mode Exit fullscreen mode

In production, schema changes go through payload migrate with explicit, reviewed migration files.

The Lesson

The database and code can diverge for months while the application adapts, types get coerced, and nothing breaks visibly. When something finally tries to reconcile the two, a push, a migration tool, a new ORM version, it all surfaces at once.

Signs you might have schema drift:

  • Your DB was created a long time ago and has never been formally migrated
  • You've upgraded your ORM or database adapter
  • You're running push mode on a database that predates the current schema definition
  • Column types in the DB don't match what your application code declares

How to stay ahead of it:

  • Use migration files for schema changes, even small ones
  • Run payload migrate:status to see if anything is pending before you touch anything
  • Never run push mode against a production database
  • Take a backup (pg_dump) before any schema-altering operation

Resources

Top comments (0)