一次開発は1テーブル集中管理でいき、二次開発で実装判定に拡張しやすい形にしておきましょう。
下に「①テーブル設計」「②サンプル投入」「③将来の差分確認用ビュー（簡易版）」をまとめました。すべてPostgreSQL想定です。

---

① 開発ポータル：フィールド情報 1テーブル設計

目的

• 画面項目＝1行で管理（モデル横断で集約）。
• 将来、ir_*メタや実テーブルと照合しやすい列を持たせる。
• 多言語UIテキストはJSONBで一括保持（ポータル表示向け）。

CREATE TABLE portal_fields (
  id               BIGSERIAL PRIMARY KEY,

  -- モデル識別
  model            TEXT NOT NULL,              -- 例: 'res.partner'
  model_table      TEXT NOT NULL,              -- 例: 'res_partner'（照合を簡単にするため持っておく）

  -- フィールド識別
  field_name       TEXT NOT NULL,              -- 例: 'x_customer_rank'
  ttype            TEXT NOT NULL,              -- 例: 'char','text','html','integer','float','boolean','selection','many2one','one2many','many2many','date','datetime','monetary','binary'
  relation_model   TEXT,                       -- many2one/m2m/o2m の相手モデル（不要ならNULL）

  -- 多言語UI（ポータル表示用）
  label_i18n       JSONB,                      -- {"ja_JP":"顧客ランク","en_US":"Customer Rank"}
  help_i18n        JSONB,                      -- {"ja_JP":"ランクに応じて自動割引...","en_US":"Auto discount..."}
  placeholder_i18n JSONB,                      -- {"ja_JP":"ランクを選択してください", ...}
  unit_i18n        JSONB,                      -- {"ja_JP":"kg","en_US":"kg"}

  -- 値の仕様
  translate        BOOLEAN NOT NULL DEFAULT FALSE,  -- レコード値が多言語（Odoo側はJSONBになる想定）
  selection_items  JSONB,                      -- selectionの場合: [{"key":"bronze","label_i18n":{"ja_JP":"ブロンズ","en_US":"Bronze"}}, ...]
  default_value    JSONB,                      -- 例: "bronze" / {"ja_JP":"ブロンズ","en_US":"Bronze"} など型に応じて

  -- UI/制御（ポータル入力→将来ビュー生成のヒント）
  widget           TEXT,                       -- 例: 'radio','many2one','html'
  ui_control       JSONB,                      -- {"required":true, "required_domain":[["state","=","draft"]], "invisible":false, "readonly":false, "layout":{"horizontal":true}}

  -- 権限
  groups_xml_ids   TEXT[] ,                    -- 例: ARRAY['base.group_sale_manager','sales_team.group_sale_manager']

  -- 運用メタ
  origin           TEXT NOT NULL DEFAULT 'portal',  -- 'studio' | 'code' | 'portal'
  code_status      TEXT NOT NULL DEFAULT 'planned', -- 'generated' | 'needs_manual' | 'not_implemented' | 'planned'

  notes            TEXT,

  -- 監査
  created_at       TIMESTAMP NOT NULL DEFAULT now(),
  updated_at       TIMESTAMP NOT NULL DEFAULT now(),

  -- 一意＆妥当性
  CONSTRAINT uq_portal_fields UNIQUE (model, field_name),
  CONSTRAINT ck_ttype CHECK (ttype IN ('char','text','html','integer','float','boolean','selection','many2one','one2many','many2many','date','datetime','monetary','binary')),
  CONSTRAINT ck_relation_required CHECK (
    (ttype IN ('many2one','one2many','many2many') AND relation_model IS NOT NULL)
    OR (ttype NOT IN ('many2one','one2many','many2many'))
  )
);

-- よく使う項目に索引
CREATE INDEX idx_portal_fields_model        ON portal_fields (model);
CREATE INDEX idx_portal_fields_status       ON portal_fields (code_status);
CREATE INDEX idx_portal_fields_groups_gin   ON portal_fields USING GIN (groups_xml_ids);
CREATE INDEX idx_portal_fields_label_gin    ON portal_fields USING GIN (label_i18n);
CREATE INDEX idx_portal_fields_ui_gin       ON portal_fields USING GIN (ui_control);

画面項目 ↔ テーブル列の対応（要点）

• 基本情報：内部名＝field_name／ラベル＝label_i18n／型＝ttype／Widget＝widget／デフォルト＝default_value／プレイスホルダ＝placeholder_i18n／単位＝unit_i18n／ヘルプ＝help_i18n
• 表示制御：ui_control（required/required_domain/invisible/readonly/layout.horizontal）
• 権限：groups_xml_ids
• 多言語（値）：translate = true（Odoo側でJSONB列になる想定）
• selection候補：selection_items（キーと多言語ラベルをJSONBで保持）

---

② サンプル投入（ご指定の項目を反映）

例：res.partner に x_customer_rank を追加する仕様

• 型：テキスト（※ラジオ表示にしたいなら実装時は selection が自然）
• Widget：ラジオボタン
• デフォルト：bronze
• プレイスホルダ：ランクを選択してください
• 単位：kg（UI付記）
• ヘルプ：ランクに応じて自動割引が適用されます
• 表示制御：必須（条件 state='draft'）、水平配置
• 権限：Sales Manager のみ

INSERT INTO portal_fields (
  model, model_table, field_name, ttype, relation_model,
  label_i18n, help_i18n, placeholder_i18n, unit_i18n,
  translate, selection_items, default_value,
  widget, ui_control, groups_xml_ids,
  origin, code_status, notes
) VALUES (
  'res.partner', 'res_partner', 'x_customer_rank', 'char', NULL,
  '{"ja_JP":"顧客ランク","en_US":"Customer Rank"}',
  '{"ja_JP":"ランクに応じて自動割引が適用されます","en_US":"Auto discount will be applied based on rank."}',
  '{"ja_JP":"ランクを選択してください","en_US":"Please choose a rank"}',
  '{"ja_JP":"kg","en_US":"kg"}',
  TRUE,
  -- テキスト型＋ラジオ想定のため、候補は参考に保持（selectionに切替え時に利用）
  '[{"key":"bronze","label_i18n":{"ja_JP":"ブロンズ","en_US":"Bronze"}},
    {"key":"silver","label_i18n":{"ja_JP":"シルバー","en_US":"Silver"}},
    {"key":"gold","label_i18n":{"ja_JP":"ゴールド","en_US":"Gold"}}]',
  '"bronze"',
  'radio',
  '{"required": true,
    "required_domain": [["state","=","draft"]],
    "invisible": false,
    "readonly": false,
    "layout": {"horizontal": true}}',
  ARRAY['base.group_sale_manager'],
  'portal', 'planned', '初期仕様：将来selection型へリファクタ候補'
);

実装メモ

• ラジオ表示を厳密に行くなら、実装段階では ttype='selection' にし、DB値はキーを保存、ラベルはコード翻訳で表示が自然です。
• ただし一次はポータルの定義集約が目的なので、上のように候補を selection_items に保持しておけば、二次開発で selection 化する際の素材になります。

---

③ 将来の差分確認向け：簡易ビュー（参考）

一次では「保存のみ」。ただし**“取っておくと差分しやすい列”**を使って、軽い照合をすぐ試せる簡易ビューも置いておけます。
※本格的な実装判定は二次開発で。

CREATE OR REPLACE VIEW portal_field_diff AS
WITH ir_selection AS (
  SELECT
    f.id AS field_id,
    array_agg((s.value)::text ORDER BY s.value) AS ir_keys  -- ← text[] に統一
  FROM ir_model_fields f
  JOIN ir_model m ON f.model_id = m.id
  LEFT JOIN ir_model_fields_selection s ON s.field_id = f.id
  GROUP BY f.id
)
SELECT
  p.id,
  p.model,
  p.model_table,
  p.field_name,

  -- ir_model / ir_model_fields 存在確認
  (im.id IS NOT NULL)               AS ir_model_exists,
  (imf.id IS NOT NULL)              AS ir_field_exists,

  -- 型・関連先・翻訳フラグの比較
  p.ttype        AS spec_ttype,
  imf.ttype      AS ir_ttype,
  (imf.ttype = p.ttype)             AS ttype_match,

  p.relation_model AS spec_relation,
  imf.relation     AS ir_relation,
  (COALESCE(imf.relation,'') = COALESCE(p.relation_model,'')) AS relation_match,

  p.translate    AS spec_translate,
  imf.translate  AS ir_translate,
  (COALESCE(imf.translate,false) = p.translate) AS translate_match,

  -- 物理列の型（多言語→jsonb想定）
  isc.data_type  AS physical_type,
  CASE
    WHEN p.translate THEN (isc.data_type = 'jsonb')
    ELSE (isc.data_type <> 'jsonb')
  END AS jsonb_ok,

  -- selection候補の簡易比較（NULLは空配列扱いにする）
  (
    COALESCE(
      (SELECT array_agg((elem->>'key')::text ORDER BY elem->>'key')
         FROM jsonb_array_elements(p.selection_items) elem),
      ARRAY[]::text[]
    )
    IS NOT DISTINCT FROM
    COALESCE(sel.ir_keys::text[], ARRAY[]::text[])
  ) AS selection_keys_match

FROM portal_fields p
LEFT JOIN ir_model im
       ON im.model = p.model
LEFT JOIN ir_model_fields imf
       ON imf.model_id = im.id AND imf.name = p.field_name
LEFT JOIN information_schema.columns isc
       ON isc.table_name = p.model_table AND isc.column_name = p.field_name
LEFT JOIN ir_selection sel
       ON sel.field_id = imf.id;

使い方（例）

SELECT *
FROM portal_field_diff
WHERE NOT (ir_model_exists AND ir_field_exists AND ttype_match AND relation_match AND translate_match AND jsonb_ok);

• 注意
  • ビュー存在や attrs/groups/widget の一致まではXML解析が必要なので二次開発で対応。
  • selection がコード定義の場合、DBの ir_model_fields_selection に出ないため selection_keys_match は参考扱い。
  • model_table を持っておくことで information_schema 照合が安定します（モデル→テーブル名の解決を省ける）。

---

運用フロー（一次）

1. コンサルが画面で入力 → portal_fields に1行作成
2. 画面一覧は model + field_name で並べ、code_status で進捗を管理
3. （任意）上の portal_field_diff を使い軽い整合性チェックを実行

（再現用）テーブルの新規作成→既存テーブルのメタ情報格納まで

① ir_translation 互換ビュー（最初の1回だけ）

CREATE SCHEMA IF NOT EXISTS portal_stage;

DO $$
BEGIN
  IF to_regclass('public.ir_translation') IS NOT NULL THEN
    EXECUTE $v$
      CREATE OR REPLACE VIEW portal_stage.v_ir_translation AS
      SELECT
        id::int,
        type::text,
        name::text,
        res_id::int,
        lang::text,
        src::text,
        value::text
      FROM ir_translation
    $v$;
  ELSE
    -- ir_translation が無いDB向けの空ビュー
    EXECUTE $v$
      CREATE OR REPLACE VIEW portal_stage.v_ir_translation AS
      SELECT
        NULL::int  AS id,
        NULL::text AS type,
        NULL::text AS name,
        NULL::int  AS res_id,
        NULL::text AS lang,
        NULL::text AS src,
        NULL::text AS value
      WHERE FALSE
    $v$;
  END IF;
END $$;

---

② portal_fields テーブル（json を許容）＋索引

既に作成済みでも、そのまま実行OK（制約を差し替え、足りない索引を作成）。

-- テーブル（未作成なら作る）
CREATE TABLE IF NOT EXISTS portal_fields (
  id               BIGSERIAL PRIMARY KEY,

  -- モデル識別
  model            TEXT NOT NULL,
  model_table      TEXT NOT NULL,

  -- フィールド識別
  field_name       TEXT NOT NULL,
  ttype            TEXT NOT NULL,
  relation_model   TEXT,

  -- 多言語UI
  label_i18n       JSONB,
  help_i18n        JSONB,
  placeholder_i18n JSONB,
  unit_i18n        JSONB,

  -- 値の仕様
  translate        BOOLEAN NOT NULL DEFAULT FALSE,
  selection_items  JSONB,
  default_value    JSONB,

  -- UI/制御
  widget           TEXT,
  ui_control       JSONB,

  -- 権限
  groups_xml_ids   TEXT[],

  -- 運用メタ
  origin           TEXT NOT NULL DEFAULT 'portal',
  code_status      TEXT NOT NULL DEFAULT 'planned',
  notes            TEXT,

  -- 監査
  created_at       TIMESTAMP NOT NULL DEFAULT now(),
  updated_at       TIMESTAMP NOT NULL DEFAULT now(),

  CONSTRAINT uq_portal_fields UNIQUE (model, field_name)
);

-- ★ ck_ttype を json まで許容に差し替え
ALTER TABLE portal_fields
  DROP CONSTRAINT IF EXISTS ck_ttype;

ALTER TABLE portal_fields
  ADD CONSTRAINT ck_ttype CHECK (
    ttype IN (
      'char','text','html','integer','float','boolean','selection',
      'many2one','one2many','many2many','date','datetime','monetary','binary',
      'json'
    )
  );

-- 関連必須チェック（既存ならスキップしてOK）
ALTER TABLE portal_fields
  DROP CONSTRAINT IF EXISTS ck_relation_required;

ALTER TABLE portal_fields
  ADD CONSTRAINT ck_relation_required CHECK (
    (ttype IN ('many2one','one2many','many2many') AND relation_model IS NOT NULL)
    OR (ttype NOT IN ('many2one','one2many','many2many'))
  );

-- 索引（無ければ作成）
CREATE INDEX IF NOT EXISTS idx_portal_fields_model      ON portal_fields (model);
CREATE INDEX IF NOT EXISTS idx_portal_fields_status     ON portal_fields (code_status);
CREATE INDEX IF NOT EXISTS idx_portal_fields_groups_gin ON portal_fields USING GIN (groups_xml_ids);
CREATE INDEX IF NOT EXISTS idx_portal_fields_label_gin  ON portal_fields USING GIN (label_i18n);
CREATE INDEX IF NOT EXISTS idx_portal_fields_ui_gin     ON portal_fields USING GIN (ui_control);

---

③ portal_fields 初期投入（sale.order 向け／全モデルにも対応）

既定は sale.order。全モデルに入れる場合は param.model を NULL に変更してください。

-- ==== パラメータ（このモデルだけ投入。全モデルは NULL）====
WITH param AS (
  SELECT 'sale.order'::text AS model
),

-- 1) 基本フィールド定義（label/help を text に正規化）
base_fields AS (
  SELECT
    mf.id                           AS field_id,
    mf.model                        AS model,
    replace(mf.model, '.', '_')     AS model_table,
    mf.name                         AS field_name,
    mf.ttype                        AS ttype,
    mf.translate::boolean           AS translate,
    mf.relation                     AS relation_model,

    -- field_description: jsonb or text → text
    COALESCE(
      CASE WHEN pg_typeof(mf.field_description)::text = 'jsonb' THEN
        COALESCE(
          mf.field_description->>'ja_JP',
          mf.field_description->>'ja',
          mf.field_description->>'en_US',
          (SELECT value FROM jsonb_each_text(mf.field_description) LIMIT 1)
        )
      END,
      mf.field_description::text
    )                               AS label_base_text,

    -- help: jsonb or text → text
    COALESCE(
      CASE WHEN pg_typeof(mf.help)::text = 'jsonb' THEN
        COALESCE(
          mf.help->>'ja_JP',
          mf.help->>'ja',
          mf.help->>'en_US',
          (SELECT value FROM jsonb_each_text(mf.help) LIMIT 1)
        )
      END,
      mf.help::text
    )                               AS help_base_text

  FROM ir_model_fields mf
  WHERE (SELECT model FROM param) IS NULL OR mf.model = (SELECT model FROM param)
),

-- 2) ラベル/ヘルプの翻訳（ja_JP / en_US）※互換ビュー経由（無ければ空）
i18n_label AS (
  SELECT
    b.field_id,
    MAX(CASE WHEN t.lang = 'ja_JP' THEN NULLIF(t.value,'') END) AS ja,
    MAX(CASE WHEN t.lang = 'en_US' THEN NULLIF(t.value,'') END) AS en
  FROM base_fields b
  LEFT JOIN portal_stage.v_ir_translation t
    ON t.type = 'model'
   AND t.name = 'ir.model.fields,field_description'
   AND t.res_id = b.field_id
  GROUP BY b.field_id
),
i18n_help AS (
  SELECT
    b.field_id,
    MAX(CASE WHEN t.lang = 'ja_JP' THEN NULLIF(t.value,'') END) AS ja,
    MAX(CASE WHEN t.lang = 'en_US' THEN NULLIF(t.value,'') END) AS en
  FROM base_fields b
  LEFT JOIN portal_stage.v_ir_translation t
    ON t.type = 'model'
   AND t.name = 'ir.model.fields,help'
   AND t.res_id = b.field_id
  GROUP BY b.field_id
),

-- 3) ビューXML（モデル単位）を言語別に用意
views AS (
  SELECT
    v.id    AS view_id,
    v.model AS model,
    CASE WHEN pg_typeof(v.arch_db)::text = 'jsonb' THEN v.arch_db->>'ja_JP' ELSE NULL END AS arch_ja,
    CASE WHEN pg_typeof(v.arch_db)::text = 'jsonb' THEN v.arch_db->>'en_US' ELSE NULL END AS arch_en,
    CASE
      WHEN pg_typeof(v.arch_db)::text = 'jsonb' THEN
        COALESCE(
          v.arch_db->>'ja_JP',
          v.arch_db->>'ja',
          v.arch_db->>'en_US',
          (SELECT value FROM jsonb_each_text(v.arch_db) LIMIT 1)
        )
      ELSE v.arch_db::text
    END AS arch_default
  FROM ir_ui_view v
  WHERE v.model IS NOT NULL
    AND ( (SELECT model FROM param) IS NULL OR v.model = (SELECT model FROM param) )
),
xml_views AS (
  SELECT
    view_id,
    model,
    CASE WHEN arch_default IS NOT NULL AND arch_default LIKE '%<%' THEN xmlparse(document regexp_replace(arch_default, '^[\uFEFF\s]*', '')) END AS x_default,
    CASE WHEN arch_ja      IS NOT NULL AND arch_ja      LIKE '%<%' THEN xmlparse(document regexp_replace(arch_ja,      '^[\uFEFF\s]*', '')) END AS x_ja,
    CASE WHEN arch_en      IS NOT NULL AND arch_en      LIKE '%<%' THEN xmlparse(document regexp_replace(arch_en,      '^[\uFEFF\s]*', '')) END AS x_en
  FROM views
),

-- 4) ビュー内の <field> ノードを言語別にフラット化
field_nodes AS (
  SELECT model, 'default'::text AS lang, unnest(xpath('//field[@name]', x_default)) AS n
  FROM xml_views WHERE x_default IS NOT NULL
  UNION ALL
  SELECT model, 'ja_JP', unnest(xpath('//field[@name]', x_ja)) AS n
  FROM xml_views WHERE x_ja IS NOT NULL
  UNION ALL
  SELECT model, 'en_US', unnest(xpath('//field[@name]', x_en)) AS n
  FROM xml_views WHERE x_en IS NOT NULL
),

-- 5) ノードから属性抽出
nodes_attrs AS (
  SELECT
    fn.model,
    fn.lang,
    (xpath('string(./@name)', fn.n))[1]::text AS field_name,
    NULLIF((xpath('string(./@placeholder)', fn.n))[1]::text,'') AS placeholder,
    NULLIF((xpath('string(./@widget)',      fn.n))[1]::text,'') AS widget,
    NULLIF((xpath('string(./@groups)',      fn.n))[1]::text,'') AS groups_raw,
    NULLIF((xpath('string(./@attrs)',       fn.n))[1]::text,'') AS attrs_raw,
    NULLIF((xpath('string(./@options)',     fn.n))[1]::text,'') AS options_raw,
    NULLIF((xpath('string(./@required)',    fn.n))[1]::text,'') IN ('1','true','True') AS required_attr,
    NULLIF((xpath('string(./@readonly)',    fn.n))[1]::text,'') IN ('1','true','True') AS readonly_attr,
    NULLIF((xpath('string(./@invisible)',   fn.n))[1]::text,'') IN ('1','true','True') AS invisible_attr
  FROM field_nodes fn
),

-- 6) 言語別 placeholder/widget/attrs/groups をフィールド単位に集約
agg_view_meta AS (
  SELECT
    b.field_id,
    b.model,
    b.field_name,
    MAX(CASE WHEN na.lang = 'ja_JP' THEN na.placeholder END) AS placeholder_ja,
    MAX(CASE WHEN na.lang = 'en_US' THEN na.placeholder END) AS placeholder_en,
    (ARRAY_REMOVE(ARRAY_AGG(NULLIF(na.widget,'')), NULL))[1] AS widget_any,
    -- groups を結合→分割→重複除去
    ARRAY(
      SELECT DISTINCT g
      FROM unnest(
             regexp_split_to_array(
               COALESCE(string_agg(na.groups_raw, ','), ''),
               '\s*,\s*'
             )
           ) AS g
      WHERE g <> ''
    ) AS groups_xml_ids,
    (ARRAY_REMOVE(ARRAY_AGG(NULLIF(na.attrs_raw,'')),   NULL))[1] AS attrs_any,
    (ARRAY_REMOVE(ARRAY_AGG(NULLIF(na.options_raw,'')), NULL))[1] AS options_any,
    BOOL_OR(na.required_attr)  AS required_attr_any,
    BOOL_OR(na.readonly_attr)  AS readonly_attr_any,
    BOOL_OR(na.invisible_attr) AS invisible_attr_any
  FROM base_fields b
  LEFT JOIN nodes_attrs na
    ON na.model = b.model
   AND na.field_name = b.field_name
  GROUP BY b.field_id, b.model, b.field_name
),

-- 7) attrs から domain を抽出（文字列のまま保持）
attrs_domains AS (
  SELECT
    a.field_id,
    CASE
      WHEN a.attrs_any ~* '(?is)\brequired\s*:\s*\['
      THEN regexp_replace(a.attrs_any, '(?is).*?\brequired\s*:\s*(\[[^\]]*\]).*', '\1')
    END AS required_domain_raw,
    CASE
      WHEN a.attrs_any ~* '(?is)\breadonly\s*:\s*\['
      THEN regexp_replace(a.attrs_any, '(?is).*?\breadonly\s*:\s*(\[[^\]]*\]).*', '\1')
    END AS readonly_domain_raw,
    CASE
      WHEN a.attrs_any ~* '(?is)\binvisible\s*:\s*\['
      THEN regexp_replace(a.attrs_any, '(?is).*?\binvisible\s*:\s*(\[[^\]]*\]).*', '\1')
    END AS invisible_domain_raw,
    (a.options_any ~* '(?is)\bhorizontal\b\s*:\s*(1|true|True)') AS layout_horizontal
  FROM agg_view_meta a
),

-- 8) selection 項目（翻訳が無いDBでも base 名で構築）
sel_lines AS (
  SELECT s.id AS sel_id, s.field_id, s.value AS key, s.name AS name_base
  FROM ir_model_fields_selection s
  JOIN base_fields b ON b.field_id = s.field_id
),
selection_items AS (
  SELECT
    sl.field_id,
    jsonb_agg(
      jsonb_build_object(
        'key', sl.key,
        'label_i18n', jsonb_build_object(
          'ja_JP', sl.name_base,
          'en_US', sl.name_base
        )
      )
      ORDER BY sl.key
    ) AS items_json
  FROM sel_lines sl
  GROUP BY sl.field_id
),

-- 9) portal_fields 形に整形
rows AS (
  SELECT
    b.model,
    b.model_table,
    b.field_name,
    b.ttype,
    CASE WHEN b.ttype IN ('many2one','one2many','many2many') THEN b.relation_model ELSE NULL END AS relation_model,

    jsonb_build_object(
      'ja_JP', COALESCE(il.ja, b.label_base_text),
      'en_US', COALESCE(il.en, b.label_base_text)
    ) AS label_i18n,
    jsonb_build_object(
      'ja_JP', COALESCE(ih.ja, b.help_base_text),
      'en_US', COALESCE(ih.en, b.help_base_text)
    ) AS help_i18n,
    jsonb_build_object(
      'ja_JP', NULLIF(av.placeholder_ja,''),
      'en_US', NULLIF(av.placeholder_en,'')
    ) AS placeholder_i18n,
    NULL::jsonb AS unit_i18n,

    b.translate,

    si.items_json AS selection_items,
    NULL::jsonb   AS default_value,

    NULLIF(av.widget_any,'') AS widget,

    jsonb_strip_nulls(
      jsonb_build_object(
        'required',  COALESCE(av.required_attr_any,  false),
        'readonly',  COALESCE(av.readonly_attr_any,  false),
        'invisible', COALESCE(av.invisible_attr_any, false),
        'required_domain',  ad.required_domain_raw,
        'readonly_domain',  ad.readonly_domain_raw,
        'invisible_domain', ad.invisible_domain_raw,
        'layout', CASE WHEN ad.layout_horizontal THEN jsonb_build_object('horizontal', true) END
      )
    ) AS ui_control,

    COALESCE(av.groups_xml_ids, ARRAY[]::text[]) AS groups_xml_ids,

    'portal'::text  AS origin,
    'planned'::text AS code_status,
    NULL::text      AS notes
  FROM base_fields b
  LEFT JOIN i18n_label      il ON il.field_id = b.field_id
  LEFT JOIN i18n_help       ih ON ih.field_id = b.field_id
  LEFT JOIN agg_view_meta   av ON av.field_id = b.field_id
  LEFT JOIN attrs_domains   ad ON ad.field_id = b.field_id
  LEFT JOIN selection_items si ON si.field_id = b.field_id
)

-- 10) ★ UPSERT into portal_fields
INSERT INTO portal_fields (
  model, model_table, field_name, ttype, relation_model,
  label_i18n, help_i18n, placeholder_i18n, unit_i18n,
  translate, selection_items, default_value,
  widget, ui_control, groups_xml_ids,
  origin, code_status, notes
)
SELECT
  r.model,
  r.model_table,
  r.field_name,
  r.ttype,
  r.relation_model,
  r.label_i18n,
  r.help_i18n,
  r.placeholder_i18n,
  r.unit_i18n,
  r.translate,
  r.selection_items,
  r.default_value,
  r.widget,
  r.ui_control,
  r.groups_xml_ids,
  r.origin,
  r.code_status,
  r.notes
FROM rows r
ON CONFLICT (model, field_name) DO UPDATE SET
  model_table      = EXCLUDED.model_table,
  ttype            = EXCLUDED.ttype,
  relation_model   = EXCLUDED.relation_model,
  label_i18n       = EXCLUDED.label_i18n,
  help_i18n        = EXCLUDED.help_i18n,
  placeholder_i18n = EXCLUDED.placeholder_i18n,
  unit_i18n        = EXCLUDED.unit_i18n,
  translate        = EXCLUDED.translate,
  selection_items  = EXCLUDED.selection_items,
  default_value    = EXCLUDED.default_value,
  widget           = EXCLUDED.widget,
  ui_control       = EXCLUDED.ui_control,
  groups_xml_ids   = EXCLUDED.groups_xml_ids,
  origin           = EXCLUDED.origin,
  code_status      = EXCLUDED.code_status,
  notes            = EXCLUDED.notes,
  updated_at       = now();

---

メモ

• すでに portal_fields がある場合でも、②で ck_ttype を json まで拡張してから③を流せばOKです。
• 他モデルを入れるときは、③の param.model を該当モデル or NULL に変更。
• さらに型を増やしたい場合（例：reference）は、②の ck_ttype に追記してください。

メタ情報抜き出し

— 対象モデル（NULLで全モデル）
WITH param AS (
SELECT ‘sale.order’::text AS model
),

— 1) ir_model_fields を基礎に、label/help を textへ正規化
base_fields AS (
SELECT
mf.id AS field_id,
mf.model AS model,
replace(mf.model, ‘.’, ‘_’) AS model_table,
mf.name AS field_name,
mf.ttype AS ttype,
mf.translate::boolean AS translate,
mf.relation AS relation_model,

-- field_description: jsonb or text → text
COALESCE(
  CASE WHEN pg_typeof(mf.field_description)::text = 'jsonb' THEN
    COALESCE(
      mf.field_description->>'ja_JP',
      mf.field_description->>'ja',
      mf.field_description->>'en_US',
      (SELECT value FROM jsonb_each_text(mf.field_description) LIMIT 1)
    )
  END,
  mf.field_description::text
) AS label_base_text,

-- help: jsonb or text → text
COALESCE(
  CASE WHEN pg_typeof(mf.help)::text = 'jsonb' THEN
    COALESCE(
      mf.help->>'ja_JP',
      mf.help->>'ja',
      mf.help->>'en_US',
      (SELECT value FROM jsonb_each_text(mf.help) LIMIT 1)
    )
  END,
  mf.help::text
) AS help_base_text

FROM ir_model_fields mf
WHERE (SELECT model FROM param) IS NULL OR mf.model = (SELECT model FROM param)
),

— 2) ビューXML（モデル単位）：arch_db が text/JSONB どちらでも対応
views AS (
SELECT
v.id AS view_id,
v.model AS model,
CASE WHEN pg_typeof(v.arch_db)::text = ‘jsonb’ THEN v.arch_db->>’ja_JP’ ELSE NULL END AS arch_ja,
CASE WHEN pg_typeof(v.arch_db)::text = ‘jsonb’ THEN v.arch_db->>’en_US’ ELSE NULL END AS arch_en,
CASE
WHEN pg_typeof(v.arch_db)::text = ‘jsonb’ THEN
COALESCE(
v.arch_db->>’ja_JP’,
v.arch_db->>’ja’,
v.arch_db->>’en_US’,
(SELECT value FROM jsonb_each_text(v.arch_db) LIMIT 1)
)
ELSE v.arch_db::text
END AS arch_default
FROM ir_ui_view v
WHERE v.model IS NOT NULL
AND ( (SELECT model FROM param) IS NULL OR v.model = (SELECT model FROM param) )
),
xml_views AS (
SELECT
view_id,
model,
CASE WHEN arch_default IS NOT NULL AND arch_default LIKE ‘%<%’ THEN xmlparse(document regexp_replace(arch_default, ‘^[\uFEFF\s]‘, ”)) END AS x_default, CASE WHEN arch_ja IS NOT NULL AND arch_ja LIKE ‘%<%’ THEN xmlparse(document regexp_replace(arch_ja, ‘^[\uFEFF\s]‘, ”)) END AS x_ja,
CASE WHEN arch_en IS NOT NULL AND arch_en LIKE ‘%<%’ THEN xmlparse(document regexp_replace(arch_en, ‘^[\uFEFF\s]*’, ”)) END AS x_en
FROM views
),

— 3) ビュー内 ノードを言語別にフラット化
field_nodes AS (
SELECT model, ‘default’::text AS lang, unnest(xpath(‘//field[@name]’, x_default)) AS n
FROM xml_views WHERE x_default IS NOT NULL
UNION ALL
SELECT model, ‘ja_JP’, unnest(xpath(‘//field[@name]’, x_ja)) AS n
FROM xml_views WHERE x_ja IS NOT NULL
UNION ALL
SELECT model, ‘en_US’, unnest(xpath(‘//field[@name]’, x_en)) AS n
FROM xml_views WHERE x_en IS NOT NULL
),

— 4) ノード属性抽出
nodes_attrs AS (
SELECT
fn.model,
fn.lang,
(xpath(‘string(./@name)’, fn.n))[1]::text AS field_name,
NULLIF((xpath(‘string(./@placeholder)’, fn.n))[1]::text,”) AS placeholder,
NULLIF((xpath(‘string(./@widget)’, fn.n))[1]::text,”) AS widget,
NULLIF((xpath(‘string(./@groups)’, fn.n))[1]::text,”) AS groups_raw,
NULLIF((xpath(‘string(./@attrs)’, fn.n))[1]::text,”) AS attrs_raw,
NULLIF((xpath(‘string(./@options)’, fn.n))[1]::text,”) AS options_raw,
NULLIF((xpath(‘string(./@required)’, fn.n))[1]::text,”) IN (‘1′,’true’,’True’) AS required_attr,
NULLIF((xpath(‘string(./@readonly)’, fn.n))[1]::text,”) IN (‘1′,’true’,’True’) AS readonly_attr,
NULLIF((xpath(‘string(./@invisible)’, fn.n))[1]::text,”) IN (‘1′,’true’,’True’) AS invisible_attr
FROM field_nodes fn
),

— 5) placeholder/widget/groups/attrs をフィールド単位に集約
agg_view_meta AS (
SELECT
b.field_id,
b.model,
b.field_name,
MAX(CASE WHEN na.lang=’ja_JP’ THEN na.placeholder END) AS placeholder_ja,
MAX(CASE WHEN na.lang=’en_US’ THEN na.placeholder END) AS placeholder_en,
(ARRAY_REMOVE(ARRAY_AGG(NULLIF(na.widget,”)), NULL))[1] AS widget_any,
ARRAY(
SELECT DISTINCT g
FROM unnest(
regexp_split_to_array(
COALESCE(string_agg(na.groups_raw, ‘,’), ”),
‘\s,\s‘
)
) AS g
WHERE g <> ”
) AS groups_xml_ids,
(ARRAY_REMOVE(ARRAY_AGG(NULLIF(na.attrs_raw,”)), NULL))[1] AS attrs_any,
(ARRAY_REMOVE(ARRAY_AGG(NULLIF(na.options_raw,”)), NULL))[1] AS options_any,
BOOL_OR(na.required_attr) AS required_attr_any,
BOOL_OR(na.readonly_attr) AS readonly_attr_any,
BOOL_OR(na.invisible_attr) AS invisible_attr_any
FROM base_fields b
LEFT JOIN nodes_attrs na
ON na.model = b.model
AND na.field_name = b.field_name
GROUP BY b.field_id, b.model, b.field_name
),

— 6) attrs から domain をざっくり抽出（文字列のまま保持）
attrs_domains AS (
SELECT
a.field_id,
CASE
WHEN a.attrs_any ~* ‘(?is)\brequired\s:\s[‘
THEN regexp_replace(a.attrs_any, ‘(?is).?\brequired\s:\s([[^]]]).‘, ‘\1’) END AS required_domain_raw, CASE WHEN a.attrs_any ~ ‘(?is)\breadonly\s:\s[‘
THEN regexp_replace(a.attrs_any, ‘(?is).?\breadonly\s:\s([[^]]]).‘, ‘\1’) END AS readonly_domain_raw, CASE WHEN a.attrs_any ~ ‘(?is)\binvisible\s:\s[‘
THEN regexp_replace(a.attrs_any, ‘(?is).?\binvisible\s:\s([[^]]]).‘, ‘\1’) END AS invisible_domain_raw, (a.options_any ~ ‘(?is)\bhorizontal\b\s:\s(1|true|True)’) AS layout_horizontal
FROM agg_view_meta a
),

— 7) selection を i18n っぽく整形（翻訳テーブルが無くても base 名で両言語へ）
sel_lines AS (
SELECT s.id AS sel_id, s.field_id, s.value AS key, s.name AS name_base
FROM ir_model_fields_selection s
JOIN base_fields b ON b.field_id = s.field_id
),
selection_items AS (
SELECT
sl.field_id,
jsonb_agg(
jsonb_build_object(
‘key’, sl.key,
‘label_i18n’, jsonb_build_object(
‘ja_JP’, sl.name_base,
‘en_US’, sl.name_base
)
)
ORDER BY sl.key
) AS items_json
FROM sel_lines sl
GROUP BY sl.field_id
),

— 8) portal_fields 形に整形（SELECTとして返す）
rows AS (
SELECT
b.model,
b.model_table,
b.field_name,

-- ★ 許容型のみ（ck_ttype 準拠）
b.ttype,
CASE WHEN b.ttype IN ('many2one','one2many','many2many') THEN b.relation_model ELSE NULL END AS relation_model,

jsonb_build_object(
  'ja_JP', b.label_base_text,
  'en_US', b.label_base_text
) AS label_i18n,
jsonb_build_object(
  'ja_JP', b.help_base_text,
  'en_US', b.help_base_text
) AS help_i18n,
jsonb_build_object(
  'ja_JP', NULLIF(av.placeholder_ja,''),
  'en_US', NULLIF(av.placeholder_en,'')
) AS placeholder_i18n,

NULL::jsonb AS unit_i18n,    -- 不明なので空

b.translate,

si.items_json AS selection_items,  -- selection 以外は NULL
NULL::jsonb   AS default_value,    -- 不明なので空

NULLIF(av.widget_any,'') AS widget,

jsonb_strip_nulls(
  jsonb_build_object(
    'required',  COALESCE(av.required_attr_any,  false),
    'readonly',  COALESCE(av.readonly_attr_any,  false),
    'invisible', COALESCE(av.invisible_attr_any, false),
    'required_domain',  ad.required_domain_raw,
    'readonly_domain',  ad.readonly_domain_raw,
    'invisible_domain', ad.invisible_domain_raw,
    'layout', CASE WHEN ad.layout_horizontal THEN jsonb_build_object('horizontal', true) END
  )
) AS ui_control,

COALESCE(av.groups_xml_ids, ARRAY[]::text[]) AS groups_xml_ids,

'portal'::text  AS origin,
'planned'::text AS code_status,
NULL::text      AS notes

FROM base_fields b
LEFT JOIN agg_view_meta av ON av.field_id = b.field_id
LEFT JOIN attrs_domains ad ON ad.field_id = b.field_id
LEFT JOIN selection_items si ON si.field_id = b.field_id
)

SELECT *
FROM rows
WHERE
— ★ ck_ttype に合わせた型だけ返す（json 等を除外）
ttype IN (
‘char’,’text’,’html’,’integer’,’float’,’boolean’,’selection’,
‘many2one’,’one2many’,’many2many’,’date’,’datetime’,’monetary’,’binary’
)
— ★ リレーション必須チェック（many系は relation_model 必須）
AND (
(ttype IN (‘many2one’,’one2many’,’many2many’) AND relation_model IS NOT NULL)
OR ttype NOT IN (‘many2one’,’one2many’,’many2many’)
)
ORDER BY model, field_name;