データベース設計

1. 設計思想：システム DB ＋ プロダクト DB

クヌギGEO は SQLite を 2 種類のファイルに分けて使用しています。スナップショット（日次で積み上がる最大級のテーブル）が肥大化しても、他のメタ情報を巻き込まずにバックアップ・移行・削除ができるようにすることが目的です。

スナップショットへのアクセス時は、システム接続に ATTACH DATABASE <product_<id>.db> AS pdb でアタッチし、pdb.survey_snapshots として参照します（api/bootstrap.php の llmo_attach_product_db()）。

2. システム DB のテーブル一覧

users — アカウント

Web ログインアカウントを管理します。username と email はメールアドレスで同期されます。

app_settings — システム共通設定（id=1 の 1 行のみ）

products — プロダクト

product_keywords — キーワード

user_products — ユーザのアクセス可能プロダクト

システム管理者（admin）以外（プロダクト管理者 product_manager／閲覧者 viewer）に対して、見せるプロダクトを user_id × product_id で限定するためのテーブル。システム管理者（admin）はこのテーブルに関係なく全プロダクトに到達できます。

product_ga4 — プロダクトの GA4 連携情報

ga4_oauth_states — OAuth フロー中の state 保持（短命）

「Google で認証」を押したときに発行する state を一時保存します。コールバック後は不要になります。

product_extended_reports — プロダクトごとの拡張レポート ON/OFF

products/extended_reports/*.php を自動検出する拡張レポート機能で、プロダクトごとに表示 ON/OFF をオーバーライドします。行が無いプロダクトは各レポートの @report-default-enabled に従います。

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

「パスワードを忘れた」フローで発行する短命トークン。token_hash はハッシュで保存され、平文は DB には保存されません。

login_attempts — ログイン試行レート制限

ログイン成功・失敗を記録し、ブルートフォース対策に使います。

3. プロダクト DB（product_<id>.db）のテーブル

survey_snapshots — 調査スナップショット

各キーワード × 軸の取得結果（JSON）を 1 行ずつ保存します。日次で増え続けるテーブルなので、プロダクトごとにファイルを分けてあります。

UNIQUE(product_keyword_id, captured_at) のため、同一バッチでの重複保存は発生しません（再取得時は INSERT OR REPLACE 相当の動作）。

result_json の構造（JSON フィールド一覧）

調査ランナー（cron/lib/survey_runner.php）と手動実行ハンドラ（api/catalog_handlers.php の llmo_catalog_save_snapshot）は、軸ごとの結果フラグ・派生指標を 1 つのオブジェクトに詰めて JSON 化し、この列に保存します。

共通／メタ

順位取得（fetch_rank）

Google AI Overview（fetch_ai_overview）

Google AI Mode（fetch_ai_mode）

独立エンドポイント（serp/google/ai_mode）で取得します。

LLM Responses（fetch_llm_chatgpt / _claude / _gemini / _perplexity）

下記の <axis> は llm_chatgpt / llm_claude / llm_gemini / llm_perplexity のいずれか。各軸ごとに同じ形式で並びます。本文は llm_engines[].data として保存されます。

brand_metrics_* 共通スキーマ（プロミネンス指標）

brand_metrics_ai_overview / brand_metrics_ai_mode / brand_metrics_<axis> はすべて同じ形です。本文が空のときも items は 0 値で埋めて返します。

4. バックアップと復元

1. 定期バックアップ： 手動での調査実行画面で「DB バックアップ」を実行すると、backup/app_YYYY-MM-DD_HHmmss.db として生成されます。cron で日次に cp -p も推奨。
2. 取得すべきもの：
   • data/app.db（および app.db-wal / app.db-shm）
   • data/products/product_*.db（および -wal / -shm）
   • data/auth_secret.bin（パスワードリセットトークン署名鍵 ※消すと既存リセットリンクが無効化）
   • カスタムロゴをアップロードしている場合 img/custom/
3. 復元： サーバを停止 → 上記ファイルをまるごと差し戻し → サーバを起動。

5. マイグレーション方針

スキーマ変更は api/bootstrap.php の冒頭で CREATE TABLE IF NOT EXISTS および ALTER TABLE ... ADD COLUMN（llmoAddColumnIfMissing ヘルパ）で適用しています。既存のカラムは原則削除しません。「旧 → 新」へ移行する場合は新カラムを並列で追加し、起動時のマイグレーションで値をコピーする戦略を採っています（例：products.survey_schedule_json は旧 app_settings.survey_schedule_json から一度だけシードされます）。