Archivo del skill

Migration Guide

Name: Migration Guide
Author: tyjpark0317

Database migration, RLS, schema guide — performance patterns, anti-patterns, SECURITY DEFINER, safety checklist. Use when creating/modifying migration files, RLS policies, or DB schema.

tyjpark03170 estrellas29 mar 2026

Ocupación
Categorías: Herramientas de Bases de Datos

Contenido de la habilidad

Database Migration & RLS Guide

Reference skill for writing migration files, designing RLS policies, and schema changes. Based on official documentation, performance benchmarks, and production incident learnings.

1. Migration File Rules

Timestamps — Never Duplicate

Always verify the next available timestamp before creating a new migration.

File Naming

GOOD: 20260315000001_add-user-alerts.sql
BAD:  20260315000001_update.sql

File Structure

-- ============================================================
-- Purpose: [what is being changed]
-- Reason: [why it is needed]
-- Impact: [which tables/policies are affected]
-- ============================================================

Skills relacionados

Migration Guide | Skills Pool

-- BAD: auth.uid() called per-row (179ms)
CREATE POLICY "..." ON my_table
  USING (auth.uid() = user_id);

-- GOOD: initPlan executes once (9ms, 94.97% improvement)
CREATE POLICY "..." ON my_table
  TO authenticated
  USING ((select auth.uid()) = user_id);

CREATE INDEX IF NOT EXISTS idx_{table}_{column}
  ON public.{table} USING btree ({column});

-- GOOD: anon skips instantly
CREATE POLICY "..." ON my_table
  TO authenticated
  USING ((select auth.uid()) = user_id);

-- GOOD: fixed-value join
CREATE POLICY "..." ON test_table TO authenticated
  USING (
    team_id IN (
      SELECT team_id FROM team_user
      WHERE user_id = (select auth.uid())
    )
  );

CREATE POLICY "{table}_select_own" ON public.{table}
  FOR SELECT TO authenticated
  USING ((select auth.uid()) = user_id);

CREATE POLICY "{table}_insert_own" ON public.{table}
  FOR INSERT TO authenticated
  WITH CHECK ((select auth.uid()) = user_id);

CREATE POLICY "{table}_update_own" ON public.{table}
  FOR UPDATE TO authenticated
  USING ((select auth.uid()) = user_id)
  WITH CHECK ((select auth.uid()) = user_id);

CREATE FUNCTION private.has_role(role_name TEXT)
RETURNS BOOLEAN LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''  -- Required: prevents search_path injection
STABLE AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM public.roles_table
    WHERE user_id = auth.uid() AND role = role_name
  );
END; $$;

-- Migration 1: instant (validates new rows only)
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Migration 2: validates existing rows with lightweight lock
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

CREATE INDEX CONCURRENTLY idx_col ON public.{table}(col);

Operation	Lock	Blocks Reads?	Blocks Writes?
ADD COLUMN (nullable)	ACCESS EXCLUSIVE	Momentary	Momentary
ALTER COLUMN TYPE	ACCESS EXCLUSIVE	Full	Full
CREATE INDEX	SHARE	No	Yes
CREATE INDEX CONCURRENTLY	None	No	No
VALIDATE CONSTRAINT	SHARE UPDATE EXCLUSIVE	No	No

Migration Guide

Database Migration & RLS Guide

1. Migration File Rules

Timestamps — Never Duplicate

File Naming

File Structure

Migration Guide

Database Migration & RLS Guide

1. Migration File Rules

Timestamps — Never Duplicate

File Naming

File Structure

2. RLS Performance Patterns (Benchmarks on 100K-row tables)

Pattern 1: (select auth.uid()) Wrapping — 94-99.99% Improvement

Pattern 2: Add Indexes — 99.94% Improvement

Pattern 3: Specify TO Role — 99.78% Improvement

Pattern 4: JOIN Direction — 99.78% Improvement

Pattern 5: Client Filter Duplication — 94.74% Improvement

3. RLS Policy Design

Core Principles

RLS Policy Template

FOR ALL Prohibited

Permissive vs Restrictive

4. RLS Anti-Patterns

Anti-Pattern 1: Self-Referencing Recursion (CRITICAL)

Anti-Pattern 2: Under-Permissive RLS (HIGH)

Anti-Pattern 3: Missing DELETE Policy (MEDIUM)

Anti-Pattern 4: auth.uid() NULL Handling (LOW)

5. SECURITY DEFINER Pattern

Required Security Rules

6. Zero-Downtime Migration Patterns

NOT VALID + VALIDATE

CONCURRENTLY Index

DDL Lock Reference

7. Migration Safety Checklist

Structure

RLS

SECURITY DEFINER

Compatibility

Database Migrations

Database Migrations

Postgres Patterns

Frontend Query & Mutation

Db Migrations

Drizzle

Pattern 1: `(select auth.uid())` Wrapping — 94-99.99% Improvement