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