Fully retire D1 for chain data — delete tables, prune logic, ingest endpoints, and the D1 fallback (gated on #4771)

[JSONbored]

Context

The end state for metagraphed-health D1, per ADR 0014 and the #2108 infra epic, is full retirement for chain data -- D1 was always meant to be a temporary hot cache during the Postgres cutover, not a permanent architecture. Tonight's work (#2112, #4746/#4750) got blocks/extrinsics/account_events flipped to Postgres-primary, and #4771 (once built) will do the same for the metagraph/neuron pipeline. This issue tracks the actual deletion once every dependency clears -- it is explicitly a "once X, Y, Z are true" issue, not startable yet.

D1 hosts tables in genuinely different categories, and this issue is scoped ONLY to the chain-data ones -- the health/registry-monitoring tables (surface_checks, surface_status, surface_uptime_daily, rpc_proxy_events) are unrelated to chain data and are explicitly NOT part of D1 retirement; they're registry/health-prober telemetry with their own, already-working retention and no relationship to the Postgres cutover. Do not delete them under this issue.

Similarly, account_events_daily and subnet_snapshots are explicitly, deliberately retained indefinitely per their own migration comments (migrations/0009_account_events.sql:33-35, migrations/0002_analytics.sql:9-10) -- low-volume, long-term-by-design tables, not a capacity risk at any realistic timescale (~2GB/decade and ~4MB/decade respectively at current growth). These are NOT part of D1 retirement either; do not touch them under this issue.

Requirements

• Gate 1: Build the metagraph/neuron pipeline's missing Postgres write path (neurons + neuron_daily) #4771 (metagraph/neuron Postgres pipeline) must be built, verified, and flipped -- neurons/neuron_daily are the only chain-data tables still D1-only after tonight's work.
• Gate 2: neuron_daily's backlog-clearing prune has been silently stuck since the 400->90 day retention cut #4770 (neuron_daily prune bug) -- moot once Build the metagraph/neuron pipeline's missing Postgres write path (neurons + neuron_daily) #4771 ships and D1's copy is deletable, but if Build the metagraph/neuron pipeline's missing Postgres write path (neurons + neuron_daily) #4771 is delayed, neuron_daily's backlog-clearing prune has been silently stuck since the 400->90 day retention cut #4770 should still be fixed independently so D1 doesn't hit its cap again in the meantime.
• Gate 3: blocks/extrinsics/account_events Postgres serving must be proven reliable over a real sustained window (not just tonight's spot-verification) before the D1 fallback is removed -- per ADR 0014's own stricter pre-launch criteria, this is explicitly a lower bar tonight (no real users yet) than what should gate a permanent code deletion.
• Gate 4: the accepted gaps from tonight (infra(serving): cut serving over D1 → Postgres via Hyperdrive, tier-by-tier #2112's 9,000-block Postgres hole, the mixed extrinsics shape in historical rows) should be closed via the full archive-gated re-backfill (blocked on the archive node reaching chain tip, ~19 days out per infra(archive-node): bare-metal buildout — provision → sync → first-party RPC origin #2111) before deleting D1's copy entirely -- once D1 is gone, it's no longer a safety net if a real gap in Postgres surfaces.
• Once all gates clear: delete D1's blocks/extrinsics/account_events/neurons/neuron_daily tables and their prune-cron logic (pruneBlocks/pruneExtrinsics/pruneAccountEvents/pruneNeuronDaily and the archive-then-prune wiring in workers/api.mjs's NEURON_HISTORY_ROLLUP_CRON handler).
• Delete the now-dead D1 ingest endpoints (/api/v1/internal/blocks, /internal/events in workers/api.mjs) and the tryPostgresTier D1-fallback branches for these tiers (workers/postgres-tier.mjs callers) -- once nothing depends on the fallback, keeping dead fallback code around is exactly the kind of thing that causes confusion later.
• Delete scripts/stream-events.py, scripts/fetch-events.py (if nothing else uses it), the streamer's Ansible role, docs/realtime-streamer.md, the */3 R2-drain cron, and .github/workflows/backfill-events.yml -- all of these exist solely to feed or repair D1's chain-data tables.
• Update ADR 0014 (or file its successor if enough has changed by then, per its own "if reality moves again, write ADR 0015" guidance) to reflect D1 fully retired for chain data.

Deliverables

• A single, well-tested PR (or a small tightly-sequenced set) deleting the schema, prune logic, ingest endpoints, and fallback code identified above.
• Deletion of the now-unused scripts/docs/workflows/Ansible role.
• An updated ADR reflecting the actual end state.
• Confirmation that account_events_daily, subnet_snapshots, and the health/registry-monitoring tables remain untouched and D1 continues to exist for exactly those, by design.

Expected outcomes

• metagraphed-health D1 shrinks to just its intentionally-D1-native tables (health/registry monitoring + the two deliberately-indefinite rollups).
• No dead code paths remain referencing the retired tables.
• The block explorer's chain-data serving is 100% Postgres, with no D1 fallback remaining for blocks/extrinsics/account_events/neurons/neuron_daily.

Links/resources

• Roadmap: metagraphed-core hybrid infra (Cloudflare edge · Railway core · Postgres) #2108 (parent infra epic), infra(serving): cut serving over D1 → Postgres via Hyperdrive, tier-by-tier #2112 (blocks/extrinsics/account_events cutover), infra(decommission): retire GitHub poller + streamer + */3 drain; demote D1 to hot cache #2116 (streamer/poller decommission -- this issue is effectively infra(decommission): retire GitHub poller + streamer + */3 drain; demote D1 to hot cache #2116's final step for the D1 side), Build the metagraph/neuron pipeline's missing Postgres write path (neurons + neuron_daily) #4771 (metagraph/neuron pipeline -- the actual remaining blocker), neuron_daily's backlog-clearing prune has been silently stuck since the 400->90 day retention cut #4770 (neuron_daily prune bug, independent stopgap), infra(archive-node): bare-metal buildout — provision → sync → first-party RPC origin #2111 (archive node sync, gates the historical-backfill gate)
• ADR 0014 (docs/adr/0014-chain-data-infrastructure-and-postgres-cutover.md) -- Sequencing step 8 ("Delete D1's tables and prune logic once they're empty")
• migrations/0009_account_events.sql:33-35, migrations/0002_analytics.sql:9-10 -- the explicit "retain indefinitely" comments for the two tables this issue does NOT touch

Priority

Medium -- not startable until #4771 lands, and not urgent in itself (D1's chain-data tables are now correctly bounded and shrinking, not an active risk). This is the clean final step of a project already well underway, tracked now so it isn't forgotten once the real blocker (#4771) clears.

[linear-code]

JSO-3410