Database Schema¶

Database testing dashboard with schema analysis and checks

Database testing dashboard with schema analysis and checks.

All database models in the Quorvex AI platform. Uses SQLModel (SQLAlchemy) with SQLite (development) or PostgreSQL (production).

Source files: orchestrator/api/models_db.py, orchestrator/api/models_auth.py, orchestrator/api/db.py

Model Summary¶

Model	Table	Group	Description
`Project`	`projects`	Multi-Tenancy	Top-level tenant with name, base URL, settings (JSON)
`ProjectMember`	`project_members`	Multi-Tenancy	User-project association with role
`User`	`users`	Authentication	User account with email, bcrypt password hash, lockout fields
`RefreshToken`	`refresh_tokens`	Authentication	JWT refresh tokens with rotation support
`TestRun`	`testrun`	Test Execution	Core test execution record with queue, stage, healing tracking
`SpecMetadata`	`specmetadata`	Test Execution	Spec tags, description, author
`AgentRun`	`agentrun`	Test Execution	Generic AI agent execution record
`ExecutionSettings`	`execution_settings`	Test Execution	Singleton row for execution configuration
`RegressionBatch`	`regression_batches`	Regression	Batch grouping of test runs with aggregated counts
`RecordingSession`	`recording_sessions`	Recording	Browser recording session metadata and generated output
`ExplorationSession`	`exploration_sessions`	Exploration	AI exploration run with discovery counts
`DiscoveredTransition`	`discovered_transitions`	Exploration	Individual state change during exploration
`DiscoveredFlow`	`discovered_flows`	Exploration	Multi-step user flow
`FlowStep`	`flow_steps`	Exploration	Individual step within a flow
`DiscoveredApiEndpoint`	`discovered_api_endpoints`	Exploration	API endpoint captured via network monitoring
`Requirement`	`requirements`	Requirements	Requirement with code, category, priority, acceptance criteria
`RequirementSource`	`requirement_sources`	Requirements	Links requirement to its source
`RtmEntry`	`rtm_entries`	RTM	Maps requirements to test specs
`RtmSnapshot`	`rtm_snapshots`	RTM	Point-in-time RTM coverage snapshot
`CoverageMetric`	`coverage_metrics`	Coverage	Coverage metrics per test run
`DiscoveredElement`	`discovered_elements`	Coverage	UI elements found during crawling
`TestPattern`	`test_patterns`	Coverage	Successful interaction patterns for reuse
`CoverageGap`	`coverage_gaps`	Coverage	Identified gaps with suggested tests
`ApplicationMap`	`application_map`	Coverage	Discovered application structure
`PrdGenerationResult`	`prd_generation_results`	PRD	PRD feature-to-spec generation progress
`RunArtifact`	`run_artifacts`	Storage	Artifact tracking across local and MinIO
`ArchiveJob`	`archive_jobs`	Storage	Archival operation audit log
`StorageStats`	`storage_stats`	Storage	Daily storage statistics
`TestrailCaseMapping`	`testrail_case_mappings`	TestRail	Local spec to TestRail case mapping
`TestrailRunMapping`	`testrail_run_mappings`	TestRail	Batch to TestRail run mapping
`JiraIssueMapping`	`jira_issue_mappings`	Jira	Test run to Jira issue mapping

Additional current model groups in models_db.py:

Group	Tables
Agent definitions	`agent_tool_definitions`, `agent_definitions`
Custom workflows	`workflow_definitions`, `workflow_definition_revisions`, `workflow_runs`, `workflow_run_steps`, `workflow_schedules`, `workflow_schedule_executions`, `workflow_events`, `workflow_notifications`, `workflow_step_types`
Browser exploration memory	`browser_page_states`, `browser_elements`, `browser_transitions`, `browser_frontier_items`, `browser_state_clusters`, `discovered_flow_reviews`, `discovered_issues`
Specialized testing	`load_test_runs`, `security_scan_runs`, `security_findings`, `db_connections`, `db_test_runs`, `db_test_checks`, `openapi_import_history`
LLM testing	`llm_providers`, `llm_test_runs`, `llm_test_results`, `llm_comparison_runs`, `llm_datasets`, `llm_dataset_cases`, `llm_dataset_versions`, `llm_schedules`, `llm_schedule_executions`, `llm_spec_versions`, `llm_prompt_iterations`
Scheduling	`cron_schedules`, `schedule_executions`
Autonomous missions	`autonomous_missions`, `autonomous_mission_runs`, `autonomous_agent_work_items`, `autonomous_agent_events`, `autonomous_findings`, `autonomous_test_proposals`, `autonomous_approvals`
CI and PR intelligence	`ci_pipeline_mappings`, `ci_workflow_change_requests`, `ci_audit_events`, `ci_test_subsets`, `ci_test_subset_items`, `pr_impact_analyses`, `pr_quality_gate_runs`, `pr_changed_files`, `pr_selected_tests`, `test_impact_maps`, `test_execution_history`, `repo_index_snapshots`, `repo_indexed_files`
Assistant and memory	`chat_conversations`, `chat_messages`, `chat_message_feedback`, `agent_memories`, `memory_injection_events`, `memory_feedback_events`, `memory_feedback_aggregates`, `memory_graph_nodes`, `memory_graph_edges`
AutoPilot	`autopilot_sessions`, `autopilot_phases`, `autopilot_questions`, `autopilot_spec_tasks`, `autopilot_test_tasks`

Multi-Tenancy Models¶

Project¶

Column	Type	Constraints	Description
`id`	string	PK	UUID
`name`	string	Unique	Project name
`base_url`	string	--	Default URL for the project
`description`	string	--	Project description
`settings`	JSON	--	Encrypted credentials, integration config
`created_at`	datetime	--	Creation timestamp
`last_active`	datetime	--	Last activity timestamp

ProjectMember¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`project_id`	string	FK (projects)	Project reference
`user_id`	string	FK (users)	User reference
`role`	string	--	`admin`, `editor`, `viewer`
`granted_by`	string	FK (users)	Who granted membership
`granted_at`	datetime	--	When membership was granted

Unique constraint: (project_id, user_id)

Authentication Models¶

User¶

Column	Type	Constraints	Description
`id`	string	PK	UUID
`email`	string	Unique, indexed	User email
`password_hash`	string	--	bcrypt hash
`full_name`	string	--	Display name
`is_active`	bool	--	Account active flag
`is_superuser`	bool	--	Full platform access
`email_verified`	bool	--	Email verification status
`failed_login_attempts`	int	--	Counter for lockout
`locked_until`	datetime	--	Lockout expiry

RefreshToken¶

Column	Type	Constraints	Description
`id`	string	PK	UUID
`user_id`	string	FK (users)	Owner
`token_hash`	string	--	Hashed token value
`device_info`	string	--	Client device info
`ip_address`	string	--	Client IP
`expires_at`	datetime	--	Token expiry
`revoked_at`	datetime	--	When revoked (null if active)
`replaced_by`	string	--	Next token in rotation chain

Test Execution Models¶

TestRun¶

Column	Type	Constraints	Description
`id`	string	PK	Run ID (format: `YYYY-MM-DD_HH-MM-SS`)
`spec_name`	string	--	Spec file path
`status`	string	--	`queued`, `running`, `passed`, `failed`, `error`, `stopped`
`test_name`	string	--	Display name
`steps_completed`	int	--	Completed step count
`total_steps`	int	--	Total step count
`browser`	string	--	Browser used
`queue_position`	int	--	Position in execution queue
`batch_id`	string	FK (regression_batches)	Batch reference (if part of batch)
`project_id`	string	FK (projects)	Project scope
`error_message`	string	--	Error details
`current_stage`	string	--	`planning`, `generating`, `testing`, `healing`
`healing_attempt`	int	--	Current healing attempt number

SpecMetadata¶

Column	Type	Constraints	Description
`spec_name`	string	PK	Spec file path
`tags_json`	string	--	JSON-encoded tags array
`description`	string	--	Spec description
`author`	string	--	Author name
`project_id`	string	FK (projects)	Project scope

AgentRun¶

Column	Type	Constraints	Description
`id`	string	PK	UUID
`agent_type`	string	--	`exploratory`, `writer`, `spec-synthesis`
`config_json`	string	--	JSON-encoded configuration
`result_json`	string	--	JSON-encoded results
`status`	string	--	Run status
`project_id`	string	FK (projects)	Project scope

ExecutionSettings¶

Column	Type	Constraints	Description
`id`	int	PK	Always 1 (singleton)
`parallelism`	int	--	Max concurrent test runs
`parallel_mode_enabled`	bool	--	Enable parallel execution
`headless_in_parallel`	bool	--	Headless mode for parallel runs
`memory_enabled`	bool	--	Memory system toggle

Regression Models¶

RegressionBatch¶

Column	Type	Constraints	Description
`id`	string	PK	Format: `batch_YYYY-MM-DD_HH-MM-SS`
`name`	string	--	Display name
`triggered_by`	string	--	Who/what triggered the batch
`browser`	string	--	Browser used
`hybrid_mode`	bool	--	Hybrid healing enabled
`total_tests`	int	--	Total test count
`passed`	int	--	Passed count
`failed`	int	--	Failed count
`stopped`	int	--	Stopped count
`status`	string	--	Batch status
`project_id`	string	FK (projects)	Project scope

Exploration Models¶

ExplorationSession¶

Column	Type	Constraints	Description
`id`	string	PK	UUID
`project_id`	string	FK (projects)	Project scope
`entry_url`	string	--	Starting URL
`status`	string	--	`pending`, `running`, `paused`, `completed`, `failed`
`strategy`	string	--	`goal_directed`, `breadth_first`, `depth_first`
`config`	JSON	--	Session configuration
`pages_discovered`	int	--	Pages found
`flows_discovered`	int	--	Flows found
`api_endpoints_discovered`	int	--	API endpoints found

DiscoveredTransition¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`session_id`	string	FK (exploration_sessions)	Session reference
`sequence_number`	int	--	Order within session
`before_url`	string	--	URL before action
`action_type`	string	--	Action performed
`action_target`	JSON	--	Target element details
`after_url`	string	--	URL after action
`transition_type`	string	--	`navigation`, `modal_open`, `modal_close`, `inline_update`, `error`, `no_change`
`api_calls`	JSON	--	API calls during transition

DiscoveredFlow¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`session_id`	string	FK (exploration_sessions)	Session reference
`project_id`	string	FK (projects)	Project scope
`flow_name`	string	--	Flow display name
`flow_category`	string	--	`authentication`, `crud`, `navigation`, `form_submission`, `search`, etc.
`start_url`	string	--	First URL in flow
`end_url`	string	--	Last URL in flow
`step_count`	int	--	Number of steps
`is_success_path`	bool	--	Whether this is a happy path

FlowStep¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`flow_id`	int	FK (discovered_flows)	Flow reference
`step_number`	int	--	Order within flow
`transition_id`	int	FK (discovered_transitions)	Transition reference
`action_type`	string	--	Action performed
`action_description`	string	--	Human-readable description

DiscoveredApiEndpoint¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`session_id`	string	FK (exploration_sessions)	Session reference
`project_id`	string	FK (projects)	Project scope
`method`	string	--	HTTP method
`url`	string	--	Endpoint URL
`response_status`	int	--	HTTP response status
`call_count`	int	--	Times observed

Requirements & RTM Models¶

Requirement¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`project_id`	string	FK (projects)	Project scope
`req_code`	string	--	Code (e.g., `REQ-001`)
`title`	string	--	Requirement title
`description`	string	--	Full description
`category`	string	--	Functional category
`priority`	string	--	`low`, `medium`, `high`, `critical`
`status`	string	--	`draft`, `approved`, `implemented`, `tested`
`truth_state`	string	--	Requirement truth/review state such as candidate, confirmed, or rejected
`source_type`	string	--	Origin such as manual, exploration, or PRD
`confidence`	float	--	Generation or extraction confidence
`uncertainty_reason`	string	--	Explanation when the requirement needs review
`confirmed_by` / `confirmed_at`	string / datetime	--	Confirmation audit fields
`rejected_by` / `rejected_at`	string / datetime	--	Rejection audit fields
`title_embedding_json`	JSON string	--	Cached embedding used for deduplication
`acceptance_criteria`	JSON	--	List of acceptance criteria
`source_session_id`	string	FK (exploration_sessions)	Source exploration

RequirementSource¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`requirement_id`	int	FK (requirements)	Requirement reference
`source_type`	string	--	`flow`, `element`, `api_endpoint`, `transition`
`source_id`	int	--	ID of source entity
`confidence`	float	--	Confidence score

RtmEntry¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`project_id`	string	FK (projects)	Project scope
`requirement_id`	int	FK (requirements)	Requirement reference
`test_spec_name`	string	--	Spec file path
`mapping_type`	string	--	`full`, `partial`, `suggested`
`confidence`	float	--	Mapping confidence score

RtmSnapshot¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`project_id`	string	FK (projects)	Project scope
`total_requirements`	int	--	Total requirements at snapshot time
`covered_requirements`	int	--	Covered requirements
`coverage_percentage`	float	--	Coverage percentage
`snapshot_data`	JSON	--	Full RTM data at snapshot time

Coverage & Memory Models¶

CoverageMetric¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`run_id`	string	--	Test run reference
`metric_type`	string	--	Coverage metric type
`covered`	int	--	Covered count
`total`	int	--	Total count
`percentage`	float	--	Coverage percentage

DiscoveredElement¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`url`	string	--	Page URL
`selector_type`	string	--	Selector strategy
`selector_value`	string	--	Selector value
`element_type`	string	--	Element type (button, input, etc.)
`test_count`	int	--	Times tested

TestPattern¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`pattern_hash`	string	--	Dedup hash
`action`	string	--	Action type
`selector_type`	string	--	Selector strategy
`success_count`	int	--	Successful uses

CoverageGap¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`gap_type`	string	--	Gap type (page, element, flow)
`severity`	string	--	`high`, `medium`, `low`
`description`	string	--	Gap description
`suggested_test`	string	--	Suggested test to fill gap

ApplicationMap¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`url`	string	Unique	Page URL
`page_title`	string	--	Page title
`linked_urls`	JSON	--	Outbound links
`elements`	JSON	--	Interactive elements
`forms`	JSON	--	Form elements
`api_endpoints`	JSON	--	API calls from page

PRD Models¶

PrdGenerationResult¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`prd_project`	string	--	PRD project identifier
`feature_name`	string	--	Feature being generated
`status`	string	--	Generation status
`spec_path`	string	--	Generated spec file path
`project_id`	string	FK (projects)	Project scope

Storage & Archival Models¶

RunArtifact¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`run_id`	string	--	Test run reference
`artifact_type`	string	--	`plan`, `trace`, `report`, `screenshot`, `validation`
`artifact_name`	string	--	File name
`storage_path`	string	--	File path or MinIO key
`storage_type`	string	--	`local` or `minio`
`archived_at`	datetime	--	When archived to MinIO
`expires_at`	datetime	--	Expiration date

ArchiveJob¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`job_type`	string	--	Job type
`status`	string	--	Job status
`artifacts_processed`	int	--	Count of processed artifacts
`bytes_freed`	int	--	Bytes freed by archival

StorageStats¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`postgres_size_mb`	float	--	PostgreSQL database size
`runs_dir_size_mb`	float	--	Runs directory size
`minio_backups_size_mb`	float	--	MinIO backups size
`minio_artifacts_size_mb`	float	--	MinIO artifacts size

TestRail Integration Models¶

TestrailCaseMapping¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`spec_name`	string	--	Local spec file path
`testrail_case_id`	int	--	TestRail case ID
`local_hash`	string	--	Content hash of local spec
`remote_hash`	string	--	Content hash of TestRail case
`project_id`	string	FK (projects)	Project scope

TestrailRunMapping¶

Column	Type	Constraints	Description
`id`	int	PK	Auto-increment
`batch_id`	string	--	Regression batch ID
`testrail_run_id`	int	--	TestRail run ID
`results_count`	int	--	Number of results synced
`project_id`	string	FK (projects)	Project scope

JSON Storage Pattern¶

Models store structured data as JSON strings with property accessors:

class Model(SQLModel, table=True):
    data_json: str = "{}"

    @property
    def data(self) -> dict:
        return json.loads(self.data_json)

    @data.setter
    def data(self, value: dict):
        self.data_json = json.dumps(value)

Models using Column(JSON) for native PostgreSQL JSONB: CoverageMetric.extra_data, DiscoveredElement.attributes, ApplicationMap.linked_urls/elements/forms/api_endpoints, Project.settings.

Database Initialization¶

init_db() creates all tables via SQLModel.metadata.create_all(engine)
Default project created if none exists
Default execution settings (singleton row) created
Database type detected from DATABASE_URL
is_parallel_mode_available() returns True only for PostgreSQL

Migrations¶

Alembic migrations for PostgreSQL. Config: alembic.ini. Scripts: orchestrator/migrations/versions/.

Command	Description
`make db-upgrade`	Apply pending migrations
`make db-downgrade`	Roll back one step
`make db-history`	View migration history
`make db-migrate M="description"`	Generate new migration
`make db-stamp R=001`	Stamp existing DB at a revision

Auto-upgrade on startup: init_db() runs alembic upgrade head for PostgreSQL.