supabase-database-ops

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

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

---

Rule #1: ALWAYS Filter by publication_id

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

```typescript

// ✅ 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.

```typescript

// ✅ 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.

```typescript

// ✅ 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.

```typescript

// ✅ 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

```

---

Standard Query Pattern

```typescript

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

```typescript

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

```typescript

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

```typescript

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)

```typescript

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()

```

---

❌ Forgetting publication_id Filter

```typescript

// 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

```typescript

'use client'

// ❌ Exposes service role key to browser

export default function MyComponent() {

const { data } = await supabaseAdmin.from('...').select()

}

```

❌ No Error Handling

```typescript

// ❌ 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 *

```typescript

// ❌ Fetches unnecessary data, impacts performance

const { data } = await supabaseAdmin

.from('articles')

.select('*')

```

---

✅ 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

---

If you see "Row level security policy violated":

1. Check if you're filtering by publication_id
2. Verify you're using supabaseAdmin (not client)
3. Confirm you're on server-side (API route/Server Action)

If you see "column does not exist":

1. Verify column name spelling
2. Check if field exists in database schema
3. Ensure you're querying the correct table

---

Skill Status: ACTIVE GUARDRAIL ✅

Enforcement Level: BLOCK (Critical)

Line Count: < 500 ✅

Purpose: Prevent multi-tenant data leakage ✅