K12 Views: QRG Traceability & Analysis

Overview

The K12 QRG system provides five analytical views that join normalized tables to answer common questions about request lifecycle, input-to-output traceability, AI pipeline performance, and error patterns.

View	Purpose	Key Use Cases
`vw_qrg_full_traceability`	Complete request lineage	"Show me everything for this request"
`vw_qrg_input_output_trace`	Form → QRG traceability with metrics	"Which forms generated which QRGs?"
`vw_qrg_ai_call_summary`	Aggregated AI statistics per request	"Token usage and performance analysis"
`vw_qrg_annex_ai_details`	Annex-level AI processing details	"What AI calls happened for this form?"
`vw_qrg_error_summary`	Errors with full context	"Recent errors and failure patterns"

`vw_qrg_full_traceability`

Purpose: Join requests → generations → annexes → generated items for complete lineage.

What It Provides:

Request identification (RequestId, SessionId, WorkflowRunId, status)
Generation metadata (EmergencyOperationPlanId, annex count, QRG count)
Annex input data (FormJson, classification results, extracted groups, role mappings)
Generated QRG output (names, types, functionality, hazard, content blocks)
Processing status (success/error per annex)

Schema:

CREATE VIEW [K12SAFETY].[vw_qrg_full_traceability] AS
SELECT
    -- Request identification
    r.RequestId,
    r.SessionId,
    r.WorkflowRunId,
    r.Status AS RequestStatus,
    r.CreatedAtUtc AS RequestCreatedAt,
    
    -- Generation metadata
    g.EmergencyOperationPlanId,
    g.GeneralUserTagManagementType,
    g.AnnexCount,
    g.QrgCount AS TotalQrgCount,
    
    -- Annex input
    a.Id AS GenerationAnnexId,
    a.AnnexId,
    a.AnnexName,
    a.AnnexFormJson,
    
    -- AI classification output
    a.ClassifiedCategory,
    a.ClassifiedType,
    
    -- AI group extraction output
    a.ExtractedGroupsJson,
    
    -- AI role mapping output
    a.RoleMappingsJson,
    
    -- Generated QRG item
    gi.GeneratedItemId,
    gi.QrgName,
    gi.QrgType,
    gi.Functionality,
    gi.Hazard,
    gi.GeneralUserTagIdsJson,
    gi.ContentBlocksJson,
    
    -- Annex processing status
    a.ProcessingStatus AS AnnexProcessingStatus,
    a.ErrorMessage AS AnnexErrorMessage
    
FROM K12SAFETY.qrg_requests r
LEFT JOIN K12SAFETY.qrg_generations g ON r.RequestId = g.RequestId
LEFT JOIN K12SAFETY.qrg_generation_annexes a ON r.RequestId = a.RequestId
LEFT JOIN K12SAFETY.qrg_generated_items gi ON r.RequestId = gi.RequestId
    AND gi.AnnexId = a.AnnexId
WHERE r.RequestType = 'GENERATION';

Example Queries:

-- Complete view of request processing
SELECT 
    RequestId, AnnexName, ClassifiedCategory, 
    QrgName, QrgType, Functionality
FROM K12SAFETY.vw_qrg_full_traceability
WHERE RequestId = '103692F8-60FD-4BD6-AA5E-9A9A18840CE3'
ORDER BY AnnexName, QrgName;

-- Count QRGs by category
SELECT 
    ClassifiedCategory,
    COUNT(DISTINCT GeneratedItemId) AS QrgCount,
    COUNT(DISTINCT AnnexId) AS AnnexCount
FROM K12SAFETY.vw_qrg_full_traceability
WHERE RequestId = @requestId
GROUP BY ClassifiedCategory;

-- Show AI pipeline results per annex
SELECT 
    AnnexName,
    ClassifiedCategory,
    ClassifiedType,
    ExtractedGroupsJson,
    RoleMappingsJson,
    COUNT(GeneratedItemId) AS QrgsGenerated
FROM K12SAFETY.vw_qrg_full_traceability
WHERE RequestId = @requestId
GROUP BY AnnexName, ClassifiedCategory, ClassifiedType, 
         ExtractedGroupsJson, RoleMappingsJson;

DDL: db/sqlproj/Mastra.Platform.K12SafetyDb/model/Views/K12SAFETY.vw_qrg_full_traceability.sql

`vw_qrg_input_output_trace`

Purpose: Complete form-to-QRG lineage with detailed metrics and analysis.

What It Provides:

Request Context — RequestId, Status, WorkflowRunId, timestamps, EOP ID
Input Data (Forms) — FormId, FormName, FormStatus, RawFormDataJson, FormContentJson, input metrics
AI Processing Pipeline — Classification, extracted groups, role mappings, processing status
Output Data (QRGs) — QRG names, types, content blocks, role assignments, output metrics
Input-Output Analysis — Data categories, transformation relationships, success flags
Error Context — Workflow errors linked to specific forms

Key Analysis Columns:

Column	Type	Purpose
`FormDataCategory`	VARCHAR	Classifies input size: NO_FORM_DATA, EMPTY_FORM_OBJECT, MINIMAL_FORM_DATA, MODERATE_FORM_DATA, SUBSTANTIAL_FORM_DATA
`InputOutputRelationship`	VARCHAR	Status: INPUT_USED_SUCCESSFULLY, INPUT_PROCESSED_NO_OUTPUT, INPUT_PROCESSING_FAILED, INPUT_SKIPPED
`IsEmptyInProgressForm`	BIT	Flag for forms with no data still in progress (should error)
`IsSuccessfulTransformation`	BIT	Form data successfully became QRG
`RawFormDataLength`	INT	Size of input form JSON
`FormContentLength`	INT	Size of extracted content
`QrgContentBlocksLength`	INT	Size of generated QRG content
`QrgsGeneratedFromAnnex`	INT	Count of QRGs from this form

Example Queries:

-- Complete input-to-output lineage for request
SELECT 
    RequestId, AnnexName, FormStatus, FormDataCategory,
    QrgsGeneratedFromAnnex, QrgName, QrgType,
    InputOutputRelationship, IsSuccessfulTransformation
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = '103692F8-60FD-4BD6-AA5E-9A9A18840CE3'
ORDER BY AnnexRecordId, GeneratedItemId;

-- Correlate input data size with output success
SELECT 
    FormDataCategory,
    COUNT(*) AS TotalForms,
    SUM(CASE WHEN IsSuccessfulTransformation = 1 THEN 1 ELSE 0 END) AS SuccessfulForms,
    CAST(SUM(CASE WHEN IsSuccessfulTransformation = 1 THEN 1 ELSE 0 END) * 100.0 
         / COUNT(*) AS DECIMAL(5,2)) AS SuccessRate,
    AVG(QrgsGeneratedFromAnnex) AS AvgQrgsPerForm,
    AVG(RawFormDataLength) AS AvgInputSize
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = @requestId
GROUP BY FormDataCategory
ORDER BY TotalForms DESC;

-- Identify empty forms that caused errors
SELECT 
    AnnexName, FormId, FormStatus, FormDataCategory,
    RawFormDataLength, AnnexProcessingStatus, 
    AnnexErrorMessage, InputOutputRelationship
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = @requestId
  AND (IsEmptyInProgressForm = 1 
       OR FormDataCategory IN ('NO_FORM_DATA', 'EMPTY_FORM_OBJECT'))
GROUP BY AnnexName, FormId, FormStatus, FormDataCategory, 
         RawFormDataLength, AnnexProcessingStatus, 
         AnnexErrorMessage, InputOutputRelationship;

-- Compare input JSON to output JSON
SELECT 
    AnnexName, FormId,
    RawFormDataJson,           -- What came in from MOEOP
    ClassifiedCategory,        -- AI classification
    ExtractedGroupsJson,       -- AI extraction
    RoleMappingsJson,          -- AI mappings
    QrgName,
    QrgContentBlocksJson       -- What went out
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = @requestId
ORDER BY AnnexRecordId, GeneratedItemId;

-- Success rate by form status
SELECT 
    FormStatus,
    COUNT(*) AS TotalForms,
    SUM(CASE WHEN IsSuccessfulTransformation = 1 THEN 1 ELSE 0 END) AS SuccessCount,
    SUM(CASE WHEN InputOutputRelationship = 'INPUT_PROCESSING_FAILED' THEN 1 ELSE 0 END) AS FailureCount
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = @requestId
GROUP BY FormStatus;

Pre-built Query Examples:
See db/sqlproj/Mastra.Platform.K12SafetyDb/docs/query-examples-input-output-trace.sql for 12 comprehensive queries.

DDL: db/sqlproj/Mastra.Platform.K12SafetyDb/model/Views/K12SAFETY.vw_qrg_input_output_trace.sql

`vw_qrg_ai_call_summary`

Purpose: Aggregated AI call statistics per request (token usage, timing, success rates).

What It Provides:

Call counts by step type (classify, extract, map, generate)
Success vs error counts
Token totals (prompt, completion, total)
Timing metrics (total, average, max duration)
Request context (RequestId, SessionId, Status)

Schema:

CREATE VIEW [K12SAFETY].[vw_qrg_ai_call_summary] AS
SELECT
    r.RequestId,
    r.SessionId,
    r.Status AS RequestStatus,
    
    -- Call counts by type
    COUNT(CASE WHEN ac.StepType = 'CLASSIFY_CATEGORY' THEN 1 END) AS ClassifyCalls,
    COUNT(CASE WHEN ac.StepType = 'EXTRACT_GROUPS' THEN 1 END) AS ExtractGroupsCalls,
    COUNT(CASE WHEN ac.StepType = 'MAP_ROLES' THEN 1 END) AS MapRolesCalls,
    COUNT(CASE WHEN ac.StepType = 'GENERATE_CONTENT' THEN 1 END) AS GenerateContentCalls,
    COUNT(ac.Id) AS TotalCalls,
    
    -- Success/error counts
    COUNT(CASE WHEN ac.Status = 'success' THEN 1 END) AS SuccessfulCalls,
    COUNT(CASE WHEN ac.Status = 'error' THEN 1 END) AS FailedCalls,
    
    -- Token totals
    SUM(ac.PromptTokens) AS TotalPromptTokens,
    SUM(ac.CompletionTokens) AS TotalCompletionTokens,
    SUM(ac.TotalTokens) AS TotalTokens,
    
    -- Timing
    SUM(ac.DurationMs) AS TotalDurationMs,
    AVG(ac.DurationMs) AS AvgDurationMs,
    MAX(ac.DurationMs) AS MaxDurationMs,
    
    r.CreatedAtUtc
    
FROM K12SAFETY.qrg_requests r
LEFT JOIN K12SAFETY.qrg_ai_calls ac ON r.RequestId = ac.RequestId
WHERE r.RequestType = 'GENERATION'
GROUP BY r.RequestId, r.SessionId, r.Status, r.CreatedAtUtc;

Example Queries:

-- AI call summary for request
SELECT 
    RequestId, TotalCalls, TotalTokens, TotalDurationMs,
    SuccessfulCalls, FailedCalls,
    ClassifyCalls, ExtractGroupsCalls, MapRolesCalls, GenerateContentCalls
FROM K12SAFETY.vw_qrg_ai_call_summary
WHERE RequestId = @requestId;

-- Token cost analysis (last 7 days)
SELECT 
    RequestId,
    TotalCalls,
    TotalTokens,
    CAST(TotalTokens * 0.00002 AS DECIMAL(10,4)) AS EstimatedCostUSD,
    TotalDurationMs / 1000.0 AS TotalSeconds
FROM K12SAFETY.vw_qrg_ai_call_summary
WHERE CreatedAtUtc >= DATEADD(day, -7, GETUTCDATE())
ORDER BY TotalTokens DESC;

-- Performance comparison
SELECT 
    AVG(TotalCalls) AS AvgCalls,
    AVG(TotalTokens) AS AvgTokens,
    AVG(TotalDurationMs) AS AvgDurationMs,
    MAX(TotalTokens) AS MaxTokens,
    MIN(TotalTokens) AS MinTokens
FROM K12SAFETY.vw_qrg_ai_call_summary
WHERE RequestStatus = 'success'
  AND CreatedAtUtc >= DATEADD(day, -7, GETUTCDATE());

-- Success rate by request
SELECT 
    RequestId,
    TotalCalls,
    SuccessfulCalls,
    FailedCalls,
    CAST(SuccessfulCalls * 100.0 / NULLIF(TotalCalls, 0) AS DECIMAL(5,2)) AS SuccessRate
FROM K12SAFETY.vw_qrg_ai_call_summary
WHERE TotalCalls > 0
ORDER BY FailedCalls DESC;

DDL: db/sqlproj/Mastra.Platform.K12SafetyDb/model/Views/K12SAFETY.vw_qrg_ai_call_summary.sql

`vw_qrg_annex_ai_details`

Purpose: Annex-level AI processing details (prompts, responses, tokens, timing).

What It Provides:

AI call identification (AiCallId, StepType, StepSequence)
Model information (Provider, Deployment)
Prompts and responses (truncated previews)
Token breakdown per call
Timing per call
Error messages and retry counts
Annex context (AnnexId, AnnexName)

Schema:

CREATE VIEW [K12SAFETY].[vw_qrg_annex_ai_details] AS
SELECT
    ac.RequestId,
    ac.AnnexId,
    a.AnnexName,
    
    -- AI call details
    ac.Id AS AiCallId,
    ac.StepType,
    ac.StepSequence,
    ac.Status AS CallStatus,
    
    -- Model info
    ac.ModelProvider,
    ac.ModelDeployment,
    
    -- Prompts (truncated for overview)
    LEFT(ac.SystemPrompt, 200) AS SystemPromptPreview,
    LEFT(ac.UserPrompt, 500) AS UserPromptPreview,
    
    -- Response (truncated for overview)
    LEFT(ac.ResponseRaw, 500) AS ResponsePreview,
    
    -- Tokens
    ac.PromptTokens,
    ac.CompletionTokens,
    ac.TotalTokens,
    
    -- Timing
    ac.StartedAtUtc,
    ac.CompletedAtUtc,
    ac.DurationMs,
    
    -- Errors
    ac.ErrorMessage,
    ac.RetryCount
    
FROM K12SAFETY.qrg_ai_calls ac
LEFT JOIN K12SAFETY.qrg_generation_annexes a 
    ON ac.RequestId = a.RequestId AND ac.AnnexId = a.AnnexId;

Example Queries:

-- AI calls for specific annex
SELECT 
    AnnexName, StepType, CallStatus, 
    PromptTokens, CompletionTokens, DurationMs,
    StartedAtUtc
FROM K12SAFETY.vw_qrg_annex_ai_details
WHERE RequestId = @requestId 
  AND AnnexName LIKE '%Evacuation%'
ORDER BY StartedAtUtc;

-- Token breakdown by annex
SELECT 
    AnnexName,
    COUNT(*) AS TotalCalls,
    SUM(TotalTokens) AS TotalTokens,
    SUM(DurationMs) AS TotalMs,
    STRING_AGG(StepType, ' → ') WITHIN GROUP (ORDER BY StartedAtUtc) AS Pipeline
FROM K12SAFETY.vw_qrg_annex_ai_details
WHERE RequestId = @requestId
GROUP BY AnnexName
ORDER BY TotalTokens DESC;

-- Failed AI calls with details
SELECT 
    AnnexName, StepType, StepSequence,
    ErrorMessage, RetryCount,
    UserPromptPreview, ResponsePreview
FROM K12SAFETY.vw_qrg_annex_ai_details
WHERE RequestId = @requestId 
  AND CallStatus = 'error'
ORDER BY StartedAtUtc;

-- AI pipeline timing per annex
SELECT 
    AnnexName,
    StepType,
    AVG(DurationMs) AS AvgMs,
    MIN(DurationMs) AS MinMs,
    MAX(DurationMs) AS MaxMs
FROM K12SAFETY.vw_qrg_annex_ai_details
WHERE RequestId = @requestId
  AND CallStatus = 'success'
GROUP BY AnnexName, StepType
ORDER BY AnnexName, AvgMs DESC;

DDL: db/sqlproj/Mastra.Platform.K12SafetyDb/model/Views/K12SAFETY.vw_qrg_annex_ai_details.sql

`vw_qrg_error_summary`

Purpose: Errors joined with annex and request context for quick analysis.

What It Provides:

Error details (Type, Category, Message)
Failed step identification
Form context (FormId, FormName)
Annex context (AnnexName)
Request status
Timestamp

Schema:

CREATE VIEW [K12SAFETY].[vw_qrg_error_summary] AS
SELECT
    e.OccurredAtUtc,
    e.RequestId,
    a.AnnexName,
    e.ErrorType,
    e.ErrorCategory,
    e.ErrorMessage,
    e.FailedStep,
    e.FormId,
    e.FormName,
    r.Status AS RequestStatus
FROM K12SAFETY.qrg_execution_errors e
LEFT JOIN K12SAFETY.qrg_generation_annexes a 
    ON e.RequestId = a.RequestId AND e.AnnexId = a.AnnexId
LEFT JOIN K12SAFETY.qrg_requests r ON e.RequestId = r.RequestId;

Example Queries:

-- Recent errors with context
SELECT TOP 50 
    OccurredAtUtc, RequestId, AnnexName,
    ErrorType, ErrorCategory, ErrorMessage, FailedStep
FROM K12SAFETY.vw_qrg_error_summary
ORDER BY OccurredAtUtc DESC;

-- Errors for specific request
SELECT 
    OccurredAtUtc, AnnexName, ErrorType, 
    ErrorMessage, FailedStep
FROM K12SAFETY.vw_qrg_error_summary
WHERE RequestId = @requestId
ORDER BY OccurredAtUtc;

-- Error frequency by type
SELECT 
    ErrorType,
    ErrorCategory,
    COUNT(*) AS ErrorCount,
    COUNT(DISTINCT RequestId) AS AffectedRequests,
    MIN(OccurredAtUtc) AS FirstSeen,
    MAX(OccurredAtUtc) AS LastSeen
FROM K12SAFETY.vw_qrg_error_summary
WHERE OccurredAtUtc >= DATEADD(day, -30, GETUTCDATE())
GROUP BY ErrorType, ErrorCategory
ORDER BY ErrorCount DESC;

-- Forms with repeated errors
SELECT 
    FormId, FormName,
    COUNT(*) AS FailureCount,
    STRING_AGG(ErrorType, ', ') AS ErrorTypes,
    MAX(OccurredAtUtc) AS LastFailure
FROM K12SAFETY.vw_qrg_error_summary
WHERE FormId IS NOT NULL
GROUP BY FormId, FormName
HAVING COUNT(*) > 2
ORDER BY FailureCount DESC;

-- Error patterns by failed step
SELECT 
    FailedStep,
    ErrorCategory,
    COUNT(*) AS ErrorCount,
    STRING_AGG(DISTINCT ErrorType, ', ') AS ErrorTypes
FROM K12SAFETY.vw_qrg_error_summary
WHERE OccurredAtUtc >= DATEADD(day, -7, GETUTCDATE())
GROUP BY FailedStep, ErrorCategory
ORDER BY ErrorCount DESC;

DDL: db/sqlproj/Mastra.Platform.K12SafetyDb/model/Views/K12SAFETY.vw_qrg_error_summary.sql

View Usage Patterns

Complete Request Analysis

-- Combine all views for comprehensive request analysis
WITH RequestSummary AS (
    SELECT * FROM K12SAFETY.vw_qrg_ai_call_summary WHERE RequestId = @requestId
),
ErrorSummary AS (
    SELECT COUNT(*) AS ErrorCount 
    FROM K12SAFETY.vw_qrg_error_summary 
    WHERE RequestId = @requestId
)
SELECT 
    rs.RequestId,
    rs.TotalCalls,
    rs.TotalTokens,
    rs.TotalDurationMs / 1000.0 AS TotalSeconds,
    rs.SuccessfulCalls,
    rs.FailedCalls,
    es.ErrorCount AS WorkflowErrors
FROM RequestSummary rs
CROSS JOIN ErrorSummary es;

Input-Output Analysis

-- Analyze transformation success by input characteristics
SELECT 
    FormStatus,
    FormDataCategory,
    COUNT(*) AS TotalForms,
    SUM(CASE WHEN IsSuccessfulTransformation = 1 THEN 1 ELSE 0 END) AS SuccessForms,
    AVG(RawFormDataLength) AS AvgInputSize,
    AVG(QrgsGeneratedFromAnnex) AS AvgQrgsPerForm
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = @requestId
GROUP BY FormStatus, FormDataCategory
ORDER BY TotalForms DESC;

Performance Benchmarking

-- Compare performance across requests
SELECT 
    r.RequestId,
    g.AnnexCount,
    g.QrgCount,
    acs.TotalCalls,
    acs.TotalTokens,
    acs.TotalDurationMs,
    (SELECT COUNT(*) FROM K12SAFETY.vw_qrg_error_summary e WHERE e.RequestId = r.RequestId) AS ErrorCount
FROM K12SAFETY.qrg_requests r
JOIN K12SAFETY.qrg_generations g ON r.RequestId = g.RequestId
LEFT JOIN K12SAFETY.vw_qrg_ai_call_summary acs ON r.RequestId = acs.RequestId
WHERE r.Status = 'success'
  AND r.CreatedAtUtc >= DATEADD(day, -7, GETUTCDATE())
ORDER BY acs.TotalTokens DESC;

Tables: K12 QRG Storage
Query Examples: db/sqlproj/Mastra.Platform.K12SafetyDb/docs/query-examples-input-output-trace.sql
DDL Files: db/sqlproj/Mastra.Platform.K12SafetyDb/model/Views/K12SAFETY.vw_*.sql

Overview​

vw_qrg_full_traceability​

vw_qrg_input_output_trace​

vw_qrg_ai_call_summary​

vw_qrg_annex_ai_details​

vw_qrg_error_summary​

View Usage Patterns​

Complete Request Analysis​

Input-Output Analysis​

Performance Benchmarking​

Related Documentation​

Overview

`vw_qrg_full_traceability`

`vw_qrg_input_output_trace`

`vw_qrg_ai_call_summary`

`vw_qrg_annex_ai_details`

`vw_qrg_error_summary`

View Usage Patterns

Complete Request Analysis

Input-Output Analysis

Performance Benchmarking

Related Documentation