The Complete Production SOP: How to Deploy and Run Flyway Reliable Versioned SQL Database Migrations in Autonomous AI Agents
Step-by-step SOP to install and run Flyway Reliable Versioned SQL Database Migrations in Codex, Claude Code, and Cursor. Fix checksums and automate schema drift.
Technical Overview & Architectural Context
Autonomous coding agents frequently corrupt database release pipelines when generating incremental schema changes. LLMs commonly produce non-deterministic filename conventions (such as single-underscore prefixes or timestamp collisions), alter previously executed immutable migration files in place, or fail to account for RDBMS-specific transaction DDL capabilities (e.g., PostgreSQL transactional DDL versus MySQL implicit commit on DDL). Without deterministic boundaries, agents attempt ad-hoc ALTER TABLE executions directly against running databases, triggering checksum mismatches, corrupting the flyway_schema_history tracking table, and causing catastrophic production deployment halts. The 'flyway-versioned-database-migrations' skill enforces strict procedural boundaries across Codex, Claude Code, Cursor, and Windsurf. It establishes rigid naming topologies (V<Version>__<Description>.sql for versioned migrations, R__<Description>.sql for repeatable migrations), binds execution order, enforces baseline-on-migrate policies for legacy transitions, and automates checksum repair protocols. By grounding the agent within an explicit schema-as-code operational sequence, teams prevent destructive schema drift and maintain audited, reproducible database states across CI/CD pipelines.
Prerequisites & Environment Requirements
Verified Search Intents & Long-Tail Queries Solved
This operational tutorial is engineered to provide deterministic solutions for these developer search queries:
Step-by-Step Execution Playbook
Local Environment Setup & SKILL.md Mounting
Mount the deterministic Flyway skill manifest directly into your project's agent skills directory.
Autonomous coding assistants require structured metadata to invoke precise domain rules over default generative tendencies. Mount the SKILL.md specification into your project or user-level agent directory (.agents/skills/ or .cursor/rules/) so the engine parses trigger keywords and restricts file modifications strictly to the src/main/resources/db/migration tree.
mkdir -p .agents/skills/flyway-versioned-database-migrations && cat <<'EOF' > .agents/skills/flyway-versioned-database-migrations/SKILL.md
---
name: "flyway-versioned-database-migrations"
description: "Automate version-controlled relational database migrations across environments with Flyway."
metadata:
author: "Redgate"
version: "1.0.0"
runtime_compatibility:
- "codex"
- "claude-code"
- "cursor"
- "windsurf"
triggers:
- "create flyway migration script"
- "repair flyway checksums"
- "baseline on migrate flyway"
permissions:
fs_read: true
fs_write: "workspace"
shell_exec: false
net_access: false
---
# Flyway Reliable Versioned SQL Database Migrations
Configures Flyway: naming conventions for versioned SQL migrations (V1__init.sql) and repeatable scripts (R__views.sql), running migrations on startup via Spring Boot / Quarkus, baseline-on-migrate, and repairing checksums.
## When to Invoke
Invoke this skill whenever you need to:
- create flyway migration script
- repair flyway checksums
- baseline on migrate flyway
## Execution SOP & Rules
1. Name versioned migrations: V<Major>_<Minor>__<description>.sql using double underscores.
2. Name repeatable migrations: R__<description>.sql (for views, functions, stored procedures).
3. NEVER edit a previously applied migration script; always create a new incremental version.
4. Configure application.yml: spring.flyway.baseline-on-migrate=true, spring.flyway.validate-on-migrate=true.
5. Inspect flyway_schema_history table before performing manual schema interventions.
EOF---
name: "flyway-versioned-database-migrations"
description: "Automate version-controlled relational database migrations across environments with Flyway."
metadata:
author: "Redgate"
version: "1.0.0"
runtime_compatibility:
- "codex"
- "claude-code"
- "cursor"
- "windsurf"
triggers:
- "create flyway migration script"
- "repair flyway checksums"
- "baseline on migrate flyway"
permissions:
fs_read: true
fs_write: "workspace"
shell_exec: false
net_access: false
---Keep the SKILL.md under 40 lines. Runtimes inject active skill frontmatter into initial token windows; limiting instructions to deterministic naming rules saves hundreds of system prompt tokens per execution turn.
Ensure the agent's workspace configuration gives read access to src/main/resources/db/migration/ so it can discover the current highest version number before creating the next file.
Application Configuration & Migration Scaffold
Configure deterministic Flyway properties in your Spring Boot or Quarkus configuration files.
To prevent migrations from running against uninitialized schemas or failing during cold starts, explicitly configure baseline settings and target locations within your application properties. This guarantees the AI agent works with predictable paths and fail-safe defaults.
cat <<'EOF' > src/main/resources/application.yml
spring:
datasource:
url: ${SPRING_DATASOURCE_URL:jdbc:postgresql://localhost:5432/app_db}
username: ${SPRING_DATASOURCE_USERNAME:postgres}
password: ${SPRING_DATASOURCE_PASSWORD:postgres}
flyway:
enabled: true
baseline-on-migrate: true
baseline-version: 0
locations:
- classpath:db/migration
validate-on-migrate: true
out-of-order: false
EOFAlways set spring.flyway.out-of-order: false in production environments to prevent developers and agents from inserting past versions out of topological order.
Do not hardcode database credentials in application.yml. Use environment variable placeholders matching your local test containers or production secret injectors.
Intent Matching & Trigger Invocation
Prompt the agent using frontmatter-aligned triggers to generate compliant schema increments.
Invoke the skill using explicit trigger statements such as 'create flyway migration script' alongside your schema requirements. The runtime pattern-matcher activates the skill and instructs the agent to audit the highest existing migration file (e.g., V2__add_orders.sql) and generate the subsequent increment (V3__add_payment_methods.sql) using exact double-underscore syntax.
Include the target database engine (e.g., PostgreSQL 16) in the prompt so the agent emits dialect-accurate syntax like CREATE INDEX CONCURRENTLY or JSONB operators.
Agents often default to single-underscore file naming (e.g., V3_add_payment.sql). The skill explicitly enforces V<version>__<description>.sql; verify the generated filename adheres strictly.
Verification, Linting & Checksum Validation Gate
Execute local verification checks and dry-run migrations to validate SQL syntax and checksum integrity.
Before committing changes generated by the autonomous agent, run Flyway's info and validate goals using the build tool wrapper. This detects syntax errors, conflicting versions, or unapplied checksum discrepancies before code reaches CI/CD.
./mvnw flyway:info flyway:validateIntegrate flyway:validate into your pre-commit hooks or local CI script to block pull requests containing modified historical migrations automatically.
If using in-memory databases like H2 for tests, ensure Flyway scripts do not contain PostgreSQL- or MySQL-exclusive procedural extensions unless H2 compatibility mode is explicitly set.
Real-World Scenario: Add Versioned Customer Audit Table and Repeatable Materialized View
“create flyway migration script to add an audit_logs table with UUID primary key, actor_id, action VARCHAR(50), payload JSONB, and created_at TIMESTAMPTZ default NOW(). Also generate a repeatable flyway migration script for refreshing customer summary stats view.”
Runtime Troubleshooting & Failure Modes
Root Cause: An agent or engineer modified an existing, already applied SQL migration file (e.g., altering a column definition in V1__init.sql instead of generating a new incremental migration). Flyway detects that the SHA-256 hash in flyway_schema_history does not match the file on disk.
./mvnw flyway:repair
# Or, if intentional in development: revert the historical file and create a new incremental migration (e.g., V4__alter_column.sql)Root Cause: Two developers or parallel agent branches generated migrations with the same version prefix (e.g., V3__add_index.sql and V3__add_column.sql) merged into the same target branch.
git status
# Renumber the conflicting migration to the next sequential version:
mv src/main/resources/db/migration/V3__add_column.sql src/main/resources/db/migration/V4__add_column.sql
./mvnw flyway:infoFrequently Asked Questions (Flyway Reliable Versioned SQL Database Migrations SOP)
Q:Can I use Flyway Reliable Versioned SQL Database Migrations across Codex, Claude Code, and Cursor simultaneously?
Yes. The skill conforms to the universal SKILL.md specification supported across all modern agent runtimes. By placing the manifest in your shared repository (.agents/skills/ or root rules), any compatible agent reads the same triggers, naming rules, and verification checklists.
Q:How does Flyway Reliable Versioned SQL Database Migrations prevent context window overflow during long agent sessions?
By establishing concise procedural boundaries and deterministic validation gates rather than polluting system prompts with redundant rules. The agent only receives instructions on file naming patterns (V vs R), checksum handling, and command execution when the explicit triggers are matched.
Q:Does this skill require escalated root or container permissions?
No. It is audited with a security score of 98/100 and runs safely within workspace-scoped sandboxes. The skill operates via standard project build wrappers (mvnw/gradlew) and file writes within the project's src/main/resources/db/migration directory.
Ready to equip your autonomous agent with Flyway Reliable Versioned SQL Database Migrations?
Copy the complete audited SKILL.md specification or download the 1-click zip bundle directly from our verified registry.