Database: Core schema

What "Core" is

Core.* tables are shared, cross-cutting database assets used across all workloads.

They support:

• Agent configuration — Versioned agent prompts and LLM overrides
• Multi-environment management — Control table for syncing agents across dev/staging/prod
• LLM configuration — Model capabilities and parameter sets
• Resource dimensions — Azure resource metadata (endpoints, Key Vault secrets)
• Health tooling — Table inventory for health checks

Schema source: db/sqlproj/Mastra.CoreDb/model/

Tables

Core.AgentProfiles

The central repository for agent system prompts and LLM parameter overrides.

Column	Type	Description
AgentProfileSK	BIGINT	Primary key (surrogate)
AgentId	NVARCHAR(128)	Stable agent identifier (e.g., sdac-cost-review-agent)
AgentAlias	NVARCHAR(128)	Human-readable display name
SystemPrompt	NVARCHAR(MAX)	The agent's system prompt
Notes	NVARCHAR(512)	Optional notes/comments
MetadataJson	NVARCHAR(MAX)	Flexible key-value metadata
LlmOverridesJson	NVARCHAR(MAX)	Model parameter overrides (temperature, max tokens)
ProfileHash	CHAR(64)	SHA256 hash for deduplication
IsCurrent	BIT	1 = active version, 0 = historical
VersionNumber	INT	Incremental version counter
EffectiveStartUtc	DATETIME2(3)	When this version became active
EffectiveEndUtc	DATETIME2(3)	When this version was superseded

Key indexes:

• IX_AgentProfiles_Current — Unique filtered index on AgentId where IsCurrent = 1
• IX_AgentProfiles_AliasCurrent — Unique filtered index on AgentAlias where current

Example queries:

-- List all current agents
SELECT AgentId, AgentAlias, VersionNumber, CreatedAtUtc
FROM Core.AgentProfiles
WHERE IsCurrent = 1
ORDER BY AgentId;

-- View version history for an agent
SELECT VersionNumber, EffectiveStartUtc, EffectiveEndUtc, CreatedBy
FROM Core.AgentProfiles
WHERE AgentId = 'sdac-cost-review-agent'
ORDER BY VersionNumber DESC;

Core.AgentProfileControl

Aggregates agent profiles from multiple environments (dev, staging, prod) for comparison and promotion. Used in test environments to manage cross-environment synchronization.

Column	Type	Description
AgentProfileControlSK	BIGINT	Primary key
SourceEnvironment	NVARCHAR(64)	Source environment name (dev, staging, prod)
SourceServer	NVARCHAR(256)	Source database server
SourceDatabase	NVARCHAR(128)	Source database name
SourceAgentProfileSK	BIGINT	Original PK from source database
AgentId	NVARCHAR(128)	Agent identifier
SystemPrompt	NVARCHAR(MAX)	Agent's system prompt
ProfileHash	CHAR(64)	Hash for comparison
IsCurrent	BIT	Whether this was current in source
SyncedAtUtc	DATETIME2(3)	When synced to control table
SyncRequestId	UNIQUEIDENTIFIER	Batch sync identifier

Core.LLMModelCapabilities

Records model-level capabilities and hard limits.

Column	Type	Description
ModelName	NVARCHAR(128)	Model identifier (e.g., gpt-4o)
Capability	NVARCHAR(64)	Capability name (chat, function_calling, vision, reasoning)
IsEnabled	BIT	Whether capability is enabled
MaxTokens	INT	Maximum token limit for this capability

Core.LLMParamsDim

Versioned parameter sets for LLM deployments (temperature, top_p, max tokens).

Column	Type	Description
ModelName	NVARCHAR(128)	Model identifier
DeploymentName	NVARCHAR(128)	Azure deployment name
Temperature	DECIMAL	Temperature setting
TopP	DECIMAL	Top-p (nucleus sampling)
MaxOutputTokens	INT	Maximum output tokens
IsCurrent	BIT	Active version flag

Resource Dimensions

Azure resource metadata tables:

Table	Purpose
Core.AISearchResourceDim	Azure AI Search endpoints and auth
Core.ContentSafetyResourceDim	Azure Content Safety configuration
Core.FormRecognizerResourceDim	Azure Document Intelligence/Form Recognizer

Core.log_TableInventory

Authoritative list of tables for health checks. When adding new tables that should be monitored, add an inventory row.

Views

Core.vw_AgentProfileComparison

Compares current agent profiles across environments to detect drift.

Column	Description
AgentId	Agent identifier
DevHash	Profile hash in dev environment
StagingHash	Profile hash in staging
ProdHash	Profile hash in production
SyncStatus	IN_SYNC, PROD_BEHIND, DEV_AHEAD, DRIFT, MISSING

Example:

SELECT AgentId, SyncStatus, DevVersion, StagingVersion, ProdVersion
FROM Core.vw_AgentProfileComparison
WHERE SyncStatus != 'IN_SYNC';

Stored Procedures

Core.usp_PromoteAgentFromControl

Promotes an agent profile from the control table to the local AgentProfiles table.

DECLARE @NewSK BIGINT;
EXEC Core.usp_PromoteAgentFromControl
    @AgentProfileControlSK = 42,
    @PromotedBy = 'john.doe',
    @NewAgentProfileSK = @NewSK OUTPUT;

Core.usp_PromoteAgentByEnvironment

Promotes an agent by AgentId and source environment (more intuitive).

DECLARE @NewSK BIGINT;
EXEC Core.usp_PromoteAgentByEnvironment
    @AgentId = 'sdac-cost-review-agent',
    @SourceEnvironment = 'prod',
    @PromotedBy = 'release-pipeline',
    @NewAgentProfileSK = @NewSK OUTPUT;

Core.usp_PromoteAllAgentsFromEnvironment

Bulk promotes all agents from an environment.

DECLARE @Promoted INT, @Skipped INT;
EXEC Core.usp_PromoteAllAgentsFromEnvironment
    @SourceEnvironment = 'prod',
    @PromotedBy = 'release-pipeline',
    @PromotedCount = @Promoted OUTPUT,
    @SkippedCount = @Skipped OUTPUT;
-- Output: "=== Promotion Complete: 5 promoted, 3 skipped ==="

ER Diagram

Multi-Environment Workflow

┌─────────────┐     sync_agents_to_control.py      ┌─────────────────────────┐
│   DEV DB    │ ─────────────────────────────────► │                         │
│ AgentProfiles│                                    │      TEST DB            │
└─────────────┘                                    │  AgentProfileControl    │
                                                   │                         │
┌─────────────┐     sync_agents_to_control.py      │  vw_AgentProfileComparison │
│ STAGING DB  │ ─────────────────────────────────► │                         │
│ AgentProfiles│                                    │                         │
└─────────────┘                                    │                         │
                                                   │                         │
┌─────────────┐     sync_agents_to_control.py      │                         │
│   PROD DB   │ ─────────────────────────────────► │                         │
│ AgentProfiles│                                    └─────────────────────────┘
└─────────────┘
                    usp_PromoteAgentByEnvironment
                    ─────────────────────────────►
                    (promote selected agents)

Health check

Validate connectivity and expected schemas:

curl -X POST "$MASTRA_BASE_URL/admin/tools/db-health-check" \
  -H "Content-Type: application/json" \
  -d '{"data":{"schema":"Core","tables":["Core.AgentProfiles","Core.LLMModelCapabilities"]}}'

If health check fails, fix connectivity/env before debugging higher-level workflow behavior.