Work Log: Flyway Adoption for Sztab Database Migrations

Scope

Introduce Flyway as the standard mechanism for versioned PostgreSQL schema migrations across all Sztab environments: developer machines, CI, internal test deployments, and customer releases.

Estimated Time: 8–10 hours

Task Breakdown

1. Add Flyway Dependencies and Configuration (0.5h)

• Add Flyway starter to backend pom.xml.
• Configure spring.flyway.* properties for dev/test/prod.
• Verify Flyway auto-detection of migration folder.

2. Generate and Normalize Baseline Migration (2h)

• Extract current PostgreSQL schema using pg_dump (schema-only).
• Clean and reorder SQL for deterministic Flyway application.
• Save as V1__baseline.sql under src/main/resources/db/migration/.
• Validate that the baseline matches existing live schemas.

3. Convert Existing Bootstrapping SQL (1h)

• Move any schema creation logic from schema.sql, test scaffolding, or initializer code into structured Flyway migration files.
• Remove redundant manual schema creation steps.

4. Disable Hibernate Auto-DDL (0.5h)

• Set spring.jpa.hibernate.ddl-auto=none across all profiles.
• Verify application boot without schema-generation warnings.

5. Update Integration Tests to Use Flyway (1h)

• Ensure Docker Compose PostgreSQL container is initialized via Flyway.
• Confirm migrations run before Spring Boot context loads in tests.
• Fix tests that implicitly relied on auto-DDL behavior.

6. Validate Full Workflow (2h)

• Local run: wipe DB, start app, verify migrations applied.
• CI run: migrations applied before backend tests.
• Deployment run: verify migrations executed in controlled order.

7. Write Team Migration Workflow Documentation (1h)

• How to create VXXX__description.sql migrations.
• Naming conventions and versioning rules.
• Branching model: how to handle parallel features requiring schema changes.
• Guidelines for SQL quality (idempotency, constraints, index naming, etc.).

Expected Outcomes

• Full elimination of schema drift during development.
• Deterministic, version-controlled DB evolution across environments.
• Cleaner CI runs with reproducible schema setup.
• Predictable customer upgrades with packaged migration sets.
• Removal of Hibernate auto-DDL reliance.

Notes

• Migration workflow must become a mandatory step for all schema-affecting changes.
• Future releases will include corresponding Flyway version ranges in release notes.
• Team should update integration test harness to ensure Flyway is always executed cleanly.

Flyway Implementation Notes:

Sztab Database Schema Workflow (Hibernate => Baseline => Flyway)

This document describes the authoritative workflow for managing the Sztab database schema in a reproducible, team-friendly, and production-safe manner.

The core principle is clear ownership:

• Hibernate defines the intended schema (model).
• Flyway owns the history and creation of the schema.
• The database is never an implicit artifact.

Goals

• Deterministic database startup across all environments
• Reproducible docker-compose up for all team members
• Safe schema evolution without hidden mutations
• Clean separation between model definition and schema history

Phase 1: Feature Development (Pre-Freeze)

During active feature development (e.g. Issue Management, Pull Requests):

• Hibernate DDL generation may be enabled for rapid iteration.
• Database instances are considered ephemeral.
• Schema changes are not yet versioned.

Typical configuration:

spring:
  jpa:
    hibernate:
      ddl-auto: update

Notes:

• Databases may be dropped and recreated freely.
• This phase is explicitly not reproducible across machines.
• No schema artifacts are committed to git yet.

Phase 2: Schema Freeze (Baseline Creation)

Once the final structural feature is complete:

1. Start with a fresh database.
2. Let Hibernate generate the complete schema from entities.
3. Inspect the resulting database structure.
4. Export the schema (structure only):

pg_dump \
  --schema-only \
  --no-owner \
  --no-privileges \
  sztabdb > schema.sql

5. Create a Flyway baseline migration:

db/migration/
  V1__baseline.sql

6. Paste the exported schema into V1__baseline.sql.
7. Commit this file to git.

This file represents:

• The canonical Sztab DB v1
• The starting point for all future environments

Phase 3: Ownership Transfer (Post-Freeze)

After the baseline is committed:

• Hibernate must no longer mutate schema
• Flyway becomes the sole authority for schema creation and evolution

Configuration is updated to:

spring:
  flyway:
    enabled: true
    baseline-on-migrate: true
    locations: classpath:db/migration

  jpa:
    hibernate:
      ddl-auto: validate

Important:

• ddl-auto=update or create is no longer allowed.
• spring.jpa.defer-datasource-initialization must be disabled.
• Hibernate now only validates schema correctness.

Phase 4: Application Startup Order (Authoritative)

The startup lifecycle is strictly:

Step 1) Database container starts

Step 2) Flyway runs:

• Executes V1__baseline.sql (if new DB)
• Applies incremental migrations

Step 3) Hibernate starts:

• Validates schema against entity model

Step 4) Application starts

Hibernate never runs before Flyway. Hibernate never modifies schema after the freeze.

Phase 5: Incremental Schema Evolution

All future schema changes are expressed as Flyway migrations:

V2__add_issue_comments.sql
V3__add_issue_audit_log.sql
V4__add_global_issue_number.sql

Rules:

• Migrations are append-only.
• Old migrations are never modified.
• Data backfills are allowed and explicit.
• Each migration represents an intentional change.

Development Guidelines

• For experimentation:

  • Use scratch databases.
  • Enable Hibernate DDL temporarily.
  • Never commit experimental schemas.

• For shared environments:

  • Always start from Flyway baseline + migrations.
  • Never rely on runtime Hibernate mutations.

Core Rule (Summary)

Hibernate may generate the schema once.
After the baseline is frozen, only Flyway may change it.

This workflow ensures:

• Predictable deployments
• Reproducible local setups
• Safe long-term schema evolution