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 prompts/roles 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 (roles / tags)
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
Store role rows → K12SAFETY.qrg_request_roles
Insert generation envelope → K12SAFETY.qrg_generations
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 one row per role label (and optional tag id) used for generation.

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
AnnexCount, QrgCount, ResultPayloadJson, CompletedAtUtc

ResultPayloadJson Structure:

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

On success:

{
  "root": [
    {
      "qrgName": "Fire Safety - Principal",
      "beforeContent": "<p>...</p>",
      "duringContent": "<p>...</p>",
      "afterContent": "<p>...</p>",
      "generalUserTagIds": ["..."],
      "qrgType": "EMERGENCY",
      "instructionFor": "FIRE"
    }
  ],
  "_meta": {
    "status": "success",
    "aiStats": {
      "totalAnnexes": 6,
      "aiClassifySuccess": 6,
      "aiClassifyError": 0,
      "aiGroupsSuccess": 6,
      "aiGroupsError": 0,
      "aiRolesSuccess": 6,
      "aiRolesError": 0,
      "aiGenerateSuccess": 12,
      "aiGenerateError": 0
    }
  }
}

Note: generalUserTagIds may be an empty array when role mappings fail or return invalid UUIDs. QRGs are still stored without role assignments.

On error (AI failed):

{
  "_meta": {
    "status": "error",
    "errors": [
      "[classify:Fire Safety Annex] AI classification failed: PermissionDenied",
      "[classify:Evacuation Annex] AI classification failed: PermissionDenied"
    ],
    "aiStats": {
      "totalAnnexes": 6,
      "aiClassifySuccess": 0,
      "aiClassifyError": 6,
      "aiGroupsSuccess": 0,
      "aiGroupsError": 0,
      "aiRolesSuccess": 0,
      "aiRolesError": 0,
      "aiGenerateSuccess": 0,
      "aiGenerateError": 0
    }
  }
}

_meta fields:

status - "success" or "error" (no fallback content is ever generated)
errors - Array of detailed error messages from AI call failures
aiStats - Counters tracking AI success vs errors for each workflow step

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 (JSON array), ContentBlocksJson (JSON)

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)
AI pipeline results: ClassifiedCategory, ClassifiedType (FUNCTIONAL/EMERGENCY), ExtractedGroupsJson, RoleMappingsJson
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 (CLASSIFY_CATEGORY/EXTRACT_GROUPS/MAP_ROLES/GENERATE_CONTENT/OTHER), StepSequence
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