Why this migration breaks (and how to avoid it)
An Airtable to Supabase sync sounds straightforward — until you run it twice and end up with duplicate rows. Airtable “just works” because it mixes storage, UI, and computed logic. Supabase is Postgres + APIs — you get more control, but you have to make data integrity explicit.
Common failure modes:
- Row duplication when a sync runs twice or retries after a timeout
- Broken relations (linked records) when you don’t have stable IDs
- “Computed” Airtable fields (rollups/lookups/formulas) getting treated like source-of-truth columns
- Trigger storms (Zapier/automations) causing race conditions
Phase 0 — Map your raw Airtable tables (no computed fields yet)
Goal: mirror Airtable’s base into Supabase with the minimum structure needed for reliable syncing.
- Export your table/field inventory
- Field types (text, select, multi-select, attachment, date, linked record, etc.)
- Which fields are computed (formulas, rollups, lookups) → exclude from Phase 0
- Decide your “Airtable record id” strategy
- Add a column on every Supabase table:
airtable_record_id(TEXT, NOT NULL) - Add a unique constraint:
UNIQUE(airtable_record_id) - Decide how you’ll represent linked records
- Quick + safe early approach: store linked Airtable record IDs in join tables (or as arrays/JSON if you must), then normalize later
- Don’t try to perfectly model every relationship before the pipeline works — you can refactor once the data is stable
Phase 1 — Create Supabase tables + constraints (the anti-duplication foundation)
For each Airtable table:
- Create a Supabase table with:
airtable_record_id(unique)- raw fields you actually need operationally
created_at/updated_attimestamps (optional but helpful)- Add indexes on:
airtable_record_id- foreign keys you know you’ll query heavily
Why it matters: if the database can enforce uniqueness, your sync can retry safely.
Phase 2 — Sync path #1: New rows (Airtable → Supabase)
Option A: Zapier-style (fast to ship)
Use Zapier’s Airtable “New Record” trigger and send a webhook to Supabase (REST) or an Edge Function that inserts.
Implementation checklist:
Airtable trigger: New record in table
Transform: map Airtable fields → Supabase columns
Insert: use UPSERT semantics (not plain insert)
Option B: Script-based (more control)
Poll Airtable’s API (or use webhooks if available for your plan) and write inserts with retries + logging.
Implementation checklist:
Persist “last successful sync cursor”
Write idempotent insert logic (safe to re-run)
Log per-record success/failure for replay
Phase 3 — Sync path #2: Updates (Airtable → Supabase)
New rows are the easy part. Updates are where ops break.
Implementation checklist:
Choose update trigger strategy
- Zapier: “Updated record” (careful: can fire for formula changes too)
- Script: query updated records since last cursor
Make updates idempotent
- Update by
airtable_record_id - Prefer UPSERT over “update then insert” branching
Protect against partial updates
- If your sync doesn’t always send all fields, decide whether missing fields mean “no change” or “clear it”
Phase 4 — Sync path #3: Historical backfill (one-time migration)
Do not backfill first. Backfill after new + update pipelines are stable.
Implementation checklist:
Freeze schema changes during backfill
Backfill table-by-table (or in dependency order)
Batch writes (e.g., 500–5,000 rows at a time) to avoid timeouts
Verify counts + spot-check records
Re-run backfill safely (UPSERT makes this possible)
Duplication prevention patterns that actually work
1) Use Airtable’s record ID as the durable key
Store it and enforce uniqueness:
airtable_record_idis your source-of-truth key- Every write uses it to decide “insert vs update”
2) Use database-level uniqueness + UPSERT
In Postgres terms, you want
INSERT ... ON CONFLICT ... DO UPDATE.In Supabase client terms, you want
.upsert(..., { onConflict: 'airtable_record_id' }) and a unique constraint in the table.3) Treat retries as normal
Zapier retries. Scripts crash. Webhooks duplicate. Assume it’ll happen and design for it.
When to migrate computed fields (rollups/lookups/formulas)
Airtable computed fields are derived, not raw truth.
Do this after the raw pipeline is stable:
- Recreate formulas as SQL views
- Recreate rollups with SQL aggregations + views/materialized views
- Recreate lookups via joins
A practical checkpoint:
- If “new rows” + “updates” have run cleanly for 7–14 days with no duplication incidents → start Phase 2 for computed fields.
Zapier vs Script: which should you pick?
Pick Zapier when:
- You need it working this week
- Volumes are low to moderate
- You can tolerate occasional delay/retry behavior
Pick a script/worker when:
- You need strong observability + replay
- You have high volume or many tables
- You need custom logic (transformations, denormalization, ordering)
Many teams do both:
1) Start with Zapier for speed
2) Replace with scripts once the schema stabilizes
Suggested implementation order (fastest path to “not broken”)
- Raw tables + constraints in Supabase
- New rows sync (ship it)
- Updates sync (stabilize it)
- Historical backfill (migrate old data)
- Relations cleanup + normalization
- Computed fields via SQL (views/rollups)
Get help building this
An Airtable to Supabase sync usually breaks at the same two points: the first time a webhook retries and creates a duplicate, and when backfill runs against a schema that wasn’t built for it. If you’ve hit that wall, book a ZoomFlow session — one of our consultants can walk through your schema, set up the UPSERT constraints, and get your sync running cleanly in a single call.
