Unique Constraint Hash Missing direction Drops a Refund

The dedup hash didn’t distinguish deposit from withdrawal, so a same-day deposit and its refund hashed identically — whichever imported second silently failed to land.

Conclusion

A dedup hash meant to stop double-counting on bank CSV re-imports didn’t include transaction direction in its formula, so a same-day, same-amount, same-description, same-account “deposit” and its “refund” (a same-day reversal) collided on the same hash. The second row to be imported was silently dropped — no error — leaving only the deposit in the ledger and quietly throwing off cash-flow reconciliation. The fix adds |direction to the hash expression and recomputes it for existing rows using the same formula.

Symptom

The hash used to prevent double-counting on re-import was built from four fields: date, amount, description, and account.

// mf-csv.ts:136-141 (post-fix comment; the old formula used only these four fields, no direction)
/**
 * Dedup hash for re-import double-counting. **Must include direction (income/expense)** (audited 2026-07-25).
 * The old formula (date|amount|desc|account) let a same-day, same-amount, same-description,
 * same-account "deposit" and "refund (same-day reversal)" collide — whichever arrived second
 * silently dropped, throwing off ledger reconciliation.
 * ⚠ Changing this formula breaks matching against existing rows on re-import of old CSVs,
 *   so migration 0099 recomputes existing rows (source <> 'mf_api') with the **same new formula**
 *   to keep them consistent. Any formula change must ship together with that kind of migration.
 */

When a deposit lands on a given day, and a refund (a reversal, same description, same account) of that exact deposit lands later the same day, all four fields — date|amount|desc|account — match the deposit row exactly. With no field in the hash to tell the two apart by direction, PostgreSQL has no way to see them as anything but “the same transaction being re-imported,” so the hash of the later row collides with the existing one.

Cause

This hash was designed to solve one specific problem: “don’t let the same CSV row get registered twice when the CSV is re-imported.” Under that framing, “same date, amount, description and account” was a sufficient test for “this is the same transaction being re-imported.” But real bank activity includes same-day, same-amount, same-description, same-account pairs that differ only in direction — a deposit and its same-day refund. The assumption “different direction implies a different transaction” was never encoded into the hash’s design, and the collision only surfaced in a 2026-07-25 audit.

The fix

direction was added to the hash function’s formula.

// mf-csv.ts:142-145
export function dedupHash(
  date: string,
  amount: number,
  desc: string | null,
  account: string | null,
  direction: "income" | "expense"
): string {
  return createHash("sha256")
    .update(`${date}|${amount}|${desc ?? ""}|${account ?? ""}|${direction}`)
    .digest("hex");
}

Changing the formula means previously imported rows’ old hashes no longer match the new hash — which would create the opposite failure: re-importing the same CSV again would look like “a duplicate under the old hash, but not a duplicate under the new hash,” and let a true duplicate slip through. To prevent that, a migration recomputed existing rows’ hashes with the same new formula.

-- 0099_dedup_hash_direction.sql:1-23
update public.finance_transactions
set dedup_hash = encode(
  extensions.digest(
    txn_date::text || '|' || amount::text || '|' || coalesce(description, '') || '|' || coalesce(account, '') || '|' || direction,
    'sha256'
  ),
  'hex'
)
where source <> 'mf_api';

Rows with source = 'mf_api' are excluded. Those rows get their hash from a separate scheme tied to the external integration (mf-sync.ts), so they’re explicitly left out of this recomputation. On collision safety, the migration’s own comment gives the reasoning: because the old hash already enforced a UNIQUE constraint that made every existing row distinct from every other purely on those four fields, appending |direction to that same expression cannot introduce a new collision. If a four-field combination was already unique across every existing row, adding a fifth field on top of it preserves that uniqueness.

Preventing a repeat

This hash was built to solve “don’t let the same row get double-registered on re-import,” but the design only considered the collision case of “importing the exact same row twice” — it never considered “two genuinely different transactions that happen to share the same key fields.” Date, amount, description and account do uniquely identify most transactions, but real bank activity does contain same-day, same-amount, same-description, same-account pairs that differ only in direction. When designing a dedup key, the right question isn’t “how rare is a collision in the common case” — it’s “what attribute does this key leave out, and could two distinct real-world records share every field this key does check.” Here, that missing attribute was direction.