Reorg-Safe Event Projection: SQLite Fixtures and Expected States

Reorg-Safe Event Projection: SQLite Fixtures and Expected States version 1.0.0 is a synthetic reference dataset for inspecting how one event projection behaves after a failed transaction, a repeated inclusion and a replacement branch. Six scenarios supply explicit calls to a preserved SQLite implementation. A separate oracle lists the complete expected database state after each of 17 steps. The archive contains invented events, not chain observations or customer records. The Pharos Production blockchain software development practice packages a previously published executable example into inputs and expected states that another team can inspect or adapt. The source model appeared in Dmytro Nasyrov's article on idempotent blockchain event handlers. The archived source file and its seven-step baseline are unchanged. This release adds a verifier, six scenario definitions and a fixed state oracle. What the fixtures show Block R100 starts the synthetic chain. Branch A contains A101 with a seven-unit credit for invoice I-42 and A102 with three more units. A second branch contains B101 with a five-unit credit and an empty B102. After the replacement, the selected projection changes from 10 to 5. The raw A-branch inclusions remain available for inspection but no longer contribute to the selected credit. These amounts are arbitrary test values. They do not model a particular token transfer. The crash-before-commit-and-retry scenario checks that an injected exception leaves the A101 checkpoint and seven-unit credit intact before a successful retry. duplicate-inclusion checks that replaying A102 does not add another raw row or credit. branch-replacement checks the selected tip, retained inclusions and rebuilt credit. stale-writer-rejected starts from B102 and attempts to write with the obsolete A102 checkpoint. The state must remain unchanged. conflicting-payload-rejected changes the amount under an already stored B101 inclusion key and expects a rollback. invalid-branch-rolled-back supplies a block with the wrong parent. Each failure has a named expected error and an unchanged expected state. The expected state file records the tip, canonical rows, raw log rows, credit rows and balance. The input file names every branch block and every call to the projection. Readers can compare state transitions without inferring the oracle from the implementation's output. This is a small boundary suite, not a claim of exhaustive reorganization coverage. Reproduce and apply With Python 3.10 or newer, extract the archive and run python3 run_fixtures.py. The standard-library verifier creates one in-memory SQLite database per scenario, compares all 17 steps with expected-state-trace.json and separately checks the unchanged source example against source-baseline.json. No RPC endpoint, credential, external service or production data is required. fixture-manifest.json identifies the preserved source and release files. SHA256SUMS records their hashes. Pharos Production connects this test boundary to its blockchain indexer and backend development services: a product team can use the fixture to specify what should survive a rollback before discussing an indexer implementation. The crypto transaction monitoring integration guide describes another reorg-sensitive state, a cached screening verdict. It provides adjacent engineering context, not independent validation of these synthetic states. Scope and reuse The caller supplies the branch treated as canonical. The fixture checks continuity, retained ancestry and the expected checkpoint inside one SQLite transaction. It does not authenticate a node, determine consensus or finality, model a process restart, deliver an outbox message or compensate an irreversible external action. The full rebuild is for a tiny instructional projection and is not a throughput benchmark. A passing run establishes agreement with this bounded oracle, not production safety. Data and documentation use CC BY 4.0. Python code uses MIT, with file-level scope in LICENSE-MANIFEST.md. The source article and linked website pages retain their owners' rights. Fixture design, documentation and verification code were AI-assisted. No independent human validation or measured production outcome is claimed.