Schedule icon
Query icon
If icon
Log icon
SlackIncomingWebhook icon

SQLite Database Integrity and Corruption Sentinel

Run SQLite PRAGMA integrity_check on a schedule, classify the database HEALTHY or CORRUPT, and alert Slack with the exact errors.

Categories
DataInfrastructure

An embedded SQLite file can go silently corrupt - a crashed write, a disk fault, or a killed process mid-transaction can leave pages malformed or index entries missing, with no server process around to notice or report it. This blueprint runs SQLite's own low-level PRAGMA integrity_check on a schedule, classifies the result, and alerts Slack with the exact problem descriptions SQLite itself reports.

How it works

  1. periodic_integrity_check (io.kestra.plugin.core.trigger.Schedule) runs every 6 hours, explicitly passing sqlite_db_path and max_errors_to_report through its own inputs: block so the flow is self-contained when the trigger fires. Shipped disabled so a manual run can be verified first.
  2. probe_integrity_check (io.kestra.plugin.jdbc.sqlite.Query, fetchType: FETCH) runs PRAGMA integrity_check(N) against the file at sqlite_db_path. Per SQLite's own documentation, this returns exactly one row containing the literal value ok when the database is consistent, or one row per problem description (up to N) when it is not.
  3. evaluate_integrity (io.kestra.plugin.core.flow.If) branches on whether exactly one row came back and that row's value is ok.
    • then (HEALTHY): log_integrity_healthy records a clean check.
    • else (CORRUPT): alert_integrity_corruption reports every returned problem description to Slack, joined into a single message.
  4. log_audit_complete always runs last, printing how many rows probe_integrity_check returned regardless of which branch fired.
  5. The errors block alerts Slack separately if the flow itself fails outright - a missing file or unreadable path must not read as either HEALTHY or CORRUPT.

What you get

  • A HEALTHY/CORRUPT answer sourced directly from SQLite's own low-level consistency checker, not inferred from query errors or an external agent.
  • The exact problem descriptions SQLite itself reports, delivered to Slack, instead of requiring someone to open the file with the sqlite3 CLI by hand.
  • No database credentials to manage at all - SQLite is an embedded, fileless-auth engine, so this flow's only secret is the Slack webhook.
  • A configurable error cap (max_errors_to_report) so a badly corrupted file does not flood the alert with an unbounded problem list.

Who it's for

  • Teams running SQLite as an embedded store in edge devices, local caches, or lightweight application backends who need an early signal of silent file corruption.
  • On-call engineers who want one Slack alert with SQLite's own problem descriptions instead of running PRAGMA integrity_check by hand after a crash is suspected.
  • Anyone evaluating the SQLite JDBC plugin who wants a worked example of Query driving a diagnostic PRAGMA rather than a table read or write.

Why orchestrate this with Kestra

PRAGMA integrity_check run by hand answers one moment in time; it does not decide a cadence, classify the result, or notify anyone - and SQLite itself has no scheduler or alerting of its own. Kestra supplies the schedule, the HEALTHY/CORRUPT classification as a first-class branch, and an execution history that shows exactly when a file degraded and what SQLite's own checker said about it.

Prerequisites

  • A SQLite database file reachable on the Kestra worker's local filesystem or a mounted volume (update sqlite_db_path). SQLite is embedded - there is no separate database server process to reach over the network, and this flow needs no database credentials.
  • A Slack incoming webhook for alerts.

Local testing (no database server container is needed - SQLite is embedded in the JDBC driver the Kestra worker loads directly; the command below only creates a sample file to point sqlite_db_path at):

docker run --rm -v /tmp:/data nouchka/sqlite3 sqlite3 /data/sentinel-test.db "CREATE TABLE t(id INTEGER PRIMARY KEY, val TEXT); INSERT INTO t VALUES (1,'ok');"

nouchka/sqlite3 is a community (not SQLite-project-official) SQLite CLI image, used here only to create the test fixture at /tmp/sentinel-test.db on the host - substitute any SQLite CLI you already have. No docker network connect is required for this flow, since no database server container is involved; it only applies if the Kestra worker itself runs in a container that needs the host-mounted path, in which case run docker network ls to confirm the Kestra worker and this fixture share a visible filesystem path.

Secrets

  • SLACK_WEBHOOK_URL: Slack incoming webhook used by alert_integrity_corruption and the errors block. This is the only secret this flow uses.
  • In Kestra OSS (no Enterprise secrets backend), secrets are supplied as environment variables prefixed SECRET_, base64-encoded - for example SECRET_SLACK_WEBHOOK_URL=$(echo -n '<your-webhook-url>' | base64) - and read back in flows with {{ secret('NAME') }}.

Inputs

  • sqlite_db_path (STRING, default /tmp/sentinel-test.db): filesystem path to the SQLite file, used as jdbc:sqlite:<path>.
  • max_errors_to_report (INT, default 10): passed as PRAGMA integrity_check(N); SQLite's own default is 100 when omitted entirely.

Outputs

  • outputs.probe_integrity_check.size: number of rows PRAGMA integrity_check returned - 1 on a healthy database, more on a corrupt one.
  • outputs.probe_integrity_check.rows: the raw row list; each row's single value is either the literal ok or one problem description.

Quick start

  1. Create a sample database file with the command above (or point sqlite_db_path at any existing .sqlite/.db file).
  2. Add the SLACK_WEBHOOK_URL secret.
  3. Run the flow manually with the defaults and confirm log_integrity_healthy fires against the freshly created file.
  4. To exercise the CORRUPT path, corrupt a copy of the file directly (for example, truncate it mid-file with truncate -s 50% sentinel-test.db, which is destructive and should only be done on a disposable copy) and re-run to confirm alert_integrity_corruption reports SQLite's own problem descriptions.
  5. Enable periodic_integrity_check once you trust the check against your real file.

How to extend

  • Swap the direct url: jdbc:sqlite:{{ inputs.sqlite_db_path }} connection for the plugin's own sqliteFile property to audit a database file that lives in Kestra internal storage (for example, one uploaded by a separate backup task) instead of the worker's local filesystem.
  • Add a .backup step using the same plugin ahead of the probe to snapshot the file before every check, so a CORRUPT verdict always has a last-known-good copy sitting next to it.
  • Loop over several sqlite_db_path values with io.kestra.plugin.core.flow.Loop to audit every embedded database file on a host in one run.

Pitfalls

  • The exact column label SQLite's JDBC driver assigns to the integrity_check result is not asserted here. evaluate_integrity and the Slack payload both read each row's value positionally via jq('to_entries[0].value') rather than by a named key, specifically to avoid depending on an unverified column name.
  • io.kestra.plugin.jdbc.sqlite.Query supports only a single SQL statement per task. Use io.kestra.plugin.jdbc.sqlite.Queries instead for flows that need more than one statement per task.
  • A missing file at sqlite_db_path does not read as CORRUPT. SQLite's JDBC driver creates a new, empty (and therefore trivially "healthy") database file if none exists at that path for some connection modes, or the connection fails outright depending on URL options - either way, verify the path actually points at the file you intend to audit, since an empty new file passing integrity_check is not a meaningful result.
  • This checks on-disk consistency, not application-level data correctness. A database can pass integrity_check cleanly while still containing logically wrong data; this sentinel only catches the low-level corruption SQLite's own page/index/constraint scan is documented to detect.
  • This blueprint is UNTESTED against a real corrupted SQLite file. The PRAGMA integrity_check behavior and output contract are taken verbatim from SQLite's own documentation, and the io.kestra.plugin.jdbc.sqlite.Query properties and outputs are taken from the plugin's own source on main - but no Kestra engine was run to execute this flow end to end, and no genuinely corrupted file was exercised against it.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.