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)

tenants

profiles

organisations

organization_members

organization_roles

organization_invitations

tenant_members

profile_organizations

profile_locations

profile_projects

user_sessions

user_reports

2. Projects & Context (9 tables)

projects

project_participants

project_contexts

organization_contexts

project_profiles

project_seed_interviews

project_analyses

seed_interview_templates

development_plans

3. Stories & Content (10 tables)

stories

transcripts

media_assets

media_usage_tracking

extracted_quotes

transcription_jobs

media_import_sessions

title_suggestions

galleries

gallery_photos

4. Distribution & Syndication (11 tables)

story_distributions

story_access_tokens

embed_tokens

story_syndication_consent

external_applications

story_access_log

webhook_subscriptions

webhook_delivery_log

consent_change_log

consent_proofs

story_review_invitations

5. Partner Portal (6 tables)

partner_projects

story_syndication_requests

partner_messages

partner_team_members

partner_analytics_daily

partner_message_templates

6. Analytics & Insights (17 tables)

storyteller_analytics

narrative_themes

storyteller_themes

storyteller_quotes

storyteller_connections

storyteller_demographics

storyteller_recommendations

storyteller_dashboard_config

storyteller_milestones

storyteller_engagement

storyteller_impact_metrics

cross_narrative_insights

cross_sector_insights

geographic_impact_patterns

theme_evolution_tracking

analytics_processing_jobs

platform_analytics

7. Engagement Tracking (2 tables)

story_engagement_events

story_engagement_daily

8. AI & Safety (9 tables)

ai_usage_events

tenant_ai_policies

ai_agent_registry

ai_usage_daily

elder_review_queue

moderation_results

moderation_appeals

ai_moderation_logs

ai_safety_logs

9. Admin & System (8 tables)

audit_logs

deletion_requests

activity_log

notifications

admin_messages

message_recipients

ai_analysis_jobs

platform_stats_cache

10. World Tour (3 tables)

tour_requests

tour_stops

dream_organizations

11. Cultural & Impact (5 tables)

cultural_protocols

cultural_tags

community_impact_insights

community_impact_metrics

live_community_narratives

locations

events

12. Additional Tables (in Supabase, no migrations)

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

activities

outcomes

annual_reports

annual_report_stories

report_sections

report_templates

blog_posts

testimonials

services

service_impact

partners

team_members

13. Photo System (in Supabase only)

photo_analytics

photo_faces

photo_galleries

photo_gallery_items

photo_locations

photo_memories

photo_organizations

photo_projects

photo_storytellers

photo_tags

14. Legacy/Sync Tables

empathy_entries

empathy_sync_log

syndicated_stories

scraped_services

scraper_health_metrics

scraping_metadata

⚠️ Spelling Note

Supabase uses

organizations

organisations

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

src/types/database/user-profile.ts

src/types/database/organization-tenant.ts

src/types/database/project-management.ts

src/types/database/content-media.ts

src/types/database/story-ownership.ts

src/types/database/story-access-tokens.ts

src/types/database/cultural-sensitivity.ts

src/types/database/location-events.ts

src/types/database/analysis-support.ts

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"