Skip to content

I-023 — OCBC-S duplicate-transaction reconciliation

Symptom

The Working Paper Pack showed an OCBC-S bank balance that did not match the real account. The owner supplied the authoritative figures (bank portal + the ERL_Register_BankTransaction_20260630.xlsx register): the real OCBC-S balance is HKD +283.30, but Firestore summed to −4,720.11.

Root cause — 6 synthetic-seqNo duplicates

The OCBC-S feed carried 6 duplicate transaction docs. Each duplicated a genuine bank record but carried a synthetic 16-digit date-based seqNo (e.g. 2026013104156577) instead of the bank's real short seqNo (e.g. 75205, 31937 — owner-verified from the portal). The synthetic rows were written by system-backfill / codex:wopc-ref-repair paths that lacked the real seqNo, so checkOCBCDuplicates (which dedups on transaction.ocbc.seqNo) never saw them as duplicates of the genuine rows.

Reconciliation proof (exact): −4,720.11 (Firestore, all 6 dups) + 5,003.41 (sum of the 6 dups) = +283.30 — the real bank balance to the cent. This proved Firestore = bank + exactly 6 duplicate docs, nothing missing or mis-valued.

The 6 dups:

dup id amount gl WOPC twin (genuine, kept)
YsQ2zn7t3KdzczD4XFHp −1 1120 — — (clean dup)
lMTkCtw8aaJyzIpvKvsl −0.41 1120 — — (clean dup)
tUXTy0RXplHc9i6UhaA2 −1 1120 — — (clean dup)
uGuiuAtMR0qml31aXPwn −1 1120 — — (clean dup)
5RAGrB7H3FWisVMqoMT3 −3000 5050 ERL-WOPC/2025-020 lmCh8DatqL4c1qW96hE9 (seq 75205)
r4SeZHaHRajPI9aDzaSn −2000 5050 ERL-WOPC/2026-002 RMG7xFUJqn6bN3jqJs45 (seq 31937)

Fix (live Firestore data op — no code change)

  1. 4 clean dups (small amounts, gl 1120, no WOPC/invoice link) → deleted.
  2. 2 WOPC-linked dups — owner decision (verbatim): "Use the WOPC ones instead of the 1120." The naïve "keep the WOPC doc" would have kept the synthetic seqNo and discarded the genuine bank record (incl. its real bank reference HKIT260131886834), and re-duplicated on the next wide sync (the genuine seqNo would no longer exist to dedup against). So the correct execution migrated the categorization onto the genuine record instead:
  3. copied gl.5050 (the full WOPC categorization: payee, bank, clientCompany, wopcRef) from the synthetic dup onto the genuine bank record (which keeps its real seqNo + bank reference + counterparty), set status: categorized;
  4. re-pointed each WOPC's transaction.id to the genuine record (ERL-WOPC/2025-020 → lmCh8…, ERL-WOPC/2026-002 → RMG7…) — note 2025-020 is signed (2026-06-13); re-pointing the tx link does not alter signed content, so no content-lock violation;
  5. deleted the synthetic dup docs.
  6. Cleaned 6 orphan bankSyncSnapshots/{txId} docs for the removed dups.

Verification

  • ERL-OCBC-S: 99 docs, signed balance = +283.30 (was 101 docs / −4,716.70 after step 1; 105 / −4,720.11 originally). Matches the real bank to the cent.
  • 0 remaining 16-digit synthetic-seqNo docs.
  • lmCh8… → gl:[5050], WOPC/2025-020, seq 75205, ref HKIT260131886834.
  • RMG7… → gl:[5050], WOPC/2026-002, seq 31937.
  • Both WOPCs active, transaction.id → genuine twin, not orphaned.
  • Full pre-change backup of all 4 tx docs + 2 WOPC docs + 6 snapshots retained off-repo (session scratchpad backup-recon-final.json).

Statement-level verification (2026-06-30, authoritative)

Owner asked for a cross-check against the real bank statements. Pulled all 19 monthly OCBC statements (ERL_BankStatement_OCBC_*.pdf, Nov 2024 – May 2026) from the owner's Drive via the service account (the Drive connector tool was gated behind an approval this session couldn't grant; the SA — same one used for Firestore — read them directly). ERL-OCBC-S = the OCBC Statement Savings sub-account (integrated acct 757699-831); statements print no per-tx seqNo (that's a Velocity-portal/API field) — the printed reference is the HKIT…/ERLWOPC…/FPS… string, which matches Firestore remarks.

Result — Firestore reproduces the bank's running balance at every monthly boundary, to the cent, ending at 283.30 (e.g. Jan26 3,747.41 · Feb26 96.10 · Apr26 2,283.30 · May26 283.30). A missing/extra tx would break the chain at some cutoff; none do.

Crucially this validated the earlier deletion of the four small "test" rows: the statements show those amounts are real (20260227: 1.00/1.00/0.41 debits HKIT260131886815/816/828, plus the 3,000.00 HKIT260131886834), but their genuine twins are present in Firestore under real short seqNos 75202/75203/75204/75205(/75206) with the matching HKIT references. The deleted rows were synthetic-seqNo duplicates of these — same pattern as the −3,000/−2,000 — so no real transaction was lost. The −3,000 (HKIT260131886834, seq 75205, kept as lmCh8) and −2,000 (ERLWOPC/2026-002, seq 31937, kept as RMG7) each appear exactly once on the statements.

Notes / follow-ups

  • The register export (ERL_Register_BankTransaction_20260630.xlsx) reflected the pre-cleanup state (it contained both −3000 rows and the −2000 r4Se row; the genuine −2000 RMG7 was imported by an OCBC sync on 2026-06-30 05:42 UTC, after the owner downloaded the register, so it isn't in that file). (Correction: an earlier note here claimed the Document Ref / Transaction ID columns showed [object Object] — that was an artifact of a raw-dump script stringifying ExcelJS hyperlink cells; the real cells are proper {text, hyperlink} rich-text links and render correctly in Excel. No exporter bug.)
  • Where the seqNos came from (CORRECTED — proven via updateLog): an earlier draft of this ticket guessed "uncommitted / ad-hoc data load." That was wrong. Each fluke's updateLog subcollection (orphaned but still readable after the doc delete) shows a type=imported event by me@jefferochan.com from the OCBC Velocity sync — i.e. they were created at sync time, through the web app. The same real transactions were synced twice on different dates and recorded different seqNos each time:
    • −3000: synced 2026-02-01 → 2026013104156577; re-synced 2026-04-07 → 75205.
    • −2000: synced 2026-05-05 → 2026050545719968; re-synced 2026-06-30 → 31937.
    • 4 small Jan rows: synced 2026-02-01 (16-digit); re-synced 2026-04-07 (75202-75206). velocity-client.ts has always mapped seqNo: raw.seqNo (verified across all of git history — it never fabricates), so the differing values came from the OCBC Velocity API itself: it returned a 16-digit date-stamped reference for recent transactions that later settles to a short permanent seqNo. Because the two values differ, the seqNo-based dedup (correctly) saw the later sync as new → duplicate. (codex:wopc-ref-repair / system-backfill only relabeled these rows later; they are not the origin.)
  • Root cause = the OCBC seqNo is not stable across syncs (temporary→permanent), so a UID-by-seqNo dedup can't catch a re-import. This is exactly what T-145 must address: dedup on a stable fingerprint (date+amount+running-balance, not the volatile seqNo), store the bank's per-tx running balance, add a running-balance continuity check, and don't silently import into a closed period. Cross-link when T-145 is opened.

Decision log

2026-06-30 — ✅ Read AGENTS.md before this data op. Source: Accounting (Diagnostics) · https://claude.ai/code/session_01G58Y71noihrYCDEDMexmea

Owner explicitly authorized the deletion ("If you confirm that there's a duplication, then please remove the duplicated tx") and the survivor choice ("Use the WOPC ones instead of the 1120") and "you can proceed with your reconciliation." Ticket-only (one-off live data fix); the durable prevention is tracked under T-145.