LLM-Generated Database Summary

[Entropy-rgb]

Problem Statement

When connecting a database, users see raw schema (tables, columns, types) but no business context or overview of what the data represents. There is no way to get a quick understanding of what the database contains and its key characteristics.

Proposed Solution

Generate an AI-powered summary of the connected database, including business insights, data quality assessment, and key metrics.

Acceptance Criteria

• Auto-generate summary on database connect
• Summary includes: database purpose, key entities, relationships, data quality
• Highlight notable patterns (large tables, high-null columns, recent data)
• Suggest starter questions based on schema
• Display summary in chat as welcome message
• Refresh summary on demand
• Summary stored in workspace memory
• Customizable summary sections

Technical Approach

Backend Changes

1. Summary generator (backend/app/services/summary.py):

class DatabaseSummaryGenerator:
    def __init__(self, llm, db):
        self.llm = llm
        self.db = db
    
    async def generate_summary(self, schema: dict, db_id: str) -> dict:
        """Generate comprehensive database summary."""
        
        # Gather statistics
        stats = self._gather_statistics(schema)
        
        # Generate LLM insights
        prompt = f"""Analyze this database schema and provide a comprehensive summary:

Schema: {schema}
Statistics: {stats}

Provide:
1. Database purpose (what business domain it serves)
2. Key entities (most important tables)
3. Relationships (how tables connect)
4. Data quality assessment
5. Notable patterns or insights
6. 5 suggested starter questions

Return as JSON with sections: purpose, entities, relationships, quality, patterns, starter_questions
"""
        
        llm_response = await self.llm.generate(prompt)
        
        return {
            "db_id": db_id,
            "statistics": stats,
            "llm_insights": llm_response,
            "generated_at": datetime.utcnow()
        }
    
    def _gather_statistics(self, schema: dict) -> dict:
        """Gather key statistics from schema."""
        stats = {
            "total_tables": len(schema.get("tables", [])),
            "total_columns": 0,
            "large_tables": [],
            "high_null_columns": [],
            "date_columns": [],
            "recent_data": None
        }
        
        for table in schema.get("tables", []):
            stats["total_columns"] += len(table.get("columns", []))
            
            # Track large tables
            if table.get("row_count", 0) > 10000:
                stats["large_tables"].append({
                    "name": table["name"],
                    "row_count": table["row_count"]
                })
            
            # Track high-null columns
            for col in table.get("columns", []):
                if col.get("null_rate", 0) > 0.1:
                    stats["high_null_columns"].append({
                        "table": table["name"],
                        "column": col["name"],
                        "null_rate": col["null_rate"]
                    })
        
        return stats

2. Summary storage (backend/app/pgdatabase/models.py):

class DatabaseSummary(Base):
    __tablename__ = "database_summaries"
    id = Column(UUID, primary_key=True)
    db_id = Column(String, nullable=False, unique=True)
    summary = Column(JSON, nullable=False)
    generated_at = Column(DateTime, default=func.now())
    updated_at = Column(DateTime, onupdate=func.now())

3. API routes (backend/app/routes/summary.py):

• GET /api/summary - Get summary for connected database
• POST /api/summary/generate - Regenerate summary
• PATCH /api/summary/sections - Customize summary sections

4. Auto-generate on connect - Hook into database connect flow

Frontend Changes

1. Summary display (frontend/src/lib/components/DatabaseSummary.svelte):

<script>
    export let summary;
</script>

<div class="database-summary">
    <h2>Database Overview</h2>
    
    <section class="purpose">
        <h3>Purpose</h3>
        <p>{summary.llm_insights.purpose}</p>
    </section>
    
    <section class="entities">
        <h3>Key Entities</h3>
        <ul>
            {#each summary.llm_insights.entities as entity}
                <li><strong>{entity.name}</strong>: {entity.description}</li>
            {/each}
        </ul>
    </section>
    
    <section class="statistics">
        <h3>Statistics</h3>
        <div class="stats-grid">
            <div class="stat">
                <span class="value">{summary.statistics.total_tables}</span>
                <span class="label">Tables</span>
            </div>
            <div class="stat">
                <span class="value">{summary.statistics.total_columns}</span>
                <span class="label">Columns</span>
            </div>
        </div>
    </section>
    
    <section class="starter-questions">
        <h3>Suggested Questions</h3>
        {#each summary.llm_insights.starter_questions as question}
            <button class="starter-btn" on:click={() => askQuestion(question)}>
                {question}
            </button>
        {/each}
    </section>
    
    <button on:click={refreshSummary}>Refresh Summary</button>
</div>

2. Welcome message - Show summary as first message in new conversations

3. Sidebar integration - Summary in collapsible section

Key Files

• backend/app/services/summary.py - Summary generator (new)
• backend/app/pgdatabase/models.py - DatabaseSummary model
• backend/app/routes/summary.py - API routes (new)
• frontend/src/lib/components/DatabaseSummary.svelte - Summary display

Related Issues

• Part of Feature Roadmap (Feature Roadmap: Next Iteration of BoloDB #71)
• Related to Auto-insights on Connect (Auto-insights on Connect #233) for proactive analysis
• Related to LLM-Generated Database Summary for business context