296 lines
12 KiB
Python
296 lines
12 KiB
Python
"""市場情報 migration 套用前演練。
|
||
|
||
本模組只組裝正式 migration 前的檢查、人工步驟與回滾演練，不執行 SQL、
|
||
不建立 ORM session、不寫入資料庫。
|
||
"""
|
||
|
||
|
||
def _schema_state(schema_db_probe):
|
||
mode = schema_db_probe.get("mode")
|
||
if mode == "schema_db_probe_planned":
|
||
return "planned_no_db_probe"
|
||
if mode == "schema_db_probe_error":
|
||
return "probe_error"
|
||
if not schema_db_probe.get("read_only_query_executed"):
|
||
return "planned_no_db_probe"
|
||
if schema_db_probe.get("schema_tables_exist"):
|
||
return "already_applied"
|
||
existing_tables = schema_db_probe.get("existing_tables") or []
|
||
if existing_tables:
|
||
return "partial_schema"
|
||
return "not_applied"
|
||
|
||
|
||
def _check_item(key, label, passed, status_when_blocked="blocked"):
|
||
return {
|
||
"key": key,
|
||
"label": label,
|
||
"passed": bool(passed),
|
||
"status": "pass" if passed else status_when_blocked,
|
||
}
|
||
|
||
|
||
def _manual_step(key, label, status="required"):
|
||
return {
|
||
"key": key,
|
||
"label": label,
|
||
"status": status,
|
||
}
|
||
|
||
|
||
def _build_blocked_reasons(*, checks, schema_state, execute_requested):
|
||
blocked_reasons = [
|
||
"migration_not_executed_by_drill",
|
||
"api_never_runs_migration",
|
||
"backup_not_verified",
|
||
"operator_approval_missing",
|
||
"production_maintenance_window_required",
|
||
]
|
||
blocked_reasons.extend(
|
||
key for key, passed in checks.items()
|
||
if not passed
|
||
)
|
||
if not execute_requested:
|
||
blocked_reasons.append("read_only_db_probe_not_executed")
|
||
if schema_state == "probe_error":
|
||
blocked_reasons.append("schema_probe_error_requires_manual_review")
|
||
if schema_state == "partial_schema":
|
||
blocked_reasons.append("partial_market_schema_requires_manual_review")
|
||
if schema_state == "already_applied":
|
||
blocked_reasons.append("market_schema_already_present_apply_not_required")
|
||
return blocked_reasons
|
||
|
||
|
||
def build_migration_apply_drill_preview(
|
||
*,
|
||
runtime_status,
|
||
migration_blueprint,
|
||
schema_db_probe,
|
||
platform_seed_db_diff,
|
||
):
|
||
"""建立 migration apply drill payload；不執行 migration 或 DB write。"""
|
||
execute_requested = bool(
|
||
schema_db_probe.get("execute_requested")
|
||
or platform_seed_db_diff.get("execute_requested")
|
||
)
|
||
schema_state = _schema_state(schema_db_probe)
|
||
checks = {
|
||
"migration_file_matches_blueprint": bool(
|
||
migration_blueprint.get("file_created")
|
||
and migration_blueprint.get("file_matches_blueprint")
|
||
),
|
||
"forward_sql_additive_only": bool(
|
||
migration_blueprint.get("safety_checks", {}).get("forward_sql_additive_only")
|
||
and not migration_blueprint.get("forward_has_destructive_sql")
|
||
),
|
||
"schema_probe_read_only_or_planned": bool(
|
||
not schema_db_probe.get("database_write_executed")
|
||
and not schema_db_probe.get("database_commit_executed")
|
||
and not schema_db_probe.get("migration_executed")
|
||
and not schema_db_probe.get("database_session_created")
|
||
),
|
||
"seed_diff_read_only_or_planned": bool(
|
||
not platform_seed_db_diff.get("database_write_executed")
|
||
and not platform_seed_db_diff.get("database_commit_executed")
|
||
and not platform_seed_db_diff.get("migration_executed")
|
||
and not platform_seed_db_diff.get("database_session_created")
|
||
and not platform_seed_db_diff.get("seed_write_executed")
|
||
),
|
||
"feature_flags_default_safe": bool(
|
||
not runtime_status.enabled
|
||
and not runtime_status.crawler_enabled
|
||
and not runtime_status.write_enabled
|
||
),
|
||
"database_write_blocked": bool(not runtime_status.database_write_allowed),
|
||
"scheduler_detached": bool(not runtime_status.scheduler_attached),
|
||
"external_fetch_blocked": bool(not runtime_status.crawler_enabled),
|
||
"rollback_manual_only": bool(
|
||
migration_blueprint.get("rollback_requires_manual_approval")
|
||
),
|
||
}
|
||
drill_ready_for_operator_review = all(checks.values())
|
||
ready_for_manual_apply_review = bool(
|
||
drill_ready_for_operator_review
|
||
and execute_requested
|
||
and schema_state in {"not_applied", "partial_schema"}
|
||
)
|
||
ready_to_apply_migration = False
|
||
|
||
pre_apply_checklist = [
|
||
_manual_step(
|
||
"confirm_worktree_and_release_scope",
|
||
"確認本次只包含 market_intel migration / drill 相關變更",
|
||
),
|
||
_manual_step(
|
||
"run_backup_system",
|
||
"套正式 migration 前先完成 python backup_system.py 並確認備份可用",
|
||
),
|
||
_manual_step(
|
||
"run_schema_probe_execute_true",
|
||
"人工呼叫 /api/market_intel/schema_db_probe?execute=true 做只讀 catalog 檢查",
|
||
),
|
||
_manual_step(
|
||
"run_seed_diff_execute_true",
|
||
"人工呼叫 /api/market_intel/platform_seed_db_diff?execute=true 做只讀 seed diff",
|
||
),
|
||
_manual_step(
|
||
"review_forward_sql",
|
||
"確認 forward SQL 只有 CREATE TABLE / CREATE INDEX / GRANT 類 additive 操作",
|
||
),
|
||
_manual_step(
|
||
"open_maintenance_window",
|
||
"確認正式 DB migration 維護窗口與操作員身分",
|
||
),
|
||
_manual_step(
|
||
"apply_psql_manually",
|
||
"由操作員手動執行 migration command；API 不執行 psql",
|
||
),
|
||
_manual_step(
|
||
"post_apply_smoke",
|
||
"套用後驗證 /health、schema_db_probe?execute=true、deployment_readiness",
|
||
),
|
||
]
|
||
post_apply_verification = [
|
||
_manual_step("health_endpoint", "驗證 /health healthy 且版本正確"),
|
||
_manual_step(
|
||
"schema_probe_all_tables",
|
||
"確認 schema_db_probe?execute=true 顯示所有 market_* 表存在",
|
||
),
|
||
_manual_step(
|
||
"seed_writer_cli_status",
|
||
"確認 seed writer CLI status 仍未自動寫入，等待下一次獨立批准",
|
||
),
|
||
_manual_step(
|
||
"flags_still_off",
|
||
"確認 MARKET_INTEL_* flags 仍全關，crawler 與 scheduler 未啟用",
|
||
),
|
||
]
|
||
rollback_drill = {
|
||
"mode": "rollback_drill_preview",
|
||
"rollback_sql_available": bool(migration_blueprint.get("rollback_sql")),
|
||
"rollback_executed": False,
|
||
"database_write_executed": False,
|
||
"database_commit_executed": False,
|
||
"requires_manual_approval": True,
|
||
"requires_backup_before_rollback": True,
|
||
"requires_data_loss_review": True,
|
||
"rollback_table_order": list(reversed(migration_blueprint.get("expected_tables") or [])),
|
||
"manual_command_shape": (
|
||
"將已審核 rollback SQL 寫入臨時檔後，手動執行 "
|
||
"psql \"$DATABASE_URL\" -v ON_ERROR_STOP=1 -f <reviewed_rollback.sql>"
|
||
),
|
||
"fallback_first": [
|
||
"先關閉 MARKET_INTEL_* feature flags",
|
||
"確認 scheduler 未掛載市場情報 job",
|
||
"app-only 回退上一版並只 recreate momo-app",
|
||
"只有在確認 market_* 無需保留資料時才評估 rollback SQL",
|
||
],
|
||
}
|
||
risk_register = [
|
||
{
|
||
"key": "partial_schema",
|
||
"label": "若只建立部分 market_* 表，必須人工比對 migration 檔與 catalog，再決定重跑或補修。",
|
||
"severity": "medium",
|
||
},
|
||
{
|
||
"key": "grant_or_sequence_missing",
|
||
"label": "表存在不代表 GRANT / sequence 權限完整；post-apply smoke 需檢查 seed writer 仍可用。",
|
||
"severity": "medium",
|
||
},
|
||
{
|
||
"key": "rollback_drops_market_data",
|
||
"label": "rollback SQL 會 drop market_* 表，未來有正式資料後不可當一般回退手段。",
|
||
"severity": "high",
|
||
},
|
||
{
|
||
"key": "api_boundary",
|
||
"label": "API 只呈現 drill，不得替操作員執行 psql、docker、SSH 或 DB write。",
|
||
"severity": "high",
|
||
},
|
||
]
|
||
|
||
return {
|
||
"mode": "migration_apply_drill_preview",
|
||
"execute_requested": execute_requested,
|
||
"schema_state": schema_state,
|
||
"drill_ready_for_operator_review": drill_ready_for_operator_review,
|
||
"ready_for_manual_apply_review": ready_for_manual_apply_review,
|
||
"ready_to_apply_migration": ready_to_apply_migration,
|
||
"migration_executed": False,
|
||
"rollback_executed": False,
|
||
"database_connection_opened": bool(
|
||
schema_db_probe.get("database_connection_opened")
|
||
or platform_seed_db_diff.get("database_connection_opened")
|
||
),
|
||
"read_only_query_executed": bool(
|
||
schema_db_probe.get("read_only_query_executed")
|
||
or platform_seed_db_diff.get("read_only_query_executed")
|
||
),
|
||
"database_session_created": False,
|
||
"explicit_transaction_opened": False,
|
||
"database_write_executed": False,
|
||
"database_commit_executed": False,
|
||
"external_network_executed": False,
|
||
"scheduler_attached": False,
|
||
"api_executes_migration": False,
|
||
"api_executes_rollback": False,
|
||
"api_executes_docker": False,
|
||
"api_executes_ssh": False,
|
||
"checks": checks,
|
||
"check_items": [
|
||
_check_item(key, key, passed)
|
||
for key, passed in checks.items()
|
||
],
|
||
"blocked_reasons": _build_blocked_reasons(
|
||
checks=checks,
|
||
schema_state=schema_state,
|
||
execute_requested=execute_requested,
|
||
),
|
||
"pre_apply_checklist": pre_apply_checklist,
|
||
"post_apply_verification": post_apply_verification,
|
||
"rollback_drill": rollback_drill,
|
||
"risk_register": risk_register,
|
||
"manual_commands": {
|
||
"schema_probe": "/api/market_intel/schema_db_probe?execute=true",
|
||
"platform_seed_db_diff": "/api/market_intel/platform_seed_db_diff?execute=true",
|
||
"migration_apply": migration_blueprint.get("command_plan", {})
|
||
.get("migration_apply_command", {})
|
||
.get("command", ""),
|
||
},
|
||
"migration_blueprint_summary": {
|
||
"suggested_filename": migration_blueprint.get("suggested_filename"),
|
||
"file_created": bool(migration_blueprint.get("file_created")),
|
||
"file_matches_blueprint": bool(
|
||
migration_blueprint.get("file_matches_blueprint")
|
||
),
|
||
"table_count": migration_blueprint.get("table_count", 0),
|
||
"forward_statement_count": migration_blueprint.get(
|
||
"forward_statement_count",
|
||
0,
|
||
),
|
||
"rollback_statement_count": migration_blueprint.get(
|
||
"rollback_statement_count",
|
||
0,
|
||
),
|
||
},
|
||
"schema_db_probe_summary": {
|
||
"mode": schema_db_probe.get("mode"),
|
||
"read_only_query_executed": bool(
|
||
schema_db_probe.get("read_only_query_executed")
|
||
),
|
||
"schema_tables_exist": bool(schema_db_probe.get("schema_tables_exist")),
|
||
"missing_tables": schema_db_probe.get("missing_tables") or [],
|
||
"existing_tables": schema_db_probe.get("existing_tables") or [],
|
||
},
|
||
"platform_seed_db_diff_summary": {
|
||
"mode": platform_seed_db_diff.get("mode"),
|
||
"read_only_query_executed": bool(
|
||
platform_seed_db_diff.get("read_only_query_executed")
|
||
),
|
||
"seed_rows_ready": bool(platform_seed_db_diff.get("seed_rows_ready")),
|
||
"missing_codes": platform_seed_db_diff.get("missing_codes") or [],
|
||
"changed_codes": platform_seed_db_diff.get("changed_codes") or [],
|
||
"matching_codes": platform_seed_db_diff.get("matching_codes") or [],
|
||
},
|
||
}
|