Introduce schema migration tooling (Flyway/Liquibase) and baseline the existing schema #52

Description

This repository has no schema migration tooling. Schema is managed entirely by Hibernate's spring.jpa.hibernate.ddl-auto=update (backend-api/src/main/resources/application.properties), across all 26 @Entity classes and every feature module.

The core objective is to introduce a versioned, reviewable migration baseline (Flyway or Liquibase) so that schema changes are explicit artifacts rather than a side effect of entity annotations, and so that a change which cannot be applied by ddl-auto fails at deploy time rather than at the first insert in production.

Filed at the maintainer's request, from the discussion in #51 — see this comment for the original analysis. The baseline strategy is a repo-wide decision (versioning scheme, how it interacts with contributors' local databases), so it wants a design call before code.

Component

Backend (Spring Boot / Java 17)

Why this matters now

ddl-auto=update is additive-only. It creates tables and adds columns; it will not widen an existing CHECK constraint, drop one, change a column type, or remove anything. That limit is not theoretical — #51 hit it:

TransactionStatus gained PENDING, FAILED, REFUNDED and REVERSED. Hibernate had generated a CHECK constraint over the original two values when it first created transactions, and update cannot widen it. On any pre-existing database, every insert of a new status fails with:

new row for relation "transactions" violates check constraint
"transactions_transaction_status_check"

The workaround shipped in #51 is a documented manual statement, run once per environment:

ALTER TABLE transactions DROP CONSTRAINT IF EXISTS transactions_transaction_status_check;

That works, but it is a hand-written ALTER TABLE in a markdown file that nothing enforces, nothing versions, and CI cannot catch — CI starts a clean Postgres every run, so the failure is invisible there and appears only against a long-lived database. This will happen again the next time an enum grows or a column type changes.

Current state

• No Flyway or Liquibase dependency, and no db/migration directory anywhere.
• 26 @Entity classes → 27 tables (the extra is an implicit @ManyToMany join table, transaction_history_transactions).
• 4 entities have no explicit @Table(name = ...) and rely on Hibernate's implicit naming strategy: Address → address, Consultation → consultation, ConsultationAvailability → consultation_availability, TransactionHistory → transaction_history. A hand-written baseline must reproduce these names exactly or startup validation will fail.
• Recent features (escrow_orchestration_requests, on_chain_events, and the 7 payments-ledger tables in feat(backend): payments ledger with Paystack webhook verification & double-entry reconciliation #51) all landed as annotations only, so there is no partial migration history to build on.

Full generated table list, from a database created fresh by the current entity set:

address                              payments
appointments                         payouts
chain_event_outbox                   processed_webhook_events
clients                              refresh_tokens
consultation                         reviews
consultation_availability            skilled_workers
escrow_orchestration_requests        skills
ledger_accounts                      slot_reservations
ledger_entries                       stellar_channel_accounts
ledger_transactions                  stellar_transaction_submissions
notifications                        transaction_history
on_chain_events                      transaction_history_transactions
payment_reconciliation_discrepancies transactions
                                     user_accounts

Design decisions to settle first

These are the reason this is an issue rather than a PR:

1. Flyway or Liquibase. Flyway's plain-SQL versioned files are the lighter fit for a single-database Spring Boot service; Liquibase's changelog abstraction buys database portability this project does not currently need.
2. Baseline strategy. Either generate V1__baseline.sql from the current schema and have existing environments flyway baseline at V1, or start migrations at the payments tables and treat everything before as pre-existing. The first is cleaner and the second is less disruptive.
3. What ddl-auto becomes. validate is the point of the exercise — it turns "the entity and the table disagree" into a startup failure instead of a runtime one. Worth confirming this is the intent, since it changes the local-development loop for every contributor.
4. Contributors' local databases. Anyone with an existing local DB built by ddl-auto needs either a baseline command or a drop-and-recreate. This should be a documented one-liner in backend-api/README.md, not something people work out individually.
5. Whether CI should prove it. CI currently starts a clean Postgres, which only ever exercises the create-from-scratch path. Catching the class of bug in feat(backend): payments ledger with Paystack webhook verification & double-entry reconciliation #51 needs a second job that applies migrations to a database at the previous release's schema.

Tasks

• Decide Flyway vs. Liquibase, and the baseline strategy (see above)
• Add the dependency and configuration
• Generate and review V1__baseline.sql against a database created from the current entities, including the 4 implicitly-named tables and the implicit join table
• Add the follow-up migration covering the transactions_transaction_status_check drop from feat(backend): payments ledger with Paystack webhook verification & double-entry reconciliation #51, so existing environments converge without the manual step
• Switch spring.jpa.hibernate.ddl-auto to validate
• Document the baseline/reset procedure for existing local and deployed databases in backend-api/README.md
• Consider a CI job that migrates from the previous release's schema rather than only creating from scratch
• Remove the manual ALTER TABLE note from backend-api/docs/PAYMENTS_LEDGER.md once the migration supersedes it

Acceptance Criteria

• A fresh database is built entirely by migrations, with ddl-auto=validate, and the application starts
• A database at the pre-feat(backend): payments ledger with Paystack webhook verification & double-entry reconciliation #51 schema migrates forward cleanly, including the CHECK constraint that ddl-auto could not widen
• ./mvnw verify passes with validate in effect — no entity/table drift anywhere in the 27 tables
• The 4 implicitly-named tables and transaction_history_transactions validate against their entities
• The manual ALTER TABLE step is no longer needed and the docs referencing it are updated
• The procedure for an existing local database is documented and works from a clean checkout

Notes

Difficulty, priority and campaign labels left off deliberately — those are maintainer calls. Happy to pick this up once the decisions in "Design decisions to settle first" are made.