ai_ctxe_uniform_resource_merge_group_risks

Column	Type
uniform_resource_id	VARCHAR
uri	TEXT
created_at	TIMESTAMPTZ
body_text
nature	TEXT
merge_group
merge_order
filename	TEXT

SQL DDL

CREATE VIEW ai_ctxe_uniform_resource_merge_group_risks AS

SELECT
    ur.uniform_resource_id,
    ur.uri,
    ur.created_at,
    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,
    ur.nature,
    json_extract(ur.frontmatter, '$.merge-group') AS merge_group,
    json_extract(ur.frontmatter, '$.order') AS merge_order,
    fs.file_basename AS filename
FROM uniform_resource ur
JOIN ur_ingest_session_fs_path_entry fs
  ON fs.uniform_resource_id = ur.uniform_resource_id
WHERE ur.deleted_at IS NULL
  AND (
        json_extract(ur.frontmatter, '$.merge-group') IS NULL
     OR (
        json_extract(ur.frontmatter, '$.merge-group') IS NOT NULL
        AND json_extract(ur.frontmatter, '$.order') IS NULL
     )
  )