sqlite-data

name	sqlite-data
description	Query and inspect SQLite databases used by data tools. Use when you need to directly inspect stored pipeline runs, metrics, or configuration data stored in a SQLite database file. Triggers include "query the database", "inspect SQLite", "check raw data", "what is in the db", or any task requiring direct database access.

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

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

Recent run history with status

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)

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

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

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)

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

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.