wulf-pulse/tasks/prd-data-chatbot.md

# PRD: Data Chatbot & Query Interface

## Introduction/Overview

The Data Chatbot is an intelligent query interface that allows internal users to quickly access and analyze synced Autotask data through natural language conversations. Users can ask questions like "Who had the most time entries last week?" or "What customer had the most tickets?" and receive accurate, formatted responses with visualizations and export options.

**Problem it solves:** Currently, accessing specific insights from synced data requires writing SQL queries or navigating through multiple database views. This creates a barrier for non-technical users and slows down decision-making. The chatbot democratizes data access by allowing anyone to query the database using plain English.

**Goal:** Provide a conversational, AI-powered interface that makes synced Autotask data instantly accessible to all internal team members, regardless of technical skill level.

## Goals

1. **Accessibility**: Enable non-technical users to query complex database relationships using natural language
2. **Speed**: Reduce time-to-insight from minutes (manual queries) to seconds (conversational interface)
3. **Accuracy**: Deliver correct results with 95%+ accuracy for common query patterns
4. **Flexibility**: Support both AI-powered natural language and structured query templates
5. **Mobile-First**: Provide PWA experience for on-the-go data access
6. **Actionable Insights**: Present data in multiple formats (tables, charts, exports) for immediate use

## User Stories

### Primary User Stories

1. **As a manager**, I want to ask "Who had the most time entries last week?" so that I can quickly identify top performers without running SQL queries.

2. **As a support lead**, I want to ask "What customer had the most tickets this month?" so that I can proactively reach out to high-volume clients.

3. **As a project manager**, I want to ask "Show me all open tickets for Acme Corp assigned to John" so that I can check project status during client calls.

4. **As an executive**, I want to ask "What's our average ticket resolution time by priority?" so that I can track KPIs without waiting for reports.

5. **As a technician on mobile**, I want to quickly check "My open tickets" while in the field so that I can prioritize my work.

6. **As a billing coordinator**, I want to ask "Show me unbilled time entries from last month" so that I can prepare invoices.

### Secondary User Stories

7. **As a data analyst**, I want to export query results to CSV so that I can perform additional analysis in Excel.

8. **As a team lead**, I want to save frequently-used queries so that I can access them quickly without retyping.

9. **As a user**, I want to see my query history so that I can reference previous insights.

10. **As a user**, I want to choose between AI-powered queries and template-based queries so that I can balance cost and flexibility.

## Functional Requirements

### Core Query Engine

1. The system **must** accept natural language queries in a conversational chat interface.
2. The system **must** support querying all synced entities: tickets, tasks, projects, companies, resources, contacts, contracts, time entries, billing items, configuration items, and their relationships.
3. The system **must** support multi-entity joins (e.g., "tickets with their assigned resources and companies").
4. The system **must** execute queries against the live PostgreSQL database.
5. The system **must** return results within 5 seconds for 95% of queries.
6. The system **must** handle common query patterns:
   - Aggregations (count, sum, average, min, max)
   - Filtering (by date range, status, assignment, company, etc.)
   - Sorting (top N, bottom N, ordered by field)
   - Grouping (by company, resource, status, etc.)
   - Time-based queries (last week, this month, last 30 days, etc.)

### AI/LLM Integration

7. The system **must** allow users to choose between two query modes:
   - **AI Mode**: Uses LLM (OpenAI GPT-4 or Claude) for natural language understanding
   - **Template Mode**: Uses predefined query patterns (faster, no API cost)
8. The system **must** convert natural language to SQL queries safely (prevent SQL injection).
9. The system **must** validate generated SQL before execution.
10. The system **must** provide query explanations (e.g., "I'm searching for tickets created in the last 7 days...").

### User Interface - Desktop

11. The system **must** provide a dedicated page at `/data-chat` or similar route.
12. The system **must** display a chat interface with:
    - Message history (user queries and bot responses)
    - Input field for typing queries
    - Send button and Enter key support
    - Mode toggle (AI vs Template)
13. The system **must** display results in multiple formats:
    - **Table view**: Sortable, paginated data tables
    - **Card view**: Visual cards for entity records
    - **Chart view**: Bar charts, line charts, pie charts for aggregated data
14. The system **must** provide export options:
    - CSV download
    - JSON download
    - Copy to clipboard
15. The system **must** show loading states during query execution.
16. The system **must** display error messages clearly when queries fail.

### User Interface - Mobile (PWA)

17. The system **must** be responsive and optimized for mobile devices.
18. The system **must** function as a Progressive Web App (PWA):
    - Installable to home screen
    - Works offline for query history (results require connection)
    - Fast loading with service worker caching
19. The system **must** provide a mobile-optimized chat interface:
    - Full-screen chat on mobile
    - Touch-friendly buttons and inputs
    - Swipeable result cards
20. The system **must** support voice input on mobile devices (optional but recommended).

### Query Management

21. The system **must** maintain query history for each user session.
22. The system **must** allow users to save favorite queries with custom names.
23. The system **must** provide quick-access buttons for common queries:
    - "My open tickets"
    - "Team time entries this week"
    - "Top 10 customers by ticket volume"
    - "Overdue tickets"
24. The system **must** allow users to edit and re-run previous queries.

### Data Visualization

25. The system **must** automatically suggest appropriate chart types based on query results:
    - Bar charts for comparisons (e.g., tickets by company)
    - Line charts for time series (e.g., tickets over time)
    - Pie charts for distributions (e.g., tickets by status)
26. The system **must** allow users to toggle between table and chart views.
27. The system **must** make charts interactive (hover for details, click to filter).

### Security & Permissions

28. The system **must** require authentication (use existing auth system).
29. The system **must** respect user permissions (if implemented in the future).
30. The system **must** log all queries for audit purposes.
31. The system **must** prevent SQL injection and malicious queries.
32. The system **must** rate-limit queries to prevent abuse (e.g., 60 queries per minute per user).

### Performance & Caching

33. The system **should** cache common query results for 5 minutes.
34. The system **should** implement query result pagination for large datasets (>1000 rows).
35. The system **should** provide query performance metrics (execution time).

## Non-Goals (Out of Scope)

1. **Data Modification**: The chatbot will NOT allow users to insert, update, or delete data. It is read-only.
2. **Real-time Streaming**: The chatbot will NOT provide real-time updates or websocket-based live data feeds.
3. **Advanced Analytics**: Complex statistical analysis, machine learning predictions, or forecasting are out of scope.
4. **External Data Sources**: The chatbot will only query synced Autotask data, not external APIs or services.
5. **Multi-tenant Isolation**: Initial version assumes single organization; multi-tenant support is future work.
6. **Custom Dashboards**: Building and saving custom dashboard layouts is out of scope (separate feature).
7. **Scheduled Reports**: Automated report generation and email delivery is out of scope.
8. **Data Governance**: Advanced role-based access control (RBAC) at the field level is out of scope for v1.

## Design Considerations

### UI/UX Guidelines

- **Chat Interface**: Follow modern chat UI patterns (similar to ChatGPT, Claude, or Slack)
  - User messages: Right-aligned, blue background
  - Bot responses: Left-aligned, gray background
  - Timestamps on messages
  - Typing indicator while processing
  
- **Component Library**: Use existing shadcn/ui components
  - `Card` for message bubbles
  - `Table` for data tables
  - `Button` for actions
  - `Select` for mode toggle
  - `Tabs` for view switching (table/chart)
  
- **Icons**: Use Lucide React icons
  - `MessageSquare` for chat
  - `BarChart3` for charts
  - `Download` for exports
  - `History` for query history
  - `Sparkles` for AI mode
  - `List` for template mode

- **Color Scheme**: Follow existing app theme
  - Primary: Blue for AI mode
  - Secondary: Gray for template mode
  - Success: Green for successful queries
  - Error: Red for failed queries

### Mobile PWA Requirements

- **Manifest File**: Create `manifest.json` with app metadata
- **Service Worker**: Implement for offline query history
- **Responsive Breakpoints**:
  - Mobile: < 768px (single column, full-screen chat)
  - Tablet: 768px - 1024px (sidebar + chat)
  - Desktop: > 1024px (full layout with panels)

### Example Queries to Support

```
Natural Language Examples:
- "Who had the most time entries last week?"
- "What customer had the most tickets this month?"
- "Show me all open tickets for Acme Corp"
- "What's the average ticket resolution time?"
- "List tickets assigned to John Doe that are overdue"
- "How many projects are currently active?"
- "Show me time entries for Project X in October"
- "Which resources have the highest billable hours?"
- "What are the top 5 issues by ticket count?"
- "Show me all high priority tickets created yesterday"
```

## Technical Considerations

### Architecture

1. **Frontend**: Next.js 14+ with App Router
   - New route: `/app/data-chat/page.tsx`
   - Components: `/components/data-chat/`
   - PWA config: `/public/manifest.json`, service worker

2. **Backend API**: Next.js API routes
   - `/api/data-chat/query` - Execute queries
   - `/api/data-chat/history` - Get/save query history
   - `/api/data-chat/templates` - Get predefined query templates
   - `/api/data-chat/export` - Export results

3. **Database**: PostgreSQL (existing)
   - Read-only queries via connection pool
   - New table: `query_history` for storing user queries
   - New table: `saved_queries` for favorite queries

4. **LLM Integration**:
   - **Option 1**: OpenAI GPT-4 API (more accurate, costs ~$0.01-0.03 per query)
   - **Option 2**: Anthropic Claude API (alternative, similar cost)
   - **Fallback**: Template-based queries (free, predefined patterns)

5. **Query Generation**:
   - Use LLM to generate SQL from natural language
   - Implement SQL sanitization and validation
   - Use parameterized queries to prevent injection
   - Whitelist allowed tables and columns

6. **Caching**: Redis or in-memory cache for frequent queries

### Dependencies

```json
{
  "openai": "^4.0.0",           // For AI mode
  "@anthropic-ai/sdk": "^0.9.0", // Alternative AI provider
  "recharts": "^2.10.0",         // For charts
  "react-chartjs-2": "^5.2.0",   // Alternative charting
  "papaparse": "^5.4.0",         // CSV export
  "sql-formatter": "^15.0.0",    // SQL formatting for display
  "zod": "^3.22.0"               // Query validation
}
```

### Database Schema

```sql
-- Query history table
CREATE TABLE query_history (
  id SERIAL PRIMARY KEY,
  user_id VARCHAR(255) NOT NULL,
  query_text TEXT NOT NULL,
  query_mode VARCHAR(20) NOT NULL, -- 'ai' or 'template'
  generated_sql TEXT,
  result_count INTEGER,
  execution_time_ms INTEGER,
  success BOOLEAN DEFAULT true,
  error_message TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Saved queries table
CREATE TABLE saved_queries (
  id SERIAL PRIMARY KEY,
  user_id VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  query_text TEXT NOT NULL,
  query_mode VARCHAR(20) NOT NULL,
  is_favorite BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes
CREATE INDEX idx_query_history_user_id ON query_history(user_id);
CREATE INDEX idx_query_history_created_at ON query_history(created_at);
CREATE INDEX idx_saved_queries_user_id ON saved_queries(user_id);
```

### Security Considerations

1. **SQL Injection Prevention**:
   - Use parameterized queries exclusively
   - Validate and sanitize all LLM-generated SQL
   - Whitelist allowed tables and columns
   - Block dangerous SQL keywords (DROP, DELETE, UPDATE, INSERT, ALTER, etc.)

2. **Rate Limiting**:
   - Implement per-user rate limiting (60 queries/minute)
   - Implement per-IP rate limiting for API endpoints
   - Consider cost controls for AI mode (e.g., 100 AI queries per user per day)

3. **Authentication**:
   - Reuse existing NextAuth.js setup
   - Require authenticated session for all data-chat routes

4. **Query Validation**:
   - Parse generated SQL with SQL parser library
   - Verify only SELECT statements are executed
   - Ensure queries only access allowed tables
   - Limit result set size (max 10,000 rows)

### Performance Optimization

1. **Query Optimization**:
   - Add database indexes for common query patterns
   - Implement query result pagination
   - Set query timeout (30 seconds max)

2. **Caching Strategy**:
   - Cache common queries for 5 minutes
   - Cache template query results for 10 minutes
   - Invalidate cache on data sync completion

3. **Frontend Optimization**:
   - Lazy load chart libraries
   - Virtual scrolling for large result tables
   - Progressive loading for query history

## Success Metrics

### Primary Metrics

1. **Query Accuracy**: 95%+ of queries return correct results (measured by user feedback)
2. **Response Time**: 95% of queries complete within 5 seconds
3. **Adoption Rate**: 70%+ of internal users try the chatbot within first month
4. **Engagement**: Average 10+ queries per active user per week

### Secondary Metrics

5. **AI vs Template Usage**: Track ratio to optimize cost vs. accuracy
6. **Query Success Rate**: 90%+ of queries execute without errors
7. **Export Usage**: Track how often users export results (indicates value)
8. **Mobile Usage**: 30%+ of queries come from mobile devices
9. **Saved Queries**: Average 3+ saved queries per active user
10. **User Satisfaction**: 4.5+ star rating in feedback surveys

### Monitoring

- Log all queries with execution time and success/failure
- Track LLM API costs and usage patterns
- Monitor database query performance
- Collect user feedback via in-app rating system
- Track error rates and common failure patterns

## Open Questions

1. **LLM Provider**: Should we start with OpenAI GPT-4, Claude, or both? (Recommend: Start with OpenAI, add Claude as fallback)

2. **Cost Management**: What's the acceptable monthly budget for LLM API calls? (Estimate: $100-500/month for 10-20 active users)

3. **User Authentication**: Should we use existing NextAuth.js setup or implement separate auth? (Recommend: Use existing auth)

4. **Query Templates**: What are the top 20 most common queries we should pre-build? (Needs input from team)

5. **Data Freshness**: Should we show when data was last synced? (Recommend: Yes, display "Data as of [timestamp]")

6. **Error Handling**: How should we handle ambiguous queries? (Recommend: Ask clarifying questions or suggest alternatives)

7. **Multi-language Support**: Do we need to support languages other than English? (Defer to v2)

8. **Voice Input**: Is voice input a must-have for mobile or nice-to-have? (Recommend: Nice-to-have for v1)

9. **Collaboration**: Should users be able to share queries with team members? (Defer to v2)

10. **Notifications**: Should users get notified when saved queries have new results? (Defer to v2)

## Implementation Phases

### Phase 1: MVP (2-3 weeks)
- Basic chat interface (desktop only)
- AI mode with OpenAI GPT-4
- Table view for results
- Query history
- Basic error handling
- CSV export

### Phase 2: Enhanced Features (2 weeks)
- Template mode with predefined queries
- Chart visualizations
- Saved queries
- Mobile responsive design
- Query explanations

### Phase 3: PWA & Polish (1-2 weeks)
- PWA implementation
- Mobile optimization
- Performance optimization
- Caching layer
- Advanced error handling
- User feedback system

### Phase 4: Advanced Features (Future)
- Voice input
- Query sharing
- Scheduled queries
- Advanced visualizations
- Multi-language support
- Role-based permissions

## Appendix: Example Query Templates

```typescript
// Common query templates for Template Mode
const queryTemplates = [
  {
    name: "My Open Tickets",
    description: "Show all tickets assigned to me that are not completed",
    sql: "SELECT * FROM tickets WHERE assigned_resource_id = $userId AND status != 5 AND is_deleted = false"
  },
  {
    name: "Top Customers by Ticket Volume",
    description: "Show customers with most tickets this month",
    sql: `SELECT c.name, COUNT(t.id) as ticket_count 
          FROM companies c 
          JOIN tickets t ON c.id = t.company_id 
          WHERE t.create_date >= date_trunc('month', CURRENT_DATE) 
          GROUP BY c.id, c.name 
          ORDER BY ticket_count DESC 
          LIMIT 10`
  },
  {
    name: "Time Entries This Week",
    description: "Show all time entries for current week",
    sql: `SELECT r.first_name, r.last_name, SUM(te.hours_worked) as total_hours
          FROM time_entries te
          JOIN resources r ON te.resource_id = r.id
          WHERE te.date_worked >= date_trunc('week', CURRENT_DATE)
          GROUP BY r.id, r.first_name, r.last_name
          ORDER BY total_hours DESC`
  },
  // Add 17 more common templates...
];
```

---

**Document Version**: 1.0  
**Created**: 2024-11-03  
**Last Updated**: 2024-11-03  
**Status**: Draft - Awaiting Approval