HunterX v7 — Sprint 034.3

FINAL RELEASE GATE — Phase 3: TIDB, Persistence & Data Isolation Certification

Date: 2026-08-10 Phase: 034.3 (persistence certification — no feature development) Author: Principal Data Architect & Persistence Engineer (Phase 034.3) Scope: TIDB schema inventory & completeness, foreign-key integrity, target & mission isolation, evidence provenance, finding/proof round-trips, history, N+1/index/duplicate-control audits, migration validation, repository contracts, transaction integrity, error recovery, audit trail, secrets, graph consistency, backup durability, data volume, required tests, repairs, release-gate verdict.

0. Executive Summary

HunterX v7’s persistence model is reliable, complete, isolated and reproducible — and the TIDB is a genuine system of record that answers every operational question the release gate poses (what target, what assets, what tools, what observations, what hypotheses, what evidence, what findings, what proofs, what impact, what attack paths, what happened first, what changed, why each decision, which tool produced each observation, which mission produced each result).

Phase gate: PASS for TIDB / persistence / data-isolation certification. Three release-relevant persistence defects found during the audit were repaired in this phase (migration/model drift, soft-delete visibility leak in SQL list/list_by, and runtime audit-trail wiring). The certification suite adds 176 new tests (59 + 87 + 16 + 14) covering every section of the sprint, all passing. The overall V7 release gate remains BLOCKED only by the 034.1/034.2 carry-overs (P0-01 untracked V7 tree; P1-01 repo-root V6 shadowing), which are release-integrity issues outside the persistence scope.

Headline results

1. Schema Inventory

1.1 Migration chain (linear, 21 revisions, head a3f5b7c9d1e3)

Total after upgrade head: 401 tidb_* tables + 6 legacy hunterx_* tables = 407 data tables + alembic_version. Every TIDB table carries the 10-column USS envelope (id ULID PK, created_at, updated_at, first_seen, last_seen, version, revision, schema_version, deleted_at, meta), verified for all 401 tables by tests/integration/tidb/test_schema_audit.py.

1.2 Repository roles

• Generic TIDB port (domain/ports/tidb_repositories.py): TidbRepository[E] (get, get_or_raise, save, save_many, delete, soft_delete, count, list, list_by, stream) + TidbRepositoryFactory. Implemented by SqlCrudRepository / SqlTidbRepositoryFactory (infrastructure/db/sql/crud.py) and InMemoryCrudRepository / InMemoryTidbRepositoryFactory (infrastructure/db/sql/memory.py). Entity→ORM-model resolution is automatic via the class-name registry (db/sql/registry.py, 100+ entities) and the row mapper (db/sql/mapping.py).
• Legacy entity ports (domain/ports/repositories.py): mission / finding / target / scan / asset / report — implemented for SQL (Sql*Repository) and memory (InMemory*Repository).
• Other in-memory ports: mission planning, tool factory, orchestration, object store, knowledge graph (in-memory only by design — no SQL adapter).

2. Schema Coverage & Completeness

All sprint-listed entity classes are covered (mapped to actual TIDB entities):

Missing / partial: no dedicated TargetScopeRecord entity (scope is a field

• ScopePolicy); ToolExecution (baseline tidb_tool_executions) carries no mission_id/target_id scoping columns — tool executions are associated with missions via the Execution entity. Classified P2 (documented; not data-losing). No speculative tables were added.

3. Foreign Key Integrity

Verified by tests/integration/tidb/test_schema_audit.py::test_key_foreign_keys_exist using the SQLAlchemy inspector against the created schema:

subdomains.domain_id→domains.id, dns_records.domain_id→domains.id, ip_addresses.hostname_id→hostnames.id, ip_addresses.cidr_id→cidrs.id, cidrs.asn_id→asns.id, ports.ip_address_id→ip_addresses.id, services.port_id→ports.id, technologies.service_id→services.id, endpoints.url_id→urls.id, parameters.endpoint_id→endpoints.id, credentials/api_keys/tokens.secret_id→secrets.id, rest_endpoints/authentication_schemes.api_id→apis.id.

Orphan prevention: SQLite does not enforce FKs by default (PRAGMA foreign_keys off), so referential integrity is by construction (entities carry scoping keys; services resolve via list_by and tolerate orphans). The tests verify that orphan queries never cross scope (§4) and that entity chains (observation→evidence→finding→proof) round-trip without dangling references (test_evidence_provenance.py). No release-relevant orphan risk was found for the generic repository path.

4. Target Isolation (release-blocking requirement)

tests/integration/persistence/test_target_isolation.py (SQL + memory) and tests/security/data_isolation/test_adversarial_isolation.py:

• TARGET-A and TARGET-B each receive targets, 3 assets, 5 observations, 2 evidence records, a finding, a proof and a tool execution.
• Repository level: list_by("target_id", …)/list_by("mission_id", …) return only that target’s records; id sets are disjoint across A/B.
• Service level: TargetIntelligenceQueryService.get_target/assets/observations are scoped.
• API level (tests/security/data_isolation/test_api_isolation.py via create_app(platform=...) + TestClient): GET /findings?mission_id=… returns only that mission’s findings; finding-by-id is exact.
• Adversarial: direct-id reads resolve only the owning record; cross-scope queries return empty; soft-deleted records are invisible to every scope; a finding can never be reassigned across missions.

Verdict: PASS. No cross-target intelligence leakage through repositories, application services, the API or graph queries.

5. Mission Isolation

tests/integration/persistence/test_mission_isolation.py (SQL + memory): MISSION-A (httpx, injection hypothesis, finding FA1) and MISSION-B (nuclei, ssrf hypothesis, finding FB1) run against the same target. Observations, hypotheses, decisions, timeline entries, findings and lifecycle state (start/pause/resume) remain correctly associated; MissionOrchestrationQueryService returns only each mission’s records, and the finding contexts are disjoint.

Verdict: PASS. No cross-mission contamination.

6. Evidence Provenance

tests/integration/persistence/test_evidence_provenance.py: a full-provenance evidence object (evidence_id, target_id, mission_id, asset_key, what/where/ when/how, source, why_trust, reproducibility, tool, tool_version, command_configuration, raw_artifact_ref, parser/normalizer versions, confidence, meta) survives SQL and memory round-trips byte-for-byte. The observation → evidence (raw artifact) → finding (evidence_refs) → proof (evidence_ids) → execution chain stays intact after reload, and nmap/naabu/masscan observations remain separately attributable (which_tool_produced_which_observation).

Verdict: PASS. Provenance survives persistence/retrieval.

7. Finding Persistence

tests/integration/persistence/test_finding_roundtrip.py: a complete validated finding (candidate → evidence → verification → proof → impact → report_ready, with affected assets/endpoints/parameters, observations, evidence/validation/proof/impact/reproduction refs, scope, provenance, analysis_version) round-trips through both backends with no field loss; lifecycle status and envelope counters survive.

Verdict: PASS.

8. Proof / PoC Persistence

tests/integration/persistence/test_proof_roundtrip.py: a complete VulnerabilityProof (target, finding, hypothesis, validation, mission, tool context via provenance, preconditions, steps, inputs, expected/observed behavior, evidence_ids, impact_evidence_ids, replay counts, confidence, validated_at, proof_version, created_by) and a sanitized ProofOfConcept (format, evidence, replay/safety policy, version, status) round-trip through both backends and stay linked to the correct finding via proof_id/finding_id and the finding’s proof_refs.

Verdict: PASS.

9. History / Timeline

tests/integration/persistence/test_history.py: first/last-seen tracking (preserves first_seen, advances last_seen), append-only repeated observations (4 runs preserved with distinct stamps), chronological discovery history and state-change history (verified via created_at ascending order), append-only finding lifecycle (version/revision bump via touch()), proof→evidence history links, and descending/ascending timeline ordering.

Verdict: PASS. Historical evidence is never overwritten incorrectly.

10. Query Analysis (N+1 audit)

tests/performance/persistence/test_n_plus_one.py instruments SQLAlchemy cursor execution:

• list(500), list_by(500), get, count, stream(2000), save_many(50) → 1 query each (stream ≤ 2; batched via yield_per). No N+1 in the repository port.
• save_many issues one per-row existence SELECT before the batched INSERT — a documented N+1 read pattern on bulk inserts (P2; acceptable at current volume, batch-worthy at scale).
• Application-level correlation (finding → its evidence refs) is deliberately query-per-finding (P2, no nested amplification).

Known service-level pattern (P2): TargetIntelligenceQueryService.assets/observations/gaps/conflicts stream the whole table then filter in Python when stores is set (e.g. list(...repository_for(IntelligenceAssetRecord).stream())), instead of using list_by(target_id, …). This is safe and correct but becomes a hot path at scale; the fix is to route through list_by (indexed). Not optimized blindly per the sprint mandate.

11. Index Audit

Verified by test_schema_audit.py::test_required_indexes_exist: intelligence_targets.{target_id,mission_id}, intelligence_assets.target_id, intelligence_observations.{target_id,mission_id}, intelligence_evidence.target_id, finding_records.{finding_id,mission_id, target_id,severity,status}, vulnerability_proofs.{proof_id,finding_id, target_id}, tool_executions.execution_id, mission_orchestrations.{mission_id,tenant} and the composite scoping index ix_tidb_intel_target_scope (tenant, mission_id, target_id) are all present. created_at/first_seen/last_seen are on the envelope; list ordering uses created_at (indexed via the envelope on all tables — SQLite auto-indexes nothing extra, but ordering is bounded by page size). High-frequency query columns are indexed. No missing index was found that requires a schema change for the release-relevant query set.

12. Duplicate Control

tests/integration/persistence/test_duplicate_control.py:

• DB-enforced canonical keys: URL.url, Domain.name, IPAddress.address, (subdomain.domain_id, name), (endpoint.url_id, method, path), (parameter.endpoint_id, name, location) are unique — verified by insert- rejection tests.
• Primary-key identity: the finding service sets id=finding_id, so a duplicate finding_id is an upsert, never a second row.
• Multi-tool attribution: the same port from Nmap/Naabu/Masscan persists as three distinct, attributable observations that all resolve to the same canonical normalized_value + dedup_key (canonical target intelligence).
• Application-layer dedup (documented, P2): business ids on targets, assets, observations, evidence and proofs (target_id, asset_key, dedup_key, proof_id) rely on application semantics, not DB constraints. Service lookups use list_by(..., limit=1) and canonical asset_key/dedup_key overwrite semantics.

Verdict: PASS for the documented posture; DB-level uniqueness for business ids on target/asset/proof is P2 tech debt (worth a migration if duplicate rows are ever observed).

13. Soft Delete / Retention

• All 401 TIDB tables carry deleted_at; get/count/list/list_by/stream hide soft-deleted rows; include_deleted=True restores them (tested across both backends, incl. the repaired SQL list/list_by).
• Lifecycle states exist on the relevant entities (status, state, supersedes, resolved, refuted-style enums); active/inactive/archived/ expired retention policies are not implemented — none invented (per mandate).
• Dangerous irreversible deletion: repo.delete(id) hard-deletes. The versioning listener records a delete audit entry with a before snapshot before hard delete, so hard deletes remain reconstructable via audit. No cascade/retention purge exists that could silently destroy data.

14. Migration Validation

tests/integration/tidb/test_sql_migrations.py on a fresh file-backed SQLite:

1. alembic upgrade head → 408 tables (401 tidb + 6 legacy + version).
2. Schema is insertable/retrievable (raw insert + read on tidb_intelligence_targets).
3. alembic downgrade base → only alembic_version remains.
4. alembic upgrade head again → full schema + alembic check → “No new upgrade operations detected.”
5. Chain is linear: single head, 21 revisions.
6. All migrations are pure create_table/create_index — no data-mutating migration exists, so an upgrade can never damage existing data.

Repair applied this phase (P1): the migrations had drifted from the ratified ORM models — alembic check reported 312 operations of drift: 184 modify_nullable (proof/proof_strategy/finding-state envelope columns created NOT NULL while the model declares them nullable), 80 modify_type (js-intelligence String()→Text()), 24 index renames (mission tables). Fixed at the source in c51bafedb05e, d4a5b6c7e8f0, e1f2a3b4c5d6, a3f5b7c9d1e3. Safe because the V7 tree is unreleased/uncommitted (P0-01) and the runtime creates schema via create_all() (models) — existing DBs already match the models.

15. Migration Data Safety

Because every migration is a pure create_table/create_index operation (verified for all 21 files), there are no ALTER/DROP-column migrations that could harm existing rows, relationships, indexes or constraints. Data-insertion tests confirm the upgraded schema is fully usable; the upgrade→downgrade→ upgrade cycle reproduces the identical schema. No fixture-based data-loss risk exists in the migration path.

16. Memory vs SQL

tests/integration/persistence/test_memory_vs_sql.py: build_platform(Settings()) (default URL) resolves the in-memory TIDB factory and no session_factory; build_platform(Settings(database=url= "sqlite:///:memory:")) resolves the SQL factory. Both expose the same repository roles and run an identical mission flow and target-intelligence flow with identical behavior. Application services depend only on the TidbRepositoryFactory port — no application code references SQL repository classes.

Documented semantic divergences (P2, test_repository_contracts.py):

1. SQL validates the TIDB envelope on save (rejects a non-ULID id); in-memory does not validate.
2. SQL list_by/list raise ValueError for unknown columns; in-memory returns []/sorts silently.

17. Repository Contract Testing

tests/integration/tidb/test_repository_contracts.py runs the same suite against InMemoryTidbRepositoryFactory and SqlTidbRepositoryFactory (parametrized): save/get round-trip, upsert (no duplicate), absent→None, get_or_raise→NotFoundError, soft-delete hide/restore, soft-deleted rows hidden from list/list_by, hard delete, save_many+count, pagination/ ordering, list_by filtering, stream batching, JSON field round-trips, and the two documented divergences. Equivalent semantic behavior confirmed on the documented port contract.

18. Concurrency

tests/integration/tidb/test_concurrency.py (file-backed SQLite, WAL + busy_timeout, threads): concurrent writes to 4 different targets (40 rows, no loss), concurrent writes to the same target (40 distinct rows, no loss), concurrent upserts of the same finding id (exactly 1 row, no duplicates), concurrent writes to different findings (10 rows), and concurrent save_many batches on the same mission (20 rows, all scoped correctly).

Finding (P2): the SQL save does an unguarded select-then-insert; two concurrent first-time saves of the same new id race and one can raise IntegrityError instead of a clean upsert. No corruption or duplication results; this is an idempotency/retry gap (a SELECT … FOR UPDATE-style or catch-and-retry upsert would close it).

19. Transaction Integrity

tests/integration/tidb/test_transaction_integrity.py:

• save_many is atomic: one invalid entity → DomainValidationError and zero rows written; a unique-constraint violation mid-batch → full rollback (zero rows).
• A raw DB failure mid-write leaves no half-written row (NOT-NULL violation rolls back the whole insert).
• The finding→evidence→proof flow is per-row atomic: a mid-flow failure leaves prior rows intact, no corrupt proof row, and the DB remains consistent and queryable.
• Mission state survives failed writes; observation batches are atomic.

Verdict: PASS. No invalid partial state where atomicity is required.

20. Database Error Recovery

tests/integration/tidb/test_persistence_failure_recovery.py:

• Connection failure (dead URL) → OperationalError classified as a connection-class error; the healthy repository still works afterwards.
• Constraint violation (duplicate URL) → IntegrityError, rolled back, no duplicate.
• Validation errors → DomainValidationError; absent records → NotFoundError.
• Mission state and evidence rows survive unrelated failed writes (no corruption).

Verdict: PASS.

21. Audit Trail

tests/integration/persistence/test_audit_trail.py: with install_versioning(...), creates write AuditLog (action, actor, before/ after), VersionHistory, ChangeHistory (field-level old/new), and TimelineEvent rows. Finding created→validated and proof creation are reconstructable in chronological order; soft delete is audited with before/ after; the mission lifecycle (create→start→pause→resume→finalize) produces create + update audit entries.

Repair applied this phase (P1): the versioning listener was not installed at runtime — build_platform produced an SQL session factory with no audit recording. assembler._build_repositories now calls install_versioning(session_factory, source="hunterx") when SQL is active, and test_platform_build_wires_versioning verifies a production-style platform records the audit trail. (In-memory mode has no audit by design.)

22. Secret / Credential Storage

tests/integration/persistence/test_secrets_storage.py:

• Secret stores only value_masked + checksum (masked via hunterx.shared.masking); plaintext never round-trips.
• APIKey/Token store only key_hash/token_hash; Credential references a secret_id with no password column.
• JSIntelligenceSecret stores masked_value + value_hash.
• Evidence command_configuration uses masked values; a secret-like string is never persisted verbatim in documented secret fields.

No plaintext secret storage path was found in the database, logs, events, tool records, artifacts, reports or cache (the 034.1 credential scan also found no secret material in the repo).

23. Graph / TIDB Consistency

tests/integration/persistence/test_graph_consistency.py documents the actual architecture:

• The runtime knowledge graph (InMemoryKnowledgeGraph) is a derived/cached layer, NOT persisted in the TIDB. It is ephemeral per process and served through the KnowledgeGraphPort. A graph write/delete has no effect on TIDB rows and vice-versa.
• Derived graph facts ARE persisted relationally: TopologyRelationship stores typed edges with provenance (sources, evidence, confidence, mission, execution, correlation, in_scope); AdaptiveAttackPathRecord persists attack-path graphs as structured node/edge step lists.
• Classification: TIDB = authoritative system of record; graph = derived, in-memory, cache-like. Synchronization is one-directional (domain writes → both TIDB projection and graph projection as the application requires); there is no two-way sync contract to violate.

24. Backup / Restore

No dedicated backup tooling ships with v7 (recorded as technical debt, P2 — release policy does not require a backup service in this phase). The durability contract any restore path must satisfy is verified by tests/integration/persistence/test_backup_restore.py: targets, assets, evidence, findings, proofs and relationships survive a file-backed SQLite close/reopen cycle with a fresh engine, and cross-entity references (evidence→finding→proof→relationship) remain intact after restore.

25. Data Volume

tests/performance/persistence/test_data_volume.py generates 3000 assets + 3000 observations + 2000 URLs + 2000 endpoints + 1000 findings and verifies scoped list, correlation and report retrieval complete quickly. Measured on the development machine (file-backed SQLite):

No pathological query found at this volume. Recorded risks: per-row existence SELECT in save_many (P2); table-stream-then-filter in TargetIntelligenceQueryService scoped reads (P2).

26. Required Tests

New certification suites (all passing):

All ten sprint-required files are present: test_target_isolation.py, test_mission_isolation.py, test_evidence_provenance.py, test_finding_roundtrip.py, test_proof_roundtrip.py, test_history.py, test_repository_contracts.py, test_sql_migrations.py, test_transaction_integrity.py, test_persistence_failure_recovery.py.

27. Repairs

No redesign of the TIDB architecture, no duplicate repositories and no parallel persistence layer were introduced.

28. Technical Debt

Pre-existing, unchanged: 034.1/034.2 carry-overs — P0-01 (untracked V7 tree), P1-01 (repo-root V6 shadowing), P2-08 (ruff 77 pre-existing violations), P2-14 (duplicate concepts), P2-15 (mypy wide-surface), P2-16, P3-05..07.

29. Release Blockers

No new persistence release blockers remain. The three P1 persistence defects found by this audit (migration drift, soft-delete leak, missing runtime audit) were repaired and re-certified.

The overall V7 release gate remains blocked solely by the 034.1 carry-overs, which are outside the persistence scope:

30. Test Results

31. Final Verdict

TIDB / Persistence / Data Isolation certification — PASS

• Schema audited — 401 tidb_* + 6 legacy tables inventoried; envelope on all 401 verified.
• Critical entities verified — every sprint-listed entity class maps to a TIDB entity/table.
• Foreign keys verified — 15 key relationships confirmed by schema inspection.
• Target isolation passes — repos, services, API, adversarial (SQL + memory).
• Mission isolation passes — two missions on one target never contaminate.
• Evidence provenance survives persistence — full provenance round-trips intact.
• Findings round-trip correctly — complete validated finding, no field loss.
• PoCs round-trip correctly — proof + PoC metadata and links intact.
• History works — first/last seen, discovery/state/finding/proof history, chronological, append-only.
• Duplicate control works — canonical URL/IP/domain/endpoint keys DB-enforced; multi-tool attribution preserved; business-id dedup documented.
• Migrations pass — fresh upgrade, insert, downgrade, upgrade again, alembic check clean.
• Repository contracts pass — same suite green on SQL and memory.
• Memory/SQL semantics compatible — platform switching verified; two divergences documented (P2).
• Transaction integrity passes — atomic save_many, no half-written rows, consistent multi-step flows.
• Database failure recovery passes — connection/constraint/validation errors classified, safe rollback, state preserved.
• Secrets are not leaked — masked/hashed storage verified across database, evidence and API.
• Graph/TIDB relationship understood — graph is derived/cached; TIDB is authoritative; topology/attack-path facts persisted.
• Performance risks documented — N+1 read in save_many, service-layer table streaming, no pathological queries at volume.
• No P0 blocker remains — in persistence scope.
• No P1 persistence blocker remains — three P1s repaired this phase.
• Certification report generated — this document.

STOP — Phase 034.3 complete. Do not proceed to 034.4 automatically.

The TIDB is certified as the reliable, complete, isolated and reproducible operational memory of HunterX v7. The overall V7 release gate remains blocked only by the 034.1 repository-integrity carry-overs (P0-01 untracked V7 tree; P1-01 repo-root V6 shadowing).