Database Versioning

---
name: database-versioning
description: Database version control and change management patterns. Use when managing schema history, coordinating database changes across environments, implementing audit trails, or versioning database objects.
context: fork
agent: database-engineer
version: 1.0.0
tags: [database, versioning, schema, change-management, audit]
author: OrchestKit
user-invocable: false
---

# 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

```sql
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](references/audit-trails.md) for row versioning, temporal tables, CDC
- **Environment coordination**: See [references/environment-coordination.md](references/environment-coordination.md) for multi-env flows, locks
- **Object versioning**: See [references/object-versioning.md](references/object-versioning.md) for procedures, views, reference data
- **Migration testing**: See [references/migration-testing.md](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

```python
# 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