Building Web and App Products That Can Evolve After Launch

Keep request acceptance and notification intent together

The request_state.py module below accepts a trusted account, a submission key and a body. A real application must establish that account from its authenticated context and current permissions. The client may supply a key, but must not choose the trusted account. The string checks are deliberately small, not a complete service-request validation policy.

For a new key, submit starts an explicit transaction and inserts the request before its pending notification intent. An injected failure between those writes rolls both back. For an existing account/key pair, identical body text returns the original reference; changed text is rejected. Matching is exact, so a production API must decide normalization before it adopts this contract.

SQLite permits one simultaneous write transaction. BEGIN IMMEDIATE requests that write transaction before the lookup, so this local check and insert are serialized with other writers. Python’s sqlite3 documentation explains explicit transaction control when isolation_level is None. Lock waits can still fail; the module supplies a bounded connection timeout, not a complete contention-retry policy.

"""Synthetic local request boundary; caller supplies a trusted account, not HTTP input."""
import sqlite3


def connect(path):
    db = sqlite3.connect(path, isolation_level=None, timeout=3)
    db.execute("PRAGMA foreign_keys=ON")
    return db


def create_schema(db):
    db.executescript("""
        CREATE TABLE requests (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            account TEXT NOT NULL,
            submission_key TEXT NOT NULL,
            body TEXT NOT NULL,
            UNIQUE(account, submission_key)
        );
        CREATE TABLE notifications (
            request_id INTEGER PRIMARY KEY REFERENCES requests(id) ON DELETE CASCADE,
            state TEXT NOT NULL DEFAULT 'pending'
        );
    """)


def submit(db, account, key, body, checkpoint=lambda: None):
    for label, value, limit in [("account", account, 64), ("key", key, 64), ("body", body, 1000)]:
        if not isinstance(value, str) or not value.strip() or len(value) > limit:
            raise ValueError(label)
    db.execute("BEGIN IMMEDIATE")
    try:
        prior = db.execute(
            "SELECT id, body FROM requests WHERE account=? AND submission_key=?",
            (account, key),
        ).fetchone()
        if prior:
            if prior[1] != body:
                raise ValueError("submission key reused with different body")
            db.execute("COMMIT")
            return prior[0]
        reference = db.execute(
            "INSERT INTO requests(account, submission_key, body) VALUES (?, ?, ?)",
            (account, key, body),
        ).lastrowid
        checkpoint()
        db.execute("INSERT INTO notifications(request_id) VALUES (?)", (reference,))
        db.execute("COMMIT")
        return reference
    except BaseException:
        if db.in_transaction:
            db.execute("ROLLBACK")
        raise


def lookup(db, account, key):
    return db.execute(
        "SELECT id, body FROM requests WHERE account=? AND submission_key=?",
        (account, key),
    ).fetchone()


def retrieve(db, account, reference):
    return db.execute(
        "SELECT id, body FROM requests WHERE account=? AND id=?",
        (account, reference),
    ).fetchone()

Reconcile the unknown result before creating another request

The captured trace starts with account-a and retry-key accepting the body “repair” as reference 1. The test discards that return and closes the connection. Reopening and looking up that account/key returns reference 1 with the same body. Resubmitting the same values returns 1 again, with one request and one notification intent in the database.

Reusing retry-key with “replace” raises an error and leaves “repair” unchanged. Account-b cannot find account-a’s request through either its submission key or reference. The same key can identify an independent request in account-b, producing reference 2. These results test query scoping for trusted account inputs, not whether an attacker can forge an authenticated session.

OWASP distinguishes authentication from authorization and recommends permission checks on every request. I recommend carrying the same account boundary through a future status endpoint and document retrieval. Hiding another account’s record in the list screen would not protect a direct lookup. This module only implements submission, lookup and retrieval; it has no edit, membership or file-download operation.

Exercise simultaneous submissions without promising unlimited concurrency

Two test threads open separate SQLite connections and meet at a barrier before submitting the same account-c key and body. Both return reference 4, and the database contains one account-c request with one notification intent. The preceding deletion counterexample consumes reference 3, which explains the sequence.

This is actual local thread execution, not a throughput measurement or proof for every possible schedule. The application uses SQLite’s write lock and a uniqueness constraint on account plus submission key. Another database or a split service needs its own transaction and conflict handling. No load, timeout-exhaustion, process-kill or distributed-writer result is claimed.

A stored intent cannot undo an external send

The notification probe appends reference 1 to a Python list, then raises an injected exception before marking the intent sent. The accepted request remains retrievable and the intent stays pending. Retrying appends 1 again before marking it sent. The visible external-effect list is therefore [1, 1], despite one local intent.

I recommend treating durable acceptance and external delivery as separate outcomes. The database can retain work for an operator without proving that an email was delivered once. A real relay needs a defined retry and reconciliation policy, with a provider contract if it relies on receiver deduplication. This list is neither a broker nor an implementation of those operational controls.

The fixture passes 27 named assertions and rejects five actual source defects at their intended assertions: an early request commit, missing notification intent, ignored body conflict, unscoped key lookup and unscoped reference retrieval. It records the exact preceding observations and executed-source hashes for each failure. These are injected application failures, not power-loss durability tests.

Retiring replay records changes the behaviour

The test intentionally deletes reference 1 and its dependent notification intent. Submitting the original account/key/body afterwards creates reference 3. Deleting the memory of a request has allowed an old submission to become new. The earlier synthetic delivery effect still exists outside the database.

I recommend agreeing a replay window and retention policy together before adding automatic deletion. Clients need to know when a key can no longer reconcile an old attempt. A minimal tombstone may preserve replay decisions without retaining the whole request body, but its contents and retention need a separate design and test. This fixture implements neither tombstones nor a recommended retention period.

Changes after launch must preserve whichever policy is chosen. A new client must not silently generate a fresh key for the same uncertain attempt. A schema migration needs compatibility checks with the old application before rollback is trusted. Those are future release requirements; this fixture tests neither client storage nor schema migration.

Let the next interface prove the same contract

I recommend keeping one backend deployment while the request and notification-intent writes benefit from a shared transaction. The trade-off is shared release and capacity constraints. Extracting notification delivery can be useful, but moving the intent to another database would remove the transaction demonstrated here and require a new handoff design.

W3C’s form guidance calls for clear submission feedback and actionable errors. For a real portal, demonstrate correction after invalid input and reconciliation after an uncertain response, including keyboard and assistive-technology use. The backend tests establish no frontend accessibility result. Confirmation must describe the state actually known, rather than equating “recorded” with “read by support.”

Keep these boundary tests beside future changes and add the missing integration checks for the system being built. Assign an operator to unresolved submissions and delivery failures. A different framework, native client or proposed AI feature earns its place by preserving the required outcomes and supplying evidence for its new failure cases. Passing this local example does not certify a production release.