Good. Only three changes needed: fix the grammar artifact, scrub the two leaked firm names. Everything else stays.

TLDR: When you ask an LLM to produce a schema mapping, prompt clarity isn't enough. Validate at the boundary and halt loudly — or you'll get silent, empty rows and wonder why your migration "worked."

The Setup

We needed to move my business partner's entire law firm practice out of Airtable (spreadsheet-database she'd been running her firm in) and into the custom practice management system we'd been building for her.

72 contacts. 29 matters (legal cases/files). Dozens of linked-record relationships to reassemble into junction rows (the link table connecting contacts to matters by role).

Not terrible in raw numbers. But the field mapping — which Airtable column lands in which Supabase column — was genuinely messy. So I had what felt like a GENIUS idea.

The Bright Idea

Why not let claude-opus-4-7 propose the mapping?

I built inspect.mjs (Phase 1 of the migration pipeline under scripts/migrate-airtable/) to pull the Airtable schema, hand it to the model, and ask for a JSON object: Airtable field name → Supabase column name. Machine-readable. Human-reviewable. I could edit approvals.json before anything destructive ran.

Clean. Elegant. Almost worked.

Two Bugs I Didn't See Coming

Bug #1 — Names vs. IDs.

Airtable fields have both a human name (First Name) and a machine ID (fldXXX). My schema prompt listed both, because that's what the API gives you. The model sometimes emitted field IDs as mapping keys.

The problem? record.fields is keyed by name. So record.fields["fldXXX"] is always undefined. Every row would "import" with those columns silently blank. No error. No warning. Just gone.

Bug #2 — Prose Drift on Relationship Labels.

For linked-record fields like Client and Opposing Party, I needed the model to tell me what each one represents — so I could match it against a relationship-types lookup. I asked for a short label.

What I got from a real run:

"represents": "Primary client contact"

My lookup expected "Client". It found "Primary client contact". No match. Silent mismatch (until I added the guard that caught it).

What I Tried First (and Why It Wasn't Enough)

I tightened the prompt. Told it explicitly: keys must be field NAMES, not IDs. Values for represents must be single short labels — "Client", "Opposing Party" — maximum three words, not prose.

And it got better. But it still drifted, occasionally, on different runs. With a different record shape, or a longer schema, the model would slip back.

That's when it clicked: I was trying to prompt my way out of a validation problem.

The Fix That Actually Worked

Two runtime guards, added to the mapping loader:

  1. Build a Set(Object.keys(record.fields)) from a sample of real records. Before running, check every mapping key against it. If a key isn't in the set — warn immediately, with the exact key.

  2. When matching a represents value against the relationship-types lookup, halt with "found 'X', expected one of [Client, Opposing Party, Related Parties]" — not a silent fallback.

Fail loudly, at the boundary, before anything writes.

The Result

72/72 contacts imported. 29/29 matters. 49 junction rows synthesized. One logged role conflict between two parties — kept Opposing Party, flagged for my business partner to re-tag.

Production went live that night.

Why This Sticks With Me

The prompt instructions mattered — they reduced drift. But the runtime guard is the thing that made the migration trustworthy. LLMs drift. Even with explicit constraints, on a slightly different schema or a longer context, the model will produce output you didn't ask for.

The lesson I'd hand to any builder: write the clearest prompt you can, then validate as if you wrote no prompt at all.

P.S. There's a second foot-gun hiding in Airtable's own ID system — tbl prefixes (table IDs) look almost identical to app prefixes (base IDs) in the URL. I caught that one before it burned us. Worth a glance at ref_airtable_id_prefixes.md if you're building anything Airtable-adjacent.