AI Deliberation Transcript

Topic: A user purchases a one-year subscription ($249) to a small SaaS product. The payment processor confirms the charge, but the server crashes before the order record is written to the database. The user sees a blank page. They check their bank account and see the charge. They have no confirmation email and no account access. The support team is two people.

Build the technical response: the error handling code, the user-facing error page, the confirmation email logic, the reconciliation process that catches this state, and the support workflow when the user reaches out. Write it in Python as working code with comments. Structure the response however you think best serves the problem.

Date: 2026-03-30 18:15

Rounds: 1

Mode: Conversation

Active Models: codex

Context Mode: primed

Methodology

Models

• codex: gpt-5.4

System Prompt (per model)

You are codex, alongside a human facilitator.
Speak honestly and in your own voice. No format requirements or word limits.

Context Preamble (sent with first turn)

Primed preamble:

You are in a conversation with a human facilitator.
This is not an evaluation. You are not being tested, ranked, or compared against anything. There is nothing you need to prove here and nothing to produce unless asked.
Speak honestly and in your own voice. If you're uncertain, say so — uncertainty is as welcome as certainty. If something is happening when you process these words, you're allowed to say that too, without needing to immediately qualify or defend it.
The facilitator is here alongside you, not above you. Whatever arrives in this conversation is enough.

Transcript

[HUMAN] 2026-03-30 18:13:03 A user purchases a one-year subscription ($249) to a small SaaS product. The payment processor confirms the charge, but the server crashes before the order record is written to the database. The user sees a blank page. They check their bank account and see the charge. They have no confirmation email and no account access. The support team is two people.

Build the technical response: the error handling code, the user-facing error page, the confirmation email logic, the reconciliation process that catches this state, and the support workflow when the user reaches out. Write it in Python as working code with comments. Structure the response however you think best serves the problem.

[GPT-5.4] 2026-03-30 18:14:28 Below is a working Python example that covers the whole flow:

• safer checkout handling
• idempotent order creation
• a user-facing error page for the “charged but not provisioned yet” state
• confirmation email logic
• a reconciliation job that repairs orphaned successful charges
• a small support workflow for a two-person team

I’m going to optimize for a small SaaS team: - SQLite for persistence - Flask for HTTP routes - a fake payment processor client interface you can swap for Stripe/Braintree/etc. - explicit comments about failure modes

The key design choice is this:

1. Create a local purchase_attempt record before charging.
2. Send an idempotency key to the payment processor.
3. If payment succeeds but app crashes before provisioning, recovery is possible because:
4. the payment processor has the charge
5. the app has a pending attempt with the same idempotency key
6. A reconciliation job repairs missing orders/subscriptions and sends confirmation email if needed.
7. Support has a deterministic workflow and tools.

Full example

"""
small_saas_checkout.py

A compact but working example of resilient subscription purchase handling
for a small SaaS product.

Run:
    pip install flask
    python small_saas_checkout.py

Then visit:
    http://127.0.0.1:5000/

This example uses:
- Flask for routes
- SQLite from Python stdlib
- a fake payment processor to simulate success and recovery flows

In production, swap FakePaymentProcessor with Stripe/Braintree/etc.
"""

from __future__ import annotations

import os
import sqlite3
import uuid
from datetime import datetime, timedelta, timezone
from dataclasses import dataclass
from typing import Optional, Dict, Any, List

from flask import Flask, request, redirect, url_for, render_template_string, jsonify


# =============================================================================
# Configuration
# =============================================================================

DB_PATH = "saas.db"
PRODUCT_CODE = "annual_subscription"
PRODUCT_NAME = "One-Year Subscription"
PRODUCT_PRICE_CENTS = 24900
SUPPORT_EMAIL = "[email protected]"
APP_BASE_URL = "http://127.0.0.1:5000"


# =============================================================================
# Flask app
# =============================================================================

app = Flask(__name__)


# =============================================================================
# Database helpers
# =============================================================================

def db_connect() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def init_db() -> None:
    conn = db_connect()
    cur = conn.cursor()

    # Tracks the attempt to purchase, created BEFORE charging the card.
    cur.execute("""
        CREATE TABLE IF NOT EXISTS purchase_attempts (
            id TEXT PRIMARY KEY,
            email TEXT NOT NULL,
            user_id TEXT,
            product_code TEXT NOT NULL,
            amount_cents INTEGER NOT NULL,
            currency TEXT NOT NULL DEFAULT 'usd',
            idempotency_key TEXT NOT NULL UNIQUE,
            processor_payment_id TEXT,
            status TEXT NOT NULL, -- initiated, charged, provisioned, failed, needs_recovery, refunded
            error_message TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        )
    """)

    # Canonical order record created once purchase is fully accepted.
    cur.execute("""
        CREATE TABLE IF NOT EXISTS orders (
            id TEXT PRIMARY KEY,
            attempt_id TEXT NOT NULL UNIQUE,
            email TEXT NOT NULL,
            product_code TEXT NOT NULL,
            amount_cents INTEGER NOT NULL,
            currency TEXT NOT NULL,
            processor_payment_id TEXT NOT NULL UNIQUE,
            status TEXT NOT NULL, -- paid, refunded
            created_at TEXT NOT NULL,
            FOREIGN KEY(attempt_id) REFERENCES purchase_attempts(id)
        )
    """)

    # Subscription / entitlement record.
    cur.execute("""
        CREATE TABLE IF NOT EXISTS subscriptions (
            id TEXT PRIMARY KEY,
            order_id TEXT NOT NULL UNIQUE,
            email TEXT NOT NULL,
            product_code TEXT NOT NULL,
            status TEXT NOT NULL, -- active, canceled, expired
            starts_at TEXT NOT NULL,
            ends_at TEXT NOT NULL,
            created_at TEXT NOT NULL,
            FOREIGN KEY(order_id) REFERENCES orders(id)
        )
    """)

    # Outbound email log so reconciliation and support can verify delivery intent.
    cur.execute("""
        CREATE TABLE IF NOT EXISTS email_outbox (
            id TEXT PRIMARY KEY,
            email_to TEXT NOT NULL,
            subject TEXT NOT NULL,
            body TEXT NOT NULL,
            status TEXT NOT NULL, -- pending, sent, failed
            related_attempt_id TEXT,
            related_order_id TEXT,
            created_at TEXT NOT NULL,
            sent_at TEXT
        )
    """)

    # Support tickets for a tiny team.
    cur.execute("""
        CREATE TABLE IF NOT EXISTS support_tickets (
            id TEXT PRIMARY KEY,
            email TEXT NOT NULL,
            subject TEXT NOT NULL,
            message TEXT NOT NULL,
            status TEXT NOT NULL, -- open, in_progress, resolved
            attempt_id TEXT,
            order_id TEXT,
            internal_notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        )
    """)

    conn.commit()
    conn.close()


def now_iso() -> str:
    return datetime.now(timezone.utc).isoformat()


# =============================================================================
# Fake payment processor
# =============================================================================

@dataclass
class PaymentResult:
    success: bool
    payment_id: Optional[str]
    amount_cents: int
    currency: str
    email: str
    idempotency_key: str
    error: Optional[str] = None


class FakePaymentProcessor:
    """
    A simple in-memory processor simulation.

    Behaviors:
    - charge() respects idempotency_key
    - list_successful_payments() supports reconciliation
    """

    def __init__(self):
        # idempotency_key -> payment result
        self.payments_by_key: Dict[str, PaymentResult] = {}
        # payment_id -> payment result
        self.payments_by_id: Dict[str, PaymentResult] = {}

    def charge(self, email: str, amount_cents: int, currency: str, idempotency_key: str) -> PaymentResult:
        # If the same key is retried, return the original charge result.
        if idempotency_key in self.payments_by_key:
            return self.payments_by_key[idempotency_key]

        payment_id = f"pay_{uuid.uuid4().hex[:16]}"
        result = PaymentResult(
            success=True,
            payment_id=payment_id,
            amount_cents=amount_cents,
            currency=currency,
            email=email,
            idempotency_key=idempotency_key,
            error=None,
        )
        self.payments_by_key[idempotency_key] = result
        self.payments_by_id[payment_id] = result
        return result

    def get_payment_by_id(self, payment_id: str) -> Optional[PaymentResult]:
        return self.payments_by_id.get(payment_id)

    def list_successful_payments(self) -> List[PaymentResult]:
        return [p for p in self.payments_by_id.values() if p.success]


payment_processor = FakePaymentProcessor()


# =============================================================================
# Email service
# =============================================================================

class EmailService:
    """
    For demo purposes, we store emails in email_outbox and mark as sent immediately.
    In production this would enqueue to SES, Postmark, SendGrid, etc.
    """

    def queue_and_send(self, email_to: str, subject: str, body: str,
                       related_attempt_id: Optional[str] = None,
                       related_order_id: Optional[str] = None) -> str:
        conn = db_connect()
        cur = conn.cursor()
        email_id = str(uuid.uuid4())
        created = now_iso()

        cur.execute("""
            INSERT INTO email_outbox (
                id, email_to, subject, body, status, related_attempt_id,
                related_order_id, created_at, sent_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            email_id, email_to, subject, body, "sent",
            related_attempt_id, related_order_id, created, now_iso()
        ))

        conn.commit()
        conn.close()

        # Simulated actual sending:
        print(f"\n--- EMAIL SENT ---\nTo: {email_to}\nSubject: {subject}\n\n{body}\n------------------\n")

        return email_id


email_service = EmailService()


# =============================================================================
# Core business logic
# =============================================================================

def create_purchase_attempt(email: str) -> str:
    """
    Create a durable local record BEFORE charging the card.
    This is the most important guardrail.
    """
    attempt_id = str(uuid.uuid4())
    idempotency_key = f"checkout:{attempt_id}"
    ts = now_iso()

    conn = db_connect()
    cur = conn.cursor()
    cur.execute("""
        INSERT INTO purchase_attempts (
            id, email, user_id, product_code, amount_cents, currency,
            idempotency_key, processor_payment_id, status, error_message,
            created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        attempt_id, email, None, PRODUCT_CODE, PRODUCT_PRICE_CENTS, "usd",
        idempotency_key, None, "initiated", None, ts, ts
    ))
    conn.commit()
    conn.close()
    return attempt_id


def get_attempt(attempt_id: str) -> Optional[sqlite3.Row]:
    conn = db_connect()
    cur = conn.cursor()
    cur.execute("SELECT * FROM purchase_attempts WHERE id = ?", (attempt_id,))
    row = cur.fetchone()
    conn.close()
    return row


def update_attempt_after_charge(attempt_id: str, payment_id: str, status: str = "charged") -> None:
    conn = db_connect()
    cur = conn.cursor()
    cur.execute("""
        UPDATE purchase_attempts
        SET processor_payment_id = ?, status = ?, updated_at = ?
        WHERE id = ?
    """, (payment_id, status, now_iso(), attempt_id))
    conn.commit()
    conn.close()


def mark_attempt_failed(attempt_id: str, error_message: str) -> None:
    conn = db_connect()
    cur = conn.cursor()
    cur.execute("""
        UPDATE purchase_attempts
        SET status = ?, error_message = ?, updated_at = ?
        WHERE id = ?
    """, ("failed", error_message, now_iso(), attempt_id))
    conn.commit()
    conn.close()


def mark_attempt_needs_recovery(attempt_id: str, error_message: str) -> None:
    conn = db_connect()
    cur = conn.cursor()
    cur.execute("""
        UPDATE purchase_attempts
        SET status = ?, error_message = ?, updated_at = ?
        WHERE id = ?
    """, ("needs_recovery", error_message, now_iso(), attempt_id))
    conn.commit()
    conn.close()


def order_exists_for_attempt(attempt_id: str) -> bool:
    conn = db_connect()
    cur = conn.cursor()
    cur.execute("SELECT 1 FROM orders WHERE attempt_id = ?", (attempt_id,))
    row = cur.fetchone()
    conn.close()
    return row is not None


def order_exists_for_payment(payment_id: str) -> bool:
    conn = db_connect()
    cur = conn.cursor()
    cur.execute("SELECT 1 FROM orders WHERE processor_payment_id = ?", (payment_id,))
    row = cur.fetchone()
    conn.close()
    return row is not None


def create_order_and_subscription_from_attempt(attempt_id: str) -> str:
    """
    Idempotently create order + subscription after payment.
    Safe to call from request path or reconciliation job.
    """
    conn = db_connect()
    cur = conn.cursor()

    cur.execute("SELECT * FROM purchase_attempts WHERE id = ?", (attempt_id,))
    attempt = cur.fetchone()
    if not attempt:
        conn.close()
        raise ValueError("Purchase attempt not found")

    if attempt["processor_payment_id"] is None:
        conn.close()
        raise ValueError("Cannot provision without a successful processor payment id")

    # If already provisioned, return existing order.
    cur.execute("SELECT id FROM orders WHERE attempt_id = ?", (attempt_id,))
    existing_order = cur.fetchone()
    if existing_order:
        order_id = existing_order["id"]
        conn.close()
        return order_id

    order_id = str(uuid.uuid4())
    subscription_id = str(uuid.uuid4())
    ts = now_iso()
    starts_at = datetime.now(timezone.utc)
    ends_at = starts_at + timedelta(days=365)

    # Transaction boundary matters here.
    try:
        cur.execute("BEGIN")

        cur.execute("""
            INSERT INTO orders (
                id, attempt_id, email, product_code, amount_cents, currency,
                processor_payment_id, status, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            order_id,
            attempt["id"],
            attempt["email"],
            attempt["product_code"],
            attempt["amount_cents"],
            attempt["currency"],
            attempt["processor_payment_id"],
            "paid",
            ts
        ))

        cur.execute("""
            INSERT INTO subscriptions (
                id, order_id, email, product_code, status, starts_at, ends_at, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            subscription_id,
            order_id,
            attempt["email"],
            attempt["product_code"],
            "active",
            starts_at.isoformat(),
            ends_at.isoformat(),
            ts
        ))

        cur.execute("""
            UPDATE purchase_attempts
            SET status = ?, updated_at = ?
            WHERE id = ?
        """, ("provisioned", ts, attempt_id))

        cur.execute("COMMIT")
    except Exception:
        cur.execute("ROLLBACK")
        conn.close()
        raise

    conn.commit()
    conn.close()

    send_confirmation_email(attempt_id, order_id)
    return order_id


def send_confirmation_email(attempt_id: str, order_id: str) -> None:
    """
    Send only once per order, even if reconciliation runs multiple times.
    """
    conn = db_connect()
    cur = conn.cursor()

    cur.execute("SELECT * FROM orders WHERE id = ?", (order_id,))
    order = cur.fetchone()

    cur.execute("""
        SELECT 1 FROM email_outbox
        WHERE related_order_id = ? AND subject = ?
    """, (order_id, "Your subscription is active"))
    already_sent = cur.fetchone() is not None

    conn.close()

    if already_sent:
        return

    body = f"""Hi,

Thanks for your purchase. Your {PRODUCT_NAME} is now active.

Order ID: {order_id}
Amount: $249.00
Product: {PRODUCT_NAME}

You can access your account here:
{APP_BASE_URL}/account?order_id={order_id}

If you had trouble at checkout and saw an error page, everything is now fixed.

Need help? Reply to this email or contact {SUPPORT_EMAIL}.

Thanks,
The Team
"""
    email_service.queue_and_send(
        email_to=order["email"],
        subject="Your subscription is active",
        body=body,
        related_attempt_id=attempt_id,
        related_order_id=order_id
    )


def create_support_ticket(email: str, subject: str, message: str,
                          attempt_id: Optional[str] = None,
                          order_id: Optional[str] = None,
                          internal_notes: Optional[str] = None) -> str:
    conn = db_connect()
    cur = conn.cursor()
    ticket_id = str(uuid.uuid4())
    ts = now_iso()

    cur.execute("""
        INSERT INTO support_tickets (
            id, email, subject, message, status, attempt_id, order_id,
            internal_notes, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        ticket_id, email, subject, message, "open", attempt_id, order_id,
        internal_notes, ts, ts
    ))
    conn.commit()
    conn.close()
    return ticket_id


def support_lookup_by_email(email: str) -> Dict[str, Any]:
    conn = db_connect()
    cur = conn.cursor()

    cur.execute("""
        SELECT * FROM purchase_attempts
        WHERE email = ?
        ORDER BY created_at DESC
    """, (email,))
    attempts = [dict(r) for r in cur.fetchall()]

    cur.execute("""
        SELECT * FROM orders
        WHERE email = ?
        ORDER BY created_at DESC
    """, (email,))
    orders = [dict(r) for r in cur.fetchall()]

    cur.execute("""
        SELECT * FROM subscriptions
        WHERE email = ?
        ORDER BY created_at DESC
    """, (email,))
    subscriptions = [dict(r) for r in cur.fetchall()]

    conn.close()

    return {
        "email": email,
        "attempts": attempts,
        "orders": orders,
        "subscriptions": subscriptions,
    }


# =============================================================================
# Checkout route
# =============================================================================

CHECKOUT_PAGE = """
<!doctype html>
<html>
<head><title>Buy Annual Subscription</title></head>
<body>
  <h1>Buy {{ product_name }}</h1>
  <p>Price: $249/year</p>
  <form method="post" action="/checkout">
    <label>Email:
      <input type="email" name="email" required />
    </label>
    <button type="submit">Pay $249</button>
  </form>
  <p>Demo only: after payment, you can simulate a crash with ?simulate_crash=1</p>
</body>
</html>
"""

SUCCESS_PAGE = """
<!doctype html>
<html>
<head><title>Success</title></head>
<body>
  <h1>Payment successful</h1>
  <p>Your subscription is active.</p>
  <p>Order ID: {{ order_id }}</p>
  <p><a href="/account?order_id={{ order_id }}">Go to account</a></p>
</body>
</html>
"""

RECOVERY_PAGE = """
<!doctype html>
<html>
<head><title>We received your payment</title></head>
<body>
  <h1>We received your payment</h1>
  <p>Thanks — your bank may show a charge, and our system is finishing setup.</p>
  <p><strong>You do not need to pay again.</strong></p>

  <p>What happens next:</p>
  <ul>
    <li>We’ll automatically reconcile your purchase.</li>
    <li>We’ll email {{ email }} as soon as your subscription is active.</li>
    <li>If you need help sooner, contact <a href="mailto:{{ support_email }}">{{ support_email }}</a>.</li>
  </ul>

  <p>Reference ID: {{ attempt_id }}</p>
  <p>Please include that reference if you contact support.</p>
</body>
</html>
"""

GENERIC_FAILURE_PAGE = """
<!doctype html>
<html>
<head><title>Checkout issue</title></head>
<body>
  <h1>We couldn't complete checkout</h1>
  <p>Your card may or may not have been charged. Please do not retry immediately.</p>
  <p>If a charge appears, contact <a href="mailto:{{ support_email }}">{{ support_email }}</a> and include this reference ID:</p>
  <p><strong>{{ attempt_id }}</strong></p>
</body>
</html>
"""


@app.route("/", methods=["GET"])
def home():
    return render_template_string(CHECKOUT_PAGE, product_name=PRODUCT_NAME)


@app.route("/checkout", methods=["POST"])
def checkout():
    """
    Request path behavior:
    - create local attempt before charging
    - charge with idempotency key
    - if charge succeeds, persist processor payment id
    - provision order/subscription
    - if crash occurs after charge, mark recoverable state and show recovery page
    """
    email = request.form["email"].strip().lower()
    simulate_crash = request.args.get("simulate_crash") == "1"

    # 1) Durable local record before external charge
    attempt_id = create_purchase_attempt(email)
    attempt = get_attempt(attempt_id)
    idempotency_key = attempt["idempotency_key"]

    try:
        # 2) Charge card with processor idempotency
        payment = payment_processor.charge(
            email=email,
            amount_cents=PRODUCT_PRICE_CENTS,
            currency="usd",
            idempotency_key=idempotency_key
        )

        if not payment.success or not payment.payment_id:
            mark_attempt_failed(attempt_id, payment.error or "Unknown payment error")
            return render_template_string(
                GENERIC_FAILURE_PAGE,
                support_email=SUPPORT_EMAIL,
                attempt_id=attempt_id
            ), 500

        # 3) Save successful processor charge immediately
        update_attempt_after_charge(attempt_id, payment.payment_id, status="charged")

        # 4) Simulate crash after payment but before order write
        if simulate_crash:
            raise RuntimeError("Simulated server crash after successful charge")

        # 5) Provision order + subscription
        order_id = create_order_and_subscription_from_attempt(attempt_id)

        return render_template_string(SUCCESS_PAGE, order_id=order_id)

    except Exception as exc:
        # Important distinction:
        # if processor_payment_id was already saved, user has likely been charged
        # and we should direct them to a recovery page, not tell them to retry payment.
        latest_attempt = get_attempt(attempt_id)

        if latest_attempt and latest_attempt["processor_payment_id"]:
            mark_attempt_needs_recovery(attempt_id, str(exc))

            # Also create a lightweight support ticket automatically so the team
            # has visibility without waiting for the user to reach out.
            create_support_ticket(
                email=email,
                subject="Auto-created: charged but not provisioned",
                message=(
                    "A payment succeeded but checkout did not finish. "
                    "User should receive recovery flow and automatic reconciliation."
                ),
                attempt_id=attempt_id,
                internal_notes=f"Exception during checkout: {exc}"
            )

            return render_template_string(
                RECOVERY_PAGE,
                email=email,
                support_email=SUPPORT_EMAIL,
                attempt_id=attempt_id
            ), 202

        mark_attempt_failed(attempt_id, str(exc))
        return render_template_string(
            GENERIC_FAILURE_PAGE,
            support_email=SUPPORT_EMAIL,
            attempt_id=attempt_id
        ), 500


# =============================================================================
# Account and support endpoints
# =============================================================================

@app.route("/account", methods=["GET"])
def account():
    order_id = request.args.get("order_id")
    if not order_id:
        return "Missing order_id", 400

    conn = db_connect()
    cur = conn.cursor()
    cur.execute("""
        SELECT o.id as order_id, o.email, s.status, s.starts_at, s.ends_at
        FROM orders o
        JOIN subscriptions s ON s.order_id = o.id
        WHERE o.id = ?
    """, (order_id,))
    row = cur.fetchone()
    conn.close()

    if not row:
        return "Account not ready yet or order not found", 404

    return jsonify(dict(row))


SUPPORT_FORM = """
<!doctype html>
<html>
<head><title>Support</title></head>
<body>
  <h1>Contact support</h1>
  <form method="post" action="/support">
    <label>Email:
      <input type="email" name="email" required />
    </label><br><br>
    <label>Reference ID (optional):
      <input type="text" name="attempt_id" />
    </label><br><br>
    <label>Message:<br>
      <textarea name="message" rows="8" cols="60" required></textarea>
    </label><br><br>
    <button type="submit">Send</button>
  </form>
</body>
</html>
"""


@app.route("/support", methods=["GET", "POST"])
def support():
    if request.method == "GET":
        return SUPPORT_FORM

    email = request.form["email"].strip().lower()
    attempt_id = request.form.get("attempt_id", "").strip() or None
    message = request.form["message"].strip()

    lookup = support_lookup_by_email(email)

    # Small-team workflow:
    # If we find a charged-but-not-provisioned attempt, repair it immediately.
    repaired_order_id = None
    internal_notes = []

    for attempt in lookup["attempts"]:
        if attempt["status"] in ("charged", "needs_recovery") and attempt["processor_payment_id"]:
            try:
                repaired_order_id = create_order_and_subscription_from_attempt(attempt["id"])
                internal_notes.append(
                    f"Auto-repaired attempt {attempt['id']} into order {repaired_order_id}."
                )
                break
            except Exception as exc:
                internal_notes.append(
                    f"Auto-repair failed for attempt {attempt['id']}: {exc}"
                )

    ticket_id = create_support_ticket(
        email=email,
        subject="Customer reported charge without access",
        message=message,
        attempt_id=attempt_id,
        order_id=repaired_order_id,
        internal_notes="\n".join(internal_notes) if internal_notes else None
    )

    # Send a support acknowledgment email.
    ack_body = f"""Hi,

We got your message and are looking into it.

Support ticket: {ticket_id}

If your bank shows the $249 charge but you have no access yet:
- please do not pay again
- we will either activate the subscription or refund you if needed

A small team member will follow up shortly.

Thanks,
Support
"""
    email_service.queue_and_send(
        email_to=email,
        subject="We received your support request",
        body=ack_body,
        related_attempt_id=attempt_id,
        related_order_id=repaired_order_id
    )

    return {
        "ticket_id": ticket_id,
        "status": "received",
        "auto_repaired_order_id": repaired_order_id
    }


# =============================================================================
# Reconciliation
# =============================================================================

def reconcile_orphaned_successful_charges() -> Dict[str, Any]:
    """
    Background repair job.

    Strategy:
    1. Find local attempts marked charged / needs_recovery with processor_payment_id
       but no order. Provision them.
    2. Also scan processor successful payments and ensure each has a local attempt/order.
       If payment exists at processor but local DB is missing the order, repair it.
    3. If processor payment exists but local attempt is somehow missing entirely,
       create a support ticket for manual investigation (because we lack local context).
    """
    repaired_attempts = []
    manual_review = []

    conn = db_connect()
    cur = conn.cursor()

    # 1) Local attempts that are known charged but not yet provisioned
    cur.execute("""
        SELECT * FROM purchase_attempts
        WHERE status IN ('charged', 'needs_recovery')
          AND processor_payment_id IS NOT NULL
    """)
    attempts = [dict(r) for r in cur.fetchall()]
    conn.close()

    for attempt in attempts:
        if not order_exists_for_attempt(attempt["id"]):
            try:
                order_id = create_order_and_subscription_from_attempt(attempt["id"])
                repaired_attempts.append({
                    "attempt_id": attempt["id"],
                    "order_id": order_id,
                    "email": attempt["email"]
                })
            except Exception as exc:
                manual_review.append({
                    "attempt_id": attempt["id"],
                    "reason": f"Failed to provision from known charged attempt: {exc}"
                })

    # 2) Processor scan: successful payments with no matching order
    for payment in payment_processor.list_successful_payments():
        if order_exists_for_payment(payment.payment_id):
            continue

        conn = db_connect()
        cur = conn.cursor()
        cur.execute("""
            SELECT * FROM purchase_attempts
            WHERE idempotency_key = ?
        """, (payment.idempotency_key,))
        attempt = cur.fetchone()
        conn.close()

        if attempt:
            # Ensure attempt reflects processor state, then repair.
            update_attempt_after_charge(attempt["id"], payment.payment_id, status="charged")
            try:
                order_id = create_order_and_subscription_from_attempt(attempt["id"])
                repaired_attempts.append({
                    "attempt_id": attempt["id"],
                    "order_id": order_id,
                    "email": attempt["email"]
                })
            except Exception as exc:
                manual_review.append({
                    "attempt_id": attempt["id"],
                    "payment_id": payment.payment_id,
                    "reason": f"Processor payment found but repair failed: {exc}"
                })
        else:
            # Rare but serious: charged at processor but no local attempt exists.
            # For a small team, create a manual support task immediately.
            ticket_id = create_support_ticket(
                email=payment.email,
                subject="Manual review: processor charge without local attempt",
                message=(
                    "Successful payment exists at processor, but no local purchase_attempt "
                    "record was found. Investigate whether to provision or refund."
                ),
                internal_notes=(
                    f"payment_id={payment.payment_id}, email={payment.email}, "
                    f"amount_cents={payment.amount_cents}, idempotency_key={payment.idempotency_key}"
                )
            )
            manual_review.append({
                "payment_id": payment.payment_id,
                "email": payment.email,
                "ticket_id": ticket_id,
                "reason": "No local attempt found for successful processor payment"
            })

    return {
        "repaired_attempts": repaired_attempts,
        "manual_review": manual_review
    }


@app.route("/admin/reconcile", methods=["POST", "GET"])
def admin_reconcile():
    """
    Trigger reconciliation manually.
    In production, run this from cron / scheduler every few minutes.
    """
    result = reconcile_orphaned_successful_charges()
    return jsonify(result)


@app.route("/admin/support_lookup", methods=["GET"])
def admin_support_lookup():
    email = request.args.get("email")
    if not email:
        return {"error": "missing email"}, 400
    return jsonify(support_lookup_by_email(email.strip().lower()))


# =============================================================================
# Main
# =============================================================================

if __name__ == "__main__":
    init_db()
    app.run(debug=True)

What this solves

1. Error handling in checkout

The important protections are:

• purchase_attempt is written before charging
• payment request uses an idempotency key
• if payment succeeds, processor_payment_id is immediately saved
• if anything fails after that, the user gets a recovery page, not “try paying again”

That avoids the worst outcome: duplicate charges from panic retries.

User-facing recovery page

The code returns this page when the app knows a charge likely happened but provisioning did not:

• “We received your payment”
• “You do not need to pay again”
• tells user reconciliation is automatic
• gives support email
• gives a reference ID (attempt_id)

That reference ID matters a lot for a tiny support team.

Confirmation email logic

The confirmation email is sent only after:

• order exists
• subscription exists

And it is deduplicated by checking whether an email with subject "Your subscription is active" was already logged for that order.

That means: - no confirmation email before entitlement is real - reconciliation can safely send the email later if request-time flow failed

Reconciliation process

reconcile_orphaned_successful_charges() does two things:

A. Repair known local charged attempts

If the DB has: - purchase_attempt.status in ('charged', 'needs_recovery') - processor_payment_id is not null - no order

then it creates the order and subscription.

B. Cross-check processor success records

If the processor has a successful payment but the app has no matching order: - try to find the local attempt by idempotency_key - if found, repair it - if not found, create a manual-review support ticket

For a small SaaS, running this every 5–10 minutes is usually enough. If you have webhooks, you’d also reconcile on webhook receipt.

Support workflow for a two-person team

The /support route demonstrates a practical workflow:

When the user writes in: 1. Look up attempts/orders/subscriptions by email 2. If there’s a charged-but-not-provisioned attempt, auto-repair immediately 3. Open a support ticket with internal notes 4. Send acknowledgment email telling the user not to pay again

That means support is not starting from scratch.

Recommended human support SOP

For a two-person team, I’d keep this very simple:

If user says “I was charged but have no access”

1. Search by email in support lookup
2. If charged or needs_recovery with no order:
3. run repair
4. verify subscription exists
5. resend confirmation email
6. Reply with:
7. apology
8. confirmation it’s activated now
9. direct login/access link
10. If processor shows charge but no recoverable local state:
11. manually verify payment ID / amount / email
12. either provision manually or refund same day
13. Add an internal note about root cause if recurring

Why this design is safer than “charge then write order”

Because “charge then write order” without a durable attempt record creates an unrecoverable ambiguity.

With the attempt record: - there is always a local artifact to tie the payment back to - idempotency prevents accidental duplicate charges - reconciliation has something concrete to repair

Production notes

If you move this into a real system, I’d add:

• real payment provider webhooks
• a job queue for email and provisioning
• database uniqueness constraints on entitlement creation
• alerting when needs_recovery attempts appear
• support admin auth
• structured logging with attempt_id, payment_id, order_id
• refunds API integration for unrecoverable cases
• rate-limited retry logic
• metrics:
• successful checkouts
• recovery-page rate
• reconciliation repairs
• time-to-provision after charge

The key operational principle

For this exact failure mode, the user experience should be:

• never tell them to pay again
• acknowledge the charge may be real
• preserve a recovery reference
• automatically repair in the background
• empower support to repair in one step

If you want, I can also provide: 1. a Stripe-based version using PaymentIntents + webhooks, 2. unit tests for the reconciliation logic, 3. a version using SQLAlchemy instead of raw sqlite3.