[HIGH] Admin UI/Dashboard for Database and Query Management

[Sakeeb91]

🔴 Priority: HIGH | Type: Feature

1. SUMMARY

• No web-based admin interface exists for managing databases, reviewing queries, monitoring system health, or managing feedback.
• Impact: Administrators must use CLI/API for all operations. No visual dashboards for monitoring. Poor discoverability of system state and issues.

2. SYSTEM CONTEXT

Current state: CLI/API only
─────────────────────────────
Admin → curl/httpie → API endpoints → Response JSON
Admin → Grafana (separate) → Metrics only

Desired state: Integrated Admin UI
─────────────────────────────────────
Admin → Web Dashboard →
  ├── Database Management (register, health, schema viewer)
  ├── Query Explorer (history, search, replay)
  ├── Feedback Queue (review, verify, apply)
  ├── Monitoring (health, metrics, alerts)
  └── Configuration (settings, users, API keys)

Existing infrastructure:
├── FastAPI (can serve static files)
├── Prometheus metrics (available at /monitoring/metrics)
├── Health endpoints (/api/v1/health, /monitoring/health)
└── OpenAPI spec (auto-generated)

3. CURRENT STATE (with code)

📄 File: app/main.py

app = FastAPI(
    title="Text2SQL Agent",
    docs_url="/docs",  # Swagger UI exists
    redoc_url="/redoc",  # ReDoc exists
)
# No admin UI mounted

Only auto-generated API docs exist.

📄 File: app/routes_databases.py

@router.get("/databases")
async def list_databases(...):
    # Returns JSON, no UI

Database management is API-only.

📄 File: monitoring/grafana/dashboard.json

// Grafana dashboard exists but requires separate Grafana instance

Monitoring requires external tools.

4. PROPOSED SOLUTION

Create an embedded admin dashboard using a modern frontend framework:

1. React/Vue SPA served by FastAPI
2. Real-time updates via WebSocket
3. Role-based access control
4. Mobile-responsive design

📄 File: admin/ directory structure (NEW)

admin/
├── frontend/                 # React/Vue app
│   ├── src/
│   │   ├── pages/
│   │   │   ├── Dashboard.tsx       # Overview with key metrics
│   │   │   ├── Databases.tsx       # Database management
│   │   │   ├── QueryExplorer.tsx   # Query history and search
│   │   │   ├── FeedbackQueue.tsx   # Feedback review
│   │   │   ├── Monitoring.tsx      # Health and metrics
│   │   │   └── Settings.tsx        # Configuration
│   │   ├── components/
│   │   │   ├── SchemaViewer.tsx    # Interactive schema browser
│   │   │   ├── QueryPlayground.tsx # Test queries interactively
│   │   │   ├── MetricsChart.tsx    # Embedded metrics charts
│   │   │   └── AlertsPanel.tsx     # Active alerts display
│   │   └── hooks/
│   │       ├── useWebSocket.ts     # Real-time updates
│   │       └── useAuth.ts          # Admin authentication
│   └── package.json
├── api/
│   └── admin_routes.py       # Admin-specific endpoints
└── static/                   # Built frontend (served by FastAPI)

📄 File: app/main.py (ENHANCED)

from fastapi.staticfiles import StaticFiles

# Mount admin UI
app.mount("/admin", StaticFiles(directory="admin/static", html=True), name="admin")

# Admin API routes
app.include_router(admin_router, prefix="/api/admin", tags=["admin"])

5. IMPLEMENTATION CHECKLIST

Phase 1: Core Infrastructure

• Set up React/Vite frontend project in admin/frontend/
• Configure FastAPI to serve static files
• Implement admin authentication (separate from API auth)
• Create base layout with navigation

Phase 2: Dashboard Overview

• Key metrics cards (queries/day, success rate, avg latency)
• Recent activity feed
• System health status
• Quick actions (test query, view logs)

Phase 3: Database Management

• List registered databases with health status
• Register new database form with connection test
• Interactive schema browser (tables, columns, relationships)
• Database health history graph

Phase 4: Query Explorer

• Query history with search and filters
• Query detail view (SQL, reasoning trace, results)
• Replay query functionality
• Export queries to CSV/JSON

Phase 5: Feedback Management

• Pending feedback queue
• Review interface with side-by-side comparison
• Bulk verify/reject actions
• Feedback analytics (common corrections)

Phase 6: Monitoring Integration

• Embedded metrics charts (without Grafana)
• Active alerts display
• Log viewer (structured logs)
• Performance trends

Phase 7: Settings & Configuration

• API key management
• User management (if multi-user)
• Feature flags
• Cache management (clear, stats)

6. FILES TO MODIFY TABLE

File	Lines	Action	Description
admin/frontend/	NEW	Create	React frontend application
admin/api/admin_routes.py	NEW	Create	Admin-specific API endpoints
app/main.py	TBD	Modify	Mount admin static files and routes
app/security/auth.py	TBD	Modify	Add admin role/scope
requirements.txt	TBD	Modify	Add any backend dependencies
Dockerfile	TBD	Modify	Include frontend build step
.github/workflows/ci.yml	TBD	Modify	Add frontend build/test job
docs/ADMIN_GUIDE.md	NEW	Create	Admin UI documentation

7. RISK ASSESSMENT

Risk	Impact	Mitigation
Frontend adds complexity	🟡	Use simple stack (React + Vite); minimal dependencies
Security exposure	🔴	Separate admin auth; IP whitelisting; audit logs
Build time increase	🟡	Cache node_modules; parallel builds
Maintenance burden	🟡	Keep UI simple; use component library (shadcn/ui)

8. RELATED CONTEXT

• Existing Grafana dashboard: monitoring/grafana/dashboard.json
• Health endpoints: app/monitoring/endpoints.py
• OpenAPI spec: Auto-generated at /docs
• Related issue: [HIGH] Query Feedback Loop - Learn from User Corrections #45 (Feedback Loop - needs admin UI for review queue)
• Similar projects: Metabase, Apache Superset (for inspiration)

9. DESIGN MOCKUPS

Dashboard Overview:

┌─────────────────────────────────────────────────────────────┐
│  Text2SQL Admin                    [Health: ✅] [User ▼]   │
├──────────┬──────────────────────────────────────────────────┤
│          │  Dashboard                                       │
│ Dashboard│  ┌──────────┐ ┌──────────┐ ┌──────────┐         │
│ Databases│  │ 1,234    │ │ 94.2%    │ │ 156ms    │         │
│ Queries  │  │ Queries  │ │ Success  │ │ Avg Lat  │         │
│ Feedback │  └──────────┘ └──────────┘ └──────────┘         │
│ Monitor  │                                                  │
│ Settings │  Recent Activity                                 │
│          │  ┌─────────────────────────────────────────────┐ │
│          │  │ 2m ago  Query: "Show sales..." → Success    │ │
│          │  │ 5m ago  DB: analytics registered            │ │
│          │  │ 8m ago  Feedback: #123 verified             │ │
│          │  └─────────────────────────────────────────────┘ │
└──────────┴──────────────────────────────────────────────────┘

Query Playground:

┌─────────────────────────────────────────────────────────────┐
│  Query Playground                                           │
├─────────────────────────────────────────────────────────────┤
│  Database: [analytics ▼]                                    │
│  ┌─────────────────────────────────────────────────────────┐│
│  │ Show me top 10 customers by revenue                     ││
│  └─────────────────────────────────────────────────────────┘│
│  [Execute] [Show Reasoning]                                 │
│                                                             │
│  Generated SQL:                          Confidence: 92%    │
│  ┌─────────────────────────────────────────────────────────┐│
│  │ SELECT c.name, SUM(o.total) as revenue                  ││
│  │ FROM customers c JOIN orders o ON c.id = o.customer_id  ││
│  │ GROUP BY c.id ORDER BY revenue DESC LIMIT 10            ││
│  └─────────────────────────────────────────────────────────┘│
│                                                             │
│  Results: 10 rows                                           │
│  ┌─────────┬───────────┐                                    │
│  │ name    │ revenue   │                                    │
│  ├─────────┼───────────┤                                    │
│  │ Acme    │ $125,000  │                                    │
│  │ ...     │ ...       │                                    │
│  └─────────┴───────────┘                                    │
└─────────────────────────────────────────────────────────────┘