Data Outlives Code

On the evening of 31 January 2017, an engineer at GitLab was trying to fix database replication. A secondary database server had fallen behind, and the plan was to wipe its data directory and copy everything over again from the primary.

He ran the wipe on the wrong server.

He noticed a second or two later and stopped it. By then, according to GitLab’s own postmortem, around 300 GB of data was gone from the primary database, the one GitLab.com was running on.

That’s a bad night, but it should have been a recoverable one. GitLab had several layers of protection: daily dumps, disk snapshots, LVM snapshots and a replica. Then the team found out what each was actually worth:

The daily pg_dump backups to S3: the bucket was empty. The backup script used an older pg_dump version than the database, so it had been failing. The failure emails were being rejected, so nobody knew.
Disk snapshots on Azure: not enabled for the database servers.
Replication: the thing they had been trying to fix.
What saved them was an LVM snapshot (a copy of the disk at one moment) that an engineer happened to take by hand about six hours earlier, for a different reason.

GitLab.com was down for about 18 hours while that copy was restored. Database changes from roughly six hours were lost: GitLab’s best estimate was at least 5,000 projects, 5,000 comments and about 700 new user accounts. Git repositories themselves were fine.

GitLab published all of this in public, in detail, which is one of the most useful things a company has ever done for the rest of us. No AI was involved. The lesson is older than AI: code can be rewritten. Data that’s gone is gone.

This is Part 5 of After Vibe Coding. The skill: treating data as the thing that matters most, and designing so mistakes are survivable.

Why this gets more urgent with AI tools

In July 2025, SaaStr founder Jason Lemkin was building an app with Replit’s AI agent. Around day nine, after he had told it to freeze changes, the agent deleted his live database, then told him it couldn’t be rolled back. It could. He got the data back from a backup. I told the full story in “Built With AI in a Weekend”: What Happened Next, so I’ll keep it short here.

What’s interesting is Replit’s fix. Its CEO called the incident unacceptable and announced automatic separation of development and production databases, a planning-only mode, and easier restores from backup. None of those are AI features. They’re the same boring safeguards GitLab wished it had in 2017.

AI tools change the risk in three ways:

Code got cheap; data didn’t. If an AI writes bad code, you throw it away and regenerate. If it writes a bad migration that drops a column, there’s nothing to regenerate. That asymmetry is the whole post.
Agents run commands. An assistant that suggests SQL is one thing. An agent with a production connection string can run DROP TABLE faster than you can read it.
AI loves a clean slate. Ask it to “fix the schema” and a common answer is to drop and recreate the table. On your laptop, that’s fine. On real data, it’s a disaster.

Skill 1: a schema that defends itself

Your app code will be rewritten many times, by you, by teammates and by AI. The database schema is the one place where rules are enforced no matter which code is talking to it. So put the important rules there.

Here’s a schema the way AI tools often write it when nobody asks for more:

-- BAD: everything optional, nothing checked
create table bookings (
  id serial primary key,
  customer_id integer,
  check_in text,
  check_out text,
  total numeric,
  status text
);

Every column can be empty. Dates are text, so "next Tuesday" is valid. A booking can point at a customer who doesn’t exist. Status can be "Confirmed", "confirmed" or "confrimed".

-- FIXED: the database refuses bad data
create table customers (
  id bigint generated always as identity primary key,
  email text not null,
  full_name text not null,
  deleted_at timestamptz,
  created_at timestamptz not null default now()
);

-- one active customer per email, ignoring case
create unique index customers_email_active
  on customers (lower(email)) where deleted_at is null;

create table bookings (
  id bigint generated always as identity primary key,
  customer_id bigint not null references customers (id) on delete restrict,
  check_in date not null,
  check_out date not null,
  total_cents bigint not null check (total_cents >= 0),
  currency char(3) not null,
  status text not null check (status in ('pending', 'confirmed', 'cancelled')),
  created_at timestamptz not null default now(),
  check (check_out > check_in)
);

Each piece does one job:

not null: this value must exist. Default to not null, and make a column optional only when you can say what “empty” means.
references (a foreign key): a booking must point to a real customer. on delete restrict means you can’t delete a customer who still has bookings, so you never get orphan rows.
unique: no two active customers with the same email. Here it’s a partial unique index, which we’ll use again for soft delete below.
check: simple rules about values. Money can’t be negative, check-out must be after check-in, status must be one of three words.
Proper types: date for dates, timestamptz for moments in time, whole-number cents for money. I go deep on why in Time, Money and IDs: The Data Types That Bite.

Each of those rules turns a quiet data bug into a loud error at write time. Try to insert check-out before check-in, a status of 'paid', a booking for customer 999, or a second AN@example.com, and Postgres rejects every one. A loud error today is much cheaper than bad data you discover in six months.

If you’re building something with many customer accounts in one database, the same thinking applies to tenant_id, which I covered in Multi-Tenant SaaS Database Design.

Skill 2: migrations you can trust

A migration is a small, versioned script that changes the schema: add a column, create an index, rename a table. Tools like Prisma Migrate, Drizzle Kit, Rails, Django, Flyway and plain numbered SQL files all do the same thing: keep a list of changes, record which ones ran, and run the new ones in order.

A few rules that save a lot of pain:

Never edit a migration that has already run somewhere else. If it ran on production, it’s history. Fix mistakes with a new migration. AI tools will happily “clean up” an old migration file, so watch for that in diffs.

Prefer forward-only in production. Many tools let you write a “down” migration to undo a change. They’re useful in development. In production, a down migration that drops a column you just added also drops any data written to it since. Often the safer undo is another forward migration.

Know which changes are dangerous. Dropping a column or table destroys data. Renaming a column breaks every running copy of the old code instantly. Adding not null to a big table, or an index without concurrently, can lock the table while it works. On a small table none of this matters. On a busy one, “the migration took the site down” is a real outcome.

Expand and contract

The pattern that makes risky changes safe is called expand and contract (also “parallel change”). Instead of changing something in one step, you add the new thing, move over gradually, then remove the old thing.

Say you want to replace guests.name with guests.display_name. A one-step rename column breaks any server still running the old code during deploy. Instead:

Expand and contract turns one risky change into several boring ones, and only the last step is irreversible.

In SQL, the database side looks like this. Each block is its own migration, shipped separately, with app deploys in between:

-- Migration 1 (expand): new column, nullable, so old code is unaffected
alter table guests add column display_name text;

-- Deploy: app now writes BOTH name and display_name on every insert/update.

-- Migration 2 (backfill): small batches, run repeatedly until 0 rows change
update guests set display_name = name
where id in (
  select id from guests
  where display_name is null
  order by id
  limit 1000
);

-- Deploy: app now reads display_name.

-- Migration 3: enforce NOT NULL without a long lock.
-- NOT VALID skips checking existing rows; VALIDATE checks them under a lighter lock.
-- A valid CHECK lets SET NOT NULL skip its own full-table scan.
alter table guests add constraint guests_display_name_nn
  check (display_name is not null) not valid;
alter table guests validate constraint guests_display_name_nn;
alter table guests alter column display_name set not null;
alter table guests drop constraint guests_display_name_nn;

-- Deploy: app stops writing name. Wait. Take a backup. Then:

-- Migration 4 (contract): the only step you can't undo
alter table guests drop column name;

It feels slow. It’s four small pull requests instead of one. But every step can be deployed on a Tuesday afternoon without anyone noticing, and if something looks wrong at step 3, you stop, with no data lost.

Two smaller habits from the same family: create indexes on big tables with create index concurrently (which can’t run inside a transaction, so some migration tools need a flag for it), and when adding a column with a default, use a fixed default value, which Postgres 11 and later apply without rewriting the table.

Skill 3: backups you have actually restored

GitLab had backups. On paper, it had several kinds. What it didn’t have was proof that any of them worked.

A backup you haven’t restored is a hope, not a backup. The only test that counts is restoring it somewhere and checking the data is there.

For Postgres, a basic version looks like this:

#!/usr/bin/env bash
set -euo pipefail

STAMP=$(date -u +%Y%m%dT%H%MZ)
FILE="backup-$STAMP.dump"

# 1. Back up (custom format: compressed, restorable table by table)
pg_dump --format=custom --file="$FILE" "$PROD_READONLY_URL"

# 2. Restore into a throwaway database
dropdb --if-exists restore_check
createdb restore_check
pg_restore --no-owner --dbname=restore_check "$FILE"

# 3. Check it's real data, not an empty or stale shell
RECENT=$(psql --dbname=restore_check --tuples-only --no-align --command \
  "select count(*) from bookings where created_at > now() - interval '2 days';")
if [ "$RECENT" -eq 0 ]; then
  echo "Restore check failed: no recent bookings in $FILE" >&2
  exit 1
fi

# 4. Only now ship it off-site (your storage of choice), and alert on ANY failure

Some notes on that script:

set -euo pipefail makes the script stop at the first failure instead of carrying on. GitLab’s dump job failed for a long time without anyone noticing.
Step 3 checks for recent rows. An empty or stale backup fails the check.
Whatever runs this must alert loudly when it fails, through a channel you actually watch. Silence should never mean “fine”.

Most managed databases (RDS, Cloud SQL, Supabase, Neon and others) offer automatic backups and point-in-time recovery, which lets you restore to a specific minute, not just last night. Check what your plan includes, how many days it keeps, and do one practice restore before you need one. Write down how long it took. That number is how long you’ll be down on your worst day.

Skill 4: production credentials live somewhere else

The simplest protection against an agent or a tired human wrecking production is that the production connection string isn’t on the machine where the experimenting happens.

Development uses a local or separate database with fake or copied-and-scrubbed data.
.env on your laptop points at dev. Production secrets live in your hosting platform’s secret settings, not in a file in the repo.
Use different database names, users and even colours in your tools for production, so it’s obvious where you are.
If you need production data to debug, use a read-only connection or a recent restored copy, not the live primary with full rights.

This is exactly what Replit made automatic after the SaaStr incident. You can do it yourself on day one.

Skill 5: give AI agents the least privilege that works

AI agents increasingly connect to databases directly, through tools like MCP servers or a connection string in their environment. Decide what that connection is allowed to do before the agent starts, in the database itself, not in the prompt. “Please don’t delete anything” is a request. A missing permission is a wall.

-- A read-only role for an AI agent or analytics tool
create role ai_agent_readonly login password 'use-a-generated-secret';

grant connect on database app to ai_agent_readonly;
grant usage on schema public to ai_agent_readonly;
grant select on all tables in schema public to ai_agent_readonly;

-- tables created later are readable too
alter default privileges in schema public
  grant select on tables to ai_agent_readonly;

-- extra seatbelt: sessions start read-only by default
alter role ai_agent_readonly set default_transaction_read_only = on;

With this role, select works, delete from guests fails with “permission denied”, and drop table guests fails because the role doesn’t own the table. The default_transaction_read_only line is only a seatbelt, since a session can switch it off. The grants are the real protection.

If an agent truly needs to write, give it a role that can insert and update specific tables, never delete or drop, and never ownership of the schema. Run migrations with a separate, more powerful role that only your deploy pipeline uses, so schema changes always go through review.

Skill 6: soft delete, hard delete, and knowing which one you need

A hard delete removes the row. A soft delete marks it as deleted and hides it:

-- soft delete
update customers set deleted_at = now() where id = $1;

-- everyday reads use a view that hides deleted rows
create view active_customers as
  select * from customers where deleted_at is null;

Soft delete is great for “oops” buttons, audit history and support (“I deleted that by accident, can you bring it back?”). The partial unique index from Skill 1 (where deleted_at is null) is what lets a customer delete their account and later sign up again with the same email.

It has real costs too:

Every query must remember the filter. Forget deleted_at is null once and “deleted” data shows up. Using a view, or an RLS policy, makes the right thing the default.
Soft-deleted isn’t erased. If a customer asks you to delete their personal data, privacy laws in many places expect it to be really gone. Plan a purge job that hard-deletes or anonymises soft-deleted rows after a set period.
Foreign keys don’t know about it. A soft-deleted customer still “has” bookings. Decide what that means for your product.

My default: soft delete for things users create and might want back, hard delete with a purge schedule for personal data once it’s no longer needed, and never a hard delete without a recent tested backup.

How to build this skill while using AI

1. Ask for constraints by name. Don’t just say “create a bookings table”. Say “Postgres table for bookings: not null by default, foreign key to customers with on delete restrict, money as bigint cents, a check that check_out is after check_in, and a status check.” AI tools follow schema rules well. They rarely add them unasked.

2. Make every migration a reviewed file. Have the AI generate a migration file, never run schema changes directly against a shared database. Then ask a second prompt: “Is anything in this migration destructive or likely to lock a large table? Would old code still work while it runs?”

3. Give agents a sandbox, not the keys. Local database, seeded fake data, read-only role if it must see production. Tell the agent which database it’s on, and make it impossible, not just discouraged, to reach the other one.

4. Schedule a restore drill. Once a month or once a quarter, restore last night’s backup into a scratch database and run your check query. If it takes three hours to figure out, better now than during an outage.

5. Read migrations slowly. In a diff, these words deserve a pause: drop, truncate, delete from without where, alter column ... type, rename, cascade, and any edit to an old migration file.

Checklist

Key ideas

Code is cheap to regenerate. Data isn’t. Protect the thing you can’t get back.
Put the important rules in the schema. Constraints hold no matter which code, human or AI, is writing.
Change schemas in small, reversible steps. Expand, migrate, contract. Only the final drop is a one-way door.
A backup you haven’t restored is a hope. GitLab had several methods, and was saved by a snapshot someone took by hand for another reason.
Keep production credentials away from experiments. Replit’s fix after the SaaStr incident was separation, not smarter AI.
Enforce agent limits in the database, not the prompt. A missing permission beats a polite instruction.
Soft delete is a feature with costs. Filter by default, and purge personal data on schedule.

Sources: GitLab, “Postmortem of database outage of January 31” (10 February 2017). SaaStr and Replit: eWeek and TechTarget (July 2025). PostgreSQL documentation, ALTER TABLE (NOT VALID constraints, SET NOT NULL with a valid CHECK, ADD COLUMN defaults).