PostgreSQLのunique constraintがhash keyのdirection欠落でdedup衝突

結論

銀行CSVの再取込による二重計上を防ぐためのdedup_hashが、入出金の方向（direction）を式に含めていなかったため、同日・同額・同摘要・同口座の「入金」と「出金（同日の返金）」が同一ハッシュに衝突していた。 衝突した2行目はエラーも出さずに取り込まれなくなり、台帳上は入金だけが残って資金繰りの突合を誤誘導していた。式に|directionを追加し、既存行も同じ式で再計算することで解決した。

症状

再取込の二重計上防止用ハッシュは、次のように日付・金額・摘要・口座の4項目から作られていた。

// mf-csv.ts:136-141（修正後のコメント。旧式は direction を含まない4項目のみ）
/**
 * 再取込の二重計上防止ハッシュ。**方向（income/expense）を含める**（2026-07-25 監査）。
 * 旧式（date|amount|desc|account）だと、同日・同額・同摘要・同口座の「入金」と
 * 「出金（同日返金）」が衝突し、後から来た方が黙って落ちて台帳が突合を誤誘導していた。
 * ⚠ 式を変えると旧CSVの再取込が既存行と一致しなくなるため、migration 0099 が既存行
 *   （source<>'mf_api'）を**同じ式で再計算**して整合させる。式を変える時は必ず対で。
 */

同日に入金が1件あり、その同じ日のうちに同じ摘要・同じ口座で返金（出金）が発生するケースで、date|amount|desc|accountの4項目が入金の行と一致してしまう。方向を区別する項目が式に無いため、PostgreSQL側では「全く同じ取引の再取込」としか見分けられず、後から取り込まれた方のハッシュが既存行と衝突する。

原因

このハッシュは「CSVを再取込したときに、同じ行をもう一度登録してしまう」ことを防ぐ目的で設計されていた。その前提では「日付・金額・摘要・口座が全部同じなら、同一取引の再取込である」という判定で十分だった。しかし実際の銀行の入出金には、同日・同額・同摘要・同口座で方向だけが違う取引、つまり入金とその日のうちの返金（出金）が存在するケースがあった。ハッシュの設計時点では「方向が違えば別の取引」という前提が暗黙に抜けており、2026-07-25の監査で初めてこの衝突が見つかった。

直し方

ハッシュ関数の式にdirectionを加えた。

// 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");
}

式を変えると、以前に取り込んだ行の旧ハッシュと新しいハッシュが一致しなくなり、同じCSVをもう一度取り込んだときに「旧ハッシュで見れば重複なのに、新ハッシュでは重複と判定されない」という逆の事故が起きる。これを防ぐため、migrationで既存行のハッシュを同じ新しい式で再計算した。

-- 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';

source = 'mf_api'の行は対象外にしている。この行は外部連携（mf-sync.ts）側の別スキームでハッシュを扱っているため、今回の再計算からは明示的に除外されている。衝突安全性については、migrationのコメントが「旧ハッシュは既にUNIQUE制約で既存行間ですべて相異なっていたため、同じ式に|directionを追加しても新たな衝突は起きない」ことを根拠として挙げている。4項目の組み合わせが既存行の間で一意だったなら、そこに5つ目の項目を足しても一意性は保たれる、という理屈だ。

再発防止

このハッシュは「再取込による同一行の二重登録を防ぐ」という目的のために作られたが、設計時点で想定していた衝突ケースは「同じ行をもう一度取り込む」ことだけで、「別の取引なのに同じキー項目を持つ」ケースの検討が抜けていた。日付・金額・摘要・口座の4項目は確かに多くの取引を一意に識別できるが、銀行の実際の入出金には同日・同額・同摘要・同口座で方向だけが異なる組が存在する。重複排除用のキーを設計するときは、「このキーが本当に取引を一意に表しているか」を、正常系の多さではなく、キーが落としている属性（この場合は方向）から逆算して検討する必要がある。