Database Migrations

Creating New Migrations

CRITICAL: Always use npx supabase migration new <name> to create migration files. Never create them manually.

npx supabase migration new descriptive_migration_name

This creates a properly timestamped migration file in supabase/migrations/.

Applying Migrations

When changes are already applied to the database (e.g., via direct SQL execution), mark the migration as applied without re-running:

# Mark migration as applied (skip execution)
npx supabase migration repair --status applied <timestamp>

# Example:
npx supabase migration repair --status applied 20260122214732

Pushing New Migrations

For changes not yet applied to the database:

npx supabase db push

Listing Migrations

# Show recent migrations
npx supabase migration list | tail -5

Migration Best Practices

1. ALWAYS create migrations with CLI - Use npx supabase migration new never manual file creation
2. Data migrations first - Add columns, then migrate data, then drop old structures
3. Test migrations locally if possible (though we use remote DB)
4. Use transactions for complex data migrations
5. Verify after migration - Query the data to ensure migration succeeded

Type Generation

Regenerating Types from Database Schema

After any schema changes, regenerate TypeScript types from the live database:

# Generate from remote database (recommended for this project)
npx supabase gen types typescript --linked > src/integrations/supabase/types.ts

# If local Supabase is running (Docker required)
npx supabase gen types typescript --local > src/integrations/supabase/types.ts

Location: Types are generated to src/integrations/supabase/types.ts

When to Regenerate Types

Regenerate types after:

• Adding new columns to tables
• Creating new tables
• Modifying column types
• Adding/modifying enums
• Database schema migrations

IMPORTANT: The types file is generated from the database schema, not manually edited.

Edge Functions

Creating New Edge Functions

# Create new function directory
npx supabase functions new my-function-name

# Or create directory manually in supabase/functions/<function-name>/

CRITICAL: Always set verify_jwt = false in supabase/config.toml for new functions. The JWT verification uses the anon key which is not useful for our use case.

Adding Function to Config

After creating a function, add it to supabase/config.toml:

[functions.my-function-name]
enabled = true
verify_jwt = false  # Always false for this project
import_map = "./functions/my-function-name/deno.json"
entrypoint = "./functions/my-function-name/index.ts"

Deploying Edge Functions

npx supabase functions deploy my-function-name

Deleting Edge Functions

To properly remove an edge function:

1. Delete from remote Supabase:

   npx supabase functions delete function-name
   

2. Remove local directory:

   rm -rf supabase/functions/function-name
   

3. Remove from config.toml: Delete the [functions.function-name] section from supabase/config.toml

Complete Edge Function Deletion Example

# 1. Delete from remote
npx supabase functions delete create-team-member

# 2. Delete local directory
rm -rf supabase/functions/create-team-member

# 3. Edit config.toml to remove the function section
# Remove lines like:
# [functions.create-team-member]
# enabled = true
# verify_jwt = false
# import_map = "./functions/create-team-member/deno.json"
# entrypoint = "./functions/create-team-member/index.ts"

Listing Edge Functions

# List all functions
ls supabase/functions/

# Check config for function definitions
grep -A 4 "\[functions" supabase/config.toml

Database Query Execution

Running SQL Queries

This project uses a custom Supabase CLI wrapper:

# Run a query
bun run supabase:sql --query "SELECT COUNT(*) FROM profiles"

# Alternative: Use the generated TypeScript CLI
./supabase-cli execute-sql --query "SELECT COUNT(*) FROM profiles"

Common Workflows

Complete Schema Change Workflow

When making database schema changes:

1. Create migration:

   npx supabase migration new describe_the_change
   

2. Write migration SQL in the created file

3. Apply migration:

   npx supabase db push
   

4. Regenerate types:

   npx supabase gen types typescript --linked > src/integrations/supabase/types.ts
   

5. Update frontend code to use new types/fields

Database Investigation Workflow

When investigating database state:

# Use bun run supabase:sql --query for quick queries
bun run supabase:sql --query "SELECT * FROM profiles LIMIT 5"

# Or use the generated CLI
./supabase-cli execute-sql --query "SELECT * FROM app_roles"

Project-Specific Notes

This Project's Supabase Setup

• Database: Remote Supabase (project ref: sbdlvtfjsdldekqjzngh)
• Local Docker: Not configured (use --linked flag for remote operations)
• Type Location: src/integrations/supabase/types.ts
• Migrations Location: supabase/migrations/
• Functions Location: supabase/functions/
• Config Location: supabase/config.toml

Key Learnings

• Always use migration repair when changes are already applied to remote DB
• Regenerate types after every schema change - don't manually edit types
• verify_jwt = false is standard for this project's edge functions
• Use --linked flag for remote database operations (no local Docker)
• Delete edge functions completely - remote, local files, AND config.toml