Database Migrations

Complete guide to managing database migrations in Ryva

Ryva uses SQL migrations to manage database schema changes. Migrations run automatically when the API starts and can also be managed manually.

Overview

Migrations in Ryva:

• Written in pure SQL (no ORM abstractions)
• Versioned with timestamps
• Support both up (apply) and down (rollback) operations
• Run automatically on API startup
• Located in apps/api/db/migrations/

Migration Workflow

Automatic Migrations

By default, migrations run automatically when the API server starts. This ensures your database schema is always up-to-date.

When you start the API:

# Migrations run automatically
make dev-api

# Or in production
docker compose up -d

The API checks for pending migrations and applies them before accepting requests.

Creating Migrations

1. Generate migration files

   From Root

   # Create migration from project root
   make db-create-migration name=add_users_table
   

   This creates two files:

   apps/api/db/migrations/
   ├── 20260103120000_add_users_table.up.sql
   └── 20260103120000_add_users_table.down.sql
   
2. Write the up migration

   Edit the .up.sql file with your schema changes:

   apps/api/db/migrations/20260103120000_add_users_table.up.sql

   -- Create users table
   CREATE TABLE IF NOT EXISTS public.users (
       id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
       email TEXT NOT NULL UNIQUE,
       full_name TEXT,
       avatar_url TEXT,
       created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
       updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
   );
   
   -- Create index for faster lookups
   CREATE INDEX idx_users_email ON public.users(email);
   
   -- Create trigger for updated_at
   CREATE TRIGGER update_users_updated_at
       BEFORE UPDATE ON public.users
       FOR EACH ROW
       EXECUTE FUNCTION public.update_updated_at_column();
   
3. Write the down migration

   Edit the .down.sql file to reverse the changes:

   apps/api/db/migrations/20260103120000_add_users_table.down.sql

   -- Drop trigger
   DROP TRIGGER IF EXISTS update_users_updated_at ON public.users;
   
   -- Drop index
   DROP INDEX IF EXISTS idx_users_email;
   
   -- Drop table
   DROP TABLE IF EXISTS public.users;
   
4. Test the migration

   # Apply migration
   make db-migrate-up
   
   # Verify it worked
   make db-migrate-status
   
   # Test rollback
   make db-migrate-down
   
   # Re-apply
   make db-migrate-up
   

Migration Commands

Root Makefile Commands

From the project root:

# Create new migration
make db-create-migration name=migration_name

# Apply all pending migrations
make db-migrate-up

# Rollback last migration
make db-migrate-down

# Show migration status
make db-migrate-status

# Force specific version (use with caution)
make db-migrate-force version=20260103120000

# Seed database with initial data
make db-seed

API Makefile Commands

From apps/api:

# Create new migration
make migrate-create name=migration_name

# Apply all pending migrations
make migrate-up

# Rollback last migration
make migrate-down

# Rollback ALL migrations (destructive!)
make migrate-down-all

# Show current migration version
make migrate-version

# Show migration status
make migrate-status

# Force migration to specific version
make migrate-force version=20260103120000

Migration Examples

Creating a Table

up.sql

-- Create organizations table
CREATE TABLE IF NOT EXISTS public.organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    avatar_url TEXT,
    plan_type TEXT NOT NULL DEFAULT 'free',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create indexes
CREATE INDEX idx_organizations_slug ON public.organizations(slug);
CREATE INDEX idx_organizations_plan_type ON public.organizations(plan_type);

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

-- Add trigger
CREATE TRIGGER update_organizations_updated_at
    BEFORE UPDATE ON public.organizations
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

Adding a Column

up.sql

-- Add is_verified column to users
ALTER TABLE public.users
ADD COLUMN IF NOT EXISTS is_verified BOOLEAN NOT NULL DEFAULT false;

-- Create index for common queries
CREATE INDEX IF NOT EXISTS idx_users_is_verified 
ON public.users(is_verified);

Creating RLS Policies

up.sql

-- Policy: Users can view their own data
CREATE POLICY "Users can view own data"
    ON public.users
    FOR SELECT
    USING (auth.uid() = id);

-- Policy: Users can update their own data
CREATE POLICY "Users can update own data"
    ON public.users
    FOR UPDATE
    USING (auth.uid() = id)
    WITH CHECK (auth.uid() = id);

-- Policy: Service role can do anything
CREATE POLICY "Service role has full access"
    ON public.users
    FOR ALL
    USING (auth.role() = 'service_role');

Creating Functions and Triggers

up.sql

-- Create reusable function for updated_at
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = ''
AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$;

-- Create function to handle new user creation
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = ''
AS $$
BEGIN
    INSERT INTO public.users (id, email, full_name, avatar_url)
    VALUES (
        NEW.id,
        NEW.email,
        NEW.raw_user_meta_data->>'full_name',
        NEW.raw_user_meta_data->>'avatar_url'
    );
    RETURN NEW;
END;
$$;

-- Create trigger on auth.users
CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW
    EXECUTE FUNCTION public.handle_new_user();

Adding Enum Types

up.sql

-- Create enum type for organization plans
DO $$ BEGIN
    CREATE TYPE public.plan_type AS ENUM ('free', 'personal', 'team', 'enterprise');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- Add column using the enum
ALTER TABLE public.organizations
ADD COLUMN IF NOT EXISTS plan public.plan_type NOT NULL DEFAULT 'free';

Migration Best Practices

Naming Conventions

Use descriptive names that clearly indicate what the migration does:

Good Names

create_users_table
add_email_verification_to_users
create_organizations_rls_policies
add_billing_fields_to_organizations
remove_deprecated_status_column

Writing Safe Migrations

1. Use IF EXISTS / IF NOT EXISTS

   Always use conditional statements to make migrations idempotent:

   CREATE TABLE IF NOT EXISTS ...
   ALTER TABLE ... ADD COLUMN IF NOT EXISTS ...
   DROP TABLE IF EXISTS ...
   CREATE INDEX IF NOT EXISTS ...
   
2. Add indexes for foreign keys

   Always index columns used in JOINs and WHERE clauses:

   CREATE INDEX idx_organization_members_user_id 
   ON organization_members(user_id);
   
   CREATE INDEX idx_organization_members_org_id 
   ON organization_members(organization_id);
   
3. Use transactions when needed

   For complex migrations, wrap in a transaction:

   BEGIN;
   
   -- Multiple related changes
   ALTER TABLE users ADD COLUMN ...;
   CREATE INDEX ...;
   UPDATE users SET ...;
   
   COMMIT;
   
4. Set appropriate defaults

   When adding columns, provide defaults for existing rows:

   ALTER TABLE users 
   ADD COLUMN is_verified BOOLEAN NOT NULL DEFAULT false;
   
5. Test rollbacks

   Always test your down migration:

   make db-migrate-up      # Apply
   make db-migrate-down    # Rollback
   make db-migrate-up      # Re-apply
   

Production Migration Checklist

Before deploying migrations to production:

• Test migration on local database
• Test rollback (down migration)
• Re-test applying after rollback
• Check for blocking operations (locks, full table scans)
• Verify indexes are created
• Test with production-sized data (if possible)
• Plan for downtime (if required)
• Have rollback plan ready
• Monitor database performance after deployment

Migration Versioning

Migrations use timestamp-based versioning:

20260103120000_migration_name.up.sql
│         │
│         └─ Migration name
└─ Timestamp: YYYYMMDDHHMMSS

Benefits:

• Chronological ordering
• Prevents version conflicts in team environments
• Easy to identify when migration was created

Database Schema Tracking

The migration tool uses a schema_migrations table to track applied migrations:

CREATE TABLE schema_migrations (
    version bigint PRIMARY KEY,
    dirty boolean NOT NULL
);

• version: Timestamp of the migration
• dirty: Indicates if migration failed mid-execution

Checking Migration Status

# Show current version
make db-migrate-status

# Output:
# Current migration version:
# 20260103120000

Recovering from Failed Migrations

If a migration fails and marks the database as “dirty”:

1. Identify the issue

   # Check migration status
   make db-migrate-status
   
   # Review migration logs
   docker compose logs backend | grep migration
   
2. Fix the database manually

   If the migration partially applied, manually fix the database state:

   # Connect to database
   psql $DATABASE_URL
   
   # Inspect what was applied
   \dt  # List tables
   \d table_name  # Describe table
   
   # Manually fix or rollback changes
   
3. Force version

   Only use after manually fixing the database state!

   # Force migration to specific version
   make db-migrate-force version=20260103120000
   
   # Or mark as clean at current version
   make db-migrate-force version=<current_version>
   

Supabase-Specific Considerations

Row Level Security (RLS)

Always enable RLS on tables and create appropriate policies:

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

-- Create policies
CREATE POLICY "Users can view own data"
    ON public.users
    FOR SELECT
    USING (auth.uid() = id);

Auth Schema

Reference Supabase’s auth.users table:

CREATE TABLE public.users (
    id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    -- other columns
);

Storage Buckets

Create storage buckets in migrations:

-- Create avatars storage bucket
INSERT INTO storage.buckets (id, name, public)
VALUES ('avatars', 'avatars', true)
ON CONFLICT (id) DO NOTHING;

-- Set up RLS for bucket
CREATE POLICY "Avatar images are publicly accessible"
    ON storage.objects
    FOR SELECT
    USING (bucket_id = 'avatars');

Troubleshooting

Migration fails with 'relation already exists'

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

-- Bad
CREATE TABLE users (...);

-- Good
CREATE TABLE IF NOT EXISTS users (...);

Database marked as dirty

A migration failed mid-execution:

1. Check logs: docker compose logs backend | grep migration
2. Manually fix database state
3. Force version: make db-migrate-force version=<current>
4. Try migration again

Migrations not running in Docker

Check environment variables:

# Verify DATABASE_URL is set
docker compose exec backend env | grep DATABASE_URL

# Check backend logs
docker compose logs backend

Permission denied errors

Ensure your database user has sufficient permissions:

• Use service_role key for Supabase
• Grant necessary permissions on self-hosted PostgreSQL
• Check RLS policies aren’t blocking service role

Migration Tools

Ryva uses golang-migrate for database migrations.

Manual Installation

If you need to run migrations manually:

# Install golang-migrate
go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

# Run migrations
cd apps/api
migrate -path db/migrations -database "$DATABASE_URL" up

Next Steps

Environment Variables

Configure DATABASE_URL and other settings

Deployment Guide

Deploy migrations to production