Skip to main content

Database (Overview)

What lives where​

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

  • Azure SQL (schemas: Core, MastraNative, 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, evaluations187
MastraNativeNative Mastra runtime storage/observability tables modeled in the Core DB project100
MemoryMastra memory resources, threads, and messages40
IngestionFile ingestion runs, batches, files, mappings, and config60
K12SAFETYK12 workload: EOP request/status logging, QRG storage, scenario evaluation219
SDACSDAC workload: cost reports, personnel, validation, review workflows213
TAPTAP workload: user context, tagging suggestions, legacy batch/content safety logs163
REASONINGReasoning engine: payloads, step outputs, task logs50
RECOVEREDArchive: historical workflow outputs10
HANDBOOKHandbook RAG, source artifacts, product workbench metadata, and policy-review job/run/finding/reviewer state140

Current SQL-project inventory: 116 tables, 22 views, 6 stored procedures. These counts come from CREATE definitions under db/sqlproj/**/model and should be updated with schema changes rather than treated as a runtime inventory.

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
├── Mastra.Platform.HandbookDb/    # HANDBOOK schema
├── migrations/                    # Cross-project migration scripts
└── seeds/                         # Explicit seed scripts (not automatic DACPAC data)
note

Use db/sqlproj/**/model for schema definitions. Other db/** folders can contain seeds, snapshots, migrations, and historical/service-specific material; they do not replace the SQL projects.

Database projects​

ProjectSchemaPurpose
Mastra.AllDbAllMaster project that aggregates all schemas via MSBuild wildcards
Mastra.CoreDbCore, Memory, MastraNativeAgent profiles, LLM configuration, resource dimensions, memory tables, and native Mastra compatibility tables
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
Mastra.Platform.HandbookDbHANDBOOKHandbook RAG audit, source artifacts, product workbench state, and policy-review persistence

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 agent/docs-chat/model seed helpers
├── handbook/                 # Handbook agent profiles
├── 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 profile seeds
sqlcmd -S <server> -d <database> -i "db\sqlproj\seeds\_run_by_env.sql" -v ENV="k12_sdac"

DACPAC post-deployment scripts intentionally do not run agent/profile seeds. Use runtime seedConfig, explicit scripts under db/sqlproj/seeds, the Admin UI, or the promotion model-runtime seed stage as appropriate.

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​