Clarify ownership between the Prisma schema and the indexer migrations

[priscaenoch]

Problem

The repository contains two independent database migration mechanisms:

• prisma/ at the root, used by the TypeScript API service, with prisma:migrate, prisma:push, and a validate:prisma CI step.
• indexer/migrations/, a separate migration directory owned by the indexer service.

Both services connect to the same per-network database (*_DATABASE_URL). With two migration systems and no documented ownership, it is unclear which system owns which tables, what order migrations must run in, or how to bring up a fresh database correctly. This risks schema drift and conflicting migrations.

What needs to be done

• Document which tables each system owns and the required order of application.
• Decide and document the target state: a single migration system, or a clear, enforced boundary between the two.
• Provide a single documented command/sequence to bring a fresh database to a working state for both services.
• If consolidating, plan and land the migration path.

Files

• prisma/
• indexer/migrations/
• README.md / docs/

Acceptance deliverables

• Documented ownership of every table and the correct bring-up sequence.
• A fresh database can be initialised for both services with a documented command.

Tests to pass

• A documented fresh-database bring-up succeeds and both services start against it.
• npm run validate:prisma continues to pass.

[divinemike019]

Hi, I'd like to work on this issue. Since the repository currently has two independent database migration mechanisms (Prisma schema and indexer migrations), the core problem is unclear ownership — it's not obvious which one is the source of truth, which risks the two drifting out of sync over time.

My plan is to:

1. Review both migration paths thoroughly to understand what each currently owns and where they overlap.
2. Propose (and document) a clear ownership boundary — likely designating one as the source of truth for schema changes, with the other consuming/deriving from it, or clearly scoping each to distinct, non-overlapping concerns.
3. Update the relevant docs/README so future contributors know which mechanism to use for what, avoiding this ambiguity going forward.
4. Where needed, adjust the actual migration setup to enforce that boundary rather than just documenting an inconsistency.

I have backend and database experience, and I'll make sure any changes are safe and don't disrupt existing data or deployment flows. Happy to share my findings and proposed approach early for feedback before finalizing.

[grantfox-oss]

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

Good luck! Track your progress on GrantFox.