SQL and events · Transaction integrity and reconciliation

-- Teaching schema only; do not run schema changes against an employer database.
CREATE TABLE payments (
  id text PRIMARY KEY, merchant_id text NOT NULL, amount_minor bigint NOT NULL,
  currency text NOT NULL, status text NOT NULL, created_at timestamptz NOT NULL
);
CREATE TABLE attempts (
  id text PRIMARY KEY, payment_id text NOT NULL, provider_reference text,
  status text NOT NULL, created_at timestamptz NOT NULL
);
CREATE TABLE ledger (
  id text PRIMARY KEY, payment_id text NOT NULL, kind text NOT NULL,
  amount_minor bigint NOT NULL, currency text NOT NULL
);
CREATE TABLE events (
  id text PRIMARY KEY, payment_id text NOT NULL, payload jsonb NOT NULL,
  created_at timestamptz NOT NULL
);

-- Failed payments for one merchant in a half-open UTC day.
SELECT id, amount_minor, currency FROM payments
WHERE merchant_id = 'merchant-sandbox' AND status IN ('DECLINED', 'FAILED')
  AND created_at >= TIMESTAMPTZ '2026-10-01 00:00:00+00'
  AND created_at < TIMESTAMPTZ '2026-10-02 00:00:00+00'
ORDER BY created_at, id LIMIT 50;

-- NULL is unknown: `= NULL` does not select missing references.
SELECT id, COALESCE(provider_reference, '(missing)') AS reference
FROM attempts WHERE provider_reference IS NULL;

-- Keyset continuation; next cursor is the prior page's last timestamp and id.
SELECT id, created_at FROM payments
WHERE (created_at, id) > (TIMESTAMPTZ '2026-10-01 12:00:00+00', 'pay-100')
ORDER BY created_at, id LIMIT 100;

-- Candidate duplicate capture effect. Actual ledger grain must be agreed first.
SELECT payment_id, COUNT(*) AS capture_rows
FROM ledger WHERE kind = 'CAPTURE'
GROUP BY payment_id HAVING COUNT(*) > 1;

-- Orphan attempts in the declared schema (which intentionally has no FK here).
SELECT a.id, a.payment_id FROM attempts a
LEFT JOIN payments p ON p.id = a.payment_id WHERE p.id IS NULL;

-- Captured payment missing any capture projection.
SELECT p.id FROM payments p WHERE p.status = 'CAPTURED'
AND NOT EXISTS (
  SELECT 1 FROM ledger l WHERE l.payment_id = p.id AND l.kind = 'CAPTURE'
);

-- Aggregate BEFORE joining so multiple attempts cannot multiply money totals.
WITH captured AS (
  SELECT payment_id, currency, SUM(amount_minor) AS captured_minor
  FROM ledger WHERE kind = 'CAPTURE' GROUP BY payment_id, currency
)
SELECT p.id, p.amount_minor, c.captured_minor
FROM payments p LEFT JOIN captured c ON c.payment_id = p.id AND c.currency = p.currency
WHERE p.status = 'CAPTURED' AND p.amount_minor IS DISTINCT FROM c.captured_minor;

-- Currency-separated totals and basic statistics.
SELECT currency, COUNT(*) AS payments, SUM(amount_minor) AS total_minor,
       MIN(amount_minor), MAX(amount_minor), AVG(amount_minor)
FROM payments WHERE status = 'CAPTURED'
GROUP BY currency HAVING COUNT(*) >= 1;

WITH ranked AS (
  SELECT a.*, ROW_NUMBER() OVER (
    PARTITION BY payment_id ORDER BY created_at DESC, id DESC
  ) AS rn FROM attempts a
)
SELECT payment_id, id, status FROM ranked WHERE rn = 1;

SELECT payment_id, id,
       payload->>'status' AS business_status,
       payload->'provider' AS provider_json
FROM events WHERE payload @> '{"status":"CAPTURED"}'::jsonb
ORDER BY created_at, id;

SELECT id, payment_id, status,
  LAG(status) OVER (PARTITION BY payment_id ORDER BY created_at, id) AS previous_status
FROM attempts;

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT COUNT(*) FROM payments WHERE status = 'CAPTURED';
SELECT COUNT(*) FROM ledger WHERE kind = 'CAPTURE';
ROLLBACK;

// Scoped test body. Only read completion; do not repeat payment creation in poll.
let observed = 'PENDING';
await expect.poll(async () => {
  const response = await request.get(`/sandbox/payments/${payment.id}`);
  expect(response.status()).toBe(200);
  const body: { status: string } = await response.json();
  observed = body.status;
  return ['CAPTURED', 'DECLINED', 'FAILED'].includes(observed);
}, { timeout: 15_000, intervals: [200, 500, 1000] }).toBe(true);
expect(observed).toBe('CAPTURED');