Supabase Policy Guardrails

-- supabase/migrations/00000000000000_rls_policy_library.sql
-- Shared RLS policy library — apply these templates to new tables

-- ============================================================
-- Template 1: Owner-only access (user owns the row)
-- Usage: tables with a user_id column (todos, profiles, settings)
-- ============================================================
CREATE OR REPLACE FUNCTION public.rls_owner_only(table_name text, user_column text DEFAULT 'user_id')
RETURNS void AS $$
BEGIN
  EXECUTE format('ALTER TABLE public.%I ENABLE ROW LEVEL SECURITY', table_name);

  EXECUTE format(
    'CREATE POLICY "owner_select" ON public.%I FOR SELECT USING (%I = auth.uid())',
    table_name, user_column
  );
  EXECUTE format(
    'CREATE POLICY "owner_insert" ON public.%I FOR INSERT WITH CHECK (%I = auth.uid())',
    table_name, user_column
  );
  EXECUTE format(
    'CREATE POLICY "owner_update" ON public.%I FOR UPDATE USING (%I = auth.uid())',
    table_name, user_column
  );
  EXECUTE format(
    'CREATE POLICY "owner_delete" ON public.%I FOR DELETE USING (%I = auth.uid())',
    table_name, user_column
  );
END;
$$ LANGUAGE plpgsql;

-- ============================================================
-- Template 2: Organization-scoped access (user is member of org)
-- Usage: tables with org_id referencing org_members
-- ============================================================
CREATE OR REPLACE FUNCTION public.rls_org_scoped(
  table_name text,
  org_column text DEFAULT 'org_id',
  allow_delete boolean DEFAULT false
)
RETURNS void AS $$
BEGIN
  EXECUTE format('ALTER TABLE public.%I ENABLE ROW LEVEL SECURITY', table_name);

  EXECUTE format(
    'CREATE POLICY "org_select" ON public.%I FOR SELECT USING (
      %I IN (SELECT org_id FROM public.org_members WHERE user_id = auth.uid())
    )', table_name, org_column
  );
  EXECUTE format(
    'CREATE POLICY "org_insert" ON public.%I FOR INSERT WITH CHECK (
      %I IN (SELECT org_id FROM public.org_members WHERE user_id = auth.uid())
    )', table_name, org_column
  );
  EXECUTE format(
    'CREATE POLICY "org_update" ON public.%I FOR UPDATE USING (
      %I IN (SELECT org_id FROM public.org_members WHERE user_id = auth.uid() AND role IN (''admin'', ''editor''))
    )', table_name, org_column
  );

  IF allow_delete THEN
    EXECUTE format(
      'CREATE POLICY "org_delete" ON public.%I FOR DELETE USING (
        %I IN (SELECT org_id FROM public.org_members WHERE user_id = auth.uid() AND role = ''admin'')
      )', table_name, org_column
    );
  END IF;
END;
$$ LANGUAGE plpgsql;

-- ============================================================
-- Template 3: Public read, authenticated write
-- Usage: blog posts, product listings, public content
-- ============================================================
CREATE OR REPLACE FUNCTION public.rls_public_read_auth_write(
  table_name text,
  owner_column text DEFAULT 'created_by'
)
RETURNS void AS $$
BEGIN
  EXECUTE format('ALTER TABLE public.%I ENABLE ROW LEVEL SECURITY', table_name);

  EXECUTE format(
    'CREATE POLICY "public_select" ON public.%I FOR SELECT USING (true)',
    table_name
  );
  EXECUTE format(
    'CREATE POLICY "auth_insert" ON public.%I FOR INSERT WITH CHECK (auth.uid() IS NOT NULL)',
    table_name
  );
  EXECUTE format(
    'CREATE POLICY "owner_update" ON public.%I FOR UPDATE USING (%I = auth.uid())',
    table_name, owner_column
  );
  EXECUTE format(
    'CREATE POLICY "owner_delete" ON public.%I FOR DELETE USING (%I = auth.uid())',
    table_name, owner_column
  );
END;
$$ LANGUAGE plpgsql;

-- Apply templates to tables:
-- SELECT public.rls_owner_only('todos');
-- SELECT public.rls_org_scoped('projects', 'org_id', true);
-- SELECT public.rls_public_read_auth_write('blog_posts', 'author_id');

-- supabase/migrations/00000000000001_naming_convention_check.sql
-- Validation function that checks naming conventions at migration time

CREATE OR REPLACE FUNCTION public.validate_naming_conventions()
RETURNS TABLE(issue text, object_name text, suggestion text) AS $$
BEGIN
  -- Tables must be snake_case, plural
  RETURN QUERY
  SELECT
    'Table name should be plural snake_case'::text,
    t.tablename::text,
    regexp_replace(t.tablename, '([A-Z])', '_\1', 'g')::text
  FROM pg_tables t
  WHERE t.schemaname = 'public'
  AND (
    t.tablename ~ '[A-Z]'           -- contains uppercase
    OR t.tablename ~ '-'             -- contains hyphens
    OR t.tablename !~ 's$'           -- not plural (heuristic)
  )
  AND t.tablename NOT LIKE '\_%';   -- skip internal tables

  -- Columns must be snake_case
  RETURN QUERY
  SELECT
    'Column name should be snake_case'::text,
    (c.table_name || '.' || c.column_name)::text,
    regexp_replace(c.column_name, '([A-Z])', '_\1', 'g')::text
  FROM information_schema.columns c
  WHERE c.table_schema = 'public'
  AND (c.column_name ~ '[A-Z]' OR c.column_name ~ '-');

  -- Foreign key columns should end with _id
  RETURN QUERY
  SELECT
    'Foreign key column should end with _id'::text,
    (tc.table_name || '.' || kcu.column_name)::text,
    (kcu.column_name || '_id')::text
  FROM information_schema.table_constraints tc
  JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
  WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'public'
  AND kcu.column_name NOT LIKE '%_id';

  -- Boolean columns should start with is_ or has_
  RETURN QUERY
  SELECT
    'Boolean column should start with is_ or has_'::text,
    (c.table_name || '.' || c.column_name)::text,
    ('is_' || c.column_name)::text
  FROM information_schema.columns c
  WHERE c.table_schema = 'public'
  AND c.data_type = 'boolean'
  AND c.column_name NOT LIKE 'is_%'
  AND c.column_name NOT LIKE 'has_%';
END;
$$ LANGUAGE plpgsql;

-- Run: SELECT * FROM public.validate_naming_conventions();