Repository Docs & Knowledge Base

Consolidated Spensara documentation repository (722 Markdown & architecture documents in ../repository)

Built Word Documents (.docx): Spensara Overview (.docx) Capability Assessment (.docx) Brink POS Ingestion Analysis (.docx) The Borg Ingestion Architecture (.docx)

Implementation Specs (157)

Detailed engineering and feature implementation specifications.

implementation-specs/specs/AnomalyDetection/TASK6.3.md 12,496 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

TASK 6.3: Test Against Real KDSDev with Sample Data

Summary

This task covers manual testing and validation of the anomaly detection system against the real KDSDev database. It includes test procedures, expected results, and troubleshooting guidance. All CLI commands use the new --name/--spec-id syntax with MonitorState management.

Dependencies

All previous tasks completed
Access to KDSDev database
benborla/mcp-server-mysql configured
AWS Bedrock credentials configured

Prerequisites Checklist

Before testing, verify:

[ ] KDSDev database is accessible
[ ] MCP server can connect to KDSDev (stdio transport configured)
[ ] AWS Bedrock credentials are configured
[ ] Application database (kdsaidb) is migrated
[ ] At least one store has order data in KDSDev

Test Procedures

Test 1: Basic Connectivity

Objective: Verify all components can connect

bash

# Step 1: Test database migration (auto-applies at AppHost startup)
# Just start the application and verify no migration errors

# Step 2: List monitors (should show empty or existing)
dotnet run --project backend/Kds.Ai.Cli -- anomaly list

Expected Result:

Application starts without migration errors
List command runs without connection errors
Output shows "No monitor specs found." or existing monitors

Test 2: Monitor Spec Creation

Objective: Create a monitor spec using the example prompt

bash

# Create a monitor spec with the built-in example prompt
dotnet run --project backend/Kds.Ai.Cli -- anomaly create \
  -n "Prep Time Monitor - Test Store" \
  --example

# Verify creation
dotnet run --project backend/Kds.Ai.Cli -- anomaly list

# Check it was created in Draft state
dotnet run --project backend/Kds.Ai.Cli -- anomaly list --state draft

Expected Result:

Monitor spec created successfully with unique ID
State shows as Draft
Spec appears in list output

Test 3: Activate Monitor and Dry Run

Objective: Activate the monitor and run analysis without persisting results

bash

# Activate the monitor
dotnet run --project backend/Kds.Ai.Cli -- anomaly set-state \
  --spec-id "YOUR_SPEC_ID" \
  --state active

# Run dry analysis with verbose output
dotnet run --project backend/Kds.Ai.Cli -- anomaly run \
  -n "Prep Time Monitor - Test Store" \
  --dry-run \
  --verbose

Expected Result:

State successfully changed to Active
Progress messages show each phase
Agent queries data via MCP mysql_query tool
Analysis completes with results
No database records created (dry run)

Sample Output:

Monitor: Prep Time Monitor - Test Store (abc-123-...)
State: Active
Analysis time: 2025-01-15 14:00:00 UTC

[DRY RUN MODE - No changes will be saved]
[....] LoadingSpec: Loading monitor specification...
[....] Initializing: Initializing monitor run...
[....] RunningAgent: Running anomaly detection agent...
[....] AnalyzingResults: Processing detection results...
[DONE] Completed: No anomalies detected

=== Results ===
Run ID: 00000000-0000-0000-0000-000000000000
Status: Completed
Duration: 4523ms
Anomalies Detected: 0
Tokens Used: 2847

Test 4: Full Run with Alert Emission

Objective: Run full analysis and verify alert creation

bash

# Run without dry-run
dotnet run --project backend/Kds.Ai.Cli -- anomaly run \
  -n "Prep Time Monitor - Test Store" \
  --verbose

Expected Result:

Run completes and is saved to database
If anomaly detected, alert event(s) created and listed
Alerts Emitted count shown (from run.AlertEvents collection)

Test 5: Historical Analysis

Objective: Analyze a specific historical time period

bash

# Analyze 2 hours ago
dotnet run --project backend/Kds.Ai.Cli -- anomaly run \
  -n "Prep Time Monitor - Test Store" \
  -t "-2h" \
  --verbose

# Analyze yesterday
dotnet run --project backend/Kds.Ai.Cli -- anomaly run \
  -n "Prep Time Monitor - Test Store" \
  -t "-1d" \
  --verbose

Expected Result:

Analysis uses the specified time window
Results reflect historical data
Agent's SQL queries reference appropriate date ranges

Test 6: Run History and Stats

Objective: Verify run history is recorded

bash

# Check history for the monitor
dotnet run --project backend/Kds.Ai.Cli -- anomaly history \
  --spec-id "YOUR_SPEC_ID"

# Check overall stats
dotnet run --project backend/Kds.Ai.Cli -- anomaly stats

Expected Result:

History shows all previous runs with status, duration, anomaly counts
Stats show aggregate information

Test 7: Batch Execution

Objective: Run all active monitors

bash

# Create a second monitor with example prompt
dotnet run --project backend/Kds.Ai.Cli -- anomaly create \
  -n "Prep Time Monitor - Second Store" \
  --example

# Activate it
dotnet run --project backend/Kds.Ai.Cli -- anomaly set-state \
  --spec-id "SECOND_SPEC_ID" \
  --state active

# Run all active monitors
dotnet run --project backend/Kds.Ai.Cli -- anomaly run-all

Expected Result:

All monitors in Active state execute
Monitors in Draft, Paused, or Archived states are skipped
Each run recorded separately
Summary shows all results

Test 8: State Management

Objective: Verify all state transitions work

bash

# List all monitors
dotnet run --project backend/Kds.Ai.Cli -- anomaly list --state all

# Pause a monitor
dotnet run --project backend/Kds.Ai.Cli -- anomaly set-state \
  --spec-id "YOUR_SPEC_ID" \
  --state paused

# Verify paused monitors don't run
dotnet run --project backend/Kds.Ai.Cli -- anomaly run-all
# Should skip the paused monitor

# Archive a monitor
dotnet run --project backend/Kds.Ai.Cli -- anomaly set-state \
  --spec-id "YOUR_SPEC_ID" \
  --state archived

# Verify archived state
dotnet run --project backend/Kds.Ai.Cli -- anomaly list --state archived

Expected Result:

State transitions work for all 4 states: Draft, Active, Paused, Archived
Only Active monitors execute during run-all
List filtering by state works correctly

Test 9: Custom Prompt File

Objective: Create a monitor from a custom prompt file

bash

# Create a prompt file
cat > /tmp/custom-monitor-prompt.txt << 'EOF'
You are a data quality monitor. Analyze the orders table for data anomalies.

## Database
Use the mysql_query tool to access the MySQL database. The key table is `orders`:
- `create_time` (int, unix timestamp)
- `store_guid` (varchar)
- `is_deleted` (tinyint)
- `done` (tinyint)

## Analysis
1. Check for unusual deletion rates in the last 24 hours
2. Check for stores with zero orders when they normally have activity
3. Call emit_conclusion with your findings
EOF

# Create the monitor
dotnet run --project backend/Kds.Ai.Cli -- anomaly create \
  -n "Data Quality Monitor" \
  -p /tmp/custom-monitor-prompt.txt

# Verify
dotnet run --project backend/Kds.Ai.Cli -- anomaly list

Expected Result:

Monitor created with the custom prompt content
Shows in list with Draft state
Demonstrates the system is truly generic (not hardcoded to prep times)

Test 10: Error Handling

Objective: Verify graceful error handling

bash

# Test with nonexistent monitor name
dotnet run --project backend/Kds.Ai.Cli -- anomaly run \
  -n "Nonexistent Monitor"

# Test with nonexistent spec ID
dotnet run --project backend/Kds.Ai.Cli -- anomaly run \
  --spec-id "00000000-0000-0000-0000-000000000000"

# Test with neither name nor spec-id
dotnet run --project backend/Kds.Ai.Cli -- anomaly run

Expected Result:

Nonexistent monitor: Clear error message with suggestion to use anomaly list or anomaly create
Missing arguments: Error message about required --name or --spec-id
No unhandled exceptions or stack traces in normal mode

Validation Queries

Run these against KDSDev to understand the data (useful for verifying agent behavior):

sql

-- Check data freshness
SELECT 
  store_guid,
  MAX(FROM_UNIXTIME(create_time)) as latest_order,
  COUNT(*) as orders_24h
FROM orders
WHERE create_time > UNIX_TIMESTAMP(NOW() - INTERVAL 24 HOUR)
GROUP BY store_guid
ORDER BY orders_24h DESC
LIMIT 10;

-- Check prep time distribution (example of what the agent might query)
SELECT 
  HOUR(FROM_UNIXTIME(create_time)) as hour,
  AVG(preparation_time) as avg_prep,
  STDDEV(preparation_time) as stddev_prep,
  COUNT(*) as orders
FROM orders
WHERE store_guid = 'YOUR_STORE_GUID'
  AND create_time > UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY)
  AND done = 1
  AND preparation_time IS NOT NULL
GROUP BY hour
ORDER BY hour;

Note: These queries are for manual validation only. The agent formulates its own SQL based on the MonitorSpec's system prompt. The queries above are examples of what the agent might run, not hardcoded queries in the system.

Troubleshooting

MCP Connection Failed

Error: Failed to connect to MCP server

Solutions:

Verify MCP server binary is accessible (benborla/mcp-server-mysql)
Check McpMySqlSettings configuration (connection string, server path)
Test MCP server independently: start it manually and send a test query
Check stdio transport: ensure the server process can start

No Data Available

Agent found no data or returned empty results

Solutions:

Check if the store referenced in the prompt has recent orders
Verify the agent's system prompt references correct table/column names
Run a manual query against KDSDev to confirm data exists
Check the agent's reasoning/tool calls in verbose output

LLM Timeout

Error: Execution timed out

Solutions:

Increase timeout in RunOptions (default may be too short for complex analysis)
Check AWS Bedrock connectivity and credentials
Review token usage (may need to simplify the system prompt)
Check if the agent is stuck in a tool-calling loop

SQL Errors from MCP

Agent received SQL error from mysql_query tool

Solutions:

Check KDSDev connection string in MCP server config
Verify table/column names in the system prompt match actual schema
Test the failing query directly in a MySQL client
The agent should handle SQL errors gracefully and report them in its analysis

Performance Benchmarks

Target metrics for a healthy run:

Metric	Target	Warning
Total Duration	< 30s	> 60s
Token Usage	< 5000	> 10000
Tool Calls	3-6	> 10
Query Time	< 5s each	> 10s

Sign-Off Checklist

After completing all tests:

[ ] Basic connectivity verified
[ ] Monitor spec creation works (with --example and --prompt-file)
[ ] State management works (Draft, Active, Paused, Archived)
[ ] Dry run completes successfully
[ ] Full run persists results
[ ] Alerts emitted when anomalies detected (via run.AlertEvents collection)
[ ] Historical analysis works (relative time: -2h, -1d)
[ ] Run history and stats accessible
[ ] Batch execution runs only Active monitors
[ ] Custom prompt file creates a non-prep-time monitor (proves generic design)
[ ] Error handling appropriate (clear messages, no stack traces)
[ ] Performance within targets
[ ] No critical bugs found

Changes from Old Design

All CLI commands use --name / --spec-id instead of --store
No --skip-preflight option (preflight checks were domain-specific, removed)
No --threshold or --lookback options (agent decides these from prompt)
anomaly create uses --example or --prompt-file instead of -s "STORE_GUID"
anomaly list filters by --state instead of -s "STORE_GUID"
anomaly set-state replaces anomaly toggle --enable/--disable
anomaly history filters by --spec-id instead of -s "STORE_GUID"
Batch execution via run-all calls ExecuteAllActiveAsync() instead of creating monitors per store
Added Test 9 (custom prompt file) to demonstrate the system is generic
Added Test 8 (state management) for the 4-state MonitorState enum
Validation queries are framed as examples of what the agent might run, not hardcoded system queries

Notes

First runs may be slower due to cold starts (MCP server startup, LLM model loading)
LLM responses may vary slightly between runs (non-deterministic)
The agent formulates its own SQL - the queries it runs may differ from the validation queries above
Document any store-specific issues discovered during testing
Consider creating a dedicated test store with known patterns for reproducible testing

Repository Docs & Knowledge Base

Categories

Implementation Specs (157)

TASK 6.3: Test Against Real KDSDev with Sample Data

Summary

Dependencies

Prerequisites Checklist

Test Procedures

Test 1: Basic Connectivity

Test 2: Monitor Spec Creation

Test 3: Activate Monitor and Dry Run

Test 4: Full Run with Alert Emission

Test 5: Historical Analysis

Test 6: Run History and Stats

Test 7: Batch Execution

Test 8: State Management

Test 9: Custom Prompt File

Test 10: Error Handling

Validation Queries

Troubleshooting

MCP Connection Failed

No Data Available

LLM Timeout

SQL Errors from MCP

Performance Benchmarks

Sign-Off Checklist

Changes from Old Design

Notes