Database Migrations

scripts/
├── 001_initialize_database.sql
├── 002_create_admin_config_table.sql
├── 003_add_banner_link.sql
├── 004_create_shared_sessions_table.sql
├── 005_fix_site_settings_schema.sql
├── 007_create_feedback_tables.sql
├── 007_create_rate_limit_table.sql
├── 008_create_shared_quizzes_table.sql
├── 010_create_user_profiles.sql
├── 010_create_user_system_tables.sql
├── 012_create_admin_user.sql
├── 013_create_ai_workspace_tables.sql
├── 014_add_favorites_to_study_items.sql
├── 015_add_first_last_name_to_users.sql
├── 016_enhance_quiz_results_table.sql
├── 017_make_quiz_item_id_nullable.sql
├── 018_fix_quiz_results_table.sql
├── 019_recreate_results_tables.sql
├── 025_create_shared_materials_table.sql
├── 026_create_notifications_table.sql
└── [additional migrations]

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

psql -d your_database -f scripts/001_initialize_database.sql

# Using psql
for file in scripts/*.sql; do
  echo "Running $file..."
  psql -d your_database -f "$file"
done

# Login to Supabase
supabase login

# Link your project
supabase link --project-ref your-project-ref

# Create a new migration
supabase db new migration_name

# Apply migrations
supabase db push

CREATE TABLE IF NOT EXISTS schema_migrations (
  id SERIAL PRIMARY KEY,
  migration_name TEXT NOT NULL UNIQUE,
  executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Record each migration after running
INSERT INTO schema_migrations (migration_name) 
VALUES ('001_initialize_database');

DROP TABLE IF EXISTS quiz_results CASCADE;
DROP TABLE IF EXISTS flashcard_results CASCADE;
DROP TABLE IF EXISTS writing_results CASCADE;

# Next available number
touch scripts/027_your_migration_name.sql

-- Description: Brief description of what this migration does
-- Author: Your name
-- Date: 2026-03-07

-- Create new table
CREATE TABLE IF NOT EXISTS new_feature (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add indexes
CREATE INDEX idx_new_feature_user_id ON new_feature(user_id);

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

-- Create policies
CREATE POLICY "Users can view their own records" 
ON new_feature 
FOR SELECT 
USING (auth.uid() = user_id);

psql -d your_local_db -f scripts/027_your_migration_name.sql

-- 027_your_migration_name_rollback.sql
-- Rollback for migration 027

DROP TABLE IF EXISTS new_feature;

-- Add column if it doesn't exist
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'users' AND column_name = 'new_column'
  ) THEN
    ALTER TABLE users ADD COLUMN new_column TEXT;
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_table_column 
ON table_name(column_name);

-- Concurrent index (doesn't lock table)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_table_column 
ON table_name(column_name);

-- Change column type
ALTER TABLE users 
ALTER COLUMN credits TYPE BIGINT;

-- Add NOT NULL constraint
ALTER TABLE users 
ALTER COLUMN email SET NOT NULL;

-- Add default value
ALTER TABLE users 
ALTER COLUMN credits SET DEFAULT 5000;

ALTER TABLE child_table
ADD CONSTRAINT fk_parent
FORGN KEY (parent_id) 
REFERENCES parent_table(id) 
ON DELETE CASCADE;

SELECT * FROM schema_migrations 
WHERE migration_name = '027_your_migration';

-- Check current role
SELECT current_user;

-- Switch to postgres or service_role
SET ROLE postgres;

DROP POLICY IF EXISTS "policy_name" ON table_name;

CREATE POLICY "policy_name" ON table_name
  FOR SELECT USING (true);

-- Check if parent table exists
SELECT * FROM information_schema.tables 
WHERE table_name = 'parent_table';

-- Check column types match
SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name IN ('parent_table', 'child_table');

# Supabase backup
supabase db dump -f backup_$(date +%Y%m%d_%H%M%S).sql

supabase db push

-- Check tables were created
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public';

-- Verify data integrity
SELECT COUNT(*) FROM your_new_table;

# Restore from backup
psql -d your_database -f backup_20260307_120000.sql

# Or run rollback script
psql -d your_database -f scripts/027_your_migration_rollback.sql