The Problem

You’ve decided to migrate from Sheets (or AppSheet, or Excel) to PostgreSQL. The business can’t stop while you migrate — orders are still coming in, customers are still being created, and someone is still editing the old sheet.

You need a migration that is:

• Zero-downtime. The old system stays live during the migration.
• Idempotent. You can re-run it safely — it won’t duplicate rows.
• Verifiable. You can prove row counts and aggregates match between old and new.
• Reversible. If something goes wrong on cutover day, you can switch back in minutes.

Here’s the pattern I’ve used on multiple migrations. It works whether your source is Google Sheets, AppSheet, a CSV export, or another API.

---

The Pattern: Idempotent Cursor + Parallel Run

Step 1: Read with a watermark

Never do a full extract on every run. Use an updatedAt timestamp as your cursor:

-- On the first run, create a migration_state table
CREATE TABLE migration_state (
  source     TEXT PRIMARY KEY,
  last_cursor TIMESTAMPTZ NOT NULL DEFAULT '1970-01-01'
);

-- Each sync run: fetch rows updated since the cursor
SELECT * FROM source_customers
WHERE updated_at > (SELECT last_cursor FROM migration_state WHERE source = 'sheets')
ORDER BY updated_at ASC;

Step 2: Upsert, never INSERT

Use ON CONFLICT with a stable business key — not an auto-increment ID from the source, which won’t survive a migration:

INSERT INTO customers (external_id, name, email, updated_at)
VALUES ($1, $2, $3, $4)
ON CONFLICT (external_id) DO UPDATE SET
  name      = EXCLUDED.name,
  email     = EXCLUDED.email,
  updated_at = EXCLUDED.updated_at;

The external_id is your bridge — it ties the Postgres row back to the source row. Keep it forever, at least in an indexed column.

Step 3: Advance the cursor only after commit

// Pseudocode for a single sync batch
const rows = await fetchSince(lastCursor, BATCH_SIZE);
for (const row of rows) {
  await db.upsert('customers', row);
}
// Only update the cursor AFTER the batch succeeds
await db.query(
  `UPDATE migration_state SET last_cursor = $1 WHERE source = 'sheets'`,
  [rows.at(-1).updatedAt]
);

If the script crashes mid-batch, the cursor stays at the last committed position. Re-run it — idempotent.

Step 4: Validate every night

During the parallel run (typically 1–2 weeks), run a validation script:

-- Row count parity
SELECT 'customers' AS entity, COUNT(*) FROM customers
UNION ALL
SELECT 'sheets_customers', COUNT(*) FROM sheets_import.customers;

Then spot-check aggregates — total revenue by month, orders per customer, anything the business cares about. If they match for 5 consecutive nights, you’re ready to cut over.

---

The Cutover Checklist

When both systems have matched for at least 5 days:

• Freeze writes to the old system. Announce a 2-hour window. If it’s Sheets, protect the range.
• Run one final sync. Catch the last few rows created since the last nightly validation.
• Run the validation one more time. If clean, proceed.
• Switch DNS / config. Point the app at the new database. Keep the old connection string in an env var.
• Smoke-test. Log in, create a record, check it appears. Check a few existing ones.
• Keep the old system read-only for 72 hours. If a panic-rollback is needed, you’re switching DNS back — not restoring a backup.

---

When This Pattern Breaks (and what to do)

Scenario	Fix
Source has no updatedAt column	Add one. If you can’t, use a hash of all columns as a change detector — slower, but works.
Source supports hard deletes	You need a full-diff approach: compare IDs in source vs target and soft-delete missing ones. Add a deleted_at column to Postgres instead of hard-deleting.
Schema changes during migration	Freeze the source schema for the duration of the parallel run. If it must change, restart the cursor from zero after the change lands in Postgres.
Millions of rows, no batch pagination	Use keyset pagination: WHERE updated_at > $cursor ORDER BY updated_at, id LIMIT 1000. Never use OFFSET on large tables.

---

Real Examples

This exact pattern powered the migration in two case studies:

• Migrating an AppSheet CRM to Express + PostgreSQL — ETL from AppSheet API, parallel run, zero data loss.
• Building a Multi-tenant CRM — schema-per-tenant isolation with per-tenant exports via the same upsert pattern.

---

TL;DR

Pattern	Idempotent ETL: cursor → upsert → advance cursor → validate nightly
Key technique	ON CONFLICT (external_id) DO UPDATE with a stable business key, never auto-increment
Parallel run	1–2 weeks; run validation every night; cut over when aggregates match for 5+ days
Rollback	Keep old system read-only for 72h; switch DNS back — don’t restore a backup
Edge cases	No updatedAt → hash diff. Hard deletes → full diff. Schema changes → freeze or restart.