ai_ctxe_uniform_resource_all_files

Column	Type
uniform_resource_id	VARCHAR
uri	TEXT
filename	TEXT
nature	TEXT
created_at	TIMESTAMPTZ
created_by	TEXT
content	BLOB
frontmatter	TEXT
title
summary
body_text

SQL DDL

CREATE VIEW ai_ctxe_uniform_resource_all_files AS

SELECT 
    ur.uniform_resource_id,
    ur.uri,
    
   fs.file_basename AS filename,
    ur.nature,
    
    ur.created_at,
    ur.created_by,
    ur.content,
    ur.frontmatter,
    
    -- Extract title and summary from frontmatter JSON
    json_extract(ur.frontmatter, '$.title') AS title,
    json_extract(ur.frontmatter, '$.summary') AS summary,

    -- content with frontmatter stripped
    TRIM(
    CASE
      WHEN instr(ur.content, '---') = 1
        THEN substr(
          ur.content,
          instr(ur.content, '---') + 3 + instr(substr(ur.content, instr(ur.content, '---') + 3), '---') + 3
        )
      ELSE ur.content
    END
  ) AS body_text

FROM uniform_resource ur
LEFT JOIN
  ur_ingest_session_fs_path_entry fs
  ON fs.uniform_resource_id = ur.uniform_resource_id
WHERE ur.deleted_at IS NULL