Add lightweight SQLite database diagnostics script

[ejboy]

Goal

Add a small read-only shell script for troubleshooting StatLite database size, growth, and abnormal data patterns.

The default mode should be inexpensive and safe to run against a live StatLite database. An optional --deep mode should perform additional analytical queries useful for diagnosing unexpected database growth.

Commands

Basic diagnostics:

./scripts/statlite-db-info.sh /path/to/statlite.sqlite

Deeper analysis:

./scripts/statlite-db-info.sh --deep /path/to/statlite.sqlite

Requirements

Create:

scripts/statlite-db-info.sh

The script should:

• require the path to a StatLite SQLite database;
• require sqlite3;
• perform read-only diagnostics only;
• support a lightweight default mode suitable for a live StatLite instance;
• support an optional --deep mode for more expensive analytical queries;
• never run VACUUM, checkpoints, migrations, deletes, updates, or other modifying operations;
• fail clearly when the file does not exist or cannot be queried;
• keep output compact and human-readable.

Default diagnostics

Files

Report:

• database path;
• main SQLite file size;
• -wal file size when present;
• -shm file size when present;
• total size.

SQLite allocation

Use inexpensive SQLite pragmas to report:

• page size;
• page count;
• freelist page count;
• approximate allocated database size;
• approximate reusable/free space;
• reusable space percentage.

This distinction is important because retention can delete rows while SQLite retains the physical pages for future reuse.

StatLite data

Report:

• target count;
• poll count;
• metric sample count;
• collector event count;
• oldest poll timestamp;
• newest poll timestamp;
• approximate history span.

Example:

StatLite database diagnostics

Database:        /var/lib/statlite/statlite.sqlite

Files
Main:            284 MB
WAL:              12 MB
SHM:              32 KB
Total:           296 MB

SQLite
Page size:         4 KB
Pages:            72,704
Free pages:       18,320
Reusable space:   71.6 MB (25.2%)

Data
Targets:               3
Polls:           248,103
Metric samples: 2,481,030
Events:            1,284
Oldest poll:    2026-05-11 14:22:00
Newest poll:    2026-08-09 21:21:00
History span:             90 days

Deep diagnostics

--deep should answer a different question:

What is generating the stored data, and are there obvious anomalies?

It may perform more expensive aggregation queries because the user explicitly requested deeper analysis.

Daily activity

Report averages over the retained history:

• polls per day;
• metric samples per day;
• collector events per day;
• poll errors per day.

Also report recent activity, preferably for the most recent complete day or last 24 hours, so the user can compare current behavior with the historical average.

Example:

Activity
                         Avg/day     Last 24h
Polls                     8,640        8,612
Metric samples           86,400       86,120
Events                       14           93
Poll errors                   2           41

This makes sudden event/error growth immediately visible.

Per-target breakdown

For each target report:

• poll count;
• approximate polls per day;
• metric sample count;
• average metric samples per successful poll;
• collector event count;
• poll error count.

Example:

Targets
catalog-api
  Polls:                 86,390
  Polls/day:                960
  Samples/poll:              10
  Events:                   124
  Poll errors:               18

payments-api
  Polls:                 86,401
  Polls/day:                960
  Samples/poll:              10
  Events:                 4,912
  Poll errors:            2,104

This should make a single noisy or failing target easy to identify.

Event breakdown

Report collector events grouped by:

• severity;
• event type.

Include count and approximate events/day.

Prefer showing the highest-volume event types first and limit output to a reasonable number, such as the top 10.

Example:

Event types
collector_timeout          2,104   23.4/day
metric_missing               844    9.4/day
restart_detected              17    0.2/day

Poll status

Report:

• successful polls;
• failed polls;
• failure percentage.

Where practical, include the same breakdown per target.

This is useful because an unexpectedly large collector_events table may simply reflect a target failing every polling interval.

Data density

Report:

• average metric samples per poll;
• maximum metric samples observed for one poll;
• average events per poll;
• maximum events observed for one poll.

These values help detect cases where a collector unexpectedly starts producing substantially more rows per poll.

Recent growth

Where practical without introducing excessive complexity, report row counts for a recent window such as the last 24 hours:

• polls;
• samples;
• events;
• errors.

Comparing these with historical daily averages is more useful for anomaly detection than reporting lifetime totals alone.

Anomaly hints

--deep may print a small Observations section based on simple deterministic conditions.

Examples:

Observations
- Events in the last 24h are 6.6x the historical daily average.
- Target "payments-api" accounts for 91% of recorded poll errors.
- 38% of allocated SQLite pages are currently reusable.

Keep these rules simple and factual.

Do not introduce statistical anomaly detection, machine learning, configurable thresholds, or an alerting framework.

The purpose is to point the user toward obviously unusual values already calculated by the script.

Performance

Default mode should remain lightweight.

--deep is explicitly allowed to perform aggregate queries over retained history, but queries should still use the existing schema and indexes sensibly.

Avoid producing large raw result sets. Aggregation should happen in SQLite and return only compact summaries.

Do not run expensive database integrity checks automatically as part of either mode.

Live database behavior

Default mode must be suitable for a running StatLite instance.

--deep should also remain read-only and may normally be run against a live database, but documentation should note that it performs more database work than the default mode.

Do not instruct users to copy only the .sqlite file while StatLite is running. A WAL-mode database may also depend on its -wal file.

If an isolated snapshot is needed, recommend SQLite's backup command:

sqlite3 /var/lib/statlite/statlite.sqlite \
  ".backup '/tmp/statlite-diagnostics.sqlite'"

Then:

./scripts/statlite-db-info.sh --deep /tmp/statlite-diagnostics.sqlite

Documentation

Add a short troubleshooting section showing both modes:

./scripts/statlite-db-info.sh /path/to/statlite.sqlite
./scripts/statlite-db-info.sh --deep /path/to/statlite.sqlite

Explain that:

• both modes are read-only;
• default mode is intended to be cheap against a live database;
• deep mode performs additional aggregation queries;
• physical SQLite size may remain high after retention cleanup because pages can be reused;
• deep mode helps identify noisy targets, excessive failures/events, unexpected samples-per-poll, and recent growth anomalies.

Non-goals

Do not add:

• automatic VACUUM;
• database modifications or repairs;
• automatic WAL checkpoints;
• /healthz changes;
• a new HTTP diagnostics endpoint;
• general SQLite administration features;
• query-plan/index recommendations;
• statistical anomaly detection;
• alerting.

Keep this a small StatLite-specific troubleshooting utility.

Acceptance criteria

• Default mode runs successfully against a normal live StatLite database.
• --deep provides additional aggregate diagnostics without modifying the database.
• Missing -wal and -shm files are handled normally.
• Missing database or sqlite3 produces a clear error and non-zero exit status.
• Default output includes physical sizes, SQLite reusable-space information, row counts, and history boundaries.
• Deep output includes daily averages, recent activity, per-target statistics, event/error breakdowns, and data-density information.
• Deep output makes obvious sources of abnormal database growth identifiable without dumping raw database rows.
• Output remains compact enough to paste into a GitHub issue.