**Parent:** Split from [#138 — Pre-deploy review pass 2](https://gitlab.com/PlasticDigits/yieldomega/-/issues/138) (Finding 2). **Severity:** Medium ## Problem Per-block ingestion in [`indexer/src/ingestion.rs`](https://gitlab.com/PlasticDigits/yieldomega/-/blob/main/indexer/src/ingestion.rs) runs **sequential SQL operations without a single database transaction**: - For each log: `persist_decoded_log` (per event). - Then: `upsert_indexed_block`. - Then: `save_chain_pointer`. If the process crashes **after** some events from block N are committed but **before** `save_chain_pointer`, the chain pointer remains at **N−1** while partial data for **N** exists. On restart, block N is re-fetched; `ON CONFLICT DO NOTHING` on `(tx_hash, log_index)` may skip re-inserts, while **orphan rows** from a reorged-out version of N can remain forever — reorg rollback keyed on `block_number` never deletes them if the pointer never advanced. This violates [`indexer/REORG_STRATEGY.md`](https://gitlab.com/PlasticDigits/yieldomega/-/blob/main/indexer/REORG_STRATEGY.md): *“reorged events must never stick”* (extended to crash windows at MegaETH-scale block times). ## References | Kind | Path / link | |------|-------------| | Ingestion loop | [`indexer/src/ingestion.rs`](https://gitlab.com/PlasticDigits/yieldomega/-/blob/main/indexer/src/ingestion.rs) (block loop ~L103–165) | | Persist API | [`indexer/src/persist.rs`](https://gitlab.com/PlasticDigits/yieldomega/-/blob/main/indexer/src/persist.rs) — may need `*_tx(&mut Transaction<'_, Postgres>, ...)` helpers | | Reorg | [`indexer/src/reorg.rs`](https://gitlab.com/PlasticDigits/yieldomega/-/blob/main/indexer/src/reorg.rs) | | Strategy doc | [`indexer/REORG_STRATEGY.md`](https://gitlab.com/PlasticDigits/yieldomega/-/blob/main/indexer/REORG_STRATEGY.md) (“Atomic rollback”, “reorged events must never stick”) | | Invariant | [`docs/testing/invariants-and-business-logic.md`](https://gitlab.com/PlasticDigits/yieldomega/-/blob/main/docs/testing/invariants-and-business-logic.md) — indexer correctness / rollback | | Skill | [`.cursor/skills/yieldomega-guardrails/SKILL.md`](https://gitlab.com/PlasticDigits/yieldomega/-/blob/main/.cursor/skills/yieldomega-guardrails/SKILL.md) | ## Recommended fix Wrap the per-block body in `sqlx::Transaction`: ```rust let mut tx = pool.begin().await?; for lg in &logs { persist_decoded_log_tx(&mut tx, &decoded).await?; } upsert_indexed_block_tx(&mut tx, next, block_hash).await?; save_chain_pointer_tx(&mut tx, &pointer).await?; tx.commit().await?; ``` Refactor `persist_decoded_log` to accept either `&PgPool` or `&mut Transaction` (duplicate thin wrappers acceptable for clarity). Ensure **error paths** roll back the transaction. Consider whether `bootstrap_pointer` and reorg paths already transactional — keep behavior consistent. ## Acceptance criteria - [ ] Events + `indexed_blocks` + `chain_pointer` updates for a given block are **all-or-nothing** committed. - [ ] Crash mid-block leaves DB as if the block had not been processed (pointer unchanged, no partial event rows for that block — or define explicit idempotent cleanup; preferred: transactional commit). - [ ] Existing reorg detection logic unchanged; only durability model improves. - [ ] New or extended test coverage (see Finding 8 sibling issue for integration test idea). ## Verification checklist - [ ] `cargo test` in `indexer/`. - [ ] Stress: kill indexer mid-block (manual or test harness) and confirm no partial block state vs pointer mismatch. - [ ] Review `ON CONFLICT` behavior after transactional fix — document any remaining edge cases. - [ ] Load test on local Postgres: no deadlocks under batch inserts.