Link Integrity Sweep · 2026-05-03

What triggered it

Quick spot-check on May 2's results: trailer PTLZ202425 existed on Airtable but not on TruxFlow. Trailers FA480406 and W83282 existed on both, but appeared as "duplicate" — there were two AT rows for each, both Active. Driver Tony Rivers showed the same pattern.

The pattern: on ~2026-05-01 (the day before the May 2 sweep), brand-new duplicate AT rows were silently created for entities that already existed. May 2 then mapped TF to the wrong (newer, duplicate) AT rows.

Root cause

The TF→AT sync helper (upsertAirtableRecord) had a default behavior: POST a new record whenever the TF row's airtableRecordId was null. This is correct for first-time creates, but devastating for updates of TF rows whose link had been lost.

Sequence that produced each duplicate:

TF row had airtableRecordId = null (one of ~280 unlinked rows pre-May 2).
Something triggered a sync (form save, doc upload, status change).
Sync helper saw null record ID → POSTed a brand-new AT row with whatever fields TF had.
Original AT row sat untouched, now orphaned alongside the new duplicate.

This wasn't a one-off: every TF row without an Airtable record ID is a future duplicate if anyone touches it. The patch below closes that.

Cross-reference audit (read-only)

Built a 5-bucket scan across drivers/trucks/trailers/carriers to surface every kind of broken link:

Bucket	Drivers	Trucks	Trailers	Carriers	What it means
A — TF→AT broken	4	3	6	0	TF points at AT row whose TF-ID is empty or different.
B — AT→TF broken	16	22	46	17	AT has TF-ID set, but TF's airtableRecordId points elsewhere.
C — TF dead-link	4	3	0	0	TF points at deleted AT id.
D — TF null link	14	20	32	18	TF in scope with no AT link — duplicate factory.
E — AT-only	0	0	3	1	AT in scope with no TF row — needs creating or deduping.

Pass 1 — Repair links (no data changes)

For every AT row that already claimed a valid TF cuid, wrote that AT id back to TF's airtableRecordId. For every TF row pointing at the wrong/dead AT row, repointed it at the correct AT row.

Entity	Pass 1 fills	Pass 2/3 repoints	Skipped
Drivers	16	2	6
Trucks	22	2	4
Trailers	46	2	4
Total	84	6	14

Skipped rows pointed at AT IDs that no AT row currently claimed back — mostly already-deactivated test data. Left for human review (most were addressed in the test-cleanup pass below).

Pass 2 — Create the AT-only entities

Trailer PTLZ202425 — created on TruxFlow as ACTIVE, on SSMK Logistics carrier, with full data (VIN, plate, type, size, make, year, pickup date, lease company). 14 documents downloaded from Airtable's CDN, uploaded to Linode S3, inserted as Document rows (8× pictures, 1× title, 3× DOT inspection, 2× registration). TF cuid written back to Airtable.
Carrier GRODB LLC — created on TruxFlow as INACTIVE (preserves the data + record without making it operational). DOT, MC, EIN, phone all transferred. TF cuid written back to Airtable.

Pass 3 — Status mirror (now finally accurate)

With links repaired, the existing mirror-status job could finally see what it was supposed to. Three real deactivations:

Driver Eric Young (D-1301) — TF ACTIVE → INACTIVE (AT InActive on MW Trucking).
Trailer 408 on Highland Dallas Freight — TF ACTIVE → DEACTIVATED.
Trailer 53705 on Highland Dallas Freight — TF ACTIVE → DEACTIVATED.

Pass 4 — Test-residue cleanup

Five clearly-test items had to be deactivated manually since they had no Airtable counterpart for mirror-status to compare against:

Driver D-1473 "test11 test12" — ONBOARDING → INACTIVE.
Truck 666666 — ACTIVE → DEACTIVATED.
Trailers test11, test12, test13 — ONBOARDING → DEACTIVATED.

Root-cause patch — sync now refuses silent creates

The duplicate factory has been disarmed in the TruxFlow backend. upsertAirtableRecord now requires the caller to explicitly opt in via { allowCreate: true } before issuing a POST. Every legitimate first-time create path (driver/truck/trailer create endpoints, both legacy controllers and Phase 2 services) was updated to pass that flag. Every other path defaults to PATCH-only: if the TF row is missing its Airtable ID, sync logs an explicit error instead of silently creating a duplicate.

Effect going forward: a new entity created in the TruxFlow UI mirrors over to Airtable, and Airtable's record ID is written back immediately. No more orphan entities. No more silent duplicates.

End state vs start of session

Bucket	Before	After	Notes
A — TF→AT broken	13	10	Remaining are already-inactive on both sides — harmless dangling pointers.
B — AT→TF broken	101	18	Remaining 18 are all carriers (no `airtableRecordId` column on Carrier — structural).
C — TF dead-link	7	4	Remaining 4 are inactive/test rows.
D — TF null link	84	19	Remaining 19 are all carriers (structural).
E — AT-only	4	0	PTLZ created, GRODB created, FA480406+W83282 dupes deleted by team.

Net for the day

100

TF rows updated total

14

Documents uploaded (PTLZ)

2

AT-side dupes removed

8

Test items deactivated

1

Root-cause sync patched

0

Failures

Strategic note (for the team)

Captured a formal decision: the Airtable sync exists only to keep WeLink dispatch operations functional during this transition. It is NOT a long-term TruxFlow feature. When the TruxFlow Dispatch module ships and replaces Airtable dispatch, all sync code gets removed cleanly. Today's defensive measures (refusing silent POST, writing IDs both sides on create) are appropriate even though they add scaffolding code — operational corruption on Airtable directly hurts the team's ability to dispatch.