Skip to main content

Database (Overview)

What lives where​

This repo uses two different persistence layers for two different jobs:

  • Azure SQL (schemas: Core, Memory, Ingestion, K12SAFETY, SDAC, TAP, REASONING, RECOVERED, HANDBOOK)

    • Stores: operational logs, workflow request/status records, agent prompt profiles, cost report data, and validation results.
    • This is the system of record for "what happened" and for the Admin UI-managed agent prompts.

  • Mastra local store (LibSQL) in .mastra/data/mastra.db

    • Stores: agent/workflow memory and Mastra runtime state.
    • This is not the same database as Azure SQL; don't look here for Core.AgentProfiles or K12 request logs.

When debugging workflows, tool execution, request timelines, or prompt overrides, consult Azure SQL as the operational source of truth.

Schemas at a glance​

SchemaPurposeTablesViews
CoreShared tables: agent profiles, LLM metadata, resource dimensions, feature flags, evaluations167
MemoryMastra memory resources, threads, and messages40
IngestionFile ingestion runs, batches, files, mappings, and config50
K12SAFETYK12 workload: EOP request/status logging, QRG storage, scenario evaluation219
SDACSDAC workload: cost reports, personnel, validation, review workflows153
TAPTAP workload: user context, tagging suggestions, legacy batch/content safety logs103
REASONINGReasoning engine: payloads, step outputs, task logs50
RECOVEREDArchive: historical workflow outputs10
HANDBOOKMSBA handbook RAG audit records for retrieval, generation, verification, warnings, and telemetry10

Total: 78 tables, 22 views, 5 stored procedures

Table naming conventions​

PrefixPurposeExample
log_Execution logs, audit trailslog_EOP_TaskStatus, log_AIAgentCalls
data_Data storage (facts, reports)data_CostReports, data_PersonnelRecords
fact_Fact tables (dimensional modeling)fact_AIAnalysisResults
dim_Dimension tables (reference data)dim_Districts, dim_ValidationRules
val_Validation resultsval_ValidationResults
wf_Workflow tableswf_ReviewRequests, wf_ReviewDecisions
qrg_Quick Reference Guide (K12)qrg_generations, qrg_requests
vw_Viewsvw_AgentProfileComparison
usp_Stored proceduresusp_PromoteAgentByEnvironment

Source of truth for schema​

The SSDT/DACPAC projects under db/sqlproj/ are the authoritative schema source:

db/sqlproj/
├── Mastra.Databases.sln           # Solution file
├── Mastra.AllDb/                  # Master project (aggregates all schemas)
├── Mastra.CoreDb/                 # Core schema
├── Mastra.Platform.K12SafetyDb/   # K12SAFETY schema
├── Mastra.Platform.SDACDb/        # SDAC schema
├── Mastra.Platform.TapDb/         # TAP schema
├── Mastra.Platform.ReasoningEngineDb/  # REASONING schema
├── Mastra.Platform.IngestionDb/   # Ingestion schema
├── Mastra.Platform.RecoveredDb/   # RECOVERED schema
├── Mastra.Platform.HandbookDb/    # HANDBOOK schema
├── migrations/                    # Migration scripts
└── seeds/                         # Seed data scripts
note

Legacy folders db/core/ddl/* and db/platform/* have been deprecated. Use db/sqlproj/ for all schema definitions.

Database projects​

ProjectSchemaPurpose
Mastra.AllDbAllMaster project that aggregates all schemas via MSBuild wildcards
Mastra.CoreDbCoreAgent profiles, LLM configuration, resource dimensions
Mastra.Platform.K12SafetyDbK12SAFETYEOP logging, QRG generation and storage
Mastra.Platform.SDACDbSDACCost reports, personnel, validation, review workflows
Mastra.Platform.TapDbTAPUser context, content tagging, safety findings
Mastra.Platform.ReasoningEngineDbREASONINGReasoning engine payloads and task logs
Mastra.Platform.IngestionDbIngestionFile ingestion runs, batches, mappings, and files
Mastra.Platform.RecoveredDbRECOVEREDArchived workflow outputs

Bootstrapping & verification​

Build and deploy a DACPAC:

# Build all schemas
cd db/sqlproj
msbuild Mastra.AllDb/Mastra.AllDb.sqlproj /t:Build /p:Configuration=Release

# Publish to database
SqlPackage /Action:Publish \
  /SourceFile:"./Mastra.AllDb/bin/Release/Mastra.AllDb.dacpac" \
  /TargetConnectionString:"Server=tcp:<server>.database.windows.net,1433;..." \
  /p:BlockOnPossibleDataLoss=True

Verify connectivity + table presence through the registered admin tool:

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

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

Key identifiers (so joins make sense)​

Not every workload uses the same ID types:

WorkloadID TypeExample Column
EOP loggingNVARCHARRequestID, SessionID
QRG tablesUNIQUEIDENTIFIERRequestId
SDAC cost reportsUNIQUEIDENTIFIERReportId
Agent profilesBIGINT (surrogate)AgentProfileSK

When joining or filtering, use the exact table's type (string vs GUID vs BIGINT).

Seeding infrastructure​

Database seeding is organized under db/sqlproj/seeds/:

seeds/
├── _run_all.sql              # Master orchestrator
├── _run_by_env.sql           # Environment-specific (tap_only, sdac_only, k12_sdac, all)
├── core/                     # Core schema seeds
├── k12/                      # K12 schema seeds
├── sdac/                     # SDAC schema seeds
├── tap/                      # TAP schema seeds
├── reasoning/                # Reasoning engine seeds
└── tools/                    # Python utilities
    ├── copy_agents_between_dbs.py    # Copy agents between databases
    └── sync_agents_to_control.py     # Sync to control table

Run seeds for specific environments:

# All schemas
sqlcmd -S <server> -d <database> -i "db\sqlproj\seeds\_run_all.sql"

# K12 + SDAC only
sqlcmd -S <server> -d <database> -i "db\sqlproj\seeds\_run_by_env.sql" -v ENV="k12_sdac"

Agent profile control (multi-environment)​

The Core.AgentProfileControl table aggregates agent profiles from all environments for comparison and management:

-- Sync agents from prod to control table (Python)
python db/sqlproj/seeds/tools/sync_agents_to_control.py

-- Compare profiles across environments
SELECT * FROM Core.vw_AgentProfileComparison;

-- Promote agent from prod to local
EXEC Core.usp_PromoteAgentByEnvironment
    @AgentId = 'sdac-cost-review-agent',
    @SourceEnvironment = 'prod';

Next pages​