One collation setting, two row orders, a publish gate that lied

At 3am the NFL injury pipeline refused to publish. The verification gate that runs before anything reaches the live injury reports kept throwing one error: the report hash collided with a completely different set of player rows. Except it hadn't. Same players, same data, byte-identical content. I spent a while chasing a corruption that didn't exist, and the answer turned out to be the least dramatic thing possible: sort order.

Statpro publishes NFL injury reports as a whole, and one of the last checks before publishing is a hash comparison. Hashes are unforgiving about bytes, which is the point. But Postgres doesn't promise you the same byte order twice unless you ask for it. This database runs a case-insensitive collation, and that changes how ORDER BY sequences strings. Same rows, different order, different hash, gate says no.

flowchart TD
    A["same planned rows"] --> B["database A
order X"]
    A --> C["database B
order Y"]
    B --> D["stringify + compare"]
    C --> D
    D --> E["false mismatch
publish blocked"]
    style A fill:#191620,stroke:#8d8496,color:#efe9df
    style B fill:#191620,stroke:#8d8496,color:#efe9df
    style C fill:#191620,stroke:#8d8496,color:#efe9df
    style D fill:#191620,stroke:#8d8496,color:#efe9df
    style E fill:#191620,stroke:#e8b873,color:#efe9df

One collation setting, two row orders, a publish gate that lied

Flow showing one planned row set reaching two databases that return the same rows in different orders, which makes a string comparison report a false mismatch and blocks publishing — Same data, two collations, two orders, one hash comparison that can't tell the difference between "different" and "differently sorted".

The fix is one line, and you should steal it

When you compare two row sets that came back from a database, sort both sides yourself in application code before comparing. Don't let the database decide. We do it with a localeCompare on a stable key:

function comparePlayerSlug(
  left: { readonly officialProfileSlug: string },
  right: { readonly officialProfileSlug: string },
): number {
  return left.officialProfileSlug.localeCompare(right.officialProfileSlug);
}

const actualRows = stored.map(toRow).sort(comparePlayerSlug);
const expectedRows = expected.map(toRow).sort(comparePlayerSlug);

That's the whole fix. Both sides now agree on the order, so the comparison only fails when the content actually differs.

The general lesson applies way beyond injury reports. Any time you assert "these two row sets are equal", you're implicitly asserting their order is equal too, and order is a property of the machine that gave you the rows. Sort in your own code, on a stable key, before you compare. The database's job is to give you the right rows. Yours is to put them in an order you chose.

The verification gate is doing its job, by the way. It's caught real data bugs. This time the alarm was false, but I'd rather tune a smoke detector than skip it.