Surgery on a live database

Early on, the Trad Directory made a simplifying assumption that turned out to be a lie: one artist works at one shop in one city. Real tattoo artists guest-spot constantly. They split time between two cities. They leave a shop and the old link is now wrong. My schema modeled artist.city_id and a single shop link, and that model was quietly rotting.

Fixing it meant changing the shape of a database that has real traffic on it. You can’t just take it down, run some ALTER TABLEs, and bring it back — well, you can, but people hitting the site during that window get errors, and I’d rather not. The answer is a pattern called expand-contract, and running it end to end is the most genuinely “senior engineer” thing this project ever forced me to do.

The idea: never break the running thing

Expand-contract is three moves, spread out over as much time as you need:

1. Expand — add the new shape alongside the old one. Nothing reads it yet. Totally safe.
2. Migrate — start writing to both, backfill history, flip reads over to the new shape.
3. Contract — once nothing touches the old shape, drop it.

The trick is that at no single moment is the database in a broken state. Old code and new code both work throughout. Let me walk the actual surgery.

Expand: a table nobody reads yet

First, the new shape in the type layer. An artist grows a locations array, while keeping the old flat fields so nothing that reads them breaks:

export interface ArtistLocation {
  city_name: string;
  state_name: string;
  country_name: string;
  shop_id?: number | null;
  is_primary: boolean;
}

export interface Artist {
  id: number;
  name: string;
  city_name: string;      // legacy flat fields kept for compat
  shop_name: string;
  locations: ArtistLocation[];   // <- the new array
}

Then the table. Note the comment — this is the whole philosophy in two lines. It creates a junction table and backfills it from existing data, and it touches nothing that already exists:

-- This is ADDITIVE ONLY - does not modify existing tables or columns
CREATE TABLE artist_location (
  id BIGSERIAL PRIMARY KEY,
  artist_id BIGINT NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
  city_id BIGINT REFERENCES cities(id) ON DELETE SET NULL,
  shop_id BIGINT REFERENCES tattoo_shops(id) ON DELETE SET NULL,
  is_primary BOOLEAN NOT NULL DEFAULT true,
  UNIQUE(artist_id, city_id, shop_id)
);

INSERT INTO artist_location (artist_id, city_id, shop_id, is_primary)
SELECT a.id, a.city_id, ash.shop_id, true
FROM artists a
LEFT JOIN artist_shop ash ON ash.artist_id = a.id;

At this point the new table exists and mirrors the old data, and the live site has no idea. If I’d stopped here and walked away for a month, nothing would have broken.

Migrate: two sources of truth, on purpose

This is the phase that feels wrong and is correct. Every write endpoint now dual-writes — it updates the old columns AND the new table:

// Update artist_location primary row when city_id or shop_id changes
if (data.city_id !== undefined || data.shop_id !== undefined) {
  const { data: existingLoc } = await supabase
    .from("artist_location").select("id")
    .eq("artist_id", id).eq("is_primary", true).maybeSingle();
  if (existingLoc) {
    await supabase.from("artist_location").update(locUpdate).eq("id", existingLoc.id);
  } else {
    // No primary row exists yet — insert one
    await supabase.from("artist_location").insert({ artist_id: id, /* ... */ is_primary: true });
  }
}

Keeping two representations in sync is exactly the kind of thing you’re taught to avoid — until you’re mid-migration, when it’s the only safe way across. One honest wart: if that location write fails it logs a warning, it doesn’t roll back the artist update. So an artist could end up with no location row. Which is exactly why the next step exists.

Because writes only fix rows as they’re touched, the artists nobody edited still had gaps. So a standalone backfill script swept the whole table and inserted location rows for any artist that was still missing one. Boring, essential, the unglamorous middle of every migration.

Contract: the scary part

Months later, everything reads and writes artist_location. Time to drop the old shape — the genuinely destructive, no-undo step. By then I’d moved onto the Supabase CLI migration workflow, so this was a real ordered pipeline of timestamped migrations: expand a couple more columns, one final idempotent backfill to consolidate city_id, secondary_city_id, and the old artist_shop links, add a constraint enforcing exactly one primary location per artist, deploy the code cutover — and only then, contract:

-- P5 Contract (destructive, final): drop the legacy location sources now that
-- everything reads/writes artist_location. Run ONLY after the code cutover is
-- deployed. A fresh backup is taken before this is pushed to prod.
drop view if exists "public"."artist_shop_view";
drop table if exists "public"."artist_shop";
alter table "public"."artists" drop column if exists "secondary_city_id";
alter table "public"."artists" drop column if exists "city_id";

I took a full backup right before running it. Two of them, actually — schema and data — sitting in a folder named precontract. When the operation is DROP COLUMN on a live production table, you want a parachute you’ve actually checked.

One nice detail on the “expand” migrations: adding a NOT NULL column with a default to a big live table used to mean a full table rewrite and a lock. Postgres 11+ stores the default in the catalog instead, so it’s instant. The migration says so out loud, because that assumption is the only reason it’s safe to run on a live table:

-- Additive and safe: NOT NULL columns with a default don't rewrite existing
-- rows (Postgres 11+ stores the default in catalog)...
add column if not exists "role" "text" not null default 'resident',

While I had the patient open

Two other things rode along with the schema work. I ripped out the Redis/ioredis caching layer entirely — it was premature for this traffic, an extra moving part earning nothing. Reads are fast enough straight off Postgres with tiny in-memory session caches on the frontend.

And I finally locked the doors. Every write and admin endpoint now runs through one bearer-token gate — 25 handlers importing the same check:

const authHeader = req.headers["authorization"];
if (!authHeader || authHeader !== `Bearer ${adminApiKey}`) {
  res.status(401).json({ error: "Unauthorized" });
  return false;
}
return true;

Not fancy. A single shared secret for a single-operator tool. But before this, some write endpoints were just… open. That’s the kind of thing you fix quietly and don’t brag about, except here I am.

What I actually learned

The lesson isn’t the SQL — it’s the discipline. Expand, backfill, contract, and never let old and new be broken at the same moment. Take the backup before the destructive step, every time, not just when you remember. It’s slower and more boring than “just change the table,” and that slowness is the entire point: the site never went down, and no user ever knew their favorite artist’s record quietly grew the ability to live in two cities at once.