Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt

Use this file to discover all available pages before exploring further.

Version: 2.0.0
Last Updated: 2026-01-15
Purpose: Run audit SQL scripts against staging database to identify and fix issues.

​
Overview

This guide explains how to run SQL audit scripts using both MCP (preferred for simple queries) and Dashboard/CLI (for complex scripts). Audits to Run:
  1. RLS Policy Audit - Find UPDATE policies missing WITH CHECK (P0)
  2. Column Compliance Audit - Find missing audit columns (P1)
  3. Storage Bucket Audit - Find non-compliant bucket names (P1)

​
Prerequisites

  • Staging Supabase branch/project created
  • All migrations applied (supabase db push)
  • MCP connection verified or Dashboard access
Verify Setup (MCP): 
mcp_supabase_get_project_url()
mcp_supabase_list_tables({ schemas: ["public"] })

​
Audit 1: RLS Policy Audit

​
Quick Check via MCP

Check for Missing WITH CHECK: 
mcp_supabase_execute_sql({
  query: `
    SELECT COUNT(*) as missing_with_check_count
    FROM pg_policies
    WHERE schemaname = 'public' 
      AND cmd = 'UPDATE' 
      AND with_check IS NULL;
  `
})
// Expected: 0 (after fixes applied)
// Before fix: ~118
List Affected Policies: 
mcp_supabase_execute_sql({
  query: `
    SELECT tablename, policyname, cmd
    FROM pg_policies
    WHERE schemaname = 'public' 
      AND cmd = 'UPDATE' 
      AND with_check IS NULL
    ORDER BY tablename
    LIMIT 20;
  `
})

​
Full Audit via Dashboard

For generating fix statements, use the full audit script:
  1. Go to Supabase Dashboard → SQL Editor
  2. Copy contents of scripts/audit-rls-policies.sql
  3. Run the script
  4. Save Query 3 output - Contains ALTER POLICY fix statements

​
Expected Results

QueryPurposeExpected Before FixExpected After Fix
Query 1List all UPDATE policies~118 missing0 missing
Query 2Summary by command--
Query 3Generate fix statements118 statementsEmpty

​
Audit 2: Column Compliance Audit

​
Quick Check via MCP

Check for Missing updated_by: 
mcp_supabase_execute_sql({
  query: `
    SELECT COUNT(*) as missing_updated_by_count
    FROM (
      SELECT table_name FROM information_schema.columns
      WHERE table_schema = 'public' AND column_name = 'created_by'
      EXCEPT
      SELECT table_name FROM information_schema.columns
      WHERE table_schema = 'public' AND column_name = 'updated_by'
    ) t;
  `
})
// Expected: 0 (after fixes applied)
// Before fix: ~60
List Affected Tables: 
mcp_supabase_execute_sql({
  query: `
    SELECT table_name
    FROM information_schema.columns
    WHERE table_schema = 'public' AND column_name = 'created_by'
    AND table_name NOT IN (
      SELECT table_name FROM information_schema.columns
      WHERE table_schema = 'public' AND column_name = 'updated_by'
    )
    ORDER BY table_name
    LIMIT 20;
  `
})
Check for Missing organization_id: 
mcp_supabase_execute_sql({
  query: `
    SELECT table_name
    FROM information_schema.tables t
    WHERE table_schema = 'public'
      AND table_type = 'BASE TABLE'
      AND table_name LIKE '%_%'
      AND NOT EXISTS (
        SELECT 1 FROM information_schema.columns c
        WHERE c.table_schema = t.table_schema
          AND c.table_name = t.table_name
          AND c.column_name = 'organization_id'
      )
      AND table_name NOT IN ('pf_organizations', 'pf_platforms')
    ORDER BY table_name
    LIMIT 20;
  `
})

​
Full Audit via Dashboard

For generating fix statements:
  1. Go to Supabase Dashboard → SQL Editor
  2. Copy contents of scripts/audit-table-columns.sql
  3. Run the script
  4. Save Query 4 output - Contains ADD COLUMN statements

​
Expected Results

QueryPurposeExpected Before FixExpected After Fix
Query 1Missing organization_idReview listVerified needed
Query 2Missing audit columns~60 tables0 tables
Query 4Generate fix statements60+ statementsEmpty

​
Audit 3: Storage Bucket Audit

​
Quick Check via MCP

Check for Non-Compliant Buckets: 
mcp_supabase_execute_sql({
  query: `
    SELECT COUNT(*) as non_compliant_count
    FROM storage.buckets
    WHERE name NOT LIKE '%-%';
  `
})
// Expected: 0 (after fixes applied)
// Before fix: 4
List Non-Compliant Buckets: 
mcp_supabase_execute_sql({
  query: `
    SELECT name, public, created_at
    FROM storage.buckets
    WHERE name NOT LIKE '%-%'
    ORDER BY name;
  `
})
// Expected non-compliant: avatars, portal-logos, documents, spreadsheets
Check Storage RLS: 
mcp_supabase_execute_sql({
  query: `
    SELECT policyname, cmd, qual
    FROM pg_policies
    WHERE schemaname = 'storage' AND tablename = 'objects'
    ORDER BY policyname;
  `
})

​
Full Audit via Dashboard

For generating fix statements:
  1. Go to Supabase Dashboard → SQL Editor
  2. Copy contents of scripts/audit-storage-buckets.sql
  3. Run the script
  4. Save Query 3 output - Contains rename statements

​
Expected Results

QueryPurposeExpected Before FixExpected After Fix
Query 1All buckets~7 buckets~7 buckets
Query 2Object counts--
Query 3Generate rename statements4 statementsEmpty

​
Creating Fix Migrations

After running audits, create migrations from the results:

​
Step 1: Create Migration Files

Use CLI: 
# Create RLS fix migration
supabase migration new fix_rls_with_check

# Create column fix migration
supabase migration new fix_missing_audit_columns

# Create storage fix migration
supabase migration new fix_storage_bucket_names

​
Step 2: Fill with Audit Output

Copy the fix statements from each audit into the corresponding migration file. RLS Fix Example: 
-- Fix: Add WITH CHECK to UPDATE policies
-- Generated from audit-rls-policies.sql Query 3

ALTER POLICY "policy_name" ON "table_name"
  USING (organization_id = auth.uid())
  WITH CHECK (organization_id = auth.uid());
-- ... more statements
Storage Fix Example: 
-- Fix: Rename buckets to {core}-{purpose} convention

UPDATE storage.buckets SET name = 'pf-avatars' WHERE name = 'avatars';
UPDATE storage.buckets SET name = 'pf-portal-logos' WHERE name = 'portal-logos';
UPDATE storage.buckets SET name = 'pf-documents' WHERE name = 'documents';
UPDATE storage.buckets SET name = 'pf-spreadsheets' WHERE name = 'spreadsheets';

​
Step 3: Apply and Verify

Use CLI: 
supabase db push
Use MCP to verify: 
// Re-run quick checks - all should return 0
mcp_supabase_execute_sql({
  query: `SELECT COUNT(*) FROM pg_policies WHERE schemaname = 'public' AND cmd = 'UPDATE' AND with_check IS NULL;`
})

​
Quick Reference: MCP vs Dashboard

TaskUse MCPUse Dashboard
Quick count queries✅
List first 20 results✅
Full audit script✅
Generate fix statements✅
Complex multi-query scripts✅
Save results to file✅

​
Troubleshooting

​
Issue: MCP Query Times Out

Solution: Use Dashboard for complex queries. MCP has execution limits.

​
Issue: Query Returns Empty

Solution: Verify migrations applied. Check table exists with: 
mcp_supabase_list_tables({ schemas: ["public"] })

​
Issue: Fix Statement Syntax Error

Solution: Review generated SQL. Some complex policies may need manual adjustment.

​
Issue: Permission Denied

Solution: Check RLS policies on system tables. Use Dashboard SQL Editor which bypasses RLS.

​
Output File Locations

Save audit results to:
  • reports/audits/rls-fix-statements.sql
  • reports/audits/column-fix-statements.sql
  • reports/audits/storage-fix-statements.sql

​
Next Steps

After completing audits:
  1. Create fix migrations using templates
  2. Apply to staging: supabase db push
  3. Re-run audits to verify all issues resolved
  4. Generate TypeScript types: See TYPE_GENERATION_GUIDE.md
Last Updated: 2026-01-15
Version: 2.0.0