> ## 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.

# SQL Audit Execution Instructions

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

**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):**

```typescript theme={null}
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:**

```typescript theme={null}
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:**

```typescript theme={null}
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

| Query   | Purpose                  | Expected Before Fix | Expected After Fix |
| ------- | ------------------------ | ------------------- | ------------------ |
| Query 1 | List all UPDATE policies | \~118 missing       | 0 missing          |
| Query 2 | Summary by command       | -                   | -                  |
| Query 3 | Generate fix statements  | 118 statements      | Empty              |

***

## Audit 2: Column Compliance Audit

### Quick Check via MCP

**Check for Missing updated\_by:**

```typescript theme={null}
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:**

```typescript theme={null}
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:**

```typescript theme={null}
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

| Query   | Purpose                  | Expected Before Fix | Expected After Fix |
| ------- | ------------------------ | ------------------- | ------------------ |
| Query 1 | Missing organization\_id | Review list         | Verified needed    |
| Query 2 | Missing audit columns    | \~60 tables         | 0 tables           |
| Query 4 | Generate fix statements  | 60+ statements      | Empty              |

***

## Audit 3: Storage Bucket Audit

### Quick Check via MCP

**Check for Non-Compliant Buckets:**

```typescript theme={null}
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:**

```typescript theme={null}
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:**

```typescript theme={null}
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

| Query   | Purpose                    | Expected Before Fix | Expected After Fix |
| ------- | -------------------------- | ------------------- | ------------------ |
| Query 1 | All buckets                | \~7 buckets         | \~7 buckets        |
| Query 2 | Object counts              | -                   | -                  |
| Query 3 | Generate rename statements | 4 statements        | Empty              |

***

## Creating Fix Migrations

After running audits, create migrations from the results:

### Step 1: Create Migration Files

**Use CLI:**

```bash theme={null}
# 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:**

```sql theme={null}
-- 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:**

```sql theme={null}
-- 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:**

```bash theme={null}
supabase db push
```

**Use MCP to verify:**

```typescript theme={null}
// 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

| Task                        | Use MCP | Use 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:

```typescript theme={null}
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](TYPE_GENERATION_GUIDE.md)

***

## Related Documentation

* [Completion Plan](COMPLETION_PLAN.md) - Full migration workflow
* [MCP Usage Guide](../development/MCP_USAGE.md) - When to use MCP vs CLI
* [Pre-Production Checklist](PRE_PRODUCTION_CHECKLIST.md) - Final validation

***

**Last Updated:** 2026-01-15\
**Version:** 2.0.0