dbTalk reference
Reference
Every component, threshold and interface. The engineering notes are in README.md; the database-side setup is in grants_postgres.sql and grants_mysql.sql.
Components
| Program | What it does | Typical schedule |
|---|
drift_web.py | The website, accounts, billing and question box over the stored history; --run-monitors and --send-reminders for the scheduled jobs | Always on |
monitor.py | Measures one database, updates its baseline, raises drift flags, analyzes the worst statements, writes a report | Hourly |
engines/postgres.py, engines/mysql.py | Read-only collectors and analyzers per engine: readiness checks, snapshots, statement analysis, database review, fix scripts | Used by the above |
drift.py | Engine-independent history (SQLite), baselines, flags and reports | Used by the above |
mcp_server.py | Seven read-only tools over MCP (stdio or HTTP), REST and OpenAPI | Always on |
accounts.py, billing.py | Sign-in, encrypted credentials, reminders; Stripe Checkout, portal and webhooks | Used by drift_web.py |
Supported databases
| Engine | Versions | Requires | Statement id |
|---|
| PostgreSQL (self-managed, RDS, Aurora, Azure, Cloud SQL) | 12 and later | pg_stat_statements (or pg_stat_monitor); role pg_monitor | queryid, e.g. 4281133954018577510 |
| MySQL (self-managed, RDS, Aurora, Azure, Cloud SQL) | 5.7 and 8.x | performance_schema with the statements_digest consumer; SELECT on performance_schema, PROCESS | first 16 characters of the digest, e.g. 6f1d2c3b4a5e6f70 |
| MariaDB | 10.5 and later | performance_schema turned on (off by default) | as MySQL |
Drift flags
Baseline: median and median absolute deviation of each measure over the last 14 days (--baseline-days), compared at the same clock hour when there is enough history. Statements using under 10 seconds in an interval are ignored.
| Flag | Raised when | Severity |
|---|
| Statement regression | Time per execution 2x its baseline median (--ratio) and robust z-score 3 or more, after 3 intervals of history | HIGH at 4x or 600+ extra database seconds per hour, else MEDIUM |
| Plan change | A plan id not used in the baseline (pg_stat_monitor only) | HIGH if 1.5x slower, LOW if unchanged, INFO if faster |
| New heavy statement | Not in the baseline, 60+ seconds and 5%+ of the interval’s statement time | MEDIUM |
| Execution spike | 3x the baseline execution rate, 1,000+ executions | MEDIUM |
| System drift | A database-wide measure 1.5x its baseline and z 3 or more, after 5 intervals | HIGH for average active sessions at 2x, else MEDIUM |
Each flag is NEW, ONGOING (with the run it was first seen) or RESOLVED. The website shows each episode from first raised to resolved.
Measures
| Level | Measured each interval |
|---|
| Per statement | Executions, elapsed time, CPU (MySQL 8.0.28+), buffer reads, disk reads, rows |
| Database-wide | Average active sessions (statement time per second; PostgreSQL 14+ uses active_time), statements/s, transactions/s, buffer reads/s, disk reads/s, rows read and written/s, temp MB/s (PostgreSQL) or on-disk temp tables/s (MySQL), WAL / redo MB/s, lock waits/s, deadlocks/hour, active connections |
Data sources
| Engine | Reads | Optional, used when present |
|---|
| PostgreSQL | pg_stat_statements, pg_stat_database, pg_stat_wal (14+), pg_stat_checkpointer (17+) or pg_stat_bgwriter, pg_stat_activity, pg_stat_user_tables, pg_stat_user_indexes, pg_settings | pg_stat_monitor, pg_wait_sampling_profile, pg_qualstats_index_advisor(), EXPLAIN (GENERIC_PLAN) on 16+ |
| MySQL | events_statements_summary_by_digest, global_status (or SHOW GLOBAL STATUS), global_variables, setup_consumers, setup_instruments, INNODB_METRICS | events_statements_history_long (sample text for EXPLAIN FORMAT=JSON), table_io_waits_summary_by_index_usage, events_waits_summary_global_by_event_name |
Counters are cumulative, so each run stores the difference from the previous one. A server restart or a statistics reset is detected (start time, stats_reset, uptime) and starts a new interval instead of producing negative numbers.
Findings and fixes
| Engine | Per statement (Analyze) | Database review |
|---|
| PostgreSQL | Disk reads, temp spill (work_mem per role), rows per call, time variability (auto_explain), planning share, N+1 pattern, JIT share, sequential scan with a filter (CREATE INDEX CONCURRENTLY) | pg_stat_statements off, missing pg_monitor, track_io_timing, cache hit ratio, sequential-scan tables, dead rows (VACUUM), never analyzed, unused indexes (DROP INDEX CONCURRENTLY, undo = the index definition), forced checkpoints (max_wal_size), connections, idle in transaction, random_page_cost, pg_qualstats advice, waits |
| MySQL | Rows examined per row returned, no index used, full join, on-disk temp tables, sort merge passes, lock time share, errors, N+1 pattern, full scan in the plan (ALTER TABLE … ADD INDEX, ALGORITHM=INPLACE, LOCK=NONE) | performance_schema off (with my.cnf lines), buffer pool misses, on-disk temp table ratio, connections, redo capacity, slow query log, tables read without an index, unused indexes (INVISIBLE first), waits |
Every fix script has every statement commented out, numbered steps, and the undo for each. dbTalk never runs them.
Question words (built-in rules)
| Words | Understood as |
|---|
| last week, 3 days, 12 hours, two weeks, today, yesterday, this month | How far back (default 7 days, up to 60) |
| load, active sessions, AAS, DB time, CPU, busy | Average active sessions |
| connections, sessions, threads | Active connections |
| temp, spill, tmp | Temp MB/s (PostgreSQL) or on-disk temp tables/s (MySQL) |
| WAL, redo, binlog · deadlocks · locks · disk, I/O · buffer, cache · commits, transactions, TPS · rows · writes · queries, statements, QPS | That database-wide measure |
| a statement id (PostgreSQL queryid, or a 16-character MySQL digest) | That statement’s time per execution |
| flags, alerts, regressions, plan change, what changed | The flags raised |
| a database alias | That database |
| anything else, “drift” | The drift overview |
Website API
Single-team mode: every /api call needs Authorization: Bearer <token>. Accounts mode: the session cookie. Responses are JSON.
| Call | Returns |
|---|
GET /api/databases | Aliases, engine, descriptions, whether history exists, whether a model is configured |
GET /api/overview?database=&days= | Database-wide series with typical-for-the-hour, flag episodes, statement drift table with text excerpts, database review findings, latest run |
GET /api/sql?database=&sql_id=&days= | One statement’s time per execution (by plan where known), its text and flags |
GET /api/analyze?database=&sql_id= | Live read-only analysis: findings, plan note and the fix script |
POST /api/ask {"question", "database"} | The chart request, who read it (rules or model), the caption and the data |
GET /health | Liveness (no token) |
Accounts, billing and staff API
Accounts mode only. Sessions are an HttpOnly cookie; every POST after sign-in also needs the X-CSRF-Token header from /auth/me.
| Call | Does |
|---|
GET /auth/me | Mode, whether signed in, user, organization status (grace days left, suspended), CSRF token, billing info |
POST /auth/login {email, password, remember} · POST /auth/logout | Sign in (12 h sliding, or 30 days) / out |
POST /auth/forgot {email} · POST /auth/reset {token, password} | Emailed single-use link (30 min) / set the new password |
POST /auth/password {old, new} | Change password; other sessions end |
GET /api/account/databases | The organization’s databases (never the password) |
POST /api/account/databases/test {fields, password} or {id} | Connect and check every item read; nothing stored. Fields: engine (postgres, mysql), alias, description, host, port, dbname, tls (verify, require, off), username |
POST /api/account/databases/add · /remove | Test then save (password encrypted) / remove |
GET /api/billing · POST /api/billing/checkout · /portal | Status / Stripe Checkout URL / Stripe customer portal URL |
POST /billing/webhook | Stripe events, verified by the Stripe-Signature header |
GET /api/admin/summary · POST /api/admin/org | Staff only: organizations, users, events; actions disable, enable, simulate_payment_failed, simulate_payment_ok |
Billing states
| Status | Set by | Access |
|---|
active | checkout.session.completed, invoice.paid, subscription active | Full |
past_due | invoice.payment_failed, subscription past_due or unpaid | Full for grace_days (7) from the first failure, then 402 (“suspended”) and no monitoring |
canceled | customer.subscription.deleted | 402 until a new subscription |
| Email | Sent |
|---|
| Payment did not go through (with the invoice link and next retry date) | On invoice.payment_failed, once per invoice |
| Please confirm your payment (3-D Secure) | On invoice.payment_action_required |
| Payment still failing (3 and 6 days); monitoring paused | Daily job, once each per episode |
| Subscription ended | On customer.subscription.deleted |
Configuration
| Key | Meaning |
|---|
reports_dir | Where history, reports and per-organization audit logs live |
accounts {dir, grace_days} | Turns on accounts mode; folder for the accounts database, keys and outbox; days of access after a failed payment (7) |
public_url | The site’s address, used in reset, reminder and billing links (required on a public address) |
secure_cookies, trust_proxy | Behind HTTPS: Secure cookies; take the client address and scheme from the reverse proxy |
block_private_hosts | Refuse databases on private addresses (a public hosted service); loopback and link-local are always refused |
signups | false closes self-service sign-up |
mail | SMTP host, port, STARTTLS, user, password_env, from address |
billing | Stripe price_id, plan_name, price_display; keys from STRIPE_SECRET_KEY, STRIPE_WEBHOOK_SECRET |
push | vapid_key_file (from --gen-vapid) and subject for phone notifications |
databases | Single-team mode: by alias, each with engine, host, port, dbname, user, tls, label, description |
redact, audit_log | Literal redaction in returned SQL; audit file |
Secrets come from the environment, never the config file: DBTALK_SECRET_KEY (encrypts stored database passwords), STRIPE_*, DBTALK_SMTP_PASSWORD, DBTALK_TOKEN and DBTALK_PW_<ALIAS> (single-team mode), LLM_API_KEY. CA files for verified TLS: DBTALK_PG_CA, DBTALK_MYSQL_CA. Examples: dbtalk_hosted.example.json, dbtalk.example.json, deploy/dbtalk.env.example.
Command line
| drift_web.py option | Does |
|---|
--demo | Demo server: two synthetic databases, demo login, simulated billing, outbox |
--config FILE --http HOST:PORT | Run the website (accounts mode when the config has "accounts") |
--run-monitors | Measure every database once, then send notifications (hourly) |
--send-reminders | Payment reminder emails (daily) |
--create-staff EMAIL | Create a Bandlei staff login |
--notify [--min-severity HIGH] | Push notifications for new flags |
--gen-vapid PEM | Create the push key pair |
--llm-url URL --llm-model NAME | Use a model to read questions (rules remain the fallback) |
| monitor.py option | Does |
|---|
--engine postgres|mysql --host --port --dbname --user --tls verify|require|off | The database to measure |
--label NAME --out-dir DIR | Where its history and reports go (DIR/NAME) |
--password-env VAR | Password variable (default DBTALK_DB_PASSWORD; else the OS keyring, else a prompt) |
--check | Readiness checklist only |
--baseline-days --min-samples --ratio --analyze --alert-severity --alert-cmd --retain-days --keep | Thresholds, how many statements to analyze, alerting and retention |
| Tool | Returns |
|---|
list_databases | Aliases with engine and description |
check_connection | Version, and OK, MISSING or OPTIONAL per item read, with the command to enable it |
drift_status | Latest flags with evidence, resolved flags, database-wide measures |
top_statements | Heaviest statements now, with redacted text |
analyze_statement | Findings, plan note and the fix script for one statement |
drift_history | One statement’s time and reads per execution over time |
database_review | Settings, maintenance and index findings with a fix script |
HTTP endpoints: POST /mcp (Streamable HTTP), POST /tools/<name>, GET /openapi.json, GET /health.
Security controls
| Control | How |
|---|
| Read-only | Read-only sessions (PostgreSQL default_transaction_read_only; MySQL SET SESSION TRANSACTION READ ONLY), 30-second statement limits, only SELECT and EXPLAIN without ANALYZE. Fixes are scripts for a DBA. |
| Least privilege | PostgreSQL pg_monitor; MySQL SELECT on performance_schema and PROCESS. Table SELECT only if plans are wanted. |
| No passwords in files | Environment variables per alias, or the OS keyring |
| Redaction | Literals in returned SQL become ?; identifiers kept (pg_stat_statements and digests already normalize most literals) |
| Input validation | Statement ids, aliases, host names, user and database names, ranges and question length checked before use |
| Access | Bearer token with constant-time comparison; no-token mode only on 127.0.0.1; TLS through your reverse proxy |
| Audit | Every tool call and question as a JSON line: time, user, arguments, result, duration |
| Model boundary | Models never reach the database; on the website they only choose the chart, and their output is validated |
| Browser | Content-Security-Policy, no framing, no caching of API responses |
| Accounts | scrypt password hashes; 10+ character passwords; lockout after 8 failures in 15 minutes; single-use 30-minute reset links (only hashes stored); no account enumeration; sessions end on password change; HttpOnly SameSite cookies with CSRF tokens and an Origin check |
| Tenant isolation | Every account and data call is scoped to the signed-in user’s organization, each with its own audit log and alert list; staff pages need the staff role |
| Database credentials | Encrypted at rest (Fernet); never sent to the browser; decrypted in memory only to test or measure; TLS by default with certificate verification; connection tests refuse loopback, link-local and metadata addresses |
| Payments | Stripe Checkout and customer portal; no card data passes through dbTalk; webhooks verified by signature and timestamp |
Exit codes (monitor.py)
| Code | Meaning |
|---|
0 | Done, nothing new to alert on |
1 | Could not connect, or configuration problem |
2 | Previous run still in progress (lock file) |
3 | Finished, but some measurements or analyses failed |
4 | ALERT: new drift at or above --alert-severity |
Output layout
reports/<label>/
baseline.sqlite hourly history (read by the website and agent tools)
drift/<YYYY-MM-DD_HHMMSS>/ index.html, flags.json, stmt_<id>.html, fix_<id>.sql,
database.html, database_fix.sql
drift/latest.html newest drift report
drift/drift_history.csv one line per flag per run