データベース設計

方針

• クヌギスキーマは SQLite 単一 DB（data/schema.db）で動きます。複数 DB の分割はしていません。
• マイグレーションは src/Database.php の migrate() 内で毎回 CREATE TABLE IF NOT EXISTS を実行するシンプルな方式です。初回起動時に自動で全テーブルが作られます。
• 手動クロール中でも他タブが結果を閲覧できるよう、WAL モード（PRAGMA journal_mode=WAL）・synchronous=NORMAL・busy_timeout=30000・キャッシュ拡張などを起動時に設定しています。外部キー（PRAGMA foreign_keys=ON）も有効です。
• クロール結果の生データは crawl_results.result_json に JSON テキストとして一括保存しています。スキーマ変更に強く、画面側で柔軟にパースできます。

テーブル一覧

クロール対象を絞り込む主要ヘルパー（Repository）

各画面（手動でクロール実行・cron・ダッシュボード）はテーブルを直接 SQL で触らず、src/Repository.php のヘルパーメソッドを通じて取得します。新仕様で重要なものは下記です。

users — ログインユーザー

CREATE TABLE users (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  email         TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  role          TEXT NOT NULL DEFAULT 'admin',   -- 'admin' or 'product_manager'
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

• email は小文字化＋前後空白除去して保存。検証は FILTER_VALIDATE_EMAIL。
• password_hash は PHP の password_hash($pw, PASSWORD_DEFAULT)（現状は bcrypt）で保存。
• role は admin（システム管理者）と product_manager（プロダクト管理者）の 2 種。詳細は アカウント管理。
• 旧バージョンに存在した display_name・allowed_pages カラムは、起動時に検出されると ALTER TABLE DROP COLUMN で自動削除されます（SQLite 3.35+ の場合のみ。古いバージョンでは残ったままになりますが、アプリ側は参照しません）。

products — プロダクト

CREATE TABLE products (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

• 「プロダクト」はクロール対象を束ねる単位です。自社のコーポレートサイト、商品 LP 群、ブランドサイトなどを個別の単位として登録する想定。
• 旧バージョンの description カラムは起動時マイグレーションで削除されます（SQLite 3.35+）。
• プロダクトを削除すると ON DELETE CASCADE により、配下の URL・クロール履歴・期待値・検出設定・cron スケジュールなどがすべて削除されます。

user_products — ユーザー × プロダクト

CREATE TABLE user_products (
  user_id    INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  PRIMARY KEY (user_id, product_id),
  FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

• 将来的に「プロダクト管理者をプロダクト単位で割り当てる」拡張を見据えたテーブルです。現バージョンでは未使用ですが、起動時に作成されます。
• 現状のアクセス制御はページ単位（Auth::PRODUCT_MANAGER_PAGES の固定 5 ページ）で行っています。詳細は アカウント管理。

product_urls — 事前登録 URL

CREATE TABLE product_urls (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  product_id INTEGER NOT NULL,
  url        TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE (product_id, url),
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

• 1 プロダクト内で同一 URL の重複登録を禁止します（UNIQUE (product_id, url)）。複数行を一括投入したとき、既存と重複した URL は INSERT OR IGNORE でスキップされます。
• 旧 note カラムはマイグレーションで削除されます。一覧表示の HTTP / 最終クロールは crawl_results からの集計です。

product_website_expectations — メタ情報の事前登録

CREATE TABLE product_website_expectations (
  product_id                  INTEGER PRIMARY KEY,
  name                        TEXT,
  url                         TEXT,
  alternate_name              TEXT,
  description                 TEXT,
  in_language                 TEXT,
  publisher_name              TEXT,
  publisher_url               TEXT,
  search_action_target        TEXT,
  search_action_query_input   TEXT,
  same_as_json                TEXT NOT NULL DEFAULT '[]',
  breadcrumb_selectors_json   TEXT NOT NULL DEFAULT '[]',
  updated_at                  TEXT,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

• プロダクト 1 件につき 1 行。WebSite 期待値とパンくずセレクターを同居させています（メタ情報の事前登録）。
• 空文字のフィールドは NULL として保存し、照合時は「未設定＝スキップ」を表現します。
• same_as_json / breadcrumb_selectors_json は JSON 配列文字列です。

product_schema_detection_settings — 検出設定

CREATE TABLE product_schema_detection_settings (
  product_id           INTEGER PRIMARY KEY,
  disabled_types_json  TEXT NOT NULL DEFAULT '[]',
  updated_at           TEXT,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

• チェックを外した構造化マークアップ種別（代表 @type 名）のリストを JSON で保存します（構造化マークアップ検出設定）。
• 行が無いプロダクトは「すべて検出 ON」と同義です。

crawl_runs — クロール実行ログ（Run 単位）

CREATE TABLE crawl_runs (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  product_id    INTEGER NOT NULL,
  trigger       TEXT    NOT NULL DEFAULT 'manual',  -- 'manual' or 'cron'
  started_at    TEXT    NOT NULL DEFAULT (datetime('now')),
  finished_at   TEXT,
  total_urls    INTEGER NOT NULL DEFAULT 0,
  success_count INTEGER NOT NULL DEFAULT 0,
  error_count   INTEGER NOT NULL DEFAULT 0,
  delay_seconds INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

• 1 回のクロール実行 = 1 行。手動実行画面 / cron 自動実行のどちらでも作成されます。trigger でどちらから起動されたかが分かります。
• started_at は INSERT 時の datetime('now')、finished_at は全 URL 処理完了時に UPDATE されます。
• ダッシュボードの「直近のクロール実行」テーブルは、このテーブルの新しい 5 件を表示しています。

crawl_results — クロール結果（1 URL 単位）

CREATE TABLE crawl_results (
  id               INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id           INTEGER NOT NULL,
  product_id       INTEGER NOT NULL,
  product_url_id   INTEGER,
  url              TEXT    NOT NULL,
  final_url        TEXT,
  http_status      INTEGER,
  content_type     TEXT,
  detected_charset TEXT,
  title            TEXT,
  meta_description TEXT,
  canonical_url    TEXT,
  primary_heading  TEXT,        -- h1（最初の 1 件）
  result_json      TEXT,        -- 解析結果一式（JSON テキスト）
  error_message    TEXT,
  is_error         INTEGER NOT NULL DEFAULT 0,    -- 0/1
  created_at       TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (run_id)     REFERENCES crawl_runs(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id)   ON DELETE CASCADE
);
CREATE INDEX idx_crawl_results_product ON crawl_results(product_id, created_at);
CREATE INDEX idx_crawl_results_iserror ON crawl_results(product_id, is_error);

result_json の中身

解析結果は 1 つの JSON オブジェクトにまとめて保存しています。画面側はこれを json_decode して必要なキーだけ取り出します。

{
  "entities": [
    { "source": "JSON-LD", "index": 1, "type": "Article",
      "properties": { "headline": "...", "author": "...", ... },
      "raw": "<script type=\"application/ld+json\">...</script>" }
  ],
  "structured_data": {
    "json_ld":    [ { "index": 1, "raw": "...", "data": { ... } } ],
    "microdata":  [ ... ],
    "rdfa":       [ ... ]
  },
  "suggestions": [
    { "type": "FAQPage", "status": "missing",
      "reason": "ページ内に FAQ ライクな見出し-本文ペアを検出しました。",
      "recommended_properties": ["mainEntity", "name", "acceptedAnswer"] }
  ],
  "consistency_checks": [
    { "status": "mismatch", "source": "JSON-LD", "type": "Article",
      "field": "headline", "schema_value": "...", "page_value": "...",
      "message": "title / h1 とヘッドラインが一致していません。" }
  ]
}

• entities： 検出された全エンティティ。source は JSON-LD / Microdata / RDFa のいずれか。同じソース内で複数ある場合は index で識別します。
• structured_data： 形式別の生データ（生 HTML を raw に保持）。結果詳細 画面で「クリックで展開」表示するのに使います。
• suggestions： 「ページの中身的に追加した方が良さそうなスキーマ」の提案。status は implemented / missing / warning など。
• consistency_checks： 「マークアップで宣言した値」と「ページの実コンテンツ」が一致しているかの判定行リスト。詳細は マークアップ × ページ内容の一致確認。

エラー時の挙動

• HTTP 4xx / 5xx の場合も例外にはせず、is_error=1 として 1 行保存します。error_message に「HTTP 404 Not Found」などの文字列が入ります。
• cURL レベルで失敗した（接続不可・タイムアウトなど）場合も同様に is_error=1 で保存され、http_status は 0 または NULL になります。
• エラー行は エラーURL管理 画面で一覧表示・削除できます。

created_at の保存タイムゾーン（重要）

• crawl_results.created_at は SQLite の datetime('now') で保存されるため、UTC（協定世界時）です。
• 「当日まだクロールしていない URL を抽出する」処理（Repository::listUrlsNotCrawledOnDate()）では、date(created_at, '+9 hours') で JST（日本時間）日付に補正してから比較しています。これにより、サーバの OS タイムゾーン設定に依存せず、日本のカレンダー基準で日次サイクルが回ります。
• 同様に Repository::countUrlsCrawledOnDate() も +9 hours 補正を行います。手動でクロール実行画面の「本日クロール済み件数」表示も内部的にこの関数を使っています。

cron_schedule — Cron 実行日設定

CREATE TABLE cron_schedule (
  product_id           INTEGER PRIMARY KEY,
  days_json            TEXT    NOT NULL DEFAULT '[]',   -- 例: '[1,15]'
  crawl_delay_seconds  INTEGER NOT NULL DEFAULT 5,
  enabled              INTEGER NOT NULL DEFAULT 1,
  last_triggered_date  TEXT,                            -- 'YYYY-MM-DD'
  updated_at           TEXT,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

• 1 プロダクト = 1 行です（product_id が主キー）。スケジュール未設定のプロダクトは行が存在せず、cron からはスキップされます。
• days_json には 1〜31 の整数配列を JSON で保存します。今日の日付（date('j')）がこの配列に含まれているかで実行可否を判定。
• last_triggered_date は「最後に cron がクロールを動かした日」の記録として更新されます。新仕様（1 時間に 1 回 cron を回す）では当日内の二重起動を許可するため、この列で重複ブロックはしません。重複防止は「URL 単位で crawl_results を見て」行います（Repository::listUrlsNotCrawledOnDate()）。

cron_log — Cron 実行サマリーログ

CREATE TABLE cron_log (
  id      INTEGER PRIMARY KEY AUTOINCREMENT,
  ran_at  TEXT NOT NULL DEFAULT (datetime('now')),
  message TEXT
);

• cron 1 起動につき複数行が追加されます（プロダクトごとに 1 行＋全体サマリー）。
• 詳細なクロール状況はあくまで crawl_runs / crawl_results 側に残ります。cron_log は「いつ起動されて何プロダクト処理したか」の最小限の足跡です。

password_reset_tokens — パスワード再発行トークン

CREATE TABLE password_reset_tokens (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id    INTEGER NOT NULL,
  token_hash TEXT    NOT NULL UNIQUE,
  expires_at TEXT    NOT NULL,
  used_at    TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX idx_prt_user    ON password_reset_tokens(user_id);
CREATE INDEX idx_prt_expires ON password_reset_tokens(expires_at);

• 「パスワードをお忘れの方はこちら」から発行されるワンタイムリンクのトークンを、ハッシュ化して保存します（生のトークンは DB に残しません）。
• expires_at を過ぎたトークンは無効。使用済みのものは used_at がセットされ、再利用不可になります。
• 詳細フローは ログイン／パスワード再発行。

app_settings — グローバル設定 key/value

CREATE TABLE app_settings (
  key   TEXT PRIMARY KEY,
  value TEXT
);

• システム全体に効くグローバル設定の汎用キーバリューです。読み書きは Repository::getAppSetting() / setAppSetting()。
• 現バージョンで利用しているキーは下記のとおりです。

  key	意味	既定値
  cron_max_urls_per_run	cron 1 回あたりに処理する URL の最大数。Cron スケジュール 画面から編集できます。0 で無制限。	500

• 新しいグローバル設定を増やしたい場合は、ここに行を足す代わりに getAppSetting() / setAppSetting() で薄いラッパ関数を作る運用が想定されています（例: getCronMaxUrlsPerRun()）。

クロール 1 回で書き込まれるテーブル

手動でも cron でも、クロール 1 回が走ると以下のテーブルへの書き込みが連動して発生します。新仕様の cron は 1 時間に 1 回起動するため、1 日に同じテーブルへ何度も書き込みが入る前提です。

1. crawl_runs に Run を 1 件 INSERT（trigger が manual か cron か）。
2. 1 URL ごとに crawl_results へ 1 行 INSERT（成功でもエラーでも必ず残る）。同一 URL を当日中に重複クロールしないのは、cron / 手動「未クロールのみ」モードが listUrlsNotCrawledOnDate() で事前にフィルタしているためです（テーブル側に UNIQUE 制約は無し）。
3. 全件処理が終わったら crawl_runs.finished_at ＋ 件数を UPDATE。
4. cron 経由の場合のみ、cron_schedule.last_triggered_date を本日に UPDATE ＋ cron_log へ 1 行 INSERT。0 件スキップ時も last_triggered_date は更新されます。