Data Analysis Skill

This skill provides patterns and best practices for AI-powered data analysis across the Empathy Ledger platform, ensuring quotes, themes, summaries, and suggestions appear consistently everywhere.

Core Data Model

Analysis Pipeline

Transcript (raw audio/text)
    ↓ AI Analysis
Theme Extraction → themes[] array
Quote Extraction → key_quotes[] array
Summary Generation → ai_summary text
Sentiment Analysis → sentiment_scores{}
    ↓ Story Creation
Story (authored content)
    ↓ Connections
Related Stories ← theme matching
Suggested Content ← AI recommendations

Key Tables for Analysis

transcripts

themes

key_quotes

ai_summary

ai_processing_status

stories

themes

cultural_tags

featured_quote

storytellers

expertise_themes

connection_strength

story_suggestions

reason

confidence_score

theme_overlap

Theme System

Standard Theme Categories

const THEME_CATEGORIES = {
  cultural: ['identity', 'heritage', 'tradition', 'language', 'ceremony'],
  family: ['kinship', 'elders', 'children', 'ancestors', 'community'],
  land: ['country', 'connection', 'seasons', 'wildlife', 'sacred-sites'],
  resilience: ['survival', 'adaptation', 'strength', 'healing', 'hope'],
  knowledge: ['wisdom', 'teaching', 'learning', 'stories', 'dreams']
}

Theme Extraction Pattern

// When extracting themes from content:
interface ThemeExtraction {
  themes: string[]           // Max 5-7 primary themes
  theme_confidence: number   // 0-1 confidence score
  cultural_relevance: 'high' | 'medium' | 'low'
}

// Supabase query for theme-based matching
const { data } = await supabase
  .from('stories')
  .select('*')
  .overlaps('themes', ['identity', 'heritage'])
  .order('view_count', { ascending: false })
  .limit(5)

Quote System

Quote Extraction Standards

interface ExtractedQuote {
  text: string              // The quote itself (50-300 chars ideal)
  context?: string          // Surrounding context
  themes: string[]          // Themes this quote relates to
  significance: 'highlight' | 'supporting' | 'context'
  speaker_attribution?: string
}

// Store quotes in transcripts
UPDATE transcripts SET key_quotes = ARRAY[
  'When I walk on Country, I feel my ancestors with me.',
  'Our language carries the wisdom of thousands of years.'
]

Quote Display Patterns

// Story cards should show featured quotes
<StoryCard
  story={story}
  featuredQuote={story.key_quotes?.[0]}
  showThemes={true}
/>

// Quote highlight component
<QuoteHighlight
  quote={quote}
  attribution={storyteller.display_name}
  themes={quote.themes}
/>

AI Analysis Integration

Transcript Analysis Flow

// 1. Trigger analysis
POST /api/transcripts/{id}/analyze

// 2. AI extracts:
{
  themes: ['identity', 'land-connection', 'healing'],
  key_quotes: [
    "The river taught me patience...",
    "Our stories are our survival..."
  ],
  ai_summary: "This transcript explores themes of cultural...",
  sentiment: { positive: 0.7, reflective: 0.8 }
}

// 3. Store and index
UPDATE transcripts SET
  themes = $themes,
  key_quotes = $key_quotes,
  ai_summary = $ai_summary,
  ai_processing_status = 'completed'

Story Suggestion Algorithm

// Find related content based on theme overlap
async function getSuggestedStories(storyId: string) {
  const { data: story } = await supabase
    .from('stories')
    .select('themes, storyteller_id')
    .eq('id', storyId)
    .single()

  // Find stories with overlapping themes
  const { data: related } = await supabase
    .from('stories')
    .select('*, storytellers!inner(display_name)')
    .neq('id', storyId)
    .overlaps('themes', story.themes)
    .limit(5)

  return related.map(r => ({
    ...r,
    theme_overlap: calculateOverlap(story.themes, r.themes),
    reason: generateReason(story.themes, r.themes)
  }))
}

Supabase Best Practices

Array Operations for Themes

-- Find stories with ANY matching theme
SELECT * FROM stories WHERE themes && ARRAY['identity', 'heritage'];

-- Find stories with ALL themes
SELECT * FROM stories WHERE themes @> ARRAY['identity', 'heritage'];

-- Count theme occurrences
SELECT unnest(themes) as theme, count(*)
FROM stories
GROUP BY theme
ORDER BY count DESC;

Full-Text Search Integration

-- Add search vector for quotes
ALTER TABLE transcripts ADD COLUMN
  quote_search tsvector GENERATED ALWAYS AS (
    to_tsvector('english', array_to_string(key_quotes, ' '))
  ) STORED;

CREATE INDEX idx_quote_search ON transcripts USING GIN(quote_search);

-- Search quotes
SELECT * FROM transcripts
WHERE quote_search @@ to_tsquery('english', 'ancestor & wisdom');

Materialized View for Analytics

-- Theme analytics across platform
CREATE MATERIALIZED VIEW theme_analytics AS
SELECT
  unnest(themes) as theme,
  count(*) as story_count,
  count(DISTINCT storyteller_id) as storyteller_count,
  avg(view_count) as avg_views
FROM stories
WHERE status = 'published'
GROUP BY theme;

-- Refresh periodically
REFRESH MATERIALIZED VIEW theme_analytics;

Component Integration Points

Where Analysis Should Appear

stories.key_quotes[0]

stories.themes

theme_analytics

React Components for Analysis

// Theme badge with cultural coloring
<ThemeBadge theme="identity" variant="cultural" />

// Quote card with attribution
<QuoteCard
  quote={quote}
  storyteller={storyteller}
  showThemes
  linkToStory
/>

// Theme cloud visualization
<ThemeCloud
  themes={allThemes}
  onThemeClick={handleFilter}
  highlightActive={activeThemes}
/>

// Story suggestions panel
<SuggestedStories
  currentStory={story}
  maxSuggestions={5}
  showReason
/>

Analysis API Endpoints

POST /api/transcripts/{id}/analyze

GET /api/transcripts/{id}/analyze

GET /api/stories/{id}/suggestions

GET /api/themes

GET /api/themes/{theme}/stories

GET /api/quotes/search

GET /api/storytellers/{id}/themes

When to Use This Skill

Invoke when:

• Adding quotes to story cards or displays
• Implementing story suggestions/related content
• Building theme-based filtering or search
• Creating analytics dashboards
• Integrating AI analysis results
• Designing data visualization components
• Optimizing Supabase queries for analysis data

Reference Files

• analysis-patterns.md - Detailed code patterns
• supabase-queries.md - Optimized query examples
• theme-taxonomy.md - Complete theme hierarchy