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:

Create request → qrg_requests (Status: accepted)
Store configuration → qrg_request_prompts, qrg_request_roles
Create generation → qrg_generations
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)
Complete → Update qrg_requests (Status: success or error)

Editing workflow:

Create request → qrg_requests (RequestType: EDITING)
Store prompts → qrg_request_prompts
Persist context → qrg_edit_context (organization, role tags, context status)
Process edit → qrg_edits (Section, NewEdition)
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/.../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/.../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/.../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/.../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/.../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/.../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/.../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/.../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/.../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/.../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/.../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:

Request Context — RequestId, Status, WorkflowRunId, timestamps, EOP ID
Input Data — FormId, FormName, FormStatus, RawFormDataJson, FormContentJson, metrics
AI Processing — Classification, extracted groups, role mappings, processing status
Output Data — QRG names/types, content blocks, role assignments, metrics
Input-Output Analysis — FormDataCategory, InputOutputRelationship, success flags
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/.../docs/query-examples-input-output-trace.sql (12 comprehensive queries)

DDL: db/sqlproj/.../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/.../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/.../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/.../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: docs/query-examples-input-output-trace.sql

DACPAC Projects:

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

Overview​

Schema Components​

Core Tables​

Analytical Views​

Data Flow​

Table Reference​

K12SAFETY.qrg_requests​

K12SAFETY.qrg_request_prompts​

K12SAFETY.qrg_request_roles​

K12SAFETY.qrg_generations​

K12SAFETY.qrg_generation_annexes​

K12SAFETY.qrg_generated_items​

K12SAFETY.qrg_edits​

K12SAFETY.qrg_edit_context​

K12SAFETY.qrg_ai_calls​

K12SAFETY.qrg_execution_errors​

View Reference​

K12SAFETY.vw_qrg_full_traceability​

K12SAFETY.vw_qrg_input_output_trace​

K12SAFETY.vw_qrg_ai_call_summary​

K12SAFETY.vw_qrg_annex_ai_details​

K12SAFETY.vw_qrg_error_summary​

Query Patterns​

Request Lifecycle​

Input-to-Output Traceability​

AI Pipeline Analysis​

Error Diagnosis​

Performance Analysis​

Success Rate Analysis​

Storage Implementation​

Schema Files​

See also​

Overview

Schema Components

Core Tables

Analytical Views

Data Flow

Table Reference

`K12SAFETY.qrg_requests`

`K12SAFETY.qrg_request_prompts`

`K12SAFETY.qrg_request_roles`

`K12SAFETY.qrg_generations`

`K12SAFETY.qrg_generation_annexes`

`K12SAFETY.qrg_generated_items`

`K12SAFETY.qrg_edits`

`K12SAFETY.qrg_edit_context`

`K12SAFETY.qrg_ai_calls`

`K12SAFETY.qrg_execution_errors`

View Reference

`K12SAFETY.vw_qrg_full_traceability`

`K12SAFETY.vw_qrg_input_output_trace`

`K12SAFETY.vw_qrg_ai_call_summary`

`K12SAFETY.vw_qrg_annex_ai_details`

`K12SAFETY.vw_qrg_error_summary`

Query Patterns

Request Lifecycle

Input-to-Output Traceability

AI Pipeline Analysis

Error Diagnosis

Performance Analysis

Success Rate Analysis

Storage Implementation

Schema Files

See also