Add a unique partial index and constraint test for `api_keys.prefix` to guarantee prefix-lookup correctness

[greatest0fallt1me]

Description

The gateway auth flow in src/middleware/gatewayApiKeyAuth.ts performs prefix-based lookup before a timing-safe full-key hash compare, but there is no database guarantee that key prefixes are unique among active keys. This backend task adds the constraint/index and tests so prefix collisions cannot cause ambiguous lookups.

Requirements and Context

• Add a migration introducing a unique index on active (non-revoked) api_keys prefixes.
• Verify src/repositories/apiKeyRepository.ts lookups rely on the indexed column.
• Add constraint tests using pg-mem.
• Must be secure, tested, and documented
• Should be efficient and easy to review

Suggested Execution

1. Fork the repo and create a branch

   git checkout -b task/api-key-prefix-unique-index

2. Implement changes
   • migrations/0006_api_key_prefix_unique.sql — partial unique index
   • src/repositories/apiKeyRepository.ts — confirm lookup path
   • Add a constraint regression test
3. Test and commit
   • npm test -- src/repositories/apiKeyRepository.test.ts
   • Cover edge cases
   • Include test output and notes in the PR

Example commit message

task: enforce unique active api_key prefixes

Acceptance Criteria

• Inserting a duplicate active prefix fails at the database level
• Revoked keys do not block reuse of a prefix
• Tests cover collision and revocation cases

Guidelines

• Minimum 90% test coverage with Jest
• Clear documentation and inline comments
• Timeframe: 96 hours

[elijah4196]

@elijah4196 has applied to work on this issue as part of the Stellar Wave Program's 5th wave.

I would like to work on this issue because it aligns with my interest in backend development and database optimization.

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

[drips-wave]

Congratulations, @elijah4196! 🎉 Your application was accepted by the repo's maintainers, and the issue is due on June 2, 2026.

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