project-explorer

name	project-explorer
description	Explore EL projects, organizations, and all related content - storytellers, stories, transcripts, media, galleries, analysis. Core function for syndication API and ACT ecosystem.

Project Explorer

Explore the complete content universe for any Empathy Ledger project or organization. Shows storytellers, stories, transcripts, media, galleries, analysis, and syndication status.

Usage

/project-explorer [project-name-or-org]

Examples:

• /project-explorer GOODS - Everything in the GOODS project
• /project-explorer Oonchiumpa - Oonchiumpa project content
• /project-explorer org:goods - All projects under GOODS org
• /project-explorer all - Summary of all projects

When to Use

• Understanding what content exists for a project
• Preparing content for syndication to external sites
• Auditing data quality (missing themes, unanalyzed transcripts)
• Finding storytellers and their contributions
• Checking gallery/media inventory
• Debugging syndication API responses

Data Model (VERIFIED)

Organizations (orgs)
  └── Projects (via projects.organization_id)
        ├── Storytellers (via project_storytellers junction)
        │     ├── Stories (via stories.storyteller_id + stories.project_id)
        │     ├── Transcripts (via transcripts.storyteller_id + transcripts.project_id)
        │     └── Media (via media_assets.storyteller_id)
        ├── Galleries (via project_galleries junction)
        │     └── Media (via gallery_media_associations junction)
        └── Syndication (via syndication_sites + story syndication_destinations)

Key Relationship Rules

From	To	Via
Organization → Projects	projects.organization_id	Direct FK
Project → Storytellers	project_storytellers	Junction table (project_id, storyteller_id) — CANONICAL
Project → Stories	stories.project_id	Direct FK
Project → Transcripts	transcripts.project_id	Direct FK
Project → Galleries	project_galleries	Junction table (project_id, gallery_id)
Project → Media	media_assets.project_id	Direct FK (or via galleries)
Gallery → Media	gallery_media_associations	Junction table (gallery_id, media_asset_id)
Storyteller → Stories	stories.storyteller_id	Direct FK
Storyteller → Transcripts	transcripts.storyteller_id	Direct FK

Critical ID Notes

• profiles.id = Supabase Auth user ID (NOT the same as storyteller)
• storytellers.id = Storyteller record ID (used by stories, transcripts, media)
• storytellers.profile_id = FK to profiles.id (immutable, the link between them)
• Stories/transcripts use storyteller_id (NOT profile_id)

Organizations with Slugs

Organization	ID (first 8)	Slug
A Curious Tractor	db0de7bd	a-curious-tractor
Goods.	612ce757	goods
Oonchiumpa	c53077e1	oonchiumpa
Palm Island CC	084f851c	palm-island-community-company
Snow Foundation	4a1c31e8	snow-foundation
MingaMinga	3a924e56	mingaminga-rangers
Orange Sky	1d542d98	orange-sky
Diagrama	fbe80fa6	diagrama

Key Projects

Project	ID (first 8)	Org	Code
Goods.	6bd47c8a	ACT	ACT-GD
Goods on Country	5de91184	ACT	null
Empathy Ledger	a2e50220	ACT	ACT-EL
Oonchiumpa	1dfb747c	ACT	ACT-OC
JusticeHub	2e774118	ACT	ACT-JH
The Harvest	0584b447	ACT	ACT-HV
Quandamooka	62414cf3	ACT	ACT-QD
Palm Island	6179c4a0	PICC	ACT-PI
Orange Sky	2e5f2b39	OS	ACT-OS

Syndication Sites

Site	Slug	Domains
GOODS	goods-asset-register	goods-asset-register.vercel.app, goods-on-country.vercel.app, localhost:3000/3001
ACT Farm	actfarm	actfarm.org.au
JusticeHub	justicehub	justicehub.org.au
The Harvest	theharvest	theharvest.org.au
ACT Placemat	actplacemat	actplacemat.org.au
ACT Command	act-command	command.act.place

Workflow

Run from project root (/Users/benknight/Code/empathy-ledger-v2):

npx tsx <<'EOF'
import { createClient } from '@supabase/supabase-js'
import * as dotenv from 'dotenv'
dotenv.config({ path: '.env.local' })

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
)

async function exploreProject(search: string) {
  // 1. Find project
  const { data: projects } = await supabase
    .from('projects')
    .select('id, name, organization_id, act_project_code, status, slug')
    .or(`name.ilike.%${search}%,slug.ilike.%${search}%,act_project_code.ilike.%${search}%`)

  if (!projects?.length) {
    console.log('No projects found for: ' + search)
    return
  }

  for (const project of projects) {
    console.log('\n' + '='.repeat(60))
    console.log('PROJECT: ' + project.name)
    console.log('ID: ' + project.id)
    console.log('Code: ' + (project.act_project_code || 'none'))
    console.log('Status: ' + project.status)
    console.log('='.repeat(60))

    // Organization
    if (project.organization_id) {
      const { data: org } = await supabase
        .from('organizations').select('name, slug')
        .eq('id', project.organization_id).single()
      console.log('\nORG: ' + org?.name + ' (slug: ' + org?.slug + ')')
    }

    // Storytellers via project_storytellers (canonical junction table)
    const { data: participants } = await supabase
      .from('project_storytellers').select('storyteller_id')
      .eq('project_id', project.id)

    const storytellerIds = participants?.map(p => p.storyteller_id) || []
    console.log('\nSTORYTELLERS: ' + storytellerIds.length)

    if (storytellerIds.length > 0) {
      const { data: storytellers } = await supabase
        .from('storytellers').select('id, display_name, is_elder, is_active, location')
        .in('id', storytellerIds)
      storytellers?.forEach(s =>
        console.log('  ' + s.display_name +
          (s.is_elder ? ' [ELDER]' : '') +
          (s.is_active ? '' : ' [INACTIVE]') +
          (s.location ? ' - ' + s.location : '')))
    }

    // Stories
    const { data: stories } = await supabase
      .from('stories').select('id, title, status, storyteller_id, themes, tags, syndication_enabled')
      .eq('project_id', project.id)

    const published = stories?.filter(s => s.status === 'published') || []
    const withThemes = stories?.filter(s => s.themes && JSON.stringify(s.themes) !== '[]') || []
    console.log('\nSTORIES: ' + (stories?.length || 0) + ' total')
    console.log('  Published: ' + published.length)
    console.log('  With themes: ' + withThemes.length)
    console.log('  Syndication enabled: ' + (stories?.filter(s => s.syndication_enabled)?.length || 0))
    stories?.forEach(s =>
      console.log('  - ' + (s.title || 'Untitled').substring(0, 50) + ' [' + s.status + ']'))

    // Transcripts
    const { data: transcripts } = await supabase
      .from('transcripts').select('id, title, storyteller_id, ai_processing_status, themes')
      .eq('project_id', project.id)

    // Also check transcripts via storyteller_id if project_id not set
    let extraTranscripts: any[] = []
    if (storytellerIds.length > 0) {
      const { data: stTranscripts } = await supabase
        .from('transcripts').select('id, title, storyteller_id, ai_processing_status, themes')
        .in('storyteller_id', storytellerIds)
        .is('project_id', null)
      extraTranscripts = stTranscripts || []
    }

    const allTranscripts = [...(transcripts || []), ...extraTranscripts]
    const analyzed = allTranscripts.filter(t => t.ai_processing_status === 'completed')
    console.log('\nTRANSCRIPTS: ' + allTranscripts.length + ' total')
    console.log('  Via project_id: ' + (transcripts?.length || 0))
    console.log('  Via storyteller (no project): ' + extraTranscripts.length)
    console.log('  AI analyzed: ' + analyzed.length)
    allTranscripts.forEach(t =>
      console.log('  - ' + (t.title || 'untitled').substring(0, 50) + ' [ai=' + (t.ai_processing_status || 'none') + ']'))

    // Galleries via project_galleries
    const { data: projGalleries } = await supabase
      .from('project_galleries').select('gallery_id')
      .eq('project_id', project.id)

    if (projGalleries && projGalleries.length > 0) {
      const galleryIds = projGalleries.map(pg => pg.gallery_id)
      const { data: galleries } = await supabase
        .from('galleries').select('id, title, slug, photo_count, status')
        .in('id', galleryIds)

      console.log('\nGALLERIES: ' + galleries?.length)
      for (const g of galleries || []) {
        const { count } = await supabase
          .from('gallery_media_associations').select('*', { count: 'exact', head: true })
          .eq('gallery_id', g.id)
        console.log('  ' + g.title + ' | media=' + count + ' | photo_count=' + g.photo_count)
      }
    } else {
      console.log('\nGALLERIES: 0')
    }

    // Direct media assets
    const { count: directMedia } = await supabase
      .from('media_assets').select('*', { count: 'exact', head: true })
      .eq('project_id', project.id)
    console.log('\nMEDIA (direct project_id): ' + directMedia)

    // Syndication check
    const { data: syndSites } = await supabase
      .from('syndication_sites').select('slug, allowed_domains, is_active')
    console.log('\nSYNDICATION SITES: ' + syndSites?.length)
    syndSites?.forEach(s => console.log('  ' + s.slug + ' [' + (s.is_active ? 'active' : 'inactive') + '] domains: ' + (s.allowed_domains || []).join(', ')))

    // Data gaps
    console.log('\nDATA GAPS:')
    if ((stories?.length || 0) === 0) console.log('  - NO STORIES tagged to this project')
    if (storytellerIds.length === 0) console.log('  - NO STORYTELLERS linked via project_storytellers')
    if (allTranscripts.length > analyzed.length) console.log('  - ' + (allTranscripts.length - analyzed.length) + ' transcripts not AI-analyzed')
    if (withThemes.length < (stories?.length || 0)) console.log('  - ' + ((stories?.length || 0) - withThemes.length) + ' stories missing themes')
  }
}

// Run with argument or default
const search = process.argv[2] || 'GOODS'
exploreProject(search)
EOF