Conceptual recovery view. The lab stores accepted input and row results and derives the outcome; recovery-owner tracking is not implemented. Catalog data is fictional; the code and process-exit tests ran locally.


When a customer asks whether to upload a failed import again, the worker's health check cannot tell support what already changed. A retry count cannot answer that question either. The useful evidence is the accepted input, the committed work and the outcome derived from those records.


Here is a runnable catalog importer that makes those questions inspectable. The supplier and product data are fictional. The local implementation and failure tests were executed with Python 3.13.3 and SQLite 3.49.2; they are not a customer deployment or a distributed-system benchmark.


The lab uses one supplier's catalog in one disposable SQLite file. It deliberately keeps the catalog update and the completed-row result in the same database. There is no queue or external inventory API.

Run an import, then deliver it again

Save the three files in the appendix together: importer.py, test_importer.py and sample.json. Python's standard library supplies all dependencies. Use a new disposable database, never a production or shared one. From that directory, run:

python3 -m unittest -v test_importer python3 -c 'import sys, sqlite3; print(sys.version); print(sqlite3.sqlite_version)' 


The captured implementation suite passed all four tests. The version command lets you record your own environment rather than assume it matches the lab. For a manual delivery and replay, create a fresh directory:

lab_dir=$(mktemp -d) python3 importer.py --db "$lab_dir/catalog.sqlite" --id batch-7 --input sample.json python3 importer.py --db "$lab_dir/catalog.sqlite" --id batch-7 --input sample.json 


Each delivery should print:

{"import_id": "batch-7", "completed": 2, "total": 2, "outcome": "complete", "ack": true} 

This output was observed for the first delivery in the captured run. Identical replay also completed; test-only audit triggers recorded zero additional catalog writes. Merely seeing two catalog rows would not prove that: an upsert could rewrite the same rows without increasing their count.


The ack field is a printed illustration of where acknowledgment would occur. It does not acknowledge a message to a real broker.

1. Record exactly what was accepted

The imports table stores the caller's import ID, the original file bytes, their SHA-256 hash and the expected row count. Acceptance commits separately before processing begins, so an accepted import can exist with no completed rows.


For the supplied file, the captured record is:

import_id: batch-7 payload_sha256: ce9147caac49d6b749fdbfa4fe343823f094b8e100c6225347f94154263a47a2 row_count: 2 


The ID belongs to one exact delivery payload. Reuse it for a redelivery of those bytes. A corrected file needs a distinct ID; changing even whitespace under the existing ID is rejected by the code. The altered-input test changes a price from 1299 to 1300 cents, confirms a nonzero exit containing different bytes, and checks that prior records are unchanged.


Validation happens before acceptance. The payload must be a nonempty array; each row must have exactly sku, name and price_cents. Names and SKUs must contain non-whitespace text, prices must be nonnegative integers, and the file cannot contain duplicate SKUs. This makes row position a usable work identity within this immutable file. It is not a general identity for a product across future imports.


The stored bytes are immutable by application contract. This code does not prevent an administrator from directly altering the database.

2. Commit the catalog change and its result together

Each row is identified by (import_id, row_number), enforced as the primary key of completed_rows. The worker starts BEGIN IMMEDIATE before checking whether that row is complete.


If a result already exists, it skips the catalog write. Otherwise, it upserts the product and inserts the completed-row record, including the resulting SKU, name and price, within that transaction. Commit makes both changes visible together. The exception handler rolls back before propagating an ordinary failure; the full code below preserves that handler and the replay check.


This implementation uses SQLite's single-writer behavior for coordination. SQLite's transaction documentation explains that BEGIN IMMEDIATE starts a write transaction immediately and can fail if another writer is active. The connection waits up to 15 seconds for a lock. That is bounded waiting, not unlimited recovery or a throughput claim.


In the duplicate-worker test, two fresh worker processes target the same import and local database. A third connection holds a write transaction while the test checks that both workers are still running. After releasing it, both workers complete. Audit triggers count exactly two catalog writes for the two input rows.


That observation is narrower than claiming the workers can run all their writes in parallel: SQLite serializes them. Longer contention can still fail.

3. Derive the outcome from committed records

The importer counts completed rows and compares that count with the accepted row count. It prints complete only when those counts agree. There is no independent success flag that can get ahead of row processing.


The captured catalog contains SUP-101, Steel bottle, at 1299 cents and SUP-202, Canvas bag, at 850 cents. Each has a completed-row result with the corresponding values. Support can inspect the records without relying on a process that may have disappeared:

python3 - "$lab_dir/catalog.sqlite" <<'PY' import json import sqlite3 import sys with sqlite3.connect(sys.argv[1]) as db: for table in ('imports', 'catalog', 'completed_rows'): sql = { 'imports': 'SELECT import_id, payload_sha256, row_count FROM imports', 'catalog': 'SELECT sku, name, price_cents FROM catalog ORDER BY sku', 'completed_rows': 'SELECT import_id, row_number, sku, result FROM completed_rows ORDER BY row_number', }[table] print(table, json.dumps(db.execute(sql).fetchall())) PY 


The completed result describes what that import committed. A later import with a different ID can update the same SKU, so an older result is not proof of the catalog's current value. This lab does not solve ordering between different imports or prevent an older delivery under a different ID from overwriting newer data.

Stop the process on either side of commit

The tests launch real Python subprocesses and use os._exit at two explicit failure points. Each retry runs in a fresh process against the same temporary database. These are abrupt process exits, not operating-system crashes or power-loss tests.


Before the first row commits, the process exits with code 71. The accepted import remains, but the catalog and completed-row tables both contain zero rows. A fresh retry completes both rows. Support's next action follows from that saved state: resume the accepted import using the same ID and bytes.


After the first row commits, the process exits with code 72 before printing the acknowledgment. There is no stdout, but one catalog row and one completed result survive. The retry reaches two completed rows, and an audit trigger observes zero updates to the already committed row. Missing acknowledgment therefore does not mean no work committed.


The suite also checks identical replay, altered-file rejection, and competing workers. Its captured summary is:

competing workers: both ack complete; 2 catalog writes, 2 completed rows crash after commit: exit=72, before restart catalog=1 completed=1 ack absent; restart=2/2, committed row rewrites=0 crash before commit: exit=71, before restart catalog=0 completed=0; restart=2/2 normal/replay/altered: 2 completed, replay catalog writes=0, mismatch rejected 


Run the supplied suite to exercise these cases safely; it creates isolated temporary databases. The crash flags are test hooks, not operating instructions for valuable data.

Where this recovery argument stops

The catalog write and result share one local transaction. A remote inventory update cannot join it. If a remote request succeeds and its response is lost, a missing local result does not establish whether repeating that request is safe. That boundary needs the provider's idempotency contract and a reconciliation process, with an owner for unresolved outcomes.


For broader background, Anjana Fernando's Practical Transaction Handling in Microservice Architecture, published November 30, 2020, discusses the move from local to distributed transactions. The local tests here do not validate that broader architecture.


This lab has not tested SQS, PostgreSQL, multiple hosts, network filesystems or machine power failure. synchronous=FULL requests SQLite's commit synchronization; it is not protection against every storage failure. Production ingestion, access controls, migrations and monitoring are omitted. No end-to-end exactly-once guarantee follows from these tests.


A recovery screen built on these records could show the accepted identity, which rows committed, and which remain. A conflicting payload should remain a conflict. An uncertain remote action should remain unresolved. Neither should become a green success badge merely because another worker started.


My guide to background jobs that survive a worker restart develops the broader restart-design questions. The complete local implementation and its tests follow here so this example can be inspected without an external repository.

Appendix: complete runnable files

Save each block under the stated filename. Keep sample.json byte-for-byte if comparing its hash with the captured run, including its final newline. The importer uses the same SQL schema shown in its SCHEMA constant; no separate setup command is needed.

[ {"sku":"SUP-101","name":"Steel bottle","price_cents":1299}, {"sku":"SUP-202","name":"Canvas bag","price_cents":850} ] 

importer.py

#!/usr/bin/env python3 """Local SQLite illustration; a delivery is acknowledged only after all rows finish.""" import argparse import hashlib import json import os import sqlite3 from pathlib import Path SCHEMA = ''' CREATE TABLE IF NOT EXISTS imports ( import_id TEXT PRIMARY KEY, payload_sha256 TEXT NOT NULL, payload BLOB NOT NULL, row_count INTEGER NOT NULL CHECK(row_count > 0) ); CREATE TABLE IF NOT EXISTS catalog ( sku TEXT PRIMARY KEY, name TEXT NOT NULL, price_cents INTEGER NOT NULL CHECK(price_cents >= 0) ); CREATE TABLE IF NOT EXISTS completed_rows ( import_id TEXT NOT NULL REFERENCES imports(import_id), row_number INTEGER NOT NULL, sku TEXT NOT NULL, result TEXT NOT NULL, PRIMARY KEY(import_id, row_number) ); ''' def connect(path): db = sqlite3.connect(path, timeout=15, isolation_level=None) db.execute('PRAGMA foreign_keys=ON') db.execute('PRAGMA synchronous=FULL') db.executescript(SCHEMA) return db def load_payload(path): raw = Path(path).read_bytes() rows = json.loads(raw) if not isinstance(rows, list) or not rows: raise ValueError('payload must be a non-empty array') seen = set() for row in rows: if (not isinstance(row, dict) or set(row) != {'sku', 'name', 'price_cents'} or not isinstance(row['sku'], str) or not row['sku'].strip() or not isinstance(row['name'], str) or not row['name'].strip() or type(row['price_cents']) is not int or row['price_cents'] < 0 or row['sku'] in seen): raise ValueError('invalid row or duplicate SKU') seen.add(row['sku']) return raw, rows def deliver(db, import_id, raw, rows, crash=None): if not import_id.strip(): raise ValueError('empty import ID') digest = hashlib.sha256(raw).hexdigest() db.execute('BEGIN IMMEDIATE') try: existing = db.execute('SELECT payload_sha256, payload FROM imports WHERE import_id=?', (import_id,)).fetchone() if existing and (existing[0] != digest or existing[1] != raw): raise ValueError('import ID already belongs to different bytes') if not existing: db.execute('INSERT INTO imports VALUES (?, ?, ?, ?)', (import_id, digest, raw, len(rows))) db.commit() except BaseException: db.rollback() raise for number, row in enumerate(rows, 1): db.execute('BEGIN IMMEDIATE') try: completed = db.execute('SELECT 1 FROM completed_rows WHERE import_id=? AND row_number=?', (import_id, number)).fetchone() if not completed: db.execute('INSERT INTO catalog VALUES (?, ?, ?) ON CONFLICT(sku) DO UPDATE SET name=excluded.name, price_cents=excluded.price_cents', (row['sku'], row['name'], row['price_cents'])) result = json.dumps({'sku': row['sku'], 'name': row['name'], 'price_cents': row['price_cents']}, sort_keys=True) db.execute('INSERT INTO completed_rows VALUES (?, ?, ?, ?)', (import_id, number, row['sku'], result)) if crash == 'before-commit' and number == 1: os._exit(71) db.commit() if not completed and crash == 'after-commit' and number == 1: os._exit(72) except BaseException: db.rollback() raise total, done = db.execute('SELECT i.row_count, count(c.row_number) FROM imports i LEFT JOIN completed_rows c ON c.import_id=i.import_id WHERE i.import_id=? GROUP BY i.import_id', (import_id,)).fetchone() print(json.dumps({'import_id': import_id, 'completed': done, 'total': total, 'outcome': 'complete' if done == total else 'incomplete', 'ack': done == total})) def main(): p = argparse.ArgumentParser() p.add_argument('--db', required=True) p.add_argument('--id', required=True) p.add_argument('--input', required=True) p.add_argument('--crash', choices=['before-commit', 'after-commit']) args = p.parse_args() raw, rows = load_payload(args.input) with connect(args.db) as db: deliver(db, args.id, raw, rows, args.crash) if __name__ == '__main__': main() 

test_importer.py

import json import sqlite3 import subprocess import sys import tempfile import time import unittest from pathlib import Path HERE = Path(__file__).resolve().parent SCRIPT = HERE / 'importer.py' SAMPLE = HERE / 'sample.json' class DeliveryTests(unittest.TestCase): def setUp(self): self.temp = tempfile.TemporaryDirectory(prefix='catalog-demo-') self.addCleanup(self.temp.cleanup) self.root = Path(self.temp.name) self.db = self.root / 'test.sqlite' def command(self, source=SAMPLE, *extra): return [sys.executable, str(SCRIPT), '--db', str(self.db), '--id', 'batch-7', '--input', str(source), *extra] def run_job(self, *args): return subprocess.run(self.command(*args), text=True, capture_output=True, timeout=30) def rows(self): with sqlite3.connect(self.db) as c: imports = c.execute('SELECT import_id,payload_sha256,row_count FROM imports').fetchall() catalog = c.execute('SELECT sku,name,price_cents FROM catalog ORDER BY sku').fetchall() completed = c.execute('SELECT import_id,row_number,sku,result FROM completed_rows ORDER BY row_number').fetchall() return imports, catalog, completed def assert_complete(self): imports, catalog, completed = self.rows() self.assertEqual(len(imports), 1) self.assertEqual(imports[0][2], 2) self.assertEqual(catalog, [('SUP-101','Steel bottle',1299),('SUP-202','Canvas bag',850)]) self.assertEqual([r[:3] for r in completed], [('batch-7',1,'SUP-101'),('batch-7',2,'SUP-202')]) for r in completed: self.assertEqual(json.loads(r[3])['sku'],r[2]) return imports, catalog, completed def test_normal_duplicate_and_altered_input(self): first = self.run_job(); self.assertEqual(first.returncode, 0, first.stderr) self.assertEqual(json.loads(first.stdout)['outcome'], 'complete') original = self.assert_complete() with sqlite3.connect(self.db) as c: c.executescript('CREATE TABLE writes(sku TEXT); CREATE TRIGGER audit_update AFTER UPDATE ON catalog BEGIN INSERT INTO writes VALUES (NEW.sku); END; CREATE TRIGGER audit_insert AFTER INSERT ON catalog BEGIN INSERT INTO writes VALUES (NEW.sku); END;') repeat = self.run_job(); self.assertEqual(repeat.returncode, 0, repeat.stderr) self.assertEqual(self.rows(), original) with sqlite3.connect(self.db) as c: self.assertEqual(c.execute('SELECT count(*) FROM writes').fetchone()[0],0) altered = self.root / 'altered.json'; altered.write_text(SAMPLE.read_text().replace('1299','1300')) rejected = self.run_job(altered) self.assertNotEqual(rejected.returncode, 0) self.assertIn('different bytes', rejected.stderr) self.assertEqual(self.rows(), original) print('normal/replay/altered: 2 completed, replay catalog writes=0, mismatch rejected') def test_killed_before_commit(self): killed = self.run_job(SAMPLE, '--crash', 'before-commit') self.assertEqual(killed.returncode,71) imports, catalog, completed = self.rows() self.assertEqual((len(imports),catalog,completed),(1,[],[])) retry = self.run_job(); self.assertEqual(retry.returncode,0,retry.stderr) self.assert_complete() print('crash before commit: exit=71, before restart catalog=0 completed=0; restart=2/2') def test_killed_after_commit_before_ack(self): killed = self.run_job(SAMPLE, '--crash', 'after-commit') self.assertEqual(killed.returncode,72) self.assertEqual(killed.stdout,'') imports, catalog, completed = self.rows() self.assertEqual(len(imports),1); self.assertEqual(len(catalog),1); self.assertEqual(len(completed),1) with sqlite3.connect(self.db) as c: c.executescript('CREATE TABLE writes(sku TEXT); CREATE TRIGGER audit_update AFTER UPDATE ON catalog BEGIN INSERT INTO writes VALUES (NEW.sku); END;') retry = self.run_job(); self.assertEqual(retry.returncode,0,retry.stderr) self.assert_complete() with sqlite3.connect(self.db) as c:self.assertEqual(c.execute('SELECT count(*) FROM writes').fetchone()[0],0) print('crash after commit: exit=72, before restart catalog=1 completed=1 ack absent; restart=2/2, committed row rewrites=0') def test_competing_workers(self): # Seed schema and import before opening the gate, so the contention is on row transactions. import importer db=importer.connect(self.db) raw, rows=importer.load_payload(SAMPLE) db.execute('INSERT INTO imports VALUES (?, ?, ?, ?)', ('batch-7',__import__('hashlib').sha256(raw).hexdigest(),raw,len(rows))) db.executescript('CREATE TABLE writes(sku TEXT); CREATE TRIGGER audit_insert AFTER INSERT ON catalog BEGIN INSERT INTO writes VALUES (NEW.sku); END; CREATE TRIGGER audit_update AFTER UPDATE ON catalog BEGIN INSERT INTO writes VALUES (NEW.sku); END;') db.execute('BEGIN IMMEDIATE') p1=subprocess.Popen(self.command(),stdout=subprocess.PIPE,stderr=subprocess.PIPE,text=True) p2=subprocess.Popen(self.command(),stdout=subprocess.PIPE,stderr=subprocess.PIPE,text=True) time.sleep(0.3) self.assertIsNone(p1.poll()); self.assertIsNone(p2.poll()) db.commit();db.close() for p in (p1,p2): out,err=p.communicate(timeout=30); self.assertEqual(p.returncode,0,err);self.assertTrue(json.loads(out)['ack']) self.assert_complete() with sqlite3.connect(self.db) as c:self.assertEqual(c.execute('SELECT count(*) FROM writes').fetchone()[0],2) print('competing workers: both ack complete; 2 catalog writes, 2 completed rows') if __name__=='__main__':unittest.main(verbosity=2)