Database Seeding & Agent Synchronization

Overview

The database seeding system provides:

1. Environment-specific seeding via SQL scripts organized by schema
2. Multi-environment synchronization via control tables and promotion workflows
3. Python utilities for copying agents between databases

Seeding Infrastructure

All seed scripts live under db/sqlproj/seeds/:

db/sqlproj/seeds/
├── README.md                    # Documentation
├── _run_all.sql                 # Master orchestrator
├── _run_by_env.sql             # Environment-specific orchestrator
├── core/
│   ├── seed_agent_profiles.sql  # Core agent profiles
│   └── seed_llm_capabilities.sql # LLM model capabilities
├── tap/
│   └── seed_agent_profiles.sql  # TAP platform agents
├── k12/
│   └── seed_agent_profiles.sql  # K12 platform agents
├── sdac/
│   └── seed_agent_profiles.sql  # SDAC platform agents
├── reasoning/
│   └── seed_agent_profiles.sql  # Reasoning engine agents
└── tools/
    ├── copy_agents_between_dbs.py    # Copy agents between databases
    └── sync_agents_to_control.py     # Sync to control table

Running Seeds

All Schemas

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

Environment-Specific

Use the _run_by_env.sql script with the ENV variable:

ENV Value	Schemas Included
tap_only	Core + TAP + Reasoning
k12_only	Core + K12 + Reasoning
sdac_only	Core + SDAC + Reasoning
k12_sdac	Core + K12 + SDAC + Reasoning
all	All schemas

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

# TAP-only environment
sqlcmd -S <server> -d <database> -i "db\sqlproj\seeds\_run_by_env.sql" -v ENV="tap_only"

Individual Schema

Run a specific schema's seed directly:

sqlcmd -S <server> -d <database> -i "db\sqlproj\seeds\sdac\seed_agent_profiles.sql"

Idempotent Behavior

All seed scripts use hash-based deduplication:

SET @ProfileHash = CONVERT(CHAR(64), HASHBYTES('SHA2_256',
    CONCAT(@AgentId, @SystemPrompt, ISNULL(@MetadataJson, ''), ISNULL(@LlmOverridesJson, ''))), 2);

IF NOT EXISTS (
    SELECT 1 FROM [Core].[AgentProfiles]
    WHERE [AgentId] = @AgentId AND [ProfileHash] = @ProfileHash AND [IsCurrent] = 1
)
BEGIN
    -- Expire current version
    UPDATE [Core].[AgentProfiles]
    SET [IsCurrent] = 0, [EffectiveEndUtc] = @Now, [UpdatedAtUtc] = @Now
    WHERE [AgentId] = @AgentId AND [IsCurrent] = 1;

    -- Insert new version
    INSERT INTO [Core].[AgentProfiles] (...)
    VALUES (...);
END

Behavior:

• If agent doesn't exist → Insert as version 1
• If agent exists with same hash → Skip (no changes needed)
• If agent exists with different hash → Expire old, insert new version

Multi-Environment Synchronization

The Problem

Agent prompts are developed and tested in dev/test environments but need to be deployed to production. Manual copying is error-prone and doesn't track changes.

The Solution

A three-step workflow using control tables:

┌─────────────┐     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)

Step 1: Sync Environments to Control Table

Use sync_agents_to_control.py to copy agent profiles from each environment to the control table:

# Set source environment variables
export SOURCE_ENVIRONMENT=prod
export SOURCE_AZURE_SQL_SERVER=mastra-prod.database.windows.net
export SOURCE_AZURE_SQL_DATABASE=MastraDb
export SOURCE_AZURE_SQL_USER=admin
export SOURCE_AZURE_SQL_PASSWORD=secret

# Set target (test) environment variables
export TARGET_AZURE_SQL_SERVER=mastra-test.database.windows.net
export TARGET_AZURE_SQL_DATABASE=MastraDb
export TARGET_AZURE_SQL_USER=admin
export TARGET_AZURE_SQL_PASSWORD=secret

# Sync prod to control table
python db/sqlproj/seeds/tools/sync_agents_to_control.py

Repeat for dev and staging environments.

Step 2: Compare Environments

Use the vw_AgentProfileComparison view to identify drift:

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

SyncStatus	Meaning
IN_SYNC	All environments have same hash
PROD_BEHIND	Dev/staging match, prod is different
DEV_AHEAD	Dev is different, staging/prod match
DRIFT	All three are different
MISSING	At least one environment is missing the agent

Step 3: Promote Agents

Use stored procedures to promote agents from the control table:

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

-- Promote all agents from prod
DECLARE @Promoted INT, @Skipped INT;
EXEC Core.usp_PromoteAllAgentsFromEnvironment
    @SourceEnvironment = 'prod',
    @PromotedBy = 'release-pipeline',
    @PromotedCount = @Promoted OUTPUT,
    @SkippedCount = @Skipped OUTPUT;

Copying Agents Between Databases

For direct database-to-database copying (without the control table), use copy_agents_between_dbs.py:

# Copy specific agents
export AGENT_IDS="sdac-cost-review-agent,sdac-review-coordinator"
python db/sqlproj/seeds/tools/copy_agents_between_dbs.py

# Copy ALL current agents
unset AGENT_IDS
python db/sqlproj/seeds/tools/copy_agents_between_dbs.py

Required environment variables:

• SOURCE_AZURE_SQL_SERVER, SOURCE_AZURE_SQL_DATABASE, SOURCE_AZURE_SQL_USER, SOURCE_AZURE_SQL_PASSWORD
• TARGET_AZURE_SQL_SERVER, TARGET_AZURE_SQL_DATABASE, TARGET_AZURE_SQL_USER, TARGET_AZURE_SQL_PASSWORD

Seeded Agents by Schema

Core (2 agents)

• testing-model-agent - Basic testing agent

TAP (2 product agents)

• tap-caption-tagging-agent - Caption and tag suggestions from supplied post context and media
• tap-query-agent - Query synthesis from retrieved TAP context

K12 (3 agents)

• k12-eop-analyzer - Emergency operations plan analysis
• k12-qrg-generator - Quick Reference Guide generation
• k12-annex-classifier - Form annex classification

SDAC (2 agents)

• sdac-cost-review-agent - Cost report review and validation
• sdac-review-coordinator - Review workflow orchestration

Reasoning (10 agents)

• reasoning-orchestrator - Main orchestration
• reasoning-temporal-summary - Temporal pattern analysis
• reasoning-pattern-recognition - Pattern detection
• reasoning-correlation-finder - Correlation analysis
• reasoning-domain-retrieval - Domain knowledge retrieval
• reasoning-content-analyzer - Content analysis
• reasoning-explanation - Explanation generation
• reasoning-validation - Validation and verification
• reasoning-normalizer - Output normalization
• reasoning-analysis-summary - Summary generation

Verification Queries

Check Current Agents

SELECT AgentId, AgentAlias, VersionNumber, SourceSystem, CreatedAtUtc
FROM Core.AgentProfiles
WHERE IsCurrent = 1
ORDER BY AgentId;

Count by Source System

SELECT SourceSystem, COUNT(*) AS AgentCount
FROM Core.AgentProfiles
WHERE IsCurrent = 1
GROUP BY SourceSystem;

View Agent History

SELECT AgentId, VersionNumber, IsCurrent, EffectiveStartUtc, EffectiveEndUtc, CreatedBy
FROM Core.AgentProfiles
WHERE AgentId = 'sdac-cost-review-agent'
ORDER BY VersionNumber DESC;

Compare Profile Hashes

SELECT AgentId, ProfileHash, SourceEnvironment, SyncedAtUtc
FROM Core.AgentProfileControl
WHERE IsCurrent = 1
ORDER BY AgentId, SourceEnvironment;

Best Practices

1. Always Use Environment-Specific Seeds

Don't deploy all schemas to environments that don't need them:

# TAP production - only TAP agents
sqlcmd ... -i "_run_by_env.sql" -v ENV="tap_only"

# K12+SDAC shared environment
sqlcmd ... -i "_run_by_env.sql" -v ENV="k12_sdac"

2. Sync Before Major Releases

Before deploying to production, sync all environments to the control table and review drift:

-- Find agents that need attention
SELECT * FROM Core.vw_AgentProfileComparison
WHERE SyncStatus IN ('DRIFT', 'PROD_BEHIND');

3. Use Descriptive Promotion Metadata

When promoting agents, include meaningful @PromotedBy values:

EXEC Core.usp_PromoteAgentByEnvironment
    @AgentId = 'sdac-cost-review-agent',
    @SourceEnvironment = 'staging',
    @PromotedBy = 'release-2024.1-pipeline';  -- Traceable to release

4. Backup Before Bulk Operations

Export current profiles before bulk promotions:

SELECT * INTO #AgentBackup FROM Core.AgentProfiles WHERE IsCurrent = 1;

Related Documentation

• Core Schema - AgentProfiles and AgentProfileControl tables
• DACPAC Deployment - Building and deploying schemas
• Database Overview - Schema organization