Overview
This system provides intelligent database migration management that combines Flyway’s schema versioning with sophisticated record tracking and dependency-aware rollback capabilities. It enables safe application of data scenarios with automatic cleanup and comprehensive audit trails.
Core Architecture
Hybrid Migration Approach
The system uses Flyway for schema migrations while implementing custom record tracking for data operations. This hybrid approach provides:
- Transaction Safety: Leveraging Flyway’s all-or-nothing guarantees
- Version Control: Standard Flyway versioning for migration files
- Smart Tracking: Every data operation is tracked with metadata
- Intelligent Rollback: Dependency-aware cleanup that goes beyond standard Flyway capabilities
Example Scenario: E-commerce Platform
Let’s explore how this works with a realistic e-commerce scenario involving multiple related tables with diverse data types:
Business Domain Tables
-- Customers table with various data types
CREATE TABLE customers (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) UNIQUE NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
phone VARCHAR(20),
date_of_birth DATE,
registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
credit_limit DECIMAL(10,2) DEFAULT 1000.00,
is_premium BOOLEAN DEFAULT FALSE,
preferences JSONB,
last_login TIMESTAMP,
status VARCHAR(20) DEFAULT 'ACTIVE'
);
-- Products table with rich metadata
CREATE TABLE products (
id SERIAL PRIMARY KEY,
sku VARCHAR(50) UNIQUE NOT NULL,
name VARCHAR(200) NOT NULL,
description TEXT,
category_path VARCHAR(500), -- e.g., 'Electronics/Smartphones/iPhone'
price DECIMAL(10,2) NOT NULL,
cost DECIMAL(10,2),
weight_kg DECIMAL(8,3),
dimensions JSONB, -- {"length": 15.5, "width": 7.5, "height": 0.8}
inventory_count INTEGER DEFAULT 0,
tags TEXT[], -- Array of tags
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE
);
-- Orders table (1:N relationship with customers)
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id UUID NOT NULL REFERENCES customers(id),
order_number VARCHAR(50) UNIQUE NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(12,2) NOT NULL,
tax_amount DECIMAL(10,2),
shipping_amount DECIMAL(8,2),
status VARCHAR(30) DEFAULT 'PENDING',
shipping_address JSONB,
billing_address JSONB,
payment_method VARCHAR(50),
notes TEXT,
estimated_delivery DATE
);
-- Order Items table (N:M relationship between orders and products)
CREATE TABLE order_items (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id),
product_id INTEGER NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
discount_percent DECIMAL(5,2) DEFAULT 0.00,
line_total DECIMAL(12,2) NOT NULL,
product_snapshot JSONB -- Captures product details at time of order
);
-- Inventory Transactions table
CREATE TABLE inventory_transactions (
id BIGSERIAL PRIMARY KEY,
product_id INTEGER NOT NULL REFERENCES products(id),
transaction_type VARCHAR(20) NOT NULL, -- 'INBOUND', 'OUTBOUND', 'ADJUSTMENT'
quantity_change INTEGER NOT NULL,
reference_type VARCHAR(20), -- 'ORDER', 'RETURN', 'ADJUSTMENT'
reference_id BIGINT,
transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
reason VARCHAR(200),
performed_by VARCHAR(100)
);
Tracking Infrastructure
Record Tracker Table
┌──────────────────────────────────────────────────────────────────────────────────────┐
│ demo_records │
├──────────────────┬─────────────────┬──────────────────┬────────────────────────────────┤
│ id │ table_name │ record_id │ scenario_name │
│ UUID PK │ VARCHAR(100) │ VARCHAR(255) │ VARCHAR(255) │
│ AUTO GENERATED │ NOT NULL │ NOT NULL │ NOT NULL │
├──────────────────┼─────────────────┼──────────────────┼────────────────────────────────┤
│ migration_version│ created_at │ record_data │ operation_log_id │
│ VARCHAR(50) │ TIMESTAMP │ JSONB │ UUID FK → operation_log(id) │
│ NOT NULL │ DEFAULT NOW() │ NULLABLE │ NULLABLE │
└──────────────────┴─────────────────┴──────────────────┴────────────────────────────────┘
│ │
│ UNIQUE INDEX: (table_name, record_id, scenario_name) │
│ PURPOSE: Prevents duplicate tracking of same record in same scenario │
└────────────────────────────────────────────────────────────────────────────────────────┘
Example Records:
┌─────────────────────────┬─────────────┬─────────────────┬─────────────────┬──────────────┐
│ id │ table_name │ record_id │ scenario_name │ record_data │
├─────────────────────────┼─────────────┼─────────────────┼─────────────────┼──────────────┤
│ 550e8400-e29b-41d4-... │ customers │ 123e4567-e89b..│ ecommerce-demo │ {"email":... │
│ 6ba7b810-9dad-11d1-... │ products │ 1001 │ ecommerce-demo │ {"sku":"... │
│ 6ba7b811-9dad-11d1-... │ orders │ 2001 │ ecommerce-demo │ {"total":... │
└─────────────────────────┴─────────────┴─────────────────┴─────────────────┴──────────────┘
Operation Log Table
┌──────────────────────────────────────────────────────────────────────────────────────┐
│ operation_log │
├──────────────────┬─────────────────┬──────────────────┬────────────────────────────────┤
│ id │ scenario_name │ operation_type │ migration_version │
│ UUID PK │ VARCHAR(255) │ VARCHAR(50) │ VARCHAR(50) │
│ AUTO GENERATED │ NOT NULL │ NOT NULL │ NOT NULL │
├──────────────────┼─────────────────┼──────────────────┼────────────────────────────────┤
│ status │ records_affected│ started_at │ completed_at │
│ VARCHAR(20) │ INTEGER │ TIMESTAMP │ TIMESTAMP │
│ DEFAULT 'PENDING'│ DEFAULT 0 │ DEFAULT NOW() │ NULLABLE │
├──────────────────┼─────────────────┼──────────────────┼────────────────────────────────┤
│ error_message │ execution_time │ metadata │ │
│ TEXT │ INTEGER (ms) │ JSONB │ │
│ NULLABLE │ NULLABLE │ NULLABLE │ │
└──────────────────┴─────────────────┴──────────────────┴────────────────────────────────┘
Operation Types: 'APPLY', 'ROLLBACK', 'VALIDATE', 'CLEANUP'
Status Values: 'PENDING', 'IN_PROGRESS', 'COMPLETED', 'FAILED', 'ROLLED_BACK'
Example Log Entries:
┌─────────────────────────┬─────────────────┬──────────────┬─────────────┬──────────────────┐
│ id │ scenario_name │ operation │ status │ records_affected │
├─────────────────────────┼─────────────────┼──────────────┼─────────────┼──────────────────┤
│ 7c9e6679-7425-40de-... │ ecommerce-demo │ APPLY │ COMPLETED │ 247 │
│ 886313e1-3b8a-5372-... │ ecommerce-demo │ ROLLBACK │ COMPLETED │ 247 │
└─────────────────────────┴─────────────────┴──────────────┴─────────────┴──────────────────┘
Smart Apply Process
Enhanced SQL Generation
The system transforms standard INSERT statements to include tracking:
-- Original Migration SQL (V1201__ecommerce_scenario.sql)
-- @SETUP
INSERT INTO customers (email, first_name, last_name, phone, date_of_birth, is_premium, preferences)
VALUES
('john.doe@email.com', 'John', 'Doe', '+1-555-0123', '1985-03-15', true, '{"newsletter": true}'),
('jane.smith@email.com', 'Jane', 'Smith', '+1-555-0456', '1990-07-22', false, '{"newsletter": false}');
INSERT INTO products (sku, name, description, price, cost, weight_kg, dimensions, inventory_count, tags)
VALUES
('PHONE-001', 'Smartphone Pro', 'Latest flagship smartphone', 899.99, 450.00, 0.180,
'{"length": 15.5, "width": 7.1, "height": 0.8}', 50, '{"electronics", "mobile", "premium"}'),
('LAPTOP-001', 'Gaming Laptop', '15-inch gaming laptop', 1299.99, 800.00, 2.300,
'{"length": 35.0, "width": 24.5, "height": 2.0}', 25, '{"electronics", "computers", "gaming"}');
Generated Enhanced SQL with Tracking
-- Enhanced SQL with tracking (auto-generated)
WITH operation_log AS (
INSERT INTO operation_log (scenario_name, operation_type, migration_version, status)
VALUES ('ecommerce-demo', 'APPLY', 'V1201', 'IN_PROGRESS')
RETURNING id as log_id
),
inserted_customers AS (
INSERT INTO customers (email, first_name, last_name, phone, date_of_birth, is_premium, preferences)
VALUES
('john.doe@email.com', 'John', 'Doe', '+1-555-0123', '1985-03-15', true, '{"newsletter": true}'),
('jane.smith@email.com', 'Jane', 'Smith', '+1-555-0456', '1990-07-22', false, '{"newsletter": false}')
RETURNING id, email, first_name, last_name, is_premium, preferences
),
tracked_customers AS (
INSERT INTO demo_records (table_name, record_id, scenario_name, migration_version, record_data, operation_log_id)
SELECT 'customers', id::text, 'ecommerce-demo', 'V1201',
jsonb_build_object('email', email, 'first_name', first_name, 'last_name', last_name,
'is_premium', is_premium, 'preferences', preferences),
(SELECT log_id FROM operation_log)
FROM inserted_customers
RETURNING *
),
inserted_products AS (
INSERT INTO products (sku, name, description, price, cost, weight_kg, dimensions, inventory_count, tags)
VALUES
('PHONE-001', 'Smartphone Pro', 'Latest flagship smartphone', 899.99, 450.00, 0.180,
'{"length": 15.5, "width": 7.1, "height": 0.8}', 50, '{"electronics", "mobile", "premium"}'),
('LAPTOP-001', 'Gaming Laptop', '15-inch gaming laptop', 1299.99, 800.00, 2.300,
'{"length": 35.0, "width": 24.5, "height": 2.0}', 25, '{"electronics", "computers", "gaming"}')
RETURNING id, sku, name, price, cost, inventory_count, tags
),
tracked_products AS (
INSERT INTO demo_records (table_name, record_id, scenario_name, migration_version, record_data, operation_log_id)
SELECT 'products', id::text, 'ecommerce-demo', 'V1201',
jsonb_build_object('sku', sku, 'name', name, 'price', price, 'cost', cost,
'inventory_count', inventory_count, 'tags', tags),
(SELECT log_id FROM operation_log)
FROM inserted_products
RETURNING *
)
-- Continue with orders and order_items following same pattern...
SELECT
(SELECT COUNT(*) FROM tracked_customers) +
(SELECT COUNT(*) FROM tracked_products) as total_records_tracked;
Rollback Process Logic
Dependency Analysis and Smart Cleanup
┌─────────────────────────────────────────────────────────────────────────────────────┐
│ ROLLBACK PROCESS FLOW │
└─────────────────────────────────────────────────────────────────────────────────────┘
┌──────────────────┐
│ START ROLLBACK │
│ scenario_name │
└─────────┬────────┘
│
▼
┌──────────────────────────────────────────┐ ┌─────────────────────────────────────┐
│ STEP 1: Query Affected Tables │ │ SELECT DISTINCT table_name │
│ FROM demo_records │◄───┤ FROM demo_records │
│ WHERE scenario_name = ? │ │ WHERE scenario_name = 'ecom-demo' │
└─────────┬────────────────────────────────┘ └─────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────┐ ┌─────────────────────────────────────┐
│ STEP 2: Analyze FK Dependencies │ │ Query information_schema. │
│ Build dependency graph │◄───┤ table_constraints & │
│ │ │ referential_constraints │
└─────────┬────────────────────────────────┘ └─────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────┐
│ STEP 3: Calculate Cleanup Order │
│ (Reverse Topological Sort) │
│ │
│ ┌─────────────────────────────────────┐ │ Dependencies Found:
│ │ order_items (child) │ │ order_items.order_id → orders.id
│ │ ↓ │ │ order_items.product_id → products.id
│ │ orders (parent of order_items) │ │ orders.customer_id → customers.id
│ │ ↓ │ │ inventory_transactions.product_id → products.id
│ │ inventory_transactions │ │
│ │ ↓ │ │ Cleanup Order:
│ │ products │ │ 1. order_items (no children)
│ │ ↓ │ │ 2. inventory_transactions (no children)
│ │ customers │ │ 3. orders (child of customers)
│ └─────────────────────────────────────┘ │ 4. products (child of none tracked)
└─────────┬────────────────────────────────┘ 5. customers (root level)
│
▼
┌──────────────────────────────────────────┐ ┌─────────────────────────────────────┐
│ STEP 4: Generate DELETE Statements │ │ DELETE FROM order_items WHERE id IN │
│ For each table in cleanup order │◄───┤ (SELECT record_id FROM demo_records │
│ │ │ WHERE table_name='order_items' │
└─────────┬────────────────────────────────┘ │ AND scenario_name='ecom-demo') │
│ └─────────────────────────────────────┘
▼
┌──────────────────────────────────────────┐ ┌─────────────────────────────────────┐
│ STEP 5: Validate Safety │ │ • Check for external references │
│ Pre-rollback validation │◄───┤ • Verify no orphaned records │
│ │ │ • Confirm all tracked records exist │
└─────────┬────────────────────────────────┘ └─────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────┐
│ STEP 6: Execute in Transaction │
│ │
│ BEGIN; │
│ -- Delete in dependency-safe order │
│ DELETE FROM order_items WHERE... │
│ DELETE FROM inventory_trans WHERE... │
│ DELETE FROM orders WHERE... │
│ DELETE FROM products WHERE... │
│ DELETE FROM customers WHERE... │
│ │
│ -- Clean tracking records │
│ DELETE FROM demo_records │
│ WHERE scenario_name = 'ecom-demo'; │
│ │
│ -- Update operation log │
│ UPDATE operation_log SET │
│ status = 'COMPLETED', │
│ records_affected = ?, │
│ completed_at = NOW() │
│ WHERE scenario_name = 'ecom-demo' │
│ AND operation_type = 'ROLLBACK'; │
│ COMMIT; │
└─────────┬────────────────────────────────┘
│
▼
┌──────────────────┐
│ ROLLBACK DONE │
│ Clean Exit │
└──────────────────┘
Two Rollback Modes
Mode 1: Explicit @TEARDOWN SQL
-- V1201__ecommerce_scenario.sql
-- @TEARDOWN
DELETE FROM order_items WHERE order_id IN (
SELECT o.id FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.email LIKE '%@email.com'
);
DELETE FROM inventory_transactions WHERE product_id IN (
SELECT id FROM products WHERE sku IN ('PHONE-001', 'LAPTOP-001')
);
DELETE FROM orders WHERE customer_id IN (
SELECT id FROM customers WHERE email LIKE '%@email.com'
);
DELETE FROM products WHERE sku IN ('PHONE-001', 'LAPTOP-001');
DELETE FROM customers WHERE email LIKE '%@email.com';
Mode 2: Smart Cleanup (Auto-generated)
-- Generated automatically from tracking records
WITH operation_start AS (
INSERT INTO operation_log (scenario_name, operation_type, migration_version, status)
VALUES ('ecommerce-demo', 'ROLLBACK', 'V1201', 'IN_PROGRESS')
RETURNING id as log_id
),
-- Delete in dependency-safe order
cleanup_order_items AS (
DELETE FROM order_items
WHERE id::text IN (
SELECT record_id FROM demo_records
WHERE table_name = 'order_items' AND scenario_name = 'ecommerce-demo'
)
RETURNING id
),
cleanup_inventory_trans AS (
DELETE FROM inventory_transactions
WHERE id::text IN (
SELECT record_id FROM demo_records
WHERE table_name = 'inventory_transactions' AND scenario_name = 'ecommerce-demo'
)
RETURNING id
),
cleanup_orders AS (
DELETE FROM orders
WHERE id::text IN (
SELECT record_id FROM demo_records
WHERE table_name = 'orders' AND scenario_name = 'ecommerce-demo'
)
RETURNING id
),
cleanup_products AS (
DELETE FROM products
WHERE id::text IN (
SELECT record_id FROM demo_records
WHERE table_name = 'products' AND scenario_name = 'ecommerce-demo'
)
RETURNING id
),
cleanup_customers AS (
DELETE FROM customers
WHERE id::text IN (
SELECT record_id FROM demo_records
WHERE table_name = 'customers' AND scenario_name = 'ecommerce-demo'
)
RETURNING id
),
cleanup_tracking AS (
DELETE FROM demo_records
WHERE scenario_name = 'ecommerce-demo'
RETURNING id
)
SELECT
(SELECT COUNT(*) FROM cleanup_order_items) +
(SELECT COUNT(*) FROM cleanup_inventory_trans) +
(SELECT COUNT(*) FROM cleanup_orders) +
(SELECT COUNT(*) FROM cleanup_products) +
(SELECT COUNT(*) FROM cleanup_customers) as total_cleaned;
Configuration Management
Scenario Structure
ecommerce-demo/
├── migrations/
│ ├── V1201__setup.sql # Contains @SETUP and optional @TEARDOWN
│ └── V1202__additional_data.sql # Additional migration if needed
├── table-config.yaml # Tracking configuration
├── scenario.yaml # Scenario metadata
└── topology.json # Environment configuration
Table Configuration (table-config.yaml)
# Configuration for tracking different data types and relationships
tables:
customers:
id_column: "id"
id_type: "uuid"
tracking_fields:
["email", "first_name", "last_name", "is_premium", "preferences"]
cleanup_dependencies: [] # Root level - no dependencies
products:
id_column: "id"
id_type: "serial"
tracking_fields:
["sku", "name", "price", "cost", "inventory_count", "tags", "dimensions"]
cleanup_dependencies: [] # Root level
orders:
id_column: "id"
id_type: "bigserial"
tracking_fields:
[
"customer_id",
"order_number",
"total_amount",
"status",
"shipping_address",
]
cleanup_dependencies: ["customers"] # Must cleanup after customers are handled
order_items:
id_column: "id"
id_type: "bigserial"
tracking_fields:
["order_id", "product_id", "quantity", "unit_price", "line_total"]
cleanup_dependencies: ["orders", "products"] # Child of both orders and products
inventory_transactions:
id_column: "id"
id_type: "bigserial"
tracking_fields:
["product_id", "transaction_type", "quantity_change", "reference_type"]
cleanup_dependencies: ["products"] # Child of products
defaults:
track_timestamps: true # Include created_at, updated_at in tracking
track_json_data: true # Store JSONB fields in record_data
cleanup_mode: "smart" # "smart" (auto-generated) or "explicit" (@TEARDOWN)
validation_level: "strict" # Validate FK constraints before cleanup
batch_size: 1000 # For large dataset cleanup
DDL/DML Scenarios Analysis
✓ WORKS WELL - Current Implementation Strengths
Simple to Moderate Data Operations
- Standard INSERTs: With or without auto-generated keys (SERIAL, UUID)
- Multi-table scenarios: With foreign key relationships
- Mixed data types: VARCHAR, INTEGER, DECIMAL, BOOLEAN, TIMESTAMP, JSONB, ARRAY
- Transactional safety: All operations wrapped in transactions
- 1:N and N:M relationships: Properly handled with dependency analysis
Rollback Scenarios
- Clean rollbacks: When all data was inserted by the scenario
- Dependency-aware cleanup: Foreign key constraint respect
- Orphan prevention: Validates no external references exist
- Batch processing: Handles large datasets efficiently
⚠NEEDS EXPLORATION - Areas Requiring Further Development
Complex DDL Operations
-- Schema changes during scenarios
ALTER TABLE products ADD COLUMN new_feature_flag BOOLEAN DEFAULT FALSE;
-- Challenge: How to track and rollback schema changes?
-- Current system focuses on DML, not DDL tracking
UPDATE and DELETE Operations
-- @SETUP
UPDATE products SET price = price * 1.1 WHERE category_path LIKE 'Electronics%';
DELETE FROM customers WHERE last_login < '2020-01-01' AND is_premium = FALSE;
-- Challenge: Original values not captured for rollback
-- Potential Solution: Capture before/after states in record_data
Procedural Code and Functions
-- @SETUP
SELECT setup_demo_user_preferences();
SELECT generate_sample_orders(100);
-- Challenge: Function calls don't provide RETURNING clauses
-- Need custom tracking within procedures
Cross-Schema Operations
-- @SETUP
INSERT INTO external_schema.audit_log (...);
INSERT INTO reporting_schema.metrics (...);
-- Challenge: Tracking across multiple schemas
-- Need enhanced configuration for cross-schema scenarios
Large Dataset Scenarios
-- @SETUP - Generate 1M+ records
INSERT INTO large_table
SELECT generate_series(1, 1000000), random_data();
-- Challenge: Memory usage and tracking overhead
-- Need streaming/batch processing for tracking
Conditional Logic in Migrations
-- @SETUP
DO $$
BEGIN
IF (SELECT COUNT(*) FROM customers) < 100 THEN
-- Insert sample customers
ELSE
-- Insert different test data
END IF;
END $$;
-- Challenge: Conditional logic makes tracking complex
-- Need dynamic tracking based on execution path
🔄 ENHANCEMENT OPPORTUNITIES
1. UPDATE Operation Tracking
# Enhanced table-config.yaml
tables:
products:
track_updates: true # Enable UPDATE tracking
update_strategy: "snapshot" # "snapshot" or "differential"
snapshot_fields: ["price", "inventory_count"] # Fields to capture before UPDATE
2. Schema Change Tracking
-- DDL tracking table
CREATE TABLE schema_change_log (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
scenario_name VARCHAR(255) NOT NULL,
change_type VARCHAR(50) NOT NULL, -- 'ADD_COLUMN', 'DROP_COLUMN', 'ALTER_COLUMN'
table_name VARCHAR(100) NOT NULL,
ddl_statement TEXT NOT NULL,
rollback_statement TEXT, -- Auto-generated or explicit
applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
3. Function Call Tracking
-- @SETUP with tracking
WITH function_result AS (
SELECT setup_demo_preferences() as result_data
),
tracked_function AS (
INSERT INTO demo_records (table_name, record_id, scenario_name, migration_version, record_data)
VALUES ('function_calls', 'setup_demo_preferences', 'ecommerce-demo', 'V1201',
(SELECT jsonb_build_object('result', result_data) FROM function_result))
RETURNING *
)
SELECT * FROM tracked_function;
CLI Usage Examples
Basic Operations
# Apply a scenario
./migration-tool scenario apply ecommerce-demo
# Check scenario status
./migration-tool scenario status ecommerce-demo
# View tracking records
./migration-tool scenario inspect ecommerce-demo
# Rollback scenario
./migration-tool scenario rollback ecommerce-demo
# List all scenarios
./migration-tool scenario list
# Validate scenario without applying
./migration-tool scenario validate ecommerce-demo
Advanced Operations
# Apply with custom configuration
./migration-tool scenario apply ecommerce-demo --config custom-table-config.yaml
# Rollback to specific version
./migration-tool scenario rollback ecommerce-demo --to-version V1201
# Generate cleanup SQL without executing
./migration-tool scenario rollback ecommerce-demo --dry-run
# Force rollback (skip safety validations)
./migration-tool scenario rollback ecommerce-demo --force
# Export scenario data for backup
./migration-tool scenario export ecommerce-demo --format json
# Performance monitoring during apply
./migration-tool scenario apply ecommerce-demo --monitor --batch-size 500
Benefits and Use Cases
Benefits
- Flyway Compatibility: Leverages existing Flyway infrastructure and knowledge
- Comprehensive Tracking: Every record insertion is automatically tracked
- Smart Rollbacks: Dependency-aware cleanup without manual DELETE statement writing
- Transaction Safety: All operations are atomic and consistent
- Multi-Scenario Support: Different test scenarios can coexist safely
- Audit Trail: Complete history of all apply/rollback operations
- Flexible Configuration: Table-specific tracking and cleanup behaviors
Ideal Use Cases
- Test Data Management: Create/cleanup test scenarios for different environments
- Demo Environment Setup: Repeatable demo data with clean teardown
- Development Sandboxes: Personal development environments with isolated data
- A/B Testing: Multiple data variants for testing different features
- Training Environments: Consistent training data that can be reset
- Integration Testing: Complex multi-service test scenarios
Production Considerations
- Schema Isolation: Use separate schemas for different environments
- Backup Integration: Coordinate with existing backup strategies
- Performance Impact: Monitor tracking overhead on large datasets
- Security: Ensure tracking tables don’t expose sensitive data
- Monitoring: Integration with existing database monitoring tools
This system provides a robust foundation for managing complex data scenarios while maintaining the reliability and familiarity of Flyway migrations.