Database Deployment (DACPAC)

Overview

The database schemas (Core, Memory, MastraNative, Ingestion, K12SAFETY, SDAC, TAP, REASONING, RECOVERED, and HANDBOOK) are deployed using SQL Server Data Tools (SSDT) projects that produce DACPAC files. DACPACs provide declarative, version-controlled schema deployment with built-in safety checks.

info

DACPAC post-deployment scripts intentionally do not seed agent profiles. Runtime seedConfig, explicit seed scripts, Admin tools, and manifest-backed model seeding are separate contracts. See Database Seeding.

Project structure

All DACPAC projects live under db/sqlproj/:

db/sqlproj/
├── Mastra.Databases.sln              # Solution containing all projects
├── Mastra.CoreDb/                     # Core schema project
├── Mastra.Platform.K12SafetyDb/       # K12Safety schema project
├── Mastra.Platform.TapDb/             # TAP schema project
├── Mastra.Platform.ReasoningEngineDb/ # Reasoning schema project
├── Mastra.Platform.SDACDb/            # SDAC schema project
├── Mastra.Platform.RecoveredDb/       # Recovered schema project
├── Mastra.Platform.IngestionDb/       # Ingestion schema project
├── Mastra.Platform.HandbookDb/        # Handbook schema project
└── Mastra.AllDb/                      # Combined project (references all)

Deployment options

• Full deployment: Build and publish Mastra.AllDb to deploy all schemas in one operation
• Individual deployment: Build and publish any sub-project independently for schema-specific updates

Building DACPACs

Visual Studio

1. Open db/sqlproj/Mastra.Databases.sln
2. Right-click the desired project and select Build
3. DACPAC output: bin/Debug/<ProjectName>.dacpac

Command Line (MSBuild)

# Single project
msbuild .\Mastra.CoreDb\Mastra.CoreDb.sqlproj /t:Build /p:Configuration=Release

# Combined project (builds all dependencies)
msbuild .\Mastra.AllDb\Mastra.AllDb.sqlproj /t:Build /p:Configuration=Release

PowerShell Scripts

Each project has a build-dacpac.ps1 script:

# Build individual project
.\Mastra.CoreDb\build-dacpac.ps1 -Configuration Release

# Build combined project
.\Mastra.AllDb\build-dacpac.ps1 -Configuration Release

Publishing (Deploying) DACPACs

SqlPackage CLI

# Publish combined DACPAC
SqlPackage /Action:Publish `
  /SourceFile:".\Mastra.AllDb\bin\Output\Mastra.AllDb.dacpac" `
  /TargetConnectionString:"Server=tcp:<server>.database.windows.net,1433;Initial Catalog=<db>;Authentication=Active Directory Default;Encrypt=True;" `
  /p:BlockOnPossibleDataLoss=True `
  /p:DropObjectsNotInSource=False

Recommended Publish Options

Option	Recommended	Description
BlockOnPossibleDataLoss	True	Prevents destructive changes (column drops, type changes)
DropObjectsNotInSource	False	Don't drop objects not in DACPAC (safer for shared DBs)
AllowIncompatiblePlatform	False	Enforce target platform compatibility
GenerateSmartDefaults	True	Auto-generate defaults for new NOT NULL columns

Required Permissions

DACPAC deployment requires elevated permissions to create/alter schemas, tables, and other objects.

Minimum Permissions for Deployment

The deploying principal (user, service principal, or managed identity) needs:

-- Option 1: Database-level roles (recommended for non-prod)
ALTER ROLE db_ddladmin ADD MEMBER [deployer_principal];
ALTER ROLE db_datareader ADD MEMBER [deployer_principal];
ALTER ROLE db_datawriter ADD MEMBER [deployer_principal];

-- Option 2: db_owner (simpler but broader access)
ALTER ROLE db_owner ADD MEMBER [deployer_principal];

Granular Permissions (Production)

For tighter control in production, grant only what's needed:

-- Schema management
GRANT CREATE SCHEMA TO [deployer_principal];
GRANT ALTER ON SCHEMA::Core TO [deployer_principal];
GRANT ALTER ON SCHEMA::K12SAFETY TO [deployer_principal];
GRANT ALTER ON SCHEMA::REASONING TO [deployer_principal];
GRANT ALTER ON SCHEMA::TAP TO [deployer_principal];
GRANT ALTER ON SCHEMA::SDAC TO [deployer_principal];
GRANT ALTER ON SCHEMA::Ingestion TO [deployer_principal];
GRANT ALTER ON SCHEMA::HANDBOOK TO [deployer_principal];

-- Object creation/modification
GRANT CREATE TABLE TO [deployer_principal];
GRANT CREATE PROCEDURE TO [deployer_principal];
GRANT CREATE FUNCTION TO [deployer_principal];
GRANT CREATE VIEW TO [deployer_principal];
GRANT CREATE TYPE TO [deployer_principal];

-- References (for FK constraints)
GRANT REFERENCES ON SCHEMA::Core TO [deployer_principal];
GRANT REFERENCES ON SCHEMA::K12SAFETY TO [deployer_principal];
GRANT REFERENCES ON SCHEMA::REASONING TO [deployer_principal];
GRANT REFERENCES ON SCHEMA::TAP TO [deployer_principal];
GRANT REFERENCES ON SCHEMA::SDAC TO [deployer_principal];
GRANT REFERENCES ON SCHEMA::Ingestion TO [deployer_principal];
GRANT REFERENCES ON SCHEMA::HANDBOOK TO [deployer_principal];

-- Data operations (for pre/post-deploy scripts, seed data)
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Core TO [deployer_principal];
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::K12SAFETY TO [deployer_principal];
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::REASONING TO [deployer_principal];
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::TAP TO [deployer_principal];
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::SDAC TO [deployer_principal];
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Ingestion TO [deployer_principal];
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::HANDBOOK TO [deployer_principal];

Azure SQL Database

For Azure SQL, use Microsoft Entra ID (Azure AD) authentication:

-- Create the deployer from Entra ID (run as db admin)
CREATE USER [your-service-principal-name] FROM EXTERNAL PROVIDER;

-- Or for a managed identity
CREATE USER [your-managed-identity-name] FROM EXTERNAL PROVIDER;

-- Then grant appropriate role
ALTER ROLE db_owner ADD MEMBER [your-service-principal-name];

GitHub Actions CI/CD

When deploying from GitHub Actions:

1. Configure the GitHub environment OIDC identity
2. Ensure the principal exists as a user in the target database
3. Grant the appropriate role (see above)

Example workflow snippet:

- uses: azure/login@v3
  with:
    client-id: ${{ secrets.AZURE_CLIENT_ID }}
    tenant-id: ${{ secrets.AZURE_TENANT_ID }}
    subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

Rollback strategy

DACPAC deployments are forward-only by design. To roll back:

1. Revert the DACPAC: Build and publish the previous version of the DACPAC from source control
2. Manual rollback: For critical issues, manually drop/alter objects using T-SQL scripts
3. Data recovery: Restore from a point-in-time backup if data loss occurred

Best practice: Test deployments in a staging environment before production.

Troubleshooting Deployment Errors

"Rows were detected. The schema update is terminating because data loss might occur."

This means DacFx detected a risky change (type conversion, column drop). Options:

1. Fix the schema to match the target (recommended)
2. Empty the affected table (dev only)
3. Set BlockOnPossibleDataLoss=False (risky)

"Permission denied" or "CREATE TABLE permission denied"

The deploying principal lacks DDL permissions. Grant db_ddladmin or specific CREATE TABLE permission.

"Cannot find the object because it does not exist or you do not have permissions"

The principal can't see existing objects. Grant db_datareader or SELECT on the schema.

Source of Truth

The canonical schema definitions live in the SSDT SQL projects under db/sqlproj/:

• Mastra.CoreDb -- Core schema (agent profiles, LLM metadata, resource dimensions)
• Mastra.Platform.K12SafetyDb -- K12Safety schema (EOP logging, QRG storage)
• Mastra.Platform.TapDb -- TAP schema
• Mastra.Platform.ReasoningEngineDb -- Reasoning Engine schema
• Mastra.Platform.SDACDb -- SDAC schema
• Mastra.Platform.RecoveredDb -- Recovered/legacy tables
• Mastra.Platform.HandbookDb -- MSBA Handbook RAG audit records

Each project's model/Tables/ folder contains the authoritative DDL used for DACPAC generation and deployment.

Post-deployment and seeding

Mastra.CoreDb/Script.PostDeployment.sql and the aggregate post-deployment script are schema-only and explicitly report that no seed scripts are executed. After schema publication, choose the correct source-backed path:

• runtime agent seedConfig for factory-owned defaults;
• db/sqlproj/seeds/** for reviewed explicit SQL seeds;
• Admin tools/UI for operator-managed versioned profiles and parameters;
• the model-runtime pipeline for deployment-manifest-backed model rows.

Do not present successful DACPAC publication as evidence that application seed data is current.

Next steps

• Database Seeding - Complete seeding guide
• Core schema reference
• Agent Profiles - Agent management and versioning
• K12 database overview