Your SQLite Backup Opens. Are the Committed Rows There?

A backup can open cleanly, return ok from PRAGMA integrity_check, and still be the wrong recovery point.

That distinction is especially easy to expose with SQLite in write-ahead logging mode. In WAL mode, a transaction can commit by appending its commit record to the WAL while the corresponding pages have not yet been checkpointed into the main database file. SQLite explicitly documents that separation between commit and checkpoint. It also says that the WAL is part of the database's persistent state while it is in use. A copy of only the main file can therefore omit already committed application state. SQLite's WAL documentation describes both behaviors.

The acceptance question is consequently narrower than "does this SQLite file open?" It is:

Does this artifact restore the committed application state that the recovery contract required it to preserve?

This playbook answers that question with a falsification fixture: one disposable local database, an independent expected-state ledger, three candidate artifacts, and fresh-process restore verification. The negative candidate intentionally copies only the main database while a known commit remains in WAL. The controls use SQLite's Online Backup API and VACUUM INTO.

This is an authorized laboratory procedure only. It uses synthetic rows, isolated directories, Python 3.14, explicit integer primary keys, and a pinned SQLite 3.51.3 baseline. It does not copy production files, use a network filesystem, or experiment on a running service.

Define the recovery state before you copy anything

Backup acceptance starts with a recovery contract, not with a copy command.

For an application-owned database, the application owner must state what transaction boundary matters. A filename timestamp is not sufficient because it says when an artifact was named or written, not which application transactions it contains. A hash proves identity of bytes, not correctness of those bytes. integrity_check proves a different property again.

The contract for this fixture is deliberately inspectable:

This is intentionally narrower than broader database backup and availability foundations. Here, success means qualification of one artifact against one declared SQLite state, not a survey of backup schedules, availability architectures, or disaster-recovery products.

The scope boundary matters. SQLite documents that transactions involving multiple attached databases are atomic per database but not necessarily atomic across all attached databases as a set when WAL is involved. This laboratory therefore has no attached databases and makes no claim about files, message queues, object storage, caches, or remote side effects that may participate in a real application transaction.

A valid SQLite backup is not automatically a valid application recovery point. Those are two separate predicates:

SQLite artifact is internally usable
AND
restored application state satisfies the declared recovery contract

Only the conjunction is an acceptance result.

The expected state must also be independent of the candidate under test. Do not create the oracle by restoring the backup and then exporting what it contains. That would make the backup prove itself.

Before touching the source database, create this small ledger:

{
  "database_identity": "refonte-learning-sqlite-wal-fixture-2026-09-23",
  "schema_version": 7,
  "commit_marker": "T1-COMMITTED",
  "jobs": [
    {"id": 1001, "status": "done",    "payload": "alpha-v1"},
    {"id": 1002, "status": "running", "payload": "beta-v2"},
    {"id": 1003, "status": "done",    "payload": "gamma-v1"},
    {"id": 1004, "status": "queued",  "payload": "delta-v1"},
    {"id": 1005, "status": "done",    "payload": "epsilon-v1"}
  ]
}

Store it separately from the database, make it read-only for the experiment, and record its digest. This is the fixture's expected-state ledger, not data discovered from a backup.

Pin the SQLite runtime and the filesystem boundary

The client version and SQLite engine version are different facts.

Python's documentation defines sqlite3.sqlite_version as the version of the runtime SQLite library used by the sqlite3 module. The Python interpreter version therefore does not establish the SQLite engine underneath it. The Python 3.14 documentation also exposes Connection.backup() as the wrapper around SQLite's backup facility. Record the runtime explicitly rather than inferring it from python --version.

This playbook pins SQLite 3.51.3 exactly for the laboratory. That is a controlled baseline, not a claim that 3.51.3 is the latest SQLite release.

The reason for requiring at least this patch level is current safety context: SQLite documents a WAL-reset race affecting versions through 3.51.2 and states that it was fixed in 3.51.3, released March 13, 2026, with selected backports. The negative case below does not reproduce that race. Omitting a WAL-resident committed transaction from a main-file-only copy is normal WAL behavior and must not be misdiagnosed as the WAL-reset bug.

Record the engine actually linked to Python

Run this before creating the fixture:

python3.14 - <<'PY'
import json
import platform
import sqlite3
import sys

con = sqlite3.connect(":memory:")
try:
    manifest = {
        "python_version": sys.version,
        "python_executable": sys.executable,
        "platform": platform.platform(),
        "sqlite3_module": sqlite3.__file__,
        "sqlite_runtime": sqlite3.sqlite_version,
        "sqlite_sql_version":
            con.execute("SELECT sqlite_version()").fetchone()[0],
        "sqlite_source_id":
            con.execute("SELECT sqlite_source_id()").fetchone()[0],
        "compile_options": [
            row[0] for row in con.execute("PRAGMA compile_options")
        ],
    }
    print(json.dumps(manifest, indent=2))
finally:
    con.close()
PY

Stop if either reported SQLite version is not exactly 3.51.3. Record sqlite_source_id() and the compile options as build evidence; do not invent a source ID in documentation because downstream builds can differ. Preserve the package or container provenance used to obtain this interpreter as separate deployment evidence.

For a Linux laboratory, record the filesystem containing the lab root:

export LAB_ROOT="$PWD/sqlite-wal-acceptance-lab"

mkdir -p "$LAB_ROOT"
chmod 700 "$LAB_ROOT"
touch "$LAB_ROOT/.lab-owned"

findmnt -T "$LAB_ROOT" -o TARGET,FSTYPE,SOURCE,OPTIONS
stat -c '%A %U:%G %n' "$LAB_ROOT"

Use an ordinary local filesystem whose SQLite locking semantics are understood. Do not run this WAL experiment on NFS or another shared network filesystem. SQLite's WAL documentation says WAL requires processes to share the WAL index on the same host, and SQLite's corruption guidance separately warns about filesystems whose locking does not behave as required.

Create only dedicated test paths:

mkdir -p \
  "$LAB_ROOT/ledger" \
  "$LAB_ROOT/source" \
  "$LAB_ROOT/artifacts" \
  "$LAB_ROOT/controls" \
  "$LAB_ROOT/restores" \
  "$LAB_ROOT/quarantine" \
  "$LAB_ROOT/state" \
  "$LAB_ROOT/environment"

chmod 700 "$LAB_ROOT"/*

Then write the ledger shown earlier to:

sqlite-wal-acceptance-lab/ledger/expected.json

Make it immutable for the run:

chmod 0444 "$LAB_ROOT/ledger/expected.json"
sha256sum "$LAB_ROOT/ledger/expected.json" \
  > "$LAB_ROOT/ledger/expected.sha256"

The hash establishes which oracle was used. It does not establish that the oracle is correct; the application owner does that.

Execution-status limitation: the authoring environment available for this commissioned article did not provide Python 3.14 linked to SQLite 3.51.3, so the required publication baseline was not executed here. The result table later in the article is therefore labelled expected, not observed. The fixture is written to fail closed when the required runtime is absent.

Build an independent committed-state fixture and hold the commit in WAL

The fixture uses one source table:

CREATE TABLE job_ledger (
    id      INTEGER PRIMARY KEY,
    status  TEXT NOT NULL,
    payload TEXT NOT NULL
);

and one metadata table:

CREATE TABLE app_meta (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL UNIQUE,
    value TEXT NOT NULL
);

Explicit INTEGER PRIMARY KEY values matter for this comparison. SQLite notes that VACUUM can change rowids for tables that do not declare an explicit INTEGER PRIMARY KEY; this laboratory wants stable application keys independent of physical rebuilding. The VACUUM documentation describes that qualification.

The baseline consists of three jobs:

1001  done    alpha-v1
1002  queued  beta-v1
1003  done    gamma-v1

The producer commits those rows, checkpoints that baseline into the main file, and only then creates the transaction that matters:

UPDATE 1002 -> running / beta-v2
INSERT 1004 -> queued / delta-v1
INSERT 1005 -> done / epsilon-v1
marker      -> T1-COMMITTED

All four logical changes commit in one transaction.

The crucial property is that this second commit remains in the WAL while the source connection stays open. SQLite documents that a WAL commit is completed by appending a commit record to the WAL; the corresponding database pages need not already have been transferred into the main database. Transferring those pages is the separate checkpoint operation.

Separate the commit barrier from the checkpoint barrier

The deterministic timeline is:

Do not close the producer before the negative file copy. SQLite normally checkpoints and cleans up the WAL when the last connection closes. Closing too early can therefore turn an intended negative test into a false positive: the main file may suddenly contain the state that was supposed to remain WAL-resident.

The following lab.py implements the barrier and captures the three artifacts. It intentionally fails unless the client is Python 3.14 and the runtime SQLite version is exactly 3.51.3.

from future import annotations

import argparse
import hashlib
import json
import os
import shutil
import sqlite3
import subprocess
import sys
import time
from pathlib import Path

REQUIRED_SQLITE = "3.51.3"
DB_ID = "refonte-learning-sqlite-wal-fixture-2026-09-23"
SCHEMA_VERSION = 7
MARKER = "T1-COMMITTED"


def runtime_manifest() -> dict:
    con = sqlite3.connect(":memory:")
    try:
        return {
            "python": sys.version,
            "python_executable": sys.executable,
            "sqlite_runtime": sqlite3.sqlite_version,
            "sqlite_sql_version":
                con.execute("SELECT sqlite_version()").fetchone()[0],
            "sqlite_source_id":
                con.execute("SELECT sqlite_source_id()").fetchone()[0],
            "compile_options":
                [r[0] for r in con.execute("PRAGMA compile_options")],
        }
    finally:
        con.close()


def guard_runtime() -> dict:
    manifest = runtime_manifest()

    if sys.version_info[:2] != (3, 14):
        raise SystemExit(
            f"Need Python 3.14.x; got {sys.version.split()[0]}"
        )

    if (
        manifest["sqlite_runtime"] != REQUIRED_SQLITE
        or manifest["sqlite_sql_version"] != REQUIRED_SQLITE
    ):
        raise SystemExit(
            f"Need SQLite {REQUIRED_SQLITE}; "
            f"got {manifest['sqlite_runtime']} / "
            f"{manifest['sqlite_sql_version']}"
        )

    return manifest


def sha256(path: Path) -> str:
    h = hashlib.sha256()
    with path.open("rb") as fh:
        for block in iter(lambda: fh.read(1024 1024), b""):
            h.update(block)
    return h.hexdigest()


def producer(root: Path) -> None:
    guard_runtime()

    db = root / "source" / "app.db"
    ready = root / "state" / "COMMIT_READY.json"
    release = root / "state" / "RELEASE"

    con = sqlite3.connect(db)
    try:
        mode = con.execute("PRAGMA journal_mode=WAL").fetchone()[0]
        if mode.lower() != "wal":
            raise RuntimeError(f"WAL unavailable: {mode}")

        con.execute("PRAGMA synchronous=FULL")
        con.execute("PRAGMA foreign_keys=ON")

        # Laboratory-only checkpoint control.
        con.execute("PRAGMA wal_autocheckpoint=0")

        con.executescript("""
            CREATE TABLE app_meta(
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL UNIQUE,
                value TEXT NOT NULL
            );

            CREATE TABLE job_ledger(
                id INTEGER PRIMARY KEY,
                status TEXT NOT NULL,
                payload TEXT NOT NULL
            );
        """)

        con.execute("PRAGMA user_version=7")

        con.executemany(
            "INSERT INTO app_meta VALUES(?,?,?)",
            [
                (1, "database_identity", DB_ID),
                (2, "schema_version", str(SCHEMA_VERSION)),
                (3, "commit_marker", "BASELINE-CHECKPOINTED"),
            ],
        )

        con.executemany(
            "INSERT INTO job_ledger VALUES(?,?,?)",
            [
                (1001, "done", "alpha-v1"),
                (1002, "queued", "beta-v1"),
                (1003, "done", "gamma-v1"),
            ],
        )
        con.commit()

        checkpoint = con.execute(
            "PRAGMA wal_checkpoint(TRUNCATE)"
        ).fetchone()

        if checkpoint[0] != 0:
            raise RuntimeError(
                f"Baseline checkpoint did not complete: {checkpoint}"
            )

        # Sentinel transaction.
        con.execute("BEGIN IMMEDIATE")
        con.execute("""
            UPDATE job_ledger
               SET status='running', payload='beta-v2'
             WHERE id=1002
        """)
        con.executemany(
            "INSERT INTO job_ledger VALUES(?,?,?)",
            [
                (1004, "queued", "delta-v1"),
                (1005, "done", "epsilon-v1"),
            ],
        )
        con.execute(
            "UPDATE app_meta SET value=? WHERE name='commit_marker'",
            (MARKER,),
        )
        con.commit()

        wal = Path(str(db) + "-wal")
        if con.in_transaction:
            raise RuntimeError("Sentinel transaction still open")
        if not wal.exists() or wal.stat().st_size == 0:
            raise RuntimeError(
                "Expected committed WAL frames are not present"
            )

        marker = con.execute("""
            SELECT value FROM app_meta WHERE name='commit_marker'
        """).fetchone()[0]

        ready.write_text(json.dumps({
            "producer_pid": os.getpid(),
            "baseline_checkpoint": checkpoint,
            "wal_bytes": wal.stat().st_size,
            "marker_visible_to_source": marker,
        }, indent=2) + "\n")

        deadline = time.monotonic() + 120  # synthetic lab budget
        while not release.exists():
            if time.monotonic() > deadline:
                raise TimeoutError("Controller release timed out")
            time.sleep(0.05)

    finally:
        con.close()


def online_backup(source: Path, target: Path) -> list[dict]:
    src = sqlite3.connect(source)
    dst = sqlite3.connect(target)
    progress: list[dict] = []

    try:
        if src.in_transaction:
            raise RuntimeError("Source has an outstanding transaction")

        def report(status: int, remaining: int, total: int) -> None:
            progress.append({
                "status": status,
                "remaining": remaining,
                "total": total,
            })

        src.backup(dst, pages=16, progress=report)
        return progress
    finally:
        dst.close()
        src.close()


def vacuum_into(source: Path, target: Path) -> None:
    src = sqlite3.connect(source)
    try:
        if src.in_transaction:
            raise RuntimeError("Source has an outstanding transaction")
        src.execute("VACUUM INTO ?", (str(target),))
    finally:
        src.close()


def run(root: Path) -> None:
    manifest = guard_runtime()
    root = root.resolve()

    if not (root / ".lab-owned").exists():
        raise SystemExit("Refusing: missing .lab-owned marker")

    for name in (
        "source", "artifacts", "controls", "state",
        "environment", "quarantine"
    ):
        (root / name).mkdir(exist_ok=True)

    outputs = [
        root / "source" / "app.db",
        root / "artifacts" / "main-only.db",
        root / "artifacts" / "online-backup.db",
        root / "artifacts" / "vacuum-into.db",
    ]
    for path in outputs:
        if path.exists():
            raise SystemExit(f"Refusing to overwrite {path}")

    (root / "environment" / "runtime.json").write_text(
        json.dumps(manifest, indent=2) + "\n"
    )

    process = subprocess.Popen([
        sys.executable,
        str(Path(__file__).resolve()),
        "producer",
        "--root",
        str(root),
    ])

    ready = root / "state" / "COMMIT_READY.json"
    release = root / "state" / "RELEASE"
    deadline = time.monotonic() + 10  # synthetic lab budget

    try:
        while not ready.exists():
            if process.poll() is not None:
                raise RuntimeError(
                    f"Producer exited with {process.returncode}"
                )
            if time.monotonic() > deadline:
                raise TimeoutError("Commit barrier timed out")
            time.sleep(0.05)

        source = root / "source" / "app.db"

        # Candidate A: intentionally incomplete anti-pattern.
        shutil.copy2(
            source,
            root / "artifacts" / "main-only.db"
        )

        # Candidate B: supported Online Backup API.
        progress = online_backup(
            source,
            root / "artifacts" / "online-backup.db"
        )

        # Candidate C: supported VACUUM INTO.
        vacuum_into(
            source,
            root / "artifacts" / "vacuum-into.db"
        )

        evidence = {
            "commit_barrier": json.loads(ready.read_text()),
            "online_backup_progress": progress,
            "sha256_diagnostics": {
                p.name: sha256(p)
                for p in (root / "artifacts").glob(".db")
            },
        }
        (root / "environment" / "capture.json").write_text(
            json.dumps(evidence, indent=2) + "\n"
        )

    finally:
        release.touch(exist_ok=True)
        process.wait(timeout=10)

    # Separate verifier negative control:
    # same keys/count/marker as a good artifact, wrong payload.
    altered = root / "controls" / "altered-value.db"
    shutil.copy2(
        root / "artifacts" / "online-backup.db",
        altered
    )
    con = sqlite3.connect(altered)
    try:
        con.execute("""
            UPDATE job_ledger
               SET payload='beta-TAMPERED'
             WHERE id=1002
        """)
        con.commit()
    finally:
        con.close()


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument("command", choices=("run", "producer"))
    parser.add_argument("--root", required=True, type=Path)
    args = parser.parse_args()

    if args.command == "producer":
        producer(args.root.resolve())
    else:
        run(args.root.resolve())


if name == "__main__":
    main()

The code deliberately controls automatic checkpointing only inside this disposable experiment. Disabling application checkpoint policy merely to make backups "work" is not a production recommendation.

Create three candidates, including one deliberately incomplete artifact

The experiment now has a fixed logical source state and no active writers. That is deliberate: the first comparison is about artifact semantics, not timing luck.

The main-file-only candidate is the negative case. It copies:

source/app.db

but not:

source/app.db-wal

while the sentinel commit remains in that WAL.

SQLite's WAL documentation says that the WAL file is part of persistent database state and warns that separating a database from its WAL can lose previously committed transactions or lead to corruption. SQLite's separate corruption guide likewise warns against careless copying of live database/journal combinations. This fixture uses the behavior only inside an owned laboratory to produce a stale artifact; it is not a live-service backup recipe. SQLite's corruption guidance should govern operational handling outside this fixture.

The intended outcome is narrower than "bad copies corrupt." Because the baseline was explicitly checkpointed before the sentinel commit, the copied main file should represent a perfectly intelligible old database image: keys 1001–1003, the old 1002 value, and the baseline marker.

That is the useful falsification case. A stale-but-structurally-valid database demonstrates why opening and integrity checks cannot answer the application recovery question.

If the main-only candidate contains T1-COMMITTED, diagnose the fixture rather than declaring the anti-pattern safe. Typical causes are that the producer closed early, the sentinel was checkpointed, the wrong file was copied, or a prior artifact was reused.

The Online Backup API candidate is the first control. Python 3.14 exposes:

source.backup(
    target,
    pages=...,
    progress=...,
    name="main",
    sleep=...
)

with a distinct target Connection. Python's sqlite3.Connection.backup documentation specifies the target connection, page parameter and progress callback. SQLite's native Online Backup API documentation describes the mechanism as copying database content into a destination snapshot and explains how concurrent source writes can cause backup work to restart.

In this deterministic run there are no concurrent writes, so there is no need to infer which interleaving "won." The logical source state is stable from the COMMIT_READY barrier through all candidate captures.

The callback remains diagnostic evidence only. A callback reaching zero remaining pages is not acceptance. Exceptions, destination identity, fresh-process validation and row reconciliation all still matter.

This distinction is important when integrating backup work into automated database operations: job completion should trigger restore qualification, not substitute for it.

The VACUUM INTO candidate is the second control. SQLite documents VACUUM INTO as creating a separate database containing the same logical content as the source, fully vacuumed. The destination must not already contain a database, and interruption can leave an incomplete output. SQLite also qualifies its synchronization guarantees according to synchronous, operating-system, filesystem and hardware behavior.

Do not expect byte equality between online-backup.db and vacuum-into.db. Acceptance is logical. VACUUM INTO rebuilds the destination, whereas backup mechanisms have different physical behavior. A byte mismatch between two logically equivalent SQLite files is not evidence that one is wrong.

Likewise, do not publish a claim that one method is "faster" from this five-row fixture. No meaningful performance benchmark is commissioned here.

At the end of capture, exactly three backup candidates belong to the recovery comparison:

artifacts/main-only.db
artifacts/online-backup.db
artifacts/vacuum-into.db

The separate:

controls/altered-value.db

is not a fourth backup candidate. It is a verifier falsification control. It deliberately retains the right row count, expected key set and expected marker while changing one payload. If the verifier accepts it, the verifier is inadequate.

Restore every candidate in a fresh process and reconcile logical content

Never qualify an artifact through the same live connection that created it.

Each artifact should first be copied into a unique restore directory, preserving the candidate itself. The verifier then opens only that restored copy, in read-only mode, and logs PRAGMA database_list so the evidence shows which pathname SQLite actually opened.

This boundary is part of application and database integration boundaries: recovery validation should not depend on an application default path, current working directory, connection cache, or fallback that silently reaches the source database.

A suitable verifier is:

from future import annotations

import argparse
import json
import shutil
import sqlite3
from pathlib import Path


def runtime_identity() -> dict:
    con = sqlite3.connect(":memory:")
    try:
        return {
            "sqlite_runtime": sqlite3.sqlite_version,
            "sqlite_source_id":
                con.execute("SELECT sqlite_source_id()").fetchone()[0],
            "compile_options":
                [r[0] for r in con.execute("PRAGMA compile_options")],
        }
    finally:
        con.close()


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument("--artifact", required=True, type=Path)
    parser.add_argument("--expected", required=True, type=Path)
    parser.add_argument("--restore-dir", required=True, type=Path)
    parser.add_argument("--source", required=True, type=Path)
    parser.add_argument(
        "--runtime-manifest", required=True, type=Path
    )
    args = parser.parse_args()

    artifact = args.artifact.resolve(strict=True)
    source = args.source.resolve(strict=True)

    if artifact == source:
        raise SystemExit(
            "HOLD: artifact resolves to the live source"
        )

    if args.restore_dir.exists():
        raise SystemExit(
            "HOLD: restore directory must be fresh"
        )

    args.restore_dir.mkdir(parents=True)
    restored = args.restore_dir / "restore.db"
    shutil.copy2(artifact, restored)

    expected = json.loads(args.expected.read_text())
    required_runtime = json.loads(
        args.runtime_manifest.read_text()
    )
    actual_runtime = runtime_identity()

    runtime_match = (
        actual_runtime["sqlite_runtime"]
            == required_runtime["sqlite_runtime"]
        and actual_runtime["sqlite_source_id"]
            == required_runtime["sqlite_source_id"]
        and actual_runtime["compile_options"]
            == required_runtime["compile_options"]
    )

    result = {
        "artifact": str(artifact),
        "source": str(source),
        "restored": str(restored),
        "runtime_match": runtime_match,
        "open_ok": False,
    }

    try:
        # mode=ro means a misspelled path cannot become a new database.
        uri = restored.resolve().as_uri() + "?mode=ro"
        con = sqlite3.connect(uri, uri=True)

        try:
            result["open_ok"] = True
            result["database_list"] = [
                list(r)
                for r in con.execute("PRAGMA database_list")
            ]

            result["integrity_check"] = [
                r[0]
                for r in con.execute("PRAGMA integrity_check")
            ]

            result["foreign_key_check"] = [
                list(r)
                for r in con.execute(
                    "PRAGMA foreign_key_check"
                )
            ]

            result["user_version"] = con.execute(
                "PRAGMA user_version"
            ).fetchone()[0]

            meta = dict(con.execute(
                "SELECT name,value FROM app_meta"
            ))

            observed = [
                {
                    "id": row[0],
                    "status": row[1],
                    "payload": row[2],
                }
                for row in con.execute("""
                    SELECT id,status,payload
                      FROM job_ledger
                     ORDER BY id
                """)
            ]

            expected_rows = {
                row["id"]: row
                for row in expected["jobs"]
            }
            observed_rows = {
                row["id"]: row
                for row in observed
            }

            expected_keys = set(expected_rows)
            observed_keys = set(observed_rows)

            result["database_identity"] = meta.get(
                "database_identity"
            )
            result["marker"] = meta.get("commit_marker")
            result["rows"] = observed

            result["missing_keys"] = sorted(
                expected_keys - observed_keys
            )
            result["unexpected_keys"] = sorted(
                observed_keys - expected_keys
            )

            result["changed_rows"] = [
                {
                    "id": key,
                    "expected": expected_rows[key],
                    "observed": observed_rows[key],
                }
                for key in sorted(
                    expected_keys & observed_keys
                )
                if expected_rows[key] != observed_rows[key]
            ]

            result["accepted"] = all([
                runtime_match,
                result["integrity_check"] == ["ok"],
                not result["foreign_key_check"],
                result["user_version"]
                    == expected["schema_version"],
                result["database_identity"]
                    == expected["database_identity"],
                meta.get("schema_version")
                    == str(expected["schema_version"]),
                result["marker"]
                    == expected["commit_marker"],
                not result["missing_keys"],
                not result["unexpected_keys"],
                not result["changed_rows"],
            ])

        finally:
            con.close()

    except Exception as exc:
        result["error"] = (
            f"{type(exc).__name__}: {exc}"
        )
        result["accepted"] = False

    print(json.dumps(result, indent=2))
    raise SystemExit(0 if result["accepted"] else 2)


if name == "__main__":
    main()

Run every candidate as a separate process:

python3.14 verify.py \
  --artifact "$LAB_ROOT/artifacts/main-only.db" \
  --expected "$LAB_ROOT/ledger/expected.json" \
  --restore-dir "$LAB_ROOT/restores/main-only" \
  --source "$LAB_ROOT/source/app.db" \
  --runtime-manifest "$LAB_ROOT/environment/runtime.json"

python3.14 verify.py \
  --artifact "$LAB_ROOT/artifacts/online-backup.db" \
  --expected "$LAB_ROOT/ledger/expected.json" \
  --restore-dir "$LAB_ROOT/restores/online-backup" \
  --source "$LAB_ROOT/source/app.db" \
  --runtime-manifest "$LAB_ROOT/environment/runtime.json"

python3.14 verify.py \
  --artifact "$LAB_ROOT/artifacts/vacuum-into.db" \
  --expected "$LAB_ROOT/ledger/expected.json" \
  --restore-dir "$LAB_ROOT/restores/vacuum-into" \
  --source "$LAB_ROOT/source/app.db" \
  --runtime-manifest "$LAB_ROOT/environment/runtime.json"

Then run the altered-value control in another fresh directory.

Also deliberately request a nonexistent artifact. Because the verifier resolves the artifact strictly and ultimately opens restored databases with mode=ro, a missing path must fail instead of creating an empty SQLite file.

SQLite documents PRAGMA integrity_check as a low-level formatting and consistency test. It checks matters such as malformed records, missing pages, freelist integrity and index inconsistencies. It explicitly does not detect foreign-key violations; PRAGMA foreign_key_check serves that separate purpose. Neither command knows that job 1002 was required to contain beta-v2, that keys 1004 and 1005 had committed, or that T1-COMMITTED was the required application boundary. SQLite's PRAGMA documentation makes that scope clear.

That is why logical reconciliation is decisive.

Because the required Python 3.14/SQLite 3.51.3 environment was unavailable during preparation of this article, the following table is a reasoned expected result, not certified observed output:

The altered-value row is important. Its count is five. Its expected keys can all be present. Its marker can be correct. Yet its application content is wrong.

A single sentinel therefore cannot prove every transaction. A row count cannot prove identity. Matching sums cannot prove equal row sets. A marker cannot prove that unrelated rows were not lost or changed.

For this tiny fixture, the strongest practical oracle is cheap: compare every expected key and every relevant value in both directions.

Bound concurrent-writer claims and handle incomplete artifacts without inventing a cutoff

Do not mix the deterministic falsification experiment with a concurrency experiment.

The main comparison intentionally pauses writers because it asks a binary diagnostic question: given one stable committed state, which artifact creation procedures preserve it? Adding concurrent writes there would make a failed logical comparison ambiguous between method behavior, timing and fixture error.

A separate bounded writer test can evaluate live-backup behavior afterward.

SQLite's Online Backup API supports online databases and incremental copying. Its documentation explains that if another connection changes the source while an incremental backup is between steps, SQLite can detect the change and usually restart the backup work. A sufficiently busy source can therefore keep forcing restarts. Completion yields a consistent snapshot, but that does not justify inventing an exact application transaction cutoff from the wall-clock instant at which an operator invoked a command.

For the optional concurrent test, create a fresh copy of the laboratory and have one writer commit monotonically increasing transaction identities:

tx 2001
tx 2002
tx 2003
...

Record, from the harness rather than from filenames:

backup invocation event
each transaction commit event
backup completion event

Then validate two different properties.

Snapshot consistency asks whether the restored database is internally coherent. In a deliberately simple append-only transaction ledger, that means the restored transaction IDs should form a valid committed prefix rather than an impossible mixture.

Requested recovery-point satisfaction asks whether that coherent prefix reaches the transaction the operator required. A perfectly coherent snapshot ending at transaction 2041 is still unacceptable if the requested boundary was 2045.

That distinction prevents a common semantic mistake:

consistent snapshot != requested recovery point

For VACUUM INTO, SQLite explicitly describes the output as a consistent snapshot of the original database. It also warns that an interrupted VACUUM INTO may leave the destination incomplete or corrupt. Its post-completion persistence statements are conditional on synchronous being NORMAL or FULL and on the operating system, filesystem and hardware functioning correctly.

Do not collapse those qualifications into "VACUUM INTO guarantees durability."

This is also where managed-cloud recovery as a different operating model should remain separate from the embedded-database problem. A managed service may expose named recovery points, replicated logs or service-level restore workflows that do not map directly to the acceptance evidence used here.

When creation fails, preserve evidence before retrying.

An interrupted destination should move to quarantine under a new name such as:

quarantine/online-backup.partial.db

Record:

creation method
runtime manifest
source identity
start/completion state
exception
artifact hash if readable
verification result

Do not overwrite the last accepted artifact with a retry. Generate a new candidate, validate it independently, and change the backup catalog pointer only after acceptance.

Most importantly, never "fix" a troublesome WAL backup by deleting the live -wal file. SQLite says the WAL can contain persistent committed state. Removing or separating it outside SQLite's recovery rules can lose transactions or damage the database.

If the original source is gone and the only surviving candidate restores the baseline marker instead of T1-COMMITTED, say exactly that:

Recovered artifact is structurally valid.
Required committed state T1 is absent.
Recovery-point requirement is not met.
Decision: HOLD / data-loss assessment required.

Do not manufacture a missing transaction from assumptions.

Use an acceptance matrix that can say hold and assign ownership

Backup systems fail operationally when every non-success condition is forced into a vague red/green status.

The acceptance vocabulary here has four states.

Accept means the artifact was created by an approved method, restored independently under the required runtime, passed structural checks, and reconciled exactly to the declared logical ledger.

Reject means evidence establishes that the candidate does not satisfy the recovery contract.

Hold means the evidence is insufficient or internally inconsistent; the operator must not promote or discard the artifact as though its status were known.

Recreate means the candidate itself is not worth further qualification while the authoritative source remains available; create a new artifact through an approved method.

A practical decision matrix is:

This is where a recovery playbook benefits from DBA communication during recovery: "the file opens" and "the required transaction is present" are different statements, and an incident record should make that difference obvious to application owners.

Retain a known-good artifact during backup-method changes. Run old and new methods side by side until the new method has repeatedly produced independently accepted restores. Do not repoint the catalog merely because the replacement job completed faster or generated a smaller file.

Artifact hashes remain useful for chain-of-custody and immutability evidence:

SHA-256(candidate)
SHA-256(expected-state ledger)
runtime source ID
verification report

But none replaces reconciliation.

Likewise, any local recovery-time measurement belongs to that particular test run. For example, a harness may record monotonic start and finish times for copying and validation, but a five-row fixture cannot establish a production recovery-time objective. Do not convert a local timing into a service guarantee.

The operational record for each drill can stay compact:

Choose a recurring restore-drill interval locally according to risk and change rate; no universal interval is implied by this fixture. Trigger additional validation when any material dependency changes: SQLite runtime, Python backup client, source filesystem, schema, journaling configuration, packaging, container image or artifact-creation procedure.

Store the acceptance report with the artifact identity and retention metadata so that a future operator does not have to infer why a file was promoted.

Safe cleanup should also be scoped to the owned laboratory:

test -f "$LAB_ROOT/.lab-owned" &&
  rm -rf -- "$LAB_ROOT"

Do not reuse that pattern against a production path. The ownership marker exists specifically to prevent the article's cleanup command from becoming a generic deletion recipe.

Connect restore evidence to database administration practice

The central lesson is not that SQLite WAL mode is unsafe. WAL is doing exactly the important thing that makes the falsification possible: a transaction can be committed and visible even though its pages have not yet been checkpointed into the main file. The mistake is treating one physical file as though it necessarily represented the application's current committed database state. SQLite documents the separation explicitly.

Nor is the lesson that integrity_check is weak. It answers a valuable structural question. It simply cannot answer an application-semantic question that was never encoded into the check. foreign_key_check adds another useful layer, but it likewise cannot know which business rows a recovery operator promised to retain.

The robust workflow is therefore:

declare expected committed state
        ↓
create candidate
        ↓
restore candidate in isolation
        ↓
identify the file actually opened
        ↓
check database structure
        ↓
check relational constraints
        ↓
reconcile required keys and values
        ↓
accept / reject / hold / recreate

That is a database-administration habit rather than a SQLite trick.

Refonte Learning's published Database Administrator Essentials programme lists database design, SQL optimization, database backup and recovery, disaster-recovery strategies, monitoring, security and data migration among the competencies it intends to develop; its page currently describes a three-month programme with 12–14 hours per week and basic programming knowledge recommended. The programme page presents practical projects, guidance and potential internship opportunities; those are the provider's published offer, not evidence that this particular SQLite WAL laboratory is part of its curriculum.

The specialist practice to carry forward is simpler: test the state that comes back, not merely whether the backup command finished or the restored database opens.