Database Migrations Guide

Migrations are version-controlled changes to your database schema. This guide walks through creating, testing, and deploying migrations in the FindU ecosystem.

Migration Workflow Overview

Creating Your First Migration

Create a feature branch

git checkout dev
git pull origin dev
git checkout -b feature/add-user-preferences

Create migration file

# Create a migrations directory in your repo
mkdir -p migrations

# Create a new migration file with timestamp
touch migrations/$(date +%Y%m%d%H%M%S)_add_user_preferences_table.sql

Write your migration

Edit the generated file with your SQL:

-- Create user preferences table
CREATE TABLE IF NOT EXISTS public.user_preferences (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  student_id uuid NOT NULL REFERENCES public.students(id) ON DELETE CASCADE,
  theme text DEFAULT 'light' CHECK (theme IN ('light', 'dark', 'auto')),
  notifications_enabled boolean DEFAULT true,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Add RLS policies
ALTER TABLE public.user_preferences ENABLE ROW LEVEL SECURITY;

-- Students can only see their own preferences
CREATE POLICY "Students can view own preferences" 
  ON public.user_preferences FOR SELECT
  USING (auth.uid() IN (
    SELECT user_id FROM public.profiles WHERE student_id = user_preferences.student_id
  ));

-- Students can update their own preferences
CREATE POLICY "Students can update own preferences"
  ON public.user_preferences FOR UPDATE
  USING (auth.uid() IN (
    SELECT user_id FROM public.profiles WHERE student_id = user_preferences.student_id
  ));

-- Create updated_at trigger
CREATE TRIGGER update_user_preferences_updated_at
  BEFORE UPDATE ON public.user_preferences
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

Test your migration

Go to Supabase dashboard
Navigate to SQL Editor
Paste your migration SQL
Run in a transaction:

BEGIN;
-- Your migration SQL here
-- If it looks good:
COMMIT;
-- If something's wrong:
-- ROLLBACK;

Commit and push

git add .
git commit -m "feat: add user preferences table with RLS"
git push origin feature/add-user-preferences

Create pull request

gh pr create --base dev --title "Add user preferences table" \
  --body "Adds table for storing user theme and notification preferences"

Types of Migrations

Schema Migrations

Creating Tables
Altering Tables
Creating Indexes

-- Always use IF NOT EXISTS for safety
CREATE TABLE IF NOT EXISTS public.feature_flags (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL UNIQUE,
  enabled boolean DEFAULT false,
  rollout_percentage integer DEFAULT 0 CHECK (rollout_percentage BETWEEN 0 AND 100),
  created_at timestamptz DEFAULT now()
);

-- Always add RLS
ALTER TABLE public.feature_flags ENABLE ROW LEVEL SECURITY;

-- Add appropriate indexes
CREATE INDEX idx_feature_flags_name ON public.feature_flags(name);

-- Add columns with defaults for existing rows
ALTER TABLE public.students 
  ADD COLUMN IF NOT EXISTS onboarding_completed boolean DEFAULT false;

-- Rename columns carefully
ALTER TABLE public.students 
  RENAME COLUMN old_name TO new_name;

-- Add constraints
ALTER TABLE public.students
  ADD CONSTRAINT check_gpa CHECK (gpa >= 0 AND gpa <= 5.0);

-- Standard index
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_students_graduation_year 
  ON public.students(graduation_year);

-- Partial index for performance
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_active_conversations
  ON public.conversations(updated_at)
  WHERE closed_at IS NULL;

-- Composite index
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_interactions_student_school
  ON public.student_school_interactions(student_id, school_id);

Data Migrations

Data migrations should be idempotent - running them multiple times should have the same result.

-- Example: Backfill data with proper checks
UPDATE public.students 
SET areas_of_interest = ARRAY['undecided']
WHERE areas_of_interest IS NULL 
  OR array_length(areas_of_interest, 1) = 0;

-- Example: Migrate data between tables
INSERT INTO public.user_preferences (student_id, theme, notifications_enabled)
SELECT 
  id as student_id,
  COALESCE(settings->>'theme', 'light') as theme,
  COALESCE((settings->>'notifications')::boolean, true) as notifications_enabled
FROM public.students
WHERE NOT EXISTS (
  SELECT 1 FROM public.user_preferences WHERE student_id = students.id
);

Creating Functions

-- Utility function for updated_at timestamps
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Domain-specific function
CREATE OR REPLACE FUNCTION calculate_match_score(
  student_id uuid,
  school_id uuid
) RETURNS numeric AS $$
DECLARE
  score numeric;
BEGIN
  -- Your matching logic here
  SELECT 
    CASE 
      WHEN s.gpa >= sch.min_gpa THEN 0.25
      ELSE 0
    END +
    -- Add more scoring logic
    0.5 -- Base score
  INTO score
  FROM public.students s, public.schools sch
  WHERE s.id = student_id AND sch.id = school_id;
  
  RETURN COALESCE(score, 0);
END;
$$ LANGUAGE plpgsql;

Testing Migrations

Local Testing Checklist

Writing Rollback Migrations

For risky changes, prepare a rollback migration:

-- Migration: 20240205123456_add_complex_feature.sql
-- Rollback: 20240205123457_rollback_complex_feature.sql

-- Drop new constraints
ALTER TABLE public.students DROP CONSTRAINT IF EXISTS check_gpa;

-- Drop new columns
ALTER TABLE public.students DROP COLUMN IF EXISTS new_feature;

-- Drop new tables
DROP TABLE IF EXISTS public.user_preferences CASCADE;

Deployment Process

To Development

Automatic: Merging PR to dev branch triggers deployment
Manual: If needed, run supabase db push after switching to dev

To Production

Create PR: From dev to main branch
Review Required: Another developer must approve
Automatic Deploy: Merging triggers production deployment
Monitor: Check Supabase dashboard for any issues

Best Practices

Always Include RLS

Every table should have Row Level Security enabled and appropriate policies

Make Migrations Idempotent

Use IF NOT EXISTS, IF EXISTS, and proper checks

One Concern Per Migration

Keep migrations focused on a single feature or change

Test Thoroughly

Always run supabase db reset before pushing

Common Patterns

Adding Audit Fields

-- Standard audit fields for new tables
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now(),
created_by uuid REFERENCES auth.users(id),
updated_by uuid REFERENCES auth.users(id)

Setting Up RLS

-- Enable RLS
ALTER TABLE public.table_name ENABLE ROW LEVEL SECURITY;

-- Public read access
CREATE POLICY "Public can view" ON public.table_name
  FOR SELECT USING (true);

-- Authenticated write access
CREATE POLICY "Authenticated can insert" ON public.table_name
  FOR INSERT WITH CHECK (auth.role() = 'authenticated');

-- Owner-only access
CREATE POLICY "Users can manage own records" ON public.table_name
  FOR ALL USING (auth.uid() = user_id);

Performance Indexes

-- Foreign key indexes (often missed)
CREATE INDEX idx_table_foreign_key ON public.table_name(foreign_key_id);

-- Timestamp indexes for queries
CREATE INDEX idx_table_created_at ON public.table_name(created_at DESC);

-- Text search indexes
CREATE INDEX idx_table_search ON public.table_name USING gin(to_tsvector('english', name || ' ' || description));

Troubleshooting

Migration fails with 'already exists' error

Your migration isn’t idempotent. Add IF NOT EXISTS checks:

CREATE TABLE IF NOT EXISTS ...
CREATE INDEX IF NOT EXISTS ...
ALTER TABLE ... ADD COLUMN IF NOT EXISTS ...

RLS policies blocking access

Test policies in Supabase dashboard:

Go to Authentication > Policies
Use the policy editor to test with different users
Check policy conditions carefully

Migration works locally but fails in CI

Common causes:

Missing IF EXISTS checks
Dependency on data that doesn’t exist
Different Postgres versions
Timezone issues with timestamps

Need to modify a migration

Never modify a migration that’s been deployed. Instead:

Create a new migration to fix the issue
Include proper rollback handling
Test the upgrade path thoroughly

Next, learn about schema management or data operations.

Getting Started

Developer Guide

Web App

iOS App

Matching Algorithm

Database

AI Development

Migrations Guide

Database Migrations Guide

Migration Workflow Overview

Creating Your First Migration

Types of Migrations

Schema Migrations

Data Migrations

Creating Functions

Testing Migrations

Local Testing Checklist

Writing Rollback Migrations

Deployment Process

To Development

To Production

Best Practices

Always Include RLS

Make Migrations Idempotent

One Concern Per Migration

Test Thoroughly

Common Patterns

Adding Audit Fields

Setting Up RLS

Performance Indexes

Troubleshooting

​Database Migrations Guide

​Migration Workflow Overview

​Creating Your First Migration

​Types of Migrations

​Schema Migrations

​Data Migrations

​Creating Functions

​Testing Migrations

​Local Testing Checklist

​Writing Rollback Migrations

​Deployment Process

​To Development

​To Production

​Best Practices

Always Include RLS

Make Migrations Idempotent

One Concern Per Migration

Test Thoroughly

​Common Patterns

​Adding Audit Fields

​Setting Up RLS

​Performance Indexes

​Troubleshooting

Database Migrations Guide

Migration Workflow Overview

Creating Your First Migration

Types of Migrations

Schema Migrations

Data Migrations

Creating Functions

Testing Migrations

Local Testing Checklist

Writing Rollback Migrations

Deployment Process

To Development

To Production

Best Practices

Common Patterns

Adding Audit Fields

Setting Up RLS

Performance Indexes

Troubleshooting