[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