Create Migration

Create Database Migration

Generate a Supabase PostgreSQL migration with proper schema, RLS policies, and indexes.

Migration File Location

supabase/migrations/[YYYYMMDDHHMMSS]_[description].sql

Naming Conventions

• Tables: snake_case, plural (user_profiles, blog_posts)
• Columns: snake_case (date_created, user_id)
• Primary keys: id (UUID)
• Foreign keys: [referenced_table]_id (category_id)
• Junction tables: [table1]_[table2] (products_tags)

Standard Columns

id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
date_created timestamptz DEFAULT now(),
date_updated timestamptz,
user_created uuid REFERENCES auth.users(id),
user_updated uuid REFERENCES auth.users(id)

Migration Sections (Required)

1. Header Comment — description, author, date
2. CREATE TABLE — with data types and constraints
3. Enable RLS — ALTER TABLE ... ENABLE ROW LEVEL SECURITY
4. RLS Policies — SELECT, INSERT, UPDATE, DELETE
5. Indexes — FK columns, filtered columns, sorted columns
6. Triggers — date_updated auto-update, audit logging
7. GRANT — permissions for authenticated and anon roles

Data Types

Relationships

One-to-Many

category_id uuid REFERENCES public.categories(id) ON DELETE SET NULL

Many-to-Many (junction table)

CREATE TABLE public.products_tags (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  products_id uuid NOT NULL REFERENCES public.products(id) ON DELETE CASCADE,
  tags_id uuid NOT NULL REFERENCES public.tags(id) ON DELETE CASCADE,
  UNIQUE(products_id, tags_id)
);

RLS Policy Patterns

-- Owner can CRUD their own
CREATE POLICY "users_own_data" ON public.[table]
  FOR ALL USING (auth.uid() = user_created);

-- Published items are public
CREATE POLICY "published_public" ON public.[table]
  FOR SELECT USING (status = 'published' OR auth.uid() = user_created);

Commands

supabase migration new [description]
supabase db push
supabase db reset

References

• Special fields