Database Versioning Patterns

Version control strategies for database schemas and data across environments.

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

Keywords: schema version, database version, migration history Solves: Track schema changes, version history

temporal-queries

Keywords: temporal, point-in-time, history query Solves: Query historical data, time-travel queries

change-tracking

Keywords: cdc, change data capture, audit log Solves: Track all changes, audit compliance

environment-sync

Keywords: environment sync, migration coordination Solves: Sync across environments, coordinate deployments

Related Skills

• alembic-migrations – Migration implementation
• database-schema-designer – Schema design
• zero-downtime-migration – Safe production changes