Database Development Workflow

Complete workflow for making database changes, from schema modifications to updated application types.

Step-by-Step Process

1. Create Feature Branch (optional but recommended)

# Always start from main and create a new branch
git checkout main
git pull origin main
git checkout -b feature/descriptive-name

Examples:

git checkout -b feature/add-entities-table
git checkout -b feature/add-user-preferences
git checkout -b fix/processing-jobs-index

2. Create Migration File

# From project root
supabase migration new DESCRIPTIVE_NAME

Examples:

supabase migration new add_entities_table
supabase migration new add_user_preferences
supabase migration new fix_processing_jobs_index

Creates: supabase/migrations/20250809123456_descriptive_name.sql

3. Add SQL to Migration

Edit the generated file:

-- Example: Create table in content schema
CREATE TABLE content.entities (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name TEXT NOT NULL,
    legal_form TEXT,
    register_court TEXT,
    register_number TEXT,
    address TEXT,
    si_document_xml TEXT,
    si_document_retrieved_at TIMESTAMP WITH TIME ZONE,
    processing_status TEXT DEFAULT 'basic_discovery',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add indexes
CREATE INDEX idx_entities_name ON content.entities(name);
CREATE INDEX idx_entities_status ON content.entities(processing_status);

4. Add Development Data

Reset database and add seed data:

# From project root
npm run db:reset

This command:

1. Resets the local database (supabase db reset --no-seed)
2. Runs the seeding script to create test data and users

The seeding script creates:

• Test users:
  • test1@example.com / password123
  • test2@example.com / password123
• Sample entities and other development data

Note: The script loads environment variables from apps/web/.env.local, so ensure this file exists with your Supabase configuration.

5. Test Migration Locally

Verify in Supabase Studio:

• Open http://127.0.0.1:54323
• Check that tables and data appear correctly
• Navigate through schemas: public, content, processing, system

6. Commit and Push to Branch

git add .
git commit -m "Add entities table with sample data

- Create entities table in content schema
- Add indexes for name and status queries
- Include sample development data
- Generate updated TypeScript types"

# Push to your feature branch
git push origin feature/your-branch-name

# Push to main branch if did not create a feature branch
git push

7. Create Pull Request and Deploy (feature branch only)

On GitHub:

1. Navigate to your repository on GitHub
2. You should see a banner: "feature/your-branch had recent pushes"
3. Click "Compare & pull request" (or create manually via Pull Requests tab)
4. Add descriptive PR title and description
5. Create pull request

Supabase Integration will:

• ✅ Preview Environment: Create a preview branch for testing
• ✅ Migration Check: Validate your migrations work correctly

When ready to deploy: 6. Merge pull request → This triggers production deployment 7. Your migrations are automatically applied to production database 8. Delete the feature branch (optional cleanup)

8. Verify Production Deployment (feature branch only)

# Switch back to main and pull the merged changes
git checkout main
git pull origin main

# Check that migrations were applied to production
supabase migration list

Should show your new migrations in both local and remote columns:

20250813123827 | 20250813123827 | 2025-08-13 12:38:27 ✅