supabase-database-ops
Critical guardrail for Supabase database operations ensuring multi-tenant isolation with publication_id filtering, proper use of supabaseAdmin, avoiding SELECT *, error handling patterns, and secure server-side database access. Use when writing database queries, working with supabase, accessing newsletter_campaigns, articles, rss_posts, or any tenant-scoped data.
- Category: discovered
- Tags: github, community
- Version: 1.0.0
- Author: walid
- Downloads: 0
- Updated: 2025-11-22
Download the latest version (v1.0.0) from this page.
SKILL.md
---
name: supabase-database-ops
description: Critical guardrail for Supabase database operations ensuring multi-tenant isolation with publication_id filtering, proper use of supabaseAdmin, avoiding SELECT *, error handling patterns, and secure server-side database access. Use when writing database queries, working with supabase, accessing newsletter_campaigns, articles, rss_posts, or any tenant-scoped data.
---
# 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.**
```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
```
---
## Database Query Patterns
### 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()
```
---
## Common Mistakes
### ❌ 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('*')
```
---
## 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":
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 ✅