マインド MD の出典 URL を
Notion 両 DB に自動反映する設計と落とし穴

---
title: "AIを武器に、やりたいことを自分でデザインして生きるということ｜そら"
source: "https://note.com/sora19ai/n/n7598faf0b2a0"
published: 2026-05-02
tags: [clippings]
check_items:
  - id: cd3089987
    text: "「どうすれば楽に稼げる？」ではなく ..."
    ...
---

_URL_RE = re.compile(r"https?://[^\s\"'<>)\]]+")

def extract_source_url(source_value) -> str | None:
    """URL 直書き / "テキスト + URL" / list 形式の 3 パターンに対応。"""
    if source_value is None: return None
    if isinstance(source_value, list):
        for v in source_value:
            url = extract_source_url(v)
            if url: return url
        return None
    s = str(source_value).strip()
    if not s: return None
    m = _URL_RE.search(s)
    return m.group(0).rstrip(".,)\"';") if m else None

_SUPPORTED_PROP_TYPES = {
    "url": {"url": {}},
    "rich_text": {"rich_text": {}},
    "checkbox": {"checkbox": {}},
    "number": {"number": {"format": "number"}},
    "date": {"date": {}},
}

def ensure_database_property(token, db_id, prop_name, prop_type="url") -> bool:
    """DB に指定プロパティが無ければ追加。冪等。"""
    meta = retrieve_database(token, db_id)
    if prop_name in (meta.get("properties") or {}):
        return False  # 既存、何もしない
    _request(token, "PATCH", f"/databases/{db_id}", {
        "properties": {prop_name: _SUPPORTED_PROP_TYPES[prop_type]}
    })
    return True  # 今回追加

_COLUMN_MIGRATIONS: list[tuple[str, str, str]] = [
    ("master_items", "source_url",
     "ALTER TABLE master_items ADD COLUMN source_url TEXT"),
    ("daily_log", "source_url",
     "ALTER TABLE daily_log ADD COLUMN source_url TEXT"),
]

def _column_exists(conn, table, column):
    cur = conn.execute(f"PRAGMA table_info({table})")
    return any(row[1] == column for row in cur.fetchall())

def _apply_column_migrations(conn):
    for table, column, ddl in _COLUMN_MIGRATIONS:
        if not _column_exists(conn, table, column):
            conn.execute(ddl)
    conn.commit()

def connect(db_path=None):
    conn = sqlite3.connect(str(db_path or DB_PATH))
    conn.executescript(_SCHEMA_SQL)
    _apply_column_migrations(conn)  # ← 接続のたびに毎回チェック
    return conn

=== daily_log 2026-05-16 (source_url 集計) ===
  total=0, with_url=None
=== master_items サンプル ===
  (empty)

# 旧: 別の場所を見てしまう
DB_PATH = Path.home() / "projects" / "daily-mindset-checklist" / "data" / "checks.db"

# 新: scripts/_lib/db.py から見て project root の data/checks.db を必ず指す
DB_PATH = Path(__file__).resolve().parent.parent.parent / "data" / "checks.db"

def _backfill_urls(token, master_db_id, daily_db_id, target_date):
    """指定日の既存 Daily Log 行に Master 由来の Source URL を後付け。"""
    active_items = _list_active_master_items(token, master_db_id)
    url_by_item_id = {it["item_id"]: it.get("source_url")
                      for it in active_items}

    rows = notion_client.query_database(token, daily_db_id,
        filter_={"property": "Date",
                 "date": {"equals": target_date}})

    for row in rows:
        item_id = notion_client.rich_text(row["properties"]["Master ID"])
        url = url_by_item_id.get(item_id)
        if url and url != notion_client.url_value(row["properties"].get("Source URL", {})):
            notion_client.update_page(token, row["id"],
                {"Source URL": notion_client.make_url(url)})
    # 加えて SQLite daily_log も同じ url で UPDATE