[LOW] No dedicated database indexes on frequently queried join columns

[kilodesodiq-arch]

Engineering Gap

While Prisma schema has @@index declarations, several frequently joined columns may lack dedicated indexes for common query patterns: VerificationRequest.reviewedBy, SessionSubmission.sessionId with deletedAt, Claim.campaignId with status.

Codebase Evidence

• app/backend/prisma/schema.prisma: Indexes defined but need audit against actual query patterns
• app/backend/src/claims/claims.service.ts: Queries by campaignId + status

Risk Profile

Slow queries under load. Sequential scans on large tables.

Remediation Strategy

Audit top 10 most frequent Prisma queries. Run EXPLAIN to identify missing indexes. Add compound indexes for common filter combinations.

Success Conditions

• Top queries audited
• Missing indexes added
• Query performance documented

Change Surface

File: app/backend/prisma/schema.prisma

Security Review

No security impact.

Completion Checklist

• Implementation completed
• Peer reviewed
• Tests passing
• Ready for merge

[chuks68]

I would like to work on this issue because i am confident i can resolve the issue, having worked on and resolved similar issues. my eta is 4 hours

[grantfox-oss]

🦊 GrantFox — @chuks68 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 #131)
2. Your PR will be reviewed by the ChainForgee maintainers

Good luck! Track your progress on GrantFox.