K12 Views: Request Traceability

Overview

The K12 traceability views provide end-to-end visibility into how requests are processed, including the intermediate context resolution steps that happen before AI generation. These views are essential for debugging and auditing.

View	Workflow	Purpose	Key Use Cases
vw_eop_request_traceability	EOP Annex Editing	Complete request lineage with context resolution	"What organization/role context was resolved for this request?"
vw_qrg_edit_traceability	QRG Editing	Edit request context and outcome tracking	"Was the edit context complete? What was the outcome?"

---

vw_eop_request_traceability

Purpose: Complete traceability from EOP annex editing request to response, including all intermediate context resolution steps.

What It Answers:

1. Input Identification — "What EOP ID and plan ID were provided?"
2. Context Resolution — "Was snapshot/organization/role data successfully resolved?"
3. Role Resolution Details — "How were role tags resolved? From snapshot or organization?"
4. Prompt Usage — "What system prompt was used?"
5. Overall Status — "Did the request succeed end-to-end?"

Key Analysis Columns:

Column	Type	Purpose
ContextResolutionStatus	VARCHAR	Overall context resolution: success, partial, failed
RoleResolutionStatus	VARCHAR	Role-specific resolution: success, partial_resolution, no_tags, failed
RoleResolutionSource	VARCHAR	Where role tags came from: snapshot_tags, org_tags, none
OverallStatus	VARCHAR	Combined request + context status for quick filtering
HasSnapshotContext	BIT	Flag: snapshot data was found
HasOrganizationContext	BIT	Flag: organization data was found
HasResolvedRoleTags	BIT	Flag: role tags were successfully resolved
RoleIdRequestedButNotResolved	BIT	Warning flag: roleId provided but no tags found

Schema:

CREATE VIEW [K12SAFETY].[vw_eop_request_traceability] AS
SELECT
    -- Request Identification
    req.ID AS RequestRecordId,
    req.RequestID,
    req.SessionID,
    req.WorkflowRunId,
    req.TaskName,

    -- Request Status
    req.ResponseStatus,
    req.ResponseMessage,
    req.ResponseIsEdit,

    -- Input IDs (what was provided)
    req.EmergencyOperationPlanId AS InputEopId,
    req.RoleId AS InputRoleId,
    ctx.InputPlanId,

    -- Resolved Context (what was found)
    ctx.ResolvedPlanId,
    ctx.SnapshotId,
    ctx.SnapshotCreatedAt,
    ctx.ConfigurationId,
    ctx.OrganizationId,
    ctx.OrganizationName,
    ctx.OrganizationType,

    -- Role Resolution Details
    ctx.RoleResolutionSource,
    ctx.SnapshotGeneralUserTagIds,
    ctx.OrgGeneralUserTagIds,
    ctx.ResolvedRoleTagsJson,
    ctx.ResolvedRoleCount,

    -- Context Resolution Status
    ctx.ContextResolutionStatus,
    ctx.ContextResolutionError,

    -- Prompt Details
    prm.PromptHash AS SystemPromptHash,
    LEN(prm.SystemPromptOverrideUsed) AS SystemPromptLength,
    prm.MetaJson AS PromptMetadataJson,

    -- Context Completeness Flags
    CASE WHEN ctx.SnapshotId IS NOT NULL THEN 1 ELSE 0 END AS HasSnapshotContext,
    CASE WHEN ctx.OrganizationId IS NOT NULL THEN 1 ELSE 0 END AS HasOrganizationContext,
    CASE WHEN ctx.ResolvedRoleTagsJson IS NOT NULL
         AND ctx.ResolvedRoleTagsJson <> '[]' THEN 1 ELSE 0 END AS HasResolvedRoleTags,
    CASE WHEN req.RoleId IS NOT NULL
         AND ctx.ResolvedRoleTagsJson = '[]' THEN 1 ELSE 0 END AS RoleIdRequestedButNotResolved,

    -- Role Resolution Status (computed)
    CASE
        WHEN ctx.ResolvedRoleTagsJson IS NOT NULL AND ctx.ResolvedRoleTagsJson <> '[]' THEN 'success'
        WHEN ctx.RoleResolutionSource IS NOT NULL THEN 'partial_resolution'
        WHEN req.RoleId IS NULL THEN 'no_role_requested'
        ELSE 'failed'
    END AS RoleResolutionStatus,

    -- Overall Status (for quick filtering)
    CASE
        WHEN req.ResponseStatus = 'success' AND ctx.ContextResolutionStatus = 'success' THEN 'COMPLETE_SUCCESS'
        WHEN req.ResponseStatus = 'success' AND ctx.ContextResolutionStatus = 'partial' THEN 'SUCCESS_PARTIAL_CONTEXT'
        WHEN req.ResponseStatus = 'success' AND ctx.ID IS NULL THEN 'SUCCESS_NO_CONTEXT_CAPTURED'
        WHEN req.ResponseStatus = 'error' THEN 'ERROR'
        WHEN req.ResponseStatus = 'processing' THEN 'PROCESSING'
        ELSE 'UNKNOWN'
    END AS OverallStatus,

    -- Timestamps
    req.CreatedAt AS RequestCreatedAt,
    req.ModifiedAt AS RequestModifiedAt,
    ctx.CreatedAt AS ContextCapturedAt

FROM [K12SAFETY].[log_EOP_TaskRequests] req
    LEFT JOIN [K12SAFETY].[log_EOP_TaskRequestContext] ctx
        ON ctx.RequestID = req.RequestID AND ctx.SessionID = req.SessionID
    LEFT JOIN [K12SAFETY].[log_EOP_TaskRequestSystemPrompts] prm
        ON prm.RequestID = req.RequestID AND prm.SessionID = req.SessionID;

Example Queries:

-- Full traceability for a request
SELECT
    RequestID, TaskName, ResponseStatus,
    InputEopId, ResolvedPlanId, SnapshotId,
    OrganizationName, OrganizationType,
    RoleResolutionSource, ResolvedRoleCount,
    ContextResolutionStatus, OverallStatus
FROM K12SAFETY.vw_eop_request_traceability
WHERE RequestID = '...';

-- Find requests where role resolution failed
SELECT
    RequestID, TaskName, InputRoleId,
    RoleResolutionSource, RoleResolutionStatus,
    ResolvedRoleTagsJson, ContextResolutionError
FROM K12SAFETY.vw_eop_request_traceability
WHERE RoleIdRequestedButNotResolved = 1
  AND RequestCreatedAt >= DATEADD(day, -7, GETUTCDATE())
ORDER BY RequestCreatedAt DESC;

-- Context resolution success rate by task type
SELECT
    TaskName,
    COUNT(*) AS TotalRequests,
    SUM(CASE WHEN ContextResolutionStatus = 'success' THEN 1 ELSE 0 END) AS SuccessCount,
    SUM(CASE WHEN HasSnapshotContext = 1 THEN 1 ELSE 0 END) AS WithSnapshot,
    SUM(CASE WHEN HasResolvedRoleTags = 1 THEN 1 ELSE 0 END) AS WithRoleTags
FROM K12SAFETY.vw_eop_request_traceability
WHERE RequestCreatedAt >= DATEADD(day, -30, GETUTCDATE())
GROUP BY TaskName
ORDER BY TotalRequests DESC;

-- Debug: Role resolution details for a session
SELECT
    RequestID, TaskName,
    RoleResolutionSource,
    SnapshotGeneralUserTagIds,
    OrgGeneralUserTagIds,
    ResolvedRoleTagsJson,
    ResolvedRoleCount
FROM K12SAFETY.vw_eop_request_traceability
WHERE SessionID = '...'
ORDER BY RequestCreatedAt;

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

---

vw_qrg_edit_traceability

Purpose: Full traceability from QRG edit request to response with all context information provided by the client.

What It Answers:

1. Request Context — "What QRG was being edited? Which section?"
2. Organization Context — "What organization context was provided?"
3. Role Tags — "What role tags were available for the edit?"
4. Context Quality — "Was the context complete, partial, or missing?"
5. Edit Outcome — "Was an edit actually made? What was the result?"

Key Analysis Columns:

Column	Type	Purpose
ContextStatus	VARCHAR	Context completeness: complete, partial, missing
ContextQuality	VARCHAR	Human-readable quality assessment
EditOutcome	VARCHAR	Result: EDITED, NO_CHANGES, ERROR, PROCESSING
HasOrganizationContext	BIT	Flag: organization data was provided
HasRoleTags	BIT	Flag: role tags were provided
HasContextRecord	BIT	Flag: context was captured (vs older requests)
ContentLengthRatio	FLOAT	New content length / original content length

Schema:

CREATE VIEW [K12SAFETY].[vw_qrg_edit_traceability] AS
SELECT
    -- Request Identification
    req.Id AS RequestRecordId,
    req.RequestId,
    req.SessionId,
    req.WorkflowRunId,
    req.RequestType,

    -- Request Status
    req.Status AS RequestStatus,
    req.StatusMessage AS RequestStatusMessage,
    req.UserId,

    -- QRG Edit Input Context (from qrg_edits)
    edit.QrgName,
    edit.QrgType,
    edit.Section,
    edit.InstructionFor,
    edit.GeneralUserTagManagementType,
    edit.OriginalContentHash,
    edit.OriginalContentLength,

    -- Extended QRG Context (from qrg_edit_context)
    ctx.QrgId,
    ctx.ContextStatus,
    ctx.ContextStatusMessage,

    -- Organization Context (detailed)
    ctx.OrganizationId AS ContextOrganizationId,
    ctx.OrganizationName AS ContextOrganizationName,
    ctx.OrganizationType AS ContextOrganizationType,

    -- Role Tags Context
    ctx.ProvidedRoleTagsJson,
    ctx.ProvidedRoleTagCount,

    -- Edit Result
    edit.IsEdit,
    edit.NewEditionLength,
    edit.Explanation,

    -- Edit Outcome Summary
    CASE
        WHEN edit.IsEdit = 1 THEN 'EDITED'
        WHEN edit.IsEdit = 0 THEN 'NO_CHANGES'
        WHEN edit.IsEdit IS NULL AND req.Status = 'success' THEN 'SUCCESS_NO_EDIT_FLAG'
        WHEN req.Status = 'error' THEN 'ERROR'
        WHEN req.Status = 'processing' THEN 'PROCESSING'
        ELSE 'UNKNOWN'
    END AS EditOutcome,

    -- Context Completeness Flags
    CASE WHEN ctx.OrganizationId IS NOT NULL THEN 1 ELSE 0 END AS HasOrganizationContext,
    CASE WHEN ctx.ProvidedRoleTagsJson IS NOT NULL
         AND ctx.ProvidedRoleTagsJson <> '[]' THEN 1 ELSE 0 END AS HasRoleTags,
    CASE WHEN ctx.ID IS NOT NULL THEN 1 ELSE 0 END AS HasContextRecord,

    -- Context Quality Assessment
    CASE
        WHEN ctx.ID IS NULL THEN 'NO_CONTEXT_CAPTURED'
        WHEN ctx.ContextStatus = 'complete' THEN 'COMPLETE'
        WHEN ctx.ContextStatus = 'partial' THEN 'PARTIAL'
        WHEN ctx.ContextStatus = 'missing' THEN 'MISSING'
        ELSE 'UNKNOWN'
    END AS ContextQuality,

    -- Overall Status
    CASE
        WHEN req.Status = 'success' AND ctx.ContextStatus = 'complete' AND edit.IsEdit IS NOT NULL
        THEN 'COMPLETE_SUCCESS'
        WHEN req.Status = 'success' AND (ctx.ContextStatus = 'partial' OR ctx.ContextStatus = 'missing')
        THEN 'SUCCESS_PARTIAL_CONTEXT'
        WHEN req.Status = 'success' AND ctx.ID IS NULL
        THEN 'SUCCESS_NO_CONTEXT_CAPTURED'
        WHEN req.Status = 'error' THEN 'ERROR'
        ELSE 'UNKNOWN'
    END AS OverallStatus,

    -- Metrics
    CASE
        WHEN edit.OriginalContentLength > 0 AND edit.NewEditionLength > 0
        THEN CAST(edit.NewEditionLength AS FLOAT) / CAST(edit.OriginalContentLength AS FLOAT)
        ELSE NULL
    END AS ContentLengthRatio,

    -- Timestamps
    req.CreatedAtUtc AS RequestCreatedAt

FROM [K12SAFETY].[qrg_requests] req
    LEFT JOIN [K12SAFETY].[qrg_edits] edit ON edit.RequestId = req.RequestId
    LEFT JOIN [K12SAFETY].[qrg_edit_context] ctx
        ON ctx.RequestID = CAST(req.RequestId AS NVARCHAR(200))
WHERE req.RequestType = 'EDITING';

Example Queries:

-- Full traceability for an edit request
SELECT
    RequestId, QrgName, Section, InstructionFor,
    ContextOrganizationName, ProvidedRoleTagCount,
    ContextStatus, EditOutcome, OverallStatus
FROM K12SAFETY.vw_qrg_edit_traceability
WHERE RequestId = '...';

-- Find edits with missing context
SELECT
    RequestId, QrgName, Section,
    ContextStatus, ContextStatusMessage,
    HasOrganizationContext, HasRoleTags
FROM K12SAFETY.vw_qrg_edit_traceability
WHERE ContextQuality IN ('MISSING', 'PARTIAL')
  AND RequestCreatedAt >= DATEADD(day, -7, GETUTCDATE())
ORDER BY RequestCreatedAt DESC;

-- Context quality distribution
SELECT
    ContextQuality,
    COUNT(*) AS RequestCount,
    SUM(CASE WHEN EditOutcome = 'EDITED' THEN 1 ELSE 0 END) AS EditsPerformed,
    AVG(ContentLengthRatio) AS AvgContentChange
FROM K12SAFETY.vw_qrg_edit_traceability
WHERE RequestCreatedAt >= DATEADD(day, -30, GETUTCDATE())
GROUP BY ContextQuality
ORDER BY RequestCount DESC;

-- Edit patterns by section and context
SELECT
    Section,
    ContextQuality,
    COUNT(*) AS TotalEdits,
    SUM(CASE WHEN IsEdit = 1 THEN 1 ELSE 0 END) AS ActualEdits,
    CAST(SUM(CASE WHEN IsEdit = 1 THEN 1 ELSE 0 END) * 100.0
         / COUNT(*) AS DECIMAL(5,2)) AS EditRate
FROM K12SAFETY.vw_qrg_edit_traceability
WHERE RequestStatus = 'success'
GROUP BY Section, ContextQuality
ORDER BY Section, ContextQuality;

-- Organization context usage
SELECT
    ContextOrganizationName,
    ContextOrganizationType,
    COUNT(*) AS EditCount,
    AVG(ProvidedRoleTagCount) AS AvgRoleTags
FROM K12SAFETY.vw_qrg_edit_traceability
WHERE HasOrganizationContext = 1
GROUP BY ContextOrganizationName, ContextOrganizationType
ORDER BY EditCount DESC;

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

---

Supporting Table: qrg_edit_context

The vw_qrg_edit_traceability view joins to a dedicated context table that captures the context provided for each QRG edit request.

Purpose: Store organization and role tag context for QRG edit traceability.

Key Columns:

Column	Type	Purpose
RequestID	NVARCHAR(200)	FK to qrg_requests
QrgId	NVARCHAR(200)	K12 QRG ID (if available)
QrgName	NVARCHAR(500)	QRG display name
QrgType	NVARCHAR(50)	FUNCTIONAL or EMERGENCY
OrganizationId/Name/Type	Various	Organization context
ProvidedRoleTagsJson	NVARCHAR(MAX)	JSON array of role tags
ProvidedRoleTagCount	INT	Count of provided tags
Section	NVARCHAR(50)	BEFORE, DURING, AFTER
ContextStatus	NVARCHAR(50)	complete, partial, missing

Context Status Logic:

Status	Condition
complete	Organization AND role tags provided
partial	Organization provided but no role tags
missing	No organization context provided

DDL: db/sqlproj/Mastra.Platform.K12SafetyDb/model/Tables/K12SAFETY.qrg_edit_context.sql

---

Debug Playbook

1) EOP Request: Check Context Resolution

-- Quick context check
SELECT
    RequestID, TaskName, ResponseStatus,
    ContextResolutionStatus, RoleResolutionStatus,
    HasSnapshotContext, HasOrganizationContext, HasResolvedRoleTags,
    OverallStatus
FROM K12SAFETY.vw_eop_request_traceability
WHERE RequestID = '...';

-- Full context details
SELECT
    InputEopId, ResolvedPlanId, SnapshotId,
    OrganizationId, OrganizationName,
    RoleResolutionSource,
    SnapshotGeneralUserTagIds,
    OrgGeneralUserTagIds,
    ResolvedRoleTagsJson
FROM K12SAFETY.vw_eop_request_traceability
WHERE RequestID = '...';

2) QRG Edit: Check Context Quality

-- Quick context check
SELECT
    RequestId, QrgName, Section,
    ContextStatus, ContextQuality,
    HasOrganizationContext, HasRoleTags,
    EditOutcome, OverallStatus
FROM K12SAFETY.vw_qrg_edit_traceability
WHERE RequestId = '...';

-- Full context details
SELECT
    ContextOrganizationId, ContextOrganizationName, ContextOrganizationType,
    ProvidedRoleTagsJson, ProvidedRoleTagCount,
    ContextStatusMessage
FROM K12SAFETY.vw_qrg_edit_traceability
WHERE RequestId = '...';

3) Find Requests with Context Issues

-- EOP: Role resolution failures
SELECT TOP 20
    RequestID, TaskName, RequestCreatedAt,
    RoleResolutionStatus, ContextResolutionError
FROM K12SAFETY.vw_eop_request_traceability
WHERE RoleIdRequestedButNotResolved = 1
ORDER BY RequestCreatedAt DESC;

-- QRG: Missing organization context
SELECT TOP 20
    RequestId, QrgName, Section, RequestCreatedAt,
    ContextStatus, ContextStatusMessage
FROM K12SAFETY.vw_qrg_edit_traceability
WHERE ContextQuality = 'MISSING'
ORDER BY RequestCreatedAt DESC;

---

Related Documentation

• EOP Tables: K12 EOP Logging
• QRG Tables: K12 QRG Storage
• QRG Generation Views: K12 QRG Views