# Data Eyond — Python Agentic Service: Current Status

**Audience:** teammates onboarding onto the Python repo (`Agentic-Service-Data-Eyond-Catalog`).
**Scope:** what the code does **right now** (branch `pr/4`, ticket KM-652). Describes current state only — no roadmap or to-dos.
**Snapshot date:** 2026-06-25. **Data-layer reconcile 2026-07-01:** §8/§12 updated — dedorch cutover done, `data_catalog` model reconciled. **Query-path fix 2026-07-02:** §8/§13 — dedorch catalogs ship no FKs → Python infers them (`fk_inference.py`); shared-Fernet-key gotcha documented. **Agent-quality fixes 2026-07-08 (pr/13):** from the scoped live-test review — the planner gains an explicit **infeasible** outcome (`TaskList.infeasible_reason` → deterministic EN/ID data-gap reply via `refusals.data_gap_message`; no more force-mapping absent measures like `pa` AS "revenue"), the IR validator rejects bare selects under `group_by` (self-corrects via the planner retry), `analyze_trend` handles integer year/month columns (was collapsing every row into one 1970-01 bucket), planner few-shots add top-N (Example G) + infeasible (Example H), numeric catalog `sample_values` are base64-decoded at read (`catalog/sample_decode.py` — stopgap for Go's byte-marshaling; primary fix is Go-side), traceability no longer emits null source rows for failed retrievals, and `check_data` hides `-1` row counts. **Report v2 + analyze_merge planner support 2026-07-09 (pr/13):** Sofia's `analyze_merge` tool (8abf635, KM-703) is now planner-supported (`_validate_data_source` guards `data_right`, two-retrieve→merge few-shot Example I, planner.md "Two measures per entity" rule); the report gains per-business-question answers (`bq_answers` — drafted by the SAME single LLM call, index-based record refs, deterministic fallback unchanged), "Attempted, Unresolved" + "Excluded Analyses" sections (failed runs are no longer silently dropped), evidence tables copied from `results_snapshot` (table-kind outputs, ≤3/record ≤10 rows ≤8 cols, `check_*` skipped), normalized caveat dedupe with caps (12/10), and single-language output via `detect_reply_language`; the report surface adds `GET /tools/report/{analysis_id}/records` (curation list), `GET …/readiness` (FE delta guard), and `exclude_record_ids` on POST — see API_CONTRACT_BE_PYTHON.md. **Report compaction 2026-07-09 (pr/13):** the rendered markdown drops the "Notes & Limitations", "Attempted, Unresolved", and "How This Was Analyzed" sections (team decision — compact report; render blocks commented out in `report/generator.py`, not deleted). The JSON body keeps `caveats`/`open_questions`/`unresolved`/`method_steps` and the curation/records endpoints are unchanged. **Cross-repo update 2026-06-29:** §2/§8/§11/§12 re-verified against
the **Go source** (`Orchestrator-Agent-Service`), not its docs. The Go service has moved well past its
own (uncommitted, stale) design docs: it now hosts the **dedorch SQL migrations** in-repo and a full
**`/api/v1/analyses` + `/api/v1/skills`** REST surface. Go does **not** call Python yet — those skills
are placeholders (see §12).

> This file is grounded in the source, not the older design docs. Where the two
> disagree, the code wins — see [§11 Doc-vs-code](#11-where-the-older-docs-are-stale).
> `REPO_CONTEXT.md` / `ARCHITECTURE.md` are the original Phase-2 design docs and are
> stale on the router, joins, and the analysis/report stack.

> 🚧 **Direction update 2026-06-30 (pr/5 — DECIDED · IN PROGRESS).** The 30 June checkpoint locked a
> restructure (contract: [API_ENDPOINTS_RESTRUCTURE.md](API_ENDPOINTS_RESTRUCTURE.md); live tracker:
> [DEV_PLAN §0](DEV_PLAN.md)). **Python is becoming a generation/AI-only service** — Go owns the full
> analysis lifecycle *and* the data-plane endpoints. Scope:
> - **Unwired from `main` + Swagger** (router files kept, *not* deleted): `analysis` CRUD, `room`, `db_client`, `document`, `data_catalog`, `users`/login. **✅ DONE — KM-686, commit `0b2d678`** (so the §7 rows for these are now commented out of `main.py`).
> - **AI surface that stays live:** `chat` → **`POST /api/v2/chat/stream`** (explicit **`analysis_id`**, not `room_id`); the skills regroup under **`/api/v1/tools/`** (`list` · `help` · `report`); plus a **new `GET /api/v1/traceability`** (user-facing provenance per answer, backed by a Python-owned `message_traceability` store — renamed from `observability`, KM-691). **✅ built.**
> - **Only `chat/stream` moves to `/api/v2`;** everything else stays `/api/v1`.
>
> §2/§4/§7 below still describe the **pre-restructure wiring** except the unwire above, which has landed.

---

## 1. The product in one paragraph

Data Eyond is an **"AI data scientist"** for business analytics, modelled on **CRISP-DM**
(Business Understanding → Data Understanding → Preparation → Modeling → Evaluation →
Deployment). It targets executives doing self-serve deep-dives and analysts offloading
routine work. A user defines a goal, connects data (DB or files), asks natural-language
analytical questions, and gets CRISP-DM-structured answers that can be exported as a
versioned **report**. The aim is "junior data scientist that hands back a decision-ready
deliverable," not "chatbot over a database."

---

## 2. Three repos, one hard ownership rule

Request flow is **FE → Go → Python**. The FE never calls Python directly except for chat
streaming.

| Repo | Role | We edit? |
|---|---|---|
| **Python** — `Agentic-Service-Data-Eyond-Catalog` (this repo) | The agentic LLM service: router, gate, skills, slow analytical path, structured query engine, unstructured RAG, report generation, analysis-session state. FastAPI + async SQLAlchemy + LangChain + Azure GPT-4o. | **Yes — the only repo we edit.** |
| **Go** — `Orchestrator-Agent-Service` | Gateway / data plane: auth/JWT, documents (Azure Blob + CSV/XLSX→Parquet + embeddings), database_clients (Fernet creds), **catalog ingestion** (moved into Go, KM-578/590), **all dedorch SQL migrations** (now embedded in the Go repo: `internal/repository/postgres/migrations/0001–0004`), and the **full analysis-lifecycle REST surface** (`/api/v1/analyses` CRUD + messages + reports, `/api/v1/skills`). The **interview agent and chat-rooms are deprecated → HTTP 410** (`internal/api/deprecation.go`). | Reference only. |
| **FE** — `E2E-Frontend-Data-Eyond` | React/Vite SPA. Talks to Go for everything and to Python only for chat streaming. | Reference only. |

> **» pr/5 (decided, not yet in code):** Python's non-AI endpoints (analysis CRUD, `room`, `document`,
> `db_client`, `data_catalog`, `users`/login) are being **unwired** — Python keeps only the
> generation/AI surface (chat, tools: `help`/`report`/`list`, traceability). See the Direction-update banner.

Shared infra: **Postgres** (app tables + `data_catalog` jsonb + PGVector `langchain_pg_embedding`), **Azure Blob**, and (Python-only) **Redis**.

---

## 3. Tech stack & how to run

- Python 3.12, FastAPI, uvicorn, sse-starlette
- Async SQLAlchemy 2.0 + asyncpg (Postgres); psycopg3 for the PGVector engine
- LangChain + langchain-openai (Azure OpenAI GPT-4o) + langchain-postgres (PGVector)
- Redis (response + retrieval cache), Azure Blob (uploads + Parquet)
- pandas / pyarrow, sqlglot, pydantic v2, structlog, slowapi, langfuse
- DB connectors: psycopg2, pymysql, pymssql, sqlalchemy-bigquery, snowflake-sqlalchemy

Run (Linux/Docker): `uv run --no-sync uvicorn main:app --host 0.0.0.0 --port 7860`
Run (Windows): `uv run --no-sync python run.py` (sets `WindowsSelectorEventLoopPolicy` for psycopg3 async — don't call uvicorn directly on Windows).

Tests live locally and are gitignored. Run with `./.venv/Scripts/python.exe -m pytest`.

---

## 4. Chat request lifecycle

Entry: `POST /api/v1/chat/stream` (`src/api/v1/chat.py`) → `ChatHandler.handle(...)`
(`src/agents/chat_handler.py`). One shared `ChatHandler` per process keeps the Azure clients warm.

> **» pr/5:** this endpoint moves to **`POST /api/v2/chat/stream`** with an explicit **`analysis_id`**
> field (replacing `room_id`), and the traceability detail (planning / tool I/O / sources) moves out of
> the stream to a separate `GET /api/v1/traceability` call. See the Direction-update banner.

```
POST /chat/stream { user_id, room_id, message }
  │  (analysis_id == room_id — one session = one analysis = one chat room)
  ├─ Redis response-cache check (1h TTL, key chat:{room}:{user}:{message})  ── hit → replay
  ├─ greeting/farewell short-circuit (_fast_intent, EN+ID)                  ── hit → canned reply
  ├─ load last-10 history
  └─ ChatHandler.handle:
       1. classify → RouterDecision               [1 GPT-4o call]
       2. ensure analysis-state row (get-or-create, idempotent)
       3. emit `intent` (internal; gates caching), then dispatch:
            chat              → ChatbotAgent → SSE
            help              → HelpAgent (state + history + readiness) → SSE
            check             → check_data/check_knowledge tool → rendered table  [no LLM]
            unstructured_flow → DocumentRetriever (PGVector RAG) → ChatbotAgent → SSE
            structured_flow   → CatalogReader → (slow path | QueryService) → SSE
       4. SSE events: intent (internal), sources, chunk, status, done | error
```

Only the `chat` intent is cached (stateless). Messages persist on `done`.

> The router emits **5 intents** now. The `problem_statement` skill and the `problem_validated`
> gate were removed 2026-06-25 (KM-652) — the analysis goal is two user-entered fields
> (`objective` + `business_questions`) captured at onboarding, with no agent validation.

---

## 5. Report lifecycle

The report is a **dedicated API, not a chat route** (`src/api/v1/report.py`):

```
POST /report?analysis_id&user_id
  ├─ load analysis state; enforce the report FLOOR
  │     (≥1 substantive analyze_* success) → else 409
  ├─ ReportGenerator.generate (src/agents/report/generator.py):
  │     read persisted AnalysisRecords (list_for_analysis)
  │     deterministically assemble findings / caveats / open-questions /
  │       data-source appendix / CRISP-DM method appendix  (copied verbatim)
  │     ONE LLM call → executive summary only (deterministic fallback on failure)
  │     render markdown
  ├─ ReportStore.save: advisory-locked version assignment → dedorch `reports`
  └─ write report_id back onto analysis state

GET /report/{analysis_id}        → list versions (oldest-first)
GET /report/{analysis_id}/{ver}  → fetch one version
```

Two facts to internalise:
- **Records only exist on the slow path.** The slow path is now **always on** for
  `structured_flow` (the `ENABLE_SLOW_PATH` flag was removed 2026-07-02), so every
  structured question persists a record. Reports still 409 until at least one `analyze_*`
  task has actually succeeded (chat/help/check/unstructured turns write no record).
- **dedorch `reports` stores markdown only.** Structured report fields are computed at
  generation, rendered into `rendered_markdown`, and only the markdown is persisted; on
  read-back the structured fields come back empty.

---

## 6. Feature list (what's built)

- **5-intent handler router** (`chat`/`help`/`check`/`unstructured_flow`/`structured_flow`) with history-aware query rewriting (EN/ID).
- **Skills:** `help` (LLM, state-aware next-step guidance), `check` (no-LLM data/document inventory). *(The `problem_statement` skill and the `problem_validated` gate were removed 2026-06-25 — KM-652; `gate.py` kept as a no-op seam, `problem_statement.py` kept but unwired.)*
- **Slow analytical path:** Planner → TaskRunner → Assembler (static plan, degrade-and-continue, 3 LLM calls fixed).
- **Structured query engine:** catalog-driven JSON IR → deterministic SQL/pandas compiler → read-only executor, with **single-level FK joins** (DB sources only).
- **Unstructured RAG** over PGVector.
- **Analytics tools:** 4 registered composite `analyze_*` (descriptive, aggregate, correlation, trend) + 4 data-access tools (check_data, check_knowledge, retrieve_data, retrieve_knowledge). Four further composites (comparison, contribution, profile, segment) exist in code but are **not registered** with the Planner.
- **Versioned report generation** from persisted records.
- **Analysis sessions:** data-first creation gate (≥1 bound source); each turn reads the analysis-scope catalog so it sees only that analysis's bound sources.
- **Langfuse tracing** (PII-masked), **Redis caching**, **pooled DB engines** + speculative prewarm.

---

## 7. API surface (this repo, all under `/api/v1`)

> **» pr/5 (decided, not yet in code):** chat → `/api/v2/chat/stream` (`analysis_id`); `/tools` splits
> into `/tools/list` + `/tools/help` + `/tools/report`; new `/api/v1/traceability`; and the
> analysis-CRUD / `room` / `users` / `document` / `db_client` / `data_catalog` rows are unwired from
> `main` + Swagger. See the Direction-update banner.

| Endpoint | Purpose | Caller |
|---|---|---|
| `POST /chat/stream` | Main chat SSE (router → dispatch) | FE → Go → Python (the only FE→Python call today) |
| `DELETE /chat/cache` · `/chat/cache/room/{id}` · `/retrieval/cache/{user_id}` | Cache management | internal / ops |
| `POST /analysis/create` · `GET /analysis` · `GET /analysis/{id}` | Analysis-session CRUD (state + room + bindings created atomically) | intended FE → Go |
| `POST /report` · `GET /report/{id}` · `GET /report/{id}/{ver}` | Report generate / list / fetch | FE → Go (report button) |
| `GET /tools` | Slash-command catalog (static, cacheable) | Go caches it for the FE "/" menu |
| `users` · `room` · `document` · `db_client` · `data_catalog` routers | Phase-1 legacy; functionally migrated to Go | mostly dormant |

---

## 8. Data model

SQLAlchemy models in `src/db/postgres/models.py`. Created on startup by `init_db()`
unless `SKIP_INIT_DB=true`.

| Table | Shape | Written by | Read by |
|---|---|---|---|
| `users`, `rooms`, `chat_messages`, `message_sources` | base app | chat endpoint, Go | chat history |
| `documents`, `databases` | uploads + DB creds (Fernet-encrypted) | Go ingestion | executor cred resolution |
| `data_catalog` *(dedorch, Go-owned)* | `id` uuid, `scope_type` ('user'\|'analysis'), `user_id`, `analysis_id`, **`catalog_payload`** jsonb (the `Catalog`: Source → Table → Column), schema_version, generated_at, updated_at; partial-unique on `user_id WHERE scope_type='user'` | **Go `catalog.Service`** (all writes: DB/file ingestion) | CatalogReader → CatalogStore (**read-only**), planner, tools |
| `langchain_pg_embedding` | PGVector document chunks | Go ingestion | DocumentRetriever |
| `report_inputs` *(was `analysis_records`)* | jsonb `AnalysisRecord`, one per slow-path run; **Python-owned** | slow path | ReportGenerator, report readiness |
| `analyses` *(dedorch, plural)* | uuid `id`, `user_id`, `analysis_title`, `objective`, `business_questions` jsonb, `status` (active\|inactive), `data_bind`(+`data_bind_version`), `report_id`, `report_collection` — **defined by Go migrations**; `problem_statement`/`problem_validated`/`owner_id` already **dropped** there (`0003`/`0004`) | Go `/api/v1/analyses`; Python state store | gate (no-op), Help, report |
| `reports` *(dedorch)* | uuid, `analysis_id`, `user_id`, `title` + markdown `content` + `version` (UNIQUE per analysis) | Go + Python ReportStore | report API |
| `data_sources` *(dedorch, Go-owned)* | per-analysis binding table. **Python no longer reads or writes it** — bindings live in Go's `analyses.data_bind`, which Go materializes into the analysis-scope `data_catalog` row; Python scopes off that row. The table exists (Go migration) but Python is fully decoupled — do **not** drop it manually | Go migration | — (unused by Python) |
| `analyses_messages` *(dedorch)* | the analysis chat room (`role ∈ user\|ai`); replaces deprecated `rooms`/`chat_messages` | Go `/analyses/{id}/messages` | Python chat path **not yet migrated here** (§12) |

> ✅ **Python ORM ↔ dedorch drift — reconciled 2026-07-01.** `AnalysisStateRow` (`analyses`) dropped
> `problem_statement`/`problem_validated` and added `objective`/`business_questions` (Harry's #3);
> `data_catalog` was the last stale model. Its `Catalog` ORM (old `user_id`-PK + `data` jsonb) is now
> the dedorch shape (`id` PK, `scope_type`, **`catalog_payload`**), and `CatalogStore` reads
> `catalog_payload WHERE scope_type='user'` (matching Go's `catalog.Service`). This closed a **live
> bug**: the `check` skill / `CatalogReader` still selected the dropped `data_catalog.data` column, so
> every catalog read 500'd after the cutover ("what data do I have" → *"Sorry, I couldn't look that up:
> column data_catalog.data does not exist"*). Python's catalog **write** methods (`upsert`/
> `remove_source`/`StructuredPipeline`) were reconciled but are now **legacy** — Go owns ingestion.

**Catalog shape** (the jsonb in `data_catalog`):
`Catalog → Source[ {source_id, source_type ∈ schema|tabular|unstructured, name, location_ref} → Table[ {table_id, name, row_count, foreign_keys[]} → Column[ {column_id, name, data_type, nullable, pii_flag, sample_values|null, stats} ] ] ]`. PII columns have `sample_values: null` so real values never enter prompts.

> ⚠️ **dedorch catalogs ship empty `foreign_keys`** (Go's introspection drops FK constraints), yet the IR validator only allows FK-backed joins — so every cross-table question failed validation until 2026-07-02. `src/catalog/fk_inference.py` (wired into `CatalogStore.get`) now infers the obvious `<base>_id → <table>.id` edges at read time: conservative (single unambiguous target, matching `data_type`, schema sources only) and **self-disabling** once any real FK is present. It's a **stopgap** — the durable fix is Go emitting real FKs during introspection.

**QueryIR shape** (`src/query/ir/models.py`):
`{ source_id, table_id, joins[], select[], filters[], group_by[], order_by[], limit }`.
Joins are single-level equi-joins to a related table **in the same source**, FK-backed,
**DB sources only**.

---

## 9. Subsystems (where the code lives)

### Router — `src/agents/orchestration.py`
One GPT-4o structured-output call → `RouterDecision{intent, rewritten_query, confidence}`,
`intent ∈ {chat, help, check, unstructured_flow, structured_flow}` (`problem_statement` removed
2026-06-25). It's a
*handler* classifier: `structured_flow` = slow path, `unstructured_flow` = fast RAG; the
data-modality mix on the slow path is the Planner's job. Prompt: `src/config/prompts/intent_router.md`.

### Gate — `src/agents/gate.py`
**Neutered 2026-06-25 (KM-652):** `gate()` now passes every intent through unchanged — the
`problem_validated` redirect was removed (the goal is user-entered, no agent validation). The
function + `AnalysisState` contract are kept as a no-op seam; the call site in
`chat_handler.handle` is commented out. `AnalysisState` still carries (id, analysis_title,
problem_statement, problem_validated, owner_id, report_id, created_at, updated_at) until the
dedorch state migration (#3/#4) renames it.

### Skills — `src/agents/handlers/`
- `help.py` — LLM (streamed). A consistency guard derives the *allowed* actions from state
  (mirrors the gate) and feeds them to the prompt, so Help can't suggest a report when the goal
  isn't validated or there's nothing to report. Consumes a deterministic readiness signal.
- `check.py` — **no LLM.** Keyword cues route to `check_data`, `check_knowledge`, or both
  (helicopter view, concurrent). Renders tool tables to markdown.
- `problem_statement.py` — **unwired 2026-06-25** (no longer routed to; file kept intact). Was an
  LLM drafter that validated a goal and wrote `problem_validated`.

### Slow path — `src/agents/slow_path/` + `src/agents/planner/`
- **Planner** (`planner/service.py`) — 1 LLM call → `TaskList` (DAG of tool-call chains). 8-check
  validator with re-prompt retry (max 3). `BusinessContext` is a **stub** (`planner/business_context.py`),
  which is why the slow path stays opt-in.
- **TaskRunner** (`slow_path/task_runner.py`) — deterministic, 0 LLM. Wave-based execution,
  `${t<id>}` placeholder resolution (Pattern A), never-throw invocation, **degrade-and-continue**
  (failed task → dependents skipped, independent branches run). No replanning.
- **Assembler** (`slow_path/assembler.py`) — 1 LLM call authoring only the narrative; code copies
  the structured `results_snapshot` / `tasks_run` from the run state into the `AnalysisRecord`
  (the report's source of truth).

Streaming + persistence: `chat_handler._run_slow_path` bridges per-stage progress to SSE `status`
events, prewarms the DB engine in parallel with planning, emits the answer, then persists the
record stamped with `user_id` + `analysis_id`.

### Structured query engine — `src/query/`
`QueryService.run` (`query/service.py`): plan → validate → retry(3) → dispatch → execute; **never
raises** (errors land in `QueryResult.error`). `IRValidator` (`query/ir/validator.py`) checks
source/table/column existence, op/agg whitelists, type compatibility, limit cap, and **FK-backed
joins** (DB only). `DbExecutor` (`query/executor/db.py`): SqlCompiler → sqlglot SELECT-only guard →
Fernet-decrypt creds (with owner check) → `asyncio.to_thread` (30s timeout) → pooled engine
(read-only + statement_timeout) → 10k row cap. Defense-in-depth: IR validation + compiler whitelist
+ sqlglot guard + read-only session + LIMIT/timeout.

### Analysis-scoped catalog reads — `src/catalog/reader.py::AnalysisScopedCatalogReader`
An analysis is scoped to the sources the user picked by reading the **analysis-scope** catalog
(`data_catalog` `scope_type='analysis'`, Go-materialized with the bound db + file sources under
their real names). On a `structured_flow` turn the catalog reader is wrapped so the Planner and the
tools' re-reads see the same analysis-scoped snapshot; `check` and the report's data-source appendix
read it too. **Fail-open**: no analysis-scope row → user-scope catalog. The old `data_sources`
binding table + `AnalysisDataSourceStore`/`_ScopedCatalogReader` (#10) were **removed** — the writer
(`/analysis/create`) is Go-owned/unwired, so the table was always empty and its consumers fail-opened
to the whole (mis-named) user catalog.

### Tool layer — `src/tools/data_access.py`, `src/agents/planner/registry.py`
`DataAccessToolInvoker` implements the never-throw tool seam for the 4 data-access tools.
`retrieve_data` runs a pre-built IR (validate → dispatch → execute, skipping the planner) and
coerces `Decimal`→`float` — the Pattern A handoff the `analyze_*` tools consume. The planner
registry composes a local data-access spec stub (name-checked against `DATA_ACCESS_TOOLS`) with the
real `analytics_registry()`.

### Report — `src/agents/report/`
`generator.py` reads records, deterministically assembles structured fields, 1 LLM call for the
executive summary; `store.py` versions under an advisory lock and persists markdown to dedorch
`reports`; `readiness.py` defines the **report floor** (≥1 successful `analyze_*`; the
`problem_validated` precondition was dropped 2026-06-25) shared by the report API and the Help
readiness signal so the two can't disagree.

### Observability — Langfuse
The endpoint's `ChatHandler` runs with `enable_tracing=True`. One trace per request groups
router/planner/assembler/chatbot + tool spans. PII policy: router/planner unmasked (PII-safe
summaries); assembler/chatbot masked (see real rows); tool spans carry name + arg keys + row counts
only.

---

## 10. Feature flags

| Flag | Where | Default | Effect |
|---|---|---|---|
| ~~`ENABLE_SLOW_PATH`~~ | — | **removed 2026-07-02** | Flag deleted. `structured_flow` now **always** runs Planner/TaskRunner/Assembler (the single-query `QueryService` fast path was retired from the chat handler), so records always persist. `extra="allow"` ignores a stale `ENABLE_SLOW_PATH` left in any `.env`. |
| `ENABLE_GATE` | `settings.enable_gate` | **off** | **Deprecated 2026-06-25** — gate neutered; the flag has no effect. Kept to avoid `.env` churn. |
| `SKIP_INIT_DB` | `settings.skip_init_db` (.env/env) | **on** | Skip `init_db()` on startup — the dedorch cutover switch. **Defaults TRUE** (Go owns the dedorch schema); set `false` only for a local Python-owned DB. |
| `enable_tracing` | hardcoded `True` in `chat.py` | on (endpoint) | Langfuse tracing. |

---

## 11. Where the older docs are stale

Trust the code. The original Phase-2 docs (`ARCHITECTURE.md`, `REPO_CONTEXT.md`) and the Go repo's
copies disagree with the current code on:

| Topic | Old docs | Current code |
|---|---|---|
| Router | 3-way `source_hint` (chat/unstructured/structured) | Flat **5-intent** `RouterDecision` (was 6; `problem_statement` removed 2026-06-25) |
| Joins in IR | "single-table only; deferred" | **Single-level FK-backed joins** (DB sources only) |
| Analysis / report / gate / slow path | "Phase 2 spine only" | All built and present |
| `analysis_id` | open question | resolved: **`analysis_id == room_id`** |
| Report source | (newer invariant) "from records, never chat history" | confirmed: generator reads `AnalysisRecord`s |
| Go service scope | "interview agent + ingestion; dedorch migrations live outside the repos" | Go now hosts the **dedorch migrations in-repo** + a full **`/api/v1/analyses` + `/api/v1/skills`** REST surface; interview/rooms **deprecated (410)**. (Go's own `PROJECT_SUMMARY.md`/`REPO_CONTEXT.md` are uncommitted + stale.) |

---

## 12. dedorch migration — current state

The Python DB has moved from `dataeyond` → **dedorch** (cutover 2026-07-01; Go owns dedorch migrations;
Python is consumer-only). State **re-verified against the Go source 2026-06-29**:

- **The dedorch migrations now live IN the Go repo** — embedded SQL at
  `internal/repository/postgres/migrations/0001_create_core_schema.sql … 0004_replace_chat_with_analysis_scope.sql`,
  run on startup by `RunMigrations`. (This corrects the earlier note that the migrations were
  invisible / asserted only by Python docstrings.) The full schema is now readable there.
- **Go owns the analysis family end-to-end.** `analyses` / `analyses_messages` / `reports` /
  `data_sources` / `message_sources` / `data_catalog` are created by Go migrations and served by a
  full REST surface: `internal/api/analysis.go` (CRUD + `data-bind` w/ optimistic `expected_version`
  + messages + reports) and `internal/api/skills.go`. `analyses` already has the **pivot shape**
  (`objective` + `business_questions`, `status`, `data_bind`/`_version`, `report_collection`) and has
  **dropped** `problem_statement`/`problem_validated`/`owner_id`. Migration `0004` renames the legacy
  `rooms`/`chat_messages`/`interview_*` tables to `zdeprecated_*`.
- **`report_inputs`** (the slow-path structured output, formerly `analysis_records`) stays
  **Python-owned**; its finalized schema goes to Harry so the dedorch migration creates it post-cutover.
- **Connection-string cutover DONE (2026-07-01).** Python's `postgres_connstring` now points at
  **dedorch** and reads the Go-migrated tables directly. Every ORM model Python reads (`analyses`,
  `analyses_messages`, `data_catalog`) has been reconciled to its dedorch shape.
  **`init_db()` is now skipped by default** (`settings.skip_init_db` defaults **True**): its privileged
  DDL (`ALTER TABLE rooms …`, index creation) fails on Go-owned tables
  (`InsufficientPrivilegeError: must be owner of table rooms`). Skipping is safe — Go migration `0001`
  already provides the `vector` extension + the langchain FTS index. Set `SKIP_INIT_DB=false` (.env or
  env) only for a local Python-owned DB. `report_inputs` is not in any Go migration yet (#22) — create
  it in dedorch before enabling the slow path, else report/slow-path writes fail (chat path unaffected).

**⚠️ Integration gap (verified — the big one).** Go's `/api/v1/analyses` and `/api/v1/skills`
(`help` / `report`) are **placeholders that return dummy data** — the `SendMessage` / `GenerateReport`
handlers and the skills handler explicitly note *"placeholder integrasi backend agentic … will be
replaced by the external skills service."* **Go currently never calls Python's `/chat/stream`,
`/report`, or any skill** (no outbound HTTP to the agentic service exists in the Go source). So today
there are **two parallel, unconnected analysis stacks**: Go's self-contained placeholder lifecycle
(gate: ≥3 user messages; AI replies are canned) and Python's real agentic spine (router → slow path →
records-based report; floor: ≥1 `analyze_*` success). Wiring Go → Python is the open integration work
(DEV_PLAN #7/#18/#25), plus reconciling the two different report gates.

---

## 13. Conventions & gotchas

- **Two Postgres engines:** app engine + a separate PGVector engine (`prepared_statement_cache_size=0`)
  because PGVector emits multi-statement strings asyncpg rejects.
- **Identifiers vs values:** identifiers come from the catalog and are inlined as quoted; filter
  values are always parameterized.
- **Settings aliases:** `.env` uses double-underscore names (`azureai__api_key__4o`); `Settings`
  exposes them as `azureai_api_key_4o`.
- **Shared Fernet key across repos (gotcha).** User DB credentials in `databases` are written +
  encrypted by **Go** and decrypted by Python; both read the **same** env var
  `dataeyond__db__credential__key` (Go: `configs/app.yaml` → `credentials.fernet_key`). The two
  deployments MUST hold the **identical value** or Python's decrypt throws
  `cryptography.fernet.InvalidToken` — whose `str()` is **empty**, so it logged as `error=""` and
  masqueraded as a DB-connection failure (the executor now logs `repr(e)` to expose it). Tell-apart:
  a valid-but-wrong key → `InvalidToken`; a malformed key → a non-empty `ValueError` at cipher build.
- **Never-throw seams** are pervasive (tool invoker, query service, executors, state/catalog reads,
  record persistence, report summary). Failures degrade into soft output rather than raising — good
  for UX, but they can mask real breakage (e.g. a missing analysis-scope catalog silently falling
  back to the whole user catalog).
- **Prompts** live in `src/config/prompts/*.md`. `chatbot_system.md` has `guardrails.md` appended so
  guardrails win on conflict.
- **Tests** are gitignored (team decision) — run them locally.