Diagramas generados¶
Todos se generan desde el código con scripts/docs/gen_docs.py. Los diagramas de casos de uso, actividad, secuencia, paquetes y arquitectura de docs/diagramas/ se mantienen a mano y no se regeneran.
Modelo entidad-relación de la base interna (SQLite)¶
%% Base INTERNA (SQLite): metadatos e historial. No incluye la base Oracle objetivo.
erDiagram
projects {
INTEGER id PK
VARCHAR name
VARCHAR description
DATETIME created_at
DATETIME updated_at
}
users {
INTEGER id PK
VARCHAR username
VARCHAR full_name
VARCHAR password_hash
VARCHAR role
BOOLEAN active
INTEGER failed_attempts
DATETIME locked_until
INTEGER session_version
DATETIME created_at
DATETIME last_login_at
}
connection_profiles {
INTEGER id PK
INTEGER project_id FK
VARCHAR name
VARCHAR engine
VARCHAR host
INTEGER port
VARCHAR service_name
VARCHAR username
VARCHAR environment_type
DATETIME created_at
DATETIME updated_at
}
test_cases {
INTEGER id PK
INTEGER project_id FK
VARCHAR name
VARCHAR description
TEXT sql_query
VARCHAR expected_result
VARCHAR validation_type
DATETIME created_at
DATETIME updated_at
}
test_suites {
INTEGER id PK
INTEGER project_id FK
VARCHAR name
VARCHAR description
DATETIME created_at
DATETIME updated_at
}
usage_events {
INTEGER id PK
VARCHAR event_type
INTEGER project_id FK
VARCHAR source
VARCHAR detail
DATETIME created_at
}
execution_history {
INTEGER id PK
INTEGER project_id FK
INTEGER test_case_id FK
INTEGER suite_id FK
INTEGER connection_profile_id FK
VARCHAR status
DATETIME executed_at
FLOAT duration_ms
VARCHAR statement_type
TEXT executed_sql
VARCHAR validation_type
VARCHAR expected_result
TEXT actual_result
INTEGER rowcount
BOOLEAN rollback_applied
TEXT rollback_error
TEXT error_message
VARCHAR trigger_source
VARCHAR run_id
VARCHAR executed_by
}
suite_test_case {
INTEGER suite_id PK,FK
INTEGER test_case_id PK,FK
}
projects ||--o{ connection_profiles : "project_id"
projects ||--o{ test_cases : "project_id"
projects ||--o{ test_suites : "project_id"
projects |o--o{ usage_events : "project_id"
projects ||--o{ execution_history : "project_id"
test_cases |o--o{ execution_history : "test_case_id"
test_suites |o--o{ execution_history : "suite_id"
connection_profiles |o--o{ execution_history : "connection_profile_id"
test_suites ||--o{ suite_test_case : "suite_id"
test_cases ||--o{ suite_test_case : "test_case_id"
Clases ORM (base interna)¶
%% Clases ORM de la base interna SQLite (app/models)
classDiagram
class Project {
+Integer id
+String name
+String description
+DateTime created_at
+DateTime updated_at
}
class ConnectionProfile {
+Integer id
+Integer project_id
+String name
+String engine
+String host
+Integer port
+String service_name
+String username
+String environment_type
+DateTime created_at
+DateTime updated_at
}
class TestCase {
+Integer id
+Integer project_id
+String name
+String description
+Text sql_query
+String expected_result
+String validation_type
+DateTime created_at
+DateTime updated_at
}
class TestSuite {
+Integer id
+Integer project_id
+String name
+String description
+DateTime created_at
+DateTime updated_at
}
class ExecutionHistory {
+Integer id
+Integer project_id
+Integer test_case_id
+Integer suite_id
+Integer connection_profile_id
+String status
+DateTime executed_at
+Float duration_ms
+String statement_type
+Text executed_sql
+String validation_type
+String expected_result
+Text actual_result
+Integer rowcount
+Boolean rollback_applied
+Text rollback_error
+Text error_message
+String trigger_source
+String run_id
+String executed_by
+case_name()
+suite_name()
+connection_name()
}
class UsageEvent {
+Integer id
+String event_type
+Integer project_id
+String source
+String detail
+DateTime created_at
}
Project --> TestCase : test_cases
Project --> TestSuite : test_suites
Project --> ConnectionProfile : connection_profiles
Project --> ExecutionHistory : execution_histories
ConnectionProfile --> Project : project
ConnectionProfile --> ExecutionHistory : execution_histories
TestCase --> Project : project
TestCase --> ExecutionHistory : execution_histories
TestSuite --> Project : project
TestSuite --> TestCase : test_cases
TestSuite --> ExecutionHistory : execution_histories
ExecutionHistory --> Project : project
ExecutionHistory --> TestCase : test_case
ExecutionHistory --> TestSuite : test_suite
ExecutionHistory --> ConnectionProfile : connection_profile
Clases del motor, la seguridad y los servicios¶
%% Clases del motor de ejecución, la seguridad y los servicios (app/engine, app/security, app/services, app/core)
classDiagram
class UsageError {
}
class TargetDatabaseExecutor {
+dsn
+user
+password
+environment_type
+max_rows
+timeout_ms
+connect()
+execute_query(query, params, confirm_staging_dml) Dict~str~
+disconnect()
}
class ValidationStrategy {
<<abstract>>
+evaluate(execution_data, expected_value) bool
}
class RowCountValidation {
+evaluate(execution_data, expected_value) bool
}
class ExistenceValidation {
+evaluate(execution_data, expected_value) bool
}
class ValidationContext {
+set_strategy(strategy)
+execute_validation(execution_data, expected_value) bool
}
class ScalarValueValidation {
+evaluate(execution_data, expected_value) bool
}
class LimiteDeIntentos {
+bloqueada(ip) bool
+fallo(ip)
+reiniciar(ip)
}
class AnalisisError {
}
class ExcelError {
}
class ReglaInvalida {
}
ValidationStrategy <|-- RowCountValidation
ValidationStrategy <|-- ExistenceValidation
ValidationStrategy <|-- ScalarValueValidation
Clases Pydantic de la API¶
%% Clases Pydantic de entrada y salida de la API (app/models/schemas.py)
classDiagram
class ValidationTypeEnum {
}
class EnvironmentTypeEnum {
}
class ProjectCreate {
+str name
+Optional~str~ description
+validate_name(v) str
}
class ProjectUpdate {
+Optional~str~ name
+Optional~str~ description
+validate_name(v) Optional~str~
}
class ProjectResponse {
+int id
+datetime created_at
+datetime updated_at
}
class ConnectionProfileCreate {
+int project_id
+str name
+str engine
+str host
+int port
+str service_name
+str username
+EnvironmentTypeEnum environment_type
+validate_non_empty(v) str
+validate_host(v) str
+validate_service(v) str
+validate_engine(v) str
}
class ConnectionProfileUpdate {
+Optional~str~ name
+Optional~str~ host
+Optional~int~ port
+Optional~str~ service_name
+Optional~str~ username
+Optional~EnvironmentTypeEnum~ environment_type
+validate_non_empty(v) Optional~str~
+validate_host(v) Optional~str~
+validate_service(v) Optional~str~
}
class ConnectionProfileResponse {
+int id
+int project_id
+str name
+str engine
+str host
+int port
+str service_name
+str username
+str environment_type
+datetime created_at
+datetime updated_at
}
class TestConnectionRequest {
+str password
}
class TestCaseCreate {
+Optional~int~ project_id
+str name
+Optional~str~ description
+str sql_query
+Optional~ValidationTypeEnum~ validation_type
+str expected_result
+validacion_automatica(data) Any
+validate_text(v) str
+validate_expected(v, info) str
}
class TestCaseUpdate {
+Optional~str~ name
+Optional~str~ description
+Optional~str~ sql_query
+Optional~ValidationTypeEnum~ validation_type
+Optional~str~ expected_result
+validacion_automatica(data) Any
+validate_text(v) Optional~str~
}
class TestCaseResponse {
+int id
+int project_id
}
class TestSuiteCreate {
+Optional~int~ project_id
+str name
+Optional~str~ description
+validate_name(v) str
}
class TestSuiteUpdate {
+Optional~str~ name
+Optional~str~ description
+validate_name(v) Optional~str~
}
class TestSuiteResponse {
+int id
+int project_id
+List~TestCaseResponse~ test_cases
}
class DBCredentials {
+str password
+int connection_profile_id
+Optional~EnvironmentTypeEnum~ environment_type
+bool confirm_staging_dml
}
class ExecutionRequest {
+str sql_query
}
class TestCaseExecutionRequest {
}
class SuiteExecutionRequest {
}
class ExecutionResponse {
+bool success
+str statement_type
+List~Any~ rows
+int rowcount
+List~Any~ data
+str message
+Optional~str~ error_message
+bool rollback_applied
+Optional~str~ rollback_error
}
class QuickExecutionRequest {
+int connection_profile_id
+str password
+str sql_query
+Optional~ValidationTypeEnum~ validation_type
+str expected_result
+Optional~str~ _motivo_auto
+aceptar_auto(data) Any
+validate_text(v) str
+validate_expected_for_type()
+motivo_validacion_automatica() Optional~str~
}
class QuickExecutionResponse {
+int history_id
+str status
+str environment_type
+str connection_name
+str sql_query
+str validation_type
+str expected_result
+str expected_text
+str obtained_text
+str reason
+bool validation_auto
+Optional~str~ validation_reason
+List~str~ columns
+Optional~str~ obtained_value
+List rows
+int rows_shown
+bool rows_truncated
+bool rows_hidden
+int rowcount
+float duration_ms
+Optional~str~ error_message
}
class HistoryResponse {
+int id
+int project_id
+Optional~int~ test_case_id
+Optional~int~ suite_id
+Optional~int~ connection_profile_id
+str status
+datetime executed_at
+float duration_ms
+Optional~str~ statement_type
+str executed_sql
+Optional~str~ validation_type
+Optional~str~ expected_result
+Optional~str~ actual_result
+int rowcount
+bool rollback_applied
+Optional~str~ rollback_error
+Optional~str~ error_message
+Optional~str~ trigger_source
+Optional~str~ run_id
+Optional~str~ executed_by
+Optional~str~ case_name
+Optional~str~ suite_name
+Optional~str~ connection_name
}
class SuiteCaseResult {
+int history_id
+int test_case_id
+str case_name
+str status
+Optional~str~ validation_type
+Optional~str~ expected_result
+str expected_text
+str obtained_text
+str reason
+float duration_ms
+Optional~str~ error_message
+Optional~str~ previous_status
+Optional~datetime~ previous_executed_at
+Optional~str~ change
+Optional~str~ change_text
}
class SuiteExecutionSummary {
+int suite_id
+str suite_name
+int total_tests
+int passed
+int failed
+int errors
+float total_duration_ms
+Optional~str~ run_id
+List~HistoryResponse~ details
+List~SuiteCaseResult~ case_results
+int new_failures
+int fixed
}
ProjectCreate <|-- ProjectResponse
TestCaseCreate <|-- TestCaseResponse
TestSuiteCreate <|-- TestSuiteResponse
DBCredentials <|-- ExecutionRequest
DBCredentials <|-- TestCaseExecutionRequest
DBCredentials <|-- SuiteExecutionRequest
Componentes y dependencias entre paquetes¶
%% Dependencias entre paquetes, leídas de los import de app/ y alembic/env.py (se omiten FastAPI y, salvo en app.core y alembic, SQLAlchemy por legibilidad).
flowchart LR
nav["Navegador: frontend/ (17 archivos JS)"]
subgraph APP["Aplicación Python (app/)"]
app_api["app.api"]
app_cli["app.cli"]
app_core["app.core"]
app_engine["app.engine"]
app_main["app.main"]
app_models["app.models"]
app_security["app.security"]
app_services["app.services"]
end
alembic["alembic/ (migraciones)"]
ext_openpyxl[("openpyxl (Excel)")]
ext_oracle[("Oracle objetivo (python-oracledb)")]
ext_sqlite[("SQLite interna (SQLAlchemy)")]
ext_sqlparse[("sqlparse")]
nav -->|"HTTP /api"| app_main
app_main -->|"sirve /ui"| nav
alembic --> app_core
alembic --> app_models
app_api --> app_core
app_api --> app_engine
app_api --> app_models
app_api --> app_security
app_api --> app_services
app_cli --> app_core
app_cli --> app_engine
app_cli --> app_models
app_cli --> app_security
app_cli --> app_services
app_engine --> app_security
app_main --> app_api
app_main --> app_core
app_main --> app_models
app_main --> app_security
app_models --> app_core
app_models --> app_security
app_models --> app_services
app_security --> app_core
app_security --> app_models
app_services --> app_core
app_services --> app_engine
app_services --> app_models
app_services --> app_security
alembic -.-> ext_sqlite
app_api -.-> ext_oracle
app_cli -.-> ext_oracle
app_core -.-> ext_sqlite
app_engine -.-> ext_oracle
app_engine -.-> ext_sqlparse
app_security -.-> ext_sqlparse
app_services -.-> ext_openpyxl
app_services -.-> ext_oracle
Despliegue (compose, Terraform y workflows)¶
%% Leído de infra/production/compose.yml, infra/terraform/*.tf y .github/workflows/*.yml (no verifica que exista en Azure).
flowchart TB
user(["Persona usuaria (navegador)"])
subgraph VM["Máquina virtual Linux (Docker Compose: infra/production/compose.yml)"]
api["api<br/>sql-qa-framework:latest"]
caddy["caddy<br/>caddy:2-alpine<br/>puertos 80:80, 443:443"]
oracle_db["oracle_db<br/>container-registry.oracle.com/database/free"]
end
vol_framework_data[("volumen framework_data")]
api --- vol_framework_data
vol_oracle_data[("volumen oracle_data")]
oracle_db --- vol_oracle_data
user -->|"HTTPS 443"| caddy
caddy -->|"proxy a api:8000"| api
api -->|"oracledb"| oracle_db
api -->|"SQLAlchemy"| vol_framework_data
subgraph TF["Terraform (infra/terraform/)"]
tf_budget_tf["budget.tf<br/>resource azurerm_consumption_budget_resource_group.monthly"]
tf_main_tf["main.tf<br/>data azurerm_resource_group.existing<br/>data azurerm_virtual_machine.existing"]
tf_storage_tf["storage.tf<br/>resource azurerm_storage_account.backups<br/>resource azurerm_storage_container.backups<br/>resource azurerm_storage_management_policy.backups"]
tf_vm_new_tf["vm_new.tf<br/>resource azurerm_resource_group.new<br/>resource azurerm_virtual_network.new<br/>resource azurerm_subnet.new<br/>resource azurerm_public_ip.new<br/>resource azurerm_network_security_group.new<br/>resource azurerm_subnet_network_security_group_association.new<br/>resource azurerm_network_interface.new<br/>resource azurerm_linux_virtual_machine.new"]
end
subgraph GH["GitHub Actions (.github/workflows/)"]
wf_backup_yml["backup.yml<br/>schedule,workflow_dispatch"]
wf_db_objects_yml["db-objects.yml<br/>pull_request,push,workflow_dispatch"]
wf_deploy_yml["deploy.yml<br/>workflow_call,workflow_dispatch"]
wf_docs_generate_yml["docs-generate.yml<br/>pull_request,push,workflow_dispatch"]
wf_docs_yml["docs.yml<br/>pull_request,push"]
wf_infra_yml["infra.yml<br/>pull_request,push,workflow_dispatch"]
wf_pages_yml["pages.yml<br/>pull_request,push,workflow_dispatch"]
wf_release_yml["release.yml<br/>workflow_dispatch"]
wf_sql_checks_yml["sql-checks.yml<br/>pull_request,workflow_call,workflow_dispatch"]
wf_tests_yml["tests.yml<br/>pull_request,push"]
end
TF -.->|"gestiona/consulta"| VM
GH -.->|"construye, prueba, despliega"| VM