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

Practice	Reason
Version everything	Full traceability
Immutable history	Audit compliance
Test rollbacks	Ensure recoverability
Environment parity	Consistent deployments
Checksum verification	Detect unauthorized changes

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

database-versioning