# Data Research V60-V65 Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Upgrade DataOps data research into a tested, evidence-backed ingestion, data-element governance, and multi-domain ontology platform through V60-V65.

**Architecture:** PostgreSQL is the control-plane source of truth for jobs, evidence, candidates, reviews, immutable versions, and publication state. MinIO stores source artifacts, while Neo4j stores the active semantic projection; existing Outbox processing provides idempotent cross-store publication. A new `/api/development/v1` boundary orchestrates existing datasource, metadata, business-domain, knowledge, and graph capabilities without removing legacy APIs.

**Tech Stack:** Python 3.11, Flask, Flask-SQLAlchemy, Alembic, PostgreSQL JSONB, Neo4j, MinIO, SQLAlchemy inspector/catalog queries, pandas/openpyxl, python-docx, pdfplumber, pluggable OCR, Vue 2/Vuetify, pytest.

## Global Constraints

- Use TDD for every production behavior: add one failing test, verify the expected failure, implement minimally, then rerun the focused and phase suites.
- Preserve `/api/meta`, `/api/bd`, `/api/datasource`, `DataMeta`, and `BusinessDomain-[:INCLUDES]->DataMeta` during compatibility migration.
- Use stable UUID strings for all new cross-store references; Neo4j integer IDs are legacy compatibility only.
- Database collection is read-only through `DataSourceConnectionManager.connect(uid, "metadata_collection")`; never accept arbitrary metadata SQL from the client.
- AI/OCR output is always a candidate with confidence and evidence; it cannot directly publish a data element or ontology.
- Credentials, connection strings, tokens, and unredacted sensitive samples must not appear in API responses, logs, model requests, or evidence previews.
- `app/` is primary source. Update `deployment/app/` only through `deployment/sync_release.sh` at V65 release preparation.
- Every phase ends with focused tests, the complete Python suite, frontend production build when frontend changes exist, and `git diff --check`.

---

## V60 — Unified model and ingestion foundation

### Task 1: Add ingestion control-plane schema and models

**Files:**
- Create: `migrations/versions/20260722_100_data_research_ingestion.py`
- Create: `app/models/data_research.py`
- Modify: `app/models/__init__.py`
- Modify: `tests/test_database_migrations.py`
- Test: `tests/data_research/test_ingestion_models.py`

**Interfaces:**
- Produces: `IngestionSource`, `IngestionJob`, `SourceArtifact`, `EvidenceFragment`, `ExtractionCandidate` SQLAlchemy models.
- Produces: job statuses `created`, `queued`, `extracting`, `normalizing`, `matching`, `awaiting_review`, `published`, `partial`, `failed`, `cancelled`.

- [x] Write migration contract tests asserting the five tables, UUID foreign keys, content-hash/parser-version idempotency key, JSONB evidence locator, status checks, and non-destructive downgrade.
- [x] Run `PYTHONPATH=. .venv/bin/pytest -q tests/test_database_migrations.py tests/data_research/test_ingestion_models.py` and verify failure because the migration and models do not exist.
- [x] Implement the additive migration and SQLAlchemy models with `to_dict()` methods that never serialize storage credentials or internal MinIO secrets.
- [x] Rerun the focused tests and verify they pass.

### Task 2: Implement ingestion state machine and idempotent job service

**Files:**
- Create: `app/core/data_research/__init__.py`
- Create: `app/core/data_research/errors.py`
- Create: `app/core/data_research/models.py`
- Create: `app/core/data_research/repository.py`
- Create: `app/core/data_research/ingestion.py`
- Test: `tests/data_research/test_ingestion_service.py`

**Interfaces:**
- Produces: `IngestionService.create_job(payload, actor_uid)`, `transition(job_uid, target_status, statistics=None, error=None)`, `retry(job_uid)`, and `cancel(job_uid)`.
- Produces: `IngestionJobSpec`, `JobTransition`, and typed errors `InvalidJobTransition`, `IngestionJobNotFound`, `IngestionPayloadInvalid`.

- [x] Write tests for deterministic idempotency keys, same-input job reuse, force-rerun behavior, allowed/forbidden transitions, retry from failed, cancellation, and redacted error serialization.
- [x] Run `PYTHONPATH=. .venv/bin/pytest -q tests/data_research/test_ingestion_service.py` and verify failure because the service is absent.
- [x] Implement pure transition rules and repository-injected orchestration; keep Flask globals out of the core service.
- [x] Rerun the focused tests and verify they pass.

### Task 3: Add the development API boundary, permissions, and task contracts

**Files:**
- Create: `app/api/data_development/__init__.py`
- Create: `app/api/data_development/routes.py`
- Modify: `app/__init__.py`
- Modify: `app/core/system/permissions.py`
- Create: `frontend/src/api/dataDevelopment.js`
- Test: `tests/data_research/test_development_api.py`
- Test: `tests/data_research/test_development_frontend_contract.py`
- Modify: `tests/test_permission_matrix.py`

**Interfaces:**
- Produces: `POST/GET /api/development/v1/ingestion-jobs`, `GET /ingestion-jobs/<uid>`, `POST /<uid>/retry`, and `POST /<uid>/cancel`.
- Produces: permissions `ingestion:run`, `ingestion:admin`, `evidence:download`, `data-elements:edit`, `data-elements:publish`, `ontologies:edit`, `ontologies:publish`.

- [x] Write API and permission tests for 401, 403, editor create/list, owner cancellation, admin retry, stable response envelopes, and secret-free payloads.
- [x] Run the focused API/permission tests and verify missing blueprint/permissions failures.
- [x] Register the blueprint, add monotonic role permissions, inject the service through a testable factory, and add frontend API wrappers.
- [x] Run focused tests, regenerate `docs/architecture/OPENAPI.yaml`, then rerun architecture artifact tests.

### V60 gate

- [x] Run `PYTHONPATH=. .venv/bin/pytest -q tests/data_research tests/test_database_migrations.py tests/test_permission_matrix.py tests/test_architecture_artifacts.py`.
- [x] Run `PYTHONPATH=. .venv/bin/pytest -q` and `git diff --check`.
- [x] Record evidence in `docs/validation/data-research-v60.md` and commit V60.

## V61 — Database catalog and structured files

### Task 4: Add deterministic SQL, CSV, and Excel extractors

**Files:**
- Create: `app/core/data_research/extractors/base.py`
- Create: `app/core/data_research/extractors/sql.py`
- Create: `app/core/data_research/extractors/csv.py`
- Create: `app/core/data_research/extractors/excel.py`
- Create: `app/core/data_research/extractors/registry.py`
- Test: `tests/data_research/test_structured_extractors.py`

**Interfaces:**
- Produces: `Extractor.can_handle(media_type, filename)`, `extract(stream, context) -> ExtractionBatch`, and evidence locators `sql.statement`, `csv.header`, `excel.sheet/cell_range`.
- Consumes: existing `DDLParser` only as SQL fallback; deterministic extraction runs first.

- [x] Write tests for dialect-preserving DDL, multi-table SQL, BOM/delimiter CSV, empty/header-only CSV, multi-sheet Excel, parser version, content hash, and exact evidence positions.
- [x] Run the extractor tests and verify missing-registry failures.
- [x] Implement bounded streaming extractors and registry selection without reading arbitrary large files into unbounded memory.
- [x] Rerun focused tests and existing `tests/test_ddl_parser_view.py`.

### Task 5: Add PostgreSQL and MySQL catalog collectors with snapshot diff

**Files:**
- Create: `app/core/data_research/catalog/base.py`
- Create: `app/core/data_research/catalog/postgresql.py`
- Create: `app/core/data_research/catalog/mysql.py`
- Create: `app/core/data_research/catalog/service.py`
- Test: `tests/data_research/test_catalog_collectors.py`
- Test: `tests/integration/test_data_research_catalog.py`

**Interfaces:**
- Produces: `CatalogCollectionService.collect(data_source_uid, scope) -> CatalogSnapshot` and `diff(previous, current) -> CatalogDiff`.
- Consumes: `DataSourceConnectionManager.connect(uid, "metadata_collection")` and allowlisted schema/table filters.

- [x] Write unit tests for generated allowlisted catalog statements, schema/table exclusions, stable field keys, no business-row reads, add/remove/type/rename diffs, and query timeout propagation.
- [x] Run unit tests and verify missing collector failures.
- [x] Implement dialect collectors and pure snapshot differ; never concatenate untrusted identifiers into SQL.
- [x] Run unit tests and, when source containers are available, the integration catalog test.

### Task 6: Implement data-element lifecycle and candidate decisions

**Files:**
- Create: `app/core/data_research/data_elements.py`
- Create: `app/core/data_research/candidate_decisions.py`
- Create: `app/core/data_research/graph_projection.py`
- Modify: `app/api/data_development/routes.py`
- Modify: `app/models/data_research.py`
- Create: `tests/data_research/test_data_element_lifecycle.py`
- Create: `tests/data_research/test_candidate_decisions.py`

**Interfaces:**
- Produces: lifecycle `candidate -> draft -> in_review -> published -> deprecated -> retired`.
- Produces: batch decisions `reuse`, `create`, `map`, `ignore`, and outbox event `data_element.version_published`.

- [x] Write tests for required definitions, stable UID/version increments, optimistic concurrency, invalid lifecycle moves, batch decision atomicity, evidence retention, and no direct candidate publication.
- [x] Run focused tests and verify missing service failures.
- [x] Implement lifecycle/version services, extend API endpoints, and emit outbox events within the PostgreSQL transaction.
- [x] Rerun focused tests plus outbox and cross-store consistency tests.

### V61 gate

- [x] Run all V61-focused tests and `tests/core/data_source`.
- [x] Run the complete Python suite and `git diff --check`.
- [x] Record evidence in `docs/validation/data-research-v61.md` and commit V61.

## V62 — Documents, scanned PDFs, and images

### Task 7: Add DOCX and text-PDF evidence extractors

**Files:**
- Create: `app/core/data_research/extractors/docx.py`
- Create: `app/core/data_research/extractors/pdf.py`
- Modify: `app/core/data_research/extractors/registry.py`
- Test: `tests/data_research/test_document_extractors.py`

**Interfaces:**
- Produces evidence locators `docx.paragraph/table/row/cell` and `pdf.page/table/bbox`.

- [x] Write in-memory fixture tests for paragraphs, tables, repeated headers, empty documents, page ordering, and exact evidence locations.
- [x] Run focused tests and verify missing extractors.
- [x] Implement structured extraction using python-docx and pdfplumber; return OCR-required classification when a PDF page has no usable text.
- [x] Rerun focused tests and existing DDL document parser tests.

### Task 8: Add pluggable OCR and scanned-document processing

**Files:**
- Create: `app/core/data_research/ocr/base.py`
- Create: `app/core/data_research/ocr/http_provider.py`
- Create: `app/core/data_research/ocr/service.py`
- Create: `app/core/data_research/extractors/image.py`
- Test: `tests/data_research/test_ocr_service.py`
- Test: `tests/data_research/test_image_extractor.py`

**Interfaces:**
- Produces: `OcrProvider.extract(image_bytes) -> list[OcrBlock]` with text, normalized bounding box, page, and confidence.
- Produces: a fail-closed HTTP provider with configured endpoint, timeout, TLS verification, response-size limit, and no implicit external default.

- [x] Write tests with a local fake provider for PNG/JPEG, multi-page scanned PDF page ordering, normalized boxes, low-confidence review flags, provider timeout, malformed response, and disabled-provider failure.
- [x] Run tests and verify missing OCR interfaces.
- [x] Implement provider abstraction, fail-closed configuration, image extractor, and scanned-PDF page handoff.
- [x] Rerun focused tests and verify no source bytes or tokens appear in errors/logs.

### Task 9: Unify file policy, artifact storage, and evidence preview

**Files:**
- Create: `app/core/data_research/file_policy.py`
- Create: `app/core/data_research/artifacts.py`
- Modify: `app/api/data_development/routes.py`
- Modify: `app/config/config.py`
- Test: `tests/data_research/test_file_policy.py`
- Test: `tests/data_research/test_evidence_api.py`

**Interfaces:**
- Produces: one allowlist for SQL, XLS/XLSX, CSV, DOCX, PDF, PNG, JPG/JPEG; legacy DOC returns a deterministic conversion-required error.
- Produces: `POST /sources/files` and permission-filtered `GET /evidence/<uid>`; full download requires `evidence:download`.

- [x] Write tests for extension/MIME/magic agreement, size/page limits, legacy DOC rejection, hash deduplication, unsafe filenames, evidence redaction, and download permissions.
- [x] Run focused tests and verify failures against current split policies.
- [x] Implement the unified policy, MinIO gateway abstraction, artifact hash reuse, and redacted preview endpoint.
- [x] Rerun focused tests plus datasource/file security regression tests.

### V62 gate

- [x] Run all V62-focused tests, the complete Python suite, and `git diff --check`.
- [x] Record evidence in `docs/validation/data-research-v62.md` and commit V62.

## V63 — Ontology MVP

### Task 10: Add ontology control-plane schema and repository

**Files:**
- Create: `migrations/versions/20260722_110_data_research_ontology.py`
- Extend: `app/models/data_research.py`
- Create: `app/core/data_research/ontology/models.py`
- Create: `app/core/data_research/ontology/repository.py`
- Test: `tests/data_research/test_ontology_schema.py`
- Test: `tests/data_research/test_ontology_repository.py`

**Interfaces:**
- Produces: `Ontology`, immutable `OntologyVersion`, `OntologyChangeSet`, and `OntologyPublishRun`.
- Produces graph document fields `classes`, `properties`, `relations`, `constraints`, `domain_links`, and `element_mappings`.

- [x] Write schema and repository tests for unique ontology codes, immutable published versions, parent version, content hash, many-domain role links, and optimistic draft revisions.
- [x] Run focused tests and verify missing schema/repository failures.
- [x] Implement additive migration, dataclasses, canonical graph hashing, and repository operations.
- [x] Rerun focused and migration tests.

### Task 11: Implement ontology validation, publication, projection, and rollback

**Files:**
- Create: `app/core/data_research/ontology/validation.py`
- Create: `app/core/data_research/ontology/publication.py`
- Create: `app/core/data_research/ontology/projection.py`
- Test: `tests/data_research/test_ontology_validation.py`
- Test: `tests/data_research/test_ontology_publication.py`

**Interfaces:**
- Produces validation codes for duplicate names, dangling edges, inheritance cycles, invalid cardinality, ownerless ontology, and unmapped required property.
- Produces events `ontology.version_published` and `ontology.rollback_version_created`.

- [x] Write tests for every validation code, publish idempotency, outbox atomicity, active-version projection, retryable Neo4j failure, version diff, and rollback-as-new-version behavior.
- [x] Run focused tests and verify missing service failures.
- [x] Implement pure validation, publish transaction, UID-based Neo4j projection, diff, and rollback version creation.
- [x] Rerun focused tests plus cross-store/outbox tests.

### Task 12: Add ontology APIs and Vue 2 ontology center

**Files:**
- Modify: `app/api/data_development/routes.py`
- Modify: `frontend/src/api/dataDevelopment.js`
- Create: `frontend/src/views/dataGovernance/ontology/index.vue`
- Create: `frontend/src/views/dataGovernance/ontology/workbench.vue`
- Modify: `frontend/src/router/routes.js`
- Test: `tests/data_research/test_ontology_api.py`
- Test: `tests/data_research/test_ontology_frontend_contract.py`

**Interfaces:**
- Produces ontology CRUD, graph patch, validate, publish, diff, and rollback endpoints under `/api/development/v1`.
- Produces permission-gated ontology list and workbench routes.

- [x] Write API tests for editor drafts, publisher release, multi-domain roles, ETag conflicts, validation errors, and viewer read-only behavior; write source contract tests for routes and secret-free rendering.
- [x] Run focused tests and verify missing endpoints/pages.
- [x] Implement API handlers and Vue pages with graph JSON editing/preview, validation panel, version diff, and publish confirmation.
- [x] Run focused tests and frontend production build.

### V63 gate

- [x] Run all ontology tests, complete Python suite, frontend build, and `git diff --check`.
- [x] Record evidence in `docs/validation/data-research-v63.md` and commit V63.

## V64 — Dynamic ontology and knowledge services

### Task 13: Generate and review explainable ontology change sets

**Files:**
- Create: `app/core/data_research/ontology/suggestions.py`
- Create: `app/core/data_research/ontology/change_sets.py`
- Modify: `app/api/data_development/routes.py`
- Test: `tests/data_research/test_ontology_suggestions.py`
- Test: `tests/data_research/test_ontology_change_sets.py`

**Interfaces:**
- Produces deterministic suggestions from business domains, published data elements, foreign-key evidence, and aliases before optional AI suggestions.
- Produces decisions `accept`, `reject`, `edit` with actor, reason, evidence UIDs, confidence, and model/prompt version.

- [x] Write tests for deterministic class/property/relation proposals, evidence-required AI proposals, confidence ordering, conflict detection, partial decisions, and the guarantee that undecided suggestions never publish.
- [x] Run focused tests and verify missing services.
- [x] Implement rule suggestions, injected AI suggestion port, immutable decision audit, and accepted-change application to a draft version.
- [x] Rerun focused tests and ontology publication regressions.

### Task 14: Add JSON/RDF/OWL exchange and governance knowledge synchronization

**Files:**
- Create: `app/core/data_research/ontology/exchange.py`
- Create: `app/core/data_research/ontology/knowledge_sync.py`
- Modify: `app/api/data_development/routes.py`
- Test: `tests/data_research/test_ontology_exchange.py`
- Test: `tests/data_research/test_ontology_knowledge_sync.py`

**Interfaces:**
- Produces deterministic JSON export and bounded RDF/OWL XML export/import with stable platform UIDs.
- Consumes: `app/core/knowledge/document_builder.py`; produces versioned governance documents only for published ontology versions.

- [x] Write round-trip tests for classes/properties/relations/domain links, unsafe XML rejection, import size limits, deterministic hashes, secret redaction, and knowledge-document version/hash behavior.
- [x] Run focused tests and verify missing exchange/sync modules.
- [x] Implement bounded import/export, canonical namespaces, publication-triggered knowledge documents, and idempotent chunk sync.
- [x] Rerun focused tests plus governance document builder tests.

### Task 15: Expose read-only semantic queries and MCP context

**Files:**
- Create: `app/core/data_research/ontology/query.py`
- Modify: `app/api/data_development/routes.py`
- Modify: `app/core/mcp/context.py`
- Test: `tests/data_research/test_semantic_query.py`
- Test: `tests/mcp/test_data_research_context.py`

**Interfaces:**
- Produces bounded read-only queries from ontology property to data element, physical field, source evidence, and business domain.
- Produces MCP context tools that return published data only and enforce permission scope.

- [x] Write tests for bounded traversal, unpublished exclusion, permission filtering, stable pagination, no arbitrary Cypher input, and MCP result redaction.
- [x] Run focused tests and verify missing query/tool behavior.
- [x] Implement allowlisted query methods and MCP context adapters.
- [x] Rerun focused tests and existing MCP security/runtime suites.

### V64 gate

- [x] Run all V64-focused, governance knowledge, MCP, complete Python, and frontend build tests.
- [x] Record evidence in `docs/validation/data-research-v64.md` and commit V64.

## V65 — Production hardening and acceptance

### Task 16: Add limits, metrics, reconciliation, and recovery

**Files:**
- Create: `app/core/data_research/operations.py`
- Create: `app/commands/reconcile_data_research.py`
- Modify: `app/core/system/health.py`
- Test: `tests/data_research/test_operations.py`
- Test: `tests/integration/test_data_research_reconciliation.py`

**Interfaces:**
- Produces metrics for job states/duration, failure stage, candidate acceptance, review time, ontology coverage, and projection lag.
- Produces reconciliation reports for PostgreSQL versions, Neo4j active projections, MinIO artifacts, and governance documents.

- [x] Write tests for concurrency/resource limits, stale-job recovery, dead-letter reporting, projection mismatch repair plans, artifact-missing detection, and secret-free health output.
- [x] Run focused tests and verify missing operations module.
- [x] Implement bounded counters, recovery transitions, read-only audit, explicit repair mode, and health component.
- [x] Rerun focused tests plus health/outbox tests.

### Task 17: Complete task center, ingestion, data-element, and ontology UX

**Files:**
- Create: `frontend/src/views/dataGovernance/development/index.vue`
- Create: `frontend/src/views/dataGovernance/development/ingestion.vue`
- Create: `frontend/src/views/dataGovernance/development/tasks.vue`
- Create: `frontend/src/views/dataGovernance/development/review.vue`
- Modify: `frontend/src/views/dataGovernance/metadata/index.vue`
- Modify: `frontend/src/router/routes.js`
- Test: `tests/data_research/test_data_research_frontend_contract.py`

**Interfaces:**
- Produces source selection, scope configuration, upload, progress, evidence preview, batch decisions, data-element lifecycle, ontology generation, validation, publish, and rollback navigation.

- [x] Write frontend source-contract tests for every required route/API/permission, supported format copy, evidence navigation, status labels, no credential fields, and viewer read-only controls.
- [x] Run focused tests and verify missing pages/contracts.
- [x] Implement Vue 2 pages using existing list/dialog/form patterns and permission metadata.
- [x] Run focused tests and `npm --prefix frontend run build` with Node 24.

### Task 18: Release parity, documentation, and end-to-end acceptance

**Files:**
- Create: `tests/acceptance/test_data_research_v60_v65.py`
- Create: `docs/validation/data-research-v60-v65-acceptance.md`
- Modify: `docs/architecture/ARCHITECTURE_OVERVIEW.md`
- Modify: `docs/architecture/DATA_MODEL.md`
- Modify: `docs/architecture/NEXT_ITERATION_ROADMAP.md`
- Modify: `docs/architecture/OPENAPI.yaml`
- Modify: `deployment/app/` via `deployment/sync_release.sh`

**Interfaces:**
- Produces one acceptance entry point covering source ingestion, evidence, data-element publication, two-domain ontology, dynamic change set, publish, rollback, knowledge sync, RBAC, reconciliation, and redaction.

- [x] Write acceptance tests that initially fail on any missing V60-V65 capability and use deterministic fixtures/fake OCR/AI ports while exercising real Flask services and persistence contracts.
- [x] Run `PYTHONPATH=. .venv/bin/pytest -q tests/acceptance/test_data_research_v60_v65.py` and verify requirement-specific failures before final wiring.
- [x] Complete release wiring, regenerate OpenAPI, run `deployment/sync_release.sh`, and verify `diff -qr app deployment/app` is empty for application source.
- [x] Run the final acceptance matrix below and record exact commands, counts, skips, build output, and environment-limited checks in the acceptance document.

### V65 and final acceptance gate

- [x] `PYTHONPATH=. .venv/bin/pytest -q tests/data_research tests/acceptance/test_data_research_v60_v65.py`
- [x] `PYTHONPATH=. .venv/bin/pytest -q`
- [x] `npm --prefix frontend run build`
- [x] `python scripts/generate_openapi.py --output docs/architecture/OPENAPI.yaml` followed by `PYTHONPATH=. .venv/bin/pytest -q tests/test_architecture_artifacts.py`.
- [x] `diff -qr app deployment/app` and `git diff --check`.
- [x] When Docker is available, run catalog, reconciliation, RBAC, MCP, and migration integration tests against `deploy/docker/docker-compose.yml`.
- [x] Mark V60-V65 complete only if every non-environment-limited requirement has fresh passing evidence and every skipped external check is explicitly listed with its blocker.