Fingerprint deduplication for load sheets that are re-sent all day

How SmartLoads keeps exactly one load posting per real load when shippers re-send the same emails and Google Sheets dozens of times a day. Covers fingerprint design, slot and row salts, tenant-scoped unique indexes, full-board sync with protected fields, and the mistakes that shaped it.

A freight broker's shippers don't send tenders. They send feeds. The same Google Sheet is edited all day and re-read every 5 to 30 minutes. The same email list is re-sent at 7:10 with one rate changed and again at 9:40 with a lane added. A TMS report is exported twice because the first one looked short.

At SmartLoads' first agency customer, 17,261 import runs read 1,052,385 source rows between May 21 and September 25, 2026. The board ended up with 38,321 load postings. That's roughly 27 source rows read for every posting kept. Deduplication isn't a cleanup step here; it's the core of the import.

This paper describes how SmartLoads decides that two rows are the same load, the database constraints that enforce it, and the sync algorithm built on top.

Requirements

A correct import has to satisfy all of these at once:

1. Idempotent. Importing the same source twice leaves the board unchanged.
2. Changes replace, never add. A re-sent row with a new rate replaces the old load instead of sitting next to it. Fields outside the load's identity, such as weight, commodity or notes, update in place.
3. Counts are real. "5 loads Opa Locka to Jackson" is five loads, and stays five after every re-send.
4. Withdrawals propagate. A row removed from the source comes off the board, and off Truckstop.
5. People win. A load a broker booked, or a field a person corrected by hand, is never overwritten or removed by an import.
6. Tenant-safe. Two agencies reading the same shipper document keep separate loads.
7. Day-scoped. Each board day is its own set of postings. Freight re-listed tomorrow is posted again, but it keeps its history instead of becoming a stranger.

What goes into a fingerprint

There are two cases.

The source has its own ID. Aljex exports and spot-load captures carry the TMS load ID. The fingerprint is simply a hash of the source name and that ID:

// supabase/functions/_shared/loadFingerprint.ts
export function fingerprintWithAljexId(source: FingerprintSourceKey, aljexLoadId: string) {
  return sha256Hex(`${source}:${String(aljexLoadId).trim()}`);
}

The source has no ID. Most shipper emails and sheets don't. Identity then comes from the lane:

export function fingerprintLaneKey(input: {
  source: FingerprintSourceKey;
  originCity: string;
  destCity: string;
  equipmentType: string;
  rate: string | number | null | undefined;
  pickupDateYmd: string | null | undefined;
  extras?: string | null; // slot index, sheet row, lane copy
}) {
  const raw = [
    input.source,
    input.originCity.trim().toLowerCase(),
    input.destCity.trim().toLowerCase(),
    input.equipmentType.trim().toLowerCase(),
    input.rate == null ? "" : String(input.rate).trim(),
    String(input.pickupDateYmd ?? "").trim(),
    String(input.extras ?? "").trim(),
  ].join("|");
  return sha256Hex(raw);
}

A few choices are worth explaining:

• The source name comes first, so two shippers with the same lane never collide.
• Case and whitespace are normalized before hashing. "Opa Locka " and "opa locka" are the same place.
• The pickup date is part of identity. The same lane next week is a different load.
• SHA-256 of a delimited string keeps fingerprints a fixed length, safe to index, and reproducible in tests and in SQL when debugging.

Slots: when "5 loads" means five

A line like 5 - Opa Locka, FL - cars - Jackson, TN $2000 produces five loads with identical lanes. Hashing the lane alone would collapse them into one. So the reader numbers each copy, and the slot goes into extras:

const extras = `_slot_${slot}|${loadNumber}`; // _slot_1 … _slot_5

Re-sending the email reproduces the same five slots, so the same five fingerprints come back. If the shipper changes "5" to "3", slots 4 and 5 are missing from the next import and come off the board.

Rows: when a sheet has two identical lines

Some shipper sheets list the same lane twice on purpose: two trucks, same day, same rate. For sheet sources, the reader salts the fingerprint with the row position:

const rowSalt = src?.sheet_row_1based != null ? `_row_${src.sheet_row_1based}` : "";

This is a trade-off, and we'd rather state it than hide it. Position becomes part of identity, so if a shipper inserts a row near the top of a sheet, the rows below it get new fingerprints. The sync sees those as removals plus new loads. That's safe, because booked loads are protected (see below), but it can churn postings on the load boards. The alternative, silently merging two genuine loads into one, is worse: the broker would never see the second truck's load.

Where uniqueness is enforced

Application code can compute fingerprints, but only the database can guarantee there's one row per fingerprint. The loads table carries two unique indexes:

Both are scoped by agency and by board date, and each scope was learned the hard way.

The board-date scope. An early version had a global unique index on the fingerprint alone. After the nightly board clear archived the day's loads, those archived rows still held their fingerprints, so the next morning's import of the same lanes was rejected. Imports stopped entirely. The index was dropped on April 20, 2026. Uniqueness is per board day, and history stays in the table.

The agency scope. Later there was an index on (load_fingerprint, board_date) without the agency. Fingerprints carry no agency salt, deliberately, so the same shipper document produces the same fingerprints for every agency that subscribes to it. When a second agency subscribed to a shared source, the agency-blind index rejected its entire batch. That index was dropped on August 12, 2026. The rule now written into the repository: never add a fingerprint unique index without agency_id.

The sync algorithm

Each import is a full-board sync for one source (template_type) and one board day:

incoming  = parsed rows, each with a fingerprint
existing  = loads for (agency, template_type, board_date)

for fp in incoming:
    if fp not in existing:           INSERT
    elif relevant fields changed:    UPDATE via column allowlist
    else:                            skip (no write)

for fp in existing - incoming:
    if booked or covered:            keep
    else:                            remove from the board (and from Truckstop)

Three details make this safe to run every five minutes.

Only relevant changes cause writes. Before updating, the sync compares a fixed slice of fields: pickup and destination (city, state, zip), ship and delivery dates, trailer type, target and max pay, the raw rate, commodity, weight, customer invoice total, tarp requirement, notes and stops. If none changed, there's no write. Unchanged rows don't bump timestamps, don't trigger realtime updates on brokers' screens and don't re-post to load boards.

Updates go through an allowlist. Existing rows are updated through an explicit list of operational columns. Some fields are never sent on update: the row id, created_at, agency_id, the fingerprint itself, booked_at, and the DAT export stamps. So a re-sent sheet can correct a rate, but it can't un-book a load or make it look like it was never exported to DAT.

People outrank imports. A database trigger preserves fields a person edited by hand, so the next sync can't quietly revert a broker's correction. Booked and covered loads are never removed when they disappear from the source, because money is already in flight. There's also a per-customer "keep through ship date" switch: those loads stay on the board until their ship date, even across the nightly clear.

Reviving instead of duplicating

When a fingerprint shows up again after its load was archived, the sync reopens the archived row rather than inserting a second copy, as long as the load was never booked or covered.

• Same day: after a manual Clear Board, a re-sent sheet brings the cleared loads back as they were.
• Across days: standing freight on a Google Sheet keeps the same fingerprint day after day. This morning's import reopens yesterday's archived row and moves it to today's board.

Either way the load keeps its history, notes and any location corrections a person made. Its load-board stamps reset, so it's treated as new for today's DAT export and Truckstop posting.

This is configurable per source. For shippers whose re-listed freight should always start fresh, a per-source switch drops a re-listed row when an archived match exists instead of reopening it. Loads kept on the board through their ship date merge with the re-listed row instead of duplicating.

How it's tested

Every shipper reader has regression tests built from real files, which assert the exact loads, slots and fingerprints each file produces. The upsert module has its own tests for the insert, update and remove decisions. When a parser changes, the tests show immediately whether yesterday's files still produce the same fingerprints. That matters, because changing a fingerprint formula on a live source makes the next import see every load as new.

Lessons

• Design identity before the parser. Decide what makes two rows the same load, per source, before writing the code that reads the file.
• Scope uniqueness like the business. Per tenant, per board day. Each missing scope eventually blocked real imports.
• Make the salt explicit. Slot and row salts belong in the fingerprint on purpose, with the trade-off written down.
• Protect human work structurally. Protected columns, a manual-edit trigger and a "never remove booked loads" rule beat asking every parser to be careful.
• Measure the ratio. Source rows read versus postings kept (about 27 to 1 here) tells you how much work deduplication is doing. A sudden drop usually means a shipper changed their format.

Frequently asked questions

Why not use the shipper's own load number?

When a source has a stable ID, SmartLoads does use it. Most shipper emails and sheets don't have one, or the numbers are reused, so identity has to come from the lane.

Why include the rate in the fingerprint?

For ID-less sources, a different rate on the same lane and day often means a different load, for example a second customer or an alternate destination. The trade-off is that a rate change on a re-sent row reads as a removal plus a new load rather than an in-place update. The board still ends up with exactly one load. For sources with a stable ID, a rate change is an ordinary update.

What stops the same load posting twice to Truckstop?

The board holds one row per fingerprint per day, and Truckstop posting runs from the board's new loads only. A re-sent sheet updates rows it already has, so nothing new is posted.

Can two agencies import the same shipper feed?

Yes. Fingerprints are identical across agencies by design, and every unique index includes agency_id, so each agency gets its own copy of the loads.