Local Reconciliation Validation Test

0a. Scan and display

# Count TRANSACTN records in each prod GRRCN file
for f in ~/Documents/amex-reconcliation/DINIIKKJPA69925.GRRCN.*.decrypted; do
  date=$(basename "$f" | sed -E 's/.*GRRCN\.([0-9]{6}).*/\1/')
  count=$(grep -c '"TRANSACTN"' "$f" 2>/dev/null || echo 0)
  # Convert YYMMDD to targetDate (JST = file date + 1 day)
  echo "$date: $count txns"
done

# Count data rows in each ESDR file
for f in ~/Documents/amex-reconcliation/external_settlement_detail_report_*.csv; do
  date=$(basename "$f" | sed -E 's/.*report_(.*)\.csv/\1/')
  count=$(($(wc -l < "$f") - 1))
  echo "$date: $count rows"
done

0b. Display as table and ask user

Show a combined table like this (match GRRCN date YYMMDD with ESDR date YYYY_MM_DD by same raw date):

| # | GRRCN Date | ESDR Date   | targetDate (JST) | GRRCN Txns | ESDR Rows | Pair? |
|---|------------|-------------|------------------|------------|-----------|-------|
| 1 | 260302     | 2026_03_02  | 2026-03-03       | 5          | 5         | YES   |
| 2 | 260303     | 2026_03_03  | 2026-03-04       | 21         | 21        | YES   |
...

Then ask: "Which date(s) do you want to test? (e.g., 1, or 1,3,5)"

Also ask: "Which job(s) to run?"

• validation — reconciliation-validation only
• unified-ledger — reconciliation-unified-ledger only
• legacy-events — reconciliation-legacy-events only
• all — run all 3 in sequence (validation → unified-ledger → legacy-events)

0c. Date matching rules

• GRRCN filename date YYMMDD corresponds to ESDR date YYYY_MM_DD (same raw date)
• targetDate (JST) = GRRCN file date + 1 day (UTC-7 evening → next day in JST)
• A "pair" exists only when both GRRCN and ESDR files are present for the same raw date

Step 1: Upload Files to GCS

Upload selected GRRCN and ESDR files from ~/Documents/amex-reconcliation/ to GCS:

# GRRCN
gsutil cp ~/Documents/amex-reconcliation/<grrcn-filename> gs://dinii-payment-develop-amex-global-reconciliation-file/

# ESDR
gsutil cp ~/Documents/amex-reconcliation/<esdr-filename> gs://dinii-payment-develop-adyen-reconciliation-report/

Check if already uploaded:

gsutil ls gs://dinii-payment-develop-amex-global-reconciliation-file/ | grep <grrcn-filename>
gsutil ls gs://dinii-payment-develop-adyen-reconciliation-report/ | grep <esdr-filename>

Step 2: Insert Common Test Data (All Jobs)

All 3 jobs require settlementDeliverableStatus + settlementReportFile records.

-- DB: postgres://postgres:[email protected]:45432/online_payment_development

-- Insert deliverable status (status depends on job)
-- For validation: FILE_RECEIVED
-- For unified-ledger / legacy-events: FILE_VALIDATED
INSERT INTO "settlementDeliverableStatus" (id, provider, "reportType", "targetDate", status, "receivedAt", "createdAt", "updatedAt")
VALUES (
  '<uuid>', 'AMEX', 'GRRCN', '<targetDate-YYYY-MM-DD>',
  '<FILE_RECEIVED or FILE_VALIDATED>',
  now(), now(), now()
);

-- GRRCN file record (filePath = basename only)
INSERT INTO "settlementReportFile" (id, "settlementDeliverableStatusId", version, "filePath", source, "receivedAt", "createdAt", "updatedAt")
VALUES ('<uuid-grrcn>', '<deliverable-status-uuid>', 1, '<grrcn-filename>', 'SFTP', now(), now(), now());

-- ESDR file record (filePath must be full GCS URL format)
INSERT INTO "settlementReportFile" (id, "settlementDeliverableStatusId", version, "filePath", source, "receivedAt", "createdAt", "updatedAt")
VALUES ('<uuid-esdr>', '<deliverable-status-uuid>', 2,
  'https://storage.googleapis.com/dinii-payment-develop-adyen-reconciliation-report/<esdr-filename>',
  'WEBHOOK', now(), now(), now());

File type classification: isEsdrFile() checks if filePath contains external_settlement_detail_report. ESDR path extraction: Download function uses pathParts.slice(4).join("/") to extract GCS object name from URL. GRRCN path extraction: Download function uses basename of filePath (after last /).

Step 3: Seed Production Data for Legacy Events (legacy-events job ONLY)

Skip this step for validation and unified-ledger jobs.

The reconciliation-legacy-events job reads existing payment event records from DB to create settled/refunded events. For AMEX, all transactions are IPP (terminal) — no ECOM.

3a. Extract pspReferences from ESDR

Parse the selected ESDR CSV to get PSP Reference values (column index varies — check header row):

# Extract PSP References from ESDR file
head -1 ~/Documents/amex-reconcliation/<esdr-file> | tr ',' '\n' | grep -n "Psp Reference"
# Then extract that column for all data rows

3b. Query BigQuery for IPP event data

BQ Project: dinii-self-prod Dataset: online_payment Table prefix: public_

Query 1: Get authorizedEvent records by pspReference

SELECT id, "terminalId", "shopId", "merchantReference", "pspReference",
       amount, "paymentMethod", "merchantAccountId", "createdAt", "recordOrigin",
       "transactionFeeRate", "netAmount", "feeAmount"
FROM `dinii-self-prod.online_payment.public_adyenPaymentReportTerminalPaymentAuthorizedEvent`
WHERE "pspReference" IN (<comma-separated pspReferences from ESDR>)

Query 2: Get capturedEvent records (optional, for payoutJobScheduleId)

SELECT id, "originalReference", "createdAt", "recordOrigin", "payoutJobScheduleId"
FROM `dinii-self-prod.online_payment.public_adyenPaymentReportTerminalPaymentCapturedEvent`
WHERE "originalReference" IN (<same pspReferences>)

Query 3: Get canceledEvent records (for REFUNDED transactions)

Only needed if GRRCN contains ADJUSTMENT records (refunds).

SELECT id, "originalReference", "modificationReference", "createdAt", "recordOrigin",
       "canceledAmount", "transactionFeeRate", "canceledNetAmount", "canceledFeeAmount",
       "payoutJobScheduleId"
FROM `dinii-self-prod.online_payment.public_adyenPaymentReportTerminalPaymentCanceledEvent`
WHERE "originalReference" IN (<same pspReferences>)

Query 4: Get payoutJobSchedule records

SELECT id, "startingDate", "closingDate", "depositCycle", status, "settlementProvider",
       "remittanceRequestDate", "scheduledDepositDate"
FROM `dinii-self-prod.online_payment.public_payoutJobSchedule`
WHERE "settlementProvider" = 'AMEX'
  AND "startingDate" <= '<latest-transaction-date>'
  AND "closingDate" >= '<earliest-transaction-date>'
  AND status != 'REMITTED'

Query 5: Get bankAccountShopAssignment + parents

-- Get shopIds from authorizedEvent results
SELECT ba.id as "assignmentId", ba."bankAccountId", ba."shopId", ba."depositCycleType",
       ba."validFrom", ba."validTo"
FROM `dinii-self-prod.online_payment.public_bankAccountShopAssignment` ba
WHERE ba."shopId" IN (<shopIds from authorizedEvent results>)
  AND ba."validTo" IS NULL

Query 6: FK parent records

-- bankAccount
SELECT id, "corporationId" FROM `dinii-self-prod.online_payment.public_bankAccount`
WHERE id IN (<bankAccountIds from assignment results>)

-- corporationEntityRef
SELECT id FROM `dinii-self-prod.online_payment.public_corporationEntityRef`
WHERE id IN (<corporationIds from bankAccount results>)

-- shopEntityRef
SELECT id, "companyId" FROM `dinii-self-prod.online_payment.public_shopEntityRef`
WHERE id IN (<shopIds>)

-- companyEntityRef
SELECT id FROM `dinii-self-prod.online_payment.public_companyEntityRef`
WHERE id IN (<companyIds from shopEntityRef results>)

3c. Insert data into local DB

Insertion order matters due to FK constraints:

-- Step 1: FK parent refs (no dependencies)
INSERT INTO "corporationEntityRef" (id, "createdAt", "updatedAt") VALUES (...) ON CONFLICT DO NOTHING;
INSERT INTO "companyEntityRef" (id, "createdAt", "updatedAt") VALUES (...) ON CONFLICT DO NOTHING;

-- Step 2: shopEntityRef, bankAccount (depend on Step 1)
INSERT INTO "shopEntityRef" (id, "companyId", "createdAt", "updatedAt") VALUES (...) ON CONFLICT DO NOTHING;
INSERT INTO "bankAccount" (id, "corporationId", "createdAt", "updatedAt") VALUES (...) ON CONFLICT DO NOTHING;

-- Step 3: bankAccountShopAssignment (depends on Step 2)
INSERT INTO "bankAccountShopAssignment" (id, "bankAccountId", "shopId", "depositCycleType", "validFrom", "validTo", "createdAt", "updatedAt")
VALUES (...) ON CONFLICT DO NOTHING;

-- Step 4: payoutJobSchedule (depends on enum tables only)
INSERT INTO "payoutJobSchedule" (id, "startingDate", "closingDate", "depositCycle", status, "settlementProvider", "createdAt", "updatedAt")
VALUES (...) ON CONFLICT DO NOTHING;

-- Step 5: IPP events (depends on enum tables)
INSERT INTO "adyenPaymentReportTerminalPaymentAuthorizedEvent"
  (id, "terminalId", "shopId", "merchantReference", "pspReference", amount,
   "paymentMethod", "merchantAccountId", "createdAt", "recordOrigin",
   "transactionFeeRate", "netAmount", "feeAmount")
VALUES (...) ON CONFLICT DO NOTHING;

-- Step 6: capturedEvent (depends on authorizedEvent via originalReference FK)
INSERT INTO "adyenPaymentReportTerminalPaymentCapturedEvent"
  (id, "originalReference", "createdAt", "recordOrigin", "payoutJobScheduleId")
VALUES (...) ON CONFLICT DO NOTHING;

-- Step 7: canceledEvent (depends on authorizedEvent via originalReference FK)
INSERT INTO "adyenPaymentReportTerminalPaymentCanceledEvent"
  (id, "originalReference", "modificationReference", "createdAt", "recordOrigin",
   "canceledAmount", "transactionFeeRate", "canceledNetAmount", "canceledFeeAmount",
   "payoutJobScheduleId")
VALUES (...) ON CONFLICT DO NOTHING;

Use ON CONFLICT DO NOTHING for all inserts to make the script idempotent.

3d. Verify seeded data

-- Count seeded records
SELECT 'authorizedEvent' as tbl, count(*) FROM "adyenPaymentReportTerminalPaymentAuthorizedEvent"
WHERE "pspReference" IN (<pspReferences>)
UNION ALL
SELECT 'capturedEvent', count(*) FROM "adyenPaymentReportTerminalPaymentCapturedEvent"
WHERE "originalReference" IN (<pspReferences>)
UNION ALL
SELECT 'canceledEvent', count(*) FROM "adyenPaymentReportTerminalPaymentCanceledEvent"
WHERE "originalReference" IN (<pspReferences>)
UNION ALL
SELECT 'payoutJobSchedule', count(*) FROM "payoutJobSchedule"
WHERE "settlementProvider" = 'AMEX'
UNION ALL
SELECT 'bankAccountShopAssignment', count(*) FROM "bankAccountShopAssignment"
WHERE "shopId" IN (<shopIds>);

Step 4: Run the Job

cd packages/dinii-self-backend

# Validation
JOB_NAME=reconciliation-validation bun run dev:job -- --settlement-deliverable-status-id <uuid>

# Unified Ledger
JOB_NAME=reconciliation-unified-ledger bun run dev:job -- --settlement-deliverable-status-id <uuid>

# Legacy Events
JOB_NAME=reconciliation-legacy-events bun run dev:job -- --settlement-deliverable-status-id <uuid>

Job execution order (when running "all")

1. validation (status: FILE_RECEIVED → FILE_VALIDATED)
2. unified-ledger (status: FILE_VALIDATED, writes to unifiedSettlementLedger)
3. legacy-events (status: FILE_VALIDATED, creates settled/refunded events)

If running all 3:

• Insert with status = 'FILE_RECEIVED'
• Run validation first (it changes status to FILE_VALIDATED)
• Then run unified-ledger and legacy-events (both require FILE_VALIDATED)

If running only unified-ledger or legacy-events:

• Insert with status = 'FILE_VALIDATED' directly

Step 5: Verify Results

For validation job:

SELECT id, provider, "targetDate", status, "lastError"
FROM "settlementDeliverableStatus" WHERE id = '<uuid>';

SELECT id, "batchType", status, "startedAt", "endedAt", "executionDetails", "errorDetails"
FROM "reconciliationExecutionResult" WHERE "settlementDeliverableStatusId" = '<uuid>';

For unified ledger job:

SELECT id, "settlementProvider", "recordType", "originalPspReference", "merchantAccount",
       "companyAccount", "grossCredit", "grossDebit", "netCredit", "netDebit"
FROM "unifiedSettlementLedger"
WHERE "settlementReportFileId" IN (
  SELECT id FROM "settlementReportFile" WHERE "settlementDeliverableStatusId" = '<uuid>'
);

For legacy events job:

-- Check execution result
SELECT id, "batchType", status, "executionDetails", "errorDetails"
FROM "reconciliationExecutionResult"
WHERE "settlementDeliverableStatusId" = '<uuid>' AND "batchType" = 'SAVE_TO_LEGACY_EVENTS';

-- Check created settled events
SELECT id, "pspReference", "shopId", "amount", "netAmount", "feeAmount"
FROM "adyenPaymentTerminalTransactionSettledEvent"
WHERE "pspReference" IN (<pspReferences>);

-- Check created refunded events
SELECT id, "pspReference", "shopId", "amount", "netAmount", "feeAmount", "modificationReference"
FROM "adyenPaymentTerminalTransactionRefundedEvent"
WHERE "pspReference" IN (<pspReferences>);

Step 6: Clean Up

-- Legacy events output (delete first due to FK)
DELETE FROM "adyenPaymentTerminalTransactionRefundedEvent" WHERE "pspReference" IN (<pspReferences>);
DELETE FROM "adyenPaymentTerminalTransactionSettledEvent" WHERE "pspReference" IN (<pspReferences>);

-- Unified ledger output
DELETE FROM "unifiedSettlementLedger" WHERE "settlementReportFileId" IN (
  SELECT id FROM "settlementReportFile" WHERE "settlementDeliverableStatusId" = '<uuid>'
);

-- Execution results
DELETE FROM "reconciliationExecutionResult" WHERE "settlementDeliverableStatusId" = '<uuid>';

-- Common test data
DELETE FROM "settlementReportFile" WHERE "settlementDeliverableStatusId" = '<uuid>';
DELETE FROM "settlementDeliverableStatus" WHERE id = '<uuid>';

-- Seeded prod data (legacy-events only) — use with care
-- These are shared across test runs if same shopIds/pspReferences appear in multiple dates
-- Only delete if you're done testing all dates
DELETE FROM "adyenPaymentReportTerminalPaymentCanceledEvent" WHERE "originalReference" IN (<pspReferences>);
DELETE FROM "adyenPaymentReportTerminalPaymentCapturedEvent" WHERE "originalReference" IN (<pspReferences>);
DELETE FROM "adyenPaymentReportTerminalPaymentAuthorizedEvent" WHERE "pspReference" IN (<pspReferences>);
-- bankAccountShopAssignment, bankAccount, shopEntityRef, etc. — leave in place for reuse

Step 7: Re-run

To re-run on the same record:

-- Reset to FILE_RECEIVED (for re-running from validation)
UPDATE "settlementDeliverableStatus"
SET status = 'FILE_RECEIVED', "lastError" = NULL, "fileApprovedAt" = NULL, "updatedAt" = now()
WHERE id = '<uuid>';

-- Or reset to FILE_VALIDATED (for re-running unified-ledger or legacy-events only)
UPDATE "settlementDeliverableStatus"
SET status = 'FILE_VALIDATED', "lastError" = NULL, "fileApprovedAt" = NULL, "updatedAt" = now()
WHERE id = '<uuid>';

-- Also delete previous execution results for the batch type being re-run
DELETE FROM "reconciliationExecutionResult"
WHERE "settlementDeliverableStatusId" = '<uuid>' AND "batchType" = '<VALIDATION|SAVE_TO_UNIFIED_LEDGER|SAVE_TO_LEGACY_EVENTS>';

Architecture Notes

• File source: ~/Documents/amex-reconcliation/ (all GRRCN decrypted + ESDR CSV files)
• Prod GRRCN prefix: DINIIKKJPA69925 (use this unless testing beta with DINIIKKJPA69967)
• File structure: 1 GRRCN + 1 ESDR per settlementDeliverableStatus
• File type classification: isEsdrFile() checks if filePath contains external_settlement_detail_report
• ESDR GCS path: Must be full URL format https://storage.googleapis.com/{bucket}/{path} — download extracts via pathParts.slice(4).join("/")
• GRRCN GCS path: Uses basename only (after last /)
• ESDR ↔ GRRCN join key: invoiceReferenceNumber (NOT acquirerReferenceNumber)
• GRRCN timezone: UTC-7; targetDate = file date + 1 day (JST)
• Allowed statuses: validation accepts FILE_RECEIVED/FILE_INVALIDATED; unified-ledger and legacy-events accept FILE_VALIDATED
• AMEX = IPP only: No ECOM transactions exist for AMEX. SettlementEventCreatorService processes only processTerminalSettledEvent and processTerminalRefundedEvent
• Legacy events payoutJobScheduleId resolution: First tries inherited ID from capturedEvent/canceledEvent; falls back to bankAccountShopAssignment → payoutJobSchedule lookup by shopId + transactionDate + provider
• Legacy events idempotency: settledEvent checks pspReference uniqueness; refundedEvent checks modificationReference uniqueness
• BQ project: dinii-self-prod, dataset: online_payment, table prefix: public_
• DB: postgres://postgres:[email protected]:45432/online_payment_development
• Validation flow: currencyValidator → fileIntegrityValidator → headerRecordValidator → trailerRecordCountValidator → submissionAmountsValidator → summaryFieldsValidator → ESDR cross-reference
• Unified ledger flow: Parse ESDR → Build lookup map → Parse GRRCN → Combine via acquirerReference → Convert to UnifiedSettlementData