tem/session/tlsx_certificate.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 'title' AS component, (SELECT COALESCE(title, caption)
    FROM sqlpage_aide_navigation
   WHERE namespace = 'prime' AND path = 'tem/session/tlsx_certificate.sql/index.sql') as contents;
    ;

 --- Breadcrumb setup
SELECT 'breadcrumb' AS component;
     SELECT 'Home' AS title,
         sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/' AS link;
     SELECT 'Threat Exposure Management' AS title,
         sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/tem/index.sql' AS link;  
     SELECT 'Attack Surface Mapping By Session' AS title,
         sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/tem/attack_surface_mapping_session.sql' AS link;
     SELECT 'Findings' AS title,
         sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/tem/session/finding.sql?session_id=' || $session_id AS link;
     SELECT 'TLS Certificate Results' AS title,
         '#' AS link;

 --- Page title
 SELECT 'title' AS component,
     'TLS Certificate Results' AS contents;

 --- Page description
 SELECT 'text' AS component,
     'This page displays parsed TLS certificate scan results from tlsx JSONL data stored in uniform_resource.content.
     Each record includes host, IP, port, TLS version, cipher suite, subject/issuer details, fingerprints, and validity period.
     This helps assess TLS configuration, certificate trust, and potential misconfigurations.' AS contents;

 --- Table setup
 SELECT 'table' AS component,
     TRUE AS sort,
     TRUE AS search;

 SET total_rows = (SELECT COUNT(*) FROM tem_tlsx_certificate WHERE ur_ingest_session_id = $session_id);
SET limit = COALESCE($limit, 50);
SET offset = COALESCE($offset, 0);
SET total_pages = ($total_rows + $limit - 1) / $limit;
SET current_page = ($offset / $limit) + 1;
 SELECT
     tenant_name  AS "Tenant",
     strftime('%m-%d-%Y', observed_at) AS "Observed At",
     host                              AS "Host",
     ip_address                        AS "IP Address",
     port                              AS "Port",
     probe_status                      AS "Probe Status",
     tls_version                       AS "TLS Version",
     is_self_signed                    AS "Self-Signed",
     is_mismatched                     AS "Mismatched",
     strftime('%m-%d-%Y', valid_from)  AS "Valid From",
     strftime('%m-%d-%Y', valid_until) AS "Valid Until",
     serial_number                     AS "Serial",
     issuer_dn                         AS "Issuer DN",
     issuer_cn                         AS "Issuer CN",
     tls_connection                    AS "TLS Connection",
     sni                               AS "SNI"
 FROM tem_tlsx_certificate
 WHERE ur_ingest_session_id = $session_id;

 SELECT 'text' AS component,
    (SELECT CASE WHEN CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || COALESCE('&session_id=' || replace($session_id, ' ', '%20'), '') || ')' ELSE '' END)
    || ' '
    || '(Page ' || $current_page || ' of ' || $total_pages || ") "
    || (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || COALESCE('&session_id=' || replace($session_id, ' ', '%20'), '') || ')' ELSE '' END)
    AS contents_md
;
        ;