Guide: Creating Supabase Database Migrations¶

This document outlines the process for creating and applying new database schema migrations using the Supabase CLI for this project.

Prerequisites¶

Supabase CLI Installed: Ensure you have the Supabase CLI installed and configured on your local machine.
Project Cloned: You have the project repository cloned locally.
Local Supabase Instance: You should be able to start your local Supabase development environment.

Steps to Create and Apply a New Migration¶

1. Plan and Design Your Schema Change¶

Before creating a migration file, clearly define the database changes you want to make: _ New Tables: Define table names, columns, data types, primary keys, foreign keys, constraints (NOT NULL, UNIQUE), default values, and any indexes. _ Table Modifications: Specify alterations to existing tables (adding/dropping columns, changing data types, etc.). * Triggers & Functions: Define any necessary database functions or triggers.

**Using GUI Tools for Design and DDL Generation:**
You can use a database GUI tool like **DBeaver**, **pgAdmin**, or the **Supabase Studio** (accessible when your local Supabase instance is running, typically at `http://localhost:54323`) to help you:
    *   Visually design your new tables or modifications to existing ones.
    *   Once designed in the GUI tool (connected to your local Supabase PostgreSQL database), these tools can often generate the corresponding SQL Data Definition Language (DDL) for you. You can then copy this generated SQL to use in your migration file.
*This approach can be very helpful for complex schemas or if you prefer a visual interface for design. However, remember that the SQL migration files are the ultimate source of truth for your database schema in the project.*

2. Prepare the SQL for Your Migration¶

Based on your planning (and potentially DDL generated from a GUI tool), prepare the complete SQL statements that will implement your schema changes.

Example: Creating a new table my_new_table (This is a generic example; replace it with your actual SQL.)

-- Create the my_new_table table
CREATE TABLE public.my_new_table (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Optional: Add a comment to the table
COMMENT ON TABLE public.my_new_table IS 'Stores information about my new items.';

-- Optional: Function to update the updated_at timestamp
-- (Check if a similar function like 'moddatetime' already exists from initial migrations)
CREATE OR REPLACE FUNCTION public.handle_updated_at_generic()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = timezone('utc', now());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Optional: Trigger to automatically update updated_at on row modification
CREATE TRIGGER set_updated_at_my_new_table
BEFORE UPDATE ON public.my_new_table
FOR EACH ROW
EXECUTE PROCEDURE public.handle_updated_at_generic();

COMMENT ON TRIGGER set_updated_at_my_new_table ON public.my_new_table IS 'Automatically updates updated_at when a row is changed.';

-- Enable Row Level Security (RLS)
ALTER TABLE public.my_new_table ENABLE ROW LEVEL SECURITY;

-- Create RLS policies following best practices
-- IMPORTANT: Always use (SELECT auth.uid()) to prevent per-row re-evaluation
-- NEVER use FOR ALL - create separate policies for each operation

-- SELECT policy
CREATE POLICY "Users can view their own items"
    ON public.my_new_table
    AS PERMISSIVE
    FOR SELECT
    TO authenticated
    USING (
        created_by_user_id = (SELECT auth.uid())
    );

-- INSERT policy
CREATE POLICY "Users can insert their own items"
    ON public.my_new_table
    AS PERMISSIVE
    FOR INSERT
    TO authenticated
    WITH CHECK (
        created_by_user_id = (SELECT auth.uid())
    );

-- UPDATE policy
CREATE POLICY "Users can update their own items"
    ON public.my_new_table
    AS PERMISSIVE
    FOR UPDATE
    TO authenticated
    USING (
        created_by_user_id = (SELECT auth.uid())
    )
    WITH CHECK (
        created_by_user_id = (SELECT auth.uid())
    );

-- DELETE policy
CREATE POLICY "Users can delete their own items"
    ON public.my_new_table
    AS PERMISSIVE
    FOR DELETE
    TO authenticated
    USING (
        created_by_user_id = (SELECT auth.uid())
    );

-- Create indexes for foreign keys used in RLS policies
CREATE INDEX idx_my_new_table_created_by_user_id ON public.my_new_table(created_by_user_id);

Row Level Security (RLS) Best Practices¶

When creating RLS policies in migrations, follow these critical guidelines:

Always use (SELECT auth.uid()) and (SELECT auth.role()) - This prevents per-row re-evaluation for better performance
Never use FOR ALL - Always create separate policies for SELECT, INSERT, UPDATE, DELETE
Use EXISTS instead of IN for subqueries in RLS policies
Always create indexes for foreign key columns used in RLS policies
Service role bypasses RLS - No need to create explicit service_role policies
Use AS PERMISSIVE to make policies explicit
Specify TO authenticated or TO anon to be clear about who the policy applies to
For anonymous access, use TO anon (not TO public)

Example of checking team membership with proper indexing:

-- Create index for the foreign key used in RLS
CREATE INDEX idx_items_project_id ON public.items(project_id);
CREATE INDEX idx_user_access_scope ON public.user_access(scope_type, scope_id, user_id);

-- RLS policy using EXISTS for better performance
CREATE POLICY "Team members can view project items"
    ON public.items
    AS PERMISSIVE
    FOR SELECT
    TO authenticated
    USING (
        EXISTS (
            SELECT 1 FROM public.user_access ua
            WHERE ua.scope_type = 'project'
            AND ua.scope_id = items.project_id
            AND ua.user_id = (SELECT auth.uid())
            AND ua.status = 'Active'
        )
    );

3. Navigate to the Supabase Project Directory¶

Open your terminal and navigate to the directory containing your Supabase project configuration:

cd path/to/your/project/packages/database/supabase

4. Create a New Migration File¶

Use the Supabase CLI to generate a new, empty migration file. Choose a descriptive name for your migration.

supabase migration new <your_descriptive_migration_name>

For example:

supabase migration new create_my_new_table
# or
supabase migration new add_description_to_items_table

This command will create a new SQL file in the packages/database/supabase/migrations/ directory. The filename will be prefixed with a timestamp (e.g., YYYYMMDDHHMMSS_your_descriptive_migration_name.sql).

5. Populate the Migration File¶

Open the newly created .sql file (e.g., migrations/YYYYMMDDHHMMSS_your_descriptive_migration_name.sql) in your code editor.
Delete any placeholder content if present.
Paste the SQL DDL statements you prepared in Step 2 into this file.
Save the file.

6. Apply the Migration to Your Local Supabase Instance¶

To apply the migration and update your local development database schema:

Ensure your local Supabase instance is running. If not, start it from the packages/database/supabase/ directory:

supabase start

(Wait for it to fully initialize.)

Apply the migrations. You have two main options:
Option A: Reset and apply all migrations (Recommended for simplicity during development): This drops your local database, recreates it, and applies all migrations from the beginning, including your new one.
```
supabase db reset
```
Option B: Apply only new, unapplied migrations: This applies only the migrations that haven't been run yet on your local database.
```
supabase migration up
```

7. Verify the Migration¶

After the migration is applied, verify that your schema changes were successful:

Supabase Studio: Open Supabase Studio for your local instance (usually at http://localhost:54323 when supabase start is running). Navigate to the "Table Editor" or "SQL Editor" to inspect your tables and schema.
SQL Client: Connect to your local PostgreSQL database (connection details are usually provided when you run supabase start) using a tool like DBeaver or pgAdmin and inspect the schema.

This process ensures that all schema changes are version-controlled and can be consistently applied across different environments.