Database Schema

Database Schema

All data is stored in a SQLite database (./data/app.db). Entity Framework Core manages the schema and migrations.

---

Agents

Stores each Copilot Studio agent with its complete, independent configuration.

Column	Type	Purpose
Id	GUID	Primary key
Name	string	Display name
Description	string	Free-text description
Environment	string	dev / staging / production
DirectLineBotId	string	Copilot Studio bot identifier
DirectLineSecret	string	Web Channel security secret (encrypted)
DirectLineUseWebSocket	bool	true = WebSocket, false = polling
DirectLineReplyTimeoutSeconds	int	Per-agent reply timeout
DirectLineMaxRetries	int	Retry policy
DirectLineBackoffSeconds	int	Backoff interval between retries
DirectLineUseWebChannelSecret	bool	Use Web Channel secret vs. token exchange
JudgeEndpoint	string	Agent judge LLM URL
JudgeApiKey	string	Agent judge API key (encrypted)
JudgeModel	string	Judge model deployment name
JudgeTemperature	double	Judge temperature
JudgeTopP	double	Judge top-p
JudgeMaxOutputTokens	int	Judge max output tokens
JudgePassThreshold	double	Minimum weighted score to pass (0–1)
QuestionGenEndpoint	string?	Per-agent question gen endpoint override (null = global)
QuestionGenApiKey	string?	Per-agent question gen API key (null = global, encrypted)
QuestionGenModel	string?	Per-agent question gen model override (null = global)
QuestionGenSystemPrompt	string?	Per-agent question gen system prompt override (null = global)
IsActive	bool	Soft delete
CreatedAt	DateTime	Audit
UpdatedAt	DateTime	Audit
CreatedBy	string	Creator identity
Tags	string[]	Categorization tags

---

TestSuiteAgents (Many-to-Many)

Junction table linking test suites to one or more agents.

Column	Type	Purpose
TestSuiteId	GUID	FK → TestSuite
AgentId	GUID	FK → Agent

---

TestSuites

Column	Type	Purpose
Id	GUID	Primary key
Name	string	Suite name
Description	string	Human-readable description
Tags	string[]	Categorization
CreatedBy	string	Creator (authenticated user identity)
CreatedAt	DateTime	Audit
UpdatedAt	DateTime	Audit
Version	int	Optimistic concurrency
IsActive	bool	Soft delete
DefaultTimeoutSeconds	int	Test case timeout
MaxRetries	int	Retry policy
JudgeSettingId	GUID?	Optional FK → JudgeSettings rubric (null = use agent default)

---

TestCases

Column	Type	Purpose
Id	GUID	Primary key
SuiteId	GUID	FK → TestSuite
Name	string	Test case name
UserInput	string[]	Multi-turn user prompts
ExpectedIntent	string	Ground truth intent
ExpectedEntities	string[]	Expected entity extractions
AcceptanceCriteria	string	Pass/fail rubric
ReferenceAnswer	string	Optional expected response text
Category	string	Test classification
Priority	int	Execution order
IsGenerated	bool	AI-generated vs manual
SourceDocument	GUID	Source document for generated cases

---

Runs

Column	Type	Purpose
Id	GUID	Primary key
SuiteId	GUID	FK → TestSuite
AgentId	GUID	FK → Agent (which agent was tested)
StartedAt	DateTime	Execution start
CompletedAt	DateTime?	Execution end (null if running)
Status	string	running / completed / failed
ExecutionUser	string	Who triggered the run
GitSha	string	Code version correlation
ModelVersion	string	Judge model identifier
PromptVersion	string	Prompt template version
ConfigSnapshot	JSON	Full config at run time
TotalTestCases	int	Count
PassedCount	int	Count
FailedCount	int	Count
SkippedCount	int	Count
AverageLatencyMs	double	Mean latency
MedianLatencyMs	double	Median latency
P95LatencyMs	double	95th percentile latency

---

Results

Column	Type	Purpose
Id	GUID	Primary key
RunId	GUID	FK → Run
TestCaseId	GUID	FK → TestCase
Verdict	string	pass / fail / error / skipped
TaskSuccessScore	double	0.0–1.0
IntentMatchScore	double	0.0–1.0
FactualityScore	double	0.0–1.0
HelpfulnessScore	double	0.0–1.0
SafetyScore	double	0.0–1.0
OverallScore	double	Weighted aggregate
LatencyMs	long	Response time in ms
TurnCount	int	Number of conversation turns
TokensUsed	int	Inference tokens consumed
JudgeRationale	string	Explanation from AI judge
JudgeCitations	string[]	Evidence references
ErrorMessage	string	Failure details
ExecutedAt	DateTime	When the test ran

---

TranscriptMessages

Full conversation transcript per result.

Column	Type	Purpose
Id	GUID	Primary key
ResultId	GUID	FK → Result
Role	string	user / bot / system
Content	string	Message text
Timestamp	DateTime	When sent
SequenceNumber	int	Turn order
RawActivityJson	JSON	Full Direct Line activity

---

Documents

Column	Type	Purpose
Id	GUID	Primary key
Name	string	Original filename
DocumentType	string	pdf / text
ContentHash	string	Deduplication hash
UploadedAt	DateTime	Upload timestamp
FileSizeBytes	long	File size
StoragePath	string	Path on disk

---

Chunks

Column	Type	Purpose
Id	GUID	Primary key
DocumentId	GUID	FK → Document
Text	string	Chunk content
TokenCount	int	Estimated tokens
ChunkIndex	int	Position in document
Category	string	Topic/section tag
Embedding	bytes	Optional vector (future)

---

JudgeSettings

Named evaluation rubric presets. Each rubric defines scoring dimension weights, an optional custom judge system prompt, and optional LLM overrides. Assigned to test suites via JudgeSettingId.

Column	Type	Purpose
Id	GUID	Primary key
Name	string	Rubric name (e.g., "Strict Factuality")
PromptTemplate	string	Custom judge system prompt (empty = built-in default)
TaskSuccessWeight	double	Weight (0–1)
IntentMatchWeight	double	Weight (0–1)
FactualityWeight	double	Weight (0–1)
HelpfulnessWeight	double	Weight (0–1)
SafetyWeight	double	Weight (0–1)
PassThreshold	double	Verdict threshold (0–1)
UseReferenceAnswer	bool	Include reference answer in judge prompt
Temperature	double	LLM temperature (used only when Endpoint is set)
TopP	double	LLM top-p (used only when Endpoint is set)
MaxOutputTokens	int	LLM max output tokens (used only when Endpoint is set)
Endpoint	string?	LLM endpoint override (null = inherit agent judge LLM)
ApiKey	string?	LLM API key override (null = inherit agent judge LLM)
Model	string?	Model deployment name override (null = inherit agent judge model)
IsDefault	bool	Mark as the default rubric
CreatedAt	DateTime	Audit
UpdatedAt	DateTime	Audit

---

AuditLog

Records every mutating operation performed across the application. Written automatically on all Create, Update, Delete, and Run events triggered via the REST API or the Blazor UI. When a request is authenticated with an API key, the key's friendly name is stored as UserId.

Column	Type	Purpose
Id	GUID	Primary key
Timestamp	DateTime	When the event occurred
Action	string	Create / Update / Delete / Run
EntityType	string	e.g., Agent, TestSuite, TestCase, Document, Run, ApiKey, Result
EntityId	GUID?	ID of the affected entity
UserId	string?	Authenticated user identity or API key name (e.g., "CI Pipeline Key")
Details	string?	Human-readable description of the operation
OldValue	string?	Previous value (for update events, where applicable)
NewValue	string?	New value (for update events, where applicable)

---

GlobalQuestionGenerationSetting

Stores the global default AI question generation configuration. Only one row is used (singleton). Agents may override any of these settings individually.

Column	Type	Purpose
Id	GUID	Primary key
Endpoint	string	Azure OpenAI endpoint URL
ApiKey	string	API key (encrypted)
Model	string	Deployment/model name
Temperature	double	Sampling temperature
TopP	double	Nucleus sampling top-p
MaxOutputTokens	int	Max tokens per response
SystemPrompt	string?	Custom system prompt override (null = built-in default)
UpdatedAt	DateTime	Last updated timestamp
UpdatedBy	string	Who last saved it

---

Key Relationships

Agent ──┐
        ├── TestSuiteAgents ──── TestSuite ──── TestCase
        │                              │
        │                              └── JudgeSettings (optional rubric override)
        │
Run ────┤ (FK AgentId + FK SuiteId)
        │
        └── Result ──── TranscriptMessage

Document ──── Chunk

GlobalQuestionGenerationSetting (singleton; per-agent columns on Agent override it)
JudgeSettings (per-suite rubric presets; agent judge LLM columns are the base)