pipeline-analytics

# Pipeline Analytics — NL → SQL → Interactive Charts

Transform natural language questions into DuckDB queries and render results as interactive Recharts dashboards inline in chat.

## Workflow

```
User asks question in plain English
→ Translate to DuckDB SQL against workspace pivot views (v_*)
→ Execute query
→ Format results as report-json
→ Render as interactive Recharts components
```

## DuckDB Query Patterns

### Discovery — What objects exist?
```sql
-- List all objects and their entry counts
SELECT o.name, o.display_name, COUNT(e.id) as entries
FROM objects o
LEFT JOIN entries e ON e.object_id = o.id
GROUP BY o.name, o.display_name
ORDER BY entries DESC;

-- List fields for an object
SELECT f.name, f.field_type, f.display_name
FROM fields f
JOIN objects o ON f.object_id = o.id
WHERE o.name = 'leads'
ORDER BY f.position;

-- Available pivot views
SELECT table_name FROM information_schema.tables
WHERE table_name LIKE 'v_%';
```

### Common Analytics Queries

#### Pipeline Funnel
```sql
SELECT "Status", COUNT(*) as count
FROM v_leads
GROUP BY "Status"
ORDER BY CASE "Status"
  WHEN 'New' THEN 1
  WHEN 'Contacted' THEN 2
  WHEN 'Qualified' THEN 3
  WHEN 'Demo Scheduled' THEN 4
  WHEN 'Proposal' THEN 5
  WHEN 'Closed Won' THEN 6
  WHEN 'Closed Lost' THEN 7
  ELSE 99
END;
```

#### Outreach Activity Over Time
```sql
SELECT DATE_TRUNC('week', "Last Outreach"::DATE) as week,
       "Outreach Channel",
       COUNT(*) as messages_sent
FROM v_leads
WHERE "Last Outreach" IS NOT NULL
GROUP BY week, "Outreach Channel"
ORDER BY week;
```

#### Conversion Rates by Source
```sql
SELECT "Source",
       COUNT(*) as total,
       COUNT(*) FILTER (WHERE "Status" = 'Qualified') as qualified,
       COUNT(*) FILTER (WHERE "Status" IN ('Closed Won', 'Converted')) as converted,
       ROUND(100.0 * COUNT(*) FILTER (WHERE "Status" = 'Qualified') / COUNT(*), 1) as qual_rate,
       ROUND(100.0 * COUNT(*) FILTER (WHERE "Status" IN ('Closed Won', 'Converted')) / COUNT(*), 1) as conv_r...