sqlite-data

# sqlite-data

Direct SQLite inspection for data-pipeline-monitor's internal database.

## When to use

- Debugging unexpected pipeline states
- Exporting raw run history to CSV
- Verifying alert rules stored correctly
- Manual data correction (use with care)

## Prerequisites

- `sqlite3` CLI installed: `brew install sqlite3` or `apt install sqlite3`
- Database file path: `$DPM_DATA_DIR/dpm.db` (default: `~/.dpm/dpm.db`)

## Useful Queries

### List all pipelines

```sql
SELECT id, name, schedule, created_at FROM pipelines ORDER BY created_at;
```

### Recent run history with status

```sql
SELECT r.id, r.pipeline_id, r.job_id, r.status, r.started_at, r.duration_ms, r.error_message
FROM runs r
ORDER BY r.started_at DESC
LIMIT 50;
```

### Success rate per pipeline (last 7 days)

```sql
SELECT pipeline_id,
       COUNT(*) as total,
       SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as successes,
       ROUND(100.0 * SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) / COUNT(*), 1) as rate
FROM runs
WHERE started_at > datetime('now', '-7 days')
GROUP BY pipeline_id
ORDER BY rate ASC;
```

### p50 and p95 duration per pipeline

```sql
SELECT pipeline_id,
       PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY duration_ms) / 1000.0 as p50_s,
       PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY duration_ms) / 1000.0 as p95_s
FROM runs
WHERE status = 'success'
GROUP BY pipeline_id;
```

### Runs stuck in "running" state

```sql
SELECT id, pipeline_id, job_id, started_at, last_heartbeat_at
FROM runs
WHERE status = 'running'
  AND last_heartbeat_at < datetime('now', '-600 seconds')
ORDER BY last_heartbeat_at ASC;
```

### Alert log (recent notifications)

```sql
SELECT al.triggered_at, a.condition, a.pipeline_id, a.channel, a.target
FROM alert_log al
JOIN alerts a ON al.alert_id = a.id
ORDER BY al.triggered_at DESC
LIMIT 20;
```

## Connect via CLI

```bash
sqlite3 ~/.dpm/dpm.db

# Enable column headers and aligned output
.headers on
.mode column

# List tables
.tables

# Schema for a table
.schema runs
```

## Schema Reference

See `ARCHITECTURE.md` for the full schema. Key tables:

| Table | Contents |
|---|---|
| `pipelines` | Pipeline registration: id, schedule, jobs |
| `jobs` | Job definitions within each pipeline |
| `runs` | Execution records: status, duration, errors |
| `alerts` | Alert rule configuration |
| `alert_log` | History of sent notifications |
| `api_keys` | API key hashes and labels |
| `settings` | Server configuration values |

## Caution

- Do not edit `runs` table directly while the server is running. It can cause state machine inconsistencies.
- The `api_keys.id` column stores SHA-256 hashes. Raw keys are never stored.
- SQLite is in WAL mode. Use `PRAGMA wal_checkpoint;` if the WAL file is large.