Data Architecture: Review bidirectional FKs and denormalization between auth/processed transaction tables

Summary

The current data architecture for transaction reconciliation uses bidirectional foreign keys and field copying between omnipay_auth_txs and processed_txs tables. This pattern warrants review for potential simplification.

Current Design

omnipay_auth_txs                      processed_txs
─────────────────                     ─────────────
omnipay_auth_tx_id (PK)               processed_tx_id (PK)
auth_code                             auth_code
auth_date                             tx_date
auth_at                               posting_date
card_no                               card_no
tx_amount                             tx_amount
...                                   ...

← COPIED FROM processed_txs:          ← COPIED FROM omnipay_auth_txs:
  merchant_id                           auth_date
  terminal_id                           auth_time
  processed_tx_id (FK →)                auth_at
  is_matched_processed                  file_date
                                        card_acceptor_name
                                        omnipay_auth_tx_id (FK →)
                                        is_matched_auth

Issues with Current Approach

1. Bidirectional FKs - Both tables reference each other, creating circular dependency
2. Data duplication - Fields copied between tables instead of JOINed when needed
3. Double writes on reconciliation - Must update BOTH tables, increasing failure risk
4. Inconsistency risk - If one update fails, tables are out of sync
5. Maintenance overhead - Changes require updating both tables

Additional Complexity

processed_txs is a polymorphic table receiving data from multiple sources:

• Omnipay (matched with omnipay_auth_txs)
• Paysphere (different auth flow)
• Future payment systems

This means the FK relationship and reconciliation logic may need to be source-aware.

Potential Alternatives

Option A: Single FK Direction

-- Only processed_txs references auth
processed_txs.omnipay_auth_tx_id → omnipay_auth_txs.omnipay_auth_tx_id

-- JOIN when auth data needed
SELECT p.*, a.auth_date, a.auth_at 
FROM processed_txs p
LEFT JOIN omnipay_auth_txs a USING (omnipay_auth_tx_id)

Option B: Intermediate Reconciliation Table

-- Separate table for the relationship
tx_reconciliation (
  processed_tx_id FK,
  auth_tx_id FK,
  auth_source ENUM('omnipay', 'paysphere', ...),
  matched_at TIMESTAMP
)

Option C: Source-specific Auth Tables

-- Each source has its own auth table
omnipay_auth_txs → processed_txs (for Omnipay)
paysphere_auth_txs → processed_txs (for Paysphere)

Questions to Resolve

1. Why was the denormalized design chosen? (Performance? Reporting? Legacy compatibility?)
2. Do reports/exports depend on the copied fields being in processed_txs?
3. Is .NET compatibility a constraint?
4. How should multi-source auth be handled long-term?

Impact Assessment Needed

Before refactoring:

• [ ] Audit all queries using copied fields
• [ ] Check report dependencies
• [ ] Evaluate Spanner JOIN performance for this use case
• [ ] Consider migration path for existing data

Related

• Reconciliation performance fix (7-day filter + loop)
• auth_time column discrepancy between tables

---
Created during investigation of reconciliation timeout issues