flowchart TD
  subgraph EXTRACT["抽出 (PostgreSQL)"]
    A1["① 多言語JSONB抽出SQL
        • ir_ui_view.name
        • ir_ui_menu.name
        • portal_menu.label_i18n
        → (source,object,field,lang,text,meta)"] 
    A2["② 学習コーパス抽出SQL
        • portal_view(+*_settings)
        → (doc_id, locale, content_text, metadata)"]
  end

  A1 --> B["言語フラット化 & 欠損言語検出
             （例：ja_JPが無い行を抽出）"]
  B  --> C["機械翻訳（MT API）
             ・text → target_langへ翻訳
             ・原文言語(meta.lang)保持"]
  C  --> D["正規化/クリーニング
             ・空白/HTML除去
             ・言語タグ付与（lang=ja_JP 等）"]
  A2 --> E["テキスト整形（任意）
             ・長すぎる場合の分割
             ・不要語除去"]

  D --> F["多言語ドキュメント化
           • doc_id: i18n:{source}:{object_id}:{field}:{lang}
           • text: 翻訳済み or 原文
           • metadata: meta + {lang, source, field}"]
  E --> G["学習ドキュメント化
           • doc_id: pv:{view_id}
           • text: content_text
           • metadata: 各種設定JSON"]

  subgraph EMBED["埋め込み & ベクタ格納"]
    F --> H["Embedder（多言語対応モデル）で埋め込み"]
    G --> I["Embedder（同上）で埋め込み"]
    H --> J["ChromaDB upsert (collection='view_i18n')"]
    I --> K["ChromaDB upsert (collection='view_meta')"]
  end

  subgraph RAG["検索/利用"]
    L["ユーザー質問（任意の言語）"]
    M["ChromaDB 検索（Top-k）"]
    N["正しいSQLの提示"]
  end

  L --> M
  J --> M
  K --> M
  M --> N

。**「多言語（JSONB）→ 自動翻訳 → ChromaDB 取り込み」と、「AI学習用（ビュー説明＋カスタマイズ要旨）→ ChromaDB」**の2本立てで、抽出SQLとフロー図（Mermaid）を用意しました。

---

抽出SQL

1) 多言語フィールド（JSONB）をフラット化して取り出す

（ir_ui_view.name / ir_ui_menu.name / portal_menu.label_i18n。JSONでない環境でも動く防御付き）

-- 多言語テキストを (source, object_type, object_id, field, lang, text, meta_json) に正規化
WITH view_name AS (
  SELECT
    'ir_ui_view'::text          AS source,
    'view'::text                AS object_type,
    v.id                        AS object_id,
    'name'::text                AS field,
    kv.key                      AS lang,
    kv.value                    AS text,
    jsonb_build_object(
      'model', v.model,
      'view_type', v.type,
      'priority', v.priority,
      'xmlid', CASE WHEN imd.id IS NOT NULL THEN imd.module||'.'||imd.name ELSE NULL END
    )                           AS meta
  FROM ir_ui_view v
  LEFT JOIN ir_model_data imd
    ON imd.model='ir.ui.view' AND imd.res_id=v.id
  CROSS JOIN LATERAL (
    SELECT * FROM jsonb_each_text(
      CASE
        WHEN v.name IS NULL THEN jsonb_build_object('und','')
        WHEN v.name::text LIKE '{%' THEN v.name::jsonb
        ELSE jsonb_build_object('und', v.name::text)
      END
    )
  ) AS kv
),
menu_name AS (
  SELECT
    'ir_ui_menu'::text          AS source,
    'menu'::text                AS object_type,
    m.id                        AS object_id,
    'name'::text                AS field,
    kv.key                      AS lang,
    kv.value                    AS text,
    jsonb_build_object(
      'action', m.action,
      'parent_id', m.parent_id,
      'sequence', m.sequence
    )                           AS meta
  FROM ir_ui_menu m
  CROSS JOIN LATERAL (
    SELECT * FROM jsonb_each_text(
      CASE
        WHEN m.name IS NULL THEN jsonb_build_object('und','')
        WHEN m.name::text LIKE '{%' THEN m.name::jsonb
        ELSE jsonb_build_object('und', m.name::text)
      END
    )
  ) AS kv
),
portal_menu_i18n AS (
  SELECT
    'portal_menu'::text         AS source,
    'portal_menu'::text         AS object_type,
    pm.view_id                  AS object_id,
    'label_i18n'::text          AS field,
    kv.key                      AS lang,
    kv.value                    AS text,
    jsonb_build_object(
      'parent_xmlid', pm.parent_xmlid,
      'sequence', pm.sequence
    )                           AS meta
  FROM portal_menu pm
  CROSS JOIN LATERAL jsonb_each_text(pm.label_i18n) kv
)
SELECT * FROM view_name
UNION ALL
SELECT * FROM menu_name
UNION ALL
SELECT * FROM portal_menu_i18n
ORDER BY object_type, object_id, field, lang;

これで 言語別の原文が1行ずつ取れます（lang='ja_JP' / 'en_US' / 'und' 等）。
アプリ側で「欠けているターゲット言語（例：ja_JP）」だけを自動翻訳に回せます。

---

2) AI学習用コーパス（ビュー説明＋カスタマイズ要旨）

（portal_view 系から 説明/設定を文章化→ChromaDB用の「text＋metadata」に整形）

-- 1行 = 1ビューの学習テキスト
WITH base AS (
  SELECT
    pv.id                                  AS view_id,
    pv.model,
    pv.view_type,
    pv.view_name,
    pv.origin,               -- 'standard'/'module'/'studio'/'portal'
    pv.priority_num,
    pv.ai_purpose,
    pvc.display_fields,
    pvc.sort_field,
    pvc.sort_dir,
    pk.quick_create,
    pk.group_create,
    pk.group_by_field,
    pl.inline_edit,
    pc.date_start_field,
    pc.date_stop_field,
    pc.color_field
  FROM portal_view pv
  LEFT JOIN portal_view_common   pvc ON pvc.view_id = pv.id
  LEFT JOIN portal_list_settings pl  ON pl.view_id  = pv.id
  LEFT JOIN portal_kanban_settings pk ON pk.view_id = pv.id
  LEFT JOIN portal_calendar_settings pc ON pc.view_id = pv.id
  -- 学習対象を絞るなら WHERE pv.origin IN ('portal','studio') 等を付与
),
txt AS (
  SELECT
    view_id,
    /* 人が読める1本の説明テキストに整形（アプリ側でさらに整える前提の素朴版） */
    CONCAT_WS(' ',
      FORMAT('Model=%s, ViewType=%s, Name=%s.', model, view_type, COALESCE(view_name,'')),
      CASE WHEN ai_purpose IS NOT NULL AND ai_purpose<>'' THEN FORMAT('Purpose: %s.', ai_purpose) END,
      CASE WHEN display_fields IS NOT NULL THEN FORMAT('Fields: %s.', display_fields::text) END,
      CASE WHEN sort_field IS NOT NULL THEN FORMAT('Default order: %s %s.', sort_field, COALESCE(sort_dir::text,'desc')) END,
      CASE WHEN inline_edit IS TRUE THEN 'List inline_edit: true.' END,
      CASE WHEN quick_create IS TRUE THEN 'Kanban quick_create: true.' END,
      CASE WHEN group_create IS TRUE THEN 'Kanban group_create: true.' END,
      CASE WHEN group_by_field IS NOT NULL THEN FORMAT('Kanban group_by: %s.', group_by_field) END,
      CASE WHEN date_start_field IS NOT NULL THEN FORMAT('Calendar: start=%s stop=%s color=%s.',
           date_start_field, COALESCE(date_stop_field,'NULL'), COALESCE(color_field,'NULL')) END,
      FORMAT('Origin=%s Priority=%s.', origin, priority_num)
    ) AS content_text,
    jsonb_build_object(
      'model', model,
      'view_type', view_type,
      'view_name', view_name,
      'origin', origin,
      'priority', priority_num,
      'display_fields', display_fields,
      'sort_field', sort_field,
      'sort_dir', sort_dir,
      'inline_edit', inline_edit,
      'quick_create', quick_create,
      'group_create', group_create,
      'group_by_field', group_by_field,
      'date_start_field', date_start_field,
      'date_stop_field', date_stop_field,
      'color_field', color_field
    ) AS meta
  FROM base
)
SELECT
  CONCAT('pv:', view_id) AS doc_id,
  'neutral'              AS locale,         -- 固定でもOK。必要なら ja_JP/en_US を増やす
  content_text,
  meta
FROM txt
WHERE content_text IS NOT NULL
ORDER BY doc_id;

学習対象は要件どおり「多言語対応部分（上の①で展開）」「ビュー説明（ai_purpose）」「カスタマイズ（originがportal or studioの設定差分）」を含みます。
②のSQLはそのまま ChromaDB の documents（=content_text）＋metadatas に投入できます。