Production Database Migrations

Complete guide for applying database migrations to the shared Kubernetes Supabase instance.

---

Overview

This guide covers applying schema changes to production databases in the shared Kubernetes Supabase instance. Each app has its own schema (home_portal, money_tracker, etc.), and migrations must be applied with proper schema context.

What you'll learn: - How to apply migrations to production - Best practices for migration safety - Rollback procedures - Tracking migration state - Alternative approaches for future improvement

---

Current Process: Supabase CLI Migration Workflow

The official, recommended approach using Supabase CLI for automated migration management.

Prerequisites

• [ ] Migration file tested in local development
• [ ] Migration is idempotent (can run multiple times safely)
• [ ] Migration includes SET search_path TO <schema_name>;
• [ ] kubectl access to cluster (for port-forwarding)
• [ ] Node.js installed on Proxmox host
• [ ] Database backup taken (if making destructive changes)

Step 1: Prepare Migration File

Ensure your migration file includes the schema context:

-- migrations/20250208000004_add_basic_categories.sql
SET search_path TO money_tracker;

-- Your migration SQL here
CREATE TABLE IF NOT EXISTS categories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  type TEXT NOT NULL
);

-- Always use IF NOT EXISTS or ON CONFLICT for idempotency
INSERT INTO categories (name, type) VALUES
  ('Groceries', 'expense')
ON CONFLICT DO NOTHING;

Key principles: - Always set search_path at the top - Use IF NOT EXISTS, IF TABLE EXISTS, or ON CONFLICT for idempotency - Test in local development first - Include comments explaining what and why

Step 2: Apply Migration with Helper Script

Use the /root/scripts/migrate-app.sh helper script:

# Preview what will be applied (dry-run)
/root/scripts/migrate-app.sh money-tracker --dry-run

# Review the output, then apply for real
/root/scripts/migrate-app.sh money-tracker

What the script does: 1. Reads local migration files from the app's supabase/migrations/ directory 2. Queries remote migration history via kubectl exec psql 3. Compares local migrations against remote history to find pending ones 4. Applies each pending migration via kubectl exec psql 5. Records applied migrations in the tracking table

Why direct SQL instead of Supabase CLI: The shared Supabase instance has migrations from multiple apps. The Supabase CLI's db push command fails when it finds remote migrations without corresponding local files. The script bypasses this by using direct kubectl exec SQL execution, which: - Only considers local migrations (ignores other apps' migrations) - Avoids TLS/port-forward issues entirely - Works reliably with the shared database

Output example:

==> Starting migration for money-tracker...
==> Migration directory: //rpool/data/subvol-150-disk-0/root/money-tracker/supabase/migrations
==> Retrieving database password...
==> Checking for existing port-forwards...
==> Starting fresh port-forward to postgres...
==> Port-forward ready!
==> Checking local migrations...
==> Checking remote migration history...
==> Finding pending migrations...
==> Pending migrations: supabase/migrations/20250208000005_new_feature.sql

==> Applying 20250208000005_new_feature.sql...
    Applied successfully

==> Migration complete! ✓

Step 3: Verify Migration Applied

# Check migration tracking table
kubectl exec -n supabase postgres-0 -- psql -U postgres -d postgres \
  -c "SELECT version, name FROM supabase_migrations.schema_migrations ORDER BY version DESC LIMIT 5;"

# Check tables/data in your schema
kubectl exec -n supabase postgres-0 -- psql -U postgres -d postgres \
  -c "SELECT * FROM money_tracker.categories ORDER BY name LIMIT 10;"

Step 4: Grant Permissions (If Creating New Tables)

IMPORTANT: New tables created by migrations need explicit permissions. Include these in your migration file:

-- At the end of your migration file
GRANT ALL ON my_new_table TO postgres, authenticated, service_role;
GRANT SELECT ON my_new_table TO anon;
GRANT ALL ON ALL SEQUENCES IN SCHEMA money_tracker TO postgres, authenticated, service_role;

Or apply after the fact:

kubectl exec -n supabase postgres-0 -- psql -U postgres -d postgres << 'EOF'
GRANT ALL ON ALL TABLES IN SCHEMA money_tracker TO postgres, authenticated, service_role;
GRANT SELECT ON ALL TABLES IN SCHEMA money_tracker TO anon;
GRANT ALL ON ALL SEQUENCES IN SCHEMA money_tracker TO postgres, authenticated, service_role;
EOF

Step 5: Restart PostgREST (If Schema Changed)

If you added new tables, restart PostgREST:

kubectl rollout restart deployment -n supabase rest
kubectl rollout status deployment -n supabase rest --timeout=2m

---

Best Practices

1. Always Test Locally First

Run migrations in local Supabase before production:

# In your app's project directory (on Proxmox host)
cd /root/projects/money-tracker
npx supabase db reset  # Reset to clean state
npx supabase migration up  # Apply migrations
npm run dev  # Test app works

2. Make Migrations Idempotent

Migrations should be safe to run multiple times:

Good:

CREATE TABLE IF NOT EXISTS categories (...);

INSERT INTO categories (id, name) VALUES ('...', 'Groceries')
ON CONFLICT (id) DO NOTHING;

ALTER TABLE transactions
ADD COLUMN IF NOT EXISTS merchant TEXT;

Bad:

CREATE TABLE categories (...);  -- Fails if table exists

INSERT INTO categories (name) VALUES ('Groceries');  -- Creates duplicates

ALTER TABLE transactions ADD COLUMN merchant TEXT;  -- Fails if column exists

3. Use Transactions for Multi-Step Migrations

BEGIN;

SET search_path TO money_tracker;

-- Step 1
CREATE TABLE IF NOT EXISTS new_table (...);

-- Step 2
ALTER TABLE old_table ADD COLUMN new_col TEXT;

-- Step 3
UPDATE old_table SET new_col = 'default';

COMMIT;

If anything fails, the entire migration rolls back automatically.

4. Document Breaking Changes

If a migration requires app changes (e.g., renamed columns), document in the migration:

-- BREAKING CHANGE: Renamed column 'desc' to 'description'
-- Apps must update queries from .select('desc') to .select('description')
-- Deployed in: money-tracker v2.1.0

ALTER TABLE transactions
RENAME COLUMN desc TO description;

5. Keep Migration Files in Git

Commit migration files to your app's repository:

cd /root/projects/money-tracker
git add supabase/migrations/20250208000004_add_categories.sql
git commit -m "feat: add category system

- Add categories table
- Add category_mappings table for CSV imports
- Seed basic categories and Chase mappings"
git push

---

Common Migration Patterns

Adding a New Table

SET search_path TO money_tracker;

CREATE TABLE IF NOT EXISTS budgets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  category_id UUID REFERENCES categories(id),
  amount NUMERIC(12, 2) NOT NULL,
  period TEXT NOT NULL CHECK (period IN ('monthly', 'weekly', 'yearly')),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_budgets_user ON budgets(user_id);
CREATE INDEX IF NOT EXISTS idx_budgets_category ON budgets(category_id);

-- Enable RLS
ALTER TABLE budgets ENABLE ROW LEVEL SECURITY;

-- Create policies
DROP POLICY IF EXISTS "Users can view own budgets" ON budgets;
CREATE POLICY "Users can view own budgets" ON budgets
  FOR SELECT USING (auth.uid() = user_id);

Adding a Column

SET search_path TO money_tracker;

-- Add column with default
ALTER TABLE transactions
ADD COLUMN IF NOT EXISTS merchant TEXT;

-- Add index if needed for queries
CREATE INDEX IF NOT EXISTS idx_transactions_merchant ON transactions(merchant);

Seeding Data

SET search_path TO money_tracker;

-- Use ON CONFLICT to make idempotent
INSERT INTO categories (name, type, icon, color) VALUES
  ('Groceries', 'expense', '🛒', '#10b981'),
  ('Restaurants', 'expense', '🍽️', '#f59e0b')
ON CONFLICT DO NOTHING;

-- Or use specific conflict resolution
INSERT INTO category_mappings (source, source_category, target_category_id)
SELECT 'chase', 'Food & Drink', id
FROM categories
WHERE name = 'Restaurants' AND user_id IS NULL
ON CONFLICT (source, source_category) DO UPDATE
SET target_category_id = EXCLUDED.target_category_id;

Renaming Columns (Backward Compatible)

SET search_path TO money_tracker;

-- Option 1: Add new column, copy data, keep old column temporarily
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS description TEXT;
UPDATE transactions SET description = desc WHERE description IS NULL;
-- Don't drop old column yet - apps need to update first

-- Option 2: Use a view for backward compatibility
CREATE OR REPLACE VIEW transactions_view AS
SELECT
  id,
  description as desc,  -- Old name
  description,          -- New name
  amount,
  created_at
FROM transactions;

-- Apps can migrate gradually from transactions_view to transactions

---

Rollback Procedures

Simple Rollback (Recent Migration)

If you just applied a migration and need to revert:

# Connect to postgres
kubectl exec -n supabase postgres-0 -- psql -U postgres -d postgres

# Manually reverse the changes
# Example: if you added a table
DROP TABLE IF EXISTS money_tracker.new_table;

# If you added a column
ALTER TABLE money_tracker.transactions DROP COLUMN IF EXISTS new_column;

# If you inserted data
DELETE FROM money_tracker.categories WHERE name = 'New Category';

Rollback with Down Migrations

Create a corresponding down migration:

-- migrations/20250208000004_add_categories.sql (up)
SET search_path TO money_tracker;
CREATE TABLE categories (...);

-- migrations/20250208000004_add_categories_down.sql (down)
SET search_path TO money_tracker;
DROP TABLE IF EXISTS categories CASCADE;

Apply the down migration:

kubectl cp migrations/20250208000004_add_categories_down.sql \
  supabase/postgres-0:/tmp/rollback.sql

kubectl exec -n supabase postgres-0 -- psql -U postgres -d postgres -f /tmp/rollback.sql

Emergency: Restore from Backup

If migration caused data loss or corruption:

# 1. Stop applications from writing
kubectl scale deployment -n money-tracker money-tracker --replicas=0

# 2. Restore database from backup
# (See /root/tower-fleet/docs/reference/disaster-recovery.md)

# 3. Restart applications
kubectl scale deployment -n money-tracker money-tracker --replicas=1

---

Ongoing Workflow: Day-to-Day Usage

Once set up, applying migrations is simple:

For New Migrations

# 1. Create migration in your app (on Proxmox host)
cd /root/projects/money-tracker
npx supabase migration new add_my_feature

# 2. Edit the migration file
# Add: SET search_path TO money_tracker; at the top
# Write your SQL changes

# 3. Test locally
npx supabase db reset
npm run dev  # Verify app works

# 4. Apply to production (from Proxmox host)
/root/scripts/migrate-app.sh money-tracker --dry-run  # Preview
/root/scripts/migrate-app.sh money-tracker             # Apply

# 5. Commit the migration
git add supabase/migrations/
git commit -m "feat: add my feature

- Add new table/column/etc
- Migration: 20250XXX_add_my_feature"
git push

For Multiple Apps

# money-tracker
/root/scripts/migrate-app.sh money-tracker

# home-portal
/root/scripts/migrate-app.sh home-portal

# rms
/root/scripts/migrate-app.sh rms

That's it! Supabase CLI handles: - ✅ Tracking which migrations have been applied - ✅ Only applying new migrations - ✅ Transaction safety (all or nothing) - ✅ Error handling

---

Troubleshooting

Migration Fails: Permission Denied

Problem:

ERROR: permission denied for schema money_tracker

Solution:

kubectl exec -n supabase postgres-0 -- psql -U postgres -d postgres << 'EOF'
GRANT USAGE ON SCHEMA money_tracker TO postgres, authenticated, service_role;
GRANT ALL ON SCHEMA money_tracker TO postgres;
EOF

TLS Connection Error with Supabase CLI

Problem:

failed to connect to postgres: tls error (server refused TLS connection)

This occurs when the Supabase CLI tries to use TLS over a port-forward connection, which doesn't support SSL.

Recommended Solution: Use migrate-app.sh

The /root/scripts/migrate-app.sh script avoids this issue entirely by using kubectl exec instead of port-forwarding:

/root/scripts/migrate-app.sh money-tracker

Alternative: Direct kubectl exec

# Apply migration directly without port-forward
kubectl exec -i -n supabase postgres-0 -- psql -U postgres -d postgres < supabase/migrations/YYYYMMDD_migration.sql

# Or for inline SQL:
kubectl exec -n supabase postgres-0 -- psql -U postgres -d postgres << 'EOF'
SET search_path TO trip_planner;
ALTER TABLE trips ADD COLUMN cover_image_url TEXT;
EOF

If you must use Supabase CLI with port-forward:

# The CLI ignores sslmode in URL - must use environment variable
PGSSLMODE=disable npx supabase db push --db-url "postgresql://postgres:PASSWORD@127.0.0.1:5432/postgres"

Note: Check for stale port-forwards first with lsof -t -i :5432 and kill them if found.

After applying migrations, restart PostgREST:

kubectl rollout restart deployment -n supabase rest

Tables Not Accessible via API

Problem: Created table but getting 404 from Supabase client

Solution: 1. Grant permissions on tables 2. Restart PostgREST

# Grant permissions
kubectl exec -n supabase postgres-0 -- psql -U postgres -d postgres << 'EOF'
GRANT ALL ON ALL TABLES IN SCHEMA money_tracker TO postgres, authenticated, service_role;
GRANT SELECT ON ALL TABLES IN SCHEMA money_tracker TO anon;
EOF

# Restart PostgREST
kubectl rollout restart deployment -n supabase rest

Foreign Key Constraint Failures

Problem:

ERROR: insert or update on table "transactions" violates foreign key constraint

Solution: Ensure referenced tables/rows exist before adding constraints:

-- Check if category exists before creating FK
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM money_tracker.categories WHERE id = '...') THEN
    ALTER TABLE money_tracker.transactions
    ADD CONSTRAINT fk_category
    FOREIGN KEY (category_id) REFERENCES money_tracker.categories(id);
  END IF;
END $$;

---

Migration Tracking

Current State: Manual Tracking

Currently, migration state is tracked manually in: - App repository: supabase/migrations/ directory - Git commit history - This documentation

To check what's applied:

# List all tables in schema
kubectl exec -n supabase postgres-0 -- psql -U postgres -d postgres \
  -c "\dt money_tracker.*"

# Check specific table structure
kubectl exec -n supabase postgres-0 -- psql -U postgres -d postgres \
  -c "\d money_tracker.categories"

Future: Automated Tracking

See Proposed Alternatives below for migration tracking improvements.

---

Proposed Alternatives: Better Approaches

This section documents better approaches we could implement in the future to improve the migration workflow.

1. Supabase CLI with Remote Database

Current limitation: We use Supabase CLI locally, but apply migrations manually to production.

Better approach: Configure Supabase CLI to connect directly to production:

# In app directory
supabase link --project-ref prod-money-tracker

# Apply migrations to production
supabase db push

# View migration status
supabase migration list

Benefits: - ✅ Automatic migration tracking - ✅ Migration history visibility - ✅ Built-in rollback support - ✅ Ensures local and production stay in sync

Implementation requirements: - [ ] Configure supabase/config.toml for each app - [ ] Set up DB connection credentials - [ ] Document project linking process - [ ] Test with non-critical app first

References: - Supabase CLI Remote Migrations - Database Connection String

---

2. Schema Migration Table

Current limitation: No database-level tracking of which migrations have been applied.

Better approach: Create schema_migrations table to track applied migrations:

CREATE TABLE IF NOT EXISTS money_tracker.schema_migrations (
  version TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  applied_at TIMESTAMPTZ DEFAULT NOW(),
  checksum TEXT,  -- MD5 hash of migration file
  execution_time_ms INTEGER
);

-- Example usage
INSERT INTO schema_migrations (version, name, checksum)
VALUES ('20250208000004', 'add_basic_categories', md5('...'));

Benefits: - ✅ Know exactly what's applied to production - ✅ Prevent duplicate migrations - ✅ Audit trail of schema changes - ✅ Compare local vs production state

Implementation: - [ ] Create migration tracking table - [ ] Update migration scripts to record themselves - [ ] Build CLI tool to check migration status - [ ] Add to monitoring/alerting

---

3. CI/CD Pipeline for Migrations

Current limitation: Migrations applied manually when someone remembers.

Better approach: Automated migration on git push:

# .github/workflows/migrate.yml
name: Apply Database Migrations

on:
  push:
    branches: [main]
    paths:
      - 'supabase/migrations/**'

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - name: Setup Supabase CLI
        run: npm install -g supabase
      - name: Apply migrations
        run: supabase db push
        env:
          SUPABASE_DB_URL: ${{ secrets.PROD_DB_URL }}

Benefits: - ✅ Automatic application on merge to main - ✅ No manual steps to remember - ✅ Consistent deployment process - ✅ Audit trail via GitHub Actions logs

Implementation: - [ ] Set up GitHub Actions workflows - [ ] Configure production DB credentials as secrets - [ ] Test with staging environment first - [ ] Add rollback action for emergencies

---

4. Migration Preview/Dry Run

Current limitation: Can't see what will change before applying.

Better approach: Generate diff of schema changes:

# Compare local migrations to production
supabase db diff --schema money_tracker

# Output:
# + CREATE TABLE budgets (...)
# + ALTER TABLE transactions ADD COLUMN merchant TEXT
# - No destructive changes detected

Benefits: - ✅ Review changes before applying - ✅ Catch mistakes early - ✅ Generate migration from schema changes - ✅ Confidence in production deployments

Tools: - Supabase CLI db diff - PostgreSQL pg_dump --schema-only for comparison - Custom diffing scripts

---

5. Staging Environment

Current limitation: Testing migrations in local dev, then applying directly to production.

Better approach: Add staging Kubernetes Supabase instance:

Local Dev → Staging → Production
   ↓          ↓          ↓
 Local DB   Stage DB   Prod DB

Benefits: - ✅ Test migrations in production-like environment - ✅ Catch issues before they affect users - ✅ Validate app compatibility with schema changes - ✅ Safe experimentation space

Implementation: - [ ] Deploy second Supabase instance (staging) - [ ] Use separate namespace: supabase-staging - [ ] Configure apps to point to staging for testing - [ ] Document promotion process: staging → prod

Resource cost: ~3GB RAM for staging Supabase instance

---

6. Blue-Green Database Migrations

Current limitation: Migrations cause downtime for schema changes.

Better approach: Expand-contract pattern for zero-downtime migrations:

-- Phase 1: EXPAND (backward compatible)
-- Add new column, keep old column
ALTER TABLE transactions ADD COLUMN description TEXT;
UPDATE transactions SET description = desc;

-- Deploy app v2 (writes to both columns)

-- Phase 2: CONTRACT (remove old schema)
-- After all apps updated, drop old column
ALTER TABLE transactions DROP COLUMN desc;

Benefits: - ✅ Zero downtime deployments - ✅ Gradual rollout of schema changes - ✅ Easy rollback (just redeploy old app version) - ✅ Safe for production systems

Implementation: - [ ] Document expand-contract patterns - [ ] Build migration templates - [ ] Update deployment guide

---

7. Database Schema as Code (Declarative)

Current limitation: Imperative migrations (step-by-step SQL changes).

Better approach: Declare desired schema state, auto-generate migrations:

// schema/categories.ts
export const categories = table('categories', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  type: text('type').notNull().check(type => type.in(['income', 'expense'])),
  icon: text('icon'),
  color: text('color'),
  createdAt: timestamp('created_at').defaultNow()
})

// Run: npx db-schema diff
// Generates migration: 20250208_add_categories.sql

Benefits: - ✅ Single source of truth for schema - ✅ Auto-generate migrations from code changes - ✅ Type-safe database schemas - ✅ Easier refactoring

Tools: - Drizzle ORM - Prisma Migrate - Atlas

---

Comparison: Current vs Proposed

Aspect	Current Approach	Proposed Alternative
Migration application	Manual kubectl exec	Supabase CLI db push
Migration tracking	Git history only	schema_migrations table
Testing	Local dev only	Local → Staging → Prod
Deployment	Manual when remembered	CI/CD automated
Rollback	Manual SQL reversal	Supabase CLI db rollback
Schema preview	None	db diff before apply
Downtime	Possible for breaking changes	Zero-downtime expand-contract
Type safety	Raw SQL	Schema as code (Drizzle/Prisma)

---

Next Steps

To implement these improvements:

1. Short term (1-2 weeks):
2. [ ] Add schema_migrations tracking table
3. [ ] Create migration checklist template

4. [ ] Document all existing production schema state

5. Medium term (1-2 months):

6. [ ] Set up Supabase CLI remote connections
7. [ ] Implement CI/CD for automatic migrations

8. [ ] Add migration dry-run/preview tooling

9. Long term (3-6 months):

10. [ ] Deploy staging Supabase environment
11. [ ] Evaluate schema-as-code tools (Drizzle/Prisma)
12. [ ] Implement blue-green migration patterns

Feedback welcome: If you have experience with any of these approaches or other suggestions, please contribute!

---

Cross-Environment Schema Migration

When migrating an app from sandbox to production Supabase, or renaming a schema:

Export Data from Source

# Export schema data (excludes owner/ACL for clean import)
kubectl exec -n supabase-sandbox postgres-0 -c postgres -- pg_dump -U postgres \
  --schema=old_schema --no-owner --no-acl --data-only postgres > schema_data.sql

# Optional: export schema structure for reference
kubectl exec -n supabase-sandbox postgres-0 -c postgres -- pg_dump -U postgres \
  --schema=old_schema --no-owner --no-acl --schema-only postgres > schema_structure.sql

Transform Schema Name (if renaming)

# Rename schema references in data dump
sed -i 's/old_schema\./new_schema./g' schema_data.sql
sed -i 's/SET search_path = old_schema/SET search_path = new_schema/g' schema_data.sql

Import to Target

# Copy to production postgres pod
kubectl cp schema_data.sql supabase/postgres-0:/tmp/schema_data.sql -c postgres

# Import with FK constraints disabled (handles dependency order)
kubectl exec -n supabase postgres-0 -c postgres -- psql -U postgres \
  -c "SET session_replication_role = 'replica';" \
  -c "\i /tmp/schema_data.sql" \
  -c "SET session_replication_role = 'origin';"

Verify Data Counts

# Compare row counts between source and target
kubectl exec -n supabase-sandbox postgres-0 -c postgres -- psql -U postgres -c \
  "SELECT 'entities' as tbl, count(*) FROM old_schema.entities UNION ALL
   SELECT 'campaigns', count(*) FROM old_schema.campaigns;"

kubectl exec -n supabase postgres-0 -c postgres -- psql -U postgres -c \
  "SELECT 'entities' as tbl, count(*) FROM new_schema.entities UNION ALL
   SELECT 'campaigns', count(*) FROM new_schema.campaigns;"

Reset Sequences (if using auto-increment)

kubectl exec -n supabase postgres-0 -c postgres -- psql -U postgres << 'EOF'
-- Reset sequence to max value + 1
SELECT setval('new_schema.my_table_id_seq',
  COALESCE((SELECT MAX(id) FROM new_schema.my_table), 0) + 1, false);
EOF

Update App Configuration

1. Update ConfigMap with new PostgreSQL host
2. Update Secret with new password
3. Redeploy app to pick up changes

---

Related Documentation

• Supabase Multi-App Architecture - Schema isolation
• Production App Deployment - Full deployment guide
• Development Environment - Local migration testing
• Disaster Recovery - Backup and restore

---

Last Updated: 2025-11-15 Maintained By: Infrastructure Team Status: Living document - Current process documented, alternatives proposed