Database Migrations

Comprehensive guide to database migration management across the MyNATCA ecosystem using the symlinked migrations workflow.

Overview

The MyNATCA ecosystem uses a distributed migration ownership model with automatic symlink management:

Each project owns its own migrations in its repository
Platform repository creates symlinks to all migrations
Git hooks automatically update symlinks when switching branches
All migrations run from platform using standard Supabase CLI

This approach provides:

Separation of concerns - Each team manages their own schema
Developer flexibility - Work on multiple projects, different branches simultaneously
Standard tooling - Uses Supabase CLI without custom migration runners
Automatic synchronization - Git hooks keep symlinks up to date

Schema Ownership

Schema	Project	Migration Location	Purpose
`public`	Platform	`platform/supabase/migrations/`	Core data: members, facilities, regions, positions, grants
`pay`	Pay	`pay/supabase/migrations/`	PayChecker: pay_periods, shifts, LES data, time tracking
`discord`	Discord	`discord/supabase/migrations/`	Discord bot: verification, validation logs, summaries
`bid`	BID	(future) `bid/supabase/migrations/`	BID data (not yet implementing migrations)

Repository Structure

mynatca/
├── platform/
│   ├── supabase/
│   │   ├── migrations/
│   │   │   ├── 00000000000001_baseline_platform_schema.sql (owned by platform)
│   │   │   ├── 00000000000001_baseline_pay_schema.sql → ../../pay/supabase/migrations/... (symlink)
│   │   │   ├── 00000000000001_baseline_discord_schema.sql → ../../discord/supabase/migrations/... (symlink)
│   │   │   └── ... (all migrations, mixed owned + symlinked)
│   │   └── config.toml
│   └── scripts/
│       ├── link-project.sh
│       ├── db-refresh.sh
│       └── setup-git-hooks.sh
│
├── pay/
│   ├── supabase/
│   │   └── migrations/
│   │       ├── 00000000000001_baseline_pay_schema.sql (owned by pay)
│   │       └── ... (pay-owned migrations)
│   └── scripts/
│       └── setup-git-hooks.sh
│
└── discord/
    ├── supabase/
    │   └── migrations/
    │       ├── 00000000000001_baseline_discord_schema.sql (owned by discord)
    │       └── ... (discord-owned migrations)
    └── scripts/
        └── setup-git-hooks.sh

Initial Setup

1. Install Git Hooks (One-Time Setup)

Each developer must install git hooks in each repository they work on:

# Platform repository
cd ~/dev/mynatca/platform
./scripts/setup-git-hooks.sh
 
# Pay repository (if you work on PayChecker)
cd ~/dev/mynatca/pay
./scripts/setup-git-hooks.sh
 
# Discord repository (if you work on Discord bot)
cd ~/dev/mynatca/discord
./scripts/setup-git-hooks.sh

What these hooks do:

post-checkout - Automatically updates symlinks when you switch branches
post-merge - Automatically updates symlinks after merging
pre-commit - Blocks direct commits to main/staging (enforces PR workflow)

2. Link Subprojects

After installing git hooks, link subprojects to platform:

cd ~/dev/mynatca/platform
./scripts/link-project.sh

Follow the prompts to link each subproject (pay, discord, etc.). This creates:

Symlinks in platform/supabase/migrations/
Registry entry in .linked-projects.json
Configuration for seed orchestration

Creating New Migrations

Platform Migrations (Public Schema)

cd ~/dev/mynatca/platform
 
# Option 1: Make changes in Supabase Studio, then generate migration
supabase db diff -f add_new_platform_feature
 
# Option 2: Write migration manually
# Create: supabase/migrations/YYYYMMDDHHmmss_description.sql
 
# Test the migration
supabase db reset
 
# Commit the migration
git add supabase/migrations/
git commit -m "Add migration: add new platform feature"
git push

Pay Migrations (Pay Schema)

cd ~/dev/mynatca/pay
 
# Create migration in pay repository
supabase db diff -f add_new_pay_feature
# This creates: pay/supabase/migrations/YYYYMMDDHHmmss_add_new_pay_feature.sql
 
# Git hook in pay repo triggers platform/scripts/link-migrations.sh
# Symlink is automatically created in platform/supabase/migrations/
 
# Test from platform (all migrations run from platform)
cd ~/dev/mynatca/platform
supabase db reset
 
# Commit and push
cd ~/dev/mynatca/pay
git add supabase/migrations/
git commit -m "Add migration: add new pay feature"
git push

Discord Migrations (Discord Schema)

cd ~/dev/mynatca/discord
 
# Create migration in discord repository
supabase db diff -f add_new_discord_feature
# This creates: discord/supabase/migrations/YYYYMMDDHHmmss_add_new_discord_feature.sql
 
# Git hook in discord repo triggers platform/scripts/link-migrations.sh
# Symlink is automatically created in platform/supabase/migrations/
 
# Test from platform
cd ~/dev/mynatca/platform
supabase db reset
 
# Commit and push
cd ~/dev/mynatca/discord
git add supabase/migrations/
git commit -m "Add migration: add new discord feature"
git push

Migration Naming Convention

Format

YYYYMMDDHHmmss_description.sql

Baseline Migrations

The MyNATCA platform uses baseline migrations that capture the complete production schema state as of December 6, 2025. These replace all historical migration files.

00000000000001_baseline_platform_schema.sql  (platform - public schema)
00000000000001_baseline_pay_schema.sql       (pay - pay schema + grants)
00000000000001_baseline_discord_schema.sql   (discord - discord schema + grants)

Why baseline migrations?

Clean migration history
Faster database resets
Easier onboarding for new developers
Prevents old migration conflicts

Regular Migrations

All new migrations created after the baseline:

20251215120000_add_facility_ratings_table.sql
20251215130000_pay_add_leave_tracking.sql
20251215140000_discord_add_role_persistence.sql

Best Practices:

Use descriptive names (explain WHAT, not HOW)
Prefix subproject migrations with schema name (e.g., pay_, discord_)
Use lowercase with underscores
Keep names concise but clear
Include ticket/issue number if applicable

Examples:

Good:

add_member_verification_status.sql
pay_add_shift_approval_workflow.sql
discord_add_role_sync_table.sql
fix_positions_enddate_constraint.sql

Bad:

migration.sql (not descriptive)
new_table.sql (not descriptive)
AddMemberVerificationStatus.sql (use lowercase)
add-member-verification-status.sql (use underscores, not dashes)

Working on Multiple Branches

The symlink approach handles multi-branch development gracefully:

# Example: Working on pay feature while platform is on different branch
 
# Platform is on main branch
cd ~/dev/mynatca/platform
git checkout main
 
# Pay is on feature branch
cd ~/dev/mynatca/pay
git checkout feature/new-pay-calculator
 
# Symlinks in platform/supabase/migrations/ now point to:
# - Platform main branch migrations
# - Pay feature/new-pay-calculator branch migrations
# - Discord main branch migrations (or whatever branch discord is on)
 
# When you run db reset from platform:
cd ~/dev/mynatca/platform
supabase db reset
 
# It applies the MIX of migrations from different branches

Important: This is powerful but requires awareness of which branches you're on in each repository.

Best practices for multi-branch work:

Keep migration files backward-compatible when possible
Coordinate with team when working on breaking changes
Use feature branches for experimental schema changes
Always test migrations on a fresh database reset

Schema Protection

Read-Only Access to Public Schema

Sub-projects (Pay, Discord, BID) should only read from the public schema. Write operations must go through the Platform's API or be done by the Platform team.

Enforcement: Use application-level query builders to prevent write operations:

# Example from PayChecker (pay_checker/api/supabase_client.py)
class SchemaAwareQueryBuilder:
    """
    Wrapper that enforces schema-level permissions.
    Prevents write operations on read-only schemas.
    """
    READ_ONLY_SCHEMAS = ["public"]
 
    def insert(self, *args, **kwargs):
        """Allow INSERT only on writable schemas."""
        self._check_write_permission("INSERT")
        return self._builder.insert(*args, **kwargs)
 
    def _check_write_permission(self, operation):
        if self._schema_name in self.READ_ONLY_SCHEMAS:
            raise PermissionError(
                f"Cannot {operation} on read-only schema '{self._schema_name}'. "
                f"PayChecker can only write to 'pay' schema tables."
            )

Migration Guidelines:

✅ DO: Create foreign keys from your schema to public tables (e.g., pay.shifts.membernumber → public.members.membernumber)
✅ DO: Query public schema tables in your application code
❌ DON'T: Create migrations that modify public schema tables from sub-projects
❌ DON'T: Add triggers/functions that write to public schema from sub-projects

Typical Development Workflow

Daily Workflow

# 1. Pull latest changes from all repositories
cd ~/dev/mynatca/platform && git pull
cd ~/dev/mynatca/pay && git pull
cd ~/dev/mynatca/discord && git pull
 
# 2. Git hooks automatically update symlinks after pull (via post-merge hook)
 
# 3. Reset local database and seed all data
cd ~/dev/mynatca/platform
./scripts/db-refresh.sh  # Resets DB, applies migrations, seeds all projects

Creating and Testing a New Migration

# 1. Make your schema changes in Supabase Studio
open http://127.0.0.1:54323
 
# 2. Generate migration from your changes
cd ~/dev/mynatca/[your-project]
supabase db diff -f descriptive_migration_name
 
# 3. Review the generated migration
cat supabase/migrations/YYYYMMDDHHmmss_descriptive_migration_name.sql
 
# 4. Test the migration
cd ~/dev/mynatca/platform
supabase db reset
# Verify your schema changes are correct
 
# 5. Commit the migration
cd ~/dev/mynatca/[your-project]
git add supabase/migrations/
git commit -m "Add migration: descriptive migration name"
 
# 6. Push to remote
git push origin feature/your-feature-branch
 
# 7. Create Pull Request
# Migrations will be reviewed as part of PR process

Git Hooks Explained

Platform Repository Hooks

Location: platform/.git/hooks/

post-checkout:

Runs scripts/link-migrations.sh when switching branches
Updates symlinks to point to correct subproject branch migrations

post-merge:

Runs scripts/link-migrations.sh after merging or pulling
Ensures symlinks are current after integrating changes

pre-commit:

Blocks direct commits to main and staging branches
Enforces Pull Request workflow

Subproject Repository Hooks (Pay, Discord)

Location: pay/.git/hooks/, discord/.git/hooks/

post-checkout:

Runs ../platform/scripts/link-migrations.sh when switching branches
Updates symlinks in platform repository

post-merge:

Runs ../platform/scripts/link-migrations.sh after merging or pulling
Keeps platform symlinks synchronized

pre-commit:

Blocks direct commits to main and staging branches
Enforces Pull Request workflow

Manual Symlink Management

If git hooks fail or you need to manually update symlinks:

cd ~/dev/mynatca/platform
./scripts/link-migrations.sh

This script:

Scans ../pay/supabase/migrations/ for .sql files
Scans ../discord/supabase/migrations/ for .sql files
Creates symlinks in platform/supabase/migrations/ for each file
Updates existing symlinks if they point to wrong locations
Skips symlinks that are already correct

Production Deployment Workflow

1. Create Migrations in Feature Branches

# Create feature branch
git checkout -b feature/add-new-table
 
# Create migration
supabase db diff -f add_new_table
 
# Test locally
cd ~/dev/mynatca/platform
supabase db reset
 
# Commit and push
git add supabase/migrations/
git commit -m "Add migration: add new table"
git push origin feature/add-new-table

2. Create Pull Request

PR includes the new migration file
Team reviews migration for:
- Correctness
- Performance impact
- Data safety (no destructive changes without backups)
- Backward compatibility
- Proper schema ownership

3. Merge to Staging

# After PR approval, merge to staging branch
git checkout staging
git merge feature/add-new-table
git push origin staging

Staging deployment automatically applies migrations.

4. Test in Staging

Verify migration works correctly in staging environment:

Check database schema
Test application functionality
Verify data integrity

5. Merge to Main (Production)

# After staging verification, merge to main
git checkout main
git merge staging
git tag v1.0.1  # Tag release
git push origin main --tags

Production deployment automatically applies migrations.

6. Verify Production

After deployment:

Check migration was applied successfully
Verify application functionality
Monitor for errors

Troubleshooting

Symlinks Not Updating

Symptom: Migrations from pay/discord aren't appearing in platform

Solution:

# Manually run link script
cd ~/dev/mynatca/platform
./scripts/link-migrations.sh
 
# Verify git hooks are installed
ls -la .git/hooks/ | grep -E 'post-checkout|post-merge'
 
# Re-install hooks if missing
./scripts/setup-git-hooks.sh

Migration Conflicts

Symptom: Two migrations have the same timestamp

Solution:

Rename one migration file to have a later timestamp
Update symlink: ./scripts/link-migrations.sh
Apply migrations: supabase db reset

Broken Symlinks

Symptom: Symlinks point to non-existent files (red color in ls -la)

Solution:

# Remove broken symlinks
cd ~/dev/mynatca/platform/supabase/migrations
find . -xtype l -delete
 
# Recreate all symlinks
cd ~/dev/mynatca/platform
./scripts/link-migrations.sh

"Migration Already Exists" Error

Symptom: Supabase complains about duplicate migrations

Solution:

# Check for duplicate migration files
cd ~/dev/mynatca/platform/supabase/migrations
ls -la | sort
 
# Look for:
# - Same timestamp used twice
# - Same migration file committed in two repos
# - Broken symlinks pointing to wrong files
 
# Fix by renaming or removing duplicate migrations

Migration Fails on Production

Symptom: Migration works locally but fails on production

Common causes:

Data differences between local and production
Missing database extensions
Insufficient permissions
Constraints conflict with existing data

Solution:

Review migration for production-specific issues
Check production data that might violate new constraints
Consider adding conditional logic or data cleanup steps
Test on staging environment first
Have rollback plan ready

Schema Not Found

Symptom: Error like schema "pay" does not exist

Solution:

# Ensure baseline migration creates the schema
# Check baseline migration includes:
CREATE SCHEMA IF NOT EXISTS pay;
 
# Verify config.toml includes schema
cd ~/dev/mynatca/platform
grep "schemas =" supabase/config.toml
# Should show: schemas = ["public", "pay", "discord"]
 
# If missing, add to config.toml:
# schemas = ["public", "pay", "discord"]
 
# Reset database
supabase db reset

Best Practices

DO ✅

Install git hooks in each repository you work on
Run link-migrations.sh manually if unsure about symlink state
Test migrations locally before pushing: supabase db reset
Keep migrations idempotent (can run multiple times safely)
Document breaking changes in migration comments
Use descriptive migration names
Include rollback instructions in migration comments for complex changes
Test migrations on a fresh database (not just incremental)
Review migrations carefully in PRs

DON'T ❌

Manually create symlinks (use the script)
Commit symlinks to git (they're gitignored and auto-generated)
Modify migrations after they've been applied to staging/production
Write to public schema from sub-project migrations
Skip testing with db reset before pushing
Create migrations in the wrong repository
Use timestamps that could conflict (let Supabase CLI generate them)
Make destructive changes without team coordination
Forget to add proper constraints and indexes

Migration Checklist

Before committing a migration:

Summary

The symlink workflow provides:

Autonomy - Each team manages their own schema and migrations
Simplicity - Uses standard Supabase CLI, no custom tooling
Flexibility - Work on multiple projects and branches simultaneously
Automation - Git hooks keep everything synchronized
Safety - Application-level protection prevents cross-schema writes

Database Migrations

Overview

Schema Ownership

Repository Structure

Initial Setup

1. Install Git Hooks (One-Time Setup)

2. Link Subprojects

Creating New Migrations

Platform Migrations (Public Schema)

Pay Migrations (Pay Schema)

Discord Migrations (Discord Schema)

Migration Naming Convention

Format

Baseline Migrations

Regular Migrations

Working on Multiple Branches

Schema Protection

Read-Only Access to Public Schema

Typical Development Workflow

Daily Workflow

Creating and Testing a New Migration

Git Hooks Explained

Platform Repository Hooks

Subproject Repository Hooks (Pay, Discord)

Manual Symlink Management

Production Deployment Workflow

1. Create Migrations in Feature Branches

2. Create Pull Request

3. Merge to Staging

4. Test in Staging

5. Merge to Main (Production)

6. Verify Production

Troubleshooting

Symlinks Not Updating

Migration Conflicts

Broken Symlinks

"Migration Already Exists" Error

Migration Fails on Production

Schema Not Found

Best Practices

DO ✅

DON'T ❌

Migration Checklist

Summary

Related Documentation