Skip to main content

How to create a database migration

Aucert uses Flyway for database migrations. Migrations run as temporary pods inside AKS because the PostgreSQL instances are on a private VNet (no public access).

Database instances​

InstanceDatabasesMigration path
aucert-internal-pginternal_shared_db, astra_db, plane_dbinfra/migrations/internal-shared/, infra/migrations/astra/
aucertdev-product-pgaucert (dev)infra/migrations/product/ (when created)

Steps​

Step 1: Create the migration file​

Migration files follow the Flyway naming convention: V{NNN}__{description}.sql

# For internal_shared_db
ls infra/migrations/internal-shared/
# V001__create_platforms.sql
# V002__create_audit_log.sql
# V003__create_user_preferences.sql
# V004__seed_platforms.sql

# Create the next migration
touch infra/migrations/internal-shared/V005__add_user_roles.sql
caution
  • Version numbers must be sequential and unique
  • Use double underscores (__) between version and description
  • Use snake_case for descriptions
  • Never modify an already-applied migration — create a new one instead

Step 2: Write the SQL​

-- infra/migrations/internal-shared/V005__add_user_roles.sql

CREATE TABLE IF NOT EXISTS user_roles (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES user_preferences(id),
    role        TEXT NOT NULL CHECK (role IN ('admin', 'member', 'viewer')),
    granted_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    granted_by  TEXT NOT NULL
);

CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);

Best practices:

  • Always use IF NOT EXISTS for CREATE TABLE/CREATE INDEX
  • Include rollback comments at the top if the migration is complex
  • Keep migrations small and focused — one logical change per file
  • Use explicit types (e.g., TIMESTAMPTZ not TIMESTAMP)

Step 3: Test locally (if accessible)​

If you have a local PostgreSQL or can port-forward:

# Port-forward the internal PG
kubectl port-forward -n internal-platform svc/internal-pg 5433:5432

# Run Flyway locally
docker run --rm \
  -v $(pwd)/infra/migrations/internal-shared:/flyway/sql \
  flyway/flyway:11 \
  -url="jdbc:postgresql://host.docker.internal:5433/internal_shared_db" \
  -user="aucert_admin" \
  -password="<from-key-vault>" \
  info

Step 4: Deploy via CI​

Migrations run automatically in the deploy-astra.yml workflow when files in infra/migrations/** change. The workflow:

  1. Creates a ConfigMap from the SQL files
  2. Runs a temporary Flyway pod inside AKS (same VNet as PG)
  3. Credentials injected via secretKeyRef from astra-db-credentials K8s secret
  4. Cleans up the ConfigMap after completion
# Manual trigger if needed
gh workflow run "Deploy Astra" --field skip_migrations=false

Step 5: Verify the migration​

# Check Flyway history table
kubectl exec -n internal-platform -it <pg-pod> -- \
  psql -U aucert_admin -d internal_shared_db \
  -c "SELECT * FROM flyway_schema_history ORDER BY installed_rank DESC LIMIT 5;"

Rollback strategy​

danger

Flyway Community Edition does not support automatic rollback. Plan your rollback strategy before applying migrations in production.

For additive changes (new tables, columns, indexes): Create a new forward migration that reverses the change.

For destructive changes (drops, renames): Don't. Instead:

  1. Add the new structure in migration N
  2. Migrate data in migration N+1
  3. Drop the old structure in migration N+2 (after verifying)

What's next​