Size icon
Assert icon
Concat icon
Queries icon
Parallel icon
Query icon
Write icon
Webhook icon

Build and verify a portable SQLite full-text search catalog

Build a portable SQLite FTS5 catalog with native Kestra JDBC tasks, independent integrity checks, exact search tests and a final assertion.

Categories
Data

Package a small documentation or runbook collection as a searchable SQLite file. Native SQLite tasks load the documents, build a Unicode FTS5 index and reopen the stored database to check the searches you expect to work.

How it works

  1. Upload a JSON array or send its Kestra storage URI as documents through the keyed webhook. Bound the source to 10 MiB, then stage it into this execution.
  2. sqlite.Queries binds the document array and search checks as parameters. SQL validates types, unique IDs, text lengths and expected ID sets, and creates the content tables in one transaction.
  3. Another SQLite task loads that database, builds and optimizes an external-content FTS5 index, checks it against the content table and saves the file.
  4. Independent SQLite Query tasks reopen the artifact in parallel for integrity, count and exact search checks. A final Kestra Assert fails on any mismatch; the database flow output is withheld unless all checks pass.

What you get

  • A portable database with original text, Unicode index, search checks and source document count; JSON search results and catalog metadata.
  • At most 10,000 documents, 20 checks and 100 matches per check. Unexpected extra matches and deliberately empty result sets are both checked.
  • No Python scripts, Docker runner or pip dependencies.

Who it's for

Platform and data teams distributing small offline knowledge catalogs or checking search exports before deployment.

Why orchestrate this with Kestra

Loading, index construction and artifact verification are separate native tasks with database handoffs in internal storage. Parallel readers inspect independent copies; declarative assertions determine success.

Prerequisites

Install the SQLite JDBC plugin with JSON and FTS5 support. Input is a UTF-8 JSON array, not JSON Lines. Each object needs string id, title, body; url is optional string metadata. IDs, titles, bodies and URLs are limited to 128, 512, 100,000 and 2,048 characters. NUL characters and blank IDs/searchable text are rejected.

Secrets

  • SEARCH_WEBHOOK_KEY: protects event submissions.

Quick start

  1. Configure the secret and import the flow.
  2. Upload [{"id":"getting-started","title":"Kestra guide","body":"Run your first flow."}]. Execute with the default search check.
  3. Download the database. With FTS5-enabled SQLite, run SELECT rowid FROM documents_fts WHERE documents_fts MATCH 'kestra';.

How to extend

Add phrase, prefix and negative search tests or upload the verified file to object storage. This is a full rebuild; source permissions and later edits are not synchronized. Preserve the content/index relationship if you add incremental updates.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.