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)

Table	Purpose	Has Types
tenants	Top-level multi-tenant isolation	✅
profiles	User accounts (syncs with auth.users)	✅
organisations	Community groups with tier/policy	✅
organization_members	User ↔ Org membership	✅
organization_roles	RBAC roles within orgs	⚠️
organization_invitations	Pending invites	⚠️
tenant_members	User ↔ Tenant membership	✅
profile_organizations	Profile-org join	✅
profile_locations	User locations	✅
profile_projects	User-project join	✅
user_sessions	Session tracking	✅
user_reports	User reports	✅

2. Projects & Context (9 tables)

Table	Purpose	Has Types
projects	Story collections	✅
project_participants	Project members	✅
project_contexts	AI-extracted project context	⚠️
organization_contexts	AI-extracted org context	⚠️
project_profiles	Extended project metadata	⚠️
project_seed_interviews	Seed interview data	⚠️
project_analyses	Cached AI analyses	⚠️
seed_interview_templates	Interview templates	⚠️
development_plans	User development plans	✅

3. Stories & Content (10 tables)

Table	Purpose	Has Types
stories	Core storytelling content	✅
transcripts	Audio/text transcriptions	✅
media_assets	Images, videos, audio	✅
media_usage_tracking	Media access tracking	✅
extracted_quotes	AI-extracted quotes	✅
transcription_jobs	Transcription queue	⚠️
media_import_sessions	Bulk import tracking	⚠️
title_suggestions	AI title suggestions	⚠️
galleries	Photo galleries	✅
gallery_photos	Gallery items	✅

4. Distribution & Syndication (11 tables)

Table	Purpose	Has Types
story_distributions	External platform tracking	✅
story_access_tokens	Ephemeral share links (revocable, time-limited)	✅
embed_tokens	Secure embed tokens	✅
story_syndication_consent	Partner consent records	⚠️
external_applications	Partner apps registry	⚠️
story_access_log	External access log	⚠️
webhook_subscriptions	Partner webhooks	⚠️
webhook_delivery_log	Webhook attempts	⚠️
consent_change_log	Consent audit trail	⚠️
consent_proofs	GDPR consent proofs	⚠️
story_review_invitations	Storyteller review links	⚠️

5. Partner Portal (6 tables)

Table	Purpose	Has Types
partner_projects	Partner curated projects	⚠️
story_syndication_requests	Content requests	⚠️
partner_messages	Partner-storyteller messages	⚠️
partner_team_members	Partner team access	⚠️
partner_analytics_daily	Partner analytics	⚠️
partner_message_templates	Message templates	⚠️

6. Analytics & Insights (17 tables)

Table	Purpose	Has Types
storyteller_analytics	Aggregated storyteller stats	⚠️
narrative_themes	Platform-wide themes	⚠️
storyteller_themes	Per-storyteller themes	⚠️
storyteller_quotes	Impactful quotes	⚠️
storyteller_connections	Network connections	⚠️
storyteller_demographics	Demographics data	⚠️
storyteller_recommendations	AI recommendations	❌ ORPHANED
storyteller_dashboard_config	Dashboard prefs	⚠️
storyteller_milestones	Achievements	⚠️
storyteller_engagement	Engagement metrics	⚠️
storyteller_impact_metrics	Impact tracking	⚠️
cross_narrative_insights	Cross-story insights	❌ ORPHANED
cross_sector_insights	Sector analysis	⚠️
geographic_impact_patterns	Geographic patterns	❌ ORPHANED
theme_evolution_tracking	Theme trends	⚠️
analytics_processing_jobs	Analytics job queue	❌ ORPHANED
platform_analytics	Platform-wide stats	⚠️

7. Engagement Tracking (2 tables)

Table	Purpose	Has Types
story_engagement_events	Per-view events	⚠️
story_engagement_daily	Daily aggregates	⚠️

8. AI & Safety (9 tables)

Table	Purpose	Has Types
ai_usage_events	AI cost/usage tracking	⚠️
tenant_ai_policies	Per-tenant AI limits	⚠️
ai_agent_registry	AI agent configs	⚠️
ai_usage_daily	Daily AI aggregates	⚠️
elder_review_queue	Elder review workflow	⚠️
moderation_results	Moderation decisions	⚠️
moderation_appeals	Appeal requests	⚠️
ai_moderation_logs	AI moderation log	⚠️
ai_safety_logs	Safety check log	⚠️

9. Admin & System (8 tables)

Table	Purpose	Has Types
audit_logs	Compliance audit trail	✅
deletion_requests	GDPR deletion queue	✅
activity_log	Admin activity feed	⚠️
notifications	In-app notifications	⚠️
admin_messages	Admin broadcasts	⚠️
message_recipients	Message delivery	⚠️
ai_analysis_jobs	AI job queue	⚠️
platform_stats_cache	Cached platform stats	⚠️

10. World Tour (3 tables)

Table	Purpose	Has Types
tour_requests	Tour visit requests	⚠️
tour_stops	Completed tour stops	⚠️
dream_organizations	Target organizations	⚠️

11. Cultural & Impact (5 tables)

Table	Purpose	Has Types
cultural_protocols	Cultural guidelines	✅
cultural_tags	Cultural tags	✅
community_impact_insights	Impact moments	✅
community_impact_metrics	Aggregated impact	✅
live_community_narratives	Auto-generated narratives	✅
locations	Geographic locations	✅
events	Event tracking	✅

12. Additional Tables (in Supabase, no migrations)

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

Table	Purpose	Has Types
activities	Activity tracking (52 columns!)	❌
outcomes	Outcome tracking (38 columns)	❌
annual_reports	Annual reports	❌
annual_report_stories	Report-story links	❌
report_sections	Report sections	❌
report_templates	Report templates	❌
blog_posts	Blog content	❌
testimonials	User testimonials	❌
services	Service definitions	❌
service_impact	Service impact metrics	❌
partners	Partner organizations	❌
team_members	Team member profiles	❌

13. Photo System (in Supabase only)

Table	Purpose	Has Types
photo_analytics	Photo view tracking	❌
photo_faces	Face detection data	❌
photo_galleries	Photo galleries	❌
photo_gallery_items	Gallery items	❌
photo_locations	Photo locations	❌
photo_memories	Photo memories	❌
photo_organizations	Photo org links	❌
photo_projects	Photo project links	❌
photo_storytellers	Photo storyteller links	❌
photo_tags	Photo tags	❌

14. Legacy/Sync Tables

Table	Purpose	Has Types
empathy_entries	Legacy empathy data	❌
empathy_sync_log	Sync tracking	❌
syndicated_stories	Syndicated content	❌
scraped_services	Web scraper data	❌
scraper_health_metrics	Scraper health	❌
scraping_metadata	Scraper metadata	❌

---

⚠️ 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

Domain	Type File	Tables Covered
Users	src/types/database/user-profile.ts	profiles, profile_locations, profile_organizations, user_sessions
Orgs	src/types/database/organization-tenant.ts	organisations, organization_members, tenants, tenant_members
Projects	src/types/database/project-management.ts	projects, project_participants
Content	src/types/database/content-media.ts	stories, transcripts, media_assets, extracted_quotes
Distribution	src/types/database/story-ownership.ts	story_distributions, embed_tokens, audit_logs, deletion_requests
Share Control	src/types/database/story-access-tokens.ts	story_access_tokens
Cultural	src/types/database/cultural-sensitivity.ts	cultural_safety_moderation
Locations	src/types/database/location-events.ts	locations, events
Analysis	src/types/database/analysis-support.ts	transcript_analysis, themes, quotes

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

Client	Use Case	RLS	Auth
Browser	React components	Yes	User session
Server SSR	API routes, server components	Yes	User session
Service Role	Admin operations, background jobs	No	Service key

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"