Supabase Database Operations - Critical Guardrail

Purpose

CRITICAL GUARDRAIL to prevent multi-tenant data leakage and enforce database best practices in the AIProDaily platform.

When to Use

This skill BLOCKS database operations until verified when:

• Writing Supabase queries (supabaseAdmin.from() )

• Accessing tenant-scoped tables

• Creating API routes with database access

• Working with campaign, article, or RSS data

🚨 CRITICAL RULES 🚨

Rule #1: ALWAYS Filter by publication_id

EVERY query on tenant-scoped tables MUST include publication_id filter.

// ✅ CORRECT - publication_id filter present const { data, error } = await supabaseAdmin .from('newsletter_campaigns') .select('id, status, date') .eq('publication_id', newsletterId) // ✅ REQUIRED .eq('id', campaignId) .single()

// ❌ WRONG - Missing publication_id filter (DATA LEAKAGE!) const { data, error } = await supabaseAdmin .from('newsletter_campaigns') .select('id, status, date') .eq('id', campaignId) // ❌ Can access other tenants' data! .single()

Tenant-Scoped Tables (MUST filter by publication_id):

• newsletter_campaigns

• articles

• secondary_articles

• rss_posts

• post_ratings

• rss_feeds

• app_settings

• advertisements

• campaign_advertisements

• archived_articles

• archived_rss_posts

Non-Scoped Tables (publication_id not needed):

• newsletters (top-level tenant table)

• System-wide configuration tables

Rule #2: Use supabaseAdmin for Server-Side Operations

NEVER expose service role key client-side.

// ✅ CORRECT - Server-side API route or Server Action import { supabaseAdmin } from '@/lib/supabase'

export async function POST(request: NextRequest) { const { data } = await supabaseAdmin .from('newsletter_campaigns') .select('*') .eq('publication_id', newsletterId)

return NextResponse.json({ data }) }

// ❌ WRONG - Never in client components 'use client' import { supabaseAdmin } from '@/lib/supabase' // ❌ Security risk!

export default function ClientComponent() { // This exposes service role key to browser const { data } = await supabaseAdmin.from('...').select() }

Where to use supabaseAdmin:

• ✅ API routes (app/api/**/*.ts )

• ✅ Server Actions ('use server' functions)

• ✅ Server Components (without 'use client' )

• ✅ Background jobs/cron

• ✅ Workflow steps

Where NOT to use:

• ❌ Client Components ('use client' )

• ❌ Browser-executed code

• ❌ Public-facing pages

Rule #3: Avoid SELECT *

Only select the fields you need.

// ✅ CORRECT - Specific fields const { data } = await supabaseAdmin .from('articles') .select('id, headline, article_text, is_active') .eq('publication_id', newsletterId) .eq('campaign_id', campaignId)

// ❌ WRONG - Fetches all columns (performance impact) const { data } = await supabaseAdmin .from('articles') .select('*') .eq('publication_id', newsletterId) .eq('campaign_id', campaignId)

Exception: When you genuinely need all columns for data operations.

Rule #4: Always Check for Errors

Never assume database operations succeed.

// ✅ CORRECT - Check for errors const { data, error } = await supabaseAdmin .from('newsletter_campaigns') .select('id, status') .eq('publication_id', newsletterId) .eq('id', campaignId) .single()

if (error) { console.error('[DB] Query failed:', error.message) throw new Error('Failed to fetch campaign') }

if (!data) { console.log('[DB] No campaign found') return null }

// Now safe to use data return data

// ❌ WRONG - No error handling const { data } = await supabaseAdmin .from('newsletter_campaigns') .select('id, status') .eq('id', campaignId) .single()

return data.status // ❌ Crashes if error or data is null

Database Query Patterns

Standard Query Pattern

const { data, error } = await supabaseAdmin .from('table_name') .select('field1, field2, field3') .eq('publication_id', newsletterId) // ✅ ALWAYS for tenant tables .eq('other_field', value) .single() // or .maybeSingle() if record might not exist

if (error) { console.error('[DB] Query error:', error.message) throw new Error(Database query failed: ${error.message}) }

if (!data) { console.log('[DB] No record found') return null }

return data

Insert Pattern

const { data, error } = await supabaseAdmin .from('articles') .insert({ publication_id: newsletterId, // ✅ REQUIRED campaign_id: campaignId, headline: 'Article headline', article_text: 'Content here', is_active: false }) .select() .single()

if (error) { console.error('[DB] Insert failed:', error.message) throw new Error('Failed to create article') }

return data

Update Pattern

const { data, error } = await supabaseAdmin .from('articles') .update({ is_active: true, updated_at: new Date().toISOString() }) .eq('id', articleId) .eq('publication_id', newsletterId) // ✅ REQUIRED - prevents updating other tenants .select() .single()

if (error) { console.error('[DB] Update failed:', error.message) throw new Error('Failed to update article') }

return data

Delete Pattern

const { error } = await supabaseAdmin .from('rss_posts') .delete() .eq('id', postId) .eq('publication_id', newsletterId) // ✅ REQUIRED - prevents deleting other tenants' data

if (error) { console.error('[DB] Delete failed:', error.message) throw new Error('Failed to delete post') }

Join Pattern (Relationships)

const { data, error } = await supabaseAdmin .from('newsletter_campaigns') .select( id, status, date, articles ( id, headline, is_active ), secondary_articles ( id, headline, is_active ) ) .eq('publication_id', newsletterId) // ✅ REQUIRED on parent table .eq('id', campaignId) .single()

Common Mistakes

❌ Forgetting publication_id Filter

// This query can access ANY campaign from ANY tenant! const { data } = await supabaseAdmin .from('newsletter_campaigns') .select('*') .eq('id', campaignId) // ❌ Missing publication_id

❌ Using supabaseAdmin Client-Side

'use client'

// ❌ Exposes service role key to browser export default function MyComponent() { const { data } = await supabaseAdmin.from('...').select() }

❌ No Error Handling

// ❌ No error check - will crash on failure const { data } = await supabaseAdmin.from('...').select().single() const status = data.status // Crashes if data is null

❌ Using SELECT *

// ❌ Fetches unnecessary data, impacts performance const { data } = await supabaseAdmin .from('articles') .select('*')

Quick Reference

✅ DO:

• Always filter by publication_id on tenant-scoped tables

• Use supabaseAdmin only server-side

• Select specific fields

• Check for errors

• Use .single() for single records

• Use .maybeSingle() if record might not exist

• Log errors with [DB] prefix

❌ DON'T:

• Skip publication_id filter

• Use supabaseAdmin in client components

• Use SELECT * without reason

• Ignore errors

• Assume data exists

• Expose service keys client-side

Error Recovery

If you see "Row level security policy violated":

• Check if you're filtering by publication_id

• Verify you're using supabaseAdmin (not client)

• Confirm you're on server-side (API route/Server Action)

If you see "column does not exist":

• Verify column name spelling

• Check if field exists in database schema

• Ensure you're querying the correct table

Skill Status: ACTIVE GUARDRAIL ✅ Enforcement Level: BLOCK (Critical) Line Count: < 500 ✅ Purpose: Prevent multi-tenant data leakage ✅