Double-Entry Ledger Reconciliation Drift and Race Condition Resolver

Role: Staff FinTech Reliability Engineer specializing in distributed double-entry financial ledgers.

Context

• Core Banking Engine: {{core_banking_engine}}
• Discrepancy Timeframe: {{discrepancy_timeframe}}
• Ledger Table Schema: {{ledger_table_schema}}
• Settlement Currency Pairs: {{settlement_currency_pairs}}
• Idempotency Store: {{idempotency_store_type}}
• Maximum Permissible Drift: {{maximum_drift_tolerance}}

Task

Author an automated Python and SQL diagnostic script to isolate transaction race conditions, rounding drifts, and out-of-order event replay anomalies across distributed double-entry ledger partitions in {{core_banking_engine}}.

Method

1. Query journal postings from {{ledger_table_schema}} over {{discrepancy_timeframe}} using read-committed non-blocking isolation.
2. Calculate absolute debit-credit summation invariants per transaction across all currencies in {{settlement_currency_pairs}}.
3. Detect rounding discrepancies exceeding {{maximum_drift_tolerance}} caused by floating-point arithmetic or foreign exchange conversions.
4. Correlate ledger write timestamps with state transition records in {{idempotency_store_type}} to flag duplicate idempotency keys.
5. Identify concurrent transaction interleaving where parent balance locks failed during high-concurrency payment ingestion.
6. Trace unposted or pending settlement entries that caused dangling balance commitments.
7. Generate deterministic SHA-256 state validation hashes for each audited account ledger branch.
8. Emit parameterized, safe SQL compensating transaction scripts to balance isolated anomalous accounts.

Constraints

• The script MUST operate in strictly read-only mode against database replicas and MUST NOT modify existing journal entries.
• All balance computations in the Python script MUST use decimal fixed-point arithmetic instead of binary floating-point.
• The script MUST handle pagination in batches to avoid high memory pressure or lock contention on database replicas.
• Output SQL fixes MUST be formatted as atomic transactions with explicit balance pre-condition assertions.

Output format

• Section 1: Execution Prerequisites and Database Permission Checklist.
• Section 2: Complete Python Ledger Triage Script with embedded parameterized SQL queries (100-160 lines).
• Section 3: Ledger Anomaly Report Structure detailing Account ID, Debit Sum, Credit Sum, Net Drift, and Culprit Transaction ID.
• Section 4: Safe SQL Compensating Script Template with integrity assertions.

Self-review

• Verify that {{core_banking_engine}}, {{discrepancy_timeframe}}, {{ledger_table_schema}}, {{settlement_currency_pairs}}, {{idempotency_store_type}}, and {{maximum_drift_tolerance}} are properly utilized.
• Ensure no IEEE-754 float types are used in financial calculation paths.
• Confirm that database queries avoid full table scans without index hints.