d5c0feab5e
All checks were successful
CD Pipeline / deploy (push) Successful in 1m35s
## Telegram Bot 功能修復 - 補全 16 個 await: 按鈕的 handler（日期選擇/目標設定/促銷追蹤等）， 新增 _handle_await_callback + _process_await_input 完整狀態機 - cmd: 按鈕加入 ⏳ 即時回饋 + try/except 防 BadRequest - handle_callback 加頂層 try/except 錯誤兜底 - 補 momo:cmd:suggestion + momo:menu:main callback handler - 修復 _enhanced_keyword_matching context NameError ## AI 模型遷移（hermes3@111 → qwen2.5@188） - hermes_analyst_service: URL 192.168.0.111→188, hermes3→qwen2.5:7b-instruct - code_review_pipeline: 改用 HERMES_URL/HERMES_MODEL 常數 - elephant_alpha_orchestrator / nemoton_dispatcher: registry/footprint 同步 - aider_heal_executor: OLLAMA_API_BASE fallback 改 188 - ai_routes: footprint display 字串改 qwen2.5:7b-instruct ## ElephantAlpha 404 修復 - elephant_service: openrouter→NVIDIA NIM, nvidia/llama-3.1-nemotron-ultra-253b-v1 - ai_provider: 模型 ID 同步更新 ## TELEGRAM_CHAT_ID 環境變數修正 - cicd_routes + aider_heal_executor: 優先讀 TELEGRAM_CHAT_IDS[0]， fallback TELEGRAM_CHAT_ID，修復通知靜默失敗 ## AI 對話 logging 改善 - telegram_ai_integration: Hermes 降級改 WARNING，OpenClaw 失敗加 exc_info - hermes_analyst_service: 連線失敗 log 加 host/model context ## DB Schema 修復 - migrations/019: action_plans 補齊全欄位，DROP NOT NULL action_type - autoheal_models: ActionPlan ORM 同步為超集 schema Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
200 lines
7.6 KiB
SQL
200 lines
7.6 KiB
SQL
-- Migration 019: Fix action_plans schema — add ALL columns needed by all callers
|
||
-- 建立日期：2026-04-25
|
||
-- 問題根源：
|
||
-- (A) code_review_pipeline_service.py / openclaw_strategist_service.py 寫入
|
||
-- action_type / description / priority / metadata_json（來自 01-init.sql 定義）
|
||
-- (B) watcher_agent.py / ai_orchestrator.py 寫入
|
||
-- session_id / plan_type / sku / payload / created_by / approved_by（來自 migration 017）
|
||
-- 兩組 callers 使用不同 schema，造成寫入失敗。
|
||
-- 解決方案：讓 action_plans 成為兩組欄位的超集（superset）。
|
||
-- 修復方式：逐欄 ADD COLUMN IF NOT EXISTS（冪等，不鎖表）
|
||
-- 回滾路徑（若需還原）：
|
||
-- ALTER TABLE action_plans DROP COLUMN IF EXISTS action_type;
|
||
-- ALTER TABLE action_plans DROP COLUMN IF EXISTS description;
|
||
-- ALTER TABLE action_plans DROP COLUMN IF EXISTS priority;
|
||
-- ALTER TABLE action_plans DROP COLUMN IF EXISTS metadata_json;
|
||
-- ALTER TABLE action_plans DROP COLUMN IF EXISTS session_id;
|
||
-- ALTER TABLE action_plans DROP COLUMN IF EXISTS plan_type;
|
||
-- ALTER TABLE action_plans DROP COLUMN IF EXISTS sku;
|
||
-- ALTER TABLE action_plans DROP COLUMN IF EXISTS payload;
|
||
-- ALTER TABLE action_plans DROP COLUMN IF EXISTS created_by;
|
||
-- ALTER TABLE action_plans DROP COLUMN IF EXISTS approved_by;
|
||
-- ALTER TABLE action_plans DROP COLUMN IF EXISTS executed_at;
|
||
|
||
-- ── Group A: 01-init.sql / CodeReview / OpenClaw ──────────────────────────────
|
||
|
||
-- action_type — 'code_review_fix' | 'openclaw_recommendation'
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'action_plans' AND column_name = 'action_type'
|
||
) THEN
|
||
ALTER TABLE action_plans ADD COLUMN action_type VARCHAR(100);
|
||
RAISE NOTICE 'action_plans.action_type column added';
|
||
ELSE
|
||
RAISE NOTICE 'action_plans.action_type already exists, skipped';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- description — 人類可讀行動說明
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'action_plans' AND column_name = 'description'
|
||
) THEN
|
||
ALTER TABLE action_plans ADD COLUMN description TEXT;
|
||
RAISE NOTICE 'action_plans.description column added';
|
||
ELSE
|
||
RAISE NOTICE 'action_plans.description already exists, skipped';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- priority — 1=critical 2=high 3=medium 4=low
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'action_plans' AND column_name = 'priority'
|
||
) THEN
|
||
ALTER TABLE action_plans ADD COLUMN priority INTEGER DEFAULT 3;
|
||
RAISE NOTICE 'action_plans.priority column added';
|
||
ELSE
|
||
RAISE NOTICE 'action_plans.priority already exists, skipped';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- metadata_json — pipeline_id / commit_sha / findings JSON 擴展
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'action_plans' AND column_name = 'metadata_json'
|
||
) THEN
|
||
ALTER TABLE action_plans ADD COLUMN metadata_json TEXT;
|
||
RAISE NOTICE 'action_plans.metadata_json column added';
|
||
ELSE
|
||
RAISE NOTICE 'action_plans.metadata_json already exists, skipped';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- ── Group B: Migration 017 / watcher_agent / ai_orchestrator ─────────────────
|
||
|
||
-- session_id — Agent session identifier
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'action_plans' AND column_name = 'session_id'
|
||
) THEN
|
||
ALTER TABLE action_plans ADD COLUMN session_id VARCHAR(64);
|
||
RAISE NOTICE 'action_plans.session_id column added';
|
||
ELSE
|
||
RAISE NOTICE 'action_plans.session_id already exists, skipped';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- plan_type — 'price_adjust' | 'restock' | 'campaign'
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'action_plans' AND column_name = 'plan_type'
|
||
) THEN
|
||
ALTER TABLE action_plans ADD COLUMN plan_type VARCHAR(50);
|
||
RAISE NOTICE 'action_plans.plan_type column added';
|
||
ELSE
|
||
RAISE NOTICE 'action_plans.plan_type already exists, skipped';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- sku — 商品 SKU
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'action_plans' AND column_name = 'sku'
|
||
) THEN
|
||
ALTER TABLE action_plans ADD COLUMN sku VARCHAR(100);
|
||
RAISE NOTICE 'action_plans.sku column added';
|
||
ELSE
|
||
RAISE NOTICE 'action_plans.sku already exists, skipped';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- payload — JSON 行動內容（NemoTron 輸出）
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'action_plans' AND column_name = 'payload'
|
||
) THEN
|
||
ALTER TABLE action_plans ADD COLUMN payload TEXT;
|
||
RAISE NOTICE 'action_plans.payload column added';
|
||
ELSE
|
||
RAISE NOTICE 'action_plans.payload already exists, skipped';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- created_by — 'nemotron' | 'openclaw' | 'code_review_pipeline'
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'action_plans' AND column_name = 'created_by'
|
||
) THEN
|
||
ALTER TABLE action_plans ADD COLUMN created_by VARCHAR(50);
|
||
RAISE NOTICE 'action_plans.created_by column added';
|
||
ELSE
|
||
RAISE NOTICE 'action_plans.created_by already exists, skipped';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- approved_by — Telegram user_id
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'action_plans' AND column_name = 'approved_by'
|
||
) THEN
|
||
ALTER TABLE action_plans ADD COLUMN approved_by VARCHAR(100);
|
||
RAISE NOTICE 'action_plans.approved_by column added';
|
||
ELSE
|
||
RAISE NOTICE 'action_plans.approved_by already exists, skipped';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- executed_at — 執行時間戳
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'action_plans' AND column_name = 'executed_at'
|
||
) THEN
|
||
ALTER TABLE action_plans ADD COLUMN executed_at TIMESTAMP;
|
||
RAISE NOTICE 'action_plans.executed_at column added';
|
||
ELSE
|
||
RAISE NOTICE 'action_plans.executed_at already exists, skipped';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- ── 索引補充 ──────────────────────────────────────────────────────────────────
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_action_plans_type ON action_plans(action_type);
|
||
CREATE INDEX IF NOT EXISTS idx_action_plan_sku_status ON action_plans(sku, status);
|
||
CREATE INDEX IF NOT EXISTS idx_action_plan_created ON action_plans(created_at);
|
||
|
||
-- ── 修正 action_type NOT NULL 殘留問題 ──────────────────────────────────────
|
||
-- 根因：action_type 在 01-init.sql 原始定義中為 NOT NULL，
|
||
-- migration 019 的 IF NOT EXISTS 跳過了該欄位，NOT NULL 約束未被修正。
|
||
-- watcher_agent.py / ai_orchestrator.py 插入時不填 action_type → 違反約束。
|
||
-- 修正：DROP NOT NULL + 設定 DEFAULT 'auto'（冪等，不鎖表）
|
||
ALTER TABLE action_plans ALTER COLUMN action_type DROP NOT NULL;
|
||
ALTER TABLE action_plans ALTER COLUMN action_type SET DEFAULT 'auto';
|
||
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE 'Migration 019 done: action_plans unified schema (Group A + Group B columns) + action_type NOT NULL fix';
|
||
END $$;
|