Flyway migration — DB schema versioning

Vấn đề `ddl-auto=update`:

1. Không version: Hibernate diff entity vs DB tại runtime — không có audit trail "schema này version N".
2. Không drop / rename: add column OK, drop column / rename không. Schema dirty.
3. Không change type safely: VARCHAR(50) → VARCHAR(100) ok. INTEGER → BIGINT phức tạp tuỳ DB.
4. Race condition: 100 pod startup concurrent → multiple ALTER TABLE → conflict.
5. Inconsistent giữa Hibernate version: behavior thay đổi unpredictably.
6. Khó rollback: không có concept "undo".
7. Test khó: schema phụ thuộc Hibernate parsing entity — không reproducible.

Flyway giải quyết:

1. Versioned migration: mỗi script = 1 version. Apply once, in order, recorded in flyway_schema_history.
2. Concurrent-safe: Flyway acquire DB lock — 100 pod startup, chỉ 1 apply, rest wait.
3. Audit trail: flyway_schema_history log every change with timestamp + user.
4. Reproducible: SQL script = source of truth. Apply same → same result mọi env.
5. Pre-deploy verify: CI run migration trên fresh DB → catch error trước production.
6. Forward-only rollback: bug = new migration revert. Audit clear.
7. Multi-DB support: Flyway abstract dialect — dev local SQLite, prod Postgres.

Pattern combine:

spring:
flyway:
  enabled: true
  locations: classpath:db/migration
  baseline-on-migrate: true
jpa:
  hibernate:
    ddl-auto: validate            # KHONG update — rely Flyway

Workflow:

1. Dev local add entity field → write Flyway migration.
2. Apply local Postgres → entity validate ok.
3. Commit + PR review.
4. CI: fresh Postgres + apply all migration + run test.
5. Deploy staging: Flyway apply pending migration on startup.
6. Verify staging.
7. Deploy production: same flow.

Production safe + auditable + rollback-friendly.

1. Multiple unrelated changes: add column projects + create new table audit_log + insert config — 3 logical change. Fail debug khó.

   Fix: tách thành 3 migration:

   V5__add_project_priority_owner.sql      # column + constraint cho projects
   V6__create_audit_log_table.sql           # table audit_log
   V7__seed_initial_config.sql              # data seed

   1 migration = 1 logical change. Easy review, easy rollback.
2. Add NOT NULL column với existing rows:
   ALTER TABLE projects ADD COLUMN priority VARCHAR(20);  -- nullable, default null
   UPDATE projects SET priority = 'MEDIUM';                -- backfill

   Workflow OK trong 1 migration nhưng risk nếu fail giữa chừng. Better: NOT NULL với default value:
   ALTER TABLE projects ADD COLUMN priority VARCHAR(20) NOT NULL DEFAULT 'MEDIUM';

   Postgres backfill atomic + immediate. No two-step.
3. Hardcode env-specific data:
   INSERT INTO config VALUES ('admin_email', 'admin@local');

   Local email apply prod = wrong. Should env-specific.

   Fix: tách dev seed:

   # db/migration/         (chung)
   # db/migration/dev/V100__seed_dev_config.sql
   INSERT INTO config VALUES ('admin_email', '[email protected]');
   
   # db/migration/prod/V100__seed_prod_config.sql
   INSERT INTO config VALUES ('admin_email', '${ADMIN_EMAIL_PLACEHOLDER}');
   
   # application-dev.yml
   spring.flyway.locations:
   - classpath:db/migration
   - classpath:db/migration/dev

   Hoặc dùng Flyway placeholder substitution.

Bonus issues:

• FK constraint fail nếu users table chưa có: verify migration order (V<5 must create users).
• audit_log không có index: add CREATE INDEX idx_audit_timestamp ON audit_log(timestamp DESC) cho query log gần đây.
• id BIGINT PRIMARY KEY without auto-gen: change to BIGSERIAL.

Code đúng (3 migration):

-- V5__add_project_priority_owner.sql
ALTER TABLE projects ADD COLUMN priority VARCHAR(20) NOT NULL DEFAULT 'MEDIUM';
ALTER TABLE projects ADD COLUMN owner_id BIGINT;
ALTER TABLE projects ADD CONSTRAINT fk_owner FOREIGN KEY (owner_id) REFERENCES users(id);
CREATE INDEX idx_projects_priority ON projects(priority);

-- V6__create_audit_log_table.sql
CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  action VARCHAR(50) NOT NULL,
  timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_audit_timestamp ON audit_log(timestamp DESC);

-- db/migration/dev/V100__seed_dev_config.sql
INSERT INTO config VALUES ('admin_email', '[email protected]');

Vấn đề:

# Dev A: 10:00 commit
db/migration/V5__add_priority.sql

# Dev B: 10:05 commit
db/migration/V5__add_owner.sql

# Merge conflict — same filename

Hoặc tệ hơn: merge có 2 file V5 khác nhau → Flyway error "Duplicate version 5".

3 strategy fix:

1. Timestamp naming (recommend cho team lớn):

V20260415_1000__add_priority.sql
V20260415_1005__add_owner.sql

Pros:

• No conflict — timestamp unique.
• Order natural — chronological.
• Version evident — "this migration from 2026-04-15".

Cons:

• Filename longer.
• Out-of-order risk: dev local apply 10:05 first, 10:00 sau → Flyway warn "out of order".

Configuration:

spring:
flyway:
  out-of-order: true     # cho phep apply out of order (DEV only)

2. Sequential numbering với coordination:

Team agree "next available version", communicate qua Slack/Linear:

# Dev A: claim V5
git checkout -b feature-priority
echo "V5__add_priority.sql" > db/migration/V5__add_priority.sql

# Dev B: claim V6 (after A merges hoặc after channel agree)
git checkout -b feature-owner
echo "V6__add_owner.sql" > db/migration/V6__add_owner.sql

Pros: clean sequential. Cons: communication overhead, race condition vẫn có thể.

3. Pre-merge hook check:

# .github/workflows/migration-check.yml
- name: Check duplicate Flyway version
run: |
  files=$(ls src/main/resources/db/migration/V*__*.sql | sed 's/.*V\([0-9]*\)__.*/\1/')
  duplicates=$(echo "$files" | sort | uniq -d)
  if [ -n "$duplicates" ]; then
    echo "Duplicate versions: $duplicates"
    exit 1
  fi

CI fail merge với duplicate version. Force resolve trước merge.

Recommend cho TaskFlow capstone:

• Solo dev / small team (1-3): sequential V1, V2, V3.
• Team lớn (5+) hoặc microservices: timestamp.

Workflow merge conflict (sequential):

1. Dev A merge first → V5 in main.
2. Dev B rebase: file conflict → rename V5 → V6.
3. Push, PR, merge.

Manual rebase 30 giây — acceptable cost.

Pattern industry:

• GitHub, GitLab use sequential.
• Stripe, Shopify use timestamp.
• Atlas (Liquibase competitor) auto-detect và sort.

Vấn đề:

• CREATE INDEX Postgres default acquire SHARE lock — block writes.
• 30 phút lock → INSERT/UPDATE order block → app downtime.
• Pod startup hold migration lock → 99 pod khác wait.
• K8s readiness probe fail → restart loop.

Fix 1 — Postgres `CREATE INDEX CONCURRENTLY`:

-- V10__add_status_index.sql
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

Postgres `CONCURRENTLY` mode:

• Không acquire lock writes.
• Build index trong background — slow hơn (~2x duration).
• Insert/update vẫn run concurrent.

Caveat Flyway: `CONCURRENTLY` không thể chạy trong transaction. Mặc định Flyway wrap mỗi migration trong tx.

-- Flyway error: "CREATE INDEX CONCURRENTLY cannot run inside a transaction block"

Solution:

spring:
flyway:
  postgresql:
    transactional-lock: false   # Postgres-specific config Flyway 8+

# Hoac dung Flyway placeholder
-- /* @flyway:transactional false */
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

Fix 2 — Maintenance window:

1. Schedule maintenance: Saturday 02:00 (low traffic).
2. Disable writes: read-only mode app.
3. Run migration manually qua psql:
   psql prod -f V10__add_status_index.sql

4. Mark Flyway history insert manually:
   INSERT INTO flyway_schema_history (...) VALUES (10, '10', 'add status index', 'SQL', ...);

5. Deploy app — Flyway thấy V10 đã applied, skip.

Pros: full control. Cons: manual, error-prone.

Fix 3 — Online migration tool:

• gh-ost (GitHub for MySQL) — schema change online.
• pg_repack (Postgres) — reorganize table without lock.
• Liquibase Pro — automated zero-downtime.

Tools build for large-scale schema change. Overhead setup.

Recommend cho TaskFlow:

• Default Postgres: use `CONCURRENTLY` cho all index creation in production migration.
• Maintenance window: for major refactor (drop/rename column with data) on huge tables.
• Plan migration carefully: measure on staging với production-size data.

Ngừa từ đầu:

• Add index trong V1 init schema — no data, fast.
• Monitor query performance từ early — add index trước table grow lớn.
• Use partial index nếu phù hợp: CREATE INDEX ... WHERE status = 'ACTIVE' — smaller, faster build.

Vì sao:

Flyway compute MD5 checksum của mỗi migration script lúc apply. Lưu vào flyway_schema_history.checksum:

SELECT version, description, checksum FROM flyway_schema_history;
-- 5  add priority  -1234567890

Restart app:

1. Flyway scan db/migration/ → V5 file.
2. Compute checksum hiện tại.
3. Compare với history — checksum khác (do file modified).
4. Throw FlywayException: Migration checksum mismatch for migration version 5.
5. App fail to start.

Vì sao Flyway strict:

• Modified migration → app A start ok (apply original), app B start fail (load modified).
• Inconsistent state across env.
• Audit trail broken — DB schema không match commit history.

Cách recover (3 options):

Option 1 (correct) — Revert + new migration:

# Git revert V5 to original content
git checkout HEAD~1 -- src/main/resources/db/migration/V5__add_priority.sql

# Add new migration with desired change
echo "ALTER TABLE projects ADD COLUMN priority_extra VARCHAR(20);" > V6__add_priority_extra.sql

# Commit + deploy

Pattern: forward-only. Original V5 stay, new V6 add change. Production safe.

Option 2 — Flyway repair (production careful):

# 1. Export current DB schema (backup)
pg_dump prod > backup.sql

# 2. Update flyway_schema_history checksum manually
mvn flyway:repair \
-Dflyway.url=jdbc:postgresql://prod-db/app

# Hoac SQL direct:
UPDATE flyway_schema_history SET checksum = NULL WHERE version = '5';
-- Then Flyway recompute on next run

Recompute checksum để match modified file. **Risky** — DB state có thể không match script content.

Option 3 — Modify history table directly (NEVER recommend):

UPDATE flyway_schema_history SET checksum = <new_checksum> WHERE version = '5';

Hack quick fix. Lose audit. Don't.

Pre-deploy check (CI):

# .github/workflows/ci.yml
- name: Verify migration not modified
run: |
  # Run migration on fresh DB
  mvn flyway:migrate
  # Run on existing DB (production-like)
  mvn flyway:validate -Dflyway.url=staging-db   # fail nếu checksum mismatch

Quy tắc vàng:

1. Never modify migration đã merge to main.
2. Modify migration trên feature branch (chưa merge) — OK, applied chỉ local.
3. Need change after merge → forward migration mới.
4. Disaster recovery: option 1 hoặc 2 với backup.

Pre-merge checklist:

• Migration tested local: apply, rollback (manual), reapply.
• Migration tested staging: real data, Hibernate validate ok.
• PR review by 2 dev: SQL review + impact assessment.
• Merge → no modification thereafter.

Use case repeatable — DB views:

-- R__create_active_projects_view.sql
DROP VIEW IF EXISTS active_projects;

CREATE VIEW active_projects AS
SELECT
  p.id, p.name, p.status, p.created_at,
  COUNT(t.id) AS task_count,
  SUM(CASE WHEN t.status = 'DONE' THEN 1 ELSE 0 END) AS done_count
FROM projects p
LEFT JOIN tasks t ON t.project_id = p.id
WHERE p.status IN ('ACTIVE', 'PLANNING')
GROUP BY p.id, p.name, p.status, p.created_at;

Workflow:

1. Initial deploy: R script apply, view created.
2. Need add column to view: edit R file.
3. Next deploy: Flyway compute checksum changed → DROP + CREATE.
4. No new V version needed — same file modified.

Use case stored procedure:

-- R__function_update_audit.sql

CREATE OR REPLACE FUNCTION log_project_change()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, project_id, changed_at)
  VALUES ('projects', TG_OP, NEW.id, NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_project_change ON projects;
CREATE TRIGGER trg_project_change
  AFTER INSERT OR UPDATE OR DELETE ON projects
  FOR EACH ROW EXECUTE FUNCTION log_project_change();

Modify function logic → re-deploy → new function definition.

Lợi ích:

• Source of truth: view/procedure define trong git, applied automatically.
• No version proliferation: 100 lần modify view = 100 V migration if not repeatable. R = 1 file.
• Reload automatic: deploy → checksum compare → re-run if changed.

Caveat:

• R cần idempotent: DROP IF EXISTS + CREATE hoặc CREATE OR REPLACE.
• R run AFTER all V applied — order: V1, V2, ..., Vn, R1, R2, R3 (alphabetical).
• R không change schema_history (no version column).

Pattern enterprise:

• Schema (tables, columns, indexes) → V migration.
• Computed views, materialized views → R migration.
• Stored procedure, function, trigger → R migration.
• Reference data (seed) → V migration (immutable history).

Workflow baseline existing DB:

Step 1 — Snapshot current schema:

# Export schema (no data) từ production
pg_dump --schema-only prod > current-schema.sql

Output 100 table DDL, ~5000 dòng.

Step 2 — Tạo V1 baseline migration:

# src/main/resources/db/migration/V1__baseline.sql
# Copy nội dung current-schema.sql

-- This file is informational only
-- Existing schema as of 2026-04-15
-- Flyway baseline mode: DO NOT actually run this on existing DB

CREATE TABLE projects (...);
CREATE TABLE tasks (...);
-- ... 98 more tables

File này document schema cho new env (test, new dev local) — apply từ đầu. Production existing skip qua baseline mechanism.

Step 3 — Configure baseline mode:

spring:
flyway:
  enabled: true
  baseline-on-migrate: true
  baseline-version: 1
  baseline-description: "Existing schema as of 2026-04-15"
  locations: classpath:db/migration

Step 4 — First deploy production:

1. App start.
2. Flyway connect DB.
3. Detect flyway_schema_history không có.
4. baseline-on-migrate: true → tạo table + insert baseline row:
   INSERT INTO flyway_schema_history (version, description, type, ...)
   VALUES (1, 'Existing schema as of 2026-04-15', 'BASELINE', ...);

5. Flyway compare: V1 trong files vs V1 trong history (baseline) → V1 marked applied.
6. No pending migration. App start ok.

Production existing DB chuyển sang Flyway managed — không apply V1 (vì đã exist), nhưng track từ V2+.

Step 5 — Future migration:

# Add new feature: column priority
db/migration/V2__add_project_priority.sql
ALTER TABLE projects ADD COLUMN priority VARCHAR(20) NOT NULL DEFAULT 'MEDIUM';

# Deploy
# Flyway: baseline V1 đã applied, V2 pending → apply V2 → record history

Step 6 — New env (test, fresh dev):

# Empty DB
# Deploy app
# Flyway: no history → no baseline detect → apply V1 (full schema) + V2 + ...

New env apply cả V1 (init schema) lẫn V2+. Eventual consistency.

Caveat — mixed env:

• Production: V1 baselined, V2+ applied.
• New dev DB: V1 applied (full DDL), V2+ applied.
• End state same — schema match.

Pitfall avoid:

1. V1 không idempotent: mọi CREATE TABLE phải IF NOT EXISTS. Nếu app accidentally run V1 trên existing DB → fail. Defensive.
2. Schema diff giữa env: production và new env có thể khác micro detail (datetime precision, collation). Snapshot từ production canonical.
3. Index naming: existing indexes có name auto-generated by ORM. Snapshot include actual names → V1 reproduce.

Verify:

# Test: spin up fresh Postgres, apply all migration
docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=test postgres:16
mvn flyway:migrate -Dflyway.url=jdbc:postgresql://localhost/test

# Compare schema with production
pg_dump --schema-only test > test-schema.sql
diff test-schema.sql current-schema.sql      # nen empty

Identical → baseline + V1 work. Different → fix V1 to match production exactly.

Pattern enterprise migrate to Flyway:

• Phase 1: V1 baseline + Flyway enabled.
• Phase 2 (next sprint): start adding V2+ for new changes.
• Phase 3 (after team comfort): retrofit existing change history if compliance needs.