How to: Schema Versioning

This guide covers schema versioning workflows including upgrading existing repositories, migrating schemas, handling rollbacks, and working with schemas across multiple branches.

Prerequisites

• Horizon Epoch installed (Installation Guide)
• PostgreSQL running with a repository configured
• Familiarity with basic version control concepts

Understanding Schema Versioning

Horizon Epoch versions database schemas alongside your data. Every commit captures:

• Column definitions: Name, type, nullability, defaults
• Constraints: Foreign keys, unique, check, exclusion
• Indexes: B-tree, GIN, GiST, BRIN, hash
• Enum types: Custom PostgreSQL enums

Schema snapshots are stored as JSONB in the table_versions table, enabling full schema history and three-way merge for schema changes.

Upgrading Existing Repositories

If you have an existing Horizon Epoch repository created before schema versioning was added, you can backfill schema information for historical commits.

Step 1: Check Current Status

# See which tables have schema data
epoch schema status

# Output:
# Table      | Latest Schema | Historical | Live DB  | Status
# -----------|---------------|------------|----------|--------
# users      | Yes           | Partial    | Synced   | OK
# orders     | Yes           | No         | Synced   | Needs backfill
# products   | No            | No         | Drifted  | Needs migration

Step 2: Backfill Historical Schemas

For tables where live database is accessible, backfill historical commits:

# Backfill schema for specific table
epoch migrate-schemas users --backfill

# Backfill all tables with missing schemas
epoch migrate-schemas --backfill --all

# Preview what would be backfilled (dry-run)
epoch migrate-schemas --backfill --all --dry-run

What happens during backfill:

1. For each commit without schema data, Epoch checks if the table location is accessible
2. If accessible, it introspects the current schema and stores it
3. If not accessible, the commit is marked as “schema unavailable”

Step 3: Verify Migration

# Verify all tables have schema data
epoch schema status

# View schema at a specific commit
epoch schema show users --at abc123f

Migration Guide for Existing Repositories

Scenario: Adding Schema Versioning to Production

If you have a production repository and want to enable full schema versioning:

1. Create a Baseline

# Create a new commit that captures current schema
epoch commit -m "Capture schema baseline" --include-schema

This creates a commit with full schema snapshots for all registered tables.

2. Update Your Workflow

From this point forward, all commits automatically include schema snapshots.

# Normal workflow - schema is captured automatically
epoch branch create feature/add-column
# ... make schema changes ...
epoch commit -m "Add email_verified column"

3. Handle Historical Data (Optional)

If you need schema history for older commits:

# Option A: Backfill from live database (assumes schema hasn't changed)
epoch migrate-schemas --backfill --since "2024-01-01"

# Option B: Import from external migrations
epoch migrate-schemas --import-from flyway ./migrations
epoch migrate-schemas --import-from liquibase ./changelog.xml

Rollback Procedures

Rolling Back Schema Changes

If a schema change causes issues, you can roll back using several methods:

Method 1: Revert the Commit

Create a new commit that reverses the schema change:

# Revert the problematic commit
epoch revert abc123f

# This creates a new commit with:
# - Data changes reversed
# - Schema changes reversed
# - DDL generated to undo schema modifications

Method 2: Reset to Previous Commit

Reset the branch to a known-good state:

# Soft reset (keep changes for review)
epoch reset --soft HEAD~1

# Hard reset (discard changes)
epoch reset --hard HEAD~1

# Reset to specific commit
epoch reset --hard v1.0.0

After reset, apply the DDL to synchronize the live database:

# Generate rollback DDL
epoch schema diff HEAD v1.0.0 --show-ddl > rollback.sql

# Review and apply
psql -f rollback.sql

Method 3: Checkout and Apply

Create a new branch from a known-good state:

# Create branch from good state
epoch branch create hotfix/rollback --from v1.0.0

# Generate DDL to bring live DB to that state
epoch schema apply users --from v1.0.0 --dry-run

# Apply after review
epoch schema apply users --from v1.0.0

Handling Rollback Failures

Some schema changes cannot be automatically rolled back:

For destructive changes, consider:

# 1. Check for data dependencies
epoch schema diff HEAD~1 HEAD --check-data-impact

# 2. Create data backup before proceeding
epoch export users --at HEAD~1 > users_backup.parquet

# 3. Apply rollback with force flag
epoch schema apply users --from HEAD~1 --force

Multi-Branch Schema Workflows

Feature Branch Schema Changes

When developing schema changes on a feature branch:

# 1. Create feature branch
epoch branch create feature/add-preferences

# 2. Make schema changes
psql -c "ALTER TABLE users ADD COLUMN preferences JSONB"

# 3. Commit (schema captured automatically)
epoch commit -m "Add user preferences column"

# 4. Preview merge
epoch merge main --dry-run
# Shows: Schema changes to apply on main

# 5. Merge when ready
epoch merge main

Parallel Schema Development

When multiple branches modify the same table:

# Branch A adds column
epoch checkout feature-a
psql -c "ALTER TABLE users ADD COLUMN theme VARCHAR(20)"
epoch commit -m "Add theme column"

# Branch B adds different column
epoch checkout feature-b
psql -c "ALTER TABLE users ADD COLUMN locale VARCHAR(10)"
epoch commit -m "Add locale column"

# Merge A into main
epoch checkout main
epoch merge feature-a  # Success

# Merge B into main
epoch merge feature-b  # Also success - no conflict

Both columns are added because they don’t conflict.

Schema Conflict Resolution

When branches modify the same constraint:

# Branch A modifies check constraint
epoch checkout feature-a
psql -c "ALTER TABLE users DROP CONSTRAINT chk_age"
psql -c "ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 18)"
epoch commit -m "Require adult users"

# Branch B modifies same constraint differently
epoch checkout feature-b
psql -c "ALTER TABLE users DROP CONSTRAINT chk_age"
psql -c "ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 13)"
epoch commit -m "Allow teen users"

# Attempt merge
epoch checkout main
epoch merge feature-a  # Success
epoch merge feature-b  # CONFLICT

# Resolve conflict
epoch conflicts show
# CONFLICT (constraint): chk_age modified in both branches

# Choose resolution
epoch conflicts resolve --constraint chk_age --custom "CHECK (age >= 16)"
epoch commit -m "Merge feature-b with compromise age check"

Environment Promotion with Schema

When promoting schema changes between environments:

# 1. Develop on dev branch
epoch checkout dev
# ... make changes ...
epoch commit -m "Schema updates for v2.0"

# 2. Generate migration for staging
epoch schema diff staging dev --show-ddl > staging_migration.sql

# 3. Review migration
cat staging_migration.sql

# 4. Apply to staging environment
PGHOST=staging-db psql -f staging_migration.sql

# 5. Merge to staging branch
epoch checkout staging
epoch merge dev

# 6. Repeat for production
epoch schema diff production staging --show-ddl > prod_migration.sql

Best Practices

1. Always Review Schema Diffs

# Before merging, check schema changes
epoch diff main feature-branch --schema

# Generate DDL preview
epoch schema diff main feature-branch --show-ddl

2. Use Descriptive Commit Messages

# Bad
epoch commit -m "Update schema"

# Good
epoch commit -m "Add email_verified column with default false for GDPR compliance"

3. Test Migrations on Branch Data

# Create test branch
epoch branch create test/migration-validation

# Apply migration
epoch schema apply users --from feature-branch --dry-run

# Validate
epoch validate --table users

4. Handle Large Tables Carefully

# For large tables, use online mode
epoch schema apply users --from feature-branch --online

# Monitor progress
epoch schema apply --status

5. Version Your Enum Changes Carefully

# Adding values is safe
psql -c "ALTER TYPE status ADD VALUE 'archived'"

# Removing values requires migration
# See: docs/how-to/ddl-migrations.md

Troubleshooting

Schema Not Captured

If schema isn’t being captured in commits:

# Check table is registered
epoch table list

# Re-register with schema introspection
epoch table add users --location "postgresql://mydb/public.users" --introspect-schema

Schema Drift Detected

If live database differs from stored schema:

# See differences
epoch schema diff users --live

# Capture current state
epoch commit -m "Sync schema from live database" --rescan-schema

# Or apply stored schema to database
epoch schema apply users --from HEAD --force

Backfill Fails

If schema backfill fails:

# Check connectivity
epoch doctor

# Try specific table
epoch migrate-schemas users --backfill --verbose

# Skip inaccessible tables
epoch migrate-schemas --backfill --skip-inaccessible

See Also

• DDL Migrations - Generating and applying DDL
• Constraint Versioning Tutorial - Step-by-step guide
• CLI Schema Commands - Command reference
• Constraint Reference - Constraint type documentation