Implement Supabase lint rules, policy enforcement, and automated guardrails.
Use when setting up code quality rules for Supabase integrations, implementing
pre-commit hooks, or configuring CI policy checks for Supabase best practices.
Trigger with phrases like "supabase policy", "supabase lint",
"supabase guardrails", "supabase best practices check", "supabase eslint".
Organizational governance for Supabase at scale: a shared RLS policy library (reusable templates for common access patterns), naming conventions (tables, columns, functions, policies), migration review process (CI checks ensuring RLS, preventing destructive operations, enforcing naming), cost alert configuration (billing thresholds and usage monitoring), and security audit scripts (scanning for exposed keys, missing RLS, overly permissive policies). All patterns use real createClient from @supabase/supabase-js and Supabase CLI commands.
Prerequisites
Supabase project with supabase CLI installed and linked
@supabase/supabase-js v2+ installed
CI/CD pipeline (GitHub Actions recommended)
Database access via psql or Supabase SQL Editor
Pro plan recommended for cost alerts and usage API
Instructions
Step 1 — Shared RLS Policy Library and Naming Conventions
RLS Policy Templates
Create reusable RLS policy templates that teams apply to new tables. This prevents each developer from writing ad-hoc policies and ensures consistent access control.
-- 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');
Naming Conventions
-- 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();
Naming Convention Reference
Object
Convention
Example
Tables
Plural snake_case
user_profiles, order_items
Columns
snake_case
created_at, full_name
Foreign keys
{referenced_table_singular}_id
user_id, order_id
Booleans
is_ or has_ prefix
is_active, has_verified_email
Timestamps
_at suffix
created_at, updated_at, deleted_at
RLS policies
{scope}_{operation}
owner_select, org_insert
Functions
verb_noun
create_user, get_dashboard_metrics
Indexes
idx_{table}_{columns}
idx_orders_user_id_created_at
Migrations
{timestamp}_{verb}_{description}
20250322000000_create_orders_table.sql
Step 2 — Migration Review Process with CI Checks
See CI checks, cost alerts, and security audits for GitHub Actions migration guardrails (RLS enforcement, naming checks, destructive operation blocks), pre-commit hooks, cost monitoring with Slack alerts, security audit scripts, and scheduled Edge Function audits.
Output
Shared RLS policy library with owner-only, org-scoped, and public-read templates
Naming convention validation function checking tables, columns, FKs, and booleans
CI pipeline enforcing RLS, naming, and destructive operation controls
Pre-commit hook blocking hardcoded secrets and tables without RLS
Cost monitoring script with configurable thresholds and Slack alerting
