id: sqlite-verified-fts-catalog
namespace: company.data
inputs:
- id: documents
type: FILE
description: UTF-8 JSON array of objects with unique string id, title and body,
plus optional string url. Maximum 10 MiB and 10,000 documents.
- id: smoke_checks
type: JSON
description: '1-20 FTS5 queries with expected_ids: the exact matching ID set, at
most 100 per query.'
defaults:
- query: kestra
expected_ids:
- getting-started
tasks:
- id: source_size
type: io.kestra.plugin.core.storage.Size
uri: '{{ inputs.documents }}'
- id: bound_source
type: io.kestra.plugin.core.execution.Assert
conditions:
- '{{ outputs.source_size.size > 0 and outputs.source_size.size <=
10485760 }}'
- id: stage_documents
type: io.kestra.plugin.core.storage.Concat
files:
- '{{ inputs.documents }}'
description: Copy a producer-owned URI into this execution before read() binds
its JSON to SQLite.
- id: load_documents
type: io.kestra.plugin.jdbc.sqlite.Queries
url: jdbc:sqlite:catalog.sqlite
outputDbFile: true
parameters:
documents: '{{ read(outputs.stage_documents.uri) }}'
smoke_checks: '{{ inputs.smoke_checks | toJson }}'
sql: |
CREATE TABLE checks (ok INTEGER NOT NULL CHECK (ok = 1));
INSERT INTO checks VALUES (json_valid(:documents));
INSERT INTO checks VALUES (json_type(:documents) = 'array');
CREATE TEMP TABLE raw_documents AS SELECT value, type FROM json_each(:documents);
INSERT INTO checks SELECT count(*) BETWEEN 1 AND 10000 FROM raw_documents;
INSERT INTO checks SELECT type = 'object' FROM raw_documents;
INSERT INTO checks SELECT json_type(value, '$.id') = 'text'
AND json_type(value, '$.title') = 'text' AND json_type(value, '$.body') = 'text'
AND (json_type(value, '$.url') IS NULL OR json_type(value, '$.url') = 'text')
FROM raw_documents;
CREATE TABLE documents (
rowid INTEGER PRIMARY KEY,
doc_id TEXT NOT NULL UNIQUE CHECK (length(trim(doc_id, char(9,10,13,32))) BETWEEN 1 AND 128 AND instr(doc_id, char(0)) = 0),
title TEXT NOT NULL CHECK (length(title) <= 512 AND instr(title, char(0)) = 0),
body TEXT NOT NULL CHECK (length(body) <= 100000 AND instr(body, char(0)) = 0),
url TEXT NOT NULL CHECK (length(url) <= 2048 AND instr(url, char(0)) = 0),
CHECK (length(trim(title || body, char(9,10,13,32))) > 0)
);
INSERT INTO documents(doc_id, title, body, url)
SELECT json_extract(value, '$.id'), json_extract(value, '$.title'),
json_extract(value, '$.body'), COALESCE(json_extract(value, '$.url'), '')
FROM raw_documents ORDER BY json_extract(value, '$.id');
INSERT INTO checks VALUES (json_type(:smoke_checks) = 'array');
CREATE TEMP TABLE raw_checks AS SELECT key, value, type FROM json_each(:smoke_checks);
INSERT INTO checks SELECT count(*) BETWEEN 1 AND 20 FROM raw_checks;
INSERT INTO checks SELECT type = 'object' FROM raw_checks;
INSERT INTO checks SELECT
(SELECT count(*) FROM json_each(r.value)) = 2
AND json_type(r.value, '$.query') = 'text'
AND json_type(r.value, '$.expected_ids') = 'array'
AND json_array_length(r.value, '$.expected_ids') <= 100 FROM raw_checks r;
INSERT INTO checks SELECT j.type = 'text'
AND length(trim(j.value, char(9,10,13,32))) BETWEEN 1 AND 128
AND instr(j.value, char(0)) = 0
FROM raw_checks r, json_each(r.value, '$.expected_ids') j;
INSERT INTO checks SELECT
(SELECT count(*) FROM json_each(r.value, '$.expected_ids')) =
(SELECT count(DISTINCT value) FROM json_each(r.value, '$.expected_ids'))
FROM raw_checks r;
CREATE TABLE search_checks (
check_id INTEGER PRIMARY KEY,
query TEXT NOT NULL CHECK (length(trim(query, char(9,10,13,32))) BETWEEN 1 AND 512 AND instr(query, char(0)) = 0),
expected_ids TEXT NOT NULL
);
INSERT INTO search_checks
SELECT r.key, json_extract(r.value, '$.query'),
(SELECT json_group_array(value) FROM
(SELECT value FROM json_each(r.value, '$.expected_ids') ORDER BY value))
FROM raw_checks r;
CREATE TABLE catalog_metadata AS SELECT count(*) AS document_count FROM documents;
DROP TABLE checks;
- id: build_index
type: io.kestra.plugin.jdbc.sqlite.Queries
url: jdbc:sqlite:catalog.sqlite
sqliteFile: '{{ outputs.load_documents.databaseUri }}'
outputDbFile: true
sql: |
CREATE VIRTUAL TABLE documents_fts USING fts5(
title, body, content='documents', content_rowid='rowid',
tokenize='unicode61 remove_diacritics 2');
INSERT INTO documents_fts(documents_fts) VALUES ('rebuild');
INSERT INTO documents_fts(documents_fts) VALUES ('optimize');
-- rank=1 also compares the external-content table with the index.
INSERT INTO documents_fts(documents_fts, rank) VALUES ('integrity-check', 1);
PRAGMA user_version=1;
- id: verify_artifact
type: io.kestra.plugin.core.flow.Parallel
description: Each native Query downloads and opens the built database independently.
tasks:
- id: integrity
type: io.kestra.plugin.jdbc.sqlite.Query
url: jdbc:sqlite:catalog.sqlite
sqliteFile: '{{ outputs.build_index.databaseUri }}'
fetchType: FETCH_ONE
sql: |
SELECT count(*) = 1 AND min(integrity_check) = 'ok' AS integrity_ok
FROM pragma_integrity_check;
- id: search_results
type: io.kestra.plugin.jdbc.sqlite.Query
url: jdbc:sqlite:catalog.sqlite
sqliteFile: '{{ outputs.build_index.databaseUri }}'
fetchType: FETCH_ONE
sql: |
WITH evaluated AS (
SELECT c.check_id, c.query, c.expected_ids,
(SELECT json_group_array(doc_id) FROM (
SELECT d.doc_id FROM documents_fts JOIN documents d ON d.rowid = documents_fts.rowid
WHERE documents_fts MATCH c.query ORDER BY d.doc_id LIMIT 101
)) AS actual_ids
FROM search_checks c
)
SELECT min(actual_ids = expected_ids AND json_array_length(actual_ids) <= 100) AS search_ok,
count(*) AS check_count,
json_group_array(json_object('query', query, 'matched_ids', json(actual_ids))) AS results
FROM (SELECT * FROM evaluated ORDER BY check_id);
- id: catalog_info
type: io.kestra.plugin.jdbc.sqlite.Query
url: jdbc:sqlite:catalog.sqlite
sqliteFile: '{{ outputs.build_index.databaseUri }}'
fetchType: FETCH_ONE
sql: |
SELECT (SELECT count(*) FROM documents) AS document_count,
document_count AS source_document_count, sqlite_version() AS sqlite_version,
'unicode61 remove_diacritics 2' AS tokenizer, 1 AS schema_version
FROM catalog_metadata;
- id: save_search_results
type: io.kestra.plugin.core.storage.Write
extension: .json
content: '{{ outputs.search_results.row.results }}'
- id: save_manifest
type: io.kestra.plugin.core.storage.Write
extension: .json
content: '{{ outputs.catalog_info.row | toJson }}'
- id: verification
type: io.kestra.plugin.core.execution.Assert
conditions:
- '{{ outputs.integrity.row.integrity_ok == 1 }}'
- '{{ outputs.search_results.row.search_ok == 1 }}'
- '{{ outputs.search_results.row.check_count >= 1 and
outputs.search_results.row.check_count <= 20 }}'
- '{{ outputs.catalog_info.row.document_count ==
outputs.catalog_info.row.source_document_count }}'
outputs:
- id: database
type: FILE
required: false
value: '{{ (outputs.integrity.row.integrity_ok ?? 0) == 1 and
(outputs.search_results.row.search_ok ?? 0) == 1 and
(outputs.catalog_info.row.document_count ?? -1) ==
(outputs.catalog_info.row.source_document_count ?? -2) ?
outputs.build_index.databaseUri : null }}'
- id: manifest
type: FILE
required: false
value: '{{ outputs.save_manifest.uri ?? null }}'
- id: search_results
type: FILE
required: false
value: '{{ outputs.save_search_results.uri ?? null }}'
triggers:
- id: documents_ready
type: io.kestra.plugin.core.trigger.Webhook
key: '{{ secret(''SEARCH_WEBHOOK_KEY'') }}'
inputs:
documents: '{{ trigger.body.documents }}'
smoke_checks: '{{ trigger.body.smoke_checks ??
[{"query":"kestra","expected_ids":["getting-started"]}] }}'