Report analysis workflow

Purpose

sdac-report-analysis is the post-ingestion Mastra workflow that turns a parsed SDAC workbook into durable, queryable analysis evidence. It runs after the ingestion service has written the report and personnel rows to the SDAC database. Its output is a persisted REPORT_ANALYSIS_PACKET that conversational agents can retrieve without re-running every validation tool.

The workflow is intentionally separate from the agent. The agent answers from persisted evidence first, then falls back to raw tools only when a result is missing, stale, still running, or explicitly refreshed by the user.

High-level flow

End-to-end tool orchestration

This is the current production path from report upload to a conversational answer. Browser/widget clients call Mastra, not the ingestion worker. Mastra owns public upload/status, persists durable upload progress, calls ingestion as an internal parser worker, then starts the same report-analysis workflow once SDAC.data_CostReports and SDAC.data_PersonnelRecords exist.

Tool call order

The report-analysis path deliberately separates entry-point tools, workflow-owned tools, and answer-retrieval tools.

Order	Caller	Tool or step	What it does	Output/persistence
1	Widget/API client	POST /sdac/upload on Mastra	Creates a durable upload job and submits the workbook to the internal ingestion worker.	SDAC.ingestion_UploadJobs row; jobId; eventual reportId.
2	Widget/API client	GET /sdac/uploads/:jobId/status on Mastra	Mirrors worker progress and starts report analysis after parsing succeeds.	Durable status, report ID, analysis run metadata.
3	Workflow	Load report step	Waits for ingestion readiness and creates pending rows.	Pending rows in SDAC.analysis_Results.
4	Workflow	sdac-cross-reference-replacements, deterministic mode	Performs deterministic replacement/support linkage.	LINKAGE_DETERMINISTIC.
5	Workflow	Conditional AI linkage branch	Runs sdac-cross-reference-replacements with AI assist only when deterministic linkage has blocking unresolved rows; otherwise persists a skipped AI row.	LINKAGE_AI.
6	Workflow	Safe analysis wave	Runs analyses that do not require trusted canonical linkage.	One result row per safe analysis.
7	Workflow	Canonical linkage merge	Chooses deterministic or AI-assisted linkage output and computes blocking linkage issues.	LINKAGE_CANONICAL.
8	Workflow	Linkage-dependent analysis wave	Runs position-dependent analyses only when canonical linkage is clean.	One completed or blocked row per linkage-dependent analysis.
9	Workflow	Packet compile step	Builds one report-level packet from persisted rows.	REPORT_ANALYSIS_PACKET.
10A	Agent	sdac-get-analysis-packet	Retrieves the full packet for broad prompts.	Conversationally visible packet sections.
10B	Agent	sdac-get-analysis-result	Retrieves one persisted analysis for focused prompts.	Conversationally visible single analysis result.

For broad prompts such as "review this report", step 10A is the first answer tool. For focused prompts such as "provide fringe analysis", step 10B is the first answer tool. Raw validation tools should be a fallback path only when persisted evidence is missing, stale, still running, or explicitly refreshed.

Current parallelism

The implementation uses workflow-level parallelism around dependency units, not broad analysis buckets:

Stage	Execution	Notes
Load report	Sequential	Waits for ingestion readiness and creates pending analysis rows.
Deterministic linkage	Sequential	LINKAGE_DETERMINISTIC runs first and decides whether AI help is needed.
Conditional AI linkage + safe analyses	Parallel	LINKAGE_AI runs only when deterministic linkage has unresolved/blocking rows; otherwise it persists a skipped result. Safe analyses run in the same parallel node.
Canonical linkage	Sequential	Merges deterministic linkage and any AI linkage output, then determines blocking issues.
Linkage-dependent branches	Parallel	Every registered linkage-dependent analysis branch runs in the same workflow parallel node after canonical linkage.
Packet compile	Sequential	Reads persisted rows and writes the final packet row.

The maximum current analysis fan-out after deterministic linkage is the conditional AI linkage branch plus all safe analyses. Branches that require clean linkage do not run calculations when canonical linkage has blocking issues; they persist a blocked result immediately. Independent branches continue normally.

When one user-facing analysis has both deterministic and AI work, the intended model is deterministic-first with AI fallback for unresolved cases, then a reducer that persists one canonical result. Linkage follows this pattern: deterministic linkage runs first, AI-assisted linkage only runs for unresolved/blocking deterministic linkage, then canonical linkage persists the merged prerequisite result.

Dependency model

Blocking linkage issues stop position-dependent calculations because salary, fringe, replacement, duplicate, duplicate-context, and vacant analysis rely on canonical position linkage. Those branches still execute their blocking path so the database records why the analysis is unavailable. Independent branches still produce results because they do not require trusted replacement/support attribution.

Analysis registry

The workflow reads its analyzable sections from packages/domain-sdac/src/analysis/analysis-registry.ts.

Analysis type	Tool	Branch kind	Blocks on linkage issues
FILE_METADATA	sdac-validate-file-metadata	safe	No
REPORT_TOTALS	sdac-validate-report-totals	safe	No
SOURCE_CODE	sdac-validate-source-codes	safe	No
FUNCTION_CODE	sdac-validate-function-codes	safe	No
FEDERAL_FUNDING_BACKOUT	sdac-validate-federal-funding-backout	safe	No
INDIRECT_COST_FUNCTION	sdac-validate-indirect-cost-functions	safe	No
COMMENT_QUALITY	sdac-analyze-comment-quality	safe	No
JOB_TITLE_CLASSIFICATION	sdac-classify-job-titles	safe	No
JUSTIFICATION_INITIAL	sdac-validate-justifications	comparison	No
SALARY	sdac-validate-salaries	linkage-dependent	Yes
FRINGE	sdac-validate-fringe	linkage-dependent	Yes
REPLACEMENT	sdac-validate-replacements	linkage-dependent	Yes
DUPLICATE	sdac-detect-duplicates	linkage-dependent	Yes
DUPLICATE_CONTEXT	sdac-analyze-duplicates-context	linkage-dependent	Yes
VACANT	sdac-detect-vacant-positions	linkage-dependent	Yes

Internal linkage rows are also persisted:

• LINKAGE_DETERMINISTIC
• LINKAGE_AI
• LINKAGE_CANONICAL

They are prerequisites for packet construction, but they are not normally shown to users as standalone analysis sections.

Upload Persistence

Mastra upload progress is persisted in SDAC.ingestion_UploadJobs.

This table is separate from analysis evidence because upload parsing and analysis can progress independently. A job starts as queued, moves through processing, then ends as success, duplicate, or error. Important fields include:

Column	Purpose
JobId	Public Mastra upload job ID returned to the widget.
ReportId	Parsed SDAC report ID once ingestion has written SDAC.data_CostReports.
WorkerJobId	Internal ingestion worker job ID, usually the same ID supplied by Mastra.
Status, Stage, Message	Durable progress state shown to the widget.
WorkerStatusUrl	Internal worker status endpoint that Mastra polls.
AnalysisTriggered, AnalysisRunId, AnalysisStatus	Whether Mastra has started sdac-report-analysis for the parsed report.
ResultJson, ErrorJson	Last mirrored worker payload or failure details.
LastHeartbeatAtUtc	Used to report stalled uploads only when no progress has been observed.

The ingestion worker still exposes /sdac/upload and /sdac/uploads/:jobId/status, but those are internal service-to-service endpoints. Mastra calls them with trigger_analysis=false; analysis is started by Mastra after parsing succeeds.

Analysis Persistence

All workflow evidence is persisted in SDAC.analysis_Results.

Each report has one row per AnalysisType, enforced by UQ_analysis_Results_ReportType on (ReportId, AnalysisType). The workflow writes:

• one row for each internal linkage branch
• one row for each registered analysis
• one final REPORT_ANALYSIS_PACKET row

The packet row uses:

AnalysisType = REPORT_ANALYSIS_PACKET
ToolId = sdac-report-analysis-workflow

Important columns:

Column	Purpose
Status	pending, running, completed, error, stale, or blocked.
VisibilityStatus	visible or disabled; conversational retrieval hides disabled rows.
AnalysisVersion	Version string for the algorithm/prompt set that produced the row.
InputHash	Stable hash of report, version, and dependencies used to detect freshness.
DependencyJson	JSON dependency metadata, such as canonical linkage hash.
BlockedReason	Human-readable reason when an analysis cannot run because linkage is blocking.
ResultJson	Full normalized tool or packet output.
Passed, ErrorCount, WarningCount, RecordsChecked	Denormalized summary fields for fast retrieval and dashboard display.

See SDAC Database Schema for the full column list.

Agent Retrieval Audit

Conversational retrieval is audited separately from workflow execution in SDAC.analysis_AgentRetrievalAudit.

The audit table records actual SDAC chat stream events. A row is written when the agent emits a tool-call event and is completed when the matching tool-result event arrives. This gives reviewers an observable trail of which persisted analysis tools the agent actually called, which analysis types came back, and which requested analysis types were missing.

Audited calls include:

Tool	Audit purpose
sdac-get-analysis-packet	Confirms broad review prompts loaded the persisted report packet and records packet coverage.
sdac-get-analysis-result	Confirms focused prompts loaded the requested persisted analysis type.
sdac-search-analysis-evidence	Confirms evidence search queries and records returned or missing analysis types.
sdac-start-report-analysis	Confirms the agent requested workflow start/status, but does not count as evidence retrieval.
Raw sdac-* analysis tools	Captures fallback validation calls when persisted evidence is unavailable, stale, still running, or explicitly refreshed.

Coverage statuses are intentionally compact:

Coverage status	Meaning
complete	The retrieval returned all requested visible analysis types, or a broad packet retrieval returned analysis sections.
partial	Some requested analysis types were returned and some were missing.
single	A focused single-analysis result or raw analysis tool completed.
none	The tool returned no analysis evidence.
not_applicable	The tool was workflow/status oriented rather than evidence retrieval.
unknown	The tool has started but has not completed yet, or the output shape could not be summarized.

Use this query when validating whether the Coordinator Release Agent saw the expected analysis for a conversation:

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;

For a broad review prompt, look for a completed sdac-get-analysis-packet row with CoverageStatus = 'complete' and ReturnedAnalysisJson listing the visible packet analysis types. For a focused prompt, look for sdac-get-analysis-result or sdac-search-analysis-evidence rows that include the requested analysis type. If the table only shows raw tool fallbacks, the agent did not use the persisted packet path for that turn.

This audit proves what analysis the agent retrieved. It does not prove that the final prose used every retrieved finding correctly. Final assistant messages still live in SDAC.fact_ConversationHistory, so response-quality review should compare fact_ConversationHistory against analysis_AgentRetrievalAudit and the persisted rows in analysis_Results or analysis_EvidenceFindings.

Feature flags

Analysis visibility is controlled by database feature flags in Core.FeatureFlags. The report analysis still runs and persists all rows when a flag is off; only conversational retrieval hides disabled sections.

All SDAC analysis visibility flags use:

FlagScope = sdac

Managed flag keys:

Flag key	Controls
sdac.analysis.visible.source_code	SOURCE_CODE
sdac.analysis.visible.function_code	FUNCTION_CODE
sdac.analysis.visible.comment_quality	COMMENT_QUALITY
sdac.analysis.visible.job_title_classification	JOB_TITLE_CLASSIFICATION
sdac.analysis.visible.salary	SALARY
sdac.analysis.visible.fringe	FRINGE
sdac.analysis.visible.replacement	REPLACEMENT
sdac.analysis.visible.duplicate	DUPLICATE
sdac.analysis.visible.duplicate_context	DUPLICATE_CONTEXT
sdac.analysis.visible.vacant	VACANT

These flags are declared in src/mastra/config/project-profile-settings.manifest.js and reconciled by the profile settings deployment scripts.

Agent retrieval

The Coordinator Release Agent has three report-analysis tools:

Tool	Use
sdac-get-analysis-packet	Retrieves the full persisted packet, optionally filtered to requested sections.
sdac-get-analysis-result	Retrieves one persisted analysis result, such as FRINGE or SALARY.
sdac-start-report-analysis	Starts or reuses the workflow for on-demand analysis.

For broad prompts such as "review this report", the agent should call sdac-get-analysis-packet first. For focused prompts such as "provide the fringe benefit analysis", the agent should call sdac-get-analysis-result first. Raw validation tools should only run when persisted evidence is unavailable, stale, still running, or explicitly refreshed.

Progress surfaces

The workflow records step progress through the report-analysis runtime and tracker. Current progress can be read from persisted packet/status state and from the upload status route:

GET /sdac/uploads/{jobId}/status
GET /sdac/report-analysis/{reportId}/status

Widget browser code polls the same data through /api/mastra/sdac/uploads/{jobId}/status and /api/mastra/sdac/report-analysis/{reportId}/status.

Chat also proactively starts report analysis when a report context is available and can answer from completed packet lines while the rest of the workflow continues.

Known gaps

• The workflow now schedules all registered analysis branches in one parallel node after canonical linkage. The next refinement is splitting hybrid analyses into explicit deterministic and AI sub-branches when the tool supports that separation.
• Live testing has covered a fresh uploaded workbook and a focused fringe agent prompt, but still needs a larger fixture corpus and prompt matrix.
• Feature flag hiding is covered by unit/tool tests, but should be live-tested by toggling a DB flag and querying the agent.
• AI-assisted linkage depends on the replacement-linker agent being available in the runtime context. If that agent is unavailable, linkage can complete without useful AI contribution.
• Some agent answers summarize warning counts without identifying the exact row or position until a follow-up. Row-level prompt tests should become part of acceptance.
• The status route should be verified in the deployed route table before UI progress polling depends on it.

Source files

• Workflow: packages/domain-sdac/src/workflows/report-analysis.workflow.ts
• Runtime/status helpers: packages/domain-sdac/src/workflows/report-analysis.runtime.ts
• Progress tracker: packages/domain-sdac/src/workflows/report-analysis.tracker.ts
• Registry: packages/domain-sdac/src/analysis/analysis-registry.ts
• Packet builder: packages/domain-sdac/src/analysis/analysis-packet.ts
• Upload API: packages/domain-sdac/src/api/sdac-upload.ts
• Upload job persistence: packages/domain-sdac/src/api/sdac-upload-jobs.ts
• Ingestion worker client: packages/domain-sdac/src/api/sdac-ingestion-worker.ts
• Persistence helpers: packages/domain-sdac/src/tools/analysis-store.ts
• Database table: db/sqlproj/Mastra.Platform.SDACDb/model/Tables/SDAC.analysis_Results.sql
• Upload job table: db/sqlproj/Mastra.Platform.SDACDb/model/Tables/SDAC.ingestion_UploadJobs.sql