Skip to content

[T-010a] SQLAlchemy 2 ORM models for every table in doc 11 §3 #59

Closed

Description

@CodewithJha

Task T-010a · Milestone M3 — Oracles + runner + store · Size M · Priority P0 · Epic #3

Implements FR-STO-001 · PR-08

Branch feat/t-010a-sqlalchemy-2-orm-models-for — one task = one branch = one PR (doc 32 §6)

Summary

Map all 13 tables of the initial schema (scenario_spaces, sut_profiles, experiments, campaigns, workers, runs, oracle_results, strategy_states, analyses, failure_modes, failure_mode_members, risk_maps, artifacts, events) as SQLAlchemy 2 typed declarative models — columns, CHECK constraints, FKs and indexes exactly as the DDL.

Context / motivation

PostgreSQL is the system of record and the job queue (ADR-0007). ORM models are an adapter detail: they must never leak into domain code (doc 34 §7) — repositories (T-011a/b) translate them into core.types models.

Scope

In scope

  • backend/src/roboprobe/store/orm.py (split into store/orm/ package by aggregate if > 400 lines) using Mapped[...]/mapped_column.
  • UUIDv7 primary keys generated in Python (uuid_utils.uuid7).
  • Soft enums as CHECK constraints (not PG enums), timestamptz everywhere, jsonb, double precision[] for x_unit, text[] for tags/warnings.
  • Partial index ix_runs_queue ... WHERE status = 'PENDING' and all other indexes of doc 11 §3.
  • *_schema_version columns.

Out of scope

  • Alembic migration (T-010b)
  • Repositories (T-011a/b)

Implementation guidance

  • Allowed status/verdict/kind values should reference the enums from core.types (single source of truth) when building the CHECK expressions.
  • Naming convention via MetaData(naming_convention=...) so constraint names are stable for migrations.
  • No business logic in ORM classes.
  • Standards: respect the layering contracts (doc 16 §2) and conventions in doc 34; no hard-coded tunables — thresholds/defaults come from typed settings (doc 27); no magic numbers (ruff PLR2004); public APIs typed (mypy strict) and documented.

Acceptance criteria

  • Base.metadata.create_all on Postgres 17 produces a schema equivalent to doc 11 §3 DDL (compare with pg_dump --schema-only of the DDL in a test).
  • All CHECK constraints reject invalid values (integration test inserts a bad status).
  • mypy --strict clean; ORM imported only from roboprobe.store (import-linter).
  • Tests listed below added and green in CI; coverage of new code meets the gate in doc 18 §3.
  • Docs, config reference and CHANGELOG Unreleased updated where behaviour, keys or interfaces changed.
  • Definition of Done (doc 32 §3) satisfied.

Tests required

  • tests/integration/store/test_orm_schema.py (schema equivalence, constraint tests).

Dependencies

Blocked by: #15 (T-002a), #25 (T-017)

Blocks: #60 (T-010b)

Parallelization notes

Files / paths owned by this task — avoid editing other paths; shared files (pyproject.toml entry points, cli/main.py, api/routers/__init__.py, router.tsx, docker/compose.yaml, ci.yml) are append-only:

  • backend/src/roboprobe/store/orm.py (or store/orm/**)
  • backend/tests/integration/store/test_orm_schema.py

Owns ORM definitions only. Can run in parallel with every M2 scenario/k2d task.

Definition of Done

Per doc 32 §3 and AGENTS.md §5: lint, format, mypy strict and import-linter green; tests added and passing; docs updated; PR template completed with Closes #<this issue>; Conventional Commit messages referencing T-010a (doc 16 §6); commits authored only by the repository owner identity (AGENTS.md §4); CI green; reviewer checklist (doc 32 §5.1) satisfied.

References

Design docs: doc 11 §1 · doc 11 §2 · doc 11 §3 · ADR-0007 · ADR-0009

Requirements: FR-STO-001 · PR-08 (traceability: doc 28)

External:

Metadata

Metadata

Assignees

No one assigned

    Labels

    area:storagePostgres schema, repositories, artifactsmilestone:M3M3 — Oracles + runner + storepriority:P0Critical path / blocks otherssize:M1-3 daystaskChild task of a milestone epictype:featureNew functionality

    Projects

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions