履歴データをどう移すか ── 初期移行とスキーマ変換の設計

-- 1. 想定外の値が入っていないか（区分値の実態）SELECT status, COUNT(*) AS 件数, MIN(created_at) AS 最古, MAX(created_at) AS 最新FROM legacy_ordersGROUP BY statusORDER BY 件数 DESC;
-- 2. 時期によって値の使い方が変わっていないかSELECT    DATE_TRUNC('year', created_at) AS 年,    COUNT(*)                                          AS 件数,    COUNT(*) FILTER (WHERE tax_rate IS NULL)          AS 税率なし,    COUNT(DISTINCT tax_rate)                          AS 税率の種類,    COUNT(*) FILTER (WHERE amount < 0)                AS マイナス金額FROM legacy_ordersGROUP BY 1ORDER BY 1;
-- 3. 参照先が存在しない行（壊れた関連）SELECT COUNT(*) AS 孤児レコードFROM legacy_orders oLEFT JOIN legacy_customers c ON c.id = o.customer_idWHERE c.id IS NULL;

from dataclasses import dataclass, field

@dataclassclass MigrationResult:    converted: list = field(default_factory=list)    rejected: list = field(default_factory=list)   # 捨てずに記録する

# 時期によって区分の意味が変わっているので、期間ごとに規則を分けるSTATUS_MAP_OLD = {"1": "pending", "2": "paid", "3": "cancelled"}STATUS_MAP_NEW = {"P": "pending", "D": "paid", "C": "cancelled", "R": "refunded"}BOUNDARY = "2020-04-01"

def convert(row) -> tuple:    """(変換後, エラー理由) を返す。変換できなければ変換後は None。"""    table = STATUS_MAP_OLD if row["created_at"] < BOUNDARY else STATUS_MAP_NEW    status = table.get(row["status"])    if status is None:        return None, f"未知の区分値: {row['status']!r}（{row['created_at']}）"
    if row["amount"] is None:        return None, "金額が空"
    return {"id": row["id"], "status": status, "amount": row["amount"]}, None

def migrate(rows) -> MigrationResult:    result = MigrationResult()    for row in rows:        converted, reason = convert(row)        if converted is None:            result.rejected.append({"row": row, "reason": reason})        else:            result.converted.append(converted)    return result

-- 1. 件数（最も基本。ここがずれたら他を見る前に原因を探す）SELECT    (SELECT COUNT(*) FROM legacy_orders WHERE created_at >= '2024-01-01') AS 旧,    (SELECT COUNT(*) FROM new_orders    WHERE created_at >= '2024-01-01') AS 新;
-- 2. 集計値（金額の合計は最も気づきやすい指標）SELECT    DATE_TRUNC('month', created_at) AS 月,    SUM(amount)                     AS 合計金額,    COUNT(*)                        AS 件数FROM legacy_ordersWHERE created_at >= '2024-01-01'GROUP BY 1 ORDER BY 1;-- 同じクエリを新側でも実行し、月ごとに突き合わせる
-- 3. 個別の行（サンプリングして中身まで確認）SELECT o.id, o.status AS 旧状態, n.status AS 新状態,       o.amount AS 旧金額, n.amount AS 新金額FROM legacy_orders oJOIN new_orders n ON n.legacy_id = o.idWHERE o.status IS DISTINCT FROM n.status   OR o.amount IS DISTINCT FROM n.amountLIMIT 100;