Make schema constraints and migration safety resilient in `src/db/schema.ts`

[Jagadeeshftw]

Description

The current path can fail in ways that are hard to retry or safely replay. src/db/schema.ts owns schema constraints and migration safety, but the current behavior does not yet define the contract tightly enough for safe maintenance. Design the change so the runtime path, tests, and docs all describe the same behavior.

Requirements and context

• Keep the contract specific to src/db/schema.ts and its existing callers.
• Add coverage for the success path and the failure or boundary path relevant to this issue.
• Preserve compatibility where older callers or existing tests already rely on the current shape.
• Keep the change easy to review and small enough for one focused PR.

Suggested execution

Fork the repo and create a branch

git checkout -b schema-safety-reliability-175

Implement changes

• Update/Write: src/db/schema.ts
• Write comprehensive tests: src/db/migration.test.ts
• Add documentation: docs/db/schema.md
• Keep any new helper names consistent with the rest of src/

Test and commit

• Run pnpm test
• Run pnpm lint
• Add a note for any edge cases that are intentionally out of scope

Example commit message

fix: improve reliability and retry semantics

Guidelines

• WCAG 2.1 AA is not relevant here; keep the focus on correctness and maintainability.
• Prefer a narrow, well-documented fix over a broad refactor.
• Timeframe: 96 hours

[Jagadeeshftw]

Assigned @Osifowora to this issue. Please open a PR referencing this issue (e.g. Closes #361) when you're ready. If you have questions along the way, feel free to join our contributor chat: https://t.me/+vTbe0hOUKrFhNTJl

[drips-wave]

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

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

[Jagadeeshftw]

Thanks for applying here. Please also join our Telegram group here: https://t.me/+vTbe0hOUKrFhNTJl. It helps us keep communication clear while we review contributions.

[grantfox-oss]

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

@Osifowora's PR #498 was approved and merged by @Jagadeeshftw.

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

👏 Great work, @Osifowora! Keep contributing to Stellopay.

[drips-wave]

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

😎 @Osifowora: You earned 100 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.