No double refunds

Shell (bash)

%%bash
base=https://academy.cloudtechanalytics.com/datasets/refunds
for f in refunds.py db.py app.py schema.sql customers.csv orders.csv order_items.csv test_refunds.py pytest.ini; do
  curl -sO "$base/$f"
done
cat > service.py <<'EOF'
"""Refund requests: the refund rules, applied safely to the database."""
import sqlite3

import refunds

FEE_REASONS = {"failed_delivery", "damaged", "wrong_item"}


class RefundError(Exception):
    """A refund that the rules don't allow."""


def migrate(conn):
    """Add idempotency keys and fee tracking to an existing refunds table."""
    conn.executescript("""
        ALTER TABLE refunds ADD COLUMN request_id TEXT;
        ALTER TABLE refunds ADD COLUMN delivery_fee_kobo INTEGER NOT NULL DEFAULT 0;
        CREATE UNIQUE INDEX refunds_request_id ON refunds (request_id);
    """)


def _existing(conn, request_id):
    row = conn.execute("SELECT * FROM refunds WHERE request_id = ?", (request_id,)).fetchone()
    return dict(row) if row else None


def request_refund(conn, order_id, request_id, reason, lines):
    """Record a refund once. Returns (refund, created). lines: list of (sku, quantity)."""
    earlier = _existing(conn, request_id)
    if earlier:
        return earlier, False
    conn.execute("BEGIN IMMEDIATE")
    try:
        order = conn.execute("SELECT * FROM orders WHERE order_id = ?", (order_id,)).fetchone()
        prices = {r["sku"]: r["unit_price_kobo"] for r in conn.execute("SELECT * FROM order_items WHERE order_id = ?", (order_id,))}
        paid = sum(r["quantity"] * r["unit_price_kobo"] for r in conn.execute("SELECT * FROM order_items WHERE order_id = ?", (order_id,)))
        paid += order["delivery_fee_kobo"]
        so_far = conn.execute(
            "SELECT COALESCE(SUM(amount_kobo), 0) AS amount, COALESCE(SUM(delivery_fee_kobo), 0) AS fee FROM refunds WHERE order_id = ?",
            (order_id,),
        ).fetchone()
        goods = sum(refunds.item_refund(quantity, prices[sku], reason) for sku, quantity in lines)
        fee = order["delivery_fee_kobo"] if reason in FEE_REASONS and so_far["fee"] == 0 else 0
        if so_far["amount"] + goods + fee > paid:
            raise RefundError(f"refunds would total {so_far['amount'] + goods + fee} kobo, more than the {paid} paid")
        conn.execute(
            "INSERT INTO refunds (order_id, request_id, reason, amount_kobo, delivery_fee_kobo, created_at) "
            "VALUES (?, ?, ?, ?, ?, datetime('now'))",
            (order_id, request_id, reason, goods + fee, fee),
        )
        conn.commit()
    except sqlite3.IntegrityError:
        conn.rollback()  # another request with the same key got there first
        return _existing(conn, request_id), False
    except Exception:
        conn.rollback()
        raise
    return _existing(conn, request_id), True
EOF
echo "service.py written"

service.py written

Shell (bash)

%%bash
cat > test_service.py <<'EOF'
import pytest

import db
import service


@pytest.fixture
def conn():
    conn = db.connect()
    db.init(conn)
    service.migrate(conn)
    return conn


def test_retried_request_creates_one_refund(conn):
    # ISSUE-102: the same request twice
    first, created = service.request_refund(conn, "K-1002", "req-abc", "damaged", [("SHO-RN", 1)])
    again, created_again = service.request_refund(conn, "K-1002", "req-abc", "damaged", [("SHO-RN", 1)])
    assert created and not created_again
    assert again["refund_id"] == first["refund_id"]
    assert len(db.refunds_for(conn, "K-1002")) == 1


def test_partial_returns_refund_the_delivery_fee_once(conn):
    # ISSUE-105: K-1005 paid 2 x 2,750,000 + 4,500,000 + 300,000 fee = 10,300,000 kobo
    first, _ = service.request_refund(conn, "K-1005", "req-1", "damaged", [("POT-SET", 2)])
    second, _ = service.request_refund(conn, "K-1005", "req-2", "damaged", [("BLN-X1", 1)])
    assert first["amount_kobo"] == 5_800_000   # 5,500,000 + the fee
    assert second["amount_kobo"] == 4_500_000  # no fee the second time
    assert first["amount_kobo"] + second["amount_kobo"] == 10_300_000


def test_refunds_can_never_exceed_what_was_paid(conn):
    service.request_refund(conn, "K-1005", "req-1", "damaged", [("POT-SET", 2), ("BLN-X1", 1)])
    with pytest.raises(service.RefundError):
        service.request_refund(conn, "K-1005", "req-2", "damaged", [("BLN-X1", 1)])
    assert len(db.refunds_for(conn, "K-1005")) == 1


def test_database_refuses_a_duplicate_key_even_without_the_check(conn):
    conn.execute("INSERT INTO refunds (order_id, request_id, reason, amount_kobo, created_at) VALUES ('K-1002', 'req-x', 'damaged', 1, 'now')")
    with pytest.raises(Exception):
        conn.execute("INSERT INTO refunds (order_id, request_id, reason, amount_kobo, created_at) VALUES ('K-1002', 'req-x', 'damaged', 1, 'now')")
EOF
python -m pytest

.......
7 passed in 0.01s