Reasoning Engine Database

Overview

The Reasoning Engine uses a dedicated database schema (REASONING) to persist workflow execution state, step outputs, and error logs. This enables:

• Workflow tracing — Track execution from start to finish
• Step-by-step debugging — Inspect outputs from each pipeline stage
• Error analysis — Structured logging for troubleshooting
• Payload management — Efficient storage for large datasets
• Audit trails — Complete execution history

---

Database Schema

Entity-Relationship Diagram

---

Table Descriptions

REASONING.log_ReasoningEngineTasks

Purpose: Parent table for reasoning workflow task runs. Each row represents one complete workflow execution.

Key Columns:

Column	Type	Description
ID	INT	Auto-incrementing primary key
TaskId	NVARCHAR(200)	Unique task identifier (UUID), unique constraint
WorkflowRunId	NVARCHAR(200)	Mastra workflow run ID
WorkflowName	NVARCHAR(510)	Always 'reasoning-engine'
SessionId	NVARCHAR(200)	User session context
UserId	NVARCHAR(200)	Requesting user
Status	NVARCHAR(100)	'pending', 'running', 'completed', 'failed'
Message	NVARCHAR(MAX)	Human-readable status message
DetailsJson	NVARCHAR(MAX)	Structured metadata (JSON)
InputDataRef	NVARCHAR(200)	Reference to input payload in data_ReasoningEnginePayloads
OutputDataRef	NVARCHAR(200)	Reference to output payload
StartedAt	DATETIME2(7)	Workflow start timestamp
CompletedAt	DATETIME2(7)	Workflow completion timestamp
DurationMs	INT	Total execution time in milliseconds
CreatedAt	DATETIME2(7)	Record creation timestamp
ModifiedAt	DATETIME2(7)	Last update timestamp

Indexes:

• Primary key on ID
• Nonclustered index on TaskId
• Nonclustered index on WorkflowRunId (filtered, where not null)
• Nonclustered index on Status

DDL Location: log_ReasoningEngineTasks.sql (db/sqlproj/Mastra.Platform.ReasoningEngineDb/model/Tables/REASONING.log_ReasoningEngineTasks.sql)

---

REASONING.log_StepReasoningEngineTasks

Purpose: Per-step execution logs. The committed workflow has seven logged workflow steps: preflight (0) through validation (6).

Key Columns:

Column	Type	Description
ID	INT	Auto-incrementing primary key
TaskId	NVARCHAR(200)	FK to parent task
WorkflowRunId	NVARCHAR(200)	Mastra workflow run ID
StepName	NVARCHAR(510)	Step identifier (e.g., 'temporal-analysis', 'pattern-recognition')
StepOrder	INT	Workflow execution order (0 through 6 for committed steps)
Status	NVARCHAR(100)	'pending', 'running', 'completed', 'failed', 'skipped'
Message	NVARCHAR(MAX)	Step-specific status message
DetailsJson	NVARCHAR(MAX)	Step metadata (JSON)
InputDataRef	NVARCHAR(200)	Reference to step input payload
OutputDataRef	NVARCHAR(200)	Reference to step output payload
AgentId	NVARCHAR(200)	Registry ID of agent used (e.g., 'reasoning-engine-temporal-summary-agent')
AgentUsed	BIT	Whether AI agent was invoked (default: 0)
ErrorCode	NVARCHAR(100)	Error classification code
ErrorMessage	NVARCHAR(MAX)	Error details if step failed
StartedAt	DATETIME2(7)	Step start timestamp
CompletedAt	DATETIME2(7)	Step completion timestamp
DurationMs	INT	Step execution time in milliseconds
CreatedAt	DATETIME2(7)	Record creation timestamp
ModifiedAt	DATETIME2(7)	Last update timestamp

Indexes:

• Primary key on ID
• Nonclustered index on TaskId
• Nonclustered index on WorkflowRunId (filtered, where not null)
• Nonclustered index on StepName
• Nonclustered index on Status
• Nonclustered composite index on (TaskId, StepOrder)

DDL Location: log_StepReasoningEngineTasks.sql (db/sqlproj/Mastra.Platform.ReasoningEngineDb/model/Tables/REASONING.log_StepReasoningEngineTasks.sql)

---

REASONING.data_WorkflowStepOutputs

Purpose: Stores step outputs for downstream consumption. Enables steps to read prior outputs without passing through workflow state.

Key Columns:

Column	Type	Description
ID	BIGINT	Auto-incrementing primary key
TaskId	NVARCHAR(200)	FK to parent task
StepName	NVARCHAR(510)	Step that produced this output
StepOrder	INT	Execution order for reconstruction
OutputJson	NVARCHAR(MAX)	Full step output (JSON)
AgentUsed	BIT	Whether AI agent contributed (default: 0)
AgentId	NVARCHAR(200)	Registry ID of agent used
DurationMs	INT	Step execution time
CreatedAt	DATETIME2(7)	Record creation timestamp

Constraints:

• Unique constraint on (TaskId, StepName) — One output per step per task

Indexes:

• Primary key on ID
• Nonclustered index on TaskId

Usage Pattern:

// Steps load prior outputs from this table
const priorOutputs = await loadStepOutputs(taskId, ['temporal-analysis', 'pattern-recognition']);

The table also receives an assembled workflow-result row after validation. This row is not a separate Mastra workflow step; it is final result persistence through stepDataService.

DDL Location: data_WorkflowStepOutputs.sql (db/sqlproj/Mastra.Platform.ReasoningEngineDb/model/Tables/REASONING.data_WorkflowStepOutputs.sql)

---

REASONING.data_ReasoningEnginePayloads

Purpose: Large payload storage with content-addressable references. Prevents bloating of task/step tables.

Key Columns:

Column	Type	Description
ID	INT	Auto-incrementing primary key
DataId	NVARCHAR(200)	Unique payload reference (UUID), unique constraint
Kind	NVARCHAR(100)	Payload type: 'step-input', 'step-output', 'series', 'dataset', etc.
TaskId	NVARCHAR(200)	FK to parent task
StepName	NVARCHAR(510)	Producing step (optional)
PayloadJson	NVARCHAR(MAX)	Full payload content (JSON)
ContentHash	NVARCHAR(64)	SHA256 hash for deduplication
SizeBytes	INT	Payload size in bytes
CreatedAt	DATETIME2(7)	Record creation timestamp
ExpiresAt	DATETIME2(7)	TTL for cleanup (nullable)

Indexes:

• Primary key on ID
• Unique nonclustered index on DataId
• Nonclustered index on TaskId
• Nonclustered index on Kind
• Nonclustered index on ExpiresAt (filtered, where not null)
• Nonclustered index on ContentHash (filtered, where not null)

Deduplication:

• Uses ContentHash to detect identical payloads
• Enables storage optimization for repeated datasets

DDL Location: data_ReasoningEnginePayloads.sql (db/sqlproj/Mastra.Platform.ReasoningEngineDb/model/Tables/REASONING.data_ReasoningEnginePayloads.sql)

---

REASONING.log_Error

Purpose: Structured error logging for debugging and alerting.

Key Columns:

Column	Type	Description
ID	BIGINT	Auto-incrementing primary key
TaskId	NVARCHAR(200)	FK to parent task
WorkflowRunId	NVARCHAR(200)	Mastra workflow run ID
StepName	NVARCHAR(510)	Step where error occurred
StepOrder	INT	Step order for timeline reconstruction
ErrorCode	NVARCHAR(100)	Error classification code
ErrorType	NVARCHAR(200)	Error category: 'VALIDATION', 'AGENT', 'CORRELATION', 'DATABASE', etc.
Severity	NVARCHAR(50)	'ERROR', 'WARNING', 'INFO' (default: 'error')
Message	NVARCHAR(MAX)	Error description
StackTrace	NVARCHAR(MAX)	Full stack trace for debugging
ContextJson	NVARCHAR(MAX)	Contextual data (JSON)
AgentId	NVARCHAR(200)	Agent registry ID if applicable
AgentName	NVARCHAR(200)	Human-readable agent name
IsRecoverable	BIT	Whether workflow can continue (default: 1)
RetryCount	INT	Retry attempts (default: 0)
OccurredAt	DATETIME2(7)	Error occurrence timestamp
CreatedAt	DATETIME2(7)	Record creation timestamp

Indexes:

• Primary key on ID
• Nonclustered index on TaskId
• Nonclustered index on WorkflowRunId (filtered, where not null)
• Nonclustered index on StepName (filtered, where not null)
• Nonclustered index on ErrorType (filtered, where not null)
• Nonclustered index on Severity
• Nonclustered composite index on (TaskId, StepOrder)
• Nonclustered index on OccurredAt DESC

DDL Location: log_Error.sql (db/sqlproj/Mastra.Platform.ReasoningEngineDb/model/Tables/REASONING.log_Error.sql)

---

Common Queries

Find recent failed tasks

SELECT TaskId, WorkflowRunId, Status, Message, DurationMs, CreatedAt
FROM REASONING.log_ReasoningEngineTasks
WHERE Status = 'failed'
  AND CreatedAt >= DATEADD(day, -7, SYSUTCDATETIME())
ORDER BY CreatedAt DESC;

Get step timeline for a task

SELECT StepName, StepOrder, Status, AgentUsed, DurationMs, Message
FROM REASONING.log_StepReasoningEngineTasks
WHERE TaskId = @taskId
ORDER BY StepOrder;

Find tasks with specific error types

SELECT t.TaskId, t.Status, e.ErrorType, e.Message, e.StepName
FROM REASONING.log_ReasoningEngineTasks t
JOIN REASONING.log_Error e ON e.TaskId = t.TaskId
WHERE e.ErrorType = 'AGENT'
  AND e.Severity = 'ERROR'
ORDER BY e.OccurredAt DESC;

Reconstruct full workflow result

SELECT StepName, OutputJson, AgentUsed, DurationMs
FROM REASONING.data_WorkflowStepOutputs
WHERE TaskId = @taskId
ORDER BY StepOrder;

Find workflows by user

SELECT TaskId, Status, Message, DurationMs, CreatedAt
FROM REASONING.log_ReasoningEngineTasks
WHERE UserId = @userId
  AND CreatedAt >= DATEADD(day, -30, SYSUTCDATETIME())
ORDER BY CreatedAt DESC;

Analyze step performance

SELECT
    StepName,
    COUNT(*) AS ExecutionCount,
    AVG(DurationMs) AS AvgDurationMs,
    MIN(DurationMs) AS MinDurationMs,
    MAX(DurationMs) AS MaxDurationMs,
    SUM(CASE WHEN AgentUsed = 1 THEN 1 ELSE 0 END) AS AgentUsedCount
FROM REASONING.log_StepReasoningEngineTasks
WHERE CreatedAt >= DATEADD(day, -7, SYSUTCDATETIME())
  AND Status = 'completed'
GROUP BY StepName
ORDER BY AvgDurationMs DESC;

Find large payloads

SELECT
    DataId,
    Kind,
    TaskId,
    SizeBytes,
    SizeBytes / 1024 AS SizeKB,
    CreatedAt
FROM REASONING.data_ReasoningEnginePayloads
WHERE SizeBytes > 1048576  -- > 1MB
ORDER BY SizeBytes DESC;

Error frequency by type

SELECT
    ErrorType,
    Severity,
    COUNT(*) AS ErrorCount,
    COUNT(DISTINCT TaskId) AS AffectedTasks
FROM REASONING.log_Error
WHERE OccurredAt >= DATEADD(day, -7, SYSUTCDATETIME())
GROUP BY ErrorType, Severity
ORDER BY ErrorCount DESC;

---

Database Maintenance

Payload Cleanup

Expired payloads can be cleaned up periodically:

-- Find expired payloads
SELECT DataId, Kind, TaskId, SizeBytes, ExpiresAt
FROM REASONING.data_ReasoningEnginePayloads
WHERE ExpiresAt IS NOT NULL
  AND ExpiresAt < SYSUTCDATETIME();

-- Delete expired payloads (use with caution)
DELETE FROM REASONING.data_ReasoningEnginePayloads
WHERE ExpiresAt IS NOT NULL
  AND ExpiresAt < SYSUTCDATETIME();

Archive Old Tasks

Archive completed tasks older than 90 days:

-- Identify tasks to archive
SELECT TaskId, Status, CompletedAt, DurationMs
FROM REASONING.log_ReasoningEngineTasks
WHERE Status IN ('completed', 'failed')
  AND CompletedAt < DATEADD(day, -90, SYSUTCDATETIME());

-- Archive strategy: Export to external storage, then delete
-- (Implementation depends on archival system)

Index Maintenance

-- Rebuild fragmented indexes
ALTER INDEX ALL ON REASONING.log_ReasoningEngineTasks REBUILD;
ALTER INDEX ALL ON REASONING.log_StepReasoningEngineTasks REBUILD;
ALTER INDEX ALL ON REASONING.data_WorkflowStepOutputs REBUILD;
ALTER INDEX ALL ON REASONING.data_ReasoningEnginePayloads REBUILD;
ALTER INDEX ALL ON REASONING.log_Error REBUILD;

-- Update statistics
UPDATE STATISTICS REASONING.log_ReasoningEngineTasks;
UPDATE STATISTICS REASONING.log_StepReasoningEngineTasks;
UPDATE STATISTICS REASONING.data_WorkflowStepOutputs;
UPDATE STATISTICS REASONING.data_ReasoningEnginePayloads;
UPDATE STATISTICS REASONING.log_Error;

---

Configuration

Environment Variables

Variable	Purpose	Default
AZURE_SQL_SERVER	SQL Server hostname	(required for DB features)
AZURE_SQL_DATABASE	Database name	(required for DB features)
REASONING_SQL_LOGGING	Enable/disable SQL logging	Auto (enabled if SQL configured)
REASONING_ASSUME_TABLES_EXIST	Skip runtime table-existence checks unless set to false	true

Feature Flags

// SQL logging control
const SQL_LOGGING_ENABLED = process.env.REASONING_SQL_LOGGING
  ? process.env.REASONING_SQL_LOGGING !== 'false'
  : HAS_SQL_CONFIG;

// Step output persistence service
const ASSUME_TABLES_EXIST = process.env.REASONING_ASSUME_TABLES_EXIST !== 'false';

Step output persistence is handled by stepDataService. The committed service does not read a REASONING_DB_STEP_DATA feature flag.

---

Troubleshooting

DB logging not working

Symptoms: No records in log_ReasoningEngineTasks or log_StepReasoningEngineTasks

Resolution:

1. Verify environment variables are set:

   echo $AZURE_SQL_SERVER
   echo $AZURE_SQL_DATABASE

2. Check SQL connectivity:

   curl -X POST "$MASTRA_BASE_URL/admin/tools/db-health-check" \
     -H "Content-Type: application/json" \
     -d '{"data":{"schema":"REASONING"}}'

3. Ensure Managed Identity has permissions on the database

Step outputs not persisted

Symptoms: Empty results from data_WorkflowStepOutputs

Resolution:

1. Verify SQL configuration is present:

   echo $AZURE_SQL_SERVER
   echo $AZURE_SQL_DATABASE

2. Check that the REASONING.data_WorkflowStepOutputs table exists and is reachable.
3. If working against a database that may not have the table yet, set REASONING_ASSUME_TABLES_EXIST=false so the service performs the table check path.
4. Check for errors in log_Error and verify the step completed in log_StepReasoningEngineTasks.

Large payloads causing timeouts

Symptoms: Workflow timeouts, large DurationMs values

Resolution:

1. Monitor payload sizes:

   SELECT AVG(SizeBytes), MAX(SizeBytes)
   FROM REASONING.data_ReasoningEnginePayloads;

2. Consider data pre-aggregation in intake adapters
3. Use stepPlan or step toggles to skip expensive analysis that is not needed for the request

---

Schema Deployment

DDL Location

All table DDL scripts are located in:

db/sqlproj/Mastra.Platform.ReasoningEngineDb/model/Tables/

Table	DDL File
log_ReasoningEngineTasks	REASONING.log_ReasoningEngineTasks.sql
log_StepReasoningEngineTasks	REASONING.log_StepReasoningEngineTasks.sql
data_WorkflowStepOutputs	REASONING.data_WorkflowStepOutputs.sql
data_ReasoningEnginePayloads	REASONING.data_ReasoningEnginePayloads.sql
log_Error	REASONING.log_Error.sql

Deployment Process

See Database Deployment for DACPAC-based deployment instructions.

---

Next Steps

Reasoning Engine Overview

Architecture and design philosophy

Orchestration Details

Step-by-step workflow documentation

Database Deployment

DACPAC deployment guide

Database: Core

Core schema and agent profiles