feat(db): materialized view trident_contract_stats to power the analytics API

[Depo-dev]

Summary

The analytics endpoints (#73 and #74) need to return per-contract event counts and indexer throughput metrics. Running COUNT(*) GROUP BY contract_id against the full soroban_events table on every analytics request is a full table scan that will take seconds on a table with millions of rows. A materialized view pre-computes these aggregates and is refreshed periodically, making analytics queries instant regardless of table size.

Context

PostgreSQL materialized views store the result of a query as a physical table and can be refreshed on demand. With REFRESH MATERIALIZED VIEW CONCURRENTLY, refreshes do not lock the view for reads — analytics queries continue to work during the refresh. The tradeoff is that the data is slightly stale (up to 60 seconds old), which is entirely acceptable for an analytics endpoint that is shown on a dashboard.

What needs to be built

A migration that creates:

CREATE MATERIALIZED VIEW trident_contract_stats AS
SELECT
  contract_id,
  COUNT(*)           AS event_count,
  MAX(ledger_sequence) AS latest_ledger,
  MAX(ledger_timestamp) AS last_event_at
FROM soroban_events
GROUP BY contract_id;

CREATE UNIQUE INDEX ON trident_contract_stats (contract_id);

The unique index on contract_id is required for concurrent refresh.

A background goroutine in the Go API that calls REFRESH MATERIALIZED VIEW CONCURRENTLY trident_contract_stats every 60 seconds.

The GET /v1/analytics/contracts handler (part of issue #73) should query this view directly.

Acceptance Criteria

• Migration creates the materialized view and the unique index
• Background goroutine refreshes every 60 seconds; logs refresh duration
• Goroutine exits cleanly on context cancellation (process shutdown)
• Refresh failure logs a warning but does not crash the process
• GET /v1/analytics/contracts returns results from the view in under 10ms for a 1M-row table
• Integration test: seed 50 events for 3 contracts, trigger refresh, assert view returns correct counts for each contract

Files

database/migrations/000X_contract_stats_view.sql (new)

Dependencies

• feat(go-api): analytics endpoint — events per contract #73 (analytics endpoints per contract)

[Kingvic300]

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

Good day maintainers, my name is Victor Oladimeji, and I’m a software engineer with experience working on Go backends, PostgreSQL optimization, and analytics pipelines. I would like to work on issue #145 regarding the trident_contract_stats materialized view.

My approach will be to first review the existing analytics handlers and database migration patterns to ensure the solution integrates cleanly with the current architecture. After that, I’ll add a migration creating the trident_contract_stats materialized view and its required unique index to support concurrent refreshes.

I’ll then implement a background goroutine that refreshes the view every 60 seconds, logs refresh durations, handles failures gracefully, and shuts down cleanly on context cancellation. I’ll also update the analytics endpoint to query the materialized view directly for low-latency responses.

Finally, I’ll add an integration test that seeds sample events, triggers a refresh, and verifies that aggregated contract statistics are returned correctly. Because asking PostgreSQL to repeatedly count millions of rows for a dashboard is a good way to make a database reconsider its career choices.

I’d be happy to take this on.

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