Skip to main content

Airtable Reference Sync & SKUs

upsertByAirtableRecordId pattern, SKU decomposition/validation, inbound webhooks domain.

Deep-dive doc split out of .claude/rules/architecture.md (which is now an index). Append new findings about this area here, not to the index.

Reference-Sync Model Pattern (upsertByAirtableRecordId)​

Style/Color/Size each expose upsertByAirtableRecordId({ airtableRecordId, data }, client?) for idempotent sync from Airtable. The pattern handles three cases atomically inside a transaction:

  1. No existing row by natural key → upsert by airtableRecordId (creates a brand-new row, or updates if a previous Airtable sync already created it).
  2. Row exists by natural key with airtableRecordId IS NULL (orphan from before Airtable sync was wired up) → UPDATE that row in place, setting both airtableRecordId and the new field values in a single write. The row gets linked to its Airtable record on first sync.
  3. Row exists by natural key with the same airtableRecordId → falls through to the upsert by airtableRecordId, which finds and updates it.
  4. Row exists by natural key with a different airtableRecordId → falls through to the upsert, which fails with a P2002 unique-constraint error. Intentional — two Airtable records cannot claim the same MES row; this requires human resolution.

Other invariants:

  • On every successful write, sets airtableSyncedAt = new Date()
  • Calls cacheInvalidate('<domain>:') after the write
  • Wraps everything in handleDbErr (so P2002 surfaces as makeBadRequestError)
  • Accepts an optional Prisma.TransactionClient — if passed, runs inline within that transaction; if not, opens its own prisma.$transaction for atomicity of the find + write.
  • Per-domain natural key: Style → styleNumber, Color → colorCode.

Size is an exception (INFRA-562). 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 neither column is @unique in the Size model (plain @@index only). Identity is airtableRecordId alone; cases 3/4 above (natural-key collision) don't apply to Size at all. The orphan-detect lookup (case 2) still uses sizeCode, but filters explicitly to { sizeCode, airtableRecordId: null } in the query itself — necessary because a different, already-linked row may share the same sizeCode, and a plain findFirst/findUnique on sizeCode alone could return that row instead of the true orphan. SizeModel.findSoftDeletedFirst mirrors this: only airtableRecordId is checked (a soft-deleted row sharing a non-unique sizeCode/size can't cause a P2002 on insert, so there's nothing to guard against there). Any other code that looks up Size by sizeCode alone (e.g. skuUtils.ts SKU decomposition) must use findMany/findFirst, not findUnique — Prisma's SizeWhereUniqueInput no longer accepts sizeCode/size.

When adding a new reference table sourced from Airtable, copy this method shape verbatim — the service layer assumes it. If the new table also lacks a real unique natural key (like Size), copy the Size exception instead.

A brand-new domain with no pre-existing MES rows (e.g. SizeRange, INFRA-563) needs none of the above. The orphan-linking case (2) only exists to handle legacy rows created before Airtable sync was wired up — Style/Color/Size all predate their own Airtable integration. A domain that has never existed in MES outside of Airtable sync has no such legacy rows, so its upsertByAirtableRecordId collapses to a plain prisma.<model>.upsert({ where: { airtableRecordId }, create, update }) — no orphan lookup, no natural-key collision check, no findSoftDeletedFirst natural-key branch. Don't copy the orphan-detection machinery into a new domain unless it actually has pre-Airtable seed data to reconcile.

Gotcha when a reference-sync model gains its own outbound FK (e.g. Style.sizeRangeId, INFRA-564). Prisma generates two shapes for data on create/update: the "checked" <Model>CreateInput/<Model>UpdateInput (relation fields only settable via nested { connect: {...} }, no raw FK scalar) and the "unchecked" <Model>UncheckedCreateInput/<Model>UncheckedUpdateInput (exposes the raw FK scalar column directly, e.g. sizeRangeId: 5). Prisma.<Model>CreateArgs/UpdateArgs (used by <Model>CreateParams/UpdateParams via Prisma.SelectSubset<X, X>) type data as XOR<Checked, Unchecked>, so a plain model method like create()/update() already accepts the raw scalar with no changes needed. But a narrower type that pins to just the checked variant — e.g. <Model>UpsertByAirtableRecordIdParams.data: Omit<Prisma.<Model>CreateInput, ...> (the existing shape for Style/Color/Size, none of which had outbound FKs before) — does not accept the raw scalar and must be switched to Omit<Prisma.<Model>UncheckedCreateInput, ...> once the model grows its first outbound FK column, or the FK simply won't type-check inside data.

AdminJS crash when a new outbound FK's target model has no registered AdminJS resource (found live, INFRA-565). @adminjs/prisma's Resource.prepareProperties() builds a Property for every DMMF field except relation fields where the current model doesn't own the FK (field.relationName && !field.relationFromFields?.length — i.e. reverse/array relations like Style.workOrder WorkOrder[] are skipped entirely, no crash risk). A to-one relation the model does own the FK for (e.g. Style.sizeRange SizeRange? @relation(fields: [sizeRangeId], ...)) does get a Property, and that property's .reference() returns the target model's name. AdminJS's built-in populator (runs on list, and elsewhere any relation gets resolved) calls .reference() on every property of the resource being acted on and then admin.findResource(name) — if no resource is registered under that exact model name, this throws There are no resources with given id: "X" and 500s the entire action (list/show/edit/new), not just the relation column. Whitelisting the FK out of listProperties/properties.<field>.isVisible does not help — the crash happens during property decoration, before any per-action filtering. Style.sizeRangeId (INFRA-564) shipped without a SizeRange AdminJS resource and broke the Style list page in production for one full release before INFRA-565 caught it live and added src/routers/admin/resources/sizeRange/sizeRange.ts (registered in admin.router.ts, same shape as colorResource — refParent, adminRoleAuth/adminEditorViewerRoleAuth/adminEditorRoleAuth gating, cacheInvalidate('sizeRange:') on write). Rule: any time a Prisma model gains a new to-one @relation(fields: [...], ...) pointing at a model that isn't already an AdminJS resource (raw CRUD or custom), register at least a minimal resource for that target model in the same change — reverse/array relations on the other side never need this, only the FK-owning side. SizeRangeToSize (a join table, no admin resource) is safe only because nothing with an admin resource owns an FK pointing at it — if that ever changes, it needs the same treatment.

SKU Decomposition & Validation (src/utils/skuUtils.ts)​

A SKU string is styleNumber(5) + colorCode(6) + sizeCode(3) (14 chars total, validated by /^[a-zA-Z]{2}\d{3}[a-zA-Z\d]{9}$/). Two entry points decompose and validate a SKU against Style/Color/Size, and they are not kept in sync with each other:

  • parseSkuInfo(sku: string) — single-SKU path. Callers: the AdminJS WorkOrder resource (src/routers/admin/resources/workorder/workorder.ts) and workorder.service.ts. Looks up the Size scoped to the Style's sizeRangeId via SizeModel.findSizeByCodeInRange; on a scoped-size miss it throws `Style(${styleNumber}) Size(${sizeCode}) do not in sizeRange(${sizeRangeId})` — still the raw sizeRangeId, not resolved to a name.
  • parseSkuInfos(skus: string[]) — batch path, sole caller shipment.controller.ts (validateSkusInDb, the Shipment/WorkOrder-creation API). Two passes over the input: pass 1 builds scopedLookups (sizeCode+sizeRangeId pairs) per style, pushing a Style(...) has no sizeRangeId message directly into the shared notFoundMsgs array and continue-ing past any style with a null sizeRangeId; pass 2 independently re-derives each SKU's style/color/size lookup from the maps built in pass 1 and appends any Style/Color/Size(...) Not Found In Table/SizeRange(...) messages. The two passes are not aware of each other's skips — a style found but with no sizeRangeId produces messages from both passes for the same SKU (harmless duplication, not something either INFRA-581 or prior work has cleaned up).

INFRA-581: parseSkuInfos's "Size not found" message used to read Size(<code>) Not Found In SizeRange(<raw sizeRangeId>) — meaningless without a DB lookup (SizeRange.name, e.g. sizeRangeId 13 → "17. RING + CARAT SIZE" — sourced from reported production data, not an assumption). Fixed by batch-resolving the distinct sizeRangeIds referenced by the request's styles via SizeRangeModel.findMany({ where: { sizeRangeId: { in: [...] } } }) into a sizeRangeId → name map, built alongside the existing style/color lookups, and substituting the name into the message (falling back to the raw id only if the SizeRange row itself can't be resolved). parseSkuInfo (singular) was deliberately left untouched — different message string (do not in sizeRange(...)), different caller, out of this ticket's scope; it has the same underlying opaque-id problem and is a candidate for a follow-up ticket.

Test-mock gotcha: src/models/mock/index.ts (the global vitest setup file, wired in vitest.config.ts) had no vi.mock('models/sizeRange', ...) entry before INFRA-581 — nothing previously exercised SizeRangeModel from a spec that relies on the shared mock setup. Adding a new model dependency to a util consumed by mocked specs requires adding both a vi.mock('models/<domain>', ...) block here and a sibling src/models/mock/<domain>.mock.ts fixture file (mirroring style.mock.ts/color.mock.ts/size.mock.ts) — otherwise the spec falls through to the real Prisma client. sizeRange.mock.ts's sizeRangeRecord.sizeRangeId (10) and sizeRangeRecordSecondary.sizeRangeId (20) intentionally match style.mock.ts's styleRecord.sizeRangeId/styleRecordWithNullField.sizeRangeId so a single SKU fixture exercises both the Style and SizeRange mocks consistently.

Inbound Webhooks Domain (webhooks)​

The webhooks domain (src/{routers/api,controllers,services,schemas}/webhooks/) is an orchestration-only domain — it owns no model of its own. Each route handler calls into the appropriate target domain's model (StyleModel, ColorModel, SizeModel).

Push model: the Airtable Automation pushes the full set of synced fields in the webhook body ({ event, recordId, ...domainFields, timestamp }). MES does not call back to Airtable. The webhook caller (whoever holds the API key) is therefore authoritative — acceptable here because the payload only writes to internal reference tables and the per-table keys are independently rotatable.

Auth: each route binds its own key via makeAirtableWebhookKeyAuth(env.AIRTABLE_WEBHOOK_<TABLE>_API_KEY) so a leaked key can't be replayed across tables.

Validation: validateHandler runs the per-domain body schema (AirtableStyleWebhookBodySchema / AirtableColorWebhookBodySchema / AirtableSizeWebhookBodySchema) before the controller. The service receives an already-validated body and reads fields directly — no second-pass mapping. The event and timestamp fields are accepted (for documentation / OpenAPI fidelity) but unused; the service detects create vs update via findUnique on airtableRecordId instead.

Field-name mapping (Airtable → MES):

  • Style: styleNumber → styleNumber, styleName → styleNameEn
  • Color: colorCode → colorCode, colorName → colorNameEn
  • Size: sizeCode → sizeCode, size → size

When an Airtable field is renamed or added, update the relevant Zod schema and the service's mapping at the upsert call. There is no separate field-map constant — mapping happens inline.

Response envelope: { statusCode: 200, recordId, action: 'created' | 'updated' }.

To add a new pushed table: add a Zod schema + SchemaOpt[] export, a controller, a service method, register the route with its own AIRTABLE_WEBHOOK_<TABLE>_API_KEY, and add an upsertByAirtableRecordId method on the target model (see Reference-Sync Model Pattern above).

Alt Length variants on the Airtable side (INFRA-638) — verified gaps​

Verified live against base appTUYFNlstAI5wjy, table Synced Size View (tblVgnuyDt4rpg831), 2026-08:

  • The Size table has no Length field and no Base Size link field. Its fields are Size Name, Size Lookup, Size Code, Size Range, Curve or Straight, Core Size, SKU, Size Sort, Archive, plus lookups/rollups/formulas. Nothing today can populate Size.lengthVariant / Size.baseSizeId.
  • Zero variant records exist (0 of 332 records match SHORT/LONG in Size Name). The plan is for Airtable to add each variant as its own record with its own new Size Code.
  • A variant Size row syncs fine with no MES change — sizeCode + size is all syncAirtableSize needs — but it lands indistinguishable from a standard size, with both new columns NULL. "The sync creates variant sizes" means creating them as variants, which needs the two fields (or an explicit derivation step).
  • Label-based base resolution is ambiguous. XS/001 exists as 3+ distinct records (rec0OpiNE7zAhScR9 spanning 4 Size Ranges, rec4WsvRn0v2ShW91 in ALPHA DRESSES XXS-XXL, rec3MndMUIIOT465U in SUIT VEST). Stripping -SHORT off a label does not identify one base row.

The two fields landed — how the sync resolves them​

Re-verified live 2026-08-27, same base/table. Airtable has since added both fields, and 11 -SHORT variant records exist (Size Codes 200–208, 248, 249), all inside the MES Sync Source view (viwV8EKhA8QAUPxOt = AIRTABLE_VIEW_ID_SIZE) — so the existing gate admits them and no view-filter change was needed.

  • Alternate Length (fldo0gqCdfUkB8BZ7) is a singleSelect with exactly two choices, SHORT / LONG, matching the SizeLengthVariant Prisma enum 1:1. REST returns it as a plain string.
  • Base Size (fldKHTa4LYkEMJBCG) is a singleLineText label ('XS'), not a record link — so the ambiguity above is real and unresolved by the field itself.

The resolution rule (used identically in three places): a candidate base is a standard record (no Alternate Length of its own) whose Size Name equals the label AND that shares at least one Size Range with the variant. Verified unique for all 11 variants. Anything other than exactly one candidate fails loudly.

Where the resolution runs, and why it is not MES. The Airtable Automation script resolves the label to a record ID and pushes baseSizeRecordId; MES resolves that record ID to a sizeId via SizeModel.findSizeUnique({ airtableRecordId }) — the same shape as Style's sizeRangeRecordId (INFRA-564). MES cannot do the label match itself: at the moment a brand-new variant's Size webhook fires it has no SizeRangeToSize membership yet (that join is written later, by the Size Range webhook), so there is nothing to scope the lookup by. This is the ordering trap noted above, now closed rather than worked around.

lengthVariant / baseSizeId are written on every sync, null included. upsertByAirtableRecordId spreads data into its update branch, so conditionally omitting them would leave a stale variant marking in place when someone clears the Alternate Length cell in Airtable. Writing both unconditionally makes demotion back to a standard size work.

When the label match can break (all three fail loudly — the Automation run goes red with the count and the Size Range IDs, and the backfill logs a per-record ambiguous warning and skips):

  • A second standard size sharing a Size Name is added to a Size Range that already has one → 2+ matches.
  • A Base Size label is typo'd, or the base size's Size Name is renamed without updating the variants pointing at it → 0 matches.
  • A variant is created with no Size Range link → nothing to scope by, so the lookup is refused outright rather than matching across the whole table.

backfill-airtable-sizes.ts re-derives the label match itself — REST gives it Base Size as a label, not a record ID, so it cannot reuse the script's answer. Two consequences specific to the backfill: it processes standard sizes before variants (a variant's baseSizeId is read back out of MES by the base's airtableRecordId, so the base row must exist first), and its already-linked "skip" branch now also updates a row whose lengthVariant/baseSizeId has drifted — the two fields were added to Airtable long after these rows first synced, so a plain skip would leave every pre-existing variant permanently unmarked.

Base Size stays a text field — decided 2026-08-27, don't re-open it. Converting it to multipleRecordLinks would collapse all three implementations to reading [0].id and make the ambiguity inexpressible; it was raised and declined. The label→record-ID lookup is therefore permanent, not a stopgap, and the ~30 lines it costs in the Automation script are load-bearing. Treat the resolution rule (standard record + matching Size Name + shared Size Range) as a real invariant of the Airtable data: it is currently unique for all 11 variants, but nothing in Airtable enforces it. Both implementations fail loudly on 0 or 2+ matches rather than guessing — see the failure modes below.

  • Ordering trap. SizeRangeToSize is written by the Size Range webhook (SizeRangeModel.upsertByAirtableRecordId reconciling sizeRecordIds), not the Size webhook. A brand-new variant therefore has no range membership at the moment its own Size webhook fires, so "find the base within the same Size Range" cannot run there. An explicit Base Size link resolvable by airtableRecordId (the _resolveSizeRangeId pattern) avoids both this and the ambiguity above.
  • Linking is already free. Because the Size Range sync reconciles membership from Airtable's Size Name link field, "links variants into the relevant Size Ranges" needs no new code — only that the Size Range record links the variants.
  • Unverified: the MES Sync Source view filter (viwV8EKhA8QAUPxOt) — the Airtable list-views tool was unavailable. Whatever gates that view decides whether variant records sync at all. Note the field Valid Bridal Apparel Size for MES is TRUE for only 3 of 332 records, so it is evidently not the gate despite its name.
  • Suffix collision to respect: suit sizes already encode length as bare letters (44S, 44R, 56L, 40L) as plain unrelated Size records. Any suffix matching must require exactly -SHORT / -LONG, never a trailing S/L.

Size is now the first reference model with an outbound FK, so SizeUpsertByAirtableRecordIdParams.data had to move from Prisma.SizeCreateInput to Prisma.SizeUncheckedCreateInput — the checked variant only exposes baseSize: { connect: … } and rejects the raw baseSizeId scalar. This is the INFRA-564 gotcha documented above, now hit for real a second time; expect it for every reference model that gains its first FK.