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](../../../docs/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