1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126

-- Core Transaction Record (Your System)
CREATE TABLE transactions (
    id UUID PRIMARY KEY,
    external_id VARCHAR(255) UNIQUE,  -- Order ID, invoice ID
    
    -- Amount details
    amount BIGINT NOT NULL,           -- In smallest currency unit
    currency VARCHAR(3) NOT NULL,
    captured_amount BIGINT DEFAULT 0,
    refunded_amount BIGINT DEFAULT 0,
    
    -- Status tracking
    status VARCHAR(50) NOT NULL,
    gateway_status VARCHAR(50),
    
    -- Gateway references
    gateway VARCHAR(50) NOT NULL,
    gateway_transaction_id VARCHAR(255),
    gateway_authorization_code VARCHAR(50),
    
    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    authorized_at TIMESTAMP WITH TIME ZONE,
    captured_at TIMESTAMP WITH TIME ZONE,
    settled_at TIMESTAMP WITH TIME ZONE,
    
    -- Reconciliation state
    reconciliation_status VARCHAR(50) DEFAULT 'pending',
    -- 'pending', 'matched', 'discrepancy', 'resolved', 'written_off'
    last_reconciled_at TIMESTAMP WITH TIME ZONE
);
 
-- Gateway Transaction Records (From Gateway Reports)
CREATE TABLE gateway_records (
    id UUID PRIMARY KEY,
    gateway VARCHAR(50) NOT NULL,
    gateway_transaction_id VARCHAR(255) NOT NULL,
    report_date DATE NOT NULL,
    
    -- Transaction details from gateway
    transaction_type VARCHAR(50) NOT NULL,
    -- 'authorization', 'capture', 'refund', 'chargeback'
    amount BIGINT NOT NULL,
    currency VARCHAR(3) NOT NULL,
    status VARCHAR(50) NOT NULL,
    
    -- Fee information
    gross_amount BIGINT,
    fee_amount BIGINT,
    net_amount BIGINT,
    
    -- Linking
    merchant_reference VARCHAR(255),  -- Your transaction ID
    card_last4 VARCHAR(4),
    
    -- Metadata
    raw_data JSONB,
    imported_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(gateway, gateway_transaction_id, transaction_type)
);
 
-- Settlement Records (From Bank/Gateway Settlement Reports)
CREATE TABLE settlement_records (
    id UUID PRIMARY KEY,
    gateway VARCHAR(50) NOT NULL,
    settlement_id VARCHAR(255) NOT NULL,
    settlement_date DATE NOT NULL,
    
    -- Settlement amounts
    gross_amount BIGINT NOT NULL,
    fee_total BIGINT NOT NULL,
    net_amount BIGINT NOT NULL,
    currency VARCHAR(3) NOT NULL,
    
    -- Bank details
    bank_reference VARCHAR(255),
    deposit_date DATE,
    
    -- Status
    status VARCHAR(50) DEFAULT 'pending',
    -- 'pending', 'received', 'discrepancy', 'resolved'
    
    actual_deposit_amount BIGINT,
    deposit_discrepancy BIGINT,
    
    imported_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
-- Reconciliation Discrepancies
CREATE TABLE reconciliation_discrepancies (
    id UUID PRIMARY KEY,
    transaction_id UUID REFERENCES transactions(id),
    gateway_record_id UUID REFERENCES gateway_records(id),
    
    discrepancy_type VARCHAR(50) NOT NULL,
    -- 'missing_gateway', 'missing_internal', 'amount_mismatch',
    -- 'status_mismatch', 'settlement_mismatch', 'duplicate'
    
    severity VARCHAR(20) NOT NULL,
    -- 'low', 'medium', 'high', 'critical'
    
    expected_value TEXT,
    actual_value TEXT,
    difference_amount BIGINT,
    
    -- Resolution tracking
    status VARCHAR(50) DEFAULT 'open',
    -- 'open', 'investigating', 'resolved', 'written_off'
    assigned_to VARCHAR(255),
    resolution_notes TEXT,
    resolved_at TIMESTAMP WITH TIME ZONE,
    resolved_by VARCHAR(255),
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
-- Create indexes for efficient reconciliation queries
CREATE INDEX idx_transactions_reconciliation 
    ON transactions(reconciliation_status, created_at);
CREATE INDEX idx_gateway_records_unmatched 
    ON gateway_records(gateway_transaction_id) 
    WHERE merchant_reference IS NULL;
CREATE INDEX idx_discrepancies_open 
    ON reconciliation_discrepancies(status, created_at) 
    WHERE status = 'open';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174

interface ReconciliationResult {
  period: { start: Date; end: Date };
  gateway: string;
  
  summary: {
    totalTransactions: number;
    matched: number;
    missingInGateway: number;
    missingInDatabase: number;
    amountMismatches: number;
    statusMismatches: number;
    matchRate: number;
  };
  
  discrepancies: Discrepancy[];
}
 
class PaymentReconciliationEngine {
  constructor(
    private transactionRepo: TransactionRepository,
    private gatewayRecordRepo: GatewayRecordRepository,
    private discrepancyRepo: DiscrepancyRepository
  ) {}
  
  async reconcile(
    gateway: string,
    startDate: Date,
    endDate: Date
  ): Promise<ReconciliationResult> {
    
    // Step 1: Fetch all transactions for the period
    const internalTxs = await this.transactionRepo.findByPeriod(
      gateway, startDate, endDate
    );
    
    const gatewayRecords = await this.gatewayRecordRepo.findByPeriod(
      gateway, startDate, endDate
    );
    
    // Step 2: Build lookup maps for efficient matching
    const internalByGatewayId = new Map(
      internalTxs.map(tx => [tx.gatewayTransactionId, tx])
    );
    const gatewayById = new Map(
      gatewayRecords.map(r => [r.gatewayTransactionId, r])
    );
    
    const discrepancies: Discrepancy[] = [];
    let matched = 0;
    
    // Step 3: Check each internal transaction against gateway
    for (const tx of internalTxs) {
      const gatewayRecord = gatewayById.get(tx.gatewayTransactionId);
      
      if (!gatewayRecord) {
        // Missing in gateway records
        discrepancies.push(this.createDiscrepancy(
          tx,
          null,
          'missing_gateway',
          this.calculateSeverity('missing_gateway', tx)
        ));
        continue;
      }
      
      // Check for field mismatches
      const fieldDiscrepancies = this.compareFields(tx, gatewayRecord);
      
      if (fieldDiscrepancies.length > 0) {
        discrepancies.push(...fieldDiscrepancies);
      } else {
        matched++;
        await this.transactionRepo.markReconciled(tx.id);
      }
      
      // Mark gateway record as matched
      gatewayById.delete(tx.gatewayTransactionId);
    }
    
    // Step 4: Remaining gateway records are missing internally
    for (const gatewayRecord of gatewayById.values()) {
      discrepancies.push(this.createDiscrepancy(
        null,
        gatewayRecord,
        'missing_internal',
        'critical'  // Always critical - we have money but no record!
      ));
    }
    
    // Step 5: Store discrepancies
    await this.discrepancyRepo.createMany(discrepancies);
    
    return {
      period: { start: startDate, end: endDate },
      gateway,
      summary: {
        totalTransactions: internalTxs.length,
        matched,
        missingInGateway: discrepancies.filter(d => d.type === 'missing_gateway').length,
        missingInDatabase: discrepancies.filter(d => d.type === 'missing_internal').length,
        amountMismatches: discrepancies.filter(d => d.type === 'amount_mismatch').length,
        statusMismatches: discrepancies.filter(d => d.type === 'status_mismatch').length,
        matchRate: matched / internalTxs.length,
      },
      discrepancies,
    };
  }
  
  private compareFields(
    internal: Transaction,
    gateway: GatewayRecord
  ): Discrepancy[] {
    const discrepancies: Discrepancy[] = [];
    
    // Amount comparison (both should be in smallest currency unit)
    if (internal.capturedAmount !== gateway.amount) {
      discrepancies.push(this.createDiscrepancy(
        internal,
        gateway,
        'amount_mismatch',
        this.calculateSeverity('amount_mismatch', internal, 
          Math.abs(internal.capturedAmount - gateway.amount))
      ));
    }
    
    // Status mapping and comparison
    const mappedStatus = this.mapGatewayStatus(gateway.status);
    if (internal.status !== mappedStatus) {
      discrepancies.push(this.createDiscrepancy(
        internal,
        gateway,
        'status_mismatch',
        this.calculateSeverity('status_mismatch', internal)
      ));
    }
    
    // Currency mismatch (should never happen, but verify)
    if (internal.currency !== gateway.currency) {
      discrepancies.push(this.createDiscrepancy(
        internal,
        gateway,
        'currency_mismatch',
        'critical'
      ));
    }
    
    return discrepancies;
  }
  
  private calculateSeverity(
    type: string,
    tx: Transaction | null,
    difference?: number
  ): DiscrepancySeverity {
    // Severity based on discrepancy type and amount
    if (type === 'missing_internal') return 'critical';
    if (type === 'currency_mismatch') return 'critical';
    
    if (type === 'amount_mismatch' && difference) {
      if (difference > 10000) return 'critical';  // > $100
      if (difference > 1000) return 'high';       // > $10
      if (difference > 100) return 'medium';      // > $1
      return 'low';  // Likely rounding
    }
    
    if (type === 'missing_gateway' && tx) {
      // High-value transactions missing from gateway are critical
      if (tx.amount > 100000) return 'critical';  // > $1000
      return 'medium';
    }
    
    return 'low';
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162

interface SettlementReport {
  settlementId: string;
  gateway: string;
  settlementDate: Date;
  
  // Expected amounts from gateway
  expectedGross: number;
  expectedFees: number;
  expectedNet: number;
  currency: string;
  
  // Breakdown by type
  captures: { count: number; amount: number };
  refunds: { count: number; amount: number };
  chargebacks: { count: number; amount: number };
  adjustments: { count: number; amount: number };
  
  // List of transaction IDs in this settlement
  transactionIds: string[];
}
 
interface BankStatement {
  date: Date;
  description: string;
  reference: string;
  amount: number;
  currency: string;
}
 
class SettlementReconciliationEngine {
  async reconcileSettlement(
    settlementReport: SettlementReport,
    bankStatements: BankStatement[]
  ): Promise<SettlementReconciliationResult> {
    
    // Step 1: Find matching bank deposit
    const matchingDeposit = this.findMatchingDeposit(
      settlementReport,
      bankStatements
    );
    
    if (!matchingDeposit) {
      return {
        status: 'pending',
        expectedAmount: settlementReport.expectedNet,
        actualAmount: null,
        discrepancy: null,
        message: 'Deposit not yet received',
      };
    }
    
    // Step 2: Compare amounts
    const discrepancy = matchingDeposit.amount - settlementReport.expectedNet;
    
    if (Math.abs(discrepancy) <= 100) {  // $1 tolerance for rounding
      return {
        status: 'matched',
        expectedAmount: settlementReport.expectedNet,
        actualAmount: matchingDeposit.amount,
        discrepancy: discrepancy,
        message: 'Settlement matched',
      };
    }
    
    // Step 3: Investigate discrepancy
    const investigation = await this.investigateDiscrepancy(
      settlementReport,
      matchingDeposit,
      discrepancy
    );
    
    return {
      status: 'discrepancy',
      expectedAmount: settlementReport.expectedNet,
      actualAmount: matchingDeposit.amount,
      discrepancy: discrepancy,
      investigation,
      message: 'Settlement discrepancy detected',
    };
  }
  
  private findMatchingDeposit(
    settlement: SettlementReport,
    statements: BankStatement[]
  ): BankStatement | null {
    // Match by reference number if gateway provides it
    if (settlement.settlementId) {
      const exactMatch = statements.find(s => 
        s.reference.includes(settlement.settlementId)
      );
      if (exactMatch) return exactMatch;
    }
    
    // Match by amount and date range
    const expectedDate = new Date(settlement.settlementDate);
    expectedDate.setDate(expectedDate.getDate() + 2); // T+2 expected
    
    const candidates = statements.filter(s => {
      const daysDiff = Math.abs(
        (s.date.getTime() - expectedDate.getTime()) / (1000 * 60 * 60 * 24)
      );
      return daysDiff <= 3 &&  // Within 3 days of expected
             Math.abs(s.amount - settlement.expectedNet) < settlement.expectedNet * 0.01;  // Within 1%
    });
    
    if (candidates.length === 1) {
      return candidates[0];
    }
    
    if (candidates.length > 1) {
      // Multiple candidates - flag for manual review
      console.warn('Multiple deposit candidates found for settlement');
    }
    
    return null;
  }
  
  private async investigateDiscrepancy(
    settlement: SettlementReport,
    deposit: BankStatement,
    discrepancy: number
  ): Promise<DiscrepancyInvestigation> {
    const causes: string[] = [];
    
    // Check for late refunds
    const refundsAfterCutoff = await this.findRefundsAfterSettlement(
      settlement.transactionIds,
      settlement.settlementDate
    );
    if (refundsAfterCutoff.length > 0) {
      const refundTotal = refundsAfterCutoff.reduce((sum, r) => sum + r.amount, 0);
      if (Math.abs(discrepancy + refundTotal) < 100) {
        causes.push(`Late refunds: ${refundsAfterCutoff.length} refunds totaling ${refundTotal}`);
      }
    }
    
    // Check for chargebacks
    const chargebacks = await this.findChargebacks(
      settlement.transactionIds,
      settlement.settlementDate
    );
    if (chargebacks.length > 0) {
      const chargebackTotal = chargebacks.reduce((sum, c) => sum + c.amount, 0);
      causes.push(`Chargebacks: ${chargebacks.length} totaling ${chargebackTotal}`);
    }
    
    // Check for fee adjustments
    const feeAdjustments = await this.findFeeAdjustments(settlement.settlementId);
    if (feeAdjustments.length > 0) {
      const adjustmentTotal = feeAdjustments.reduce((sum, a) => sum + a.amount, 0);
      causes.push(`Fee adjustments: ${feeAdjustments.length} totaling ${adjustmentTotal}`);
    }
    
    return {
      discrepancyAmount: discrepancy,
      possibleCauses: causes,
      recommendedAction: causes.length > 0 
        ? 'Review identified causes' 
        : 'Escalate to finance team',
    };
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162

type ResolutionAction = 
  | { type: 'auto_accept'; reason: string }
  | { type: 'auto_resolve'; action: string }
  | { type: 'wait_and_retry'; delayMinutes: number }
  | { type: 'query_gateway'; gateway: string }
  | { type: 'manual_review'; queue: string; priority: string }
  | { type: 'escalate'; team: string };
 
class DiscrepancyResolver {
  determineAction(discrepancy: Discrepancy): ResolutionAction {
    switch (discrepancy.type) {
      case 'amount_mismatch':
        return this.resolveAmountMismatch(discrepancy);
        
      case 'missing_gateway':
        return this.resolveMissingGateway(discrepancy);
        
      case 'missing_internal':
        return this.resolveMissingInternal(discrepancy);
        
      case 'status_mismatch':
        return this.resolveStatusMismatch(discrepancy);
        
      default:
        return { type: 'manual_review', queue: 'general', priority: 'medium' };
    }
  }
  
  private resolveAmountMismatch(discrepancy: Discrepancy): ResolutionAction {
    const difference = Math.abs(discrepancy.differenceAmount || 0);
    
    // Auto-accept small differences (rounding)
    if (difference <= 100) {  // $1 or less
      return {
        type: 'auto_accept',
        reason: 'Within acceptable rounding threshold',
      };
    }
    
    // Check if difference matches known fee patterns
    if (this.matchesFeePattern(discrepancy)) {
      return {
        type: 'auto_resolve',
        action: 'Update fee records to match gateway',
      };
    }
    
    // Larger differences need investigation
    if (difference > 10000) {  // > $100
      return {
        type: 'escalate',
        team: 'finance',
      };
    }
    
    return {
      type: 'manual_review',
      queue: 'amount_discrepancies',
      priority: difference > 1000 ? 'high' : 'medium',
    };
  }
  
  private resolveMissingGateway(discrepancy: Discrepancy): ResolutionAction {
    const txAge = Date.now() - discrepancy.transaction!.createdAt.getTime();
    const hoursOld = txAge / (1000 * 60 * 60);
    
    // Very recent transactions might just be delayed
    if (hoursOld < 4) {
      return {
        type: 'wait_and_retry',
        delayMinutes: 60,
      };
    }
    
    // Older transactions - query gateway directly
    if (hoursOld < 48) {
      return {
        type: 'query_gateway',
        gateway: discrepancy.transaction!.gateway,
      };
    }
    
    // Very old missing transactions - likely webhook failure
    return {
      type: 'manual_review',
      queue: 'missing_gateway',
      priority: 'high',
    };
  }
  
  private resolveMissingInternal(discrepancy: Discrepancy): ResolutionAction {
    // This is critical - we received money but have no record!
    return {
      type: 'escalate',
      team: 'payments_critical',
    };
  }
  
  private resolveStatusMismatch(discrepancy: Discrepancy): ResolutionAction {
    const internal = discrepancy.transaction!;
    const gateway = discrepancy.gatewayRecord!;
    
    // Gateway says captured, we say authorized
    // Safe to update our record
    if (internal.status === 'authorized' && gateway.status === 'captured') {
      return {
        type: 'auto_resolve',
        action: 'Update internal status to captured',
      };
    }
    
    // Gateway says failed, we say succeeded
    // This is bad - we might have shipped product without payment
    if (internal.status === 'succeeded' && gateway.status === 'failed') {
      return {
        type: 'escalate',
        team: 'payments_critical',
      };
    }
    
    return {
      type: 'manual_review',
      queue: 'status_discrepancies',
      priority: 'medium',
    };
  }
  
  async executeResolution(
    discrepancy: Discrepancy,
    action: ResolutionAction
  ): Promise<ResolutionResult> {
    switch (action.type) {
      case 'auto_accept':
        await this.markResolved(discrepancy, action.reason);
        return { success: true, action: 'accepted' };
        
      case 'auto_resolve':
        await this.applyAutoFix(discrepancy, action.action);
        await this.markResolved(discrepancy, action.action);
        return { success: true, action: 'auto_fixed' };
        
      case 'wait_and_retry':
        await this.scheduleRetry(discrepancy, action.delayMinutes);
        return { success: true, action: 'scheduled_retry' };
        
      case 'query_gateway':
        const gatewayStatus = await this.queryGateway(
          action.gateway,
          discrepancy.transaction!.gatewayTransactionId
        );
        return await this.handleGatewayResponse(discrepancy, gatewayStatus);
        
      case 'manual_review':
        await this.createReviewTicket(discrepancy, action);
        return { success: true, action: 'queued_for_review' };
        
      case 'escalate':
        await this.escalateToTeam(discrepancy, action.team);
        return { success: true, action: 'escalated' };
    }
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

interface ReconciliationMetrics {
  timestamp: Date;
  period: 'hourly' | 'daily';
  
  // Matching metrics
  transactionsProcessed: number;
  transactionsMatched: number;
  matchRate: number;
  
  // Discrepancy metrics
  discrepanciesOpened: number;
  discrepanciesResolved: number;
  discrepanciesOpen: number;
  openDiscrepancyValue: number;
  
  // By type breakdown
  byType: Record<string, {
    count: number;
    value: number;
    avgResolutionMinutes: number;
  }>;
  
  // By severity
  bySeverity: Record<string, number>;
  
  // Settlement metrics
  settlementsExpected: number;
  settlementsReceived: number;
  settlementsPending: number;
  avgSettlementDelayDays: number;
}
 
class ReconciliationMonitor {
  constructor(
    private metricsClient: MetricsClient,
    private alertingClient: AlertingClient
  ) {}
  
  async collectAndPublishMetrics(): Promise<ReconciliationMetrics> {
    const metrics = await this.calculateMetrics();
    
    // Publish to monitoring system
    this.metricsClient.gauge('reconciliation.match_rate', metrics.matchRate);
    this.metricsClient.gauge('reconciliation.open_count', metrics.discrepanciesOpen);
    this.metricsClient.gauge('reconciliation.open_value', metrics.openDiscrepancyValue);
    
    for (const [type, data] of Object.entries(metrics.byType)) {
      this.metricsClient.gauge(`reconciliation.by_type.${type}`, data.count);
    }
    
    // Check alerting thresholds
    await this.checkAlerts(metrics);
    
    return metrics;
  }
  
  private async checkAlerts(metrics: ReconciliationMetrics): Promise<void> {
    // Critical: Any critical severity discrepancies
    if (metrics.bySeverity['critical'] > 0) {
      await this.alertingClient.critical({
        title: 'Critical Reconciliation Discrepancies',
        message: `${metrics.bySeverity['critical']} critical discrepancies require immediate attention`,
        tags: ['reconciliation', 'critical'],
      });
    }
    
    // High: Match rate below threshold
    if (metrics.matchRate < 0.995) {
      await this.alertingClient.high({
        title: 'Reconciliation Match Rate Low',
        message: `Match rate ${(metrics.matchRate * 100).toFixed(2)}% is below 99.5% threshold`,
        tags: ['reconciliation', 'match_rate'],
      });
    }
    
    // Medium: Open discrepancy value too high
    if (metrics.openDiscrepancyValue > 5000000) {  // $50,000
      await this.alertingClient.medium({
        title: 'High Open Discrepancy Value',
        message: `$${(metrics.openDiscrepancyValue / 100).toLocaleString()} in unresolved discrepancies`,
        tags: ['reconciliation', 'value'],
      });
    }
    
    // Warning: Settlement delays
    if (metrics.avgSettlementDelayDays > 5) {
      await this.alertingClient.warning({
        title: 'Settlement Delays Detected',
        message: `Average settlement delay is ${metrics.avgSettlementDelayDays.toFixed(1)} days`,
        tags: ['reconciliation', 'settlement'],
      });
    }
  }
}