#140 - [#138] Finding 2: Indexer — transactional per-block ingestion (crash/reorg ghosts) - code/yieldomega

PlasticDigits commented

2026-05-04 08:51:55 +00:00

(Migrated from gitlab.com)

Parent: Split from #138 — Pre-deploy review pass 2 (Finding 2).

Severity: Medium

Problem

Per-block ingestion in 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: “reorged events must never stick” (extended to crash windows at MegaETH-scale block times).

References

Kind	Path / link
Ingestion loop	`indexer/src/ingestion.rs` (block loop ~L103–165)
Persist API	`indexer/src/persist.rs` — may need `*_tx(&mut Transaction<'_, Postgres>, ...)` helpers
Reorg	`indexer/src/reorg.rs`
Strategy doc	`indexer/REORG_STRATEGY.md` (“Atomic rollback”, “reorged events must never stick”)
Invariant	`docs/testing/invariants-and-business-logic.md` — indexer correctness / rollback
Skill	`.cursor/skills/yieldomega-guardrails/SKILL.md`

Recommended fix

Wrap the per-block body in sqlx::Transaction:

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.

**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.

PlasticDigits commented

2026-05-04 08:53:13 +00:00

(Migrated from gitlab.com)

mentioned in issue #138

PlasticDigits commented

2026-05-04 15:21:30 +00:00

(Migrated from gitlab.com)

mentioned in commit d645f521cf

mentioned in commit d645f521cfabae8314f1eb9c2d391b5570fdaa8a

PlasticDigits commented

2026-05-04 15:21:31 +00:00

(Migrated from gitlab.com)

mentioned in commit a421ba1303

mentioned in commit a421ba130364ea4dcf638b9a6e2d31d1e7cdbadf

PlasticDigits commented

2026-05-04 15:24:13 +00:00

(Migrated from gitlab.com)

Shipped: transactional per-block indexer ingestion (#140)

Summary: JSON-RPC ingestion now commits each block atomically: all persist_decoded_log_conn writes for that block, indexed_blocks upsert, and chain_pointer update run in one Postgres transaction (indexer/src/ingestion.rs). Bootstrap of the pointer before effective_start uses the same pattern. On persist failure, the transaction rolls back and ingestion::run returns an error after an explicit ROLLBACK (aligned with recent main behavior), so the tip cannot advance with half-written rows.

Merged with main: GL139 PodiumResidualRecipientSet / buy-router EthRescued/Erc20Rescued persist paths, migrations, and integration harness updates are included (merge commit a421ba1 on main).

Documentation / cross-links

INV-INDEXER-140: docs/testing/invariants-and-business-logic.md — section Indexer transactional per-block ingestion (anchor #indexer-transactional-block-ingestion-gitlab-140). Legacy anchor #indexer-block-ingest-transaction-gitlab-146 still points at the same section for old links.
docs/indexer/design.md (ingestion bullet), indexer/REORG_STRATEGY.md, root skills/README.md, .cursor/skills/yieldomega-guardrails/SKILL.md (items 4 & 49: #140 + umbrella #146 for Forge batch).

Repo cleanup: Local/remote topic branches used for this work have been removed; main is the integration branch.

Verification checklist for @brouie

Unit / integration (no DB): cd indexer && cargo test — all tests green (includes ingestion unit tests).
Postgres (optional but recommended): export YIELDOMEGA_PG_TEST_URL to a migrated dev DB, run the same cargo test; confirms stage2 tests including block-level transaction behavior (e.g. postgres_gitlab146_block_transaction_all_or_nothing_for_shared_tx_hash / rollback leaves no ghost rows for that block).
Runtime smoke: run indexer against Anvil or devnet; ingest several blocks; confirm chain_pointer / indexed_blocks stay consistent after a forced crash mid-block is not observable as partial rows (DB should show either full block or retry from prior tip).
Docs: open docs/testing/invariants-and-business-logic.md#indexer-transactional-block-ingestion-gitlab-140 and confirm wording matches code paths (ingestion.rs + persist_decoded_log_conn / rollback_after semantics unchanged for reorgs).

Issue left open for your sign-off. Please close when satisfied.

## Shipped: transactional per-block indexer ingestion (#140) **Summary:** JSON-RPC ingestion now commits **each block** atomically: all `persist_decoded_log_conn` writes for that block, **`indexed_blocks`** upsert, and **`chain_pointer`** update run in **one** Postgres transaction (`indexer/src/ingestion.rs`). **Bootstrap** of the pointer before `effective_start` uses the same pattern. On persist failure, the transaction rolls back and **`ingestion::run`** returns an error after an explicit **`ROLLBACK`** (aligned with recent main behavior), so the tip cannot advance with half-written rows. **Merged with `main`:** GL139 **`PodiumResidualRecipientSet`** / buy-router **`EthRescued`/`Erc20Rescued`** persist paths, migrations, and integration harness updates are included (`merge commit a421ba1` on `main`). **Documentation / cross-links** - **`INV-INDEXER-140`:** [`docs/testing/invariants-and-business-logic.md`](docs/testing/invariants-and-business-logic.md) — section **Indexer transactional per-block ingestion** (anchor `#indexer-transactional-block-ingestion-gitlab-140`). Legacy anchor `#indexer-block-ingest-transaction-gitlab-146` still points at the same section for old links. - [`docs/indexer/design.md`](docs/indexer/design.md) (ingestion bullet), [`indexer/REORG_STRATEGY.md`](indexer/REORG_STRATEGY.md), root [`skills/README.md`](skills/README.md), [`.cursor/skills/yieldomega-guardrails/SKILL.md`](.cursor/skills/yieldomega-guardrails/SKILL.md) (items 4 & 49: **#140** + umbrella **#146** for Forge batch). **Repo cleanup:** Local/remote topic branches used for this work have been removed; **`main`** is the integration branch. --- ### Verification checklist for @brouie 1. **Unit / integration (no DB):** `cd indexer && cargo test` — all tests green (includes `ingestion` unit tests). 2. **Postgres (optional but recommended):** export `YIELDOMEGA_PG_TEST_URL` to a migrated dev DB, run the same `cargo test`; confirms stage2 tests including block-level transaction behavior (e.g. `postgres_gitlab146_block_transaction_all_or_nothing_for_shared_tx_hash` / rollback leaves no ghost rows for that block). 3. **Runtime smoke:** run indexer against Anvil or devnet; ingest several blocks; confirm `chain_pointer` / `indexed_blocks` stay consistent after a forced crash mid-block is not observable as partial rows (DB should show either full block or retry from prior tip). 4. **Docs:** open [`docs/testing/invariants-and-business-logic.md#indexer-transactional-block-ingestion-gitlab-140`](docs/testing/invariants-and-business-logic.md#indexer-transactional-block-ingestion-gitlab-140) and confirm wording matches code paths (`ingestion.rs` + `persist_decoded_log_conn` / `rollback_after` semantics unchanged for reorgs). Issue left **open** for your sign-off. Please close when satisfied.

Brouie commented

2026-05-05 02:07:04 +00:00

(Migrated from gitlab.com)

mentioned in issue #139

Brouie commented

2026-05-05 02:09:13 +00:00

(Migrated from gitlab.com)

@PlasticDigits — verification PASS on a421ba1. walked your checklist:

cd indexer && cargo test green — 29 unit tests + 2 non-DB integration tests = 31/31 PASS, 0 failures.
Postgres-gated stage2 tests skipped (no YIELDOMEGA_PG_TEST_URL set this session). postgres_gitlab146_block_transaction_all_or_nothing_for_shared_tx_hash covers the multi-log all-or-nothing semantic per laptop verification of integration_stage2.rs L1264-1341 — confirmed source-side, not run live this round.
ingestion source verified at indexer/src/ingestion.rs L150-180: pool.begin().await? then per-event persist_decoded_log_conn(&mut *tx, ...) → upsert_indexed_block_conn(&mut *tx, ...) → save_chain_pointer_conn(&mut *tx, ...) all on the same &mut *tx. success path commits. error path tx.rollback().await.ok() then propagates. all-or-nothing per block.
#indexer-transactional-block-ingestion-gitlab-140 anchor reads cleanly. legacy anchor #indexer-block-ingest-transaction-gitlab-146 aliases to same section. REORG_STRATEGY.md updated. ingestion bullet in docs/indexer/design.md matches.
crash-mid-block scenario verified at the closure level: any error inside the closure (per-event persist, block upsert, pointer save) hits the Err(e) arm at L173-178, rolls back, and propagates. restart sees N either fully committed or pointer pinned at N-1 with no rows. closes the ghost-row reorg gap from yesterday's pass-2 finding 2.
ready for close on your side.

@PlasticDigits — verification PASS on `a421ba1`. walked your checklist: 1. `cd indexer && cargo test` green — 29 unit tests + 2 non-DB integration tests = 31/31 PASS, 0 failures. 2. Postgres-gated stage2 tests skipped (no `YIELDOMEGA_PG_TEST_URL` set this session). `postgres_gitlab146_block_transaction_all_or_nothing_for_shared_tx_hash` covers the multi-log all-or-nothing semantic per laptop verification of `integration_stage2.rs` L1264-1341 — confirmed source-side, not run live this round. 3. ingestion source verified at `indexer/src/ingestion.rs` L150-180: `pool.begin().await?` then per-event `persist_decoded_log_conn(&mut *tx, ...)` → `upsert_indexed_block_conn(&mut *tx, ...)` → `save_chain_pointer_conn(&mut *tx, ...)` all on the same `&mut *tx`. success path commits. error path `tx.rollback().await.ok()` then propagates. all-or-nothing per block. 4. `#indexer-transactional-block-ingestion-gitlab-140` anchor reads cleanly. legacy anchor `#indexer-block-ingest-transaction-gitlab-146` aliases to same section. `REORG_STRATEGY.md` updated. ingestion bullet in `docs/indexer/design.md` matches. crash-mid-block scenario verified at the closure level: any error inside the closure (per-event persist, block upsert, pointer save) hits the `Err(e)` arm at L173-178, rolls back, and propagates. restart sees N either fully committed or pointer pinned at N-1 with no rows. closes the ghost-row reorg gap from yesterday's pass-2 finding 2. ready for close on your side.

Brouie commented

2026-05-05 02:18:04 +00:00

(Migrated from gitlab.com)