Rework event processing around a single store boundary

[diegomrsantos]

Problem

The discussions in #735 and #841 are two symptoms of the same architectural problem: we do not have a single boundary that owns blocking SQLite execution, durable commit, in-memory NetworkState publication, and post-commit side effects.

Today EventProcessor::process_logs opens a transaction, processes logs one by one, calls database helpers during the batch, updates last_processed_block, and commits only at the end. During that window:

• database helpers mutate the shared watched state before commit
• notification behavior depends on whether a path uses silent modify_state or send_modify
• side effects can be emitted before commit
• async services still perform SQLite-backed writes directly, which is why fix: offload index sync store to blocking task #841 needed a local spawn_blocking workaround

That makes the system hard to reason about because there is no simple answer to these questions:

• when is a change durable?
• when is it visible through watch<NetworkState>?
• when is it valid to notify subscribers?
• when is it valid to run side effects?
• where should blocking DB work actually live?

Why #735 and #841 are the same issue

In #735, the problem shows up as a correctness/coherence issue:

• shared in-memory state is updated during batch processing
• state publication is not cleanly tied to commit
• side effects can escape before commit

In #841, the same problem shows up as an execution-model issue:

• async code is still directly responsible for blocking SQLite writes
• so we need ad hoc spawn_blocking fixes at individual call sites

The send_modify / modify_state split and the extra spawn_blocking calls are not the root cause. They are consequences of the same missing store boundary.

What a correct design needs

A single store boundary should own all of the following:

• where blocking SQLite work runs
• the atomic unit of persistence
• publication of committed in-memory state
• post-commit side effects

The invariant should be simple:

• before commit, changes are local only
• after commit, durable state and published in-memory state advance together
• side effects happen only from committed state

Two coherent directions

1. Keep batch-level atomicity

In this model:

• EventProcessor parses logs into batch-local domain changes
• later logs validate against a batch-local staged NetworkState
• NetworkDatabase owns the transaction internally
• committed state is published exactly once, after commit

This preserves the current batch/block-style processing model, but it is inherently more complex because later events in a batch need to see earlier uncommitted changes.

2. Simplify to event-by-event commits

In this model:

• each event validates against committed state
• each event commits in its own transaction
• NetworkState is updated only after commit
• side effects run only after commit

This is much simpler, but it requires replacing the block-only progress cursor with a finer checkpoint such as (block_number, transaction_index, log_index). With only last_processed_block, replay after a crash can re-apply non-idempotent events.

Recommendation

Unless we have measured evidence that one transaction per event is too slow, the simpler event-by-event model looks like the better default.

The current design appears to be optimized around reducing transaction count, but I have not found evidence that this was measured and shown to be a real bottleneck. What is already clear is that the current approach has real correctness and maintenance costs.

This issue is about fixing that ownership model. Tactical changes like adjusting send_modify behavior or adding more spawn_blocking calls may be useful mitigations, but they do not solve the underlying problem.

[diegomrsantos]

After digging through #885, I think the replacement path should be a smaller stacked series from unstable, not one large PR.

The bug is real: we need a single boundary where SQLite writes, durable commit, NetworkState publication, and downstream side effects line up. But I do not think we should take the event-level resume and cursor machinery from #885 as part of that fix.

Two reasons:

• it makes the diff much larger than the publication-boundary fix needs
• a cursor keyed only by (block_number, transaction_index, log_index) has same-height reorg risk: if height N is partially processed and then replaced by a different block at the same height, early logs in the replacement block can be skipped as if they were already processed

Updated direction:

1. PR 1: refactor(database): prepare block-scoped event processing

• prep only
• no schema change
• no event_processor rewrite
• keep NetworkState
• separate database tx writes from in-memory state publication in database
• add tx-scoped helper APIs and post-commit publication primitives

2. PR 2: fix(eth): process contract logs one block at a time

• one SQLite transaction per block
• later events in the same block read through that same tx
• publish NetworkState once after tx.commit()
• run index-sync and exit side effects after commit
• keep existing watch<NetworkState> consumers untouched

3. PR 3: cleanup

• remove dead cursor-specific or replacement-only machinery after the runtime fix lands

This keeps the real fix:

• block-scoped transactions
• post-commit publication
• post-commit side effects

and avoids coupling it to event-level resume machinery.

Close PR #885 only after PR 1 is ready and we have the replacement direction established.