Supabase

Supabase Database Skill

Navigate and query the Empathy Ledger Supabase database with confidence.

Database Relationship Map

┌─────────────────────────────────────────────────────────────────────────────┐
│                              TENANTS (top-level)                            │
│                                    │                                        │
│    ┌───────────────────────────────┼───────────────────────────────┐        │
│    │                               │                               │        │
│    ▼                               ▼                               ▼        │
│ ┌──────────────┐           ┌──────────────┐           ┌──────────────────┐  │
│ │ organisations │◄──────────│   profiles   │──────────►│  tenant_members  │  │
│ └──────────────┘           └──────────────┘           └──────────────────┘  │
│        │                          │                                         │
│        │                          │ is_storyteller                          │
│        ▼                          ▼                                         │
│ ┌──────────────┐           ┌──────────────┐                                 │
│ │   projects   │◄──────────│    stories   │                                 │
│ └──────────────┘           └──────────────┘                                 │
│        │                          │                                         │
│        │                          ├────────────────────┐                    │
│        ▼                          ▼                    ▼                    │
│ ┌──────────────┐           ┌──────────────┐    ┌──────────────┐             │
│ │ transcripts  │           │media_assets  │    │story_distribs│             │
│ └──────────────┘           └──────────────┘    └──────────────┘             │
│        │                          │                    │                    │
│        │                          │                    ▼                    │
│        ▼                          ▼             ┌──────────────┐            │
│ ┌──────────────┐           ┌──────────────┐    │ embed_tokens │            │
│ │ key_quotes[] │           │media_usage   │    └──────────────┘            │
│ │ themes[]     │           │_tracking     │                                 │
│ │ ai_summary   │           └──────────────┘                                 │
│ └──────────────┘                                                            │
└─────────────────────────────────────────────────────────────────────────────┘

Complete Table Inventory

Live Supabase: 165 objects (153 tables, 7 views, 3 partitions, 2 system) Migration-defined: 71 tables With TypeScript Types: 35 tables

See also: DATABASE_ALIGNMENT_AUDIT.md

⚠️ Schema Drift Alert: ~80 tables exist in Supabase but have no migration files. Use npx supabase gen types typescript --local to generate accurate types.

1. Identity & Access (12 tables)

2. Projects & Context (9 tables)

3. Stories & Content (10 tables)

4. Distribution & Syndication (11 tables)

5. Partner Portal (6 tables)

6. Analytics & Insights (17 tables)

7. Engagement Tracking (2 tables)

8. AI & Safety (9 tables)

9. Admin & System (8 tables)

10. World Tour (3 tables)

11. Cultural & Impact (5 tables)

12. Additional Tables (in Supabase, no migrations)

These tables exist in live Supabase but have no migration files:

13. Photo System (in Supabase only)

14. Legacy/Sync Tables

⚠️ Spelling Note

Supabase uses organizations (US spelling) TypeScript types use organisations (UK spelling)

When querying, use the Supabase spelling. The types may need updating.

Foreign Key Relationships

Stories Table (Central Hub)

stories.storyteller_id  → profiles.id       // Who told this story
stories.author_id       → profiles.id       // Who authored/recorded
stories.project_id      → projects.id       // Which project it belongs to
stories.organization_id → organisations.id  // Which org owns it
stories.tenant_id       → tenants.id        // Tenant isolation
stories.featured_media_id → media_assets.id // Cover image

Transcripts Table

transcripts.storyteller_id → profiles.id   // Who is speaking
transcripts.tenant_id      → tenants.id    // Tenant isolation
// Note: stories can link to transcripts via content or transcript_id

Organization Hierarchy

tenants.organization_id        → organisations.id  // Primary org for tenant
organisations.tenant_id        → tenants.id        // Tenant ownership
organization_members.profile_id     → profiles.id      // User
organization_members.organization_id → organisations.id // Org

Distribution Chain

story_distributions.story_id     → stories.id  // Which story
story_distributions.tenant_id    → tenants.id  // Tenant isolation
embed_tokens.story_id            → stories.id  // Which story
embed_tokens.distribution_id     → story_distributions.id  // Parent distribution
story_access_tokens.story_id     → stories.id  // Which story (ephemeral share links)
story_access_tokens.created_by   → profiles.id // Who created the link
story_access_tokens.tenant_id    → tenants.id  // Tenant isolation

Type Files by Domain

Supabase Client Usage

Client Types

// Browser client (uses cookies, respects RLS)
import { createSupabaseBrowserClient } from '@/lib/supabase/client'
const supabase = createSupabaseBrowserClient()

// Server SSR client (for API routes, server components)
import { createSupabaseServerClient } from '@/lib/supabase/client-ssr'
const supabase = createSupabaseServerClient()

// Service role client (bypasses RLS - admin only!)
import { createSupabaseServiceClient } from '@/lib/supabase/service-role-client'
const supabase = createSupabaseServiceClient()

When to Use Each Client

Common Query Patterns

Get Stories with Storyteller

const { data } = await supabase
  .from('stories')
  .select(`
    *,
    storyteller:profiles!stories_storyteller_id_fkey(
      id, display_name, profile_image_url
    )
  `)
  .eq('status', 'published')
  .eq('tenant_id', tenantId)

Get Transcripts with Themes

const { data } = await supabase
  .from('transcripts')
  .select('id, title, themes, key_quotes, ai_summary')
  .not('themes', 'is', null)
  .order('created_at', { ascending: false })

Get Organization with Members

const { data } = await supabase
  .from('organisations')
  .select(`
    *,
    members:organization_members(
      profile:profiles(id, display_name, profile_image_url),
      role
    )
  `)
  .eq('id', orgId)
  .single()

Get Story with All Relationships

const { data } = await supabase
  .from('stories')
  .select(`
    *,
    storyteller:profiles!stories_storyteller_id_fkey(*),
    project:projects(*),
    organization:organisations(*),
    distributions:story_distributions(*),
    featured_media:media_assets(*)
  `)
  .eq('id', storyId)
  .single()

Theme-Based Story Search (Array Overlap)

// Stories with ANY matching theme
const { data } = await supabase
  .from('stories')
  .select('*')
  .overlaps('ai_themes', ['identity', 'heritage'])

// Stories with ALL themes
const { data } = await supabase
  .from('stories')
  .select('*')
  .contains('ai_themes', ['identity', 'heritage'])

Count by Status

const { count } = await supabase
  .from('stories')
  .select('*', { count: 'exact', head: true })
  .eq('status', 'published')
  .eq('tenant_id', tenantId)

Story Access Tokens (Share Control)

Validate Token and Get Story

// Use database function for validation + view count increment
const { data: validation } = await supabase.rpc('validate_and_increment_token', {
  p_token: 'abc123xyz'
})

if (validation[0]?.is_valid) {
  const { data: story } = await supabase
    .from('stories')
    .select('*, storyteller:profiles(*)')
    .eq('id', validation[0].story_id)
    .single()
}

Get Active Share Links for Story

const { data: tokens } = await supabase
  .from('story_access_tokens')
  .select('*')
  .eq('story_id', storyId)
  .eq('revoked', false)
  .gt('expires_at', new Date().toISOString())
  .order('created_at', { ascending: false })

Create Share Link

import { nanoid } from 'nanoid'

const token = nanoid(21)
const expiresAt = new Date(Date.now() + 7 * 24 * 60 * 60 * 1000) // 7 days

const { data } = await supabase
  .from('story_access_tokens')
  .insert({
    story_id: storyId,
    token,
    expires_at: expiresAt.toISOString(),
    purpose: 'social-media',
    created_by: userId,
    tenant_id: tenantId
  })
  .select()
  .single()

const shareUrl = `https://empathy-ledger.org/s/${token}`

Revoke Share Link

const { error } = await supabase
  .from('story_access_tokens')
  .update({ revoked: true })
  .eq('id', tokenId)
  .eq('story_id', storyId) // Ensure user owns the story

Get Share Analytics

// View counts, most shared stories
const { data: analytics } = await supabase
  .from('story_access_tokens')
  .select('story_id, view_count, purpose, shared_to')
  .eq('story_id', storyId)
  .order('view_count', { ascending: false })

Multi-Tenant Query Pattern

IMPORTANT: Always filter by tenant_id for data isolation.

// Standard pattern for all queries
async function getStories(userId: string) {
  const supabase = createSupabaseServerClient()

  // 1. Get user's tenant
  const { data: profile } = await supabase
    .from('profiles')
    .select('tenant_id')
    .eq('id', userId)
    .single()

  // 2. Query with tenant filter
  const { data } = await supabase
    .from('stories')
    .select('*')
    .eq('tenant_id', profile.tenant_id)  // Always include!
    .eq('status', 'published')

  return data
}

Database Functions

Available RPC functions:

// Calculate tenant analytics
const { data } = await supabase.rpc('calculate_tenant_analytics', {
  tenant_uuid: tenantId
})

// Get organization stats
const { data } = await supabase.rpc('get_organization_stats', {
  org_id: orgId
})

// Search quotes with full-text
const { data } = await supabase.rpc('search_quotes', {
  query: 'wisdom ancestors'
})

// Search media
const { data } = await supabase.rpc('search_media', {
  query: 'interview video'
})

Migrations Location

All database schema in: supabase/migrations/

Key migrations:

• 20251220093000_multi_org_tenants.sql - Multi-org tenant structure
• 20251207_story_ownership_distribution.sql - Distribution system
• 20251209000000_cultural_safety_moderation_tables.sql - Cultural safety
• 20251210000000_partner_portal_system.sql - Partner distribution

When to Use This Skill

Invoke when:

• Needing to understand table relationships
• Writing Supabase queries
• Finding the right type definitions
• Understanding foreign key constraints
• Debugging data access issues
• Implementing new features that touch the database

MCP Access

This project has MCP configured for direct Supabase access:

Read-only (default):

https://mcp.supabase.com/mcp?project_ref=yvnuayzslukamizrlhwb&read_only=true

With write access:

https://mcp.supabase.com/mcp?project_ref=yvnuayzslukamizrlhwb&features=database,docs,debugging,development,functions,branching

Available MCP Tools:

• list_tables - View all tables and columns
• execute_sql - Run SQL queries
• list_migrations - View migration history
• generate_typescript_types - Generate types from schema
• get_logs - View application logs

See: SUPABASE_ACCESS_GUIDE.md

Trigger: User asks about database tables, relationships, queries, or "how do I get X from Supabase"