Backups, Restores & Data Safety

Stage 3 · Data & Databases · B.U.I.L.D. letter: D

You built something real. Users are adding data. Then one morning — a bug in a migration, a forgotten WHERE clause, a dropped table — and it's gone. This lesson is about making sure "gone" is never permanent.

---

⚠️ The vibe trap

You shipped fast, the cloud dashboard has some checkbox labeled "Automated backups," and you clicked it. Done, right? Wrong. A backup you have never restored is not a backup — it is a hope. Hosts get misconfigured, retention windows expire, backup jobs silently fail, and the restore process itself can have bugs you only discover at 2 a.m. when you need it most. Running DELETE FROM orders without a WHERE clause on production, or testing a migration script against the live database because it was "just a quick check" — these are the moves that end products. This lesson closes those gaps before they close you.

---

🗂️ The backup landscape — what you're actually protecting against

Not all data disasters are the same, and each needs a different defense.

Mental model: Think in layers. Replication keeps a copy of your database running in real time (high availability — keeps you up if a server dies). Backups are snapshots in time you can restore from (disaster recovery — gets you back if data is corrupted or deleted). You need both for a serious app. Replication does not save you from DELETE FROM users WHERE 1=1 — it replicates that command instantly to every replica. Only a backup from before the command can save you.

Why this matters: Every managed database service (Supabase, PlanetScale, RDS, Neon) handles physical disk redundancy and high availability for you. What they do not do is make test-restore a habit for you, set your retention window to match your risk tolerance, or stop you from running destructive SQL on the wrong connection.

Common mistake: Treating replication and backups as the same thing. They are complementary, not interchangeable.

---

💾 Automated backups + retention windows

Your host almost certainly takes automated backups. Your job is to know exactly what they cover.

Key questions to answer for your project right now:

1. How often are backups taken? (Hourly? Daily?)
2. How long are they retained? (7 days? 30 days?)
3. What is the granularity? (Full snapshot, or point-in-time recovery down to the minute?)
4. Where are they stored? (Same region? Different region?)
5. How do you actually trigger a restore? (Have you ever done it?)

For Supabase (which this project uses), backups on the free tier are daily with a 7-day retention window. Pro tier adds point-in-time recovery (PITR) down to any second in the last 7 days by default, extendable to 30 days.

The pg_dump/restore workflow — even if your host manages backups, you should know how to take and restore a manual snapshot. This is your escape hatch if you ever need to migrate hosts, test locally, or recover data the managed UI can't reach.

# --- TAKE A MANUAL BACKUP ---
# Replace <connection-string> with your DATABASE_URL (from .env — never hardcode it)
# Output is a plain SQL file you can read and edit if needed

pg_dump \
  --dbname="$DATABASE_URL" \
  --format=plain \
  --no-owner \
  --no-acl \
  --file=backup-$(date +%Y-%m-%d-%H%M%S).sql

# --- VERIFY THE FILE EXISTS AND HAS CONTENT ---
ls -lh backup-*.sql

# --- RESTORE TO A LOCAL TEST DATABASE ---
# Create an empty local DB first:
createdb myapp_restore_test

# Then restore into it:
psql \
  --dbname=myapp_restore_test \
  --file=backup-2026-06-03-140000.sql

# --- SPOT-CHECK THE RESTORE ---
psql --dbname=myapp_restore_test \
  --command="SELECT COUNT(*) FROM users;"

# Compare that count to production before you trust the backup.

Mental model: A backup file is just SQL — a long list of CREATE TABLE and INSERT INTO statements. If you can read it and run it on an empty database and get the same data back, the backup is valid.

Why this matters: Managed backup UIs are convenient, but they can fail silently, have quota limits, or have restore flows that require support tickets during an incident. Knowing pg_dump / psql means you are never fully dependent on a dashboard.

Common mistake: Taking a backup, never opening it, never restoring it, and discovering it was empty or corrupted the one time you needed it. Schedule a test restore into a local or staging DB at least once a month.

---

♻️ Soft deletes — making DELETE reversible

When a user deletes their account, do you want that data gone instantly and forever? Usually no. Maybe they change their mind. Maybe a bug caused it. Maybe a fraudulent actor deleted evidence. Soft deletes let you mark rows as deleted without removing them.

The pattern: add a deleted_at column.

-- Add a soft-delete column to an existing table
ALTER TABLE posts
  ADD COLUMN deleted_at TIMESTAMPTZ DEFAULT NULL;

-- "Delete" a post — just set the timestamp
UPDATE posts
SET deleted_at = NOW()
WHERE id = 'post-uuid-here';

-- Query only active (non-deleted) posts
SELECT id, title, created_at
FROM posts
WHERE deleted_at IS NULL
ORDER BY created_at DESC;

-- Recover a mistakenly deleted post
UPDATE posts
SET deleted_at = NULL
WHERE id = 'post-uuid-here';

-- Permanently purge old soft-deleted rows after 90 days (a scheduled job)
DELETE FROM posts
WHERE deleted_at IS NOT NULL
  AND deleted_at < NOW() - INTERVAL '90 days';

Mental model: deleted_at IS NULL means the row is alive. deleted_at IS NOT NULL means it is in the trash. Your application filters everything through WHERE deleted_at IS NULL so users never see deleted rows — but your database still has them, and you can bring them back.

Why this matters: Hard deletes (DELETE FROM posts WHERE id = ...) are irreversible unless you have a backup and are willing to do a full restore just to recover one row. Soft deletes let you undo mistakes in seconds with a single UPDATE. They also create a natural audit trail — you know exactly when something was "deleted" and can log who did it.

Common mistake: Forgetting to add WHERE deleted_at IS NULL to every query that lists records. One missed query and "deleted" posts start appearing again. The robust fix is a database view or an ORM-level scope that always applies the filter automatically.

-- Create a view so you never forget the filter
CREATE VIEW active_posts AS
  SELECT * FROM posts WHERE deleted_at IS NULL;

-- Now your app queries the view, not the base table
SELECT id, title FROM active_posts ORDER BY created_at DESC;

Common mistake #2: Soft deletes do not replace backups. If a bug corrupts the data inside a row (not just marks it deleted), you still need the backup. Use both.

---

🔒 Guarding destructive queries — the habits that save databases

The single most common self-inflicted data disaster is a missing WHERE clause on a DELETE or UPDATE. Here are the habits that prevent it.

Rule 1: Always SELECT before you DELETE or UPDATE.

-- BAD: run this and instantly regret it
DELETE FROM sessions WHERE user_id = '123';

-- GOOD: run the SELECT first, confirm the rows, then swap DELETE in
SELECT id, user_id, created_at
FROM sessions
WHERE user_id = '123';
-- Output: 3 rows. Good. Now run:
DELETE FROM sessions
WHERE user_id = '123';

Rule 2: Wrap destructive operations in a transaction so you can roll back.

-- The safe pattern for any dangerous UPDATE or DELETE
BEGIN;

-- Step 1: do the operation
UPDATE users
SET plan = 'free'
WHERE trial_ends_at < NOW()
  AND plan = 'trial';

-- Step 2: inspect what changed — does the count look right?
SELECT COUNT(*) FROM users WHERE plan = 'free';

-- Step 3a: if it looks correct, commit
COMMIT;

-- Step 3b: if something is wrong, roll back BEFORE committing
-- ROLLBACK;

Mental model: BEGIN opens a transaction. Nothing is permanent until COMMIT. If you see something wrong — wrong row count, wrong values, a number that is way too large — type ROLLBACK and nothing was changed. This costs you nothing except a few seconds of verification.

Why this matters: PostgreSQL is fully transactional for DML (INSERT, UPDATE, DELETE). You are never forced to commit immediately. The entire database community calls this pattern "read before you write, wrap before you run." It takes 10 extra seconds and has saved countless databases.

Common mistake: Running migrations and destructive scripts directly against the production database connection. The correct flow:

1. Run against your local database first.
2. Run against a staging environment that mirrors production.
3. Only after both succeed, run against production — ideally during low-traffic hours with a backup taken immediately before.

---

🚧 Environment separation — never test against prod

This is the rule that, if broken once, can end a project.

# Your .env files should make the separation impossible to miss
# .env.local (local dev — your laptop)
DATABASE_URL="postgresql://postgres:password@localhost:5432/myapp_dev"

# .env.staging (staging environment — mirrors prod, fake data only)
DATABASE_URL="postgresql://...@staging-host/myapp_staging"

# .env.production (NEVER checked into git, never on your laptop by default)
DATABASE_URL="postgresql://...@prod-host/myapp_prod"

-- Add this comment to every migration or seed script you write
-- ENVIRONMENT CHECK: this script is for DEV/STAGING only
-- Never run against production without a manual backup first.

-- Optionally enforce it in SQL (Postgres-specific trick):
DO $$
BEGIN
  IF current_database() NOT IN ('myapp_dev', 'myapp_staging') THEN
    RAISE EXCEPTION 'Refusing to run: this script is not safe for %',
      current_database();
  END IF;
END $$;

Mental model: Treat production data like a loaded weapon — always know which environment your terminal is pointed at before you run anything. Make it visually obvious: some teams color-code their terminal prompt red when a production DATABASE_URL is active.

Why this matters: It is remarkably easy to have two terminal windows open — one pointed at staging, one at production — and run a destructive script in the wrong one. Environment separation, clearly named .env files, and assertions inside scripts are the layers that prevent this.

Common mistake: Storing the production DATABASE_URL in your shell's global environment (~/.zshrc or ~/.bashrc). This means every script you run picks it up by default, even when you forget. Keep production credentials out of your shell profile entirely. Load them explicitly only when you intend to use them.

---

🛠️ Your mission

Set up real backup protection for your app this week.

1. Audit your host's backup settings. Log into your Supabase dashboard (or whatever DB host you use). Find the backup configuration. Write down: frequency, retention window, restore procedure. If you are on Supabase free tier, note that PITR is a Pro feature and plan accordingly for any project with real users.

2. Take a manual backup and restore it locally. Run pg_dump against your project database (use your .env DATABASE_URL, never hardcode it). Restore the dump to a local test database. Run a row count or two to confirm the data came through. Delete the local test database when done.

3. Add a soft-delete column to your most important table. Write the ALTER TABLE migration. Update your application queries to filter WHERE deleted_at IS NULL. Create a view if your table is queried in many places. Add a deleted_at IS NOT NULL query to confirm you can "restore" a row.

4. Wrap one destructive query in a transaction. Find a place in your codebase or migration history where a DELETE or UPDATE runs. Rewrite it with BEGIN; ... SELECT COUNT(*) ...; -- COMMIT / ROLLBACK; so there is a verification step before anything is permanent.

---

✅ You're done when…

• You can run pg_dump on your project database and restore it to a fresh local DB with matching row counts — a real tested restore, not just a file sitting on disk.
• Your most critical table has a deleted_at TIMESTAMPTZ column and every SELECT query that returns "active" records filters by WHERE deleted_at IS NULL (or a view that enforces it).
• You have written at least one destructive operation (DELETE or UPDATE) inside a BEGIN; ... COMMIT; transaction with a SELECT count check between the operation and the commit.
• Your .env files are clearly named per environment and the production DATABASE_URL is not accessible in your default shell session.
• You know your host's backup retention window and have verified the restore process works — not assumed it, but verified it (cross-reference the Production-Readiness Checklist to confirm all backup and recovery items are signed off).

---

➡️ Next: the Capstone — Design & Build a Real Data Layer. Build It Right, Or Don't Build It At All. 🏛️