Migrations & Schema Design

Migrations & Schema Design

Migrations give you version-controlled schema changes. Every ALTER TABLE or CREATE TABLE should go in a migration file.

CLI Workflow

# Initialize local Supabase (first time)
supabase init
supabase start  # starts local Docker instance

# Create a migration
supabase migration new create_progress_table

# Apply migrations to local
supabase db reset  # drops and recreates from all migrations

# Push to remote
supabase db push

# Pull remote schema changes
supabase db pull

Migration File

-- supabase/migrations/20240901_create_progress.sql

CREATE TABLE IF NOT EXISTS progress (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  course_id TEXT NOT NULL,
  completed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  xp_earned INT NOT NULL DEFAULT 0,
  quiz_score INT,
  UNIQUE (user_id, course_id)
);

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

-- Policies
CREATE POLICY "Users read own progress"
  ON progress FOR SELECT USING (user_id = auth.uid());

CREATE POLICY "Users insert own progress"
  ON progress FOR INSERT WITH CHECK (user_id = auth.uid());

-- Index for fast user lookups
CREATE INDEX progress_user_id_idx ON progress (user_id);

Schema Design Principles

-- Use UUID primary keys (Supabase default)
id UUID PRIMARY KEY DEFAULT gen_random_uuid()

-- Timestamp columns (always include)
created_at TIMESTAMPTZ DEFAULT now()
updated_at TIMESTAMPTZ DEFAULT now()

-- Soft deletes (instead of DELETE)
deleted_at TIMESTAMPTZ  -- null = active, non-null = deleted

-- Foreign keys with cascade
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE

-- Use TEXT not VARCHAR (Postgres internally identical, TEXT is simpler)
title TEXT NOT NULL