ユーザーが自然文を投げる
Dev-Portal（チャットUI）が /nlq/plan を叩く
- 候補SQL・使用/スキップテーブル・（あれば）displayヒントを受け取る
UIに「SQL 要約＋軽い説明」を出し、Run ボタンで /nlq/execute を叩く
- mode: "sample"→プレビュー、OKなら "full"
- display（任意）/ publish_mode（任意、devはpublic、本番はsigned）
/nlq/execute が DB 実行 → Metabaseカード自動作成（SQLハッシュ再利用） → URL/iframe を返す
UIは 表やSQLなどの一時出力は消去して、返ってきた図表リンクだけを会話に貼る
/nlq/history に行を保存（必要最小のメタ＋図表URLのみ）

これで「毎回、自然文→即席チャートURL」を返すサービスが完成。定型レポートは不問です。

使うエンドポイント（この順番だけ覚えればOK）

POST /nlq/plan
入力: { message, session_id, turn_no, output_level }
出力: { candidate_sql, validated_sql, used_tables, skipped_tables, display_hint?, explain? }
POST /nlq/execute
入力: { sql, mode("sample"|"full"), output_level, display?, publish_mode?, session_id, turn_no }
出力: { "sql":"…(LIMIT付与後)…", "rows":[…], "row_count":123, "stats":{ "elapsed_ms":87, "metabase": { "mode":"signed", // 本番 "resource":"question", "id":456, "hash":"a1b2c3d4e5f6", // SQLハッシュ（再利用キー） "reused": true, // 既存カード再利用ならtrue "display":"line", "iframe_src":"https://…/embed/question/<JWT>#…", "expires_at": 1730880000 } } } ※ dev なら mode: "public" で url が返ります。
POST /nlq/history（非同期でもOK）
入力: variant:"execute", validated_sql, stats.artifacts に Metabaseの返却だけ残す

チャットUIの状態遷移（最小）

idle → planning（/nlq/plan 呼び出し中）
review（候補SQL/要約表示、「Run」ボタン）
executing（/nlq/execute 呼び出し中）
done（表やSQLは消す／図表URLだけ残す）

「次の検索で前の出力全消し」は UI 側で review/executing に遷移したら直前のテーブル/SQL表示をクリア、stats.metabase のリンクだけ “前回の結果” セクションへ移す、という実装がシンプルです。

フロント（擬似コード：fetchだけでOK）

// 送信用の共通メタ
const base = { session_id, turn_no, lang: "ja" };

// ① 計画
const planRes = await fetch("/nlq/plan", {
  method: "POST",
  headers: { "Content-Type": "application/json", Authorization: "Bearer devtoken" },
  body: JSON.stringify({ message, output_level: "all", ...base })
}).then(r => r.json());

// ② 実行（sample → full）
const display = planRes.display_hint ?? undefined;  // 任意
const execRes = await fetch("/nlq/execute", {
  method: "POST",
  headers: { "Content-Type": "application/json", Authorization: "Bearer devtoken" },
  body: JSON.stringify({
    sql: planRes.validated_sql,
    mode: "sample",
    output_level: "chart",
    display,                 // 任意
    // devでは "public"、本番はサーバ既定が "signed" なので省略でOK
    ...base
  })
}).then(r => r.json());

// ③ 表示はURLだけ
const link = execRes.stats?.metabase?.iframe_src ?? execRes.stats?.metabase?.url;
// チャットに「Open chart」リンク or iframe を貼る

OpenAIキーはサーバ側に置くのが安全（Dev-Portalからは自前APIを叩く）。
ストリーミングが欲しければ、/nlq/plan をSSE/WebSocketで段階イベント（plan/probe/validate）配信にしてもOK。

いま決めておくと良い“連結の約束”

session_id / turn_no は Dev-Portal で管理（1会話＝固定の session_id、発話ごとに turn_no++）。
出力クリアのルール：新規検索開始時に 表/SQLは破棄、stats.metabase だけ “Recent charts” に残す。
認証：当面 AUTH_MODE=static（Bearer devtoken）→ のちにOIDCに差し替え。
CORS：Dev-Portalのオリジンを許可。
レート制御：/nlq/execute はユーザー/分でレート制限（誤爆防止）。

サーバ側で既に用意済み＆これから足すと良いもの

もうある（あなたの実装）

単一SQLガード（複文禁止/SELECT限定/危険語拒否/LIMIT付与）
Metabase: SQLハッシュでカード再利用、返りは signed(iframe_src)/public(url)
stats.metabase に成果物を返却

これから足すと使いやすい

/nlq/plan のレスに 短い要約（explain） と display_hint（table/line/bar）
/nlq/execute の publish_mode 上書き（devだけ public を許容）
失敗時の人間向けメッセージ整形（DB例外→「対象期間が広すぎるかも」等）

まとめ

はい、Dev-Portal接続は“その時に作る”でOK。そのための インタフェースはもう揃っている ので、チャットUIは
1. /nlq/plan → 2) /nlq/execute → 3) 図表URLだけ残す、の3呼び出しで実現できます。
本番は 署名付き埋め込み（signed）必須、カードは SQLハッシュで再利用。
UIは 毎回の一時出力を消してリンクだけ保持、というあなたの方針で問題なし。

必要なら、Reactのチャットコンポーネント最小実装（送信→状態遷移→リンク表示）まで一気に出します。

あなた:

SQLが実行されて出てきたデータに対して分析を加える（OPENAI）、改善プランを出す、というのも実装しておきたいです。

ChatGPT:

いいですね。**「SQL結果にAIで分析＋改善プラン」**を、今の設計に無理なく載せる最小追加を出します。チャットUIからは /nlq/execute のオプションで呼べる形にします。

変更点の要約（最小で堅く）

API拡張：ExecuteRequest に analyze_mode を追加（off|quick|deep）。
レスポンス拡張：ExecuteResponse.stats.analysis を追加（要約・洞察・改善提案・次アクション等）。
実装追加：app/services/analysis.py（OpenAIで構造化JSONを返す）。
サーバ内で前処理：全行を投げずに
- サンプル行（最大200行）
- 列プロファイル（count/null率/最小・最大/平均/ユニーク数・上位カテゴリ） をSQLで算出して送る
  → トークン・PII・速度の三方良し。

OpenAPIの差分（抜粋）

ExecuteRequest に追加

      properties:
        analyze_mode:
          type: string
          description: SQL結果に対するAI分析レベル
          enum: ["off", "quick", "deep"]
          default: "off"

ExecuteResponse に追加

        stats:
          type: object
          properties:
            elapsed_ms: { type: integer }
            metabase: { $ref: '#/components/schemas/MetabaseLink', nullable: true }
            metabase_items:
              type: array
              items: { $ref: '#/components/schemas/MetabaseLink' }
              nullable: true
            analysis:
              $ref: '#/components/schemas/AIAnalysis'
              nullable: true
          required: ["elapsed_ms"]

新規スキーマ

    AIAnalysis:
      type: object
      properties:
        summary: { type: string, description: 一言要約 }
        key_findings:
          type: array
          items: { type: string }
        anomalies:
          type: array
          items:
            type: object
            properties:
              column: { type: string }
              type: { type: string, description: outlier|null_spike|range_mismatch|dup_key 等 }
              detail: { type: string }
        suggestions:
          type: array
          description: 改善提案（次アクション寄り）
          items:
            type: object
            properties:
              title: { type: string }
              impact: { type: string, description: low|medium|high }
              effort: { type: string, description: low|medium|high }
              action_sql: { type: string, nullable: true }   # 改善検証用SQL
        sql_improvements:
          type: array
          description: クエリ自体の改善案（安全なSELECTのみ）
          items:
            type: object
            properties:
              reason: { type: string }
              revised_sql: { type: string }
        next_questions:
          type: array
          items: { type: string }
        confidence: { type: number, format: float, minimum: 0, maximum: 1 }
        used_inputs:
          type: object
          description: AIに渡した要約情報
          properties:
            sample_rows: { type: integer }
            profile_columns: { type: integer }
            row_count: { type: integer }
            guards: { type: array, items: { type: string } }

既存との互換は崩さず、“欲しいときだけ analysis を返す” 形です。

実装の中身（サーバ側）

1) 前処理：サンプル＋列プロファイルをDBで取る

サンプル：SELECT * FROM (<validated_sql_without_limit>) t LIMIT 200
プロファイル（列ごとに）：
- 数値列：count, nulls, min, max, avg, stddev, approx_distinct
- 文字列列：count, nulls, avg_len, max_len, approx_distinct, top_kカテゴリ
- 日付列：min, max, 期間幅
これらは追加SQLで軽量に取得。結果は集計値のみ（PIIは出さない）。

2) 送るペイロードを最小化＆マスキング

サンプル行は最大200行かつ疑似匿名化（メール/電話/住所/個人名にヒットしたら *** に置換）。
列プロファイルはそのまま（値は統計量）。

3) `services/analysis.py`（雛形）

# app/services/analysis.py
from __future__ import annotations
from typing import List, Dict, Any, Literal
import os, json

Model = os.getenv("OPENAI_MODEL_ANALYSIS", "gpt-4o-mini")  # 例
MAX_TOKENS = int(os.getenv("OPENAI_ANALYSIS_MAX_TOKENS", "1200"))

SYSTEM_PROMPT = """あなたはデータアナリストです。与えられたSQL結果の要約統計と小さなサンプルから、
箇条書きの洞察・異常・改善提案・次の質問を JSON で返します。
禁止：根拠の無い推測・個人情報の露出・破壊的なSQL（INSERT/UPDATE/DELETE等）・複文。
許可：改善用の追加SELECT、インデックス提案、期間や粒度の見直し提案。"""

def build_user_payload(sql: str, row_count: int, sample_rows: List[Dict[str, Any]],
                       profiles: Dict[str, Any], display: str|None, mode: Literal["quick","deep"]) -> Dict[str, Any]:
    return {
        "sql": sql,
        "row_count": row_count,
        "display_hint": display,
        "mode": mode,
        "sample_rows": sample_rows,
        "profiles": profiles,
        "guardrails": ["single-statement","select-only","no-ddl-dml","no-pii"],
        "output_schema": {
            "summary": "string",
            "key_findings": ["string"],
            "anomalies": [{"column":"string","type":"string","detail":"string"}],
            "suggestions": [{"title":"string","impact":"low|medium|high","effort":"low|medium|high","action_sql":"string?"}],
            "sql_improvements": [{"reason":"string","revised_sql":"string"}],
            "next_questions": ["string"],
            "confidence": "0..1"
        }
    }

def analyze(sql: str, row_count: int, sample_rows: List[Dict[str, Any]],
            profiles: Dict[str, Any], display: str|None, mode: Literal["quick","deep"]="quick") -> Dict[str, Any]:
    from openai import OpenAI
    client = OpenAI(api_key=os.environ["OPENAI_API_KEY"])
    payload = build_user_payload(sql, row_count, sample_rows, profiles, display, mode)
    # 構造化JSONで返す（自由記述はしない）
    resp = client.chat.completions.create(
        model=Model,
        temperature=0.2,
        response_format={"type": "json_object"},
        messages=[
            {"role":"system","content": SYSTEM_PROMPT},
            {"role":"user","content": json.dumps(payload, ensure_ascii=False)}
        ],
        max_tokens=MAX_TOKENS,
    )
    out = json.loads(resp.choices[0].message.content)
    # サニタイズ（想定外キー除去、長文カットなど）
    return out

※ 実コードでは 全行や生PIIは送らない・単一SELECTしか提案しないようにチェックを入れてください。

4) `nlq_execute.py` 側：呼び出しポイント

DB実行後、if req.analyze_mode != "off":
- プロファイル & サンプル取得
- analysis.analyze(...) 呼び
- stats.analysis に格納
output_level が sql_only の場合は analysisは省略。

改善プランの中身（ガイド）

AIには、人がすぐ動ける提案に寄せてもらいます：

クエリ改善：
- 不要列の削減、期間の絞り、date_trunc / filter → group by の順序整理
- 安全なSELECTのみに限定（サーバ側でも強制）
性能/運用：
- WHEREやJOINに出る列の インデックス案、期間パーティション案
- サンプルが偏っている場合の 粒度/集計の見直し
品質チェック：
- NULL率/レンジ外/カテゴリ歪みの指摘
- 疑似重複キー検知（複合キー候補）
次アクション：
- 「この分解能で週次→日次に落とすと良い？」
- 「このトップカテゴリの内訳を更に分解」等
- Follow-up SELECT を suggestions[].action_sql に入れる

安全・コスト・速度の要点

トークン制御：サンプル200行上限、列プロファイルは統計のみ。quick は100–200ms級で返す設計に。
PII対策：メール/電話/住所/個人名っぽい文字列は *** に置換してから送る。
チェーン・オブ・ソート非開示：返すのは構造化サマリのみ。思考過程は返さない。
レート制御：analyze_mode="deep" はユーザー/分のレートを絞る。

導入ステップ（やる順）

OpenAPI差分を反映（analyze_mode / AIAnalysis）。
services/analysis.py を追加。
nlq_execute.py に前処理＋analysis.analyze 呼び出しを追加。
UI：チャットの「実行」後、テーブルは消してanalysis.summary と チャートURL だけを会話に表示。
- 詳細は折りたたみ（key_findings / suggestions / next_questions）。

この形なら、“自然文 → SQL → 実行 → AIの洞察と改善プラン → 図表URL” が ひと息で回ります。必要なら、プロファイルSQLのサンプル（Postgres用）もすぐ出します。

接続の全体像（チャットUI → API）