How We Made Our Supabase RLS Migrations Idempotent (And Why You Should Too)

The Problem

You write a Supabase migration, it runs fine locally. You push to staging — fine. Then you need to re-apply it (rollback test, fresh CI environment, or a teammate cloning the repo) and suddenly:

ERROR: policy "Users can view own stitched exports" for table "stitched_exports" already exists

PostgreSQL's CREATE POLICY (and CREATE INDEX) doesn't have an IF NOT EXISTS guard. So if your migration runs twice, it blows up.

This hit us in ClipCrafter when working on a new stitched_exports table. Here's the before:

CREATE INDEX stitched_exports_project_id_idx ON stitched_exports(project_id);

ALTER TABLE stitched_exports ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own stitched exports"
  ON stitched_exports FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM projects
      WHERE projects.id = stitched_exports.project_id
        AND projects.user_id = auth.uid()
    )
  );

CREATE POLICY "Service role full access to stitched_exports"
  ON stitched_exports FOR ALL
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');

Running this twice errors out on both the index and both policies.

The Fix: IF NOT EXISTS and DO $$ BEGIN Blocks

For the index, PostgreSQL 9.5+ supports IF NOT EXISTS natively:

CREATE INDEX IF NOT EXISTS stitched_exports_project_id_idx ON stitched_exports(project_id);

For RLS policies, there's no IF NOT EXISTS syntax — but you can use a DO block to query pg_policies first:

DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies
    WHERE tablename = 'stitched_exports'
      AND policyname = 'Users can view own stitched exports'
  ) THEN
    CREATE POLICY "Users can view own stitched exports"
      ON stitched_exports FOR SELECT
      USING (
        EXISTS (
          SELECT 1 FROM projects
          WHERE projects.id = stitched_exports.project_id
            AND projects.user_id = auth.uid()
        )
      );
  END IF;
END $$;

DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies
    WHERE tablename = 'stitched_exports'
      AND policyname = 'Service role full access to stitched_exports'
  ) THEN
    CREATE POLICY "Service role full access to stitched_exports"
      ON stitched_exports FOR ALL
      USING (auth.role() = 'service_role')
      WITH CHECK (auth.role() = 'service_role');
  END IF;
END $$;

Now the migration is safe to run multiple times — idempotent.

Why This Matters More Than You Think

Supabase migrations are applied sequentially and each migration file is tracked in supabase_migrations.schema_migrations. If a migration was never tracked (maybe it was applied manually during a late-night debug session), running supabase db push will try to apply it again and fail mid-way.

Idempotency makes your migration resilient to:

• CI from a clean state — fresh Supabase project for every PR preview
• Manual hotfixes — you applied a fix directly in prod SQL editor, then later git pull tries to run it again
• Team member onboarding — new dev clones the repo, runs supabase db reset, it should just work
• Rollback + re-apply — especially common in pre-launch projects that are still iterating on schema

The General Pattern

Here's a reusable template for idempotent RLS policy creation:

-- Tables
CREATE TABLE IF NOT EXISTS your_table (...);

-- Indexes
CREATE INDEX IF NOT EXISTS your_index_name ON your_table(column);

-- RLS
ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;

-- Policies (wrap each in a DO block)
DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies
    WHERE tablename = 'your_table' AND policyname = 'Your Policy Name'
  ) THEN
    CREATE POLICY "Your Policy Name"
      ON your_table FOR SELECT
      USING (user_id = auth.uid());
  END IF;
END $$;

One More Tip: CREATE TABLE IF NOT EXISTS + ALTER TABLE

If your migration also runs ALTER TABLE to add columns, use IF NOT EXISTS there too (PostgreSQL 9.6+):

ALTER TABLE your_table ADD COLUMN IF NOT EXISTS new_column TEXT;

This covers the case where someone manually ALTER-ed the table in production before the migration was tracked.

Takeaway

Make every migration idempotent from day one. The cost is a few extra lines per policy; the benefit is never having a broken CI or a tense production deploy because a migration ran twice. In Supabase specifically, wrapping CREATE POLICY in DO $$ BEGIN ... IF NOT EXISTS ... END $$; blocks is the only way to get this guarantee.

It's a small discipline that saves a lot of pain.