[DATA]: Normalize payout milestones and deliver a zero-downtime JSON migration

[grantfox-oss]

Context

Milestones are stored in Payout.milestones as JSON while milestone operations have independent status, evidence, feedback, amount, and escrow semantics. JSON storage prevents relational constraints, efficient querying, safe concurrent updates, and reliable history.

Scope

• Design Milestone, MilestoneEvidence, and milestone transition/history models.
• Preserve ordering, amount/currency, due dates, status, evidence, feedback, and on-chain identifiers.
• Add constraints ensuring milestone totals agree with payout funding and ordering is unique per payout.
• Implement an expand/backfill/verify/contract migration strategy with resumable batches.
• Dual-read or dual-write during rollout so deployment is reversible.
• Replace whole-JSON updates with optimistic or transactional row-level operations.

Acceptance criteria

• Migration handles empty, null, legacy, and malformed milestone payloads with an exception report.
• Backfill is idempotent and safe to resume.
• No milestone data is lost; verification reports source/target counts and amount totals.
• Concurrent milestone updates cannot overwrite unrelated milestones.
• API and UI behavior is covered by migration and integration tests.
• Rollback and production runbooks are documented.

Relevant areas

prisma/schema.prisma, useMilestoneActions.ts, ManageMilestonesDialog.tsx, payout schemas and API routes.

[Marvelg256]

Hi! I'd like to work on this issue. I have a strong problem-solving background and have reviewed the issue. I'm confident I can implement a clean solution and submit a PR within 48 hours. Looking forward to contributing!

[kimanicode]

Hi, I'd like to take this issue. I'll design normalized Milestone, MilestoneEvidence, and transition/history models in prisma/schema.prisma with ordering and amount-total constraints, then run an expand/backfill/verify/contract migration with resumable, idempotent batches and dual-write during rollout for reversibility. I'll replace whole-JSON milestone updates with transactional row-level operations in useMilestoneActions.ts and the payout API routes, handle malformed/legacy payloads with an exception report, and cover it with migration, integration, and concurrency tests plus rollback/runbook docs. Please assign it to me, thanks.

[grantfox-oss]

🦊 GrantFox — @kimanicode has been assigned to this issue as part of the Official Campaign | FWC26 campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #110)
2. Your PR will be reviewed by the Eduvault-stellar maintainers

Good luck! Track your progress on GrantFox.