K12 Database: QRG (normalized storage)

The mental model

QRG storage is designed so you can answer questions like:

“What request did we receive, and what is its status?”
“What exact categories and prompts were used to generate the QRGs?”
“How many QRGs were generated for an EOP, and what were their final content blocks?”

To support that, the K12SAFETY schema contains a normalized QRG data model.

Tables in the normalized path

These are the primary source of truth for all QRG flows:

qrg_requests (request envelope + status)
qrg_request_prompts (captured prompts)
qrg_request_roles (legacy generation/edit-context role evidence; not populated by the category-first generation path)
qrg_generations (generation metadata)
qrg_generated_items (one row per generated QRG)
qrg_generation_annexes (per-annex processing tracking)
qrg_ai_calls (individual AI call logs)
qrg_execution_errors (workflow-level error tracking)
qrg_edits (editing runs)
qrg_edit_context (edit context snapshots)

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

Table relationships

A typical generation flow:

Insert request → K12SAFETY.qrg_requests
Store prompts → K12SAFETY.qrg_request_prompts
Insert the category-first generation envelope → K12SAFETY.qrg_generations
Store annex/category decisions → K12SAFETY.qrg_generation_annexes
Insert generated items → K12SAFETY.qrg_generated_items (many rows)
Update status to success/error → K12SAFETY.qrg_requests

Editing flow (high level):

A request row still exists in qrg_requests (with RequestType = 'EDITING')
The edit result is written to qrg_edits

Tables (what they mean)

`K12SAFETY.qrg_requests`

The request envelope and status row.

Key columns:

RequestId (GUID, PK)
RequestType: GENERATION or EDITING
Status: accepted | processing | success | error | cancelled
Correlation: WorkflowRunId, SessionId, UserId, RoleId, OrganizationId

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

`K12SAFETY.qrg_request_prompts`

Stores:

UserPrompt (required)
SystemPromptUsed, SystemPromptHash
ReplacementsJson, BasePromptSource

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

`K12SAFETY.qrg_request_roles`

Stores historical role label/tag evidence and compatibility data. The current category-first generation workflow does not write role rows; its selection contract is stored as category IDs on qrg_generations and qrg_generated_items.

Key columns:

RoleLabel (always present)
RoleTagId (nullable)

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

`K12SAFETY.qrg_generations`

Generation metadata for a request.

Key columns:

RequestId (PK + FK to qrg_requests)
EmergencyOperationPlanId (GUID)
GeneralUserTagManagementType: ASSIGN_ALL | ASSIGN_SELECTED
GeneralUserTagCategoryIdsJson: exact selected category IDs
GenerationMode: CATEGORY_FIRST for the current generation path
GenerationScope: DISTRICT | FACILITY
AnnexCount, QrgCount, ResultPayloadJson, CompletedAtUtc

ResultPayloadJson Structure:

The ResultPayloadJson column stores the generated QRGs plus AI processing metadata:

On success:

{
  "root": [
    {
      "name": "Fire Safety - Emergency Procedures",
      "generalUserTagIds": [],
      "generalUserTagCategoryIds": ["category-uuid"],
      "type": "EMERGENCY",
      "hazard": "FIRE",
      "contentBlocks": [
        { "type": "BEFORE", "content": "<ul><li>...</li></ul>" },
        { "type": "DURING", "content": "<ul><li>...</li></ul>" },
        { "type": "AFTER", "content": "<ul><li>...</li></ul>" }
      ]
    }
  ],
  "_meta": {
    "status": "success",
    "stats": {
      "totalAnnexes": 6,
      "totalTargets": 18,
      "generatedTargets": 12,
      "skippedTargets": 6,
      "failedTargets": 0,
      "saveFailedTargets": 0
    }
  }
}

generalUserTagIds is intentionally empty in the category-first path. The exact category lives in generalUserTagCategoryIds.

On error (AI failed):

{
  "_meta": {
    "status": "error",
    "errors": [
      "Fire Safety Annex/Administrators: model request failed",
      "Evacuation Annex/Administrators: model request failed"
    ],
    "stats": {
      "totalAnnexes": 6,
      "totalTargets": 18,
      "generatedTargets": 0,
      "skippedTargets": 0,
      "failedTargets": 18,
      "saveFailedTargets": 0
    }
  }
}

_meta fields:

status - "success" or "error" (no fallback content is ever generated)
errors - Array of detailed error messages from AI call failures
stats - Target-level generated/skipped/failed/save-failed counters

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

`K12SAFETY.qrg_generated_items`

One row per generated QRG.

Key columns:

RequestId (FK)
QrgName, QrgType (FUNCTIONAL | EMERGENCY)
GeneralUserTagIdsJson (empty in category-first generation), GeneralUserTagCategoryIdsJson, GeneralUserTagCategoryName, GenerationScope, and ContentBlocksJson

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

`K12SAFETY.qrg_generation_annexes`

Tracks per-annex processing during QRG generation. One row per annex per request.

Key columns:

RequestId (FK to qrg_requests), AnnexId (unique pair)
AnnexName, FormStatus (IN_PROGRESS/COMPLETED)
Current results: ClassifiedCategory, ClassifiedType, and CategoryTargetsJson
ExtractedGroupsJson and RoleMappingsJson are retained legacy columns; category-first generation writes them as NULL
Output: GeneratedQrgCount, ProcessingStatus (success/error/skipped), ErrorMessage

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

`K12SAFETY.qrg_ai_calls`

Logs every individual AI agent invocation during QRG generation. Tracks prompt, response, tokens, duration, and status for each call.

Key columns:

RequestId (FK), AnnexId, GeneratedItemId
Call: StepType and StepSequence; category-first generation records the target generation call rather than a four-agent classify/extract/map chain
Input/Output: SystemPrompt, UserPrompt, ResponseRaw, ResponseParsed
Model: ModelProvider, ModelDeployment, ModelVersion
Tokens: PromptTokens, CompletionTokens, TotalTokens
Timing: StartedAtUtc, CompletedAtUtc, DurationMs
Status: Status (pending/success/error), ErrorMessage, RetryCount

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

`K12SAFETY.qrg_execution_errors`

Captures workflow-level errors that occur during QRG generation. Linked to specific annexes via the (RequestId, AnnexId) FK to qrg_generation_annexes.

Key columns:

RequestId (FK), AnnexId
ErrorType, ErrorCategory, ErrorMessage, ErrorContext
FailedStep, FormId, FormName
OccurredAtUtc

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

`K12SAFETY.qrg_edits`

Captures editing results.

Key columns:

RequestId (PK + FK to qrg_requests)
QrgName, Section (BEFORE|DURING|AFTER), InstructionFor
Explanation, Answer, NewEdition, CompletedAtUtc

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

`K12SAFETY.qrg_edit_context`

Stores the organization and role tag context provided for each QRG edit request. Enables full traceability from input to output.

Key columns:

RequestID (unique)
QRG: QrgId, QrgName, QrgType (FUNCTIONAL/EMERGENCY)
Organization: OrganizationId, OrganizationName, OrganizationType (DISTRICT/FACILITY), OrganizationCode, OrganizationAddressJson
Role tags: ProvidedRoleTagsJson, ProvidedRoleTagCount
Edit: Section (BEFORE/DURING/AFTER), InstructionFor, OriginalContentHash, OriginalContentLength
Validation: ContextStatus (complete/partial/missing), ContextStatusMessage

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

Views

The K12SAFETY schema includes 6 QRG-related views for traceability and analysis:

View	Purpose
`vw_qrg_full_traceability`	Full QRG request-to-result traceability (joins requests, generations, items)
`vw_qrg_input_output_trace`	Compares annex input to QRG output (used by QRG analysis queries)
`vw_qrg_ai_call_summary`	AI call metrics per request (token usage, durations, success/error counts)
`vw_qrg_annex_ai_details`	Per-annex AI processing details (classification, extraction, mapping results)
`vw_qrg_error_summary`	Joined error analysis with request and annex context
`vw_qrg_edit_traceability`	QRG edit request-to-result traceability

DDL: db/sqlproj/Mastra.Platform.K12SafetyDb/model/Views/

QRG Schema Diagram

Query playbook

Find request + latest status

SELECT TOP (1)
  RequestId, RequestType, Status, StatusMessage,
  CreatedAtUtc, ModifiedAtUtc, WorkflowRunId
FROM K12SAFETY.qrg_requests
WHERE RequestId = '00000000-0000-0000-0000-000000000000';

Fetch prompts used

SELECT
  RequestId, SystemPromptHash, CreatedAtUtc
FROM K12SAFETY.qrg_request_prompts
WHERE RequestId = '00000000-0000-0000-0000-000000000000';

List generated items

SELECT
  GeneratedItemId, QrgName, QrgType, Functionality, Hazard, CreatedAtUtc
FROM K12SAFETY.qrg_generated_items
WHERE RequestId = '00000000-0000-0000-0000-000000000000'
ORDER BY GeneratedItemId;

Get all generated items for an EOP

SELECT
  i.GeneratedItemId, i.QrgName, i.QrgType, g.EmergencyOperationPlanId, g.CompletedAtUtc
FROM K12SAFETY.qrg_generated_items i
JOIN K12SAFETY.qrg_generations g ON g.RequestId = i.RequestId
WHERE g.EmergencyOperationPlanId = '00000000-0000-0000-0000-000000000000'
ORDER BY i.GeneratedItemId;

The mental model​

Tables in the normalized path​

Table relationships​

Tables (what they mean)​

K12SAFETY.qrg_requests​

K12SAFETY.qrg_request_prompts​

K12SAFETY.qrg_request_roles​

K12SAFETY.qrg_generations​

K12SAFETY.qrg_generated_items​

K12SAFETY.qrg_generation_annexes​

K12SAFETY.qrg_ai_calls​

K12SAFETY.qrg_execution_errors​

K12SAFETY.qrg_edits​

K12SAFETY.qrg_edit_context​

Views​

QRG Schema Diagram​

Query playbook​

Find request + latest status​

Fetch prompts used​

List generated items​

Get all generated items for an EOP​

See also​

The mental model

Tables in the normalized path

Table relationships

Tables (what they mean)

`K12SAFETY.qrg_requests`

`K12SAFETY.qrg_request_prompts`

`K12SAFETY.qrg_request_roles`

`K12SAFETY.qrg_generations`

`K12SAFETY.qrg_generated_items`

`K12SAFETY.qrg_generation_annexes`

`K12SAFETY.qrg_ai_calls`

`K12SAFETY.qrg_execution_errors`

`K12SAFETY.qrg_edits`

`K12SAFETY.qrg_edit_context`

Views

QRG Schema Diagram

Query playbook

Find request + latest status

Fetch prompts used

List generated items

Get all generated items for an EOP

See also