Database Versioning Patterns

Overview

• Tracking schema changes over time
• Coordinating database changes across dev/staging/prod
• Implementing database audit trails
• Managing stored procedures and functions
• Versioning reference data

Schema Versioning Table

CREATE TABLE schema_version (
    version_id SERIAL PRIMARY KEY,
    version_number VARCHAR(20) NOT NULL,
    description TEXT NOT NULL,
    applied_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    applied_by VARCHAR(100),
    execution_time_ms INTEGER,
    checksum VARCHAR(64),
    CONSTRAINT uq_version_number UNIQUE (version_number)
);

Semantic Versioning for Databases

MAJOR.MINOR.PATCH

MAJOR: Breaking changes (drop tables, rename columns)
MINOR: Backward-compatible additions (new tables, nullable columns)
PATCH: Bug fixes, index changes, data migrations

Detailed Guides

• Audit trails: See references/audit-trails.md for row versioning, temporal tables, CDC
• Environment coordination: See references/environment-coordination.md for multi-env flows, locks
• Object versioning: See references/object-versioning.md for procedures, views, reference data
• Migration testing: See references/migration-testing.md for pytest patterns

Best Practices

Anti-Patterns

# NEVER modify deployed migrations - create new migration instead

# NEVER delete migration history
command.stamp(alembic_config, "head")  # Loses history

# NEVER skip environments
# Always: local -> CI -> staging -> production

# NEVER version sensitive data in migrations
op.bulk_insert(users, [{"password": "secret"}])  # Security risk!

Capability Details

schema-versioning

temporal-queries

change-tracking

environment-sync

Related Skills

• alembic-migrations

  - Migration implementation

• database-schema-designer

  - Schema design

• zero-downtime-migration

  - Safe production changes