Add a database index and query-plan audit for hot org-scoped list endpoints in db/migrations

[1nonlypiece]

📌 Description

Several org-scoped list endpoints (org vaults, transactions, notifications,
audit logs) filter and sort on (organization_id, created_at, id) but it is not
verified that every hot path has a matching composite index. A missing covering
index turns a paginated list into a sequential scan as data grows.

This issue audits the hot org-scoped queries and adds any missing composite
indexes, backed by an EXPLAIN-based regression test.

Goal: ensure every hot org-scoped list query uses an index, verified by an
EXPLAIN regression test.

🎯 Requirements and Context

• Must enumerate hot org-scoped queries and their required composite indexes.
• Add missing indexes via db/migrations (with down-rollback).
• Must add an EXPLAIN-based test asserting index usage (no seq scan on hot paths).
• Must not duplicate the existing performance-index migration's coverage.

🛠️ Suggested Execution

1. Fork the repo and create a branch

git checkout -b perf/org-list-index-audit

2. Implement changes

• Add index migration(s) in db/migrations.
• Document the audit in docs/performance-testing.md.

3. Test and commit

• Add src/tests/performance/orgListPlans.perf.test.ts run with bun test.
• Cover edge cases: each hot query uses index, rollback works, large-table plan.

Example commit message

perf: index and query-plan audit for org-scoped list endpoints

✅ Guidelines

• Minimum 95% test coverage on the new/changed lines.
• Clear, reviewer-friendly documentation.
• No regressions on existing queries.
• Timeframe: 96 hours.

🏷️ Labels

type-performance · area-backend · type-testing · MAYBE REWARDED · GRANTFOX OSS · OFFICIAL CAMPAIGN

💬 Community & Support

• Join the Disciplr contributor Discord to coordinate, ask questions, and get
  unblocked fast: https://discord.gg/xvNAvMJf
• Please introduce yourself in the channel before you start so we can avoid
  duplicate work, pair you with a reviewer, and get your PR merged quickly.
• Maintainers actively triage this channel and aim for fast, clear, respectful
  reviews — reach out any time you're blocked.

[cyber-excel10]

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

Hi, i do like to work on this task ....

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

[drips-wave]

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

🧑‍💻 @cyber-excel10: 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 🌊