T+2 Reconciliation and Card Authorisation/Capture Checks

Goal

You read the holiday table to recompute the T+2 settlement date on a business-day basis and find the wrong trades, reconcile the trade book and the settlement book in four directions, split settlement fails by reason and produce the amounts as well, and explain the difference between card authorization and capture completely and find the double charges.

Why it matters

T+2 in securities and authorization/capture in cards are different domains, but the structure is the same. The event is split in two, the state lives between them, and if you do not watch that state, money leaks. This lab walks through each of those two structures once.

The four things this lab enforces are all rules from the field. Keep holidays as data — if you hard-code them, every time a temporary holiday is designated you need a deployment, and if the deployment is late, an incident happens that week. Reconcile per record, in both directions — if you compare only totals, the differences in the two directions cancel each other. Split settlement fails by reason — if you count them together, no owning department is determined and nobody handles them. Find double charges by idempotency key — by authorization number they never come up.

The incident is this. The settlement team reported that the fail balance in the second week of September is three times normal, and in the same week complaints about double charges from a particular merchant piled up. The reference date is 2026-09-16.

The business-day calculation rule is as follows. Starting from the day after the trade date, advance one day at a time, count as a business day each day that is not a Saturday or Sunday and is not in the holidays table, and the second business day is the settlement date. Always read holidays from the holidays table — if you hard-code them in the code, you miss the point of this lab entirely.

Steps

Create /root/cap/settle, then run the answer key's generator as is to produce /root/cap/settle/settle.db, /root/cap/settle/auth.jsonl, and /root/cap/settle/capture.jsonl.
Read the holidays table and recompute T+2 on a business-day basis. Write trades=, holidays=, bad_settle_date=, settle_on_nonbusiness= to /root/cap/settle/calendar.txt, and trade_id,trade_date,recorded_settle_date,correct_settle_date to /root/cap/settle/baddate.csv.
Reconcile trades and settlements. Write the eight lines trades=, settlements=, amount_matched=, amount_mismatch=, amount_mismatch_total=, missing_settlement=, orphan_settlement=, orphan_amount= to /root/cap/settle/recon.txt, and trade_id,trade_amount,settled_amount,diff to /root/cap/settle/amountdiff.csv.
Find the trades whose settlement date is before 2026-09-16 but whose settlement is not finished, and write them to /root/cap/settle/fails.csv as trade_id,settle_date,amount,settlement_status, and to /root/cap/settle/fails.txt as fail_count=, fail_amount=, no_record=, pending=, failed=. If there is no settlement record at all, write the status as none.
Write eight lines to /root/cap/settle/auth.txt: auths=, captures=, approved=, voided=, declined=, full_capture=, partial_capture=, no_capture=.
Write the approved authorizations that have no capture to /root/cap/settle/orphan.csv as auth_id,merchant,amount,ts, and write no_capture_count=, no_capture_amount=, partial_count=, partial_remaining_amount= to /root/cap/settle/orphan.txt.
Group by idempotency_key and find the keys that have two or more authorizations. Write them to /root/cap/settle/dupcharge.csv as idempotency_key,auth_ids,charged_amount, and write dup_keys=, double_charged_amount= to /root/cap/settle/dupcharge.txt. Join auth_ids with a pipe (|).
Write a report in /root/cap/settle/report.md. It needs six sections: ## 무슨 일이 있었나, ## 결제일 계산, ## 대사 결과, ## 미결제, ## 카드 승인과 매입, ## 무엇을 고쳐야 하나 (the six Korean section titles mean: what happened, settlement date calculation, reconciliation result, settlement fails, card authorization and capture, and what to fix).

Notes

Read the DB with sqlite3 -readonly file. In Python it is sqlite3.connect('file:...?mode=ro', uri=True).
All amounts are integers in won. If you treat them as floating-point numbers, the totals drift slightly and fail grading.
Comparing dates directly as YYYY-MM-DD strings is accurate.
Common mistake 1: hard-coding holidays in step 2. You also have to count how many days are in the table.
Common mistake 2: leaving out the date condition in step 4. If you count even items whose settlement date has not yet arrived, the balance inflates.
Common mistake 3: adding up the amounts of both authorizations in step 7. The first is a normal charge, so only the second and later are double charges.

Generate the trade and settlement ledger and the card logs

Create /root/cap/settle, then run the answer key's generator as is to produce /root/cap/settle/settle.db, /root/cap/settle/auth.jsonl, and /root/cap/settle/capture.jsonl.

Running the generator as it is produces one sqlite DB and two JSONL files. If you change the seed, the values will not match the grading values.

Verify settlement dates with business-day T+2

Read the holidays table and recompute T+2 on a business-day basis. Write trades=, holidays=, bad_settle_date=, settle_on_nonbusiness= to /root/cap/settle/calendar.txt, and trade_id,trade_date,recorded_settle_date,correct_settle_date to /root/cap/settle/baddate.csv.

Do not hard-code holidays; read them from the holidays table. Count two business days, skipping weekends and holidays.

Reconcile the trade book and the settlement book in both directions

Reconcile trades and settlements. Write the eight lines trades=, settlements=, amount_matched=, amount_mismatch=, amount_mismatch_total=, missing_settlement=, orphan_settlement=, orphan_amount= to /root/cap/settle/recon.txt, and trade_id,trade_amount,settled_amount,diff to /root/cap/settle/amountdiff.csv.

Count the four directions separately. If you compare only count totals, the differences in the two directions cancel each other.

Split settlement fails by reason

Find the trades whose settlement date is before 2026-09-16 but whose settlement is not finished, and write them to /root/cap/settle/fails.csv as trade_id,settle_date,amount,settlement_status, and to /root/cap/settle/fails.txt as fail_count=, fail_amount=, no_record=, pending=, failed=. If there is no settlement record at all, write the status as none.

The reference date is 2026-09-16. Only trades whose settlement date is before it are targets, and ones with no settlement record at all are also fails.

Explain the difference between authorization and capture by reason

Write eight lines to /root/cap/settle/auth.txt: auths=, captures=, approved=, voided=, declined=, full_capture=, partial_capture=, no_capture=.

For each authorization, compute the sum of captured amounts and split into three — a sum of 0 is no capture, less than the authorized amount is a partial capture, and equal or greater is a full capture.

Produce the authorizations with no capture and the partial-capture remainders

Write the approved authorizations that have no capture to /root/cap/settle/orphan.csv as auth_id,merchant,amount,ts, and write no_capture_count=, no_capture_amount=, partial_count=, partial_remaining_amount= to /root/cap/settle/orphan.txt.

Voided or declined authorizations do not go in here. Only those whose status is approved but that have no capture.

Find double charges by idempotency key

Group by idempotency_key and find the keys that have two or more authorizations. Write them to /root/cap/settle/dupcharge.csv as idempotency_key,auth_ids,charged_amount, and write dup_keys=, double_charged_amount= to /root/cap/settle/dupcharge.txt. Join auth_ids with a pipe (|).

If you group by auth_id, nothing comes up. Group by idempotency_key and look at the ones with two or more. The double-charged amount is the amount captured by the second and later authorizations.

Write the settlement reconciliation report

Write a report in /root/cap/settle/report.md. It needs six sections: ## 무슨 일이 있었나, ## 결제일 계산, ## 대사 결과, ## 미결제, ## 카드 승인과 매입, ## 무엇을 고쳐야 하나 (the six Korean section titles mean: what happened, settlement date calculation, reconciliation result, settlement fails, card authorization and capture, and what to fix).

It needs six sections. The reconciliation section must include the numbers for both directions, and the remediation section must cover business-day calculation and idempotency keys.