[HARD][PERFORMANCE] Add cursor pagination, query budgets, indexes, and load-test gates

[Obiajulu-gif]

Overview

Harden ChainMove's data-access layer for growth by introducing consistent cursor pagination, query projections, index design, query budgets, and repeatable load testing for high-traffic dashboards and APIs.

Problem

Unbounded list queries, full-document reads, offset pagination, and missing compound indexes can become slow or expensive as users, transactions, investments, repayments, audit events, and activity records grow. Performance problems in finance APIs can also cause retries and duplicate user actions.

Scope

• Inventory list/search/aggregation routes and document their expected access patterns.
• Create a shared cursor-pagination contract with stable ordering and opaque signed/validated cursors.
• Replace unbounded/large offset-based reads on priority routes.
• Add explicit projections so passwords, KYC fields, metadata blobs, and unused fields are not loaded.
• Design and document compound/partial/unique indexes for priority queries.
• Add query explain/plan verification tooling using representative seeded data.
• Define query budgets: maximum page size, execution time target, documents examined/returned ratio, and aggregation memory behavior.
• Detect or prevent N+1 patterns in dashboard aggregation services.
• Add cache boundaries only for safe, non-sensitive, clearly invalidated data.
• Add k6/Artillery or equivalent load scenarios for transaction history, investor pools, repayment history, admin queues, and concurrent mutations.
• Add CI or scheduled performance regression gates with practical thresholds.

Correctness requirements

• Pagination must not skip or duplicate records when new records arrive between pages.
• Cursors must reject tampering, wrong filter context, and expired/version-incompatible formats.
• Authorization filters must be applied before pagination/counting.
• Index changes need migration/rollout guidance.
• Caching must never expose one user's data to another.

Test requirements

• stable pagination with equal timestamps
• inserts/deletes between page requests
• cursor tampering and filter mismatch
• page-size limits
• authorization isolation
• explain-plan assertions on seeded volume
• N+1 regression check
• load test with documented baseline and threshold

Acceptance criteria

• Priority list routes use a consistent cursor contract.
• Query projections and indexes match documented access patterns.
• Representative queries stay within agreed budgets on seeded volume.
• Pagination remains stable under concurrent inserts.
• A repeatable load-test suite and baseline report are committed.
• Performance regressions can be detected before deployment.
• Lint, typecheck, tests, and build pass.

Difficulty

Hard

[Emelie-Dev]

Hi, I would love to work on this issue

[TechBroAfrica]

Focused on building trust through consistent, meaningful contributions. I approach every issue with curiosity, attention to detail, and a commitment to delivering clean, maintainable, and well-tested solutions. Driven by impact, not just commits, I value clear communication, effective collaboration with maintainers, continuous learning, and delivering meaningful value to every project I contribute to.

[prissca]

i will love to work on this issue, i have gone through the requirement and i understood it

[grantfox-oss]

🦊 GrantFox — @prissca has been assigned to this issue as part of the Official Campaign | FWC26 campaign!

Next steps:

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

Good luck! Track your progress on GrantFox.

[grantfox-oss]

🎉 This issue has been marked as completed on GrantFox as part of the Official Campaign | FWC26 campaign!

@prissca's PR #136 was approved and merged by @Obiajulu-gif.

🏆 @prissca: You earned 35 FoxPoints for this contribution! Your current tier: Explorer (869 total points). Track your full progress on GrantFox.

👏 Great work, @prissca! Keep contributing to Chainmove.