Database Schema Management

The Core Problem

Database schema changes are inevitable. Without discipline, they cause data loss, production incidents, and emergency restores. This document establishes platform-agnostic principles and practices to prevent these issues.

Principles

1. Schema is Infrastructure, Not Application Code

The database schema serves multiple consumers (portals, APIs, background jobs, integrations). No single application should own or deploy the schema through its ORM.

Implications:

• Dedicated repository for schema definitions
• Separate CI/CD pipeline for database deployments
• Applications connect to the database; they don’t define it

2. Declarative Over Imperative

Define the desired state of your schema, not the steps to get there. Let tooling calculate the delta.

Approach	Example	Risk
Imperative	”Add column X, then rename Y to Z”	Steps can conflict, order matters, hard to review
Declarative	”Table should have columns A, B, C”	Tool compares current vs desired, generates safe migration

3. Data Migration is Explicit, Never Automatic

Schema tools handle structure. Data transformations require explicit scripts written and reviewed by humans.

Golden rule: If a schema change could affect existing data, a data migration script must accompany it.

The Dangerous Operations

These operations can cause data loss if handled incorrectly:

Operation	Risk	Safe Approach
Column rename	Tool may interpret as drop + add	Use tool’s refactor feature or write explicit migration
Column type change	Implicit conversion may truncate	Add new column, migrate data, drop old column
Column removal	Data gone	Verify column is unused, backup, then remove
Table rename	References break, data may be lost	Use refactor tooling or staged migration
NOT NULL addition	Fails if NULLs exist	Add default value or backfill first

Recommended Workflow

Step 1: Schema Changes in Version Control

All schema definitions live in Git:

/database
  /tables
    members.sql
    organizations.sql
  /views
  /functions
  /migrations      # Data migration scripts
  /pre-deploy      # Scripts that run before schema changes
  /post-deploy     # Scripts that run after schema changes

Step 2: Review Process

Every schema change requires:

1. Structural review — Is the SQL correct?
2. Data impact review — Does this affect existing data?
3. Rollback plan — How do we undo this if needed?

Step 3: Deployment Pipeline

[Schema Repo] → [Build/Validate] → [Generate Diff] → [Review Diff] → [Deploy to Test] → [Deploy to Prod]

Critical: Always review the generated diff before production deployment. Automated deployment without human review of the actual changes is how data gets lost.

Step 4: Protect Against Data Loss

Configure your deployment tooling to:

• Block on data loss — Abort if the operation would drop columns/tables with data
• Require explicit override — Force developers to acknowledge destructive changes
• Generate rollback script — Always have a way back

Tool Options by Platform

PostgreSQL (Azure, AWS, GCP)

Tool	Approach	Strengths
Flyway	Versioned migration scripts	Simple, widely adopted, CI/CD friendly
Liquibase	Declarative (XML/YAML/SQL)	Rollback support, database refactoring
pgAdmin/pg_dump	Manual scripts	Full control, no tooling dependency
Sqitch	Change management	Dependency tracking between changes

Recommendation for PostgreSQL: Flyway or Liquibase. Both are cloud-agnostic, work with any PostgreSQL instance, and integrate with CI/CD. CTN chose Liquibase with YAML changesets for its dialect portability (TDR-0001).

SQL Server (Azure SQL, AWS RDS, On-premise)

Tool	Approach	Strengths
SSDT	Declarative (dacpac)	Native Microsoft tooling, refactor support
Flyway	Versioned migrations	Cross-platform consistency
Liquibase	Declarative	Same tooling across database platforms

Cloud-Agnostic Strategy

If portability between clouds/databases is required:

1. Use Liquibase or Flyway — they work across PostgreSQL, SQL Server, MySQL, Oracle
2. Keep SQL as ANSI-compliant as possible
3. Isolate database-specific syntax in clearly marked files

Data Migration Patterns

Pattern 1: Expand-Contract (Safe Column Rename)

Instead of renaming directly:

-- Step 1: Add new column
ALTER TABLE members ADD COLUMN client_name VARCHAR(255);

-- Step 2: Migrate data (in post-deploy script)
UPDATE members SET client_name = customer_name WHERE client_name IS NULL;

-- Step 3: Application updated to use new column (separate deployment)

-- Step 4: Remove old column (next release, after verification)
ALTER TABLE members DROP COLUMN customer_name;

This is slower but eliminates data loss risk.

Pattern 2: Feature Flags for Schema

For major schema changes:

1. Deploy new schema structures alongside old
2. Application writes to both (feature flagged)
3. Migrate historical data
4. Switch reads to new structure
5. Remove old structure after verification

Pattern 3: Blue-Green for Databases

For high-risk migrations:

1. Create new database with new schema
2. Migrate data with transformation
3. Switch application to new database
4. Keep old database as rollback option

Pre-Deployment Checklist

Before any schema deployment:

• Schema change reviewed by second person
• Data impact assessed — which rows/tables affected?
• Migration script written for data transformations
• Rollback plan documented
• Deployment tested on non-production environment
• Backup verified and accessible
• Deployment window communicated if downtime required

Anti-Patterns to Avoid

1. ORM-driven migrations in production — ORMs are for development convenience, not production schema management
2. Auto-apply migrations on application startup — Schema changes should be deliberate, reviewed, and separate from app deployment
3. Trusting the tool blindly — Always review generated SQL before execution
4. No rollback plan — Every change needs a way back
5. Schema changes without data assessment — The question isn’t “will this SQL run?” but “what happens to existing data?”

Conclusion

Database schema management is a discipline, not a tool choice. The principles remain constant regardless of whether you use PostgreSQL on AWS, SQL Server on Azure, or any other combination:

1. Treat schema as infrastructure with its own lifecycle
2. Use declarative definitions where possible
3. Handle data migrations explicitly
4. Review every change for data impact
5. Always have a rollback plan

The specific tool matters less than the process around it. Flyway, Liquibase, SSDT — all can work well if the team follows safe practices. All can cause disasters if used carelessly.