Database: K12 QRG Storage

Overview

The K12 QRG system stores all generation and editing workflows in the K12SAFETY schema using normalized tables. This architecture enables:

• Full request lifecycle tracking (accepted → processing → success/error)
• AI pipeline observability (classification → extraction → mapping → content generation)
• Input-to-output traceability (MOEOP forms → generated QRGs)
• Error diagnosis and performance analysis

Schema Components

Core Tables

Table	Purpose	Key Columns
qrg_requests	Request envelope and status	RequestId, Status, RequestType
qrg_request_prompts	Prompt storage per request	SystemPromptUsed, UserPrompt
qrg_request_roles	Role labels and tags	RoleLabel, RoleTagId
qrg_generations	Generation metadata	EmergencyOperationPlanId, QrgCount
qrg_generation_annexes	Annex processing tracking	AnnexId, FormStatus, ClassifiedCategory
qrg_generated_items	Individual generated QRGs	QrgName, QrgType, ContentBlocksJson
qrg_edits	Edit operation results	Section, NewEdition
qrg_edit_context	Edit context for traceability	OrganizationId, ProvidedRoleTagsJson, ContextStatus
qrg_ai_calls	AI call traceability	StepType, PromptTokens, ResponseParsed
qrg_execution_errors	Workflow error logging	ErrorType, ErrorCategory, FailedStep

Analytical Views

View	Purpose
vw_qrg_full_traceability	Join requests → generations → annexes → items
vw_qrg_input_output_trace	Complete form-to-QRG lineage with metrics
vw_qrg_ai_call_summary	Aggregated AI call statistics per request
vw_qrg_annex_ai_details	Annex-level AI processing details
vw_qrg_error_summary	Errors joined with annex and request context
vw_qrg_edit_traceability	Full edit request traceability with context

Data Flow

Generation workflow:

1. Create request → qrg_requests (Status: accepted)
2. Store configuration → qrg_request_prompts, qrg_request_roles
3. Create generation → qrg_generations
4. Process each annex:
   • Log AI calls → qrg_ai_calls (classification, extraction, mapping, content)
   • Track progress → qrg_generation_annexes (FormStatus, processing results)
   • Generate QRGs → qrg_generated_items
   • Log errors → qrg_execution_errors (if any)
5. Complete → Update qrg_requests (Status: success or error)

Editing workflow:

1. Create request → qrg_requests (RequestType: EDITING)
2. Store prompts → qrg_request_prompts
3. Persist context → qrg_edit_context (organization, role tags, context status)
4. Process edit → qrg_edits (Section, NewEdition)
5. Complete → Update qrg_requests.Status

---

Table Reference

K12SAFETY.qrg_requests

Request envelope tracking the full lifecycle of a generation or editing operation.

Key Columns:

• RequestId (UNIQUEIDENTIFIER, PK)
• RequestType (VARCHAR) — GENERATION | EDITING
• Status (VARCHAR) — accepted | processing | success | error | cancelled
• StatusMessage (NVARCHAR) — Human-readable status
• WorkflowRunId (NVARCHAR) — Mastra workflow correlation ID
• SessionId, UserId, RoleId, OrganizationId (correlation IDs)
• CreatedAtUtc, ModifiedAtUtc (DATETIME2)

Common Queries:

-- Get request status
SELECT RequestId, RequestType, Status, StatusMessage, CreatedAtUtc
FROM K12SAFETY.qrg_requests
WHERE RequestId = @requestId;

-- Recent requests by status
SELECT TOP 50 RequestId, Status, CreatedAtUtc
FROM K12SAFETY.qrg_requests
WHERE OrganizationId = @orgId
ORDER BY CreatedAtUtc DESC;

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

---

K12SAFETY.qrg_request_prompts

Stores prompts used for each request (system prompt, user prompt, replacements).

Key Columns:

• RequestId (UNIQUEIDENTIFIER, PK + FK)
• UserPrompt (NVARCHAR(MAX), required)
• SystemPromptUsed (NVARCHAR(MAX))
• SystemPromptHash (NVARCHAR(64)) — SHA-256 hash for deduplication
• ReplacementsJson (NVARCHAR(MAX)) — Prompt variable substitutions
• BasePromptSource (VARCHAR(100)) — Source identifier

Common Queries:

-- Get prompts for request
SELECT SystemPromptUsed, UserPrompt, ReplacementsJson
FROM K12SAFETY.qrg_request_prompts
WHERE RequestId = @requestId;

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

---

K12SAFETY.qrg_request_roles

Captures role labels and tag IDs associated with the request.

Key Columns:

• RequestId (UNIQUEIDENTIFIER, PK + FK)
• RoleLabel (NVARCHAR(255), PK) — Role name
• RoleTagId (INT, nullable) — K12 TAP role tag ID

Common Queries:

-- Get roles for request
SELECT RoleLabel, RoleTagId
FROM K12SAFETY.qrg_request_roles
WHERE RequestId = @requestId;

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

---

K12SAFETY.qrg_generations

Generation-specific metadata (EOP ID, annex count, results payload).

Key Columns:

• RequestId (UNIQUEIDENTIFIER, PK + FK)
• EmergencyOperationPlanId (UNIQUEIDENTIFIER) — K12 EOP identifier
• GeneralUserTagManagementType (VARCHAR) — ASSIGN_ALL | ASSIGN_SELECTED
• AnnexCount (INT) — Number of annexes processed
• QrgCount (INT) — Total QRGs generated
• ResultPayloadJson (NVARCHAR(MAX)) — Final generation summary
• CompletedAtUtc (DATETIME2)

Common Queries:

-- Get generation summary
SELECT EmergencyOperationPlanId, AnnexCount, QrgCount, CompletedAtUtc
FROM K12SAFETY.qrg_generations
WHERE RequestId = @requestId;

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

---

K12SAFETY.qrg_generation_annexes

Annex-level processing tracking (form data, AI results, status).

Key Columns:

• Id (BIGINT IDENTITY, PK)
• RequestId (UNIQUEIDENTIFIER, FK)
• AnnexId (NVARCHAR(100)) — MOEOP annex identifier (supports string IDs)
• AnnexName (NVARCHAR(500))
• FormStatus (VARCHAR) — IN_PROGRESS | COMPLETED | NOT_STARTED | etc.
• AnnexFormJson (NVARCHAR(MAX)) — Raw form data from MOEOP
• AnnexContentJson (NVARCHAR(MAX)) — Annex content/text
• ClassifiedCategory (VARCHAR) — AI-classified category (e.g., EVACUATION)
• ClassifiedType (VARCHAR) — FUNCTIONAL | EMERGENCY
• ExtractedGroupsJson (NVARCHAR(MAX)) — JSON array of extracted group names
• RoleMappingsJson (NVARCHAR(MAX)) — Group → Role ID mappings
• GeneratedQrgCount (INT) — QRGs generated from this annex
• ProcessingStatus (VARCHAR) — success | error | skipped
• ErrorMessage (NVARCHAR)
• ProcessedAtUtc (DATETIME2)

Constraints:

• UNIQUE (RequestId, AnnexId)
• CHECK (ProcessingStatus IN ('success', 'error', 'skipped'))

FormStatus Logic:

The FormStatus field distinguishes:

• Empty + IN_PROGRESS → User hasn't started, should generate error
• Empty + COMPLETED → User accepted defaults, proceed with generation
• Has content → Process normally

Common Queries:

-- Get annexes for request
SELECT AnnexId, AnnexName, FormStatus, ClassifiedCategory, 
       GeneratedQrgCount, ProcessingStatus
FROM K12SAFETY.qrg_generation_annexes
WHERE RequestId = @requestId
ORDER BY ProcessedAtUtc;

-- Find empty in-progress forms
SELECT RequestId, AnnexName, FormStatus, AnnexFormJson
FROM K12SAFETY.qrg_generation_annexes
WHERE FormStatus = 'IN_PROGRESS'
  AND (AnnexFormJson = '{}' OR AnnexFormJson IS NULL);

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

---

K12SAFETY.qrg_generated_items

One row per generated QRG.

Key Columns:

• GeneratedItemId (BIGINT IDENTITY, PK)
• RequestId (UNIQUEIDENTIFIER, FK)
• QrgName (NVARCHAR(255))
• QrgType (VARCHAR) — FUNCTIONAL | EMERGENCY
• Functionality (NVARCHAR(255)) — What the QRG covers
• Hazard (NVARCHAR(255)) — Associated hazard (nullable)
• GeneralUserTagManagementType (VARCHAR) — ASSIGN_ALL | ASSIGN_SELECTED
• GeneralUserTagIdsJson (NVARCHAR(MAX)) — JSON array of role tag IDs
• ContentBlocksJson (NVARCHAR(MAX)) — Before/During/After content
• AnnexId (NVARCHAR(100)) — Source annex
• GenerationAnnexId (BIGINT) — FK to qrg_generation_annexes.Id
• CreatedAtUtc (DATETIME2)

Common Queries:

-- Get QRGs for request
SELECT GeneratedItemId, QrgName, QrgType, Functionality, Hazard
FROM K12SAFETY.qrg_generated_items
WHERE RequestId = @requestId
ORDER BY QrgName;

-- Get QRGs for an EOP
SELECT i.QrgName, i.QrgType, g.EmergencyOperationPlanId
FROM K12SAFETY.qrg_generated_items i
JOIN K12SAFETY.qrg_generations g ON i.RequestId = g.RequestId
WHERE g.EmergencyOperationPlanId = @eopId;

-- Get content for specific QRG
SELECT QrgName, ContentBlocksJson
FROM K12SAFETY.qrg_generated_items
WHERE GeneratedItemId = @itemId;

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

---

K12SAFETY.qrg_edits

Edit operation results (one per request).

Key Columns:

• RequestId (UNIQUEIDENTIFIER, PK + FK)
• QrgName (NVARCHAR(255))
• Section (VARCHAR) — BEFORE | DURING | AFTER
• InstructionFor (NVARCHAR(255)) — Functionality being edited
• Explanation (NVARCHAR(MAX)) — User's edit request
• Answer (NVARCHAR(MAX)) — AI's interpretation
• NewEdition (NVARCHAR(MAX)) — Edited content
• CompletedAtUtc (DATETIME2)

Common Queries:

-- Get edit result
SELECT QrgName, Section, Explanation, NewEdition, CompletedAtUtc
FROM K12SAFETY.qrg_edits
WHERE RequestId = @requestId;

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

---

K12SAFETY.qrg_edit_context

Captures context provided for each QRG edit request, enabling full traceability.

Key Columns:

• ID (BIGINT IDENTITY, PK)
• RequestID (NVARCHAR(200), FK to qrg_requests)
• QrgId (NVARCHAR(200), nullable) — K12 QRG ID being edited
• QrgName, QrgType — QRG identification
• OrganizationId, OrganizationName, OrganizationType — Organization context
• OrganizationCode (NVARCHAR(100), nullable)
• OrganizationAddressJson (NVARCHAR(MAX), nullable)
• ProvidedRoleTagsJson (NVARCHAR(MAX)) — JSON array of [{"value":"uuid","label":"Name"}]
• ProvidedRoleTagCount (INT)
• Section (NVARCHAR(50)) — BEFORE | DURING | AFTER
• InstructionFor (NVARCHAR(500)) — Target role/audience
• OriginalContentHash (NVARCHAR(64)) — SHA-256 of original content
• OriginalContentLength (INT)
• ContextStatus (NVARCHAR(50)) — complete | partial | missing
• ContextStatusMessage (NVARCHAR(MAX), nullable)
• CreatedAt (DATETIME2)

Context Status Logic:

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

Common Queries:

-- Get context for an edit request
SELECT QrgName, Section, OrganizationName, ProvidedRoleTagCount,
       ContextStatus, ContextStatusMessage
FROM K12SAFETY.qrg_edit_context
WHERE RequestID = @requestId;

-- Find edits with missing context
SELECT RequestID, QrgName, ContextStatus, ContextStatusMessage
FROM K12SAFETY.qrg_edit_context
WHERE ContextStatus IN ('partial', 'missing')
  AND CreatedAt >= DATEADD(day, -7, GETUTCDATE());

-- Context quality distribution
SELECT ContextStatus, COUNT(*) AS RequestCount
FROM K12SAFETY.qrg_edit_context
GROUP BY ContextStatus;

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

---

K12SAFETY.qrg_ai_calls

Individual AI call traceability (prompts, responses, tokens, timing).

Key Columns:

• Id (BIGINT IDENTITY, PK)
• RequestId (UNIQUEIDENTIFIER, FK)
• AnnexId (NVARCHAR(100), nullable) — Links to annex if applicable
• GeneratedItemId (BIGINT, nullable) — Links to generated item
• StepType (VARCHAR) — CLASSIFY_CATEGORY | EXTRACT_GROUPS | MAP_ROLES | GENERATE_CONTENT | OTHER
• StepSequence (INT) — Order within request
• SystemPrompt, UserPrompt (NVARCHAR(MAX))
• ResponseRaw (NVARCHAR(MAX)) — Raw AI response
• ResponseParsed (NVARCHAR(MAX)) — Parsed/structured response
• ModelProvider, ModelDeployment, ModelVersion (VARCHAR)
• PromptTokens, CompletionTokens, TotalTokens (INT)
• StartedAtUtc, CompletedAtUtc (DATETIME2)
• DurationMs (INT)
• Status (VARCHAR) — pending | success | error
• ErrorMessage (NVARCHAR)
• RetryCount (INT)
• CorrelationId, WorkflowRunId (NVARCHAR)

Constraints:

• CHECK (StepType IN ('CLASSIFY_CATEGORY', 'EXTRACT_GROUPS', 'MAP_ROLES', 'GENERATE_CONTENT', 'OTHER'))
• CHECK (Status IN ('pending', 'success', 'error'))

Common Queries:

-- Get all AI calls for request
SELECT StepType, StepSequence, Status, PromptTokens, CompletionTokens, DurationMs
FROM K12SAFETY.qrg_ai_calls
WHERE RequestId = @requestId
ORDER BY StepSequence;

-- Token usage by step type
SELECT StepType, 
       COUNT(*) AS CallCount,
       SUM(TotalTokens) AS TotalTokens,
       AVG(DurationMs) AS AvgDurationMs
FROM K12SAFETY.qrg_ai_calls
WHERE RequestId = @requestId
GROUP BY StepType;

-- Failed AI calls
SELECT StepType, ErrorMessage, RetryCount, StartedAtUtc
FROM K12SAFETY.qrg_ai_calls
WHERE RequestId = @requestId AND Status = 'error';

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

---

K12SAFETY.qrg_execution_errors

Workflow-level error logging (validation failures, API errors, step failures).

Key Columns:

• Id (UNIQUEIDENTIFIER, PK)
• RequestId (UNIQUEIDENTIFIER, FK)
• AnnexId (NVARCHAR(100), nullable) — Links to specific annex
• ErrorType (NVARCHAR) — e.g., empty_content, api_failure, validation_error
• ErrorCategory (NVARCHAR) — e.g., form_validation, ai_call, k12_api, workflow
• ErrorMessage (NVARCHAR(MAX)) — Human-readable description
• ErrorContext (NVARCHAR(MAX)) — JSON with additional details
• FailedStep (NVARCHAR) — Workflow step that failed
• FormId (UNIQUEIDENTIFIER) — MOEOP form ID
• FormName (NVARCHAR) — Human-readable form name
• OccurredAtUtc (DATETIME2)

Foreign Key:

• FK (RequestId, AnnexId) → qrg_generation_annexes(RequestId, AnnexId)

Common Queries:

-- Get errors for request
SELECT OccurredAtUtc, ErrorType, ErrorCategory, ErrorMessage, FailedStep
FROM K12SAFETY.qrg_execution_errors
WHERE RequestId = @requestId
ORDER BY OccurredAtUtc;

-- Error counts by type
SELECT ErrorType, ErrorCategory, COUNT(*) AS ErrorCount
FROM K12SAFETY.qrg_execution_errors
WHERE OccurredAtUtc >= DATEADD(day, -7, GETUTCDATE())
GROUP BY ErrorType, ErrorCategory
ORDER BY ErrorCount DESC;

-- Forms with repeated errors
SELECT FormId, FormName, COUNT(*) AS FailureCount
FROM K12SAFETY.qrg_execution_errors
WHERE FormId IS NOT NULL
GROUP BY FormId, FormName
HAVING COUNT(*) > 2
ORDER BY FailureCount DESC;

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

---

View Reference

K12SAFETY.vw_qrg_full_traceability

Joins requests → generations → annexes → generated items for complete lineage.

Columns:

• Request: RequestId, SessionId, WorkflowRunId, RequestStatus
• Generation: EmergencyOperationPlanId, AnnexCount, TotalQrgCount
• Annex: AnnexId, AnnexName, AnnexFormJson, ClassifiedCategory, ClassifiedType, ExtractedGroupsJson, RoleMappingsJson
• Generated Item: GeneratedItemId, QrgName, QrgType, Functionality, Hazard, ContentBlocksJson
• Status: AnnexProcessingStatus, AnnexErrorMessage

Use Cases:

• "Show me everything for this request"
• "Which annexes generated which QRGs?"
• "What did the AI extract from each form?"

Example:

SELECT RequestId, AnnexName, ClassifiedCategory, QrgName, QrgType
FROM K12SAFETY.vw_qrg_full_traceability
WHERE RequestId = @requestId;

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

---

K12SAFETY.vw_qrg_input_output_trace

Complete input-to-output traceability with detailed metrics and analysis.

What It Provides:

1. Request Context — RequestId, Status, WorkflowRunId, timestamps, EOP ID
2. Input Data — FormId, FormName, FormStatus, RawFormDataJson, FormContentJson, metrics
3. AI Processing — Classification, extracted groups, role mappings, processing status
4. Output Data — QRG names/types, content blocks, role assignments, metrics
5. Input-Output Analysis — FormDataCategory, InputOutputRelationship, success flags
6. Error Context — Workflow errors linked to forms

Computed Columns:

• FormDataCategory — NO_FORM_DATA | EMPTY_FORM_OBJECT | MINIMAL_FORM_DATA | MODERATE_FORM_DATA | SUBSTANTIAL_FORM_DATA
• InputOutputRelationship — INPUT_USED_SUCCESSFULLY | INPUT_PROCESSED_NO_OUTPUT | INPUT_PROCESSING_FAILED | INPUT_SKIPPED
• IsEmptyInProgressForm (BIT) — Flag for empty forms still in progress
• IsSuccessfulTransformation (BIT) — Form data became QRG successfully

Use Cases:

• "Which forms generated which QRGs?"
• "Did this form data actually affect the output?"
• "Why did this form fail?"
• "Which forms had empty data?"
• "What did the AI do with this form?"
• "Success rate analysis"

Example Queries:

-- Complete lineage for request
SELECT RequestId, AnnexName, FormStatus, QrgsGeneratedFromAnnex, 
       QrgName, InputOutputRelationship
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = @requestId
ORDER BY AnnexRecordId, GeneratedItemId;

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

-- Empty forms that caused errors
SELECT AnnexName, FormStatus, FormDataCategory, 
       AnnexErrorMessage, InputOutputRelationship
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = @requestId
  AND IsEmptyInProgressForm = 1
GROUP BY AnnexName, FormStatus, FormDataCategory, 
         AnnexErrorMessage, InputOutputRelationship;

-- Compare input JSON to output JSON
SELECT AnnexName, FormId,
       RawFormDataJson,           -- Input from MOEOP
       ClassifiedCategory,
       ExtractedGroupsJson,       -- AI extraction
       RoleMappingsJson,          -- AI mappings
       QrgName,
       QrgContentBlocksJson       -- Output QRG
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = @requestId;

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

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

---

K12SAFETY.vw_qrg_ai_call_summary

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

Columns:

• RequestId, SessionId, RequestStatus
• Call Counts: ClassifyCalls, ExtractGroupsCalls, MapRolesCalls, GenerateContentCalls, TotalCalls
• Success/Error: SuccessfulCalls, FailedCalls
• Tokens: TotalPromptTokens, TotalCompletionTokens, TotalTokens
• Timing: TotalDurationMs, AvgDurationMs, MaxDurationMs
• CreatedAtUtc

Use Cases:

• "How many AI calls did this request use?"
• "Token cost analysis"
• "Performance bottlenecks"
• "Success rate by request"

Example:

SELECT RequestId, TotalCalls, TotalTokens, TotalDurationMs,
       SuccessfulCalls, FailedCalls
FROM K12SAFETY.vw_qrg_ai_call_summary
WHERE RequestId = @requestId;

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

---

K12SAFETY.vw_qrg_annex_ai_details

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

Columns:

• RequestId, AnnexId, AnnexName
• AiCallId, StepType, StepSequence, CallStatus
• ModelProvider, ModelDeployment
• SystemPromptPreview, UserPromptPreview, ResponsePreview (truncated)
• PromptTokens, CompletionTokens, TotalTokens
• StartedAtUtc, CompletedAtUtc, DurationMs
• ErrorMessage, RetryCount

Use Cases:

• "What AI calls happened for this annex?"
• "Show me the prompts used"
• "Token breakdown by annex"
• "Which steps failed?"

Example:

SELECT AnnexName, StepType, CallStatus, TotalTokens, DurationMs
FROM K12SAFETY.vw_qrg_annex_ai_details
WHERE RequestId = @requestId
ORDER BY StartedAtUtc;

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

---

K12SAFETY.vw_qrg_error_summary

Errors joined with annex and request context for quick analysis.

Columns:

• OccurredAtUtc, RequestId, AnnexName
• ErrorType, ErrorCategory, ErrorMessage
• FailedStep, FormId, FormName
• RequestStatus

Use Cases:

• "Recent errors with context"
• "Which requests have errors?"
• "Error patterns by form"

Example:

SELECT TOP 50 
    OccurredAtUtc, RequestId, AnnexName, 
    ErrorType, ErrorMessage, FailedStep
FROM K12SAFETY.vw_qrg_error_summary
ORDER BY OccurredAtUtc DESC;

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

---

Query Patterns

Request Lifecycle

-- Get complete request status
SELECT 
    r.RequestId, r.RequestType, r.Status, r.StatusMessage,
    r.CreatedAtUtc, r.ModifiedAtUtc,
    g.AnnexCount, g.QrgCount, g.CompletedAtUtc
FROM K12SAFETY.qrg_requests r
LEFT JOIN K12SAFETY.qrg_generations g ON r.RequestId = g.RequestId
WHERE r.RequestId = @requestId;

Input-to-Output Traceability

-- Full form → QRG lineage
SELECT 
    a.AnnexName,
    a.FormStatus,
    a.ClassifiedCategory,
    a.GeneratedQrgCount,
    i.QrgName,
    i.QrgType,
    i.Functionality
FROM K12SAFETY.qrg_generation_annexes a
LEFT JOIN K12SAFETY.qrg_generated_items i 
    ON a.RequestId = i.RequestId AND a.AnnexId = i.AnnexId
WHERE a.RequestId = @requestId
ORDER BY a.ProcessedAtUtc, i.QrgName;

AI Pipeline Analysis

-- AI call breakdown with timing
SELECT 
    StepType,
    COUNT(*) AS Calls,
    SUM(TotalTokens) AS Tokens,
    SUM(DurationMs) AS TotalMs,
    AVG(DurationMs) AS AvgMs,
    SUM(CASE WHEN Status = 'error' THEN 1 ELSE 0 END) AS Errors
FROM K12SAFETY.qrg_ai_calls
WHERE RequestId = @requestId
GROUP BY StepType
ORDER BY TotalMs DESC;

Error Diagnosis

-- Errors by category with context
SELECT 
    e.ErrorCategory,
    e.ErrorType,
    COUNT(*) AS ErrorCount,
    STRING_AGG(e.FailedStep, ', ') AS FailedSteps,
    STRING_AGG(DISTINCT a.AnnexName, ', ') AS AffectedAnnexes
FROM K12SAFETY.qrg_execution_errors e
LEFT JOIN K12SAFETY.qrg_generation_annexes a 
    ON e.RequestId = a.RequestId AND e.AnnexId = a.AnnexId
WHERE e.RequestId = @requestId
GROUP BY e.ErrorCategory, e.ErrorType
ORDER BY ErrorCount DESC;

Performance Analysis

-- Request performance summary
SELECT 
    r.RequestId,
    r.Status,
    g.AnnexCount,
    g.QrgCount,
    acs.TotalCalls AS AiCalls,
    acs.TotalTokens,
    acs.TotalDurationMs AS AiDurationMs,
    DATEDIFF(second, r.CreatedAtUtc, g.CompletedAtUtc) AS TotalDurationSeconds
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'
ORDER BY g.CompletedAtUtc DESC;

Success Rate Analysis

-- QRG generation success rate by form status
SELECT 
    FormStatus,
    FormDataCategory,
    COUNT(DISTINCT AnnexRecordId) 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(DISTINCT AnnexRecordId) AS DECIMAL(5,2)) AS SuccessRate,
    AVG(QrgsGeneratedFromAnnex) AS AvgQrgsPerForm
FROM K12SAFETY.vw_qrg_input_output_trace
WHERE RequestId = @requestId
GROUP BY FormStatus, FormDataCategory
ORDER BY TotalForms DESC;

---

Storage Implementation

All database operations use packages/domain-k12/src/qrg-dedicated-storage.ts.

Key Functions:

• insertQrgRequest() — Create request row
• updateQrgRequestStatus() — Update status/message
• insertQrgGeneratedItems() — Bulk insert QRGs
• insertQrgAiCall() — Log AI call
• insertQrgExecutionError() — Log workflow error
• completeQrgGeneration() — Finalize generation

Workflow Integration:
packages/domain-k12/src/qrg-generation.workflow.ts orchestrates all storage calls.

---

Schema Files

All DDL files are in db/sqlproj/Mastra.Platform.K12SafetyDb/:

• Tables: model/Tables/K12SAFETY.*.sql
• Views: model/Views/K12SAFETY.*.sql
• Query Examples: db/sqlproj/Mastra.Platform.K12SafetyDb/docs/query-examples-input-output-trace.sql

DACPAC Projects:

• Mastra.Platform.K12SafetyDb.sqlproj (K12 schema only)
• Mastra.AllDb.sqlproj (all schemas combined)

See also

• K12 Internal: QRG Database -- K12-focused ER diagram, generation/editing workflows, and debugging queries for the same QRG tables.