Stop SQLite INSERT OR IGNORE From Hiding Invalid Rows

A finite SQLite import can finish without an exception and still fail its business contract. The failure mode is easy to miss: INSERT OR IGNORE can discard an input whose required payload is NULL, just as it can discard a row that collides with an existing unique value. SQLite documents that IGNORE suppresses applicable uniqueness and NOT NULL constraint errors by skipping the offending row; a successful return therefore does not establish that every submitted input became a stored row or an approved duplicate. SQLite’s ON CONFLICT documentation makes that engine behavior explicit.

The diagnostic target in this playbook is narrower than “how SQLite conflicts work.” We need one defensible disposition for every input in an owned batch: inserted, accepted identical replay, rejected invalid input, or held because the same request key carries different content. Commit is a separate decision. Fresh-connection visibility is another. Simulated acknowledgement comes last.

The repair candidate is correspondingly narrow:

INSERT INTO items VALUES (?,?,?,?)
ON CONFLICT(request_key) DO NOTHING

SQLite’s UPSERT rules let a named conflict target intervene for that uniqueness identity without converting NOT NULL or foreign-key failures into no-ops. But even this is not a replay proof. DO NOTHING only tells SQLite what to do when request_key conflicts. It does not tell the application whether the existing stored tuple is identical to the incoming request. That final equality check belongs to the application’s declared admission policy. SQLite’s UPSERT documentation distinguishes the uniqueness conflict target from other constraint classes.

This article evaluates that contract against a fourteen-case synthetic laboratory and separates statement behavior, policy acceptance, rollback, committed readback, and acknowledgement evidence.

Define the insert and replay outcomes the caller may accept

The first reliability rule is to write the acceptance contract before choosing SQL.

For this fixture, an input has four fields:

(request_key, external_ref, payload, tenant_id)

request_key is the replay identity. external_ref is a second, independently unique business identity. payload is required. tenant_id must reference an existing tenant.

Those meanings must not be collapsed. Two rows can share a request_key while disagreeing on payload; they can also have different request keys while colliding on external_ref. Those are different incidents and require different owners.

The article-owned replay policy is deliberately strict: an existing request key counts as an accepted replay only when the complete stored tuple exactly equals the complete incoming tuple. For the baseline request:

("key-a", "ref-a", "alpha", 1)

the same four values constitute an identical replay. This input does not:

("key-a", "ref-a", "CHANGED", 1)

even though its request_key is the same.

That exact-tuple rule is not a universal SQL definition of semantic equality. It is the synthetic business contract for this experiment. A real application might intentionally exclude server-populated columns or compare a separately defined canonical payload. Such a policy would need its own independent specification and qualification.

The dispositions are therefore:

A normal statement return is not itself one of those business dispositions.

This scope is also distinct from SQLite conflict writes and child-row preservation, where the central question is whether a conflict write deletes and reconstructs an existing parent. Here no accepted repair deletes or overwrites an existing row; the question is whether an input is allowed to disappear and still be called accepted.

For release review, the governing rule is stronger than “the database is consistent”: ACCEPT only when every input has either an approved exact insert or an approved identical-replay result, and committed readback agrees.

Freeze one owned SQLite transaction and its environment

A constraint experiment is only useful when its transaction and runtime assumptions are bounded.

The supplied preparation record identifies:

CPython 3.12.14
SQLite 3.53.1
Linux x86_64
one writer
one fresh disposable database file per case
standard-library sqlite3
foreign_keys=ON and verified before BEGIN
immediate constraints
ordinary tables
autocommit=True
explicit SQL BEGIN / COMMIT / ROLLBACK

Those are preparation versions, not claims that they are the newest available releases.

The current Python 3.12 documentation page is labeled 3.12.15, while the recorded execution used CPython 3.12.14. The documentation states that sqlite3.connect(..., autocommit=...) was added in Python 3.12, that Connection.in_transaction exposes the underlying SQLite transaction state, and that SQLite-originated exceptions can carry sqlite_errorcode and sqlite_errorname.

Foreign-key enforcement is a precondition, not an outcome inferred from later data. SQLite documents that enforcement is configured per connection and that changing PRAGMA foreign_keys while a multi-statement transaction is already active has no effect. The fixture therefore enables it and reads back (1,) before BEGIN. SQLite’s foreign-key documentation explicitly recommends checking the setting rather than depending on a default.

That initialization problem has its own larger test matrix in SQLite foreign-key connection checks. This experiment assumes that precondition has already been satisfied and uses the foreign-key case only to contrast OR IGNORE behavior with NOT NULL and uniqueness suppression.

Transaction ownership matters because the eventual rollback policy belongs to the component that opened the transaction. A helper that receives an arbitrary connection cannot safely decide that every caller’s transaction should be abandoned. The laboratory avoids that ambiguity: the case itself executes BEGIN, performs the measured statements, inspects the state, and decides COMMIT or ROLLBACK.

Separate driver autocommit from explicit SQL boundaries

autocommit=True in this fixture does not mean “never use transactions.” It places the underlying SQLite connection in autocommit mode until SQL explicitly starts one. Python’s documentation says that with Connection.autocommit=True, Connection.commit() and Connection.rollback() have no effect; explicit SQL transaction control remains available, and in_transaction reports whether an SQLite transaction is active.

The measured boundary is therefore literally:

BEGIN;
-- one or more tested statements
COMMIT;

or:

BEGIN;
-- provisional work
ROLLBACK;

The fixture deliberately does not replace those commands with db.commit() or db.rollback().

SQLite also distinguishes a failed statement from termination of the surrounding transaction. Under the normal ABORT behavior, the failing statement is backed out, earlier statements in the transaction are preserved, and the transaction remains active. That distinction becomes critical in the batch rollback cases.

Build the two-key schema and independent input manifest

The schema contains two independent uniqueness rules:

CREATE TABLE tenants(
    id INTEGER PRIMARY KEY
);

CREATE TABLE items(
    request_key  TEXT NOT NULL UNIQUE,
    external_ref TEXT NOT NULL UNIQUE,
    payload      TEXT NOT NULL,
    tenant_id    INTEGER NOT NULL REFERENCES tenants(id)
);

request_key and external_ref are intentionally separate. If the importer treats every uniqueness collision as deduplication, it cannot tell a replay from a collision on another business identity.

The literal test manifest is:

Tenant 1 exists. Tenant 999 does not.

The fixture’s equality policy is literal Python tuple equality. That keeps the laboratory focused on conflict admission. A system whose business identity requires normalization, collation rules, or case folding must qualify that policy separately; Unicode name-equality policies in SQLite addresses that different class of problem.

No Unicode normalization is attempted here.

Keep expected outcomes outside the SQL handler

The expected result must exist before the tested statement runs.

That principle prevents circular reasoning such as:

The row is absent, therefore it must have been a duplicate.

For NULL, the independent oracle says the payload is invalid before SQL runs. For OTHER_UNIQUE, the manifest says key-d is a new request key and that ref-a is already owned by the baseline. For MISMATCH, the incoming tuple is known to differ from the baseline.

The SQL handler may produce evidence, but it does not define the oracle.

This distinction is particularly important with broad suppression. If a write path first executes INSERT OR IGNORE, then merely searches for the input key afterward, it can discover that a missing row was skipped. It still cannot legitimately relabel every skip as an accepted replay. It must compare the observed state with the independently specified input contract.

Reproduce a missing payload with no raised error

The broad candidate is:

INSERT OR IGNORE INTO items VALUES (?,?,?,?)

Q1 establishes the valid-insert control. The input is B:

("key-b", "ref-b", "beta", 1)

The statement returns without an error, and the transaction-visible table becomes:

("key-a", "ref-a", "alpha", 1)
("key-b", "ref-b", "beta", 1)

That is an ordinary accepted insert.

Q2 uses the exact baseline tuple. It also returns without an error, but no new row appears because request_key and external_ref already conflict with the baseline. Under the experiment’s independently declared policy, Q2 can be called an identical-replay control because the incoming tuple was already known to equal the stored tuple exactly.

Q3 is the dangerous case:

("key-c", "ref-c", NULL, 1)

It also returns without an exception, yet key-c is absent afterward.

That is documented SQLite behavior. For applicable NOT NULL and uniqueness violations, the IGNORE conflict algorithm skips the offending row and returns no constraint error; foreign-key handling is different. The SQLite ON CONFLICT reference states this behavior directly.

The critical diagnostic comparison is therefore:

The same Python-level symptom, “no exception,” describes three different input outcomes.

A naive importer can misacknowledge Q3 by equating successful execution with successful admission. For example, code that appends an ordinal after each execute() call will record the missing-payload input as processed even though there is no corresponding row and no approved replay.

The appropriate operational result is therefore not ACCEPT. It is REPAIR for the broad-IGNORE write path and REJECT_INPUT for the offending record once its missing required value is exposed.

An expected rejection can still make Q3 a passing laboratory test. “Test passed” means the fixture observed the expected engine behavior. It does not mean the business input became valid.

Expose a collision on the wrong uniqueness constraint

Q4 demonstrates why “duplicate tolerant” must name the duplicate identity.

The incoming row is:

("key-d", "ref-a", "delta", 1)

key-d is new. It is not a replay of key-a. But its external_ref is ref-a, which is already owned by the baseline row.

Under INSERT OR IGNORE, the uniqueness violation on external_ref is suppressed and the candidate row is skipped. SQLite documents IGNORE as applying to applicable uniqueness conflicts generally; it is not scoped to the application’s preferred request identity.

After the statement, the table still contains only:

("key-a", "ref-a", "alpha", 1)

The absence of key-d proves that the attempted input was not stored. It does not establish that ignoring it was an approved business action.

There is also no basis here for choosing a “winner,” overwriting the baseline, or inventing a replacement external_ref. This is an ownership conflict between two independently declared identities. The import should surface it.

That gives Q4 two useful conclusions.

First, broad OR IGNORE is too permissive for a policy that intends to tolerate only repeated request_key values.

Second, merely checking that the database remains constraint-valid is insufficient. The database is perfectly valid after silently dropping key-d; the admission contract is what failed.

For the write path, the decision remains REPAIR. For the row itself, the correct disposition is REJECT_INPUT until the source-data owner resolves which input is authoritative.

Observe the foreign-key error that IGNORE does not hide

Q5 supplies:

("key-f", "ref-f", "phi", 999)

The request_key, external_ref, and payload are otherwise novel and non-NULL. The failure is the immediate foreign key because tenant 999 does not exist.

With foreign-key enforcement already enabled and verified, INSERT OR IGNORE raises an integrity error rather than silently skipping the row. SQLite explicitly documents that IGNORE behaves like ABORT for foreign-key constraint errors.

The supplied preparation evidence records:

SQLITE_CONSTRAINT_FOREIGNKEY
code 787

SQLite’s result-code reference identifies extended code 787 as SQLITE_CONSTRAINT_FOREIGNKEY. The Python driver exposes SQLite-originated numeric and symbolic values through sqlite_errorcode and sqlite_errorname.

The table remains at the baseline after the failed statement.

This is why “OR IGNORE ignores constraint errors” is an unsafe shorthand. Its documented behavior depends on the constraint class. In this laboratory it suppresses the NOT NULL and uniqueness violations but exposes the immediate foreign-key failure.

The targeted-clause foreign-key behavior is supported by SQLite’s UPSERT documentation as well: UPSERT processing intervenes for uniqueness constraints, not NOT NULL, CHECK, or foreign-key failures. The matrix did not separately execute a targeted foreign-key case, so that conclusion should remain identified as documentation-backed rather than separately observed here.

Record the error name and the transaction state together

The Q5 evidence is not complete with only the exception.

The fixture also asserts:

require(db.in_transaction, case + " transaction unexpectedly ended")

The supplied observation is therefore: the foreign-key statement failed, the baseline remained unchanged, and the explicitly owned transaction was still active before the fixture issued its decision.

That is consistent with SQLite’s ABORT semantics: a constraint failure can abort the statement while preserving earlier work in the same transaction and leaving the transaction open.

For this owned one-row probe, the fixture then executes an explicit SQL ROLLBACK.

That should not be overgeneralized into “every SQLite error leaves the transaction active.” SQLite documents other error classes where automatic rollback may occur, and applications must inspect transaction state rather than assuming one universal response.

The laboratory proves the measured immediate-constraint cases, not every possible SQLite failure.

Narrow the no-op to the intended request key

The repair candidate replaces broad suppression with a named UPSERT conflict target:

INSERT INTO items VALUES (?,?,?,?)
ON CONFLICT(request_key) DO NOTHING

This is materially narrower.

SQLite defines the expression between ON CONFLICT and DO as the conflict target. If that target’s uniqueness constraint fails, SQLite executes the associated DO NOTHING or DO UPDATE. UPSERT processing applies to uniqueness constraints; it does not intervene for failed NOT NULL or foreign-key constraints. A final targetless clause could catch other uniqueness constraints, but that is precisely why a targetless DO NOTHING is not the repair prescribed here.

Q6 through Q9 establish the important boundaries.

Q6 inserts B successfully:

("key-b", "ref-b", "beta", 1)

The row persists. Nothing special happens because no conflict exists.

Q7 supplies the exact baseline. The request_key uniqueness target conflicts, so the insert becomes a no-op. The baseline remains unchanged. That result is eligible for replay classification, but only because independent equality evidence says the incoming tuple is identical.

Q8 supplies the NULL payload:

("key-c", "ref-c", NULL, 1)

There is no request_key conflict to invoke the DO NOTHING. The required payload fails instead. The preparation run records:

SQLITE_CONSTRAINT_NOTNULL
code 1299

SQLite identifies 1299 as the extended SQLITE_CONSTRAINT_NOTNULL code.

Q9 supplies:

("key-d", "ref-a", "delta", 1)

Again, request_key does not conflict. The failure is on the unrelated external_ref uniqueness constraint, and the named request_key handler does not absorb it. The preparation run records:

SQLITE_CONSTRAINT_UNIQUE
code 2067

SQLite documents 2067 as SQLITE_CONSTRAINT_UNIQUE.

The contrast is the core repair:

Parameter binding remains unchanged; narrowing conflict policy does not require constructing SQL with interpolated values.

The targeted statement therefore exposes invalid or wrong-identity conflicts that broad IGNORE concealed. But it still has one unresolved ambiguity: a request-key conflict says nothing about whether the content matches.

Reject a replay whose content no longer agrees

Q10 closes the most important loophole in the targeted repair.

The stored baseline is:

("key-a", "ref-a", "alpha", 1)

The incoming candidate is:

("key-a", "ref-a", "CHANGED", 1)

Executing:

INSERT INTO items VALUES (?,?,?,?)
ON CONFLICT(request_key) DO NOTHING

returns without an error. The named target fires because request_key="key-a" already exists. SQLite performs the requested no-op. The stored payload remains "alpha".

At the SQL-mechanism level, this is correct behavior. The conflict target was matched and DO NOTHING did nothing.

At the application-policy level, it is not an accepted replay.

A system that classifies every conflict on request_key as “already processed” would incorrectly acknowledge the incoming "CHANGED" request even though the database contains "alpha".

That is why the corrected workflow performs a read after a no-error targeted statement:

SELECT *
FROM items
WHERE request_key = ?;

and compares the complete result with the incoming tuple.

For this fixture:

stored == item

is the replay gate.

Q10 deliberately does not use that gate yet. It proves why the targeted SQL clause alone is insufficient.

The resulting disposition is HOLD_PAYLOAD_CONFLICT. The existing row is not overwritten, because the experiment excludes DO UPDATE, and the incoming value is not silently discarded as an accepted replay. Ownership must decide why the same request identity now names different content.

The same request key does not prove the same request

A uniqueness key establishes identity under a database constraint. It does not automatically establish equivalence of every other field the application cares about.

The replay contract in this fixture includes all four columns:

request_key
external_ref
payload
tenant_id

That means all four values must match exactly for a same-key request to be accepted as a replay.

There are plausible systems where a replay comparison would be different. An ingestion service might compare a canonical serialized body, exclude a received timestamp, or use a payload digest. None of those policies can be inferred from SQLite’s ON CONFLICT syntax. They have to be declared by the application and tested independently.

The safe rule is therefore:

A named conflict target narrows which database uniqueness condition may become a no-op. It does not prove that the new request is semantically identical to the stored request.

For the synthetic contract used here, exact tuple inequality is enough to stop acknowledgement. No silent update is permitted. The case is held for an owner.

Run and inspect the complete fourteen-case laboratory

The supplied preparation run reports PASS_14_CASES under CPython 3.12.14, SQLite 3.53.1, and Linux x86_64. It also reports the original preparation source SHA-256 as:

9d6f6ee4bc3d9b5a5082a249a93827cf6809eaad88c38c80c8b31ba2b05f2870

That digest identifies the supplied original source bytes. It must not be treated as the digest of a reformatted copy. The block below is the complete runnable fixture in its supplied substantive form, formatted for readability; no claim is made that this rendering has the same byte-for-byte digest. The executed PASS_14_CASES record discussed here remains supplied preparation evidence, not a new execution performed for this article.

"""Owned temporary SQLite files only; CPython 3.12+; no external database."""
import hashlib
import json
import platform
import sqlite3
import sys
import tempfile
from datetime import datetime, timezone
from pathlib import Path

BASE = ("key-a", "ref-a", "alpha", 1)
B = ("key-b", "ref-b", "beta", 1)
E = ("key-e", "ref-e", "epsilon", 1)
NULL = ("key-c", "ref-c", None, 1)
OTHER_UNIQUE = ("key-d", "ref-a", "delta", 1)
ORPHAN = ("key-f", "ref-f", "phi", 999)
MISMATCH = ("key-a", "ref-a", "CHANGED", 1)
IGNORE = "INSERT OR IGNORE INTO items VALUES (?,?,?,?)"
TARGET = "INSERT INTO items VALUES (?,?,?,?) ON CONFLICT(request_key) DO NOTHING"
EXPECTED_BASE = [BASE]
EXPECTED_SUCCESS = [BASE, B, E]


def require(ok, reason):
    if not ok:
        raise AssertionError(reason)


def rows(db):
    return db.execute("SELECT FROM items ORDER BY request_key").fetchall()


def setup(path):
    db = sqlite3.connect(path, autocommit=True)
    db.execute("PRAGMA foreign_keys=ON")
    require(db.execute("PRAGMA foreign_keys").fetchone() == (1,), "FK off")
    db.execute("CREATE TABLE tenants(id INTEGER PRIMARY KEY)")
    db.execute("INSERT INTO tenants VALUES(1)")
    db.execute("CREATE TABLE items(request_key TEXT NOT NULL UNIQUE, "
               "external_ref TEXT NOT NULL UNIQUE, payload TEXT NOT NULL, "
               "tenant_id INTEGER NOT NULL REFERENCES tenants(id))")
    db.execute("INSERT INTO items VALUES (?,?,?,?)", BASE)
    require(rows(db) == EXPECTED_BASE, "bad seed")
    return db


def reopen(path):
    db = sqlite3.connect(path.as_uri() + "?mode=ro", uri=True, autocommit=True)
    try:
        return rows(db)
    finally:
        db.close()


def attempt(db, sql, item):
    try:
        db.execute(sql, item).close()
        return None
    except sqlite3.IntegrityError as exc:
        return {"name": exc.sqlite_errorname, "code": exc.sqlite_errorcode}



def single(case, path, sql, item, expected_error, expected_rows):
    db = setup(path)
    try:
        db.execute("BEGIN")
        error = attempt(db, sql, item)
        require((error or {}).get("name") == expected_error, case + " error")
        visible = rows(db)
        require(visible == expected_rows, case + " visible rows")
        require(db.in_transaction, case + " transaction unexpectedly ended")
        db.execute("ROLLBACK" if error else "COMMIT")
        committed = reopen(path)
        require(committed == expected_rows, case + " reopened rows")
        return {"case": case, "error": error, "visible": visible,
                "reopened": committed, "transaction_ended": not db.in_transaction}
    finally:
        if db.in_transaction:
            db.execute("ROLLBACK")
        db.close()


def batch(case, path, sql, candidates, guarded, expected_error,
          expected_pre, expected_final, expected_attempted):
    db = setup(path)
    ledger = []
    failure = None
    try:
        db.execute("BEGIN")
        for ordinal, item in enumerate(candidates, 1):
            record = {"ordinal": ordinal, "request_key": item[0]}
            ledger.append(record)
            error = attempt(db, sql, item)
            if error:
                record["error"] = error
                failure = error["name"]
                break
            stored = db.execute(
                "SELECT FROM items WHERE request_key=?", (item[0],)
            ).fetchone()
            record["stored"] = stored
            if guarded and stored != item:
                failure = "PAYLOAD_MISMATCH"
                record["error"] = failure
                break
        pre = rows(db)
        require(db.in_transaction, case + " missing transaction")
        require(failure == expected_error, case + " failure")
        require(pre == expected_pre, case + " pre-decision rows")
        require(len(ledger) == expected_attempted, case + " attempted count")
        db.execute("ROLLBACK" if failure else "COMMIT")
        final = reopen(path)
        require(final == expected_final, case + " final rows")
        published = [] if failure else [r["ordinal"] for r in ledger]
        return {"case": case, "failure": failure, "ledger": ledger,
                "pre_decision": pre, "reopened": final,
                "published_ordinals_naive_or_guarded": published,
                "guarded": guarded}
    finally:
        if db.in_transaction:
            db.execute("ROLLBACK")
        db.close()


def main():
    require(sys.version_info >= (3, 12), "CPython 3.12+ required")
    report = {
        "status": "RUNNING",
        "started_utc": datetime.now(timezone.utc).isoformat(),
        "python": sys.version,
        "sqlite": sqlite3.sqlite_version,
        "platform": platform.platform(),
        "source_sha256": hashlib.sha256(Path(__file__).read_bytes()).hexdigest(),
        "cases": [],
    }
    try:
        with tempfile.TemporaryDirectory(prefix="sqlite-ignore-") as tmp:
            root = Path(tmp)
            probe = sqlite3.connect(":memory:")
            report["sqlite_source_id"] = probe.execute(
                "SELECT sqlite_source_id()"
            ).fetchone()[0]
            probe.close()

            plan = [
                ("Q1", IGNORE, B, None, [BASE, B]),
                ("Q2", IGNORE, BASE, None, EXPECTED_BASE),
                ("Q3", IGNORE, NULL, None, EXPECTED_BASE),
                ("Q4", IGNORE, OTHER_UNIQUE, None, EXPECTED_BASE),
                ("Q5", IGNORE, ORPHAN,
                 "SQLITE_CONSTRAINT_FOREIGNKEY", EXPECTED_BASE),
                ("Q6", TARGET, B, None, [BASE, B]),
                ("Q7", TARGET, BASE, None, EXPECTED_BASE),
                ("Q8", TARGET, NULL,
                 "SQLITE_CONSTRAINT_NOTNULL", EXPECTED_BASE),
                ("Q9", TARGET, OTHER_UNIQUE,
                 "SQLITE_CONSTRAINT_UNIQUE", EXPECTED_BASE),
                ("Q10", TARGET, MISMATCH, None, EXPECTED_BASE),
            ]

            for case, sql, item, error, expected in plan:
                report["cases"].append(
                    single(
                        case,
                        root / (case + ".db"),
                        sql,
                        item,
                        error,
                        expected,
                    )
                )

            plans = [
                ("Q11", IGNORE, [B, NULL, E], False, None,
                 EXPECTED_SUCCESS, EXPECTED_SUCCESS, 3),
                ("Q12", TARGET, [B, NULL, E], False,
                 "SQLITE_CONSTRAINT_NOTNULL",
                 [BASE, B], EXPECTED_BASE, 2),
                ("Q13", TARGET, [B, BASE, E], True, None,
                 EXPECTED_SUCCESS, EXPECTED_SUCCESS, 3),
                ("Q14", TARGET, [B, MISMATCH, E], True,
                 "PAYLOAD_MISMATCH",
                 [BASE, B], EXPECTED_BASE, 2),
            ]

            for case, sql, items, guard, error, pre, final, attempted in plans:
                report["cases"].append(
                    batch(
                        case,
                        root / (case + ".db"),
                        sql,
                        items,
                        guard,
                        error,
                        pre,
                        final,
                        attempted,
                    )
                )

            require(
                [r["case"] for r in report["cases"]]
                == [f"Q{i}" for i in range(1, 15)],
                "case coverage",
            )

        report["status"] = "PASS_14_CASES"
        report["temporary_databases_removed"] = True

    except Exception as exc:
        report["status"] = "FAIL"
        report["error"] = {
            "type": type(exc).__name__,
            "message": str(exc),
        }
        raise

    finally:
        report["finished_utc"] = datetime.now(timezone.utc).isoformat()
        print(json.dumps(report, indent=2, allow_nan=False))


if name == "__main__":
    main()

Run the original source from an owned working directory:

python3 sqlite_ignore_lab.py > sqlite-ignore-results.json

Acceptance requires both a successful process exit and a complete JSON report whose status is PASS_14_CASES. A truncated result, missing case, assertion failure, runtime mismatch requiring requalification, or failed process is RERUN, not acceptance.

The case ledger is:

PASS_14_CASES means all fourteen expected positive and negative controls behaved according to the fixture oracle. It does not endorse INSERT OR IGNORE; Q3, Q4, and Q11 are passing demonstrations that the broad path violates the desired admission contract.

Reconcile every input before acknowledging the batch

Q11 is the batch-level counterexample that turns the single-row findings into an operational risk.

The candidate sequence is:

1: B
2: NULL
3: E

The SQL is broad INSERT OR IGNORE.

B inserts. The NULL-payload row is silently skipped. E inserts. No exception occurs, so a naive loop that records “processed” after every execute() reaches ordinals 1, 2, and 3.

The committed database, however, contains:

baseline
B
E

There is no key-c.

That means the acknowledgement list and stored-state ledger disagree about ordinal 2.

The problem cannot be repaired by comparing only counts. Three input ordinals were recorded, but only two new rows appeared; even that aggregate discrepancy would not tell a reviewer which input was missing. In more complicated data, counts can also be preserved while identities differ.

The required ledger is per input. At minimum it needs the input ordinal, request identity, database error if any, and actual tuple read back under that request key. For a replay-capable path, the equality verdict is also required.

Q13 demonstrates the accepted pattern with:

1: B
2: exact baseline replay
3: E

Each targeted write returns without a fatal database error. The handler then selects by request_key and compares the complete tuple:

stored = db.execute(
    "SELECT * FROM items WHERE request_key=?",
    (item[0],),
).fetchone()

if stored != item:
    failure = "PAYLOAD_MISMATCH"

B agrees. The baseline replay agrees. E agrees.

Only after all three inputs have individually satisfied the contract does the fixture commit. It then opens a separate read-only connection and verifies the committed result:

baseline
B
E

That fresh connection distinguishes “visible to the writer before commit” from “visible as committed database state afterward.”

This article does not use generated identifiers. SQLite batch ID reconciliation addresses the separate problem of proving which generated receipt belongs to which input. Here the required receipt is an input disposition backed by the actual stored tuple, not a generated row ID.

A skipped invalid row must not receive an accepted disposition

A reliable ledger separates three superficially similar situations:

INSERTED
IDENTICAL_REPLAY
REJECTED_OR_HELD

An inserted row and an identical replay may both become accepted outcomes under this fixture’s business policy, but they are not the same database event.

Likewise, an invalid input must not become accepted simply because the surrounding loop continued.

For Q11, ordinal 2 is not an accepted replay because there is no stored key-c tuple equal to the input. In fact, the input could never equal a stored row under this schema because its payload is NULL and the column is NOT NULL.

For Q13, ordinal 2 can be accepted as an identical replay because the stored tuple exactly equals the independently specified incoming tuple.

The fixture’s published_ordinals_naive_or_guarded field is only a local simulation. No queue message, HTTP response, event, broker acknowledgement, or downstream side effect occurs. This is intentionally not an exactly-once delivery claim.

The ordering rule is:

1. Attempt the database admission.

2. Determine the per-input stored state.

3. Apply the declared replay equality contract.

4. Decide the batch.

5. Commit only an accepted batch.

6. Verify committed state from a fresh connection.

7. Only then construct or release the acknowledgement set.

That order prevents a silent IGNORE skip from being promoted into an accepted disposition merely because execution continued.

Roll back the whole batch that this operation owns

Q12 tests the corrected behavior for an invalid input in the middle of an owned batch:

1: B
2: NULL
3: E

The SQL is targeted ON CONFLICT(request_key) DO NOTHING.

Ordinal 1 inserts B provisionally.

Ordinal 2 reaches the required payload constraint and raises SQLITE_CONSTRAINT_NOTNULL.

Ordinal 3 is never attempted.

Before the decision, the same connection can see:

baseline
B

That is valuable evidence. It proves the first statement succeeded before the second failed.

It does not mean B should remain committed.

SQLite’s default ABORT behavior for the failing statement does not automatically undo earlier successful statements in the same explicit transaction. SQLite documents that prior statements are preserved and the transaction remains active under that conflict response.

The batch policy, not the ABORT algorithm, says the operation is indivisible for acknowledgement purposes. Because the importer owns this transaction, it explicitly executes:

ROLLBACK;

A read-only reopen then shows only the baseline.

Q14 exercises the same rollback policy for an application-level failure rather than a SQLite constraint exception:

1: B
2: changed replay
3: E

B is provisionally inserted. The targeted write for ordinal 2 performs a request-key no-op. The equality gate reads the stored baseline and discovers that "alpha" does not equal incoming "CHANGED".

The handler sets:

PAYLOAD_MISMATCH

and stops.

E remains unattempted. The transaction-visible state is baseline plus provisional B, and explicit rollback removes B. Reopened state returns to baseline.

The ledger must preserve that distinction:

Calling E “failed” would be inaccurate. Calling B “never inserted” would erase useful diagnostic evidence. Calling either one “acknowledged” would be worse.

Rollback is safe here because this operation opened and owns the transaction. A helper operating inside an unknown outer transaction must not assume the same authority.

Repair an import that has already reported success

Precommit prevention is cleaner than postcommit repair, but broad-IGNORE paths often come to attention because a batch has already been called successful.

The first response should be preservation, not invention.

Retain the original ordered input set, the exact committed database rows, the runtime identity, and any acknowledgement evidence already released. Do not manufacture a missing payload merely to make reconciliation pass.

Then derive a row-by-row comparison.

For every submitted request_key, determine whether a row exists. When one exists, compare the fields required by the replay contract. When none exists, determine whether the original input was invalid or was lost through another admission condition.

For the synthetic cases:

key-c absent + original payload NULL
    -> REJECT_INPUT

key-d absent + original external_ref ref-a already owned
    -> REJECT_INPUT / ownership resolution

key-a present as alpha + submitted CHANGED
    -> HOLD_PAYLOAD_CONFLICT

A postcommit repair should not automatically replay all missing rows. The original failure may be a source-data defect, and replaying unchanged invalid input simply reproduces the failure once broad suppression is removed.

The practical correction sequence is:

1. Freeze further acknowledgement for the affected batch or partition.

2. Preserve the exact source inputs and committed database state.

3. Reconcile every input identity against actual stored tuples.

4. Classify missing required values and unrelated uniqueness collisions as source-data defects.

5. Classify same-key/different-payload rows as ownership conflicts.

6. Obtain corrected authoritative data from the appropriate source owner.

7. Run the corrected write in a newly owned transaction using the narrow conflict target plus equality gate.

8. Commit only after the complete ledger is acceptable.

9. Reopen independently and verify committed rows.

10. Publish only the dispositions supported by that committed evidence.

Do not mix this procedure with concurrency retry logic. Whole-transaction recovery after BUSY_SNAPSHOT concerns a stale WAL snapshot and transaction recomputation after another writer commits; this fixture deliberately has one writer and no WAL-snapshot race.

Separate precommit rollback from postcommit repair

A rollback can abandon work only while the transaction is still active.

Once the original transaction has successfully committed, a later:

ROLLBACK;

cannot travel backward and cancel that completed commit. Any compensation, corrected insertion, or reconciliation after commit is a new database operation with a new owned transaction.

That distinction is operationally important because database repair may not be the only concern. If the old importer already sent HTTP success responses, advanced a source cursor, emitted an event, or acknowledged messages, those external effects are outside SQLite’s transaction boundary. This laboratory performs none of them.

When external effects are uncertain, the appropriate action is a hold and reconciliation, not an invented assertion that database rollback restored the whole distributed workflow.

Similarly, this article makes no durability claim about power loss after acknowledgement. The fixture verifies ordinary committed visibility through a fresh read-only SQLite connection. Backup, filesystem durability, distributed delivery, and exactly-once processing require separate contracts.

Review the evidence and assign operational ownership

A release decision should translate the laboratory into explicit actions rather than ending with “tests pass.”

The decision matrix is:

Operational ownership should be similarly explicit.

The schema owner owns the meaning of UNIQUE, NOT NULL, and foreign-key constraints. The source-data owner owns malformed required values and conflicting external identities. The write-path owner owns replacing broad conflict suppression with the named target and implementing the equality gate. The transaction owner owns the BEGIN through COMMIT or ROLLBACK boundary. The acknowledgement owner ensures no external success is published before the database disposition is accepted. The release reviewer verifies that runtime, case ledger, predecision state, final reopened state, and simulated acknowledgements are mutually consistent.

Requalification is required when the schema changes in a way that affects admission, when the replay-equality contract changes, when the write SQL changes, or when a runtime change is material enough that the owner’s release policy requires the matrix to be rerun. The supplied preparation record is evidence for its recorded environment, not a perpetual guarantee across future implementations.

The scope remains intentionally finite: no REPLACE, DO UPDATE, RETURNING, executemany, generated-ID reconstruction, lastrowid, triggers, CHECK matrix, deferred foreign keys, concurrent writers, WAL retry, backup, Unicode normalization, ORMs, or performance benchmarking.

Build stronger SQL write reviews through backend practice

The general backend discipline here is not “use UPSERT everywhere.” It is to define what a caller is allowed to consider successful, choose SQL whose failure surface matches that contract, retain per-input evidence, and defer acknowledgement until transaction and committed-state checks agree.

That practice fits directly with the database and testing foundations listed for Refonte Learning’s Backend Development program. The published curriculum describes a three-month program at 10–12 hours per week and includes Node.js and Express, Database Management with MongoDB & SQL, RESTful APIs and Microservices, Testing & Debugging Backend Services, Docker/cloud deployment, and a capstone project. The page also states an admission requirement of working toward a bachelor’s or higher-level degree and describes Training and Internship certificates on successful completion. It does not establish that SQLite, Python sqlite3, or this particular conflict-admission laboratory is part of the syllabus, so no such claim is made here.

For the import under review, the operational next step is concrete: remove broad INSERT OR IGNORE, qualify ON CONFLICT(request_key) DO NOTHING, retain the exact stored-payload comparison, keep the transaction explicitly owned, and do not acknowledge the batch until every input disposition and fresh committed readback agree.