Airtable ↔ MES Sync Runbook

This file is the single source of truth for the Airtable Product Master (Style / Color / Size) ↔ MES sync system. It covers:

1. Overview — the three tickets that ship the system and how they fit together.
2. Webhook sync (INFRA-444) — Airtable Automation → MES push, per-table API keys, request/response shapes.
3. Airtable view configuration (INFRA-449) — the locked MES Sync Source views and the Automation gate they mirror.
4. Backfill scripts (INFRA-429) — npm commands, env vars, expected log shape, DO console procedure, dry-run guidance.
5. Field mapping reference — for both webhook payloads (camelCase) and direct REST reads (raw Airtable column headers).
6. Troubleshooting — soft-delete collisions, P2002 surfaces, orphan rows, key rotation.

---

1. Overview

Three tickets together implement the system:

Ticket	Direction	Trigger	What it produces
INFRA-444	Airtable → MES	Live Automation push	Webhook endpoint per table (Style/Color/Size) that upserts MES rows by airtableRecordId.
INFRA-449	Airtable side	Configuration only	The MES Sync Source views and the Automation When record matches conditions gate. Both share the same filter so backfill and webhook accept the same set of records.
INFRA-429	Airtable → MES	Manual (npm script)	One-shot backfill scripts that read the MES Sync Source views and insert into MES. Idempotent — safe to re-run.

The webhook handles steady-state changes. The backfill handles initial population, recovery from missed webhooks, and manual reconciliation.

Push-only. MES is a webhook receiver and a one-way reader. It never calls back to Airtable to write or trigger; the Airtable Automation is authoritative for what gets synced.

---

2. Webhook sync (INFRA-444)

Endpoints

Endpoint	Auth	Body schema
POST /api/webhooks/v1/airtable/style	X-API-Key: $AIRTABLE_WEBHOOK_STYLE_API_KEY	AirtableStyleWebhookBodySchema
POST /api/webhooks/v1/airtable/color	X-API-Key: $AIRTABLE_WEBHOOK_COLOR_API_KEY	AirtableColorWebhookBodySchema
POST /api/webhooks/v1/airtable/size	X-API-Key: $AIRTABLE_WEBHOOK_SIZE_API_KEY	AirtableSizeWebhookBodySchema

Each route binds its own API key via makeAirtableWebhookKeyAuth(env.AIRTABLE_WEBHOOK_<TABLE>_API_KEY) — a key leaked for one table cannot be replayed against another. Rotate keys independently.

Request body shape (Style example)

{
  "event": "record_updated",
  "recordId": "recABC123",
  "styleNumber": "S-100",
  "styleName": "Boxy Tee",
  "timestamp": "2026-04-01T00:00:00Z"
}

event and timestamp are accepted (and logged) but the service detects create vs update by looking up the existing row by airtableRecordId. Field-name mapping is camelCase here (see § 5).

Response envelope

{ "statusCode": 200, "recordId": "recABC123", "action": "updated" }

action is "created" if neither the airtableRecordId nor the natural key matched an existing row; otherwise "updated".

Collision behavior

The webhook is strict: any collision throws 400 Bad Request with a human-readable message naming the conflicting row.

• Natural-key collision (two Airtable records claiming the same styleNumber/colorCode/sizeCode/size) → operator resolves the duplicate in Airtable and retries.
• Soft-delete collision (a soft-deleted MES row holds the natural key or airtableRecordId) → operator either restores or hard-deletes that row in MES, then retries.

This is the deliberate difference vs the backfill scripts, which log and continue on collisions so a batch run isn't aborted by one bad row.

Rotating a webhook API key

1. Generate a new key: openssl rand -hex 32.
2. Set it in the deployed environment as AIRTABLE_WEBHOOK_<TABLE>_API_KEY and redeploy.
3. Update the matching Airtable Automation's request header.
4. Trigger one test record in Airtable; confirm the webhook returns 200 in MES logs.
5. Revoke the old key (remove from any local .env copies).

---

3. Airtable view configuration (INFRA-449)

The MES Sync Source view

Each of Style, Color, Size has a view named MES Sync Source (its ID — viw... — is the value of AIRTABLE_VIEW_ID_<TABLE> in MES). The view is locked to prevent ad-hoc filter edits in the UI.

Filter ≡ Automation gate

The view's filter expression and the Automation's When record matches conditions trigger must be identical. The backfill scripts read from the view (admitting exactly the records the view's filter passes), and the live webhook is triggered by the Automation. Identical filters guarantee both paths accept the same record set — no record qualifies via backfill that the webhook would reject (or vice versa).

Updating the filter

Any change to what qualifies for sync requires updating both in lockstep:

1. Unlock the view, change the filter, re-lock.
2. Edit the matching Automation's trigger condition.
3. Run npm run backfill:airtable:<table> -- --dry-run against staging to confirm the new record set looks sane.
4. Trigger one matching record in Airtable to confirm the webhook still fires.

If filter and Automation gate drift apart, the symptom is a record that appears in the view but the webhook never fires for it (or vice versa — webhook fires but the backfill misses).

Why IDs over names

Use view IDs (viw...) and table IDs (tbl...) in env vars rather than display names. IDs are stable across renames; names are not.

---

4. Backfill (INFRA-429)

One-shot scripts that read the MES Sync Source views and insert new records into MES.

npm commands

# Individual tables
npm run backfill:airtable:styles
npm run backfill:airtable:colors
npm run backfill:airtable:sizes

# All three sequentially (Style → Color → Size)
npm run backfill:airtable

# Pass --dry-run (no DB writes; logs what would happen)
npm run backfill:airtable:styles -- --dry-run
npm run backfill:airtable -- --dry-run

# Help
npm run backfill:airtable:styles -- --help

Required env vars

Variable	Purpose
AIRTABLE_API_TOKEN	Personal access token (Bearer auth). Scope: data.records:read on the Product Master base.
AIRTABLE_BASE_ID	Base ID (starts with app...).
AIRTABLE_TABLE_ID_STYLE / _COLOR / _SIZE	Table IDs (starts with tbl...).
AIRTABLE_VIEW_ID_STYLE / _COLOR / _SIZE	MES Sync Source view IDs (starts with viw...).
DATABASE_URL	MES database URL. Same as the running app.

Plus all other vars declared in src/utils/envConfig.ts — the scripts share Prisma model wrappers, which transitively load envConfig at startup.

Insert logic (per record)

Mirrors the webhook with one deliberate difference: soft-delete collisions log a warning and continue, so a single bad row doesn't abort the batch.

1. Lookup MES row by airtableRecordId → if found, skip (already linked).
2. Lookup MES row by natural key (styleNumber / colorCode / sizeCode):
   • If found with airtableRecordId IS NULL (an orphan row that pre-dates the sync) → call upsertByAirtableRecordId which atomically links the orphan in place. Counted as linked (distinct from inserted — an existing row was touched).
3. Soft-delete check via findSoftDeletedFirst:
   • If a soft-deleted row already holds the natural key or airtableRecordId, log a warning with the row's id and skip. Counted as collisions.
4. Otherwise, Model.create({ data: { airtableRecordId, <fields>, airtableSyncedAt: now } }). Counted as inserted.

Size additionally checks both sizeCode AND size for collisions (the schema enforces uniqueness on both).

Expected log output

==========================================
🎨 Backfill Airtable → MES Style
==========================================

🔍 dryRun: false

📥 page 1: fetched 100 records
📥 page 2: fetched 87 records

📊 Style Backfill Done.
Total records fetched: 187.
Inserted: 142.
Linked (orphan rows): 12.
Skipped (already linked): 30.
Soft-delete collisions: 2.
Failed: 1.

If Soft-delete collisions > 0 or Failed > 0, scroll up in the log for per-record details. Resolve in MES (restore / hard-delete / fix the malformed Airtable record), then re-run — the script is idempotent.

When to backfill vs rely on the webhook

Situation	Use
Steady-state changes from Airtable to a long-running, healthy MES	Webhook (live)
Initial bulk population of a new MES environment	Backfill
MES downtime caused dropped webhook deliveries	Backfill
A bulk import was made in Airtable and the Automation throttled / dropped some triggers	Backfill
Need to reconcile after manual data fixes in Airtable	Backfill — --dry-run first to preview
The view filter changed and historical records now qualify	Backfill

Running from the Digital Ocean console

The deployed container ships src/, scripts/, and tsx (the Dockerfile uses npm ci without --omit=dev), so npm run backfill:airtable:* resolves directly in the DO web console.

# 1. Open DO console: App Platform → <app> → Console
# 2. Confirm working directory contains package.json
pwd && ls package.json

# 3. Confirm required env vars are present (truncate sensitive values)
echo "AIRTABLE_VIEW_ID_STYLE=$AIRTABLE_VIEW_ID_STYLE"
echo "AIRTABLE_BASE_ID=$AIRTABLE_BASE_ID"
echo "DATABASE_URL=${DATABASE_URL:0:20}..."

# 4. Dry-run first (no DB writes — just logs what would happen)
npm run backfill:airtable:styles -- --dry-run

# 5. Real run — capture logs in case the SSH session closes
npm run backfill:airtable:styles 2>&1 | tee /tmp/backfill-styles-$(date +%s).log

# Or all three sequentially
npm run backfill:airtable 2>&1 | tee /tmp/backfill-all-$(date +%s).log

# 6. Verify counts in MES (admin UI or psql) against expected Airtable totals

Long-running jobs. If a table has many thousands of rows, the DO web console may close the SSH session before the script finishes. Options: (a) run individual table scripts so the chunks are smaller; (b) prefer doctl apps tier exec over the interactive console; (c) capture logs with tee so partial progress is visible after disconnect — the script is idempotent, so just re-run.

---

5. Field mapping reference

The webhook payload and the Airtable REST list-records response use different field naming.

Webhook body (Automation script transforms to camelCase)

Domain	MES column	Webhook body key
Style	styleNumber	styleNumber
Style	styleNameEn	styleName
Color	colorCode	colorCode
Color	colorNameEn	colorName
Size	sizeCode	sizeCode
Size	size	size

REST list-records response (raw Airtable column headers)

Domain	MES column	Airtable column header (case-sensitive)
Style	styleNumber	Style Number
Style	styleNameEn	Style Name
Color	colorCode	Color Code
Color	colorNameEn	Color Name
Size	sizeCode	Size Code
Size	size	Size Name

When an Airtable column is renamed, update both the webhook payload Zod schema (src/schemas/webhooks/*) and the backfill script's field accessor (record.fields['<header>']). There is no shared field-map constant — mapping happens inline at each call site to match the convention established by src/services/webhooks/webhooks.service.ts.

---

6. Troubleshooting

Soft-delete collision (P2002 surface)

Symptom. Webhook returns 400 with a message like a soft-deleted row holds airtableRecordId='recABC' or backfill log shows ⚠️ soft-deleted Style row holds airtableRecordId='recABC' (styleId=NNN).

Cause. A previous MES row was soft-deleted but still occupies the unique constraint at the DB level (the @unique index doesn't know about isDeleted). A new sync write would hit P2002.

Fix.

1. In AdminJS, find the row by id (styleId / colorId / sizeId) shown in the log.
2. Either restore (un-soft-delete) — appropriate if the row is genuinely the same one Airtable is now syncing, or
3. Hard-delete the soft-deleted row via psql if it's truly obsolete: DELETE FROM "Style" WHERE "styleId" = N AND "isDeleted" = true.
4. Re-trigger the Airtable record (for webhook) or re-run the backfill script.

Natural-key collision (webhook only)

Symptom. Webhook returns 400 with styleNumber 'S-100' is already linked to Airtable record 'recXXX'.

Cause. Two Airtable records both claim the same MES natural key. This is intentionally a hard error — only humans can decide which Airtable record should win.

Fix. In Airtable, deduplicate the records (delete one, or change one's Style Number). The webhook will succeed on the next trigger.

Orphan rows (existing MES row with airtableRecordId IS NULL)

These exist for any row created before the Airtable sync was wired up. The first sync (webhook or backfill) that matches by natural key links the orphan in place — sets airtableRecordId, airtableSyncedAt, and updates the field values to Airtable's version. Subsequent syncs find by airtableRecordId. No manual intervention needed unless the field values diverge in a way that needs review.

Identify orphans via psql:

SELECT "styleId", "styleNumber", "airtableRecordId" FROM "Style" WHERE "airtableRecordId" IS NULL AND "isDeleted" = false;
SELECT "colorId", "colorCode", "airtableRecordId" FROM "Color" WHERE "airtableRecordId" IS NULL AND "isDeleted" = false;
SELECT "sizeId",  "sizeCode",  "airtableRecordId" FROM "Size"  WHERE "airtableRecordId" IS NULL AND "isDeleted" = false;

Running a backfill picks them up automatically — the Linked counter in the summary reports how many.

Verifying a backfill matched Airtable totals

Compare the Total records fetched line in the script log against the row count in the Airtable view. If they don't match, the filter changed between the run and your check, or your Airtable view ID env var is wrong.

Re-running is safe

All sync paths are idempotent (matched by airtableRecordId). Webhook retries and backfill re-runs don't duplicate rows.