ai-context-engineering/ingest-health.sql

              SELECT 'dynamic' AS component, sqlpage.run_sql('shell/shell.sql') AS properties;
              -- not including breadcrumbs from sqlpage_aide_navigation
              -- not including page title from sqlpage_aide_navigation
              

              
      select
      'breadcrumb' as component;
     
      select
      'Home' as title,
      sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/' as link;
     
      select
      'AI Context Engineering Overview' as title,
      sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/ai-context-engineering/index.sql' as link;
     
      select
      'Ingest Health' as title;
     
      select 'title' AS component, 'Ingest Health Overview' AS contents;
      SELECT 'text' as component,
'This dashboard provides visibility into the health of the AI context ingestion pipeline. Use the metrics below to monitor the number of files ingested, processed for content, and validated for frontmatter. You can also filter recent ingests by time range.' as contents;

     
      -- === FILES SEEN ===
      select
      'big_number' as component,
      4 as columns,
      'colorfull_dashboard' as id;
     
      select
      'Files Seen' AS title,
      (SELECT COUNT(*)
      FROM uniform_resource
      WHERE deleted_at IS NULL
      ) as value,
      'green' as color,
      '/ai-context-engineering/file-list.sql' as value_link,
      TRUE as value_link_new_tab;
     
      -- === FILE WITH CONTENT ===
      SELECT
      'Files with Content' AS title,
      (
      SELECT COUNT(*)
      FROM ai_ctxe_uniform_resource_with_content
      ) AS value,
      'blue' AS color,
      '/ai-context-engineering/file-with-content-list.sql' as value_link,
      TRUE as value_link_new_tab;
     
      -- === FILES WITH FRONTMATTER ===
      SELECT
      'Files with Frontmatter' AS title,
      (
      SELECT COUNT(*)
      FROM ai_ctxe_uniform_resource_with_frontmatter
      ) AS value,
      'orange' AS color,
      '/ai-context-engineering/file-with-frontmatter-list.sql' as value_link;
 
      -- === FILES WITH VALID FRONTMATTER ===
      SELECT
      'Files with valid Frontmatter' AS title,
      (
      SELECT COUNT(*)
      FROM ai_ctxe_uniform_resource_transformed_resources_valid
      ) AS value,
      'green' AS color,
      '/ai-context-engineering/file-with-valid-frontmatter-list.sql' as value_link;
     
      -- === TIME RANGE SELECTION ===
      -- 1. Define the form
      SELECT 'form' AS component, TRUE AS auto_submit;
     
      SELECT
      'select' AS type,
      'time_range' AS name,
      'Select time range' AS label,
      'Select time range' AS empty_option,
      '[{"label": "Today", "value": "0"}, {"label": "7 day", "value": "1"}, {"label": "30 day", "value": "2"}]' AS options,
      :time_range AS value;
     
      -- 2. Define the table
      SELECT 'table' AS component,
      TRUE AS sort,
      TRUE AS search,
      'filename' AS markdown;
     
      -- 3. Query the filtered data
      SELECT
      '[' || filename || '](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/ai-context-engineering/time-range-file-detail.sql' || '?uniform_resource_id=' || uniform_resource_id || ')' AS "filename",
      uri AS "uri",
      nature AS "Nature",
      created_at AS "Created At"
      FROM ai_ctxe_uniform_resource_all_files
      WHERE
      :time_range IS NOT NULL
      AND :time_range != ''
      AND CASE
      WHEN :time_range = '0' THEN DATE(created_at) = DATE('now')
      WHEN :time_range = '1' THEN DATE(created_at) >= DATE('now', '-7 days')
      WHEN :time_range = '2' THEN DATE(created_at) >= DATE('now', '-30 days')
      ELSE 0 = 1
      END
      ORDER BY created_at DESC;