Next.js job board: zero-downtime schema changes

• I stopped breaking my job board during migrations.
• I run Postgres changes as additive-only, then backfill.
• I version my Next.js + Supabase queries to survive both schemas.
• I ship a migration checklist that catches the dumb stuff.

Context

I’m building a job board for Psychiatric Mental Health Nurse Practitioners.

It’s Next.js 14 (App Router) on Vercel. Supabase + Postgres behind it.

The data’s not small. 8,000+ active listings. 2,000+ companies. I scrape 200+ jobs daily.

And I kept doing the same dumb thing.

I’d change a column. Deploy. Then pages 500 because one query still expected the old schema. Brutal.

So I switched to a boring rule: schema changes must be zero-downtime. Additive first. Backfill second. Cutover last.

This is the exact pattern I’m using now.

1) I treat migrations like deploys. Because they are.

I don’t run manual SQL in the dashboard anymore.

I did that for weeks. It worked. Until it didn’t.

Now every change is a migration file. Even “tiny” ones.

I keep them in supabase/migrations/ and run them locally with the Supabase CLI. Same SQL in CI. Same SQL in prod.

Here’s a real example: adding a salary_min / salary_max pair to my jobs table without breaking old code.

-- supabase/migrations/202604210901_add_salary_range.sql
-- Additive-only. No renames. No drops.

alter table public.jobs
  add column if not exists salary_min integer,
  add column if not exists salary_max integer;

-- Keep reads fast. I filter on salary_min a lot.
create index if not exists jobs_salary_min_idx
  on public.jobs (salary_min);

-- Safety: prevent negative values.
alter table public.jobs
  add constraint jobs_salary_min_nonneg check (salary_min is null or salary_min >= 0);

alter table public.jobs
  add constraint jobs_salary_max_nonneg check (salary_max is null or salary_max >= 0);

That migration never breaks old code.

Old queries don’t select those columns. Old inserts don’t send them. Postgres fills nulls.

What used to break me was doing the opposite.

Renaming columns mid-flight. Dropping columns “because I’m done with them.” Then Next.js server components explode at runtime.

2) I backfill in small chunks. Not one giant UPDATE.

Backfills are where I used to time out.

I’d run a single update jobs set ... across thousands of rows.

It worked locally. Then in prod, it locked rows long enough to make my API feel flaky.

Now I backfill in chunks. With a loop. With sleep.

I run this as a one-off Node script (locally, pointed at prod), or as a manual GitHub Actions job.

// scripts/backfill-salary-range.ts
// Run: node scripts/backfill-salary-range.ts

import 'dotenv/config'
import { createClient } from '@supabase/supabase-js'

const url = process.env.SUPABASE_URL!
const serviceKey = process.env.SUPABASE_SERVICE_ROLE_KEY!

const supabase = createClient(url, serviceKey, {
  auth: { persistSession: false },
})

const BATCH = 250

async function main() {
  let offset = 0

  while (true) {
    // Pull a stable slice. Order by id so offset is predictable.
    const { data: rows, error } = await supabase
      .from('jobs')
      .select('id, salary_text') // salary_text = scraped string like "$140k-$180k" or "Up to $160,000"
      .order('id', { ascending: true })
      .range(offset, offset + BATCH - 1)

    if (error) throw error
    if (!rows?.length) break

    const updates = rows
      .map((r) => {
        const { min, max } = parseSalary(r.salary_text ?? '')
        if (min === null && max === null) return null
        return { id: r.id, salary_min: min, salary_max: max }
      })
      .filter(Boolean) as Array<{ id: string; salary_min: number | null; salary_max: number | null }>

    if (updates.length) {
      // Upsert by primary key. Only touches rows in this batch.
      const { error: upsertError } = await supabase
        .from('jobs')
        .upsert(updates, { onConflict: 'id' })

      if (upsertError) throw upsertError
    }

    offset += BATCH
    await sleep(200) // small pause. Keeps load sane.
  }
}

function parseSalary(input: string): { min: number | null; max: number | null } {
  // Cheap parser. Works for my scraped sources.
  // Examples: "$140k-$180k", "Up to $160,000", "$120,000 - $150,000/year"
  const s = input.toLowerCase().replace(/,/g, '')

  const upTo = s.match(/up to\s*\$?(\d+(?:\.\d+)?)\s*(k)?/)
  if (upTo) {
    const n = toNumber(upTo[1], upTo[2] === 'k')
    return { min: null, max: n }
  }

  const range = s.match(/\$?(\d+(?:\.\d+)?)\s*(k)?\s*[-–]\s*\$?(\d+(?:\.\d+)?)\s*(k)?/)
  if (range) {
    const min = toNumber(range[1], range[2] === 'k')
    const max = toNumber(range[3], range[4] === 'k')
    return { min, max }
  }

  return { min: null, max: null }
}

function toNumber(n: string, isK: boolean): number {
  const v = Math.round(Number(n) * (isK ? 1000 : 1))
  return Number.isFinite(v) ? v : 0
}

function sleep(ms: number) {
  return new Promise((r) => setTimeout(r, ms))
}

main().catch((e) => {
  console.error(e)
  process.exit(1)
})

This isn’t fancy.

But it avoids long locks, keeps the site responsive, and gives me a place to log weird rows.

One thing that bit me — range(offset, ...) gets slower as offset grows.

If you have millions of rows, switch to keyset pagination (where id > lastId). I’m not there yet.

3) I ship code that can read both schemas

This is the part I skipped before.

I’d add columns. Then immediately refactor the UI to assume they exist.

That’s how you get the classic production error:

column jobs.salary_min does not exist

It happens when:

• Vercel deploy hits before migration ran
• or you have multiple environments and one lags

So I write the query to tolerate missing fields.

In practice: I select the new columns, but I don’t require them.

// app/jobs/[slug]/page.tsx
// Server Component. Reads from Supabase.

import { createClient } from '@supabase/supabase-js'
import { notFound } from 'next/navigation'

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_ANON_KEY!,
)

type JobRow = {
  id: string
  title: string
  company_name: string
  salary_text: string | null
  // New columns. Nullable. Might not exist in older envs.
  salary_min?: number | null
  salary_max?: number | null
}

export default async function JobPage({ params }: { params: Promise<{ slug: string }> }) {
  const { slug } = await params

  // Keep select list explicit. Avoid "select('*')" drift.
  const { data, error } = await supabase
    .from('jobs')
    .select('id,title,company_name,salary_text,salary_min,salary_max')
    .eq('slug', slug)
    .maybeSingle()

  // If the new columns aren't deployed yet, Supabase will error.
  // I fall back to an old select list.
  if (error && /salary_(min|max)/.test(error.message)) {
    const fallback = await supabase
      .from('jobs')
      .select('id,title,company_name,salary_text')
      .eq('slug', slug)
      .maybeSingle()

    if (fallback.error || !fallback.data) notFound()
    return 
  }

  if (error || !data) notFound()
  return 
}

function JobView({ job }: { job: JobRow }) {
  const salary =
    job.salary_min || job.salary_max
      ? `${job.salary_min ?? '—'} to ${job.salary_max ?? '—'}`
      : job.salary_text ?? '—'

  return (

      # {job.title}

      {job.company_name}

      Salary: {salary}


  )
}

Yeah, it’s extra code.

But it lets me deploy UI first or DB first. Either order. No downtime.

Once the migration is everywhere, I delete the fallback.

I actually track that cleanup as a task. Otherwise the fallback lives forever.

4) I don’t drop columns until I’ve proven nothing reads them

Dropping columns is where you discover all your hidden dependencies.

Old scripts. Admin pages. A random cron route you forgot.

So I do a “soft deprecation” first.

Step 1: stop writing the old column.

Step 2: keep reading it for a week.

Step 3: add a DB trigger that screams if something still writes it.

Here’s the trigger pattern. It saved me twice.

-- Run after you've switched your app to stop writing old_salary_text
-- This throws if any insert/update still touches it.

create or replace function public.block_old_salary_writes()
returns trigger
language plpgsql
as $$
begin
  if new.old_salary_text is distinct from old.old_salary_text then
    raise exception 'old_salary_text is deprecated; stop writing it';
  end if;
  return new;
end;
$$;

drop trigger if exists trg_block_old_salary_writes on public.jobs;

create trigger trg_block_old_salary_writes
before update on public.jobs
for each row
execute function public.block_old_salary_writes();

The first time I ran this, it immediately threw.

The culprit was a scrape normalization step I forgot existed.

Spent 4 hours chasing “why are job inserts failing?”

Most of it was wrong.

But after I fixed it, I finally trusted that I could remove the column.

Results

I used this approach while expanding my jobs schema (salary range + a couple metadata fields) on a dataset with 8,000+ active listings and 2,000+ companies.

Before, a schema change usually caused at least 1 broken deploy per week. That meant real 500s on job detail pages until I reverted.

After switching to additive migrations + chunked backfills + dual-schema reads, I did 6 migrations in 9 days with 0 production rollbacks. The scraper kept ingesting 200+ jobs daily the whole time.

No heroics. Just fewer self-inflicted outages.

Key takeaways

• Add columns first. Always. Renames are downtime in disguise.
• Backfill in chunks with pauses. Your DB will thank you.
• Never select('*') on a job board schema. You’ll regret it.
• Ship UI that survives both schemas, then delete the fallback.
• Before dropping anything, block writes with a trigger and see what breaks.

Closing

Schema changes used to scare me more than scraping.

Now it’s a checklist and a couple scripts.

If you’re running Next.js + Supabase in production, you’re one “rename column” away from a bad night.

Do you handle schema cutovers by deploying DB-first, app-first, or do you build dual-schema reads like I do?