Analyzing QRG Generation Runs

Overview

After kicking off a QRG generation request, you can analyze the results using the dedicated tracking tables and views. This guide shows practical queries for understanding what happened during generation.

Quick Request Summary

Get a high-level summary of a generation request:

DECLARE @RequestId UNIQUEIDENTIFIER = 'YOUR-REQUEST-ID';

SELECT 
    RequestId,
    RequestType,
    Status,
    CreatedAtUtc,
    CompletedAtUtc,
    DATEDIFF(SECOND, CreatedAtUtc, CompletedAtUtc) AS DurationSeconds,
    ErrorMessage,
    TotalAnnexesProcessed,
    TotalQrgsGenerated,
    WorkflowRunId
FROM K12SAFETY.qrg_requests
WHERE RequestId = @RequestId;

Annex Processing Status

See which annexes were processed, skipped, or failed:

DECLARE @RequestId UNIQUEIDENTIFIER = 'YOUR-REQUEST-ID';

SELECT
    AnnexName,
    QrgGenerationStatus,       -- 'Generated', 'Skipped', 'Failed', 'No Output'
    SkipOrFailureReason,       -- Why it didn't generate (if applicable)
    QrgsGeneratedFromAnnex,    -- Number of QRGs created from this annex
    ClassifiedCategory,        -- What category the AI detected
    ClassifiedType,            -- FUNCTIONAL or EMERGENCY
    AnnexProcessingStatus,     -- success/error/skipped
    ProcessedAtUtc
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = @RequestId
GROUP BY 
    AnnexName, 
    QrgGenerationStatus, 
    SkipOrFailureReason, 
    QrgsGeneratedFromAnnex,
    ClassifiedCategory,
    ClassifiedType,
    AnnexProcessingStatus,
    ProcessedAtUtc
ORDER BY 
    CASE QrgGenerationStatus 
        WHEN 'Skipped' THEN 1 
        WHEN 'Failed' THEN 2 
        WHEN 'No Output' THEN 3 
        ELSE 4 
    END,
    AnnexName;

Understanding Skip Reasons

Common skip reasons include:

Reason Pattern	Meaning	Action
Form has no state.data	Form object missing state.data property	Check EOP annex form structure
Form state.data is empty (0 fields)	Form exists but has no fields	Annex may be a placeholder or template
Form content is empty after cleaning	All fields were HTML-only (no text)	Annex needs actual content, not just formatting
Form content too short (<50 chars)	Minimal data detected	Annex likely incomplete or placeholder

Generated QRG Details

See all QRGs that were successfully generated:

DECLARE @RequestId UNIQUEIDENTIFIER = 'YOUR-REQUEST-ID';

SELECT
    AnnexName,
    QrgName,
    QrgType,
    Functionality,
    Hazard,
    AssignedRoleIdsJson,
    LEN(QrgContentBlocksJson) AS ContentLength,
    QrgCreatedAt
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = @RequestId
    AND QrgGenerationStatus = 'Generated'
ORDER BY AnnexName, QrgName;

AI Processing Pipeline Analysis

Track what the AI extracted and classified for each annex:

DECLARE @RequestId UNIQUEIDENTIFIER = 'YOUR-REQUEST-ID';

SELECT
    AnnexName,
    ClassifiedCategory,
    ClassifiedType,
    
    -- Extract group names from JSON array
    JSON_QUERY(ExtractedGroupsJson, '$') AS ExtractedGroups,
    
    -- Show role mappings
    JSON_QUERY(RoleMappingsJson, '$') AS RoleMappings,
    
    -- Metrics
    QrgsGeneratedFromAnnex,
    FormDataCategory,          -- SUBSTANTIAL_FORM_DATA, MODERATE_FORM_DATA, etc.
    FormContentLength
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = @RequestId
GROUP BY 
    AnnexName,
    ClassifiedCategory,
    ClassifiedType,
    ExtractedGroupsJson,
    RoleMappingsJson,
    QrgsGeneratedFromAnnex,
    FormDataCategory,
    FormContentLength
ORDER BY AnnexName;

Comparing Input to Output

Understand the transformation from form data to generated QRGs:

DECLARE @RequestId UNIQUEIDENTIFIER = 'YOUR-REQUEST-ID';

SELECT
    AnnexName,
    FormId,
    
    -- Input metrics
    FormDataCategory,
    FormContentLength,
    
    -- Processing results
    ClassifiedCategory,
    ExtractedGroupCount,
    MappedRoleCount,
    
    -- Output metrics
    QrgsGeneratedFromAnnex,
    ContentBlocksLength,
    
    -- Relationship
    InputOutputRelationship,
    IsSuccessfulTransformation
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = @RequestId
GROUP BY 
    AnnexName,
    FormId,
    FormDataCategory,
    FormContentLength,
    ClassifiedCategory,
    ExtractedGroupCount,
    MappedRoleCount,
    QrgsGeneratedFromAnnex,
    ContentBlocksLength,
    InputOutputRelationship,
    IsSuccessfulTransformation
ORDER BY IsSuccessfulTransformation DESC, AnnexName;

AI Call Tracing

See detailed AI agent invocations for debugging:

DECLARE @RequestId UNIQUEIDENTIFIER = 'YOUR-REQUEST-ID';

SELECT
    AnnexId,
    StepType,                  -- CLASSIFY_CATEGORY, EXTRACT_GROUPS, MAP_ROLES, GENERATE_CONTENT
    StepSequence,
    Status,                    -- success/error
    ModelDeployment,
    PromptTokens,
    CompletionTokens,
    TotalTokens,
    DurationMs,
    ErrorMessage,
    StartedAtUtc
FROM K12SAFETY.qrg_ai_calls
WHERE RequestId = @RequestId
ORDER BY AnnexId, StepSequence;

AI Step Types

StepType	Purpose	Expected Output
CLASSIFY_CATEGORY	Categorize annex (Evacuation, Fire Safety, etc.)	Category string
EXTRACT_GROUPS	Extract role groups from content	JSON array of group names
MAP_ROLES	Map extracted groups to actual role IDs	JSON object mapping groups to roles
GENERATE_CONTENT	Create QRG content for a specific role	HTML content blocks

Troubleshooting Common Issues

No QRGs Generated

If GeneratedQrgCount = 0:

DECLARE @RequestId UNIQUEIDENTIFIER = 'YOUR-REQUEST-ID';

SELECT
    AnnexName,
    AnnexProcessingStatus,
    AnnexErrorMessage,
    ClassifiedCategory,
    ExtractedGroupsJson,
    RoleMappingsJson,
    FormContentLength
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = @RequestId
    AND (AnnexProcessingStatus != 'success' OR QrgsGeneratedFromAnnex = 0)
ORDER BY AnnexName;

Common causes:

• Skipped: Form content empty or too short
• No groups extracted: AI couldn't identify roles in the content
• No role mappings: Extracted groups didn't match any available roles (QRGs may still be generated with empty generalUserTagIds)
• Category classification failed: Couldn't determine annex type

Partial Success (Some Annexes Skipped)

DECLARE @RequestId UNIQUEIDENTIFIER = 'YOUR-REQUEST-ID';

SELECT
    QrgGenerationStatus,
    COUNT(*) AS AnnexCount,
    STRING_AGG(AnnexName, ', ') AS AnnexNames
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = @RequestId
GROUP BY QrgGenerationStatus
ORDER BY 
    CASE QrgGenerationStatus 
        WHEN 'Generated' THEN 4 
        WHEN 'Skipped' THEN 1 
        WHEN 'Failed' THEN 2 
        ELSE 3 
    END;

High Token Usage

Identify which annexes consumed the most tokens:

DECLARE @RequestId UNIQUEIDENTIFIER = 'YOUR-REQUEST-ID';

SELECT
    ann.AnnexId,
    ann.AnnexName,
    SUM(ai.PromptTokens) AS TotalPromptTokens,
    SUM(ai.CompletionTokens) AS TotalCompletionTokens,
    SUM(ai.TotalTokens) AS TotalTokens,
    COUNT(*) AS AiCallCount,
    ann.GeneratedQrgCount
FROM K12SAFETY.qrg_generation_annexes ann
LEFT JOIN K12SAFETY.qrg_ai_calls ai ON ai.AnnexId = ann.AnnexId
WHERE ann.RequestId = @RequestId
GROUP BY ann.AnnexId, ann.AnnexName, ann.GeneratedQrgCount
ORDER BY TotalTokens DESC;

Performance Analysis

Processing Time per Annex

DECLARE @RequestId UNIQUEIDENTIFIER = 'YOUR-REQUEST-ID';

SELECT
    AnnexName,
    QrgsGeneratedFromAnnex,
    DATEDIFF(SECOND, 
        MIN(ai.StartedAtUtc), 
        MAX(ai.CompletedAtUtc)
    ) AS ProcessingDurationSeconds,
    COUNT(ai.Id) AS AiCallCount,
    SUM(ai.DurationMs) AS TotalAiDurationMs
FROM K12SAFETY.qrg_generation_annexes ann
LEFT JOIN K12SAFETY.qrg_ai_calls ai ON ai.AnnexId = ann.AnnexId
WHERE ann.RequestId = @RequestId
GROUP BY ann.AnnexName, ann.QrgsGeneratedFromAnnex
ORDER BY ProcessingDurationSeconds DESC;

Export Generated QRGs

Get the final QRG output for publishing:

DECLARE @RequestId UNIQUEIDENTIFIER = 'YOUR-REQUEST-ID';

SELECT
    GeneratedItemId,
    QrgName,
    QrgType,
    Functionality,
    Hazard,
    GeneralUserTagIdsJson AS AssignedRoleIds,
    ContentBlocksJson AS QrgHtmlContent,
    EmergencyOperationPlanId,
    CreatedAtUtc
FROM K12SAFETY.qrg_generated_items
WHERE RequestId = @RequestId
ORDER BY QrgName;

Workflow Execution Errors

Check for unexpected workflow-level errors:

DECLARE @RequestId UNIQUEIDENTIFIER = 'YOUR-REQUEST-ID';

SELECT
    ErrorType,
    ErrorCategory,
    ErrorMessage,
    FailedStep,
    OccurredAtUtc,
    AnnexId
FROM K12SAFETY.qrg_execution_errors
WHERE RequestId = @RequestId
ORDER BY OccurredAtUtc;

Best Practices

1. Start with the summary query to understand overall request status
2. Check annex processing status to identify skips and failures
3. Use AI call tracing when debugging specific content issues
4. Compare input/output metrics to validate transformations
5. Export generated QRGs only after verifying success status

Related Documentation

• QRG Database Schema - Table and column details
• QRG Generation Workflow - Workflow architecture
• QRG Tools - API endpoints for generation