Background

Sztab’s database schema is now evolving alongside the application. New features routinely introduce changes to tables, constraints, indexes, and seed data. Until now, schema changes have been handled informally during development, and the risk is growing:

• Pulling a new branch may require manual DB fixes.
• Local development environments drift from one another.
• CI integration tests occasionally fail due to schema mismatches.
• Release builds require manual coordination to apply schema updates.

As Sztab matures, we need a consistent, deterministic approach to database evolution for both day-to-day development and formal customer releases.

Problem Statement

Sztab currently lacks a unified mechanism to version, apply, and promote database schema changes. Hibernate auto-DDL is unsuitable for production and unreliable for repeatable evolution. Manual SQL execution is error-prone and does not scale across environments (dev => test => staging => prod). Without a migration system, schema drift becomes inevitable.

Proposal

Adopt Flyway as the standard tool for all Sztab database migrations.

Flyway provides:

• Versioned SQL files checked into source control
• Deterministic, forward-only schema evolution
• Automatic migrations on startup in development and CI
• Controlled migrations through pipelines for customer deployments
• Portable, readable SQL that fully exposes PostgreSQL features

This approach ensures every environment—developer laptops, automated tests, internal deployments, and external customers—runs the exact same sequence of schema updates.

Scope

This issue proposes adopting Flyway for two use cases:

1. Major Customer Releases

For releases shipped to clients or external environments:

• Each release includes a set of new migration files (e.g., V104__add_review_requirements.sql).
• Deploy pipelines apply migrations before or during application rollout.
• Schema and code remain synchronized across all customer deployments.
• Release notes list migration versions included in the build.

This provides predictable upgrades and eliminates schema-related outages.

2. Incremental Development Releases

During active team development:

• Every schema-affecting change requires a corresponding Flyway migration file.
• Local application startup automatically applies migrations.
• Switching branches becomes reliable, since Flyway tracks executed versions.
• CI integration tests always run against the correct schema.
• Developers avoid manual database modifications or Hibernate auto-DDL.

This keeps everyone on a consistent schema and significantly reduces debugging time.

Implementation Plan

1. Add Flyway dependency and configuration to the backend module.
2. Create a src/main/resources/db/migration/ folder in the backend project.
3. Disable Hibernate auto-DDL in all environments.
4. Convert current schema definition into an initial baseline migration.
5. Begin adding new migrations for all subsequent schema changes.
6. Document migration workflow for feature branches and releases.
7. Integrate Flyway execution into CI and deployment pipelines.

Expected Outcomes

• Stable and reproducible database evolution across all environments.
• Fewer integration test failures related to schema drift.
• Clear visibility into when and how schema changes occur.
• Safer customer upgrades and rollouts.
• Reduced confusion during feature development and branching.