feat: Materialized analytics rollup tables from indexed events

[joelpeace48-cell]

Background & current state

Computing analytics/leaderboards by scanning raw indexed events is expensive and won't scale to thousands of campaigns/participants. There are no precomputed rollups.

Goal

Maintain materialized rollup tables derived incrementally from indexed events so dashboards and leaderboards read precomputed aggregates in <50ms.

Technical design

• Rollups: participants_by_day, credits_by_day, claims_by_day, campaign_funnel(registered, credited, claimed), top_participants(campaign_id, address, points).
• Update incrementally as new events are projected (NEW-024) — apply deltas, not full recompute. Support idempotent reprocessing (recompute a day from raw if needed).
• Index rollup tables for the exact dashboard/leaderboard queries.

Edge cases

• Late/out-of-order events → upsert deltas keyed by (campaign, day).
• Reconciliation (NEW-025) recomputes affected rollup buckets.
• Time zones → store UTC; document.
• Ties in leaderboards → deterministic tiebreak (e.g., earliest ledger).

Task breakdown

• Rollup table migrations + indexes.
• Incremental delta updates in the projection layer.
• Recompute-from-raw utility for a bucket.
• Benchmarks vs. raw scans.

Acceptance criteria

• Dashboard/leaderboard queries hit rollups (<50ms p95) instead of scanning raw events.
• Rollups remain correct after reconciliation/late events.

Testing & verification

• Benchmark before/after; correctness test vs. recompute-from-raw.

Out of scope

• The dashboards/endpoints themselves (NEW-099, leaderboard Backend: Add campaign leaderboard endpoint (top participants by points) #341/Frontend: Add campaign participant leaderboard page #348).

Dependencies / related

• Builds on NEW-024/025; feeds NEW-027, NEW-099.

Difficulty: medium · Effort: M · indexer + performance

[ryzen-xp]

@ryzen-xp has applied to work on this issue as part of the Stellar Wave Program's 6th wave.

Hi maintainers 👋
I’m a blockchain developer with strong expertise in Stellar, Rust,TS and Stellar SDK integrations. I’ve worked on multiple Stellar-based smart contract and backend systems.

I understand the scope of this issue and can deliver a reliable, production-quality solution with proper tests and documentation.
Plz assign me !!

ETA: 24 hours

ℹ️ Repo Maintainers: To accept this application, review their application or assign @ryzen-xp to this issue.

[Kami-no-san]

@Kami-no-san has applied to work on this issue as part of the Stellar Wave Program's 6th wave.

Good day Dev,
if assigned this task, i will gracefully implement materialized analytics rollup tables from indexed event and make sure :
-Dashboard/leaderboard queries hit rollups (<50ms p95) instead of scanning raw events.
-Rollups remain correct after reconciliation/late events.

ℹ️ Repo Maintainers: To accept this application, review their application or assign @Kami-no-san to this issue.

[theo372001]

@theo372001 has applied to work on this issue as part of the Stellar Wave Program's 6th wave.

This task requires careful handling of edge cases like late-arriving data, UTC time-zoning, and deterministic tie-breaking for leaderboards. I have a firm grasp on how to implement idempotent upserts so that our aggregates always stay perfectly accurate.

ℹ️ Repo Maintainers: To accept this application, review their application or assign @theo372001 to this issue.

[devjayy43]

@devjayy43 has applied to work on this issue as part of the Stellar Wave Program's 6th wave.

I would like to work on this. please assign

ℹ️ Repo Maintainers: To accept this application, review their application or assign @devjayy43 to this issue.

[osasfaith]

@osasfaith has applied to work on this issue as part of the Stellar Wave Program's 6th wave.

I would love to work on this issue.

ℹ️ Repo Maintainers: To accept this application, review their application or assign @osasfaith to this issue.

[drips-wave]

Congratulations, @devjayy43! 🎉 Your application was accepted by the repo's maintainers, and the issue is due on June 30, 2026.

🧑‍💻 @devjayy43: Please resolve the issue such that the repo's maintainers have enough time to review your contribution before the due date. You'll earn Points for completing the issue on-time, which will make you eligible for a share of the Stellar Wave Program's reward pool.

Warning

When opening a PR, please link it to this issue to ensure it gets tracked accurately. Points are awarded when this issue is marked as completed by the maintainer.

🤠 Repo maintainers: Please keep an eye on the contributor's progress and review their work before the due date. You can manage this issue, including adjusting its complexity and points, here.

🌊 Happy Wave 🌊