database-versioning

# 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
- Blue-green database deployments

## Version Control Strategies

### Schema Versioning Table

```sql
-- Track schema version in the database itself
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)
);

-- Example entries
INSERT INTO schema_version (version_number, description, applied_by) VALUES
('1.0.0', 'Initial schema', 'deploy-bot'),
('1.1.0', 'Add user preferences table', 'deploy-bot'),
('1.2.0', 'Add audit columns to all tables', 'deploy-bot');
```

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

### Migration Numbering Schemes

```
Option 1: Sequential
001_initial_schema.sql
002_add_users.sql
003_add_orders.sql

Option 2: Timestamp
20260115120000_initial_schema.sql
20260116143000_add_users.sql
20260117091500_add_orders.sql

Option 3: Hybrid (Date + Sequence)
2026_01_15_001_initial_schema.sql
2026_01_15_002_add_users.sql
2026_01_16_001_add_orders.sql
```

## Environment Coordination

### Multi-Environment Migration Flow

```
┌─────────────┐    ┌─────────────┐    ┌─────────────┐    ┌─────────────┐
│   Local     │───>│     CI      │───>│   Staging   │───>│ Production  │
│   (dev)     │    │   (test)    │    │   (preview) │    │   (live)    │
└─────────────┘    └─────────────┘