Skip to main content

Database: K12 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.

Why a separate table?

  • You can record multiple transitions (accepted → processing → success/failed) without rewriting the request row.

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, so behavior can be reproduced.

Key columns:

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

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

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 = '...';

Common failure modes​

  • Request exists but no status updates: the kickoff log succeeded but the workflow runner didn’t progress.
  • Status shows success but response payload missing: check whether the payload landed in log_Jobs.result_payload (async) vs log_EOP_TaskRequests.ResponsePayloadJson.
  • Hard-to-reproduce output: verify whether a prompt override was used (log_EOP_TaskRequestSystemPrompts).

See also​