Data Dashboard Two-Stage Separation Architecture: Design Time vs Runtime Decoupling · cfgnotes

🎯 Core Question of This Chapter

What is the core contradiction of Dashboards?

Dimension	Problems with Traditional Approaches	Our Solution
Performance	LLM called on every refresh, 3-5 second latency	Only SQL executed at runtime, completes within 200ms
Cost	100 refreshes = 100 LLM API calls	LLM called only once at design time, zero cost afterwards
Maintainability	Hardcoded UI layout, requirement changes need code changes	Configuration-driven, modify JSON to take effect
Flexibility	Fixed templates, cannot customize layout	Grid system + drag-and-drop interaction

---

📐 Architecture Overview: Two-Phase Separation Model

Data Dashboard Two-Phase Separation Architecture

Core Idea

LLM called at design time to write configuration, only SQL executed at runtime

Why Is This Design Revolutionary?

Analogy:

Think of it like prepared meals vs. cooking on the spot:

• Design time = chef carefully prepares a dish (calls LLM to generate configuration)
• Runtime = just heat it up in the microwave (read configuration + execute SQL)

Fatal Flaw of Traditional Approaches:

# ❌ Traditional approach: LLM called every time
async def get_dashboard_data(dashboard_id: int):
    dashboard = await db.get(dashboard_id)

    # SQL must be regenerated for every widget!
    for widget in dashboard.widgets:
        sql = await llm.generate_sql(widget.description)  # 💸 Tokens consumed each time
        data = await db.execute(sql)                       # ⏳ Waiting for LLM response

    return data

Our Approach:

# ✅ Our approach: No LLM calls at runtime
async def get_dashboard_data(dashboard_id: int):
    # Step 1: Load pre-generated configuration directly from the database
    config = await load_dashboard_config(dashboard_id)  # ~10ms

    # Step 2: Execute all widget SQLs concurrently (no LLM calls)
    tasks = [
        execute_query(widget.sql_query)
        for widget in config.widgets
    ]
    results = await asyncio.gather(*tasks)  # Concurrent execution, ~200ms

    return dict(zip([w.id for w in config.widgets], results))

---

🎨 I. Phase One: Design Time — LLM-Driven Configuration Generation

1.1 User Input → Structured Configuration

app/services/dashboard_service.py

async def create_from_natural_language(
    db: AsyncSession,
    datasource_id: int,
    description: str,
    name: Optional[str] = None,
) -> Dashboard:
    """
    Create a Dashboard from a natural language description

    Args:
        datasource_id: Data source ID
        description: User description, e.g., "Create a sales dashboard for me..."
        name: Dashboard name (optional, LLM can generate automatically)
    """
    # Step 1: Get semantic model (injected into the Prompt)
    semantic_model = await get_semantic_model(db, datasource_id)

    # Step 2: Build the Prompt
    prompt = f"""You are a professional data visualization expert. Based on the user's requirements and database structure,
generate a complete Dashboard configuration object.

## Database Structure
{json.dumps(semantic_model, ensure_ascii=False, indent=2)}

## User Requirements
{description}

## Output Requirements
Please return a configuration object that conforms to the following JSON Schema (do not include any other text):
{DASHBOARD_CONFIG_SCHEMA}"""

    # Step 3: Call LLM to generate configuration
    gateway = LLMGateway()
    raw_response = await gateway.generate(prompt)

    # Step 4: Clean and parse JSON
    config_json = extract_json(raw_response)
    config = DashboardConfig(**config_json)

    # Step 5: Persist to database
    dashboard = Dashboard(
        datasource_id=datasource_id,
        name=name or config.title,
        config_json=json.dumps(config, ensure_ascii=False),
    )
    db.add(dashboard)
    await db.flush()
    await db.refresh(dashboard)

    # Step 6: Create Widget records
    for widget_config in config.widgets:
        widget = DashboardWidget(
            dashboard_id=dashboard.id,
            type=widget_config.type,
            x=widget_config.x,
            y=widget_config.y,
            width=widget_config.width,
            height=widget_config.height,
            sql_query=widget_config.sql,  # ← Key: SQL is precompiled!
            chart_config=json.dumps(
                widget_config.chart_options or {},
                ensure_ascii=False
            ),
            title=widget_config.title or "",
            format_type=widget_config.format or "auto",
        )
        db.add(widget)

    return dashboard

1.2 DashboardConfig Data Structure Definition

app/schemas/dashboard.py (schemas/dashboard.py)

from pydantic import BaseModel, Field
from typing import List, Optional, Any


class WidgetConfig(BaseModel):
    """Configuration for a single Widget"""
    type: str = Field(..., description="Component type: kpi_card/line_chart/bar_chart/pie_chart/table")
    x: int = Field(0, description="Grid X coordinate (0-11)")
    y: int = Field(0, description="Grid Y coordinate")
    width: int = Field(6, description="Grid width (1-12)")
    height: int = Field(2, description="Grid height")
    title: Optional[str] = Field(None, description="Title")
    sql: str = Field(..., description="Precompiled SQL query statement")
    format: Optional[str] = Field("auto", description="Formatting method: currency/percent/number/auto")
    chart_options: Optional[dict] = Field(None, description="ECharts custom options")


class LayoutConfig(BaseModel):
    """Grid layout configuration"""
    cols: int = Field(12, description="Number of columns")
    rows: int = Field(8, description="Number of rows")
    gap: int = Field(16, description="Gap (pixels)")


class DashboardConfig(BaseModel):
    """Complete Dashboard configuration"""
    id: Optional[str] = Field(None, description="Unique identifier")
    title: str = Field("Untitled Dashboard", description="Dashboard title")
    layout: LayoutConfig = Field(default_factory=LayoutConfig)
    widgets: List[WidgetConfig] = Field(..., description="List of components")

    class Config:
        json_schema_extra = {
            "example": {
                "title": "Sales Data Analysis Dashboard",
                "layout": {"cols": 12, "rows": 8},
                "widgets": [
                    {
                        "type": "kpi_card",
                        "x": 0, "y": 0,
                        "width": 6, "height": 2,
                        "title": "Today's Sales Amount",
                        "sql": (
                            "SELECT SUM(total_amount) AS value "
                            "FROM orders "
                            "WHERE DATE(created_at) = CURDATE()"
                        ),
                        "format": "currency"
                    },
                    {
                        "type": "line_chart",
                        "x": 6, "y": 0,
                        "width": 6, "height": 2,
                        "title": "7-Day Sales Trend",
                        "sql": (
                            "SELECT DATE(created_at) AS date, "
                            "SUM(total_amount) AS value "
                            "FROM orders "
                            "WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) "
                            "GROUP BY DATE(created_at) "
                            "ORDER BY date"
                        )
                    }
                ]
            }
        }

1.3 Practical Example: From Natural Language to Complete Configuration

User Input:

Create a sales data dashboard for me, including:
• Top left: KPI card showing today's sales amount
• Top right: Line chart showing the sales trend for the last 7 days
• Bottom: Bar chart showing sales amount by category (TOP10)

Configuration Generated by LLM (stored in database):

Each Widget stores a complete SQL query (shown with line breaks for readability)

💡 Key Observations

1. Each Widget has an independent, complete SQL query — this is the prerequisite for operating without LLM at runtime
2. Coordinates use the Grid system (x/y/w/h) — supports drag-and-drop to adjust position and size
3. chart_options is optional — allows advanced users to fine-tune chart styling

---

⚡ II. Phase Two: Runtime — Efficient Data Retrieval and Rendering

2.1 Loading Dashboard Configuration

app/api/dashboards.py (api/dashboards.py)

@router.get("/{dashboard_id}")
async def get_dashboard(dashboard_id: int, db: AsyncSession = Depends(get_db)):
    """
    Load Dashboard configuration (without data)

    Performance: ~10ms (pure database read)
    """
    result = await db.execute(
        select(Dashboard).where(Dashboard.id == dashboard_id)
    )
    dashboard = result.scalar_one_or_none()

    if not dashboard:
        raise HTTPException(status_code=404, detail="Dashboard not found")

    # Load associated Widgets
    widgets_result = await db.execute(
        select(DashboardWidget)
        .where(DashboardWidget.dashboard_id == dashboard_id)
        .order_by(DashboardWidget.y, DashboardWidget.x)
    )
    widgets = list(widgets_result.scalars().all())

    return {
        "id": dashboard.id,
        "name": dashboard.name,
        "config": json.loads(dashboard.config_json),
        "widgets": [
            {
                "id": w.id,
                "type": w.type,
                "position": {"x": w.x, "y": w.y, "width": w.width, "height": w.height},
                "title": w.title,
                "format": w.format_type,
                "chartConfig": json.loads(w.chart_config) if w.chart_config else {},
            }
            for w in widgets
        ],
    }

2.2 Concurrent Querying of All Widget Data

app/api/dashboards.py (api/dashboards.py)

@router.post("/{dashboard_id}/data")
async def get_dashboard_data(
    dashboard_id: int,
    db: AsyncSession = Depends(get_db),
):
    """
    Get real-time data for all Widgets in a Dashboard

    Performance optimizations:
    1. Use asyncio.gather() to execute multiple SQL queries concurrently
    2. No LLM calls (SQL is precompiled at design time)
    3. Result caching (optional, see Chapter 8)

    Total time ≈ max(single SQL time), not sum()
    """
    # Load Widgets
    widgets_result = await db.execute(
        select(DashboardWidget).where(DashboardWidget.dashboard_id == dashboard_id)
    )
    widgets = list(widgets_result.scalars().all())

    if not widgets:
        return {}

    async def fetch_widget_data(widget: DashboardWidget) -> tuple[int, list]:
        """Query data for a single Widget"""
        try:
            data = await execute_query(widget.sql_query, datasource_id=None)
            return widget.id, data
        except Exception as e:
            logger.warning(f"Widget {widget.id} query failed: {e}")
            return widget.id, [{"error": str(e)}]

    # 🔥 Core: execute all Widget SQLs concurrently
    tasks = [fetch_widget_data(w) for w in widgets]
    results = await asyncio.gather(*tasks)

    # Assemble into {widget_id: data} mapping
    return {wid: data for wid, data in results}

🎯 Why Is Concurrency So Important?

Assume there are 5 Widgets, each SQL execution takes 100ms:

Method	Calculation	Total Time
Serial execution	100ms × 5 = 500ms	Slower
Concurrent execution	max(100ms, 100ms, …) = ~100ms	5x faster

2.3 Frontend Rendering Engine

views/dashboard/DashboardView.vue

<template>
  <div class="dashboard-container">
    <!-- Header -->
    <div class="dashboard-header">
      <h1>{{ dashboard.name }}</h1>
      <div class="actions">
        <button @click="refreshData">🔄 Refresh</button>
        <span v-if="lastRefresh">Last updated: {{ lastRefresh }}</span>
      </div>
    </div>

    <!-- Grid Layout Container -->
    <div
      class="grid-container"
      :style="{
        gridTemplateColumns: `repeat(${layout.cols}, 1fr)`,
        gridAutoRows: 'minmax(100px, auto)',
        gap: `${layout.gap}px`,
      }"
    >
      <!-- Dynamic Widget Rendering -->
      <div
        v-for="widget in widgets"
        :key="widget.id"
        class="widget-wrapper"
        :style="{
          gridColumnStart: widget.position.x + 1,
          gridRowStart: widget.position.y + 1,
          gridColumnEnd: `span ${widget.position.width}`,
          gridRowEnd: `span ${widget.position.height}`,
        }"
      >
        <!-- Component mapping table -->
        <component
          :is="getComponentType(widget.type)"
          :title="widget.title"
          :data="widgetData[widget.id]"
          :chart-config="widget.chartConfig"
          :format="widget.format"
          :loading="loadingWidgets.has(widget.id)"
        />
      </div>
    </div>
  </div>
</template>

<script setup lang="ts">
import { ref, onMounted, computed } from 'vue'
import { useRoute } from 'vue-router'
import KpiCard from '@/components/dashboard/KpiCard.vue'
import LineChart from '@/components/dashboard/LineChart.vue'
import BarChart from '@/components/dashboard/BarChart.vue'
import PieChart from '@/components/dashboard/PieChart.vue'
import DataTable from '@/components/dashboard/DataTable.vue'

const route = useRoute()
const dashboardId = computed(() => Number(route.params.id))

// Component type mapping table
const componentMap = {
  kpi_card: KpiCard,
  line_chart: LineChart,
  bar_chart: BarChart,
  pie_chart: PieChart,
  table: DataTable,
}

function getComponentType(type: string) {
  return componentMap[type] || DataTable
}

// State management
const dashboard = ref<any>(null)
const widgets = ref<any[]>([])
const widgetData = ref<Record<number, any>>({})
const loadingWidgets = ref(new Set<number>())
const lastRefresh = ref<string>('')

// Load configuration
onMounted(async () => {
  const res = await fetch(`/api/dashboards/${dashboardId.value}`)
  const data = await res.json()
  dashboard.value = data
  widgets.value = data.widgets

  // Auto-load data
  await refreshData()

  // Set up periodic refresh (default 60 seconds)
  setInterval(refreshData, 60000)
})

// Refresh data (only re-fetch data, not configuration)
async function refreshData() {
  loadingWidgets.value = new Set(widgets.value.map((w: any) => w.id))

  try {
    const res = await fetch(`/api/dashboards/${dashboardId.value}/data`, {
      method: 'POST',
    })
    const data = await res.json()
    widgetData.value = data
    lastRefresh.value = new Date().toLocaleTimeString()
  } finally {
    loadingWidgets.value.clear()
  }
}
</script>

<style scoped>
.grid-container {
  display: grid;
  padding: 16px;
}

.widget-wrapper {
  background: white;
  border-radius: 8px;
  box-shadow: 0 2px 8px rgba(0, 0, 0, 0.08);
  overflow: hidden;
}
</style>

2.4 Auto-Refresh Mechanism

// Periodic refresh strategy
interface RefreshConfig {
  enabled: boolean       // Whether enabled
  interval: number       // Interval (milliseconds), default 60000 (60 seconds)
  pauseOnHidden: boolean // Pause when page is hidden (save resources)
}

// Implementation
let timer: number | null = null

function startAutoRefresh(config: RefreshConfig) {
  if (!config.enabled) return

  const tick = () => {
    // Skip when page is hidden
    if (config.pauseOnHidden && document.hidden) return
    refreshData()
  }

  timer = window.setInterval(tick, config.interval)

  // Listen for page visibility changes
  document.addEventListener('visibilitychange', () => {
    if (document.hidden && timer) {
      clearInterval(timer)
      timer = null
    } else if (!document.hidden && !timer) {
      timer = window.setInterval(tick, config.interval)
    }
  })
}

---

🗄️ III. Data Model Design

3.1 ER Diagram

1:N relationship: One dashboard contains multiple Widgets

3.2 SQLAlchemy Models

app/models/dashboard.py (models/dashboard.py)

from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey
from sqlalchemy.orm import relationship
from datetime import datetime


class Dashboard(Base):
    __tablename__ = "dashboards"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(200), nullable=False, default="Untitled Dashboard")
    datasource_id = Column(Integer, ForeignKey("datasources.id"), nullable=False)
    config_json = Column(Text, nullable=True)  # Complete DashboardConfig JSON
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    widgets = relationship("DashboardWidget", back_populates="dashboard", cascade="all, delete-orphan")


class DashboardWidget(Base):
    __tablename__ = "dashboard_widgets"

    id = Column(Integer, primary_key=True, autoincrement=True)
    dashboard_id = Column(Integer, ForeignKey("dashboards.id"), nullable=False)

    # Component type
    type = Column(String(50), nullable=False)  # kpi_card / line_chart / bar_chart / pie_chart / table

    # Grid coordinate system
    x = Column(Integer, default=0)
    y = Column(Integer, default=0)
    width = Column(Integer, default=6)
    height = Column(Integer, default=2)

    # Content configuration
    sql_query = Column(Text, nullable=False)  # ← Core: precompiled SQL
    chart_config = Column(Text, nullable=True)  # ECharts options JSON
    title = Column(String(200), default="")
    format_type = Column(String(20), default="auto")  # currency / percent / number

    # Relationships
    dashboard = relationship("Dashboard", back_populates="widgets")

---

🔄 IV. Two-Phase Comparison Summary

4.1 Complete Lifecycle

One-time generation at design time vs. unlimited refreshes at runtime

4.2 Performance and Cost Comparison Matrix

Metric	Traditional Approach (LLM every time)	Our Approach (Two-Phase Separation)
Initial Load	3000-5000ms	3000-5000ms (includes LLM)
Refresh Latency	3000-5000ms	150-250ms (pure SQL)
Daily Cost (100 refreshes)	~$0.50	$0.005 (only first time)
Monthly Cost (30 days)	~$15	$0.15
Scalability	Limited by LLM QPS	Only limited by MySQL connection pool
Offline Availability	❌ Requires LLM service online	✅ Configuration already persisted locally

4.3 Use Case Suitability

Scenario	Suitable for Two-Phase Architecture?	Reason
✅ Real-time monitoring dashboards	Highly recommended	High-frequency refreshes, cost-sensitive
✅ Fixed report templates	Recommended	Stable SQL, no frequent adjustments needed
⚠️ Exploratory analysis	Optional	May require frequent configuration changes
❌ Fully dynamic scenarios	Not recommended	Every SQL is different, cannot be precompiled

---

🎯 V. Best Practices and Design Patterns

✅ What We Have Achieved

1. True Decoupling: UI rendering completely separated from business logic
2. Declarative Programming: Describe the interface through JSON, not imperative code
3. Extreme Performance Optimization: Zero AI cost at runtime, response speed increased by 15x+
4. Hot Update Capability: Modify database configuration to take effect in real-time
5. Frontend Componentization: Flexible Widget system based on Vue dynamic components

📚 Best Practice Checklist

• Use Pydantic to define strict Config Schema (ensure correct LLM output format)
• SQL for each Widget must be independent and complete (cannot depend on results from other Widgets)
• Use asyncio.gather() for concurrent queries (instead of serial for loop)
• Frontend uses CSS Grid or absolute positioning for flexible layouts
• Pause periodic refresh when page is hidden (save resources)
• Error isolation: failure of a single Widget query should not affect overall display
• Configuration version control: record timestamp and operator for each modification

🚀 Advanced Optimization Directions

1. Incremental Updates: Only refresh changed Widgets (via SQL hash comparison)
2. WebSocket Push: Replace polling for true real-time updates
3. SQL Cache Layer: For aggregate queries, cache results within TTL
4. Multiple Data Source Support: A single dashboard can combine data from different databases

---

Related Code Files:

• app/services/dashboard_service.py (dashboard_service.py) — Core dashboard service logic
• app/models/dashboard.py (models/dashboard.py) — Data model definitions
• app/schemas/dashboard.py (schemas/dashboard.py) — Pydantic configuration schema
• app/api/dashboards.py (api/dashboards.py) — RESTful API endpoints
• DashboardView.vue — Frontend dashboard view
• components/dashboard/*.vue — Widget component library

The next article will dive into the implementation details of the frontend drag-and-drop interaction system — HTML5 Drag API, Grid layout, mousedown move/resize (collision detection as planned capability)!

Stay tuned! 🚀