migration-guidelisted

# Database Migration Guide ## Before Creating a Migration 1. Check existing migrations in your migrations directory to understand current schema 2. Check your ORM schema files (Drizzle, Prisma, etc.) for TypeScript definitions 3. If a schema reference doc exists, read it for the full picture 4. Plan the migration — what tables/columns/indexes are being added or modified? ## Creating a New Migration ### Step 1: Create the migration file Use your migration tool to create the file: ```bash # Drizzle (hand-written SQL) # Create next sequential file in your migrations directory # e.g., migrations/00XX_description.sql # Prisma npx prisma migrate dev --name description_here # Supabase CLI npx supabase migration new description_here ``` ### Step 2: Write the SQL - IMPORTANT: Always enable Row Level Security on new tables (Supabase/Postgres): ```sql ALTER TABLE public.new_table ENABLE ROW LEVEL SECURITY; ``` - Make migrations idempotent when possible (`IF NOT EXISTS`, `IF EXISTS`) - Add indexes on columns used in WHERE clauses, JOINs, and all foreign keys - Add NOT NULL constraints where appropriate - Use foreign key constraints to maintain data integrity - Include a comment at the top describing what the migration does Example structure: ```sql -- Migration: Add tags table with many-to-many relationship to jobs -- Date: YYYY-MM-DD CREATE TABLE IF NOT EXISTS public.tags ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES auth.users(i