Skip to content

database-schema.md

Latest commit

 

History

History
99 lines (80 loc) · 9.18 KB

database-schema.md

File metadata and controls

99 lines (80 loc) · 9.18 KB

Database Schema: Sentinel Data Hub

This document defines the database schema for the Sentinel platform. The recommended database is PostgreSQL with the pgvector extension for handling vector embeddings.

The schema is designed to capture the entire lifecycle of testing activities, from specification ingestion to result analysis.

Table Definitions

1. api_specifications

Stores the ingested API specifications.

Column Name Data Type Constraints Description
id SERIAL PRIMARY KEY Unique identifier for the specification.
project_id INTEGER FOREIGN KEY (optional) Links to a project or workspace.
raw_spec TEXT NOT NULL The original, unmodified specification content (JSON or YAML).
parsed_spec JSONB NOT NULL The specification after parsing and reference resolution.
internal_graph JSONB The graph-based internal data model (SODG).
source_url TEXT The URL from which the spec was fetched.
source_filename TEXT The original filename if uploaded.
llm_readiness_score FLOAT The score generated by the Spec-Linter-Agent.
version VARCHAR(255) Version of the API specification (e.g., from the info block).
created_at TIMESTAMPTZ DEFAULT NOW() Timestamp of creation.
updated_at TIMESTAMPTZ DEFAULT NOW() Timestamp of last update.

2. test_cases

A structured repository for all agent-generated tests.

Column Name Data Type Constraints Description
id SERIAL PRIMARY KEY Unique identifier for the test case.
spec_id INTEGER FOREIGN KEY Links to the api_specifications table.
agent_type VARCHAR(255) NOT NULL The type of agent that generated the test (e.g., Functional-Negative-Agent).
description TEXT A natural language description of the test's purpose.
test_definition JSONB NOT NULL The full test details: endpoint, method, headers, payload, assertions.
tags JSONB Tags for categorization (e.g., smoke, regression, p1).
created_at TIMESTAMPTZ DEFAULT NOW() Timestamp of creation.

3. test_suites

Provides a mechanism to group related test_cases.

Column Name Data Type Constraints Description
id SERIAL PRIMARY KEY Unique identifier for the test suite.
name VARCHAR(255) NOT NULL The name of the test suite (e.g., "User API Regression").
description TEXT A description of the suite's purpose.
created_at TIMESTAMPTZ DEFAULT NOW() Timestamp of creation.

4. test_suite_entries

A mapping table to link test_cases to test_suites.

Column Name Data Type Constraints Description
suite_id INTEGER PRIMARY KEY, FOREIGN KEY Links to the test_suites table.
case_id INTEGER PRIMARY KEY, FOREIGN KEY Links to the test_cases table.
execution_order INTEGER DEFAULT 0 The order in which the test should be run within the suite.

5. test_runs

Records every execution of a test_suite.

Column Name Data Type Constraints Description
id SERIAL PRIMARY KEY Unique identifier for the test run.
suite_id INTEGER FOREIGN KEY Links to the test_suites table.
status VARCHAR(50) NOT NULL The overall status (e.g., pending, running, completed, failed).
target_environment TEXT The base URL of the environment under test.
started_at TIMESTAMPTZ Timestamp when the run started.
completed_at TIMESTAMPTZ Timestamp when the run completed.

6. test_results

Contains the detailed, granular results for each test case within a run. This table should be optimized for time-series analysis.

Column Name Data Type Constraints Description
id BIGSERIAL PRIMARY KEY Unique identifier for the result.
run_id INTEGER FOREIGN KEY Links to the test_runs table.
case_id INTEGER FOREIGN KEY Links to the test_cases table.
status VARCHAR(50) NOT NULL The pass/fail status of the individual test.
response_code INTEGER The HTTP status code received.
response_headers JSONB The response headers.
response_body TEXT The response body.
latency_ms INTEGER The time taken for the request in milliseconds.
assertion_failures JSONB A structured log of which assertions failed and why.
executed_at TIMESTAMPTZ DEFAULT NOW() Timestamp of execution.

Entity-Relationship Diagram (ERD) - Conceptual

erDiagram
    API_SPECIFICATIONS ||--o{ TEST_CASES : "generates"
    TEST_CASES }o--o{ TEST_SUITE_ENTRIES : "is part of"
    TEST_SUITES ||--o{ TEST_SUITE_ENTRIES : "contains"
    TEST_SUITES ||--o{ TEST_RUNS : "is executed in"
    TEST_RUNS ||--o{ TEST_RESULTS : "produces"
    TEST_CASES ||--o{ TEST_RESULTS : "has"
Loading