Improve Supabase query performance for high-cardinality ledger snapshots

[grantfox-oss]

some backend queries fetch large result sets, join on unindexed JSON fields, or rely on sorting/indexes that do not exist in Supabase, causing slow snapshots and timeouts under load.

Affected areas: app/backend/src/ingestion/event.repository.ts, app/backend/src/transactions/transaction.repository.ts, app/backend/src/refunds/refund.repository.ts, app/backend/supabase/migrations/*, app/backend/src/common/pagination/*.

Implementation scope:

• Audit slow query plans and add appropriate Supabase indexes for common filters and orderings.
• Rewrite heavy joins into staged queries or materialized snapshots where needed.
• Add query timeout handling and degrade gracefully for dashboard endpoints.
• Add integration tests that assert query execution under realistic data volumes.

Acceptance criteria:

• Key backend queries use indexed filter/order columns and avoid full-table scans.
• Large pagination queries remain stable under synthetic load.
• Slow query regressions are caught by CI or test assertions.

[mxrtins04]

Hiii, please can i be assigned this issue?

[Chigybillionz]

Good evening dear maintainer!!
I would like to work on this issue.

Experience:
I have experience with NestJS, TypeScript, Supabase/PostgreSQL, query optimization, database indexing, pagination systems, and backend performance tuning. I have worked on diagnosing slow query plans, eliminating full-table scans, optimizing joins, and improving API performance under high data volumes.

Implementation:
I will audit the affected repositories and query patterns, identify missing indexes for common filters and sort operations, optimize heavy joins through staged queries or snapshot-based approaches where appropriate, and introduce graceful timeout handling for dashboard endpoints. I will also add integration tests using realistic datasets to verify query performance, pagination stability, and prevent future regressions.

Delivery:
I can complete the optimization, testing, migration updates, and documentation within 8–12 hours, ensuring critical queries remain performant, scalable, and protected against regression under load.

GitHub:

https://github.com/chigybillionz

Thank you very much!!

[Meet-hybrid]

Can I work on this please?

[Glam26]

I'd like to take this issue.

I've worked on backend performance optimization for PostgreSQL/Supabase systems before, including query-plan analysis, index design, pagination scaling, and eliminating expensive joins that become bottlenecks under production load.

My approach will be to profile the affected repositories, identify full-table scans and missing indexes through query-plan analysis, and add targeted indexes for the most common filter and ordering paths. Where joins on JSON fields or large result sets are causing latency, I'll evaluate staged-query or snapshot-based alternatives that preserve correctness while improving performance.

I'll also add timeout handling and graceful degradation for dashboard-facing endpoints, then back the changes with integration tests using realistic data volumes so performance regressions are caught early in CI.

The goal is not only to make the current queries faster, but to establish guardrails that keep them performant as ingestion volume grows.

[okeolaolatun23-glitch]

Experienced in backend development, APIs, and OOP, with a strong interest in building scalable and maintainable software systems.

[noevidence1017]

Hello Maintainer. I am a fullstack dev and i've checked through this issue. I would love to resolve this issue as soon. Please assign

[nelly439]

Hi, I’d like to take this on.

From the description, it looks like the main issue is inefficient query patterns (full-table scans, joins on unindexed JSON fields, and missing indexes for sorting/pagination) causing degraded performance under load.

My approach would be to:

• Audit the affected repositories using EXPLAIN ANALYZE to identify slow paths and confirm where sequential scans or expensive joins are occurring
• Add targeted indexes (including composite and JSONB indexes where appropriate) based on actual filter and ordering patterns rather than generic indexing
• Refactor heavy joins into staged queries or precomputed/materialized views where it improves performance and consistency for dashboard reads
• Ensure pagination queries are index-friendly (e.g. cursor-based where beneficial) to avoid large OFFSET scans
• Introduce query timeout handling and safe fallbacks so dashboard endpoints degrade gracefully instead of timing out
• Add integration tests with seeded realistic data volumes to validate query performance and prevent regressions in CI

I’ll focus on measurable improvements (query time, index usage, and stability under load) and document the indexing strategy clearly in migrations and code comments.

Let me know if I can proceed.

[Menjay7]

Hi! I came across this issue and would love to work on it. I have experience with similar tasks and I'm confident I can investigate, implement a clean solution, and thoroughly test it before submitting a PR. If the issue is still open, I would appreciate it if you could assign it to me. Thank you!

[grantfox-oss]

🦊 GrantFox — @okeolaolatun23-glitch has been assigned to this issue as part of the Official Campaign campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #29)
2. Your PR will be reviewed by the BlockDash-Studios maintainers

Good luck! Track your progress on GrantFox.