HunterX v7 Target Intelligence Database — Architecture & Reference

Status: Ratified (Sprint 006.8) Version: 1.0.0 Owner: HunterX Architecture Council

1. Purpose / Scope

The Target Intelligence Database (TIDB) is the canonical relational store of HunterX v7: every target, asset, finding, piece of evidence, mission, report, user and audit record has exactly one source of truth here. This document describes the system-of-record layer built in Sprint 006.8:

1. Entities — 87 typed domain entities under src/hunterx/domain/entities/tidb/ plus 6 legacy v6 entities retrofitted with the TIDB envelope.
2. ORM models — a SQLAlchemy model per entity under src/hunterx/infrastructure/db/sql/tidb_models/, one table per model (93 tables total).
3. Persistence — a generic registry + row mapper that turns any entity into rows and back, implemented by a generic SQL CRUD repository and an in-memory twin.
4. Versioning & audit — an immutable history trail (audit log, field-level change history, version history, timeline) written by a SQLAlchemy listener.
5. Migrations — Alembic environment and an autogenerated baseline.
6. Validation — envelope and field-level validation services.

Hard constraints:

• No behavior change. This sprint changes no security tool, capability, AI provider or Mission behavior.
• Entities are pure. The TIDB entities are side-effect-free dataclasses; storage adapters do the persistence.
• Swap-able storage. The same repository port is implemented for SQL and for in-memory so the platform runs with zero external services.

Scope: src/hunterx/domain/entities/tidb/, src/hunterx/domain/ports/tidb_repositories.py, src/hunterx/domain/services/validation.py, src/hunterx/infrastructure/db/sql/tidb_models/, src/hunterx/infrastructure/db/sql/{registry,mapping,crud,memory,versioning}.py, and alembic/.

Out of scope: the Knowledge Graph, object store, cache, and queue; the mission/workflow/reporting pipelines that consume TIDB.

2. Design Goals

1. Every record versioned. Writes are immutable history: audit entries, field-level change history, version numbers and timeline events are written transactionally with the write that caused them.
2. One mapping, many stores. The row mapper derives from Python type hints, so adding an entity costs nothing beyond defining the entity and its ORM model.
3. ULID keys, UTC stamps. Identifiers are 26-char ULIDs; all timestamps are UTC ISO-8601 strings; confidence values are bounded floats.
4. Soft delete by default. Deletion sets deleted_at and bumps versioning counters; hard delete is explicit.

3. Entity Model

3.1 The Envelope

Every TIDB entity carries the USS envelope (docs/bible/08 - Unified Security Schema.md, docs/bible/09 - Database Design.md §3):

Envelope fields are kw_only=True in the dataclasses so entity-specific fields never collide with them.

3.2 Entity Groups

The 87 entities are grouped in 11 modules mirroring the ORM package:

4. ORM Models

src/hunterx/infrastructure/db/sql/tidb_models/ defines one SQLAlchemy model per entity (TidbX for entity X). All models inherit TidbModelMixin (tidb_models/_base.py), which maps the envelope onto the get_base() declarative base: String(26) ULID primary keys, JSON columns for JSONB compatibility, FKs between related tables, UniqueConstraint and composite Index objects where the domain requires them.

• 87 tidb_* tables from TIDB entities + 6 legacy hunterx_* tables = 93 tables registered on the shared metadata.
• Model docstrings state the entity they mirror; envelope_dict() renders a model back to its entity shape.
• The models and entities share names, so the registry in §5 can associate them without configuration.

5. Registry & Row Mapper

5.1 Registry (db/sql/registry.py)

ENTITY_TO_MODEL / MODEL_TO_ENTITY are derived from class names (EntityX ↔ XModel). Helpers: entity_class(), model_class(), all_entities() (returns all 87 entity classes).

5.2 Row Mapper (db/sql/mapping.py)

RowMapper(entity_cls) maps between an entity and its ORM model:

• new_row(entity) — build a model instance from an entity.
• apply(entity, row) — copy entity fields onto an existing row.
• to_entity(row) — build an entity from a row.
• to_row / apply_entity — the reverse directions.

Enums are coerced by value both ways (Enum(value)); type hints are resolved once per entity class and cached (__tidb_hints__).

6. Repository Port & Implementations

6.1 Port (domain/ports/tidb_repositories.py)

TidbRepository[E] is a Generic ABC with: get, get_or_raise, save, save_many, delete, soft_delete, count, list (paginated + ordered), list_by, and stream (batch iteration for large tables). TidbRepositoryFactory builds and caches repositories per entity.

6.2 SQL repository (db/sql/crud.py)

SqlCrudRepository(session_factory, entity_cls) implements the port:

• upsert on id (select-then-insert/update),
• soft_delete sets deleted_at and bumps version/revision,
• list/list_by accept only whitelisted sort columns (_SAFE_ORDER),
• stream iterates with yield_per batching,
• save/save_many run the envelope validator first and raise DomainValidationError on invalid entities.

SqlTidbRepositoryFactory builds repositories from session_factory and exposes available_entities.

6.3 In-memory repository (db/sql/memory.py)

InMemoryCrudRepository(entity_cls) is a dict-backed implementation with the same semantics (soft delete included), giving a zero-dependency mode for tests and single-node operation. Sorting uses a stable (value, id) tiebreaker so records created in the same millisecond sort deterministically. InMemoryTidbRepositoryFactory mirrors the SQL factory.

7. Versioning & Audit

db/sql/versioning.py installs a VersioningListener on the SQLAlchemy SessionFactory (install_versioning(session_factory)). On every before_flush it records, in the same transaction:

• AuditLogModel — one entry per create/update/soft-delete/hard-delete.
• ChangeHistoryModel — field-level diffs (skips id, created_at, updated_at, version, revision).
• VersionHistoryModel — a version snapshot per write.
• TimelineEventModel — a timeline event per write.

Audit models are themselves excluded from versioning to prevent recursion.

8. Migrations (Alembic)

The alembic/ directory contains the migration environment:

• alembic/env.py imports both legacy hunterx.models and v7 hunterx.tidb_models into get_base().metadata, applies compare_type=True, honors a HUNTERX_DB_URL override, and forces src/ onto the front of sys.path (the repo-root legacy hunterx/ would otherwise shadow the v7 package on Windows).
• alembic/versions/4302b30cb7c7_tidb_baseline.py is the autogenerated baseline: 93 create_table + 93 drop_table operations.

Workflow:

python -m alembic upgrade head    # apply
python -m alembic downgrade base  # roll back to empty schema
python -m alembic check           # assert models and DB are in sync
python -m alembic revision --autogenerate -m "<change>"  # new migration

9. Validation

domain/services/validation.py defines the validation layer:

• TidbValidator ABC and TidbValidationResult/ValidationIssue.
• EnvelopeTidbValidator — validates the shared envelope: ULID id, UTC ISO-8601 timestamps, positive version/revision/schema_version.
• EntityTidbValidator — composes the envelope checks with per-field type and enum checks against the entity’s dataclass field hints.

TidbValidationResult.raise_if_invalid() raises DomainValidationError with the collected issues; the SQL repository invokes the envelope validator on save/save_many.

10. Module Reference

11. Verification

Gates at sprint close:

• python -m pytest tests/unit tests/integration tests/component — 576 passed (526 baseline + 41 TIDB tests + 9 validation tests).
• python -m ruff check src/hunterx tests alembic — clean.
• python -m alembic upgrade head && python -m alembic downgrade base — applied and rolled back cleanly.
• python -m alembic check — “No new upgrade operations detected”.

TIDB tests live in tests/unit/test_tidb_registry_mapper.py, tests/unit/test_tidb_in_memory_repository.py, tests/unit/test_tidb_validation.py, tests/integration/test_tidb_sql_repository.py, tests/integration/test_tidb_versioning.py, tests/integration/test_tidb_alembic.py.

12. References

• docs/bible/08 - Unified Security Schema.md — USS envelope
• docs/bible/09 - Database Design.md — TIDB core tables, indexes, history, retention
• docs/bible/02 - Architecture.md §5.15 — TIDB within the platform
• docs/v7-foundation.md — v7 core foundation module reference