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