0. 目的（Phase 7-C：更新版）

• ゴール：/analytics/query → /analytics/plan → /analytics/act が 安定して通る
• 前提：Chromaが起動して 必要collectionが常に存在し、top_k>0 を安全に有効化できる
• 重要方針（固定）
  • OdooDB：SQLの実行先のみ（スキーマ検証・探索に使わない）
  • 検証ソース（唯一の正）：Dev-Portalが持つ ir_*_src（＋カスタム情報）
  • Chroma：SQL組立の材料の取得（allowlistとしても使う）

---

1. /analytics/query の“最小契約”（必ず返す）

成功/失敗どちらでもレスポンスに status + diagnostics を必ず含める（= nlq-dev が分岐できる）。

1.1 レスポンス必須フィールド（最低限）

• status: "ok" | "error"
• reason_code: 固定列挙（後述）
• validation_source: 固定で "ir_src"（ここは絶対）
• join_plan: View由来 join + LLM補強候補 + 採否
• errors[]: 採用できない理由／実行失敗理由（0件でも返す）
• diagnostics: 常に返す（タイミング、retrieval、採用数など）

1.2 レスポンス例（成功）

{
  "status": "ok",
  "reason_code": "OK",
  "validation_source": "ir_src",
  "sql": "SELECT ...",
  "join_plan": {
    "graph_source": "view",
    "base_edges": [ /* view由来 */ ],
    "llm_edges": [ /* 提案のみ */ ],
    "decisions": [ /* edge採否 */ ],
    "final_joins": [ /* 実際にSQLへ採用したjoin */ ]
  },
  "errors": [],
  "diagnostics": {
    "timings_ms": { "retrieval": 120, "llm": 850, "validate": 40, "execute": 920 },
    "retrieval": { "top_k": 6, "collections": ["portal_field_ja","portal_view_common_ja"], "hits": 18 },
    "sql_hash": "sha256:...",
    "guards_applied": ["read_only","limit_200"]
  }
}

1.3 レスポンス例（実行で落ちた：undefined_column）

{
  "status": "error",
  "reason_code": "EXECUTION_ERROR_UNDEFINED_COLUMN",
  "validation_source": "ir_src",
  "sql": "SELECT ...",
  "join_plan": { "...": "..." },
  "errors": [
    {
      "stage": "execute",
      "code": "EXECUTION_ERROR_UNDEFINED_COLUMN",
      "message": "column public.res_partner.phone_xxx does not exist",
      "hint": "ir-src再取得 or フィールド候補切替",
      "details": { "sqlstate": "42703" }
    }
  ],
  "diagnostics": {
    "timings_ms": { "retrieval": 110, "llm": 790, "validate": 35, "execute": 210 },
    "execution": { "db": "odoo", "row_count": null }
  }
}

ここがポイント：OdooDBを検証に使わないので、ズレは「実行時に死ぬ」前提でOK。
だからこそ *EXECUTION_ERROR_ を“きれいに返す契約”**が重要で、nlq-dev は reason_code で運用分岐できます。

---

2. reason_code（固定列挙：nlq-dev が分岐する前提）

2.1 大分類

• OK系
  • OK
• 前処理/材料不足
  • RETRIEVAL_NO_HITS（Chromaヒット無し/閾値未満）
  • JOIN_GRAPH_EMPTY（View由来joinが作れない）
  • VALIDATION_FAILED（ir_src検証で採用できずSQL確定できない）
  • LLM_OUTPUT_INVALID（JSON/SQL生成が壊れた）
  • CONFIG_ERROR（collection未作成、必須env欠落等）
• 実行エラー（OdooDBで死ぬ＝割り切り）
  • EXECUTION_ERROR_UNDEFINED_TABLE
  • EXECUTION_ERROR_UNDEFINED_COLUMN
  • EXECUTION_ERROR_PERMISSION
  • EXECUTION_ERROR_TIMEOUT
  • EXECUTION_ERROR_SYNTAX
  • EXECUTION_ERROR_OTHER

2.2 nlq-dev 側の典型分岐（例）

• RETRIEVAL_NO_HITS →「カスタム情報がChromaに無い。モデル投入/再パッケージしてください」
• VALIDATION_FAILED →「ir-src上で許可されない。別候補に切替」
• EXECUTION_ERROR_UNDEFINED_COLUMN/TABLE →「メタが古い。ir-src再取得して再実行」
• EXECUTION_ERROR_PERMISSION →「権限不足。DBロール/ビュー側制約を確認」

---

3. JOIN設計（“View優先＋LLM補強”を安全にやる）

あなたの希望どおり、これが一番安全です。

3.1 原則（固定）

1. 一次ソース：View由来の joinグラフ（ir_view_src から抽出して保持）
2. LLMの役割：穴埋め 候補提案のみ（採用しない／“提案”）
3. 最終確定：
   • ir_src 検証（唯一の検証ソース）
   • Chroma allowlist（使ってよいモデル/フィールドだけに絞る）
4. 失敗時：reason_code + errors[] で説明（nlq-dev が処理できる）

---

4. join_plan のデータ構造（ブレ防止）

join_plan は “あとでデバッグできる” 形に固定します。

{
  "graph_source": "view",
  "base_edges": [
    {
      "src_model": "sale.order",
      "field": "partner_id",
      "dst_model": "res.partner",
      "cardinality": "many2one",
      "confidence": 1.0,
      "evidence": { "view_xmlid": "sale.view_order_form", "path": "partner_id" }
    }
  ],
  "llm_edges": [
    {
      "src_model": "sale.order",
      "field": "user_id",
      "dst_model": "res.users",
      "cardinality": "many2one",
      "confidence": 0.6,
      "evidence": { "prompt": "…" }
    }
  ],
  "decisions": [
    {
      "edge": { "src_model": "...", "field": "...", "dst_model": "...", "cardinality": "..." },
      "allowed_by_chroma": true,
      "validated_by_ir_src": true,
      "accepted": true,
      "reject_reasons": []
    }
  ],
  "final_joins": [
    {
      "join_sql": "JOIN res_partner p ON p.id = so.partner_id",
      "models_used": ["sale.order","res.partner"],
      "fields_used": ["sale.order.partner_id","res.partner.id"]
    }
  ]
}

---

5. 検証ロジック（“ir_src を唯一の検証ソース”）を具体化

ここが抽象だとブレるので、採用条件を明文化します。

5.1 前提：最低限 ir_field_src に必要な列

（あなたのCSVに合わせて rename してOK。最低限これが要る）

• model（モデル技術名）
• name（フィールド技術名）
• ttype（many2one/one2many/many2many/char…）
• relation（comodel：many2one/one2many/many2many）
• relation_field（one2manyの逆参照：相手側many2oneフィールド）
• relation_table, column1, column2（many2many用：あれば）

5.2 Edge採用条件（確定ルール）

A) many2one edge (src –field–> dst)
採用条件：

• ir_field_src に (model=src_model AND name=field AND ttype='many2one' AND relation=dst_model) が存在
• かつ Chroma allowlist に src_model.field が含まれる（※後述）

B) one2many edge (src –field–> dst)
採用条件：

• (model=src_model AND name=field AND ttype='one2many' AND relation=dst_model AND relation_field IS NOT NULL) が存在
• join は dst 側の relation_field を使う（dst.relation_field = src.id）
• かつ allowlist に必要フィールドが含まれる

C) many2many edge（Phase 7-Cでは後回し可）
採用条件：

• relation_table/column1/column2 が揃っている
• join は中間テーブル経由（rel.column1 = src.id AND rel.column2 = dst.id）

5.3 検証SQL（devportal DB上の ir_*_src を見る）

※テーブル名は仮に ir_field_src とします（実名に合わせて置換）。

many2one の存在確認

SELECT 1
FROM ir_field_src
WHERE model = :src_model
  AND name  = :field
  AND ttype = 'many2one'
  AND relation = :dst_model
LIMIT 1;

one2many の存在確認（relation_field 取得込み）

SELECT relation_field
FROM ir_field_src
WHERE model = :src_model
  AND name  = :field
  AND ttype = 'one2many'
  AND relation = :dst_model
  AND relation_field IS NOT NULL
LIMIT 1;

allowlist（Chroma由来）チェックの最小形

• retrievalで得た “許可フィールド集合” を allowed = { "sale.order.partner_id", ... } のように持つ
• edge採用時に src_model.field in allowed を必須にする（これが「Chroma許可リスト」）

---

6. 擬似コード（JOIN確定までの流れ：ぶれない“一本道”）

def build_sql(question, locale):
    # 0) diagnostics枠を先に作る（失敗でも返す）
    diag = new_diag()

    # 1) retrieval（Chroma）
    hits = chroma_search(question, top_k=TOPK)
    if not hits:
        return error(status="error", reason_code="RETRIEVAL_NO_HITS", errors=[...], diagnostics=diag)

    allowed_fields = extract_allowed_fields(hits)  # "model.field" set
    view_edges = extract_view_join_edges(hits)     # view由来edge（一次ソース）

    if not view_edges:
        return error(reason_code="JOIN_GRAPH_EMPTY", ...)

    # 2) LLMは“穴埋め候補”だけ作る（採用はしない）
    llm_edges = llm_propose_missing_edges(question, view_edges, allowed_fields)

    # 3) 検証（唯一の正：ir_src）
    decisions = []
    accepted_edges = []
    for edge in (view_edges + llm_edges):
        allowed_by_chroma = (f"{edge.src_model}.{edge.field}" in allowed_fields)
        ok, join_info_or_reason = validate_edge_by_ir_src(edge)  # SQLで確認
        accepted = allowed_by_chroma and ok

        decisions.append({ ... })
        if accepted:
            accepted_edges.append(materialize_join(edge, join_info_or_reason))

    if not accepted_edges:
        return error(reason_code="VALIDATION_FAILED", errors=[...], join_plan=..., diagnostics=diag)

    # 4) SQL生成（where/columnsも allowed_fields から選ぶ）
    sql = llm_generate_sql(question, accepted_edges, allowed_fields, locale=locale)

    # 5) 実行（OdooDB）※ここでズレたら EXECUTION_ERROR_* を返す
    try:
        rows = execute_on_odoo(sql)
        return ok(sql=sql, join_plan=..., rows=rows, diagnostics=diag)
    except PgError as e:
        return error(reason_code=map_pgerror(e), errors=[...], diagnostics=diag, sql=sql)

---

7. これを実装する時に“触るべき箇所”（次のChroma開発に必要な最小セット）

7.1 /analytics/query のレスポンス契約対応

• api/app/routers/analytics.py
  • 例外を握りつぶしてでも status/diagnostics/reason_code/errors を返す（想定内失敗はHTTP 200で返すのが運用しやすい）
• api/app/services/analytics/orchestrator.py
  • 返り値を「QueryResponse + status/diagnostics」へ統一

7.2 reason_code 列挙の固定化

• api/app/schemas/analytics*.py（または schemas/common.py）
  • ReasonCode Enum を追加（nlq-dev前提）
  • ErrorItem(stage, code, message, hint, details) を固定

7.3 join_plan と検証の追加（本丸）

• api/app/services/analytics/join_planner.py（新規推奨）
  • extract_view_join_edges(hits)（一次ソース）
  • propose_llm_edges(...)（提案のみ）
  • validate_edge_by_ir_src(edge)（SQLで検証）
  • materialize_join(edge, join_info)（JOIN句生成）
• api/app/repos/ir_src_repo.py（新規 or 既存）
  • ir_field_src を読むSQLをここに寄せる（検証ソース固定のため）
• api/app/services/chroma/...
  • hits から allowed_fields と view_edges を取り出せる doc/metadata を整備

---

8. 次に“確定させるべき最後の1点”（あなたが言ってた部分）

「あなたの ir_*_src のテーブル名に合わせて Join検証ロジック（採用条件）を具体SQLで確定」

ここだけは実名に合わせてブレなくしたいので、devportal DBに実在するテーブル名を基準に、

• ir_model_src
• ir_field_src
• ir_view_src
  の 正確なテーブル名（＋必要カラム名）へ上のSQLを置換した「確定版」を、次のステップでそのまま実装できます。

---

必要なら、このまま **/analytics/query のレスポンス schema（Pydantic）**まで “コピペでPRにできる形” で提示します（ReasonCode enum + join_plan/errors/diagnostics を含めた完全版）。