jackyliang/database-branching

Database Branching

Overview

Treat database forks like git branches. Never make schema changes directly on the production database. Fork it, develop against the fork, merge migrations back when ready, then delete the fork.

This skill only applies to projects using Ghost DB (TimescaleDB Cloud). If the project uses Supabase, local Postgres, or another database service, this skill does not apply.

When to Use

Any time a feature involves database changes:

• Adding a new table
• Adding or removing columns
• Altering column types or constraints
• Adding indexes
• Changing enums
• Any DDL operation whatsoever

Even for "small" changes. A single ALTER TABLE ADD COLUMN still gets a fork. The cost of forking is low (2 minutes). The cost of breaking production is high.

When NOT to Use

• Feature has zero database changes (pure application logic, API changes, frontend)
• Project does not use Ghost DB
• Quick read-only queries against production (use ghost_sql directly)

How to Detect Ghost DB

Check for these signals:

• mcp__ghost__ tools are available
• .env or CLAUDE.md references a Ghost database ID (10-char alphanumeric, e.g., r7mf0mo24b)
• CLAUDE.md mentions Ghost, TimescaleDB, or ghost_list

If none of these are present, this skill does not apply.

The Workflow

Step 1: Fork the Database

Like git checkout -b feature/my-feature, but for the database.

mcp__ghost__ghost_list          # Find your source DB ID
mcp__ghost__ghost_fork
  id: "{source_db_id}"
  name: "{project}-{feature}"   # e.g., "sync-hq-auth", "sync-hq-webhooks"

Forking takes 90-120 seconds. Poll with ghost_list every 15-20 seconds until the fork shows as running. Do not give up early.

Step 2: Connect to the Fork

mcp__ghost__ghost_connect
  id: "{fork_id}"

Update .env to point to the fork:

# Comment out production URL
# DATABASE_URL=postgresql://...production...

# Use fork for development
DATABASE_URL=postgresql://...fork...

Step 3: Check for Migration Tools

Before making any schema changes, check if the project uses a migration tool:

| Tool | How to Detect | Migration Command | |------|--------------|-------------------| | Alembic (Python) | alembic.ini or alembic/ directory exists | alembic revision --autogenerate -m "description" | | Prisma (Node.js) | prisma/schema.prisma exists | npx prisma migrate dev --name description | | Drizzle (Node.js) | drizzle.config.ts exists | npx drizzle-kit generate | | Django | manage.py exists | python manage.py makemigrations | | Raw SQL | No migration tool found | Track changes manually in SQL files |

All schema changes must go through the migration tool. Never run raw DDL (CREATE TABLE, ALTER TABLE) directly against the database unless the project has no migration tool. Migrations are how changes get tracked, reviewed, and applied consistently.

Step 4: Develop on the Fork

This is where you spend most of your time:

1. Modify models/schema in code (e.g., models.py, schema.prisma)
2. Generate a migration using the project's migration tool
3. Apply the migration to the fork:

   alembic upgrade head  # or equivalent
   

4. Write tests and implementation (see test-driven-development skill)
5. Verify with queries:

   mcp__ghost__ghost_schema id: "{fork_id}"    # Check schema looks right
   mcp__ghost__ghost_sql id: "{fork_id}" query: "SELECT ..."  # Spot-check data
   

Iterate freely. If a migration is wrong, you can rollback (alembic downgrade -1), fix it, and re-apply. The fork is disposable.

Alembic Gotchas

• Autogenerated migrations may reference sqlmodel.sql.sqltypes.AutoString without importing it — always add import sqlmodel to migration files or replace with sa.VARCHAR()
• Enum changes (ALTER TYPE ... ADD VALUE) require manual SQL in the migration — Alembic can't autogenerate these
• Review every autogenerated migration before applying — they are not always correct

Step 5: Merge Back to Production

When the feature is complete and tested:

1. Merge the git branch (PR, merge to main, etc.)

2. Switch .env back to production:

   DATABASE_URL=postgresql://...production...
   

3. Run migrations on production:

   alembic upgrade head  # or equivalent
   

4. Verify production — spot-check the schema:

   mcp__ghost__ghost_schema id: "{production_db_id}"
   

5. Delete the fork:

   mcp__ghost__ghost_delete id: "{fork_id}"
   

Do not skip the delete. Forks consume resources. Clean up after yourself.

Abandoning a Branch

If the feature is cancelled or the approach didn't work:

1. Delete the fork — no migration needed:

   mcp__ghost__ghost_delete id: "{fork_id}"
   

2. Discard the migration files in git (revert/delete)
3. Restore .env to production

Deployment Considerations

When the app is deployed (e.g., Render, Railway, Fly.io):

• The deployed app uses a DATABASE_URL environment variable pointing to production
• After merging a feature with migrations, you may need to manually run migrations on the deployed database
• Some platforms support release commands (e.g., Render's "Build Command" or "Pre-Deploy Command") where you can add alembic upgrade head
• If the platform doesn't support release commands, you'll need to run migrations manually after deploy (e.g., via the platform's shell or by connecting to the DB directly)
• This is an open question for each project — document the deploy migration strategy in CLAUDE.md once established

Relationship to Other Skills

This skill complements:

• plan-driven-development — The plan tracks what to build. This skill handles how to safely make database changes during that build. If a plan milestone involves DB changes, fork before starting that milestone.
• test-driven-development — TDD handles how to test. Tests run against the development fork (or a separate test fork if you need additional isolation). This skill ensures the fork exists before tests run.

Ownership boundaries:

• Database fork lifecycle (create, develop, merge, delete) → this skill
• What to build and progress tracking → plan-driven-development
• How to write and run tests → test-driven-development

Quick Reference

| Action | Tool | Key Parameter | |--------|------|---------------| | List DBs | mcp__ghost__ghost_list | — | | Fork | mcp__ghost__ghost_fork | id (source DB) | | Connect | mcp__ghost__ghost_connect | id (fork) | | Query | mcp__ghost__ghost_sql | id, query | | Schema | mcp__ghost__ghost_schema | id | | Delete | mcp__ghost__ghost_delete | id (fork) |