SDAC Database Schema

Overview

The SDAC schema stores cost report data, personnel records, validation results, and review workflow state. It follows a dimensional modeling approach with fact and dimension tables.

Schema source: db/sqlproj/Mastra.Platform.SDACDb/model/

Schema Summary

Type	Count	Tables
Dimension tables (dim_)	2	Districts, ValidationRules
Data tables (data_)	2	CostReports, PersonnelRecords
Fact tables (fact_)	3	AIAnalysisResults, ConversationHistory, UserFeedback
Analysis tables (analysis_)	6	AgentRetrievalAudit, Evidence, EvidenceFindings, EvidenceReportRefs, Results, ReviewDecisions
Validation tables (val_)	1	ValidationResults
Workflow tables (wf_)	2	ReviewRequests, ReviewDecisions
Log tables (log_)	3	AIAgentCalls, FileProcessing, PromptTuningRuns
Views	3	PersonnelWithValidation, ReviewStatus, ValidationErrorSummary

Tables

SDAC.data_CostReports

Central repository for SDAC cost report submissions.

Column	Type	Description
ReportId	UNIQUEIDENTIFIER	Primary key
DistrictSK	BIGINT	Foreign key to dim_Districts
Quarter	INT	Quarter number (1-4)
Year	INT	Fiscal year
FiscalPeriod	NVARCHAR(20)	Period identifier (e.g., "Q1-2024")
SourceFileName	NVARCHAR(500)	Original uploaded file name
FileHash	CHAR(64)	SHA256 hash for deduplication
ProcessingStatus	NVARCHAR(20)	pending, processing, success, error, partial
ExternalDistrictId	NVARCHAR(100)	External district identifier at time of report submission (e.g. TherapyLog ID). Denormalized from dim_Districts for traceability when the district mapping changes. Indexed by period.
TotalSalaryPool1	DECIMAL(18,2)	Sum of Pool 1 salaries
TotalSalaryPool2	DECIMAL(18,2)	Sum of Pool 2 salaries
TotalFringePool1	DECIMAL(18,2)	Sum of Pool 1 fringe benefits
TotalFringePool2	DECIMAL(18,2)	Sum of Pool 2 fringe benefits

Key indexes:

• IX_data_CostReports_FileHash — Fast duplicate detection
• IX_data_CostReports_District_Period — District + fiscal period lookups
• IX_data_CostReports_Status — Filter by processing status

SDAC.data_PersonnelRecords

Individual personnel records extracted from cost reports.

Column	Type	Description
PersonnelRecordId	BIGINT	Primary key
ReportId	UNIQUEIDENTIFIER	FK to data_CostReports
RowNumber	INT	Original row number from Excel
EmployeeId	NVARCHAR(100)	Employee identifier
JobTitle	NVARCHAR(200)	Job title
SourceCode	NVARCHAR(10)	Source code (0-4)
FunctionCode	NVARCHAR(10)	Function code
CostPool	NVARCHAR(10)	Cost pool assignment
GrossSalary	DECIMAL(18,2)	Total salary
ClaimableSalary	DECIMAL(18,2)	Claimable portion
GrossFringe	DECIMAL(18,2)	Total fringe benefits
ClaimableFringe	DECIMAL(18,2)	Claimable fringe
IsReplacement	BIT	Replacement position flag
Comments	NVARCHAR(MAX)	Comment field from report

SDAC.dim_Districts

Reference table for school districts.

Column	Type	Description
DistrictSK	BIGINT	Surrogate key
DistrictId	NVARCHAR(50)	Business key
DistrictName	NVARCHAR(200)	Full district name
ExternalDistrictId	NVARCHAR(100)	External system identifier (e.g. TherapyLog district ID). Stored separately so sync systems cannot overwrite the human-readable DistrictName with a raw ID. Unique filtered index (non-NULL rows only).
CountyName	NVARCHAR(100)	County
RegionCode	NVARCHAR(20)	Regional code
IsActive	BIT	Active flag

SDAC.dim_ValidationRules

Validation rule definitions for cost report validation.

Column	Type	Description
RuleId	BIGINT	Primary key
RuleName	NVARCHAR(100)	Rule identifier
Category	NVARCHAR(50)	SOURCE_CODE, FUNCTION_CODE, SALARY, etc.
Description	NVARCHAR(MAX)	Human-readable description
Severity	NVARCHAR(20)	ERROR or WARNING
IsActive	BIT	Whether rule is enabled
IsCurrent	BIT	Current version flag

SDAC.val_ValidationResults

Stores validation execution results.

Column	Type	Description
ValidationResultId	BIGINT	Primary key
ReportId	UNIQUEIDENTIFIER	FK to cost report
PersonnelRecordId	BIGINT	FK to personnel record (nullable for report-level)
RuleId	BIGINT	FK to validation rule
Passed	BIT	Whether validation passed
ErrorMessage	NVARCHAR(MAX)	Error details
CurrentValue	NVARCHAR(500)	Actual value found
ExpectedValue	NVARCHAR(500)	Expected value
ValidatedAtUtc	DATETIME2(3)	Validation timestamp

SDAC.ingestion_UploadJobs

Durable upload job state for the Mastra-owned SDAC upload workflow. The widget polls Mastra for this state while the internal ingestion worker parses the workbook.

Column	Type	Description
JobId	UNIQUEIDENTIFIER	Public Mastra upload job ID
ReportId	UNIQUEIDENTIFIER	Parsed cost report ID, nullable until parsing completes
WorkerJobId	NVARCHAR(100)	Internal ingestion worker job ID
Status	NVARCHAR(30)	queued, processing, success, duplicate, error, or not_found
Stage	NVARCHAR(50)	Current processing stage, such as queued, processing_file, parsed, or failed
Message	NVARCHAR(2000)	Human-readable progress message
FileName	NVARCHAR(512)	Uploaded workbook filename
FileSizeBytes	BIGINT	Uploaded file size
LargeFile	BIT	Whether the upload exceeded the async progress threshold
FileHash	NVARCHAR(128)	Hash used for duplicate detection
UserEmail, UserName, DistrictName	NVARCHAR	Upload context supplied by the widget or API caller
ForceReingest	BIT	Whether duplicate detection was bypassed
WorkerStatusUrl	NVARCHAR(1000)	Internal worker status URL mirrored by Mastra
AnalysisTriggered	BIT	Whether Mastra has started sdac-report-analysis
AnalysisRunId	NVARCHAR(100)	Report-analysis runtime run ID
AnalysisStatus	NVARCHAR(MAX)	JSON workflow status snapshot
ResultJson	NVARCHAR(MAX)	Last successful worker payload
ErrorJson	NVARCHAR(MAX)	Last worker or Mastra upload error payload
CreatedAtUtc, UpdatedAtUtc, LastHeartbeatAtUtc, CompletedAtUtc, ExpiresAtUtc	DATETIME2(3)	Lifecycle timestamps

Key indexes:

• IX_ingestion_UploadJobs_StatusUpdated -- active job/status dashboard lookups
• IX_ingestion_UploadJobs_ReportId -- report-to-upload traceability
• IX_ingestion_UploadJobs_WorkerJobId -- worker status correlation

SDAC.analysis_Results

Pre-computed analysis results from validation tools and the post-ingestion report analysis workflow. Results are cached for fast retrieval by agents, dashboards, retry flows, and report packet construction.

Column	Type	Description
AnalysisId	BIGINT	Primary key (identity)
ReportId	UNIQUEIDENTIFIER	FK to data_CostReports
AnalysisType	NVARCHAR(50)	SOURCE_CODE, FUNCTION_CODE, SALARY, LINKAGE_CANONICAL, REPORT_ANALYSIS_PACKET, etc.
ToolId	NVARCHAR(100)	Tool or workflow that produced the result (e.g., sdac-validate-source-codes, sdac-report-analysis-workflow)
Status	NVARCHAR(20)	pending, running, completed, error, stale, blocked
VisibilityStatus	NVARCHAR(20)	visible or disabled; conversational tools hide disabled rows while preserving stored results
AnalysisVersion	NVARCHAR(64)	Algorithm/prompt version that produced the row
InputHash	NVARCHAR(128)	Stable input/dependency hash used for freshness checks
DependencyJson	NVARCHAR(MAX)	JSON dependency metadata, such as canonical linkage hash
BlockedReason	NVARCHAR(1000)	Reason an analysis was blocked by prerequisite issues
ResultJson	NVARCHAR(MAX)	Full tool output JSON (NULL if pending/error)
Passed	BIT	Whether all checks passed (denormalized)
ErrorCount	INT	Number of errors found
WarningCount	INT	Number of warnings found
RecordsChecked	INT	Records evaluated
ErrorMessage	NVARCHAR(2000)	Error description (when Status = error)
ErrorDetails	NVARCHAR(MAX)	Stack trace or context (when Status = error)
StartedAtUtc	DATETIME2(3)	Analysis start time
CompletedAtUtc	DATETIME2(3)	Analysis completion time
ExecutionTimeMs	INT	Duration in milliseconds
TriggeredBy	NVARCHAR(50)	ingestion, on_demand, or retry
CreatedAtUtc	DATETIME2(3)	Row creation timestamp
UpdatedAtUtc	DATETIME2(3)	Last update timestamp

Key constraints:

• UQ_analysis_Results_ReportType -- One row per report + analysis type
• CK_analysis_Results_Status -- Status must be one of the six values above
• CK_analysis_Results_VisibilityStatus -- Visibility must be visible or disabled
• CK_analysis_Results_ResultJson -- ResultJson must be valid JSON or NULL
• CK_analysis_Results_DependencyJson -- DependencyJson must be valid JSON or NULL

Key indexes:

• IX_analysis_Results_ReportId -- Fast lookup by report with summary columns
• IX_analysis_Results_ReportStatus -- Report + status for finding completed/stale analyses
• IX_analysis_Results_ReportVisibilityStatus -- Report + visibility + status for conversational retrieval
• IX_analysis_Results_Errors -- Filtered index for error retry dashboard
• IX_analysis_Results_Stale -- Filtered index for re-computation queue

The report analysis workflow stores a final packet in this table using AnalysisType = REPORT_ANALYSIS_PACKET and ToolId = sdac-report-analysis-workflow. Agents read that packet through sdac-get-analysis-packet for broad review questions and read individual rows through sdac-get-analysis-result for focused questions.

SDAC.analysis_AgentRetrievalAudit

Audit trail of SDAC conversational-agent analysis retrieval. This table is written from actual Mastra chat stream tool-call and tool-result events, so it records what analysis retrieval tools the agent really called during a conversation turn.

Column	Type	Description
AuditId	BIGINT	Primary key (identity)
ReportId	UNIQUEIDENTIFIER	FK to data_CostReports; nullable when a tool is called before report context is resolved
ConversationId	NVARCHAR(100)	Conversation/session key from the SDAC chat route
TurnNumber	INT	Conversation turn number
UserId	NVARCHAR(100)	Authenticated user identifier when available
AgentId	NVARCHAR(128)	Agent profile that made the tool call
ToolCallId	NVARCHAR(128)	Mastra stream tool-call ID
ToolName	NVARCHAR(128)	Tool called by the agent
RetrievalKind	NVARCHAR(50)	packet, result, evidence_search, workflow_status, raw_tool, or other
Status	NVARCHAR(20)	started, completed, or error
ForConversation	BIT	Whether the tool was called for conversational output filtering
IncludeRaw	BIT	Whether raw result payloads were requested
InputJson	NVARCHAR(MAX)	Tool input captured from the stream payload
RequestedAnalysisJson	NVARCHAR(MAX)	Analysis types requested by the agent
ReturnedAnalysisJson	NVARCHAR(MAX)	Analysis types returned by the tool
MissingAnalysisJson	NVARCHAR(MAX)	Requested analysis types that were unavailable or filtered
CoverageStatus	NVARCHAR(20)	complete, partial, single, none, unknown, or not_applicable
CoverageJson	NVARCHAR(MAX)	Compact coverage summary, including counts by status where available
OutputSummaryJson	NVARCHAR(MAX)	Compact summarized output; full tool output is not duplicated here
OutputHash	CHAR(64)	SHA256 hash of the summarized output for change/audit comparison
ResultSummary	NVARCHAR(1000)	Human-readable summary of the retrieval result
StartedAtUtc, CompletedAtUtc, DurationMs	DATETIME2 / INT	Tool-call timing
ExpiresAtUtc	DATETIME2(3)	Retention expiry, currently 180 days after creation

Key constraints:

• CK_analysis_AgentRetrievalAudit_RetrievalKind -- RetrievalKind must be one of the supported categories
• CK_analysis_AgentRetrievalAudit_Status -- Status must be started, completed, or error
• CK_analysis_AgentRetrievalAudit_CoverageStatus -- CoverageStatus must be one of the supported values
• JSON check constraints validate all JSON summary columns when they are not NULL

Key indexes:

• IX_analysis_AgentRetrievalAudit_ReportTurn -- Report + conversation turn audit review
• IX_analysis_AgentRetrievalAudit_Conversation -- Conversation-level audit review
• IX_analysis_AgentRetrievalAudit_Tool -- Tool usage auditing and troubleshooting
• UX_analysis_AgentRetrievalAudit_ToolCall -- One audit row per conversation turn + tool call ID when those values are present

Example audit query:

SELECT
    StartedAtUtc,
    CompletedAtUtc,
    AgentId,
    ToolName,
    RetrievalKind,
    Status,
    CoverageStatus,
    RequestedAnalysisJson,
    ReturnedAnalysisJson,
    MissingAnalysisJson,
    ResultSummary
FROM SDAC.analysis_AgentRetrievalAudit
WHERE ReportId = @ReportId
  AND ConversationId = @ConversationId
ORDER BY TurnNumber, StartedAtUtc;

Use this table with SDAC.fact_ConversationHistory to compare what the agent retrieved against what it said in the final response.

SDAC.fact_AIAnalysisResults

Stores AI analysis findings from agent processing.

Column	Type	Description
AnalysisResultId	BIGINT	Primary key
ReportId	UNIQUEIDENTIFIER	FK to cost report
AgentId	NVARCHAR(128)	Agent that produced analysis
AnalysisType	NVARCHAR(50)	Type of analysis performed
FindingsJson	NVARCHAR(MAX)	JSON-formatted findings
Confidence	DECIMAL(5,4)	Confidence score (0-1)
CreatedAtUtc	DATETIME2(3)	Analysis timestamp

SDAC.fact_ConversationHistory

Conversation turns for the SDAC AI Widget, enabling context-aware responses and conversation resume.

Column	Type	Description
ConversationSK	BIGINT	Primary key (identity)
ReportId	UNIQUEIDENTIFIER	FK to data_CostReports
UserId	NVARCHAR(100)	User identifier from auth
SessionId	NVARCHAR(100)	Browser session ID (used as conversation key)
TurnNumber	INT	Sequence number within conversation
Role	NVARCHAR(20)	user, assistant, or system
MessageContent	NVARCHAR(MAX)	Message content
RecordSK	BIGINT	FK to data_PersonnelRecords (optional context)
RuleId	NVARCHAR(50)	Validation rule reference (optional context)
ModelUsed	NVARCHAR(100)	LLM deployment name used
PromptTokens	INT	Input tokens consumed
CompletionTokens	INT	Output tokens consumed
TotalTokens	INT	Total tokens consumed
CreatedAtUtc	DATETIME2(3)	Turn timestamp
UpdatedAtUtc	DATETIME2(3)	Last update timestamp
ExpiresAtUtc	DATETIME2(3)	Retention expiry (90-day default)

Key indexes:

• IX_fact_ConversationHistory_Session — Session + turn number lookups
• IX_fact_ConversationHistory_Report — Report-based history queries
• IX_fact_ConversationHistory_User — User-based history queries

API Endpoint: POST /sdac/chat direct runtime, or POST /api/ingestion/sdac/chat through the widget proxy -- SSE streaming endpoint with server-side session management

SDAC.wf_ReviewRequests

Review request workflow queue.

Column	Type	Description
ReviewRequestId	BIGINT	Primary key
ReportId	UNIQUEIDENTIFIER	FK to cost report
RequestType	NVARCHAR(50)	Type of review requested
Priority	INT	Priority level (1-5)
Status	NVARCHAR(20)	pending, in_review, completed, rejected
AssignedTo	NVARCHAR(128)	Assigned reviewer
RequestedAtUtc	DATETIME2(3)	Request timestamp
DueAtUtc	DATETIME2(3)	Due date

SDAC.wf_ReviewDecisions

Review workflow decisions and approvals.

Column	Type	Description
DecisionId	BIGINT	Primary key
ReviewRequestId	BIGINT	FK to review request
Decision	NVARCHAR(20)	approved, rejected, needs_revision
DecisionNotes	NVARCHAR(MAX)	Reviewer comments
DecidedBy	NVARCHAR(128)	Decision maker
DecidedAtUtc	DATETIME2(3)	Decision timestamp

SDAC.log_AIAgentCalls

Audit trail of AI agent invocations.

Column	Type	Description
CallId	BIGINT	Primary key
ReportId	UNIQUEIDENTIFIER	FK to cost report
AgentId	NVARCHAR(128)	Agent identifier
ToolName	NVARCHAR(128)	Tool invoked
InputPayloadJson	NVARCHAR(MAX)	Input parameters
OutputPayloadJson	NVARCHAR(MAX)	Agent response
DurationMs	INT	Execution time
TokensUsed	INT	Total tokens consumed
CreatedAtUtc	DATETIME2(3)	Call timestamp

SDAC.fact_UserFeedback

User feedback on agent responses, enabling feedback-driven prompt tuning.

Column	Type	Description
FeedbackSK	BIGINT	Primary key (identity)
ConversationSK	BIGINT	FK to fact_ConversationHistory (the turn being rated)
ReportId	UNIQUEIDENTIFIER	FK to data_CostReports
UserId	NVARCHAR(100)	User identifier
SessionId	NVARCHAR(100)	Browser session ID
Rating	TINYINT	Rating 1-5
Category	NVARCHAR(30)	accuracy, clarity, relevance, helpfulness, tone, other
Comment	NVARCHAR(2000)	Optional free-text feedback
AgentId	NVARCHAR(128)	Denormalized for aggregation
TurnNumber	INT	Turn being rated
CreatedAtUtc	DATETIME2(3)	Feedback timestamp
ExpiresAtUtc	DATETIME2(3)	Retention expiry (90-day default)

Key indexes:

• IX_fact_UserFeedback_AgentId -- Agent + date aggregation
• UQ_fact_UserFeedback_UserTurn -- Prevents duplicate ratings (same user + turn)

API Endpoints:

• POST /sdac/feedback -- Submit feedback; widget callers use /api/ingestion/sdac/feedback
• GET /sdac/feedback/stats/{agentId} -- Aggregated feedback stats

SDAC.log_PromptTuningRuns

Audit trail of prompt tuning operations performed by the Prompt Tuner agent.

Column	Type	Description
RunId	BIGINT	Primary key (identity)
TargetAgentId	NVARCHAR(128)	Agent whose prompt was tuned
FeedbackCount	INT	Number of feedback records analyzed
FeedbackSummaryJson	NVARCHAR(MAX)	JSON summary of feedback patterns
CurrentPrompt	NVARCHAR(MAX)	Snapshot of prompt before change
ProposedPrompt	NVARCHAR(MAX)	New prompt applied
ChangeRationale	NVARCHAR(MAX)	Why the change was made
ConfidenceScore	DECIMAL(3,2)	Tuner confidence (0.00-1.00)
Status	NVARCHAR(20)	applied, rolled_back, error
AppliedProfileSK	BIGINT	FK to Core.AgentProfiles after apply
TriggeredBy	NVARCHAR(128)	Who triggered the run
CreatedAtUtc	DATETIME2(3)	Run timestamp
ExpiresAtUtc	DATETIME2(3)	Retention expiry (180-day default)

SDAC.log_FileProcessing

File processing pipeline tracking.

Column	Type	Description
ProcessingId	BIGINT	Primary key
ReportId	UNIQUEIDENTIFIER	FK to cost report
Step	NVARCHAR(50)	Processing step name
Status	NVARCHAR(20)	Step status
StartedAtUtc	DATETIME2(3)	Step start time
CompletedAtUtc	DATETIME2(3)	Step completion time
ErrorMessage	NVARCHAR(MAX)	Error details (if failed)

Views

SDAC.vw_PersonnelWithValidation

Joins personnel records with their validation results.

SELECT
    pr.*,
    vr.Passed,
    vr.ErrorMessage,
    vr.RuleId
FROM SDAC.data_PersonnelRecords pr
LEFT JOIN SDAC.val_ValidationResults vr ON pr.PersonnelRecordId = vr.PersonnelRecordId;

SDAC.vw_ReviewStatus

Current status of all review requests with decision history.

SELECT
    rr.ReviewRequestId,
    rr.ReportId,
    rr.Status,
    rr.AssignedTo,
    rd.Decision,
    rd.DecisionNotes
FROM SDAC.wf_ReviewRequests rr
LEFT JOIN SDAC.wf_ReviewDecisions rd ON rr.ReviewRequestId = rd.ReviewRequestId;

SDAC.vw_ValidationErrorSummary

Aggregated validation error counts and patterns.

SELECT
    ReportId,
    RuleId,
    COUNT(*) AS ErrorCount,
    STRING_AGG(ErrorMessage, '; ') AS ErrorMessages
FROM SDAC.val_ValidationResults
WHERE Passed = 0
GROUP BY ReportId, RuleId;

ER Diagram

Data Flow

1. File Upload
   └── log_FileProcessing (step: UPLOAD)
       └── data_CostReports (status: pending)

2. File Processing
   └── log_FileProcessing (step: PARSE, EXTRACT)
       └── data_PersonnelRecords (bulk insert)
       └── data_CostReports (status: success)

3. Validation
   └── dim_ValidationRules (get active rules)
       └── val_ValidationResults (store results)
       └── analysis_Results (cache tool output)

4. AI Analysis
   └── log_AIAgentCalls (audit trail)
       └── fact_AIAnalysisResults (findings)

5. Human Review
   └── wf_ReviewRequests (queue)
       └── wf_ReviewDecisions (decisions)

6. User Feedback
   └── fact_UserFeedback (ratings, comments)
       └── log_PromptTuningRuns (prompt optimization audit)

Query Examples

-- Get all validation errors for a report
SELECT pr.RowNumber, pr.EmployeeId, pr.JobTitle,
       vr.RuleId, vr.ErrorMessage
FROM SDAC.data_PersonnelRecords pr
JOIN SDAC.val_ValidationResults vr ON pr.PersonnelRecordId = vr.PersonnelRecordId
WHERE pr.ReportId = @reportId AND vr.Passed = 0
ORDER BY pr.RowNumber;

-- District validation summary
SELECT d.DistrictName,
       COUNT(DISTINCT cr.ReportId) AS TotalReports,
       SUM(CASE WHEN cr.ProcessingStatus = 'success' THEN 1 ELSE 0 END) AS SuccessfulReports,
       AVG(cr.TotalSalaryPool1 + cr.TotalSalaryPool2) AS AvgTotalSalary
FROM SDAC.dim_Districts d
JOIN SDAC.data_CostReports cr ON d.DistrictSK = cr.DistrictSK
GROUP BY d.DistrictName;

-- Review queue status
SELECT rr.Status, COUNT(*) AS RequestCount
FROM SDAC.wf_ReviewRequests rr
WHERE rr.RequestedAtUtc >= DATEADD(day, -30, GETUTCDATE())
GROUP BY rr.Status;

Related Documentation

• SDAC Tools — Tool implementations that query SDAC tables
• SDAC Agents — Agent configurations for SDAC workflows
• SDAC Workflows — End-to-end workflow documentation
• Database Seeding — How SDAC agents are seeded