Add an integration test that hits a real postgres

[Baskarayelu]

Summary

Use pg-mem fallback in unit tests but require a real DB in CI integration.

Background

Test coverage in this area is currently thin: regressions can land without a failing build. Adding the cases below will lock the contract in place, document the expected behaviour through executable examples, and shorten review cycles when this code changes again.

Acceptance criteria

• The change matches the summary above.
• The new test fails on the current main before the fix (if this is a regression test) and passes after.
• Tests run on every CI matrix entry, not just the local default.
• Lint, type-check, and tests all pass locally.
• PR description references this issue with Closes #<this-issue>.

Implementation hints

• Pick the right test layer: unit tests inside the affected module for pure logic, integration tests under tests/ for cross-module behaviour.
• Cover the happy path and at least one explicit sad path; the title of this issue describes the boundary that needs to be locked in.
• Where the project uses proptest/fast-check/fc, prefer a property test over many hand-written cases. Commit any failing seed back to the repo.
• Keep test names assertive (returns_zero_when_balance_is_zero), not interrogative.
• New tests should be deterministic: no Date.now()/Math.random() without an injected fake.

Repo-specific notes

• Run npx tsc --noEmit, npm test, and the security:scan / sbom:check scripts locally before pushing.
• Any new request or response shape must have a matching Zod schema and a regenerated OpenAPI entry.
• New env vars must be added to .env.example and to the relevant deployment doc.

Out of scope

• Unrelated refactors in adjacent files.
• Stylistic-only changes (formatting, renaming) that are not required by the fix.
• Anything beyond the acceptance criteria above; surface follow-ups as separate issues.

How to claim and submit

1. Comment on this issue saying you'd like to take it on; wait for a maintainer to assign you (avoids duplicated effort).
2. Open a PR that references this issue (Closes #<this-issue>).
3. Make sure CI is green and request review from a CODEOWNERS maintainer.
4. PRs that close this issue and pass review may qualify for a reward — see the MAYBE REWARDED label and the GrantFox OSS campaign page.

---

Category: test  ·  Campaign: GrantFox OSS · Official Campaign · Maybe Rewarded

[RemmyAcee]

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

I'd like to be assigned to this issue. I'll deliver a clean, maintainable solution that matches the requirements.

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

[mysterio123865]

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

Hi, I'd like to take this issue on. I'll nail down the logic as specified, make sure edge cases are covered, and submit a well-tested PR that doesn't create extra work for you on review.

Could you assign this to me? Ready to start now.

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

[grantfox-oss]

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

Good luck! Track your progress on GrantFox.

[drips-wave]

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

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

[grantfox-oss]

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

@mysterio123865's PR #610 was approved and merged by @Baskarayelu.

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

👏 Great work, @mysterio123865! Keep contributing to CredenceOrg.

[drips-wave]

This issue has been completed by @mysterio123865 as part of the Stellar Wave Program's 6th Wave 🥳

😎 @mysterio123865: You earned 200 Points for completing this issue! After the current Wave ends, you'll be eligible for a percentage of the Wave's reward pool based on the percentage of total points you've earned. Learn more here. You can also Leave a review to share your experience working on this issue.

🧑‍💻 Repo maintainers: How'd the contributor do? Leave a review to share your experience working with them.