Skip to main content

Airtable ↔ MES Sync Runbook

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

  1. Overview — the four tickets that ship the base system and how they fit together.
  2. Webhook sync (INFRA-444, extended by INFRA-563 for Size Range) — 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, extended by INFRA-563) — 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.
  7. Downstream consumer: GarmentMeasurement.sizeId (INFRA-565) — why the Size Range design exists end-to-end, POM grid scoping, and the (non-Airtable) backfill script that resolves the FK.

Size Range (INFRA-563) is a brand-new domain layered onto this same system — a SizeRange table plus a SizeRangeToSize join mirroring which Size rows belong to each range. It reuses every mechanism described below (webhook shape, view/Automation gate, backfill CLI conventions) with two differences called out inline where they apply: it has no orphan-linking (no pre-Airtable legacy rows exist for a domain that never existed in MES before Airtable sync), and its sync validates that every linked Size already exists in MES, rejecting the record if not.

Style ↔ Size Range linkage (INFRA-564) adds a nullable Style.sizeRangeId FK, synced via the Style webhook's sizeRangeRecordId field and backfilled by scripts/backfill-airtable-styles.ts. Unlike Size Range's own Size validation, an unresolvable sizeRangeRecordId only fails that one field's resolution — a Style with no Size Range assigned (sizeRangeRecordId omitted/null) is valid and expected for non-dress categories. Because Style now depends on Size Range which depends on Size, scripts/backfill-airtable-all.ts runs phases in Color → Size → Size Range → Style order (not alphabetical/table-listing order) — see § 4.

GarmentMeasurement ↔ Size linkage (INFRA-565) is the last hop in the chain and the reason the whole Size Range design exists: it makes the Style Measurement (POM) grid, its Excel export/import, and the underlying GarmentMeasurement row itself scoped to a specific Size, not a free-text label. This is a pure MES-internal consumer — it reads Style.sizeRangeId and SizeRangeToSize (both already synced by INFRA-563/564) but has no Airtable webhook or REST field of its own. See § 7.

1. Overview​

Four tickets together implement the system:

TicketDirectionTriggerWhat it produces
INFRA-444Airtable → MESLive Automation pushWebhook endpoint per table (Style/Color/Size, extended to Size Range and Style↔Size Range by INFRA-563/564) that upserts MES rows by airtableRecordId.
INFRA-449Airtable sideConfiguration onlyThe 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-429Airtable → MESManual (npm script)One-shot backfill scripts that read the MES Sync Source views and insert into MES. Idempotent — safe to re-run.
INFRA-566Airtable sideConfiguration onlySize Range's own MES Sync Source view + two Automations (entry trigger for initial sync, update trigger for edits to already-synced records) sharing one script — the INFRA-449 pattern didn't cover Size Range because the table didn't exist yet when INFRA-449 shipped.

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​

EndpointAuthBody schema
POST /api/webhooks/v1/airtable/styleX-API-Key: $AIRTABLE_WEBHOOK_STYLE_API_KEYAirtableStyleWebhookBodySchema
POST /api/webhooks/v1/airtable/colorX-API-Key: $AIRTABLE_WEBHOOK_COLOR_API_KEYAirtableColorWebhookBodySchema
POST /api/webhooks/v1/airtable/sizeX-API-Key: $AIRTABLE_WEBHOOK_SIZE_API_KEYAirtableSizeWebhookBodySchema
POST /api/webhooks/v1/airtable/sizeRangeX-API-Key: $AIRTABLE_WEBHOOK_SIZE_RANGE_API_KEYAirtableSizeRangeWebhookBodySchema

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).

Request body shape (Size Range example)​

{
  "event": "record_updated",
  "recordId": "recSizeRange123",
  "name": "ONE SIZE",
  "sizeRecordIds": ["recSize1", "recSize2"],
  "timestamp": "2026-04-01T00:00:00Z"
}

sizeRecordIds arrives as a genuine JSON array here (the Automation script transforms it), same shape as what the backfill script reads directly off the Size Name multipleRecordLinks field via REST (see § 5). Don't read the Size Range table's Size Record IDs text field — it looked like a rollup of the same data but turned out to hold stale/dead record IDs unrelated to the table's actual links, verified directly against the live base 2026-07-10 (see § 5 and the Troubleshooting entry below).

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) → 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.

Size is a special case (INFRA-562). Unlike Style/Color, Airtable's Size table does not enforce a unique sizeCode/size — the same code can appear on multiple records, each scoped to a different Size Range. So Size has no natural-key collision check at all; identity is airtableRecordId only, and only airtableRecordId is checked for soft-delete collisions. A repeated sizeCode/size across different Airtable records is expected and valid, not an error.

Size Range has no natural-key collision check either (INFRA-563), for a different reason: it's a brand-new domain with no pre-Airtable legacy rows, so identity is airtableRecordId only from day one — there's no natural key to collide on. It has its own kind of 400 instead: if any of sizeRecordIds hasn't synced to MES as a Size row yet, the sync is rejected outright, naming exactly which Airtable record IDs are missing. Sync the referenced Size records first (webhook or backfill), then retry the Size Range.

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).

Testing an Automation script against your local server (ngrok)​

Before a branch with webhook-side changes reaches staging (or when staging is missing the endpoint entirely — e.g. INFRA-563/564/565 aren't merged to main as of this writing, so staging 404s on /airtable/sizeRange), point the Automation at your local dev server instead via ngrok:

  1. Run the app locally: npm run dev (listens on PORT from .env, default 3000).
  2. Start a tunnel: ngrok http 3000 (or reuse an existing tunnel URL if one's already running).
  3. In the Automation script, swap the URL's host and path — keep the exact route casing from § "Endpoints" above (e.g. .../airtable/sizeRange, not .../airtable/sizerange): 
    https://<your-subdomain>.ngrok-free.dev/api/webhooks/v1/airtable/<style|color|size|sizeRange>
  4. Update the Automation's secret input to your local .env's AIRTABLE_WEBHOOK_<TABLE>_API_KEY value — it's a different value than staging/production, so leaving the old key in place surfaces as a 401, not a 400.
  5. Add 'ngrok-skip-browser-warning': 'true' to the script's request headers. Free ngrok domains serve an interstitial browser-warning HTML page on first hit unless this header is present — without it, a "successful" request can come back as HTML instead of JSON and fail response.json()/response.text() parsing in a way that looks unrelated to the script itself: 
    headers: {
        'Content-Type': 'application/json',
        'x-api-key': apiKey,
        'ngrok-skip-browser-warning': 'true',
    },
  6. Your local DB can carry the same stale airtableRecordId links described in the Troubleshooting entry below (from earlier testing against the pre-migration base) — either test with a style/color number that doesn't exist locally yet, or run npm run reset:airtable:stale-links -- --dry-run (then for real) against your local database first.
  7. Restart your local dev server after running the reset script. StyleModel.findStyleUnique/ColorModel.findColorUnique cache lookups in-memory keyed by <domain>:${JSON.stringify(where)} (§ "Model Caching Pattern" in .claude/rules/architecture.md), with a 5-minute TTL. The reset script runs as its own separate tsx process — its cacheInvalidate('style:') call only clears that process's memory, not your already-running npm run dev server's. If you reset a row and immediately re-test without restarting, you can still get the same collision error against the pre-reset cached value for up to 5 minutes. Restarting the dev server (a fresh process = empty cache) fixes it immediately; verified live 2026-07-13 against AA430.

Automation scripts (Style/Color/Size — verified, INFRA-566)​

All six pre-existing Style/Color/Size scripts (Create + Update pairs) have now been tested end-to-end against Test Base V2 (via the ngrok setup above, except Color which was tested directly against staging since its route predates the Size Range work and was already live there) and are captured here for the first time — none were ever committed before this ticket.

Style​

Styles To Create in MES Webhook ("When a record enters a view") and Style To Update in MES Webhook ("When a record is updated") share one identical script (below); only the trigger config differs. Two bugs surfaced while testing Styles To Create, beyond the missing sizeRecordIds-style field described earlier in this doc:

  • The script's payload never referenced its own configured sizeRangeRecordId input variable — same silent-drop bug as the earlier draft, just rediscovered live.
  • An Airtable TypeScript hover tooltip in the script editor reported the wrong property name (sizeRange instead of sizeRangeRecordId) — stale type-inference cache, not the real shape. The Test Input panel (or a runtime console.log) is ground truth; a hover tooltip is not, if the two ever disagree. Input variable names are also whatever the author typed into Airtable's config UI, not a fixed convention — don't assume a name without checking.

Style To Update's trigger config (table Synced Style View): View set to the same qualifying view Styles To Create uses (confirms the native-View-field gating pattern documented in Automation B below), watching Style Name, Style Number, and Size Range. Watch Size Range (the real multipleRecordLinks field), not Size Range Record ID (the lookup used as the script's input variable) — the lookup only recomputes off a formula that's immutable per row (RECORD_ID()), so it never changes independently of the link field itself; watching the link directly is both correct and sufficient, with nothing extra gained by also watching the lookup.

Verified script (used by both Automations):

let inputConfig = input.config();
let apiKey = input.secret('AIRTABLE_WEBHOOK_STYLE_API_KEY');

let webhookUrl = 'https://mes.birdystaging.com/api/webhooks/v1/airtable/style';

// inputConfig.sizeRangeRecordId resolves to a plain value in practice (confirmed live against
// a Style with a linked Size Range), but the Test panel's single-value display doesn't rule out
// an array under the hood — this guard is cheap insurance either way.
let sizeRangeRecordId = Array.isArray(inputConfig.sizeRangeRecordId)
    ? (inputConfig.sizeRangeRecordId[0] ?? null)
    : (inputConfig.sizeRangeRecordId ?? null);

let payload = {
    event: 'record_updated',
    recordId: inputConfig.airtableRecordId,
    styleNumber: inputConfig.styleNumber,
    styleName: inputConfig.styleName,
    sizeRangeRecordId,
    timestamp: new Date().toISOString(),
};

let response = await fetch(webhookUrl, {
    method: 'POST',
    headers: {
        'Content-Type': 'application/json',
        'x-api-key': apiKey,
    },
    body: JSON.stringify(payload),
});

let responseText = await response.text();

console.log(`Status: ${response.status}`);
console.log(`Response: ${responseText}`);

if (!response.ok) {
    throw new Error(`MES Style To Create Webhook failed with status ${response.status}: ${responseText}`);
}

(Swap webhookUrl for the ngrok URL + add the ngrok-skip-browser-warning header per the testing section above when running against a local server instead of staging.)

Color​

Colors To Create in MES Webhook and Color To Update in MES Webhook tested clean with no script bugs — Color has no linked-record field on its own webhook body (nothing analogous to Style's Size Range), so there's no equivalent of that missing-field mistake. Payload is just three fields, all plain scalars:

let inputConfig = input.config();
let apiKey = input.secret('AIRTABLE_WEBHOOK_COLOR_API_KEY');

let webhookUrl = 'https://mes.birdystaging.com/api/webhooks/v1/airtable/color';

let payload = {
    event: 'record_updated',
    recordId: inputConfig.airtableRecordId,
    colorCode: inputConfig.colorCode,
    colorName: inputConfig.colorName,
    timestamp: new Date().toISOString(),
};

let response = await fetch(webhookUrl, {
    method: 'POST',
    headers: {
        'Content-Type': 'application/json',
        'x-api-key': apiKey,
    },
    body: JSON.stringify(payload),
});

let responseText = await response.text();

console.log(`Status: ${response.status}`);
console.log(`Response: ${responseText}`);

if (!response.ok) {
    throw new Error(`MES Color To Create Webhook failed with status ${response.status}: ${responseText}`);
}

The only issue hit during testing was a 401 Invalid API key — not a script bug, but the Automation's secret input still holding a different environment's key value (this script targets staging directly; make sure the secret is staging's AIRTABLE_WEBHOOK_COLOR_API_KEY, not your local .env's, if you've been switching back and forth with the ngrok setup above).

Color.colorNameCn is MES-owned, not Airtable-owned (INFRA-628)​

Airtable has no Chinese-name field for Color — the webhook body carries only colorCode and colorName, and syncAirtableColor (src/services/webhooks/webhooks.service.ts) passes only colorCode / colorNameEn to ColorModel.upsertByAirtableRecordId. colorNameCn is never read or written by the sync, so:

  • Direct DB writes to colorNameCn are safe — a later Airtable sync will not clobber them. This is the opposite of colorNameEn, which the sync owns outright.
  • The only in-app writers are the AdminJS Color edit action (src/routers/admin/resources/color/color.ts — BGC-role users are sanitized down to allowedFields = ['colorNameCn'], so it's the one field they may edit) and the seed (prisma/seed/seed-colors.ts ← prisma/seed/data/colors.json).
  • After each sync, syncAirtableColor fires notifyNoColorCn(...) when the upserted row has no colorNameCn — that alert is the intended prompt for a human to fill it in via AdminJS.

seed-colors.ts only creates, never updates. It skips any colorCode already present in the DB, so editing colors.json fixes fresh/local databases only — it does not repair an existing environment. Correcting Chinese names on staging/production requires a direct UPDATE keyed on colorCode, run through pgAdmin. The INFRA-628 pattern: load the (colorCode, colorNameCn) pairs into a TEMP table, SELECT the rows that would change as a pre-flight, then UPDATE ... WHERE "colorNameCn" IS DISTINCT FROM t."colorNameCn" so the statement is idempotent and the reported row count is the true change count. Raw SQL bypasses the Prisma extensions, so set "updatedBy" / "updatedAt" explicitly and remember soft-deleted rows are visible. Note also that blank names in colors.json are stored as "", not null, while the DB column is nullable — compare with IS DISTINCT FROM rather than = when diffing the two.

colors.json drifts from live data. It is a point-in-time seed snapshot, not a mirror; live environments gain colors through the webhook. When diffing an external spreadsheet against it, expect codes present upstream but absent from the seed, and expect stale colorNameEn values. Verify against Airtable's Synced Color table (tblOPE5Y2r7YShi2n in Test Base V2) before concluding that a mismatch is a data error.

Archived Airtable rows reuse Color Code. Synced Color can hold more than one record with the same Color Code, with superseded ones flagged via the Archive single-select — e.g. GR0007 exists twice, as Bright Pistachio (Archive) and Cactus (active). A colorCode that looks renamed in MES is usually just the active record having changed; check the Archive field before treating it as a collision. (BL0029 behaved similarly: Cerulean moved to a new BL0045 record and BL0029 became Aquamarine.)

Size​

Sizes To Create in MES Webhook and Size To Update in MES Webhook share one identical script; only the trigger config differs. Both were originally the same shape as Color (three plain scalar fields: recordId, sizeCode, size) with no linked-record field involved. INFRA-638 adds two fields, Alternate Length and Base Size, which turn a Size record into an Alternate Length variant.

Trigger config for Size To Update must add Alternate Length and Base Size to its watched-fields list, alongside the existing Size Name / Size Code. Without that, editing only those two cells on an already-synced record fires nothing and MES keeps the stale value. Do not add Size Range to the watched list unless you also want a re-sync on every range membership change — the base lookup reads it, but it is not itself synced by this webhook.

No view-filter change is needed. All 11 variant records were confirmed present in the MES Sync Source view (viwV8EKhA8QAUPxOt) on 2026-08-27 — the existing gate already admits them, so the view ≡ Automation-gate lockstep rule in § 3 is untouched.

Input variables need no change. The three existing ones (airtableRecordId, sizeCode, size) stay exactly as they are, and the two new fields are read off the record instead — the script has to re-fetch anyway for Size Range (a multipleRecordLinks field, which Automation input-variable mapping does not reliably expose; same reason the Size Range Automation re-fetches, see § "A. Record enters view" below). Reading all three new-to-the-script fields off the record keeps one source of truth and avoids the single-select-mapping ambiguity that input variables would introduce.

The selectRecordAsync runs on every sync (one cheap record read); the selectRecordsAsync scan of the whole table (343 records) stays inside the variant branch, so a standard size never pays for it.

The table is referenced by ID, deliberately (decided 2026-08-27) — extending § 3's "Why IDs over names" rule from the env vars into the Automation scripts themselves, so renaming the Synced Size View table cannot break the sync. Note this deviates from the older sibling script, which still uses base.getTable('Synced Size Range'); converting that one is a safe follow-up, not a prerequisite. The ID is the same value as AIRTABLE_TABLE_ID_SIZE in .env, so the two stay verifiable against each other.

The script references no view — the MES Sync Source view (AIRTABLE_VIEW_ID_SIZE) gates the Automation trigger and the backfill, but the base-size candidate scan deliberately reads the whole table, so a base size that has not itself synced yet surfaces as MES's explicit "has not synced to MES yet" 400 rather than as a confusing zero-match error.

Verified script (both Automations; only event and the final error string differ between Create and Update — MES ignores event entirely):

let inputConfig = input.config();
let apiKey = input.secret('AIRTABLE_WEBHOOK_SIZE_API_KEY');

let webhookUrl = 'https://mes.birdystaging.com/api/webhooks/v1/airtable/size';
// let webhookUrl = 'https://<your-subdomain>.ngrok-free.dev/api/webhooks/v1/airtable/size';

// Alternate Length (INFRA-638). `Size Range` is a multipleRecordLinks field, so the record has
// to be re-fetched to read it — Automation input-variable mapping does not reliably expose one.
const SIZE_TABLE_ID = 'tblVgnuyDt4rpg831'; // `Synced Size View` — ID, so a rename can't break it
let table = base.getTable(SIZE_TABLE_ID);
let record = await table.selectRecordAsync(inputConfig.airtableRecordId, {
    fields: ['Size Name', 'Alternate Length', 'Base Size', 'Size Range'],
});
if (!record) {
    throw new Error(`Size record ${inputConfig.airtableRecordId} not found in ${SIZE_TABLE_ID}`);
}

// Single select — getCellValue returns {id, name, color} or null.
let alternateLength = record.getCellValue('Alternate Length')?.name ?? null;
// Plain text label (e.g. 'XS') — NOT a record link.
let baseSizeLabel = (record.getCellValue('Base Size') || '').trim() || null;

// The same Size Name exists on several records scoped to different Size Ranges, so the label
// alone is ambiguous — resolve it to exactly one record HERE. MES cannot: a brand-new variant
// has no Size Range membership in MES yet at the moment this webhook fires.
let baseSizeRecordId = null;
if (alternateLength) {
    if (!baseSizeLabel) {
        throw new Error(`Size '${inputConfig.size}' has Alternate Length '${alternateLength}' but no Base Size. Fill in Base Size, then re-run.`);
    }

    let rangeIds = (record.getCellValue('Size Range') || []).map((link) => link.id);
    if (rangeIds.length === 0) {
        throw new Error(`Size '${inputConfig.size}' has no Size Range, so Base Size '${baseSizeLabel}' cannot be resolved unambiguously.`);
    }

    // A candidate qualifies only if it is a STANDARD size (no Alternate Length of its own)
    // whose Size Name matches AND that shares a Size Range with this variant.
    let query = await table.selectRecordsAsync({ fields: ['Size Name', 'Alternate Length', 'Size Range'] });
    let matches = query.records.filter((r) => {
        if (r.id === record.id) return false;
        if (r.getCellValue('Alternate Length')) return false;
        if (r.getCellValueAsString('Size Name').trim() !== baseSizeLabel) return false;
        return (r.getCellValue('Size Range') || []).some((link) => rangeIds.includes(link.id));
    });

    if (matches.length !== 1) {
        throw new Error(`Base Size '${baseSizeLabel}' for '${inputConfig.size}' matched ${matches.length} standard sizes in Size Range(s) [${rangeIds.join(', ')}] — expected exactly 1. Fix the data in Airtable, then re-run.`);
    }
    baseSizeRecordId = matches[0].id;
}

let payload = {
    event: 'size.created',
    recordId: inputConfig.airtableRecordId,
    sizeCode: inputConfig.sizeCode,
    size: inputConfig.size,
    alternateLength,
    baseSizeRecordId,
    timestamp: new Date().toISOString(),
};

let response = await fetch(webhookUrl, {
    method: 'POST',
    headers: {
        'Content-Type': 'application/json',
        'x-api-key': apiKey,
        'ngrok-skip-browser-warning': 'true',
    },
    body: JSON.stringify(payload),
});

let responseText = await response.text();

console.log(`Status: ${response.status}`);
console.log(`Response: ${responseText}`);

if (!response.ok) {
    throw new Error(`MES Size To Create Webhook failed with status ${response.status}: ${responseText}`);
}

MES rejects a half-filled variant rather than syncing it into a silently mis-ordered row. alternateLength and baseSizeRecordId must arrive together; the base must already be synced to MES; and the base must itself be a standard size (a variant-of-a-variant would compound computeVariantSizeSortOrder's offset and collide with the next base size's slot). Each of those returns a 400 whose message names the fix. null on both fields is meaningful and expected — it demotes a row back to a standard size when the Alternate Length cell is cleared in Airtable.

Airtable-side simplification worth considering. If Base Size were changed from singleLineText to a multipleRecordLinks field pointing at the Size table, the entire resolution block above collapses to record.getCellValue('Base Size')[0].id, the ambiguity becomes impossible to express, and backfill-airtable-sizes.ts could read the record ID straight from REST instead of re-deriving the label match. That is a base schema change (and a one-time re-entry of the 11 existing values), so it is left as a recommendation rather than a prerequisite — everything above works against the current text field.

Duplication risk is specific to Size, unlike Style/Color. Size has no natural-key check at all (INFRA-562 — the same sizeCode/size label can legitimately appear on multiple records, since identity is airtableRecordId only). That means re-syncing a size label that was already synced under the old base doesn't collide (like Style/Color did) and doesn't update the existing row either — it silently creates a brand-new Size row under the new base's record ID, since the orphan-relink path ({ sizeCode, airtableRecordId: null }) only matches when an existing row's airtableRecordId is already null, which none are yet. Test with a size label that's never been synced from either base to avoid this. reset-stale-airtable-record-ids.ts deliberately does not cover Size — extending it would need to reuse the "ambiguous orphan match" detection INFRA-565 already built for Size's own natural-key looseness, not a naive reset like Style/Color's.

Automation scripts (Size Range — verified, INFRA-566)​

This is an existing pattern, not a new design. Style/Color/Size already run two Automations apiece in the original Airtable base — Styles To Create in MES Webhook ("When a record enters a view") + Style To Update in MES Webhook ("When a record is updated"), and likewise for Colors/Color and Sizes/Size. All six are now verified (above) and are currently being copied over as part of the base migration from the original Airtable base to Test Base V2 (see the Troubleshooting note on that migration below). Size Range needs the same two-Automation split, sharing one script body, and its Automations should follow the same naming convention — both are now tested end-to-end against this branch (via the ngrok setup above) and confirmed working:

  • A — Size Ranges To Create in MES Webhook ("When a record enters a view"): fires on initial sync (new record, or an existing record edited into first matching the gate).
  • B — Size Range To Update in MES Webhook ("When a record is updated"): fires on every subsequent edit to an already-synced record, so changes made after the initial sync (e.g., linking another Size, renaming the range) still reach MES.

This split exists because Automation A's trigger type is edge-triggered: it only fires on the transition into matching the view's conditions, never for a record that's already inside the view and gets edited while it continues to match. Without Automation B, a Size Range's sizeRecordIds/name in MES would silently go stale after its first sync — the exact same reason Style/Color/Size already carry a paired "To Update" Automation each.

A. Record enters view (initial sync)​

Trigger: When record matches conditions, gated on the MES Sync Source view (§ 3) — the same view the backfill script reads.

Script step input variables. Only pass the triggering record's own Airtable record ID — do not map Size Name (or any linked-record field) directly as a script input variable. Airtable's Automation input-variable mapping does not reliably expose a linked-record field's underlying record IDs; the script instead re-fetches the full record via the Scripting API (table.selectRecordAsync) and reads the real link field off it.

Input nameTypeValue
airtableRecordIdSingle line textThe triggering record's Airtable record ID. Named to match the convention Style/Color/Size already use — an earlier draft of this doc used the shorter recordId, which caused a real TypeError: recordId should be a string, not undefined when the input variable wasn't actually named that; confirmed live 2026-07-13.
AIRTABLE_WEBHOOK_SIZE_RANGE_API_KEYSecretThe deployed AIRTABLE_WEBHOOK_SIZE_RANGE_API_KEY value — set directly as a secret input, never hardcoded in the script body. Read in-script via input.secret('AIRTABLE_WEBHOOK_SIZE_RANGE_API_KEY'), not input.config() — secret inputs aren't exposed through input.config() at all. Named after the full env var, matching the convention Style/Color/Size already use (AIRTABLE_WEBHOOK_STYLE_API_KEY, etc.) — confirmed live 2026-07-13, not the shorter mesApiKey an earlier draft of this doc used.

Script body (verified):

const config = input.config();
const apiKey = input.secret('AIRTABLE_WEBHOOK_SIZE_RANGE_API_KEY'); // secret inputs are NOT exposed via input.config()
const table = base.getTable('Synced Size Range');
const record = await table.selectRecordAsync(config.airtableRecordId);

if (!record) {
  throw new Error(`Record ${config.airtableRecordId} not found in Synced Size Range`);
}

// Real multipleRecordLinks field — returns [{id, name}, ...] or null when empty.
// Do NOT read the "Size Record IDs" text field (see § 5) — it holds stale, unrelated IDs.
const sizeLinks = record.getCellValue('Size Name') || [];
const sizeRecordIds = sizeLinks.map((link) => link.id);

const body = {
  event: 'record_updated',
  recordId: record.getCellValue('Size Range Record ID'), // formula field, returns this record's own Airtable ID
  name: record.getCellValue('Size Range'),
  sizeRecordIds,
  timestamp: new Date().toISOString(),
};

const response = await fetch('https://mes.birdystaging.com/api/webhooks/v1/airtable/sizeRange', {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json',
    'X-API-Key': apiKey,
  },
  body: JSON.stringify(body),
});

const responseBody = await response.json().catch(() => null);

console.log(`Status: ${response.status}`);
console.log(`Response: ${JSON.stringify(responseBody)}`);

if (!response.ok) {
  // Throwing marks the Automation run "Failed" so it surfaces in run history.
  throw new Error(`MES Size Range sync failed (${response.status}): ${responseBody?.message ?? 'no response body'}`);
}

(Swap webhookUrl for the ngrok URL + add the ngrok-skip-browser-warning header per the testing section above when running against a local server instead of staging — that's how this script was actually verified, since staging doesn't have this route yet.)

Route casing. /api/webhooks/v1/airtable/sizeRange is camelCase, matching webhooks.router.ts and its passing spec tests. An earlier draft of this ticket asserted the route was all-lowercase sizerange, citing a staging check dated 2026-07-09 — but the route wasn't committed until 2026-07-10, so that check predates the code and is incorrect. The camelCase route above is what's actually implemented and tested.

B. Record updated (keeps already-synced ranges current)​

Trigger: When a record is updated — table Synced Size Range, watching these four fields:

  1. Size Range — the name itself; this is what gets pushed as the payload's name.
  2. Size Name — the linked Sizes; this is what gets resolved into sizeRecordIds.
  3. Style — catches a Style being linked to or unlinked from this Size Range.
  4. Associated with a Confirmed Style (from Synced Style) (rollup) — catches what watching Style alone would miss: an already-linked Style's own "Confirmed Style" checkbox flipping later, with no edit to the Style link field itself. Airtable's "record updated" trigger fires on a rollup's recomputed value, not just on a direct field edit, so this is necessary in addition to Style, not redundant with it.

Fields 3 and 4 only matter if the sync gate condition (§ 3, still unconfirmed) actually depends on Style confirmation — once that's confirmed, drop whichever of the two doesn't factor into the real filter formula.

Gating — use the trigger's built-in View field, not a separate condition step. "When a record is updated" has its own optional Table + View selector right in the trigger configuration ("Optionally, select a view to only watch updates in that view") — set the View to the same qualifying view Automation A uses, and Airtable only fires the trigger for records currently in that view. This was confirmed live 2026-07-13 against Style's own Style To Update in MES Webhook (its trigger's View is set to the same view as Styles To Create). No separate "Only continue if…" condition-step action is needed — an earlier draft of this doc recommended building one manually, which is unnecessary complexity once you know the native View field does the same job.

Script step. Byte-identical to Automation A's script above — same airtableRecordId / AIRTABLE_WEBHOOK_SIZE_RANGE_API_KEY input variables, mapped the same way (record ID token; secret input), same script body. Airtable Automations can't attach two trigger types to one Automation, so this has to be a second, separate Automation whose script step is a copy of Automation A's.

Not yet buildable end-to-end against staging. As of this doc update, INFRA-563/564/565 (the webhook route, schema, and SizeRange model) are not yet merged to main — they exist only on their own feature branches, so a live test POST to the mes.birdystaging.com URL above still 404s until those branches merge and staging redeploys. Both Automations were tested successfully against this branch directly (via the ngrok setup in § 2) instead.

3. Airtable view configuration (INFRA-449)​

The MES Sync Source view​

Each of Style, Color, Size, Size Range 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.

Size Range's view filter is unconfirmed (INFRA-566). The view itself already exists (AIRTABLE_VIEW_ID_SIZE_RANGE), but its filter formula isn't documented anywhere and isn't readable via the Airtable API — confirm it directly in the Airtable UI before wiring the matching Automation trigger condition. The most likely candidate is the Associated with a Confirmed Style (from Synced Style) rollup already on the Size Range table, which mirrors the same "gate on a confirmed Style" pattern Color's Confirmed Style (from Product) rollup uses — but this is a guess, not a verified fact, and needs sign-off from the PM/base owner.

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
npm run backfill:airtable:sizeranges

# All four, in Color -> Size -> Size Range -> Style order (INFRA-564) — dependency order, not
# alphabetical: Style resolves a linked Size Range, Size Range resolves its linked Sizes
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​

VariablePurpose
AIRTABLE_API_TOKENPersonal access token (Bearer auth). Scope: data.records:read on the Product Master base.
AIRTABLE_BASE_IDBase ID (starts with app...).
AIRTABLE_TABLE_ID_STYLE / _COLOR / _SIZE / _SIZE_RANGETable IDs (starts with tbl...).
AIRTABLE_VIEW_ID_STYLE / _COLOR / _SIZE / _SIZE_RANGEMES Sync Source view IDs (starts with viw...).
DATABASE_URLMES 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 (INFRA-562) is simpler, not stricter. Since sizeCode/size aren't unique, step 2's orphan lookup filters explicitly to { sizeCode, airtableRecordId: null } (so an already-linked row sharing the same sizeCode is never mistaken for the orphan), and step 3 only checks airtableRecordId — there is no sizeCode/size soft-delete check.

Size Range (INFRA-563) skips step 2 entirely — no orphan-linking, since it's a brand-new domain with no pre-Airtable legacy rows. It adds a step between 1 and 3 instead: resolve every sizeRecordIds entry to a MES Size.sizeId. Unlike the live webhook (which throws 400 outright), the backfill logs the record as failed and continues if any Size Record ID hasn't synced yet — consistent with the backfill's general "don't let one bad row abort the batch" philosophy.

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​

SituationUse
Steady-state changes from Airtable to a long-running, healthy MESWebhook (live)
Initial bulk population of a new MES environmentBackfill
MES downtime caused dropped webhook deliveriesBackfill
A bulk import was made in Airtable and the Automation throttled / dropped some triggersBackfill
Need to reconcile after manual data fixes in AirtableBackfill — --dry-run first to preview
The view filter changed and historical records now qualifyBackfill

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)​

DomainMES columnWebhook body key
StylestyleNumberstyleNumber
StylestyleNameEnstyleName
StylesizeRangeId (resolved)sizeRangeRecordId — a single Airtable record ID or null (INFRA-564)
ColorcolorCodecolorCode
ColorcolorNameEncolorName
SizesizeCodesizeCode
Sizesizesize
SizelengthVariantalternateLength — 'SHORT' | 'LONG' | null (INFRA-638)
SizebaseSizeId (resolved)baseSizeRecordId — a single Airtable record ID or null; the script resolves Airtable's Base Size label to a record ID before pushing (INFRA-638)
Size Rangenamename
Size RangeSizeRangeToSize join (resolved sizeIds)sizeRecordIds — a genuine JSON array of Airtable record IDs

REST list-records response (raw Airtable column headers)​

DomainMES columnAirtable column header (case-sensitive)
StylestyleNumberStyle Number
StylestyleNameEnStyle Name
StylesizeRangeId (resolved)Size Range — the real multipleRecordLinks field; a Style links to at most one Size Range, take the first array element (INFRA-564)
ColorcolorCodeColor Code
ColorcolorNameEnColor Name
SizesizeCodeSize Code
SizesizeSize Name
SizelengthVariantAlternate Length — a singleSelect, returned by REST as a plain string ('SHORT'/'LONG'), absent for a standard size
SizebaseSizeId (resolved)Base Size — a singleLineText label (e.g. 'XS'), not a record link; resolve it via Size Name + a shared Size Range (see below)
Size RangenameSize Range
Size RangeSizeRangeToSize join (resolved sizeIds)Size Name — the real multipleRecordLinks field, returned as a plain array of record IDs, same shape as the webhook's sizeRecordIds

Do not use the Size Range table's Size Record IDs field. It looks like the REST-side equivalent of sizeRecordIds (and an earlier version of the backfill script read it, splitting on ,), but it's a plain singleLineText field, not a rollup — nothing keeps it in sync with the table's actual links. Verified directly against the live base 2026-07-10: every value in it was a stale/dead record ID left over from before the base migration to Test Base V2, while the real Size Name link field held the correct, currently-synced Size records. Read Size Name directly instead — it's already a JSON array via REST, no ,-splitting needed.

Base Size is a label, and a label is ambiguous. The same Size Name legitimately exists on several Size records scoped to different Size Ranges (INFRA-562) — XS alone matched 5+ records when this was verified live on 2026-08-27. Resolve it to exactly one record by requiring the candidate to be a standard size (no Alternate Length of its own) whose Size Name matches AND that shares at least one Size Range with the variant. Verified unique under that rule for all 11 variants currently in the view. Anything other than exactly one candidate must fail loudly, not pick the first match — both the Automation script and backfill-airtable-sizes.ts implement this same rule.

Resolving MES-side is not an option. At the moment a brand-new variant's Size webhook fires, the variant has no SizeRangeToSize membership in MES yet (that join is written later, by the Size Range webhook) — so MES has nothing to scope the label lookup by. This is why the resolution lives in Airtable and MES receives a record ID, the same shape as Style's sizeRangeRecordId.

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.

Special case — whole-base migration (seen 2026-07-13, Test Base → Test Base V2). The above "deduplicate in Airtable" fix doesn't apply when the collision isn't a genuine duplicate but the entire Airtable base being copied to a new one: every record gets a brand-new Airtable record ID, but MES's Style/Color rows are still linked to the old base's IDs by styleNumber/colorCode. Syncing from the new base then throws this same collision error for every previously-synced Style/Color, not just one bad row. Style/Color are the only domains affected — Size has no natural-key check (INFRA-562) and Size Range has no pre-migration rows (INFRA-563).

Fix at scale: tsx scripts/reset-stale-airtable-record-ids.ts (npm run reset:airtable:stale-links) nulls airtableRecordId/airtableSyncedAt on every non-deleted Style/Color row, restoring them to "orphan" status (§ 4's orphan-linking case). The next sync from the new base then re-links each row in place via its natural key instead of colliding. Supports --dry-run; refuses to run when NODE_ENV=production unless --force is passed, since a base migration is a test/staging-only event — run this only against the environment actually being re-pointed at the new base, never production.

Unresolved Size reference (Size Range only)​

Symptom. Webhook returns 400 with Cannot sync Size Range '<name>': the following Size Airtable record IDs have not synced to MES yet: recXXX, recYYY or backfill log shows ❌ <recordId>: Size Range '<name>' references Size record ID(s) not yet synced to MES: recXXX, recYYY.

Cause. A Size Range's sizeRecordIds names an Airtable Size record that MES hasn't synced yet — the join can't be built against a Size row that doesn't exist.

Fix. Sync the missing Size record(s) first (trigger their Airtable Automation, or run npm run backfill:airtable:sizes), then re-trigger the Size Range sync. If Size Range backfill runs before Size backfill has ever populated MES, this is expected on first run — not a data error.

Unresolved Size Range reference (Style only)​

Symptom. Webhook returns 400 with Cannot sync Style '<styleNumber>': linked Size Range '<recordId>' has not synced to MES yet or backfill log shows ❌ <recordId>: Style '<styleNumber>' references Size Range record ID '<recordId>' not yet synced to MES.

Cause. A Style's sizeRangeRecordId names an Airtable Size Range record that MES hasn't synced yet (INFRA-564).

Fix. Sync the missing Size Range first (trigger its Airtable Automation, or run npm run backfill:airtable:sizeranges), then re-trigger the Style sync. This is why backfill-airtable-all.ts runs Size Range before Style (see § 4) — running Style backfill before Size Range has ever populated MES is expected to fail on first run for every Style that has a Size Range assigned, not a data error. A Style with no Size Range assigned in Airtable (sizeRangeRecordId omitted/null) never hits this — it's valid and expected for non-dress categories.

A "linked record" field's REST value looks wrong / references nonexistent records​

Symptom. A backfill or webhook resolution step reports referenced records as "not synced yet," but the records genuinely exist and are synced in MES — the referenced IDs just don't match anything.

Cause (seen 2026-07-10, Size Range → Size). The field being read wasn't the real multipleRecordLinks field — it was a separate plain text field that merely looked like a rollup of the link (matching name pattern, plausible-looking record IDs) but wasn't actually kept in sync with it. This can happen after a base migration/reorg: the old field's stale values survive even though the real link field correctly points at the new, current records.

Fix. Before trusting any Airtable field for record-ID resolution, confirm its type via get_table_schema (or the Airtable MCP tools) — a real link is multipleRecordLinks; anything else (singleLineText, formula, rollup, multipleLookupValues) is derived or manually maintained and may not reflect current reality. Prefer reading the real link field directly — Airtable's REST API already returns multipleRecordLinks fields as a plain array of record IDs, so there's no need for a separate lookup/rollup field at all.

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.

Doesn't apply to Size Range — it's a brand-new domain with no rows that predate Airtable sync, so an orphan row (by definition) can never exist for it.

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.

7. Downstream consumer: GarmentMeasurement.sizeId (INFRA-565)​

This section closes the loop on why the Size Range design (§ 1) exists at all. It's the one piece in this document that isn't Airtable sync itself — no webhook, no REST field, no Airtable Automation — but it's the actual payoff: it's what a style's measurement grid uses Size Range data for.

The problem Size Range was built to solve​

Before INFRA-562, Size.size (the label — "S", "M", "One Size", etc.) was globally unique in MES, so any code could safely join on it. INFRA-562 had to drop that uniqueness because Airtable's real Size table allows the same label to appear on multiple records, each scoped to a different Size Range (e.g. a jewelry "One Size" and an apparel "One Size" are different Size rows sharing a label). That fixed the Size domain's own webhook/backfill collision handling (§ 2), but it left every consumer that still joined GarmentMeasurement to Size by label — the Style Measurement (POM) grid, its Excel export/import, the work order measurement join — exposed to the same collision risk one layer up, since the global Size library was no longer safely joinable by label either.

INFRA-563 (Size Range table + SizeRangeToSize join) and INFRA-564 (Style.sizeRangeId) built the data needed to disambiguate: every Style now resolves to one Size Range, and every Size Range resolves to an explicit set of Sizes. INFRA-565 is what actually uses that data to fix the consumers.

What changed on GarmentMeasurement​

  • GarmentMeasurement.sizeId (Int?, FK → Size.sizeId) replaces free-text label matching as the authoritative link. @@unique([styleId, measurementId, sizeId]) replaces the old ..., size] constraint.
  • The physical size column is not dropped yet — the migration only adds sizeId and relaxes size to nullable (expand-contract), because prisma migrate deploy runs unattended in CI with no gate for a backfill script to run in between. Dropping the column is a deferred follow-up ticket, once the backfill (below) is verified across all environments.

POM grid scoping​

The Style Show/Edit/New measurement grid, and the measurement Excel export/import, now only display or accept sizes belonging to the style's own resolved sizeRangeId — reusing the exact relational filter pattern Size Range's own domain established (§ "Relational scoping pattern" in docs/architecture/style-and-measurements.md):

SizeService.findMany({ where: { sizeRangeToSize: { some: { sizeRangeId } } }, orderBy: {...} })
  • A style with no sizeRangeId yet (new style, or Airtable sync hasn't linked one) shows an empty grid with a banner (Style.noSizeRangeAssigned locale key), not an error — the same "valid, expected state" treatment INFRA-564 gives an unassigned Style.
  • A GarmentMeasurement row whose sizeId doesn't resolve to a column in the style's current Size Range (e.g. the style's Size Range was changed after the row was created) is never deleted — it simply doesn't render or export. Changing the Size Range back, or fixing the assignment, brings it back into view.

Backfill: a different kind of script than § 4​

scripts/backfill-garment-measurement-size-id.ts resolves sizeId for every pre-migration GarmentMeasurement row. Unlike every backfill script in § 4, it never calls the Airtable API — it's a purely MES-internal reconciliation:

GarmentMeasurement.styleId -> Style.sizeRangeId -> SizeRangeToSize (Sizes in that range) -> match the row's legacy `size` label against that range's Size labels
tsx scripts/backfill-garment-measurement-size-id.ts --dry-run
tsx scripts/backfill-garment-measurement-size-id.ts

The legacy size label is read via $queryRaw (the escape hatch for a column the Prisma model no longer declares — see the Soft Deletes section of .claude/rules/architecture.md). Rows that can't be resolved (Style has no sizeRangeId, or the label has no match within the resolved range) are logged with a specific reason and left alone — not an error, surfaced for manual review. Safe to re-run: only sizeId IS NULL rows are ever touched.

Deferred: jewelry size ordering​

CANONICAL_SIZE_ORDER (src/constants/sizeOrder.ts, § "Size Ordering" in docs/architecture/style-and-measurements.md) still only orders apparel sizes (XXS…5X). Extending it to jewelry Size Range values (ring sizes, chain lengths, etc.) is blocked pending PM-supplied Size Range Size Name list — not implemented as part of INFRA-565. Until that list is supplied, jewelry sizes fall through computeSizeSortOrder's "unknown size" branch (appended after the highest existing sortOrder, in whatever order they're encountered) rather than sorting in a jewelry-meaningful sequence.