# sql-slow v2 — Ultra Pro Slow Query Detector

> **Zero-config slow-query monitoring for Node.js** with AI-powered optimization, multi-channel alerts (Telegram · Slack · Discord · Webhook), and a real-time dark dashboard.

---

## Table of Contents

1. [Why sql-slow?](#why-sql-slow)
2. [Quick Start](#quick-start)
3. [Installation](#installation)
4. [Configuration Reference](#configuration-reference)
5. [Database Adapters](#database-adapters)
6. [AI Optimization](#ai-optimization)
7. [Alert Channels](#alert-channels)
8. [Real-Time Dashboard](#real-time-dashboard)
9. [Events API](#events-api)
10. [Manual Ingestion](#manual-ingestion)
11. [Architecture](#architecture)
12. [Pricing](#pricing)
13. [License](#license)

---

## Why sql-slow?

Even a single un-indexed query on a table with millions of rows can:

- Spike CPU on your database server
- Block all other queries from executing
- Take down Postgres / MySQL for minutes
- Cause customer-facing downtime and revenue loss

Traditional approaches require you to grep logs manually, set up slow-query log on the DB server, and write your own alerting. **sql-slow** intercepts every query at the ORM/driver level, detects the slow ones in real time, calls an AI to suggest a fix, and sends you an alert in seconds.

---

## Quick Start

```typescript
import { PrismaClient } from '@prisma/client';
import SqlSlow from 'sql-slow';

// Prisma must be created with query logging enabled
const prisma = new PrismaClient({
  log: [{ level: 'query', emit: 'event' }],
});

// One-liner: create + start
const sq = SqlSlow.create(prisma, {
  alert:    '>500ms',           // fire alert when query > 500 ms
  fix:      'ai',               // auto-detect AI provider from env vars
  appName:  'My API',

  telegram: {
    botToken: process.env.TELEGRAM_BOT_TOKEN!,
    chatId:   process.env.TELEGRAM_CHAT_ID!,
  },

  dashboard: true,              // http://localhost:4001
});

// Your app runs normally – all Prisma queries are monitored automatically
const users = await prisma.user.findMany({ where: { active: true } });
// If this takes > 500 ms you'll get a Telegram message with an AI fix suggestion
```

---

## Installation

```bash
npm install sql-slow
```

Set environment variables (copy `.env.example`):

```bash
# Required for AI suggestions (pick one)
OPENAI_API_KEY=sk-...
# ANTHROPIC_API_KEY=sk-ant-...
# GEMINI_API_KEY=...

# Required for Telegram alerts
TELEGRAM_BOT_TOKEN=123456:ABC...
TELEGRAM_CHAT_ID=-100123456789

# Optional
SLACK_WEBHOOK_URL=https://hooks.slack.com/services/...
DISCORD_WEBHOOK_URL=https://discord.com/api/webhooks/...
SQL_SLOW_DASHBOARD_PORT=4001
SQL_SLOW_DASHBOARD_SECRET=my_secret
```

---

## Configuration Reference

```typescript
const sq = SqlSlow.create(prisma, {
  // ── Threshold ──────────────────────────────────────────────────
  alert: '>500ms',   // Supports: ">500ms" | ">=1s" | ">2000" (no unit = ms)

  // ── AI ─────────────────────────────────────────────────────────
  fix: 'ai',         // 'ai' = auto-detect provider from env vars
  // OR full control:
  fix: {
    provider:              'openai',       // 'openai' | 'anthropic' | 'gemini'
    apiKey:                'sk-...',
    model:                 'gpt-4o-mini',
    maxTokens:             1500,
    temperature:           0.3,
    timeoutMs:             30_000,
    includeQueryInPrompt:  true,           // set false to mask literals
  },

  // ── Alerts ─────────────────────────────────────────────────────
  consoleAlerts: true,                    // always print to stdout

  telegram: {
    botToken:       'BOT_TOKEN',
    chatId:         'CHAT_ID',
    messageThreadId: 123,                 // optional – Topics
    parseMode:      'HTML',               // 'HTML' | 'Markdown'
  },

  slack: {
    webhookUrl:  'https://hooks.slack.com/...',
    channel:     '#db-alerts',
    username:    'sql-slow',
    iconEmoji:   ':snail:',
  },

  discord: {
    webhookUrl: 'https://discord.com/api/webhooks/...',
    username:   'sql-slow',
  },

  webhook: {
    url:     'https://your-api.com/alerts',
    method:  'POST',
    headers: { 'X-Api-Key': 'secret' },
    secret:  'bearer-token',
  },

  // ── Dashboard ──────────────────────────────────────────────────
  dashboard: {
    enabled:          true,
    port:             4001,
    host:             '0.0.0.0',
    secret:           'my-secret',       // bearer token to protect API
    maxStoredQueries: 5000,
  },

  // ── Advanced ───────────────────────────────────────────────────
  adapter:          'auto',   // 'auto' | 'prisma' | 'pg' | 'mysql2'
  sampleRate:       1.0,      // 0.0–1.0 fraction of queries to analyse
  maxConcurrentAI:  3,        // max parallel AI requests
  dailyReportHour:  8,        // send daily report at 08:00 UTC (-1 = off)
  logLevel:         'info',   // 'debug' | 'info' | 'warn' | 'error' | 'silent'
  appName:          'My API',
});
```

---

## Database Adapters

### Prisma

```typescript
// ⚠️  MUST enable query event emission
const prisma = new PrismaClient({
  log: [{ level: 'query', emit: 'event' }],
});

SqlSlow.create(prisma, { alert: '>500ms' });
```

### node-postgres (`pg`)

```typescript
import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

SqlSlow.create(pool, { alert: '>500ms' });
```

### mysql2

```typescript
import mysql from 'mysql2';
const pool = mysql.createPool({ host: 'localhost', database: 'mydb' });

SqlSlow.create(pool, { alert: '>500ms' });
```

### Manual adapter (any ORM)

```typescript
const sq = new SqlSlow(null, { alert: '>300ms' });
sq.start();

// Call sq.ingest() around your queries
const start = Date.now();
const result = await myDb.query('SELECT ...');
sq.ingest('SELECT ...', Date.now() - start);
```

---

## AI Optimization

When a slow query is detected sql-slow calls your configured AI provider and returns:

| Field | Description |
|-------|-------------|
| `issue` | Short root-cause label |
| `suggestion` | Human-readable explanation |
| `code` | Optimized SQL or migration DDL |
| `expectedImprovement` | Narrative estimate |
| `estimatedGainPct` | 0-100 percentage estimate |
| `indexRecommendations` | Array of `CREATE INDEX` DDL statements |
| `queryRewrites` | Alternative SQL rewrites |
| `severity` | `critical` / `high` / `medium` / `low` |

Results are **LRU-cached** (1 hour TTL, 500 entries) so structurally identical queries only call the AI once.

```typescript
// One-off analysis without monitoring
const result = await sq.analyzeQuery(
  'SELECT * FROM orders WHERE user_id = 1',
  1200  // ms
);
console.log(result?.suggestion);
```

---

## Alert Channels

### Telegram alert example

```
🟡 Slow Query Detected

App: My API
Duration: 1.23s  (threshold: 500ms)
Table: orders
Operation: SELECT
Severity: WARNING
Time: 16/06/2026 14:22:05 UTC

SQL:
SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC

💡 AI Suggestion
Issue: Missing composite index on orders(user_id, created_at)
Fix: Add a composite index to cover the WHERE + ORDER BY.
Expected gain: 70-80% reduction in execution time

📌 Index Recommendations:
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);
```

### Test all channels

```typescript
const results = await sq.testAlerts();
// { telegram: true, slack: false, discord: true }
```

---

## Real-Time Dashboard

Enable with `dashboard: true` (or `dashboard: { port: 4001 }`).

Visit **http://localhost:4001** to see:

- **KPI cards** – Total / Slow / Critical queries, Avg duration
- **Percentiles** – P50 · P75 · P90 · P95 · P99 · Max
- **Operation chart** – SELECT / INSERT / UPDATE / DELETE breakdown (Doughnut)
- **Top tables** – Most-hit tables (Bar chart)
- **Slowest queries table** – Live, filterable, click-to-copy SQL
- **Auto-refresh** every 4 seconds

Protect the dashboard with a bearer token:

```typescript
dashboard: { enabled: true, secret: 'my-secret' }
```

Then access `/api/stats` with `Authorization: Bearer my-secret`.

---

## Events API

```typescript
const sq = new SqlSlow(prisma, { alert: '>200ms' });

// Every query
sq.on('query', (metrics) => {
  console.log(metrics.duration, metrics.table);
});

// Only slow queries
sq.on('slow-query', ({ metrics, thresholdMs }) => {
  console.log(`${metrics.duration}ms > ${thresholdMs}ms`);
});

// Critical (≥3× threshold)
sq.on('critical-query', ({ metrics }) => {
  pagerDuty.trigger(metrics);
});

sq.start();
```

---

## Manual Ingestion

```typescript
const sq = new SqlSlow(null, { alert: '>300ms' });
sq.start();

// Wrap any database call
const t0 = Date.now();
await knex('users').where({ active: true });
sq.ingest('SELECT * FROM users WHERE active = true', Date.now() - t0);
```

---

## Architecture

```
SqlSlow (src/index.ts)
│
├── SqlSlowMonitor (src/monitor.ts)
│   ├── MetricsStore (src/utils/metrics.ts)       ← in-memory LRU store
│   ├── EventEmitter3                              ← query / slow-query / critical-query
│   └── Adapters (src/adapters/)
│       ├── prismaAdapter.ts  → prisma.$on('query')
│       ├── pgAdapter.ts      → pool.query() monkey-patch
│       └── mysql2Adapter.ts  → connection.query() monkey-patch
│
├── AIOptimizer (src/ai/optimizer.ts)
│   ├── AiCache (src/ai/cache.ts)                 ← LRU + TTL
│   └── Providers
│       ├── openai.ts    → GPT-4o-mini / GPT-4
│       ├── anthropic.ts → Claude 3 Haiku / Sonnet
│       └── gemini.ts    → Gemini 1.5 Flash / Pro
│
├── AlertService (src/alerts/alertService.ts)
│   ├── TelegramChannel  → bot.sendMessage (HTML)
│   ├── SlackChannel     → Incoming Webhook (Block Kit)
│   ├── DiscordChannel   → Webhook (Embeds)
│   ├── WebhookChannel   → Generic HTTP POST
│   └── Console          → ANSI-coloured stdout
│
└── DashboardServer (src/dashboard/server.ts)
    ├── GET  /          → Self-contained dark SPA (Chart.js + Tailwind CDN)
    ├── GET  /api/stats → Full stats JSON
    ├── GET  /api/slow-queries
    ├── GET  /api/top-slowest
    ├── GET  /health
    └── DELETE /api/metrics
```

---

## Pricing

| Tier | Price | Limits |
|------|-------|--------|
| **Free** | $0 | 10 000 queries/month · Telegram alerts · Basic stats |
| **Pro** | $34/mo | Unlimited · Dashboard · AI suggestions · Daily reports · Priority support |
| **Enterprise** | Custom | White-label · SLA · Custom integrations · 24/7 support |

---

## License

MIT © sql-slow contributors