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