Skip to main content

Database Deployment (DACPAC)

Overview​

The database schemas (Core, K12SAFETY, TAP, REASONING) 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

New: The Core DB DACPAC now automatically seeds base agents and reasoning engine agents via post-deployment scripts. See Database Seeding for details.

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.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
OptionRecommendedDescription
BlockOnPossibleDataLossTruePrevents destructive changes (column drops, type changes)
DropObjectsNotInSourceFalseDon't drop objects not in DACPAC (safer for shared DBs)
AllowIncompatiblePlatformFalseEnforce target platform compatibility
GenerateSmartDefaultsTrueAuto-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];

-- 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];

-- 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];

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 Seeding​

The Core DB DACPAC includes post-deployment scripts that automatically seed:

  • Base agents (2): content-moderator, admin-assistant
  • Reasoning engine agents (10): reasoning-*

These scripts are idempotent - they only insert agents if no current version (IsCurrent = 1) exists, preserving any customizations made via the Admin UI.

For environment-specific configuration (model deployments, platform-specific agents), use the Web UI at /setup/db.

See the Database Seeding guide for complete details on:

  • Automatic DACPAC-based seeding
  • Manual Web UI configuration
  • Bidirectional sync workflow (Test → Source Control → Production)

Next steps​