Skip to main content

K12 Database: EOP (request logging & monitoring)

The mental model​

EOP workflows need to be:

  • auditable (what input produced what output)
  • observable (what step is it on, did it fail)
  • recoverable (you can retry or inspect partial state)

To make that possible, K12 uses a small set of tables in the K12SAFETY schema.

The lifecycle (where each table is written)​

Given a RequestID (string) and optionally a SessionID:

  1. Request accepted / logged → K12SAFETY.log_EOP_TaskRequests
  2. Monitoring/timeline updated → K12SAFETY.log_EOP_TaskStatus
  3. Async runner state tracked (when applicable) → K12SAFETY.log_Jobs
  4. Prompt override audited (when applicable) → K12SAFETY.log_EOP_TaskRequestSystemPrompts
  5. Final response persisted → back into log_EOP_TaskRequests (+ log_Jobs.result_payload if async)

This separation is intentional: it allows querying “what was the request” independently from “what is the latest status”.

Tables (what they mean, how to query them)​

K12SAFETY.log_EOP_TaskRequests (request + response envelope)​

Purpose: the canonical row for “what request came in and what response did we produce”.

Primary lookup columns:

  • RequestID, SessionID, TaskName

Commonly used fields:

  • Request side: RequestFormName, RequestFormFieldContent, RequestPrompt, RawRequestJson
  • Response side: ResponseStatus, ResponseMessage, ResponseIsEdit, ResponsePayloadJson, RawResponseJson
  • Correlation: WorkflowRunId, EmergencyOperationPlanId, EmergencyOperationPlanConfigurationId

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

K12SAFETY.log_EOP_TaskStatus (status timeline)​

Purpose: a time-ordered timeline of status updates for monitoring.

Key columns:

  • RequestID, SessionID, WorkflowRunId, Status, Message, timestamps

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

K12SAFETY.log_Jobs (async execution state)​

Purpose: job-runner state for background execution.

Key columns:

  • request_id (PK), job_type, status, attempt_count, input_payload, result_payload, error

Important join detail:

  • log_Jobs.request_id is NVARCHAR(64).
  • log_EOP_TaskRequests.RequestID is NVARCHAR(200).
  • The system treats these as the same conceptual requestId, but sizes differ.

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

K12SAFETY.log_EOP_TaskRequestSystemPrompts (prompt override auditing)​

Purpose: persist the exact system prompt used for a request when an override is applied.

Key columns:

  • (RequestID, SessionID) (unique), PlanId, SystemPromptOverrideUsed, PromptHash, MetaJson

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

K12SAFETY.log_EOP_TaskRequestContext (resolved context)​

Purpose: captures the fully resolved context for each EOP annex request -- the snapshot, organization, and role tags that were used during processing. Enables full traceability from input IDs to resolved entities.

Primary lookup columns:

  • RequestID, SessionID (unique pair)

Key fields:

  • Input: InputPlanId, InputRoleId
  • Snapshot: SnapshotId, SnapshotStatus, SnapshotVersionMajor/Minor, SnapshotGeneralUserTagIds
  • Organization: OrganizationId, OrganizationName, OrganizationType (DISTRICT/FACILITY), OrganizationCode, OrganizationAddressJson
  • Role resolution: RoleTagSource, RoleTagIdsBeforeFilter, RoleTagIdsAfterFilter, ResolvedRoleTagsJson, StatusFilter, RoleResolutionStatus, RoleResolutionError
  • Status: ContextResolutionStatus (complete/partial/failed), ContextResolutionError

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

Views​

K12SAFETY.vw_eop_request_traceability -- Joins EOP request, status, job, and prompt data into a single view for end-to-end request traceability.

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

EOP Schema Diagram​

Debug playbook (copy/paste)​

1) Find the request row​

SELECT TOP (1)
  ID, TaskName, RequestID, SessionID, WorkflowRunId,
  ResponseStatus, ResponseMessage,
  CreatedAt, ModifiedAt
FROM K12SAFETY.log_EOP_TaskRequests
WHERE RequestID = '...'
ORDER BY CreatedAt DESC;

2) See the status timeline​

SELECT TOP (50)
  ID, Status, Message, WorkflowRunId, CreatedAt, ModifiedAt
FROM K12SAFETY.log_EOP_TaskStatus
WHERE RequestID = '...'
ORDER BY CreatedAt ASC;

3) If async, inspect the job row​

SELECT
  request_id, job_type, status, attempt_count,
  created_at, updated_at,
  error
FROM K12SAFETY.log_Jobs
WHERE request_id = '...';

4) If behavior looks “prompt-dependent”, check prompt audit​

SELECT TOP (1)
  RequestID, SessionID, PromptHash, CreatedAt, ModifiedAt
FROM K12SAFETY.log_EOP_TaskRequestSystemPrompts
WHERE RequestID = '...';

See also​