Chốt và đối soát: không giao dịch nào mập mờ quá 24 giờ

TL;DR: Ba nguồn báo kết quả thanh toán bù chỗ yếu cho nhau, nên thiết kế ghép cả ba. IPN (lời nhà cung cấp gọi về server của bạn để báo kết quả) là đường chính nhưng mất hẳn khi endpoint của bạn chết quá 50 phút. API tra cứu chắc chắn nhưng chỉ 10 lượt mỗi giây, nên chỉ hỏi theo lịch hai mốc: phút 1 và phút 16, ngay sau khi trang cổng hết hạn. File đối soát (bản kê giao dịch của nhà cung cấp, để so từng dòng với sổ) tới 6 giờ sáng hôm sau, kịp hạn 24 giờ cho mọi giao dịch tạo sau 6 giờ sáng của ngày chốt. So sổ với file bằng FULL JOIN theo mã giao dịch, lọc ngày trong subquery chứ không ở WHERE.

Đợt sale đóng lúc 21 giờ 10. Sổ còn 3.600 giao dịch chưa rõ, khoảng 2.160 trong số đó khách đã bị trừ thật. Treo quá 30 phút thì 30% khách gọi tổng đài, quá 24 giờ thì họ khiếu nại qua ngân hàng. Ba nguồn để hỏi, nguồn nào cũng hở một chỗ. Bạn chọn cách chốt trước, rồi tự tính và tự chạy câu so sổ trên Postgres.

1. Ba nguồn kết quả, bạn chốt giao dịch mập mờ bằng nguồn nào?

Số trích từ bảng chuẩn của bài 01. IPN (Instant Payment Notification) là lời gọi HTTP nhà cung cấp gửi vào URL của bạn để báo kết quả, gửi lại tối đa 10 lần cách 5 phút. File đối soát liệt kê giao dịch thành công và hoàn tiền của ngày chốt 0:00–23:59 giờ Việt Nam.

Chọn một phương án trước khi đọc tiếp. Hình dung bạn đặt hàng trả tiền khi nhận và cần biết đơn giao chưa:

2. Vì sao chỉ chờ IPN là không đủ?

Bình thường IPN tới trong vài giây. Chỗ hở nằm ở endpoint của bạn: quá 50 phút gửi lại, kết quả ấy không bao giờ tới nữa.

Giả sử endpoint IPN chết một giờ, đúng từ lúc mở sale. Mọi IPN của 10 phút sale hết lượt gửi lại trong lúc endpoint còn chết, nên cả kênh trang cổng mất kết quả: 1.800.000 × 70% = 1.260.000 lượt. Tra cứu bù ở 10 lượt mỗi giây mất 126.000 giây, khoảng 35 giờ. API tra cứu là cái nhíp, không phải cái xẻng.

Vì vậy endpoint IPN phải mỏng tới mức khó chết: kiểm chữ ký, ghi nguyên văn vào một bảng, trả lời đã nhận. Ghi sổ, đổi trạng thái đơn chạy sau, đọc từ bảng đó.

3. Ngân sách 10 lượt mỗi giây: hỏi lúc nào?

3.1 Lịch hai mốc

Hỏi ngay một lần đã ăn 6 trong 10 lượt mỗi giây, nên câu hỏi thật là lượt thứ hai đặt ở đâu. Mốc có sẵn trong ràng buộc: trang cổng hết hạn sau 15 phút. Trước mốc đó, "chưa trả" còn có thể đổi vì khách đang quét QR. Sau mốc đó chỉ còn "đã trả" hoặc "hết hiệu lực". Lịch hai mốc vì thế là: hỏi ở phút 1, hỏi lại ở phút 16 tính từ lúc tạo, rồi mỗi giờ một lần với số ít còn "đang xử lý".

Lời giải:

Phương án B ở trường hợp xấu nhất: 3.600 giao dịch cùng chờ đủ 15 phút, hỏi mỗi phút là 3.600 ÷ 60 = 60 lượt mỗi giây.

3.2 Hàng đợi tra cứu

Lịch hỏi sống trong một bảng query_schedule (txn_ref, created_at, attempt, next_check_at). Mỗi giây worker lấy tối đa 10 dòng đến hạn; SKIP LOCKED bỏ qua dòng worker khác đang giữ nên không hỏi trùng:

SELECT txn_ref FROM query_schedule
WHERE next_check_at <= now()
ORDER BY next_check_at
LIMIT 10
FOR UPDATE SKIP LOCKED;

Kết quả rõ thì xoá dòng. Còn "đang xử lý" thì dời next_check_at về created_at + interval '16 min' sau lần đầu, now() + interval '1 hour' sau các lần sau.

3.3 Ghi trạng thái một chiều

Ba nguồn có thể báo cùng một giao dịch theo thứ tự bất kỳ, nên câu ghi trạng thái chỉ đi một chiều:

UPDATE payments SET status = 'paid'
WHERE txn_ref = 'GD1000' AND status IN ('pending', 'unknown');

Kênh trang cổng chiếm 70% nên lúc IPN tới thanh toán thường còn pending, vì chỉ timeout hoặc phút 1 mới đẩy nó sang unknown; vế IN nhận cả hai. Lần đầu trả UPDATE 1; IPN trùng tới sau trả UPDATE 0. Nguồn báo failed sau khi đã paid cũng ra UPDATE 0: không ghi đè, nó thành dòng lệch để mục 5 bắt.

4. Giao dịch tạo lúc mấy giờ thì file không cứu kịp?

Lời giải: giao dịch tạo lúc h giờ chờ file 30 − h giờ, vượt 24 giờ khi h nhỏ hơn 6. Đó là mọi giao dịch tạo trước 6:00: rải đều thì khoảng 100.000 lượt, cỡ 200 giao dịch mập mờ. Đợt sale lúc 21 giờ thì file tới sau 9 giờ.

Với vùng này, file chỉ là lưới an toàn đến muộn. Giao dịch nào tới giờ thứ 20 vẫn "đang xử lý" thì chuyển người mở yêu cầu tra soát với nhà cung cấp, còn 4 giờ trước hạn. Mốc 20 giờ là quyết định của bạn: kéo sớm thì tốn công người, đẩy muộn thì hết đường lùi.

Thử ngẫmnhà cung cấp đổi giờ gửi file từ 6 giờ sáng sang 2 giờ sáng. Vùng giờ cần tra cứu thay file co lại còn bao nhiêu?

5. So sổ với file trước 12 giờ trưa

File tới lúc 6 giờ, hạn là 12 giờ trưa: 6 tiếng. Máy so 2,2 triệu dòng của ngày có sale trong vài giây; thứ hết giờ là người. Mỗi khoản lệch máy không tự giải thích được tốn kế toán khoảng 30 phút, nên một kế toán xử lý tối đa 12 khoản trước hạn.

Đối soát (reconciliation) là so từng dòng của hai nguồn độc lập cùng ghi một sự việc để tìm chỗ không khớp. Hai nguồn ở đây là bảng payments của bạn và bảng settlement_file nạp từ file.

Tác giả nạp 400.000 giao dịch của ngày 18/10, cài sẵn năm dòng lệch, rồi chạy:

SELECT coalesce(g.txn_ref, f.txn_ref) AS txn_ref,
       g.status, g.amount, f.gross_amount, f.fee
FROM (SELECT * FROM payments
      WHERE created_at >= '2026-10-18 00:00+07'
        AND created_at <  '2026-10-19 00:00+07') g
FULL JOIN (SELECT * FROM settlement_file
           WHERE settle_date = '2026-10-18'
             AND kind = 'payment') f
  ON f.txn_ref = g.txn_ref
WHERE g.txn_ref IS NULL OR f.txn_ref IS NULL
   OR g.status <> 'paid'
   OR g.amount <> f.gross_amount
   OR f.fee <> round(f.gross_amount * 0.011);

Điều kiện ngày nằm trong hai subquery, nên mỗi phía lọc trước rồi mới join. Hoàn tiền so riêng cùng khuôn. Postgres 16.15 trong Docker, khoảng 110 ms:

 txn_ref | status  | amount | gross_amount | fee
---------+---------+--------+--------------+------
 GD1000  | unknown | 250000 |       250000 | 2750
 GD2000  | failed  | 250000 |       250000 | 2750
 GD3000  |         |        |       250000 | 2750
 GD4000  | paid    | 250000 |              |
 GD5000  | paid    | 250000 |       250000 | 2751

GD1000 sổ chưa rõ mà file có: IPN mất, giờ file chốt nó. GD2000 sổ ghi thất bại mà file nói thành công: khách bị trừ, đơn đã huỷ. GD3000 file có mà sổ không có dòng nào, tệ nhất. GD4000 chỉ có trong sổ. GD5000 phí lệch một đồng.

Máy chỉ tự giải thích được hai kiểu: dòng như GD1000 thì chốt bằng câu UPDATE một chiều ở mục 3; dòng chỉ có trong sổ mà tạo sát nửa đêm thì có thể bị xếp sang ngày sau, chờ file hôm sau. Bảng này chỉ có kiểu đầu: GD4000 tạo lúc 15:00 nên không phải ca nửa đêm, vẫn là lệch thật. Bốn dòng còn lại là 2 giờ công kế toán, 400.000 đ theo giá một khoản lệch sổ.

Thử ngẫmmột sáng có 40 dòng lệch thật mà chỉ một kế toán trực. Bạn xếp thứ tự xử lý theo tiêu chí nào để khoản đau nhất không trễ hạn?

6. Hai cái bẫy đã đo được

❌ Lọc ngày ở WHERE sau FULL JOIN.

SELECT coalesce(g.txn_ref, f.txn_ref) AS txn_ref, g.status
FROM payments g
FULL JOIN settlement_file f ON f.txn_ref = g.txn_ref
WHERE g.created_at >= '2026-10-18 00:00+07'
  AND g.created_at <  '2026-10-19 00:00+07'
  AND f.settle_date = '2026-10-18'
  AND (g.txn_ref IS NULL OR f.txn_ref IS NULL
       OR g.status <> 'paid'
       OR f.fee <> round(f.gross_amount * 0.011));

✅ Câu này trả 3 dòng thay vì 5, mất GD3000 và GD4000: dòng chỉ có một phía mang NULL ở cột ngày của phía kia, và điều kiện ngày loại nó trước khi IS NULL kịp có tác dụng. Câu so sổ báo "sạch" đúng ở hai kiểu lệch nó sinh ra để bắt. Lọc ngày trong subquery của từng phía:

FROM (SELECT * FROM payments
      WHERE created_at >= '2026-10-18 00:00+07'
        AND created_at <  '2026-10-19 00:00+07') g
FULL JOIN (SELECT * FROM settlement_file
           WHERE settle_date = '2026-10-18') f ON f.txn_ref = g.txn_ref

❌ Cắt ngày theo múi giờ của server.

SET timezone = 'UTC';
SELECT count(*) FROM (SELECT * FROM payments
  WHERE created_at::date = '2026-10-18') g
FULL JOIN (SELECT * FROM settlement_file
  WHERE settle_date = '2026-10-18') f ON f.txn_ref = g.txn_ref
WHERE g.txn_ref IS NULL OR f.txn_ref IS NULL;

✅ Container postgres:16 của tác giả có TimeZone là Etc/UTC. timestamptz ép sang ngày theo múi giờ của phiên, nên ngày UTC lệch 7 giờ so với ngày chốt của file. Trên dữ liệu của tác giả, câu này ra 116.666 dòng "chỉ có trong file", đúng 7/24 của 400.000. Viết mốc kèm +07 hoặc dùng AT TIME ZONE 'Asia/Ho_Chi_Minh', đừng trông vào cấu hình server:

SELECT count(*) FROM (SELECT * FROM payments
  WHERE created_at >= '2026-10-18 00:00+07'
    AND created_at <  '2026-10-19 00:00+07') g
FULL JOIN (SELECT * FROM settlement_file
  WHERE settle_date = '2026-10-18') f ON f.txn_ref = g.txn_ref
WHERE g.txn_ref IS NULL OR f.txn_ref IS NULL;

7. Liên hệ các bài khác

• Bài 01: Ràng buộc và ước lượng: nguồn của 3.600 giao dịch mập mờ và con số 30 giờ chờ file.
• Bài 03: Thứ tự bước quanh lệnh trừ tiền: máy trạng thái mà ba nguồn cùng ghi vào.
• Bài 04: Sổ cái chỉ ghi thêm: nơi khoản lệch thành bút toán điều chỉnh.
• Bài 06: Đối chiếu thực tế: hệ thanh toán thật chốt và đối soát thế nào.

8. Tóm tắt

• Khi có cả IPN, tra cứu và file: IPN làm đường chính, endpoint mỏng để không chết quá 50 phút.
• Khi API tra cứu có giới hạn lượt: hỏi ở mốc mà câu trả lời thành chốt, rồi tính tải lúc đỉnh so với giới hạn.
• Khi file tới sáng hôm sau mà hạn là 24 giờ: tìm vùng giờ file không cứu kịp, giao vùng đó cho tra cứu và người xử lý.
• Khi so sổ: FULL JOIN theo mã, lọc ngày trong subquery với múi giờ tường minh.

9. Tự kiểm tra

Bài tiếp theo: Đối chiếu thực tế: các hệ thanh toán thật giữ tiền khớp thế nào?