surveilr as Ingestion Layer

surveilr admin init

cd www.opsfolio.com/src/ai-context-engineering
surveilr ingest files -r www.opsfolio.com/src/ai-context-engineering/regime/soc2.prompt.md

surveilr orchestrate transform-markdown
surveilr orchestrate transform-html

-- This set of queries provides extensive examples for testing access to the JSON
-- structure found in the 'content' column of the 'uniform_resource_transform' table.
-- The JSON is an array containing a single object, so all paths start with '$[0]'.

-- The following queries use json_extract() to retrieve specific values.
-- ----------------------------------------------------------------------------------

-- Query 1: Extract the main document title.
-- This query accesses the title at the top level of the document structure.
SELECT
  json_extract(CAST(content AS TEXT), '$[0].items[0].document[0].section.title') AS document_title
FROM uniform_resource_transform
WHERE json_valid(CAST(content AS TEXT));

-- Query 2: Extract a specific subheading title.
-- This query extracts the title of the first second-level section, "What is Opsfolio's SOC 2...".
SELECT
  json_extract(CAST(content AS TEXT), '$[0].items[0].document[0].section.body[0].section.title') AS first_subheading
FROM uniform_resource_transform
WHERE json_valid(CAST(content AS TEXT));

-- Query 3: Extract the paragraph text from a specific section.
-- This query gets the paragraph text under the "Why SOC 2 Matters for Business Growth" heading.
-- We must navigate through the nested arrays and objects to get to the text.
SELECT
  json_extract(CAST(content AS TEXT), '$[0].items[0].document[0].section.body[1].section.body[0].paragraph') AS enterprise_sales_paragraph
FROM uniform_resource_transform
WHERE json_valid(CAST(content AS TEXT));

-- Query 4: Extract a value from a nested list item.
-- This query gets the paragraph text of the first list item under the "Business Outcomes and ROI" section.
-- The path navigates into the 'body' array, then to the 'list' object, then to the 'item' array.
SELECT
  json_extract(CAST(content AS TEXT), '$[0].items[0].document[0].section.body[6].section.body[1].list[0].item[0].paragraph') AS first_list_item_text
FROM uniform_resource_transform
WHERE json_valid(CAST(content AS TEXT));

-- The following queries use json_tree() and json_each() to iterate over the JSON structure.
-- These are useful for dynamic queries or when you don't know the exact path.
-- ----------------------------------------------------------------------------------

-- Query 5: Find all paragraph texts and their paths using json_tree().
-- This is useful for debugging and exploring the entire JSON hierarchy.
SELECT
  json_tree.fullkey AS json_path,
  json_tree.value AS paragraph_text
FROM uniform_resource_transform,
     json_tree(CAST(content AS TEXT)) AS json_tree
WHERE json_tree.key = 'paragraph';

-- Query 6: Find all list items using json_each().
-- This query iterates over the 'list' array to find all of the bullet points
-- from the "Business Outcomes and ROI" section.
SELECT
  json_extract(json_each.value, '$.item[0].paragraph') AS business_outcome
FROM uniform_resource_transform,
     json_each(CAST(content AS TEXT), '$[0].items[0].document[0].section.body[6].section.body[1].list') AS json_each
WHERE json_valid(CAST(content AS TEXT));

-- The following queries demonstrate filtering and more complex joins.
-- ----------------------------------------------------------------------------------

-- Query 7: Find URIs for documents with a specific subheading.
-- This query uses json_tree to find documents where a section title contains a specific phrase.
SELECT
  T1.uri
FROM uniform_resource_transform AS T1,
     json_tree(CAST(T1.content AS TEXT), '$[0].items[0].document[0].section.body') AS T2
WHERE
  T2.key = 'section'
  AND json_extract(T2.value, '$.title') LIKE '%Proven SOC 2 Process%';