Back monthly analytics aggregates with a materialized view instead of repeated full-table scans in src/routes/analytics.ts

[Jagadeeshftw]

Description

src/routes/analytics.ts computes aggregation rollups on demand against payments, escrowEvents, and agreementEvents, fronted only by the short-lived in-process AnalyticsCache (src/utils/analytics-cache.ts, TTL from ANALYTICS_CACHE_TTL_MS). Every cache miss still re-runs the full aggregation query. Add a Postgres materialized view precomputing the monthly rollups so a cache miss becomes a cheap indexed read instead of a full scan, refreshed on a schedule.

Requirements and context

• Add a materialized view definition and refresh migration under src/db/migrations/ covering the same aggregation shape src/routes/analytics.ts currently computes in application code
• Add a REFRESH MATERIALIZED VIEW CONCURRENTLY job (script or scheduled task) and document its cadence relative to ANALYTICS_CACHE_TTL_MS
• Update src/routes/analytics.ts to read from the view first, falling back to the live aggregation query if the view is stale beyond a configurable bound
• Must be secure, tested, and documented
• Should be efficient and easy to review

Suggested execution

Fork the repo and create a branch

git checkout -b feature/analytics-materialized-view

Implement changes

• Update/Write: src/db/schema.ts
• Update/Write: src/db/migrations/ (new migration)
• Update/Write: src/routes/analytics.ts
• Add/Update tests: src/routes/analytics.aggregation.test.ts
• Add documentation: docs/routes/analytics.md
• Include clear code comments and TS types
• Validate security assumptions

Test and commit

• Run tests: pnpm test
• Cover edge cases
• Include test output and security notes

Example commit message

feat: back analytics aggregates with a materialized view

Guidelines

• Minimum 95 percent test coverage
• Clear documentation
• Timeframe: 96 hours

[Stephan-Thomas]

📋 Grainlify Application

@Stephan-Thomas has applied to work on this issue as part of the Grainlify program.

hi, i would like to work on this.

---

Repo Maintainers: To accept this application, review their application or assign @Stephan-Thomas to this issue.

[TS-mfon]

📋 Grainlify Application

@TS-mfon has applied to work on this issue as part of the Grainlify program.

GM

i'm a blockchain developer

please can i work on this issue

---

Repo Maintainers: To accept this application, review their application or assign @TS-mfon to this issue.

[dotmantissa]

📋 Grainlify Application

@dotmantissa has applied to work on this issue as part of the Grainlify program.

Hi maintainers,

I'd like to work on this. I'll add the materialized view migration covering the monthly rollup shape, wire a REFRESH MATERIALIZED VIEW CONCURRENTLY job with documented cadence relative to ANALYTICS_CACHE_TTL_MS, update analytics.ts to read from the view first with a configurable staleness fallback to the live query, and document the implementation in docs/routes/analytics.md with 95 percent test coverage.

I estimate I can have a PR ready within 5 hours of being assigned. Could you please assign this to me?

Thanks.

---

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