SQLite foreign key referential integrity and orphan record sentinel

Automate referential integrity enforcement across SQLite embedded databases by running SQLite's native PRAGMA foreign_key_check;. SQLite disables foreign key constraint enforcement by default (PRAGMA foreign_keys = OFF), and bulk batch loads, schema migrations, or legacy scripts often introduce orphan child records without triggering write-time errors. This sentinel workflow periodically audits the entire database, evaluates constraint health, sends granular Slack notifications listing the exact tables and offending row IDs, and fails execution when referential integrity is compromised.

How it works

1. A Schedule trigger periodically invokes the audit on a 12-hour cadence (shipped disabled by default).
2. Task probe_foreign_key_check uses io.kestra.plugin.jdbc.sqlite.Query to connect via JDBC (jdbc:sqlite:<path>) and run PRAGMA foreign_key_check; with fetchType: FETCH. When the database has no broken foreign keys, SQLite returns zero rows. When broken references exist, it returns one row per violation containing the child table name, rowid, parent table name, and foreign key index.
3. Task evaluate_integrity (io.kestra.plugin.core.flow.If) inspects the row count in {{ outputs.probe_foreign_key_check.size }}:
   • If 0: Runs log_integrity_healthy to record clean referential health.
   • If greater than 0: Sends an alert to Slack via io.kestra.plugin.slack.notifications.SlackIncomingWebhook formatting the offending child tables and row IDs, followed by fail_on_violations (io.kestra.plugin.core.execution.Fail) to trigger incident workflows.
4. Task log_audit_summary emits a final structured log with the total violation count.
5. Top-level errors handler catches unexpected connection or permission failures and notifies on-call teams.

What you get

• Proactive detection of orphan records and broken relational links before they corrupt downstream analytics or reporting.
• Automated inspection of SQLite databases where write-time foreign key enforcement is turned off or bypassed during bulk loads.
• Formatted Slack alert payloads pinpointing the exact child table name, row ID, and expected parent table.
• Explicit execution failure on integrity violations to halt dependent data transformation flows.

Who it's for

• Data Engineers running SQLite in edge devices, embedded services, or local analytics sandboxes.
• Backend Engineers managing applications using SQLite with periodic batch ingestion or legacy databases.
• Platform and DevOps teams maintaining SQLite state stores who need automated data corruption sentinels.

Why orchestrate this with Kestra

Standalone cron jobs running custom Python or Bash scripts to check database integrity often fail silently, lack centralized audit logging, and require custom webhook notification code. Kestra provides declarative, version-controlled orchestration with native JDBC query execution, dynamic Pebble expressions, built-in retry handling, and error routing. With Kestra, database integrity verification becomes an observable, auditable workflow integrated directly into your deployment and monitoring pipeline.

Prerequisites

• SQLite database file located on worker filesystem or a mounted volume.
• Outbound internet access from the Kestra worker to send Slack webhook alerts.

Secrets

• SLACK_WEBHOOK_URL: Slack Incoming Webhook URL used to post integrity violation alerts and runtime failure notifications.

Quick start

1. Store your Slack webhook URL in Kestra Settings under Secrets with the key SLACK_WEBHOOK_URL.
2. Provide the absolute filesystem path to your SQLite database file in the sqlite_db_path input (e.g., /tmp/sqlite-data.db).
3. Trigger a manual execution from the Kestra UI to verify database connectivity and review output logs.
4. Enable the periodic_foreign_key_audit trigger to establish recurring scheduled audits.

How to extend

• Target specific tables by passing a table name argument to PRAGMA foreign_key_check(<table_name>); to optimize audits on large databases.
• Add an automated quarantine task that extracts violating row IDs into an inspection table or dead-letter queue before purging.
• Chain a downstream backup workflow (e.g., S3 upload or Litestream replication checkpoint) that triggers only when integrity passes.

Links

• SQLite Foreign Key Documentation
• Kestra SQLite JDBC Plugin
• Kestra Slack Notification Plugin