メインコンテンツまでスキップ

データ保存戦略

結論: 保存する方が圧倒的に安い​

毎回取得 vs 保存の比較​

毎回取得保存（キャッシュ）
API呼び出し検索のたびにYouTube API + 字幕取得初回のみ
レスポンス速度5〜30秒（字幕取得に時間かかる）0.1〜1秒（DB検索のみ）
YouTube APIクォータすぐ枯渇（1日10,000ユニット）ほぼ消費しない
レート制限リスク高い（BANされる可能性）低い
DB費用不要月$0〜$25

具体例: チャンネルA（動画500本）を100人が1日1回検索した場合​

毎回取得保存
字幕取得回数/日50,000回500回（初回のみ）
YouTube API消費即クォータ超過ほぼゼロ
ユーザー体験毎回待たされる即時表示

→ 毎回取得だと1日で破綻する

おすすめ構成​

初回: ユーザーが検索時にチャンネルを指定 → 字幕を全取得 → Meilisearchに保存
検索: Meilisearchに対してクエリ（YouTube APIを使わない）
差分: 検索時に前回取得以降の新規動画を自動チェックし、あれば差分取得

データ量の見積もり​

規模データ量
動画1本の字幕5〜50KB
1,000本のチャンネル5〜50MB
10,000チャンネル分50〜500GB

検索速度の対策​

方法速度コスト
PostgreSQL LIKE遅い（数秒）追加なし
PostgreSQL 全文検索 (tsvector)速い追加なし
Meilisearch / Typesense爆速（数ms）+$0〜$30/月
Elasticsearch爆速+$50〜$100/月（高い）

初回取得のボトルネックと対策​

課題​

  • 100万本 × 平均3秒 = 約35日（並列化しても数日）
  • YouTube側の大量取得によるIP BANリスク
  • 60GBのDBに対する曖昧検索のインデックス設計

対策​

  • 新規チャンネルの字幕取得は インプロセスキュー で順次処理
  • 同じチャンネルを複数ユーザーがリクエストしても 1回だけ取得（重複排除）

アクセス権管理（user_video_access）​

ユーザーが動画の字幕にアクセス（クレジット消費）した履歴をTursoで管理する。キャッシュキーは user_id + video_id（動画単位）。

テーブル設計​

CREATE TABLE user_video_access (
  user_id    TEXT NOT NULL REFERENCES users(id),
  video_id   TEXT NOT NULL REFERENCES videos(id),
  credits    INTEGER NOT NULL,  -- 消費したクレジット数
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL, -- created_at + 4週間
  PRIMARY KEY (user_id, video_id)
);

匿名ユーザーの扱い​

注意: クレジット消費（動画字幕の閲覧）はログイン必須に変更済み。 以下は過去の設計を記録として残すものであり、新規の匿名クレジット消費は発生しない。 検索・検索履歴は引き続き匿名で利用可能（anonymousUserMiddleware は存続）。

現行の方針​

  • 検索はログイン不要。未認証ユーザーは検索と検索履歴の閲覧のみ利用可能
  • 動画字幕の閲覧（クレジット消費）にはログインが必須。POST /api/credits/consume は authMiddleware で保護
  • 匿名ユーザーの遅延作成・マージ処理は廃止済み

変更の経緯​

以前は匿名ユーザーにも Cookie ベースでクレジットを付与していたが、以下の問題があった:

  1. Cookie 削除によるクレジット不正取得: Cookie（anonymous_user_id）を削除するだけで新規匿名ユーザーとして扱われ、Free プランの 50 credits が何度でも復活する。IP 制限等の対策は VPN・共有 IP での誤検知リスクがあり現実的ではなかった
  2. 保守コストの高さ: 匿名クレジット管理のために遅延作成・マージ・月次リセット・Cookie 有効期限管理・レースコンディション対策（排他ロック）など複雑なコードパスが必要だった

クレジット消費をログイン必須にすることで、これらの問題を根本的に解消した。 認証は OAuth セッション（Better Auth）で管理されるため、Cookie 削除ではクレジットが復活しない。匿名ユーザーは検索と検索履歴のみ利用でき、クレジットに関わる操作は一切できないため、不正利用の攻撃ベクトル自体が存在しない。

マージ処理の廃止​

本番運用前（ユーザーゼロ）の段階でログイン必須化を適用したため、匿名ユーザーのマージ処理（POST /api/anonymous-user/merge）は後方互換を考慮する必要がなく、全削除した。

代替案不採用理由
セッションCookieブラウザ再起動で消失し、匿名レコードが大量に作成される
4週間（28日）user_video_access.expires_at とは揃うが、クレジットの月次リセットサイクルと噛み合わない。月末作成だとリセット後のクレジットをほぼ使えずにCookieが切れる
1年等の長期Cookie不要な匿名レコードがDBに長期残留する。60日あれば十分

マージ時のクレジット上限超過​

課題シナリオ:

  1. 認証済みユーザーA: クレジット残高45/50
  2. 匿名セッション anon1: クレジット残高30/50（20消費済み）
  3. ログイン → マージでクレジット残高を加算すると 45 + 30 = 75 > 50（上限超過）

解決方針: 上限超過を許容する

  • マージ時はクレジット残高を単純加算し、50の上限でクランプしない
  • マージは1回きりのイベントであり、月次リセットで自然に50に戻る
  • 採用理由: マージでユーザーのクレジットを切り捨てるのはユーザーにとって不利益。匿名時代に消費しなかった分を引き継ぐのが自然
代替案不採用理由
min(合計, 50) でクランプユーザーが匿名時代に節約したクレジットが消失する。不公平感がある
匿名側のクレジット残高は無視し、アクセス記録のみマージクレジット残高の扱いが不透明になり、ユーザーの期待と乖離する

マージ済み匿名CookieによるFK違反防止​

課題シナリオ:

  1. 匿名ユーザー anon1 でクレジット消費 → Cookieに anon1 が保存される
  2. ログイン → マージ処理で anon1 の users レコードが DELETE される
  3. Cookieには anon1 が残ったまま
  4. ログアウト → 次のクレジット消費時、Cookieの anon1 で user_video_access を INSERT しようとする → users テーブルに anon1 が存在しないためFK違反

解決方針: クレジット消費時にCookieの匿名ユーザーIDを検証する

  • クレジット消費の前処理として、Cookieの anonymous_user_id が users テーブルに存在するか確認する
  • 存在しない場合は新しい匿名ユーザーを作成し、Cookieを新しいIDで上書きする
  • 採用理由: マージ時にCookieを削除する方式だと、マージとCookie削除の間にレースコンディションが起きうる。消費時の検証なら確実にFK違反を防げる
代替案不採用理由
マージ完了時にCookieを削除（Set-Cookie で Max-Age=0）マージのレスポンスでCookieを消せるが、レスポンス到達前に別タブでクレジット消費が走るとFK違反が防げない
マージ時に匿名 users レコードを DELETE せず merged_to カラムで論理削除不要なレコードが残り続ける。物理削除の方がシンプル

匿名 users レコードのクリーンアップ基準​

課題シナリオ:

  • 「月次バッチの期限切れ削除で自然消滅」と記述しているが、匿名 users レコード自体の削除条件が未定義
  • user_video_access の期限切れ行は expires_at < NOW() で削除されるが、匿名 users レコードはいつ消すのか

解決方針: user_video_access クリーンアップの後に、孤立した匿名 users を削除する

  • 月次バッチの処理順序:
    1. user_video_access の期限切れ行を削除（既存の処理）
    2. user_video_access に1件も関連レコードがない匿名 users レコードを削除
  • 判定SQL: DELETE FROM users WHERE id NOT IN (SELECT DISTINCT user_id FROM user_video_access) AND id NOT IN (SELECT user_id FROM accounts)
    • accounts テーブルにレコードがない = 匿名ユーザー（既存の判別ロジックと一致）
    • user_video_access に関連レコードがない = 全てのアクセス記録が期限切れで削除済み
  • 採用理由: 既存のバッチに1ステップ追加するだけで済む。匿名ユーザーの判別ロジック（accounts テーブルの有無）をそのまま再利用できる
代替案不採用理由
users に last_active_at を追加し、一定期間アクティビティがない匿名ユーザーを削除カラム追加が必要。user_video_access の有無で判定すれば追加カラムは不要
Cookie有効期限（60日）と連動させ、作成から60日後に削除Cookieが切れてもDBにアクセス記録が残っている場合、マージの可能性がある（別ブラウザでログイン等）。アクセス記録ベースの方が安全

有料プランユーザーへのマージ時のクレジット計算​

課題シナリオ:

  • 匿名ユーザーは常にFreeプラン（50クレジット/月）として扱われる
  • マージ先がProプラン（500クレジット/月）のユーザーの場合、クレジット残高の加算はどう計算されるか
  • 例: 匿名 anon1 のクレジット残高40/50 + Proユーザーの残高480/500 = 520

解決方針: プランに関係なく単純加算（「マージ時のクレジット上限超過」と同一ロジック）

  • マージ時のクレジット加算はプランの上限を参照しない
  • 上限超過と同様に、月次リセットでプランに応じた上限値に自然に戻る
  • 採用理由: マージのロジックをプラン非依存にすることで、プラン追加・変更時にマージロジックの修正が不要になる

credits カラムの命名​

候補意味採用
credits消費したクレジット数採用
duration_minutes動画の長さ（分）不採用

credits を採用した理由:

  • このテーブルの目的は「課金としてのクレジット消費記録」であり、動画の物理的属性（長さ）の記録ではない
  • 将来クレジット換算レートが変わった場合（例: 1分=2クレジット）、credits なら実際の消費量を正確に記録できる
  • duration_minutes だと消費クレジット数を逆算する必要があり、レート変更時に不整合が生じる

運用ルール​

  • 有効期限チェック: クエリ時に WHERE expires_at > NOW() でフィルタする。期限切れレコードがDBに残っていても機能的に影響はない（無視される）
  • 再アクセス時の挙動: 期限切れ後に再度クレジットを消費した場合、新しい行は追加されず、既存行が更新される。PKが (user_id, video_id) のため同じユーザー×同じ動画の組み合わせは1行しか存在できず、ON CONFLICT (user_id, video_id) DO UPDATE で以下のように処理する:
    • expires_at → 新しい期限に上書き
    • credits → 新しい消費クレジット数に上書き
    • updated_at → 現在時刻に更新
    • created_at → 初回アクセス日としてそのまま保持（UPSERTで更新しない）
  • 期限切れレコードの物理削除: 月1回のバッチで expires_at < NOW() のレコードを削除する。これはストレージ節約が目的であり、機能的にはクエリ時フィルタだけで十分なため頻度は低くてよい

クレジット残高管理​

billing_accounts テーブル設計​

ADR

organization 将来導入と billing 分離の方針詳細は社内 ADR memo/09-organization-and-billing-strategy.md を参照。旧 user_subscriptions からのリネーム理由もそちらに記載。

課題:

  • user_video_access のテーブル設計は定義済みだが、クレジット残高とプランをどこで管理するかが未定義
  • 匿名ユーザーのマージ（残高加算）、月次リセット、クレジット消費の全てがクレジット残高に依存する

設計:

CREATE TABLE billing_accounts (
  id                 TEXT PRIMARY KEY,               -- UUID v4（将来 organization 所有時の付け替えを容易にするため独立 PK）
  user_id            TEXT NOT NULL UNIQUE REFERENCES users(id),  -- 1 ユーザー 1 billing account
  stripe_customer_id TEXT UNIQUE,                    -- Stripe 未接続ユーザーは NULL
  plan               TEXT NOT NULL DEFAULT 'free',
  credit_balance     INTEGER NOT NULL DEFAULT 100,
  created_at         DATETIME NOT NULL,
  updated_at         DATETIME NOT NULL
);
  • Better Auth の users テーブルとは別テーブルで管理する（1:1 関係、user_id UNIQUE で保証）
  • credit_balance は残高を直接保持する（消費時に減算、リセット時にプラン上限値で上書き）
  • stripe_customer_id を本テーブルで直接保持する。Better Auth の Stripe プラグインには寄せず、billing ドメインとして独立管理する
  • ON DELETE はなし: user 削除時に billing レコードを残す運用（返金・監査ログ要件）
  • 採用理由:
    • 関心の分離: billing はビジネスロジックであり、認証ライブラリ（Better Auth）のテーブルに混ぜるべきではない
    • Better Auth のアップデート耐性: Better Auth のスキーママイグレーションとドメインカラムが干渉するリスクを避けられる
    • organization 移行容易性: 所有者を user → organization に付け替える場合、独立 PK があればテーブル構造を大きく変えずに済む

月次リセットの実装:

  • 月次バッチで UPDATE billing_accounts SET credit_balance = {プラン上限値} を実行する
  • プラン上限値はプランマスタ（free=50, plus=500, pro=2000）から取得
  • 匿名ユーザーは常にfree（50）でリセット
代替案不採用理由
users テーブルに additionalFields でカラム追加技術的には可能だが、認証とビジネスロジックの関心が混在する。Better Auth のスキーマ更新時にコンフリクトのリスクがある
credits_used INT で消費量を記録し、残高は プラン上限 - credits_used で計算マージ時の加算が「used を減算」になり直感に反する。残高参照のたびにプラン上限との結合が必要

プラン変更時のクレジット残高処理​

課題シナリオ:

  • 月途中でプランを変更した場合、クレジット残高はどうなるか
  • アップグレード: Free(残30/50) → Plus → 残高は？
  • ダウングレード: Pro 10K(残8,000/10,000) → Plus → 残高は？

解決方針: プラン変更時に新プランの上限値でリセットする

  • アップグレード: credit_balance = 新プランの上限値（例: Free→Plus で残高は1,000になる）
  • ダウングレード: credit_balance = 新プランの上限値（例: Pro 10K→Plus で残高は1,000になる）
  • 採用理由: アップグレード・ダウングレードで同一のロジック（新プラン上限でリセット）にすることで、実装がシンプルになる。ダウングレード時に旧残高を引き継ぐと上限超過状態になり、月次リセットまで新プランの上限を超えた残高が残り続ける不整合が生じる
  • UI要件: プラン変更確認画面で「クレジット残高は新プランの上限値（○○クレジット）にリセットされます」と明示する
代替案不採用理由
アップグレード時は差分加算（残30 + 950 = 980）、ダウングレード時は min(残高, 新上限)アップグレードとダウングレードで異なるロジックになり複雑。差分加算だと月末アップグレードで損した感覚が生じる
残高を一切変更せず、次回月次リセットで自然に新プラン上限になるダウングレード時に旧プランの大量残高が残り、ダウングレードの意味が月末まで反映されない

複数動画の一括クレジット消費​

課題シナリオ:

  • 検索結果ページで複数のぼかし動画を一括で消費する場合のトランザクション設計が未定義
  • 10動画を一括消費中にクレジット残高が不足したらどうするか

解決方針: 1トランザクションで消費可能な分だけ処理する

  • 処理フロー:
    1. BEGIN TRANSACTION
    2. ユーザーの credit_balance を取得（排他ロック）
    3. 検索結果の表示順（関連度順 or 新着順）で動画を順に処理:
      • credit_balance >= video.credits なら user_video_access を INSERT し、credit_balance を減算
      • 残高不足になったら残りの動画はスキップ（BREAK）
    4. COMMIT
  • 消費できた動画は通常表示、残りはぼかし表示のまま（機能仕様書 の「クレジット不足時のUX」と整合）
  • 消費順序は検索結果の表示順。ユーザーが最も見たい動画（上位表示）から優先的にクレジットが使われる
  • 採用理由: 1トランザクションなので残高チェックと消費の間にレースコンディションが起きない。部分消費を許容するので、残高不足でも可能な範囲でアクセスを提供できる
代替案不採用理由
動画ごとに個別トランザクション残高チェック→消費の間に別タブのリクエストが割り込み、残高がマイナスになるリスクがある
残高不足なら全動画ロールバック（all or nothing）ユーザーが一部でもアクセスできる方がUXが良い。機能仕様書 の部分表示の仕様とも整合しない

チャンネル・動画・お気に入りチャンネルのテーブル設計​

ストレージの使い分け​

データTursoMeilisearch
チャンネルメタデータ○-
動画メタデータ○-
字幕テキスト-○（検索用 + 元データ内部保持）
お気に入りチャンネル○-

理由: チャンネル・動画メタデータは FK 制約（user_video_access.video_id、favorite_channels.channel_id）やクレジット計算（動画の長さ）にリレーショナルな管理が必要。字幕テキストは Meilisearch が検索用インデックスと元データの両方を内部保持する。R2 は MVP では不要（詳細は 検索インフラ調査 を参照）。

channels テーブル​

CREATE TABLE channels (
  id                  TEXT PRIMARY KEY,                            -- YouTube チャンネルID (UCxxxxxx)
  title               TEXT NOT NULL,                               -- チャンネル名
  handle              TEXT,                                        -- カスタムURL (@handle)
  thumbnail_url       TEXT,                                        -- サムネイル画像URL
  subtitle_status     TEXT NOT NULL DEFAULT 'pending',             -- 字幕取得状態: pending / processing / completed / failed / deleted / opted_out
  video_count         INTEGER NOT NULL DEFAULT 0,                  -- 動画数（YouTube APIから取得）
  country             TEXT,                                        -- チャンネル運営者の所在国 ISO-3166-1 alpha-2（未設定時 NULL）
  subscriber_count    INTEGER,                                     -- チャンネル登録者数（YouTube 側で非公開設定時は NULL）
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_channels_title ON channels(title);
CREATE INDEX idx_channels_handle ON channels(handle);
CREATE INDEX idx_channels_subtitle_status ON channels(subtitle_status);

カラム選定理由:

カラム理由
idYouTube チャンネルIDをPKとして使用（一意で不変）
title検索UIのCombobox、お気に入り一覧、検索結果表示に必要
handleユーザーが youtube.com/@handle 形式でURLを入力した場合の解決に必要
thumbnail_urlお気に入り一覧やComboboxでのアイコン表示
subtitle_status初回取得フローの状態管理・同時リクエスト重複排除
video_countYouTube API から取得したチャンネルの動画数。Combobox 等での表示用
countrychannels.list?part=snippet の snippet.country。検索の「投稿元の国・地域」フィルターで使用。チャンネルオーナーが自身で設定する任意項目のため NULL 許容（Filmot 実測で約 30% のチャンネルが未設定）。詳細は 投稿元の国・地域フィルター

subtitle_status 状態遷移:

pending → processing → completed
                    ↘ failed → processing（リトライ）

含めないカラム:

  • description: 現在の機能仕様に表示箇所なし
  • subscriber_count: 頻繁に変わる値。必要時に YouTube API から取得

videos テーブル​

CREATE TABLE videos (
  id               TEXT PRIMARY KEY,                              -- YouTube 動画ID (11文字)
  channel_id       TEXT NOT NULL REFERENCES channels(id),         -- 所属チャンネルID
  title            TEXT NOT NULL,                                 -- 動画タイトル
  thumbnail_url    TEXT,                                          -- サムネイル画像URL
  duration_seconds INTEGER NOT NULL,                              -- 動画の長さ（秒）
  view_count       INTEGER,                                       -- 再生回数（YouTube APIから取得、欠損時 NULL）
  like_count       INTEGER,                                       -- 高評価数（投稿者が非公開にしている場合 NULL）
  published_at     DATETIME NOT NULL,                             -- 公開日時
  subtitle_status  TEXT NOT NULL DEFAULT 'pending',               -- 字幕取得状態: pending / completed / failed / unavailable
  subtitle_type    TEXT,                                          -- 字幕タイプ: 'manual' / 'auto' / NULL
  category_id      TEXT,                                          -- YouTube カテゴリID (snippet.categoryId)。欠損時 NULL
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_videos_channel_id ON videos(channel_id);
CREATE INDEX idx_videos_published_at ON videos(published_at);
CREATE INDEX idx_videos_subtitle_status ON videos(subtitle_status);
CREATE INDEX idx_videos_channel_subtitle ON videos(channel_id, subtitle_status);

カラム選定理由:

カラム理由
idYouTube 動画IDをPKとして使用。user_video_access のFK先
channel_idチャンネルとの紐付け。検索結果でのJOIN
title検索結果一覧での表示（ぼかし時もタイトルは表示する仕様）
thumbnail_url検索結果のサムネイル表示
duration_secondsクレジット消費量の計算に必須: Math.ceil(duration_seconds / 60) = 消費クレジット数
view_count検索の視聴回数 Range フィルターで使用。videos.list?part=statistics から取得。YouTube API 側で欠損するケースは極稀だが念のため NULL 許容
like_count検索の高評価数 Range フィルターで使用。投稿者が高評価数を非公開にしている動画では API が値を返さないため NULL 許容
published_at検索結果の「公開日順」ソート（sort=published_desc / published_asc）および公開日 Range フィルター
subtitle_status動画個別の字幕取得状態。チャンネル全体が completed でも個別動画が failed の場合あり
subtitle_type字幕が手動作成（manual）か YouTube 自動生成（auto）か。検索フィルターで使用
category_idvideos.list?part=snippet の snippet.categoryId。検索のカテゴリフィルターで使用。古い動画や snippet が返らないケースで NULL 許容（実測では殆どの動画で取れる）

subtitle_status 状態遷移:

pending → completed → deleted（YouTube削除/非公開を検出）
       ↘ failed → completed（リトライ）
       ↘ unavailable（字幕が存在しない動画、リトライ対象外）

含めないカラム:

  • description: 検索対象として Meilisearch に保持。Turso には不要
  • view_count / like_count: 動画詳細ページで表示するが、頻繁に変わる値。YouTube 埋め込みプレーヤーで確認可能、または YouTube Data API から動的取得

favorite_channels テーブル​

CREATE TABLE favorite_channels (
  user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  channel_id TEXT NOT NULL REFERENCES channels(id),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, channel_id)
);

CREATE INDEX idx_favorite_channels_user_created ON favorite_channels(user_id, created_at DESC);

カラム選定理由:

カラム理由
user_idお気に入りの所有者。ON DELETE CASCADE でアカウント削除時に自動削除
channel_idお気に入りチャンネル。ON DELETE CASCADE なし（チャンネルは共有リソース）
created_at表示順が「追加日時の降順」のため必須
PK: (user_id, channel_id)同じユーザーの重複登録を防止

含めないカラム:

  • updated_at: お気に入りは追加・削除のみで更新操作がない
  • sort_order: 表示順は created_at DESC 固定

制約:

  • 匿名ユーザーは利用不可（アプリケーション層で accounts テーブルの有無を確認して制御）
  • 保存数は無制限

Meilisearch ドキュメント構造（参考）​

{
  "id": "dQw4w9WgXcQ_0",
  "videoId": "dQw4w9WgXcQ",
  "channelId": "UCuAXFkgsw1L7xaCfnd5JJOw",
  "title": "動画タイトル",
  "description": "動画説明文",
  "text": "字幕テキスト（セグメント）",
  "startTime": 12.5,
  "endTime": 17.3,
  "language": "ja",
  "publishedAt": 1680000000
}

差分取得（検索トリガー）での利用​

  • ユーザーがチャンネルを指定して検索するたびに、channels.updated_at と現在時刻の差を確認
  • 一定時間（24時間）以上経過している場合、channels.list でチャンネル最新メタ（title / handle / thumbnailUrl / videoCount / country）を取得し、channels テーブルを refresh
  • 続いて YouTube Data API v3 の publishedAfter で updated_at 以降の新規動画があるか確認
  • 新規動画があれば videos に INSERT（subtitle_status = 'pending'）し、字幕の差分取得を開始
  • 差分取得中も既存のインデックス済み動画は検索可能
  • 差分取得が不要（新規動画なし or 24時間未経過）な場合は即座に検索結果を返す
代替案不採用理由
1日1回のcronバッチで全チャンネルを更新登録チャンネル数が増えるとバッチの実行時間・API消費が膨大。検索されないチャンネルも更新してしまう
ユーザーが手動で「更新」ボタンを押すユーザーが新動画の存在を知らないと押さない。自動の方がUXが良い

フロー:

  1. channels.updated_at で最終取得日時を管理
  2. 検索時に updated_at から24時間以上経過しているかチェック
  3. 経過している場合、まず channels.list でチャンネル最新メタを取得し channels テーブルを refresh（country も最新化される）
  4. publishedAfter で updated_at 以降の動画のみ取得
  5. 新規動画を videos に INSERT（subtitle_status = 'pending'）
  6. 字幕取得後に Meilisearch に保存、subtitle_status を更新（channelCountry は 3. で refresh された値を使う）
  7. channels.updated_at を更新

ユーザーへの提示​

差分取得は「検索の副作用として自動発火する」非自明な挙動のため、ユーザーに明示する。

  • 検索フォームの近傍にツールチップで「検索時に最新動画を自動チェックします（最大24時間ごと）」と示す
  • 差分取得の発火はレスポンスをブロックしない（fire-and-forget）ため、既存のインデックス済み動画は即座に検索結果に表示される
  • 新規動画の字幕取得は数十秒〜数分かかるため、結果への反映は次回以降の検索で確認できる
  • クォータ枯渇時は差分取得をスキップし、既存データで検索を継続する（channels.updated_at は更新しない → 次回検索で再試行）
  • FAQ に「新しい動画はすぐに検索できますか？」エントリを設け、24時間サイクルでチェックが走ることを説明する

検索フィルターにおける「未設定」値の扱い​

検索フィルターでユーザーが値を指定できる各フィールドは、元データが NULL（YouTube API から取得できなかった、もしくは投稿者が非公開にしている）の状態が発生しうる。フィールドごとに未設定率が大きく異なるため、UX 対応を個別に決めている。

フィールド元データの NULL 率UI における扱いMeilisearch フィルタの扱い
channels.country（投稿元の国・地域）約 30%（Filmot 実測参考値）。YouTube チャンネルオーナーの任意入力項目「未設定」を明示ファセットとして常に表示。ユーザーが選択可能（選択時は NULL のチャンネルに所属する動画のみを抽出）channelCountry NOT EXISTS（Meilisearch 1.8+ の NULL 対応 filter）
videos.category_id（カテゴリ）< 1%（YouTube API はほぼ常に返す）ファセットに出さない。NULL の動画はカテゴリフィルタで非マッチ扱いcategoryId IN [...] 指定時のみ、NULL は除外される
subtitles.language（字幕言語）< 1%（字幕取得時に言語コード決定済み）ファセットに出さない。NULL は発生しない想定language IN [...] 指定時のみ、NULL は除外される
videos.view_count, like_count（数値 Range）view_count はほぼ 0%、like_count は投稿者が非公開の場合 NULL（数%）Range スライダーは 0 をデフォルトとし、NULL は範囲判定に含めないviewCount >= X AND viewCount <= Y 指定時、NULL は Meilisearch filter で自動的に除外される

設計判断の理由:

  • channelCountry だけ「未設定」を表のファセットに出すのは、未設定率が高く（約 30%）ユーザー体験上ノイズになるため。Filmot でも「N/A 265件 / Japan 513件」と件数を併記する方式を採用している。他フィールドは未設定率が極めて低いので「NULL = 非マッチ」扱いで UX に問題は出ない。
  • YouTube API の仕様変更で未設定率が変わった場合は本表を更新する。

詳細な UI 設計は 投稿元の国・地域フィルター を参照。

動画メタデータの更新・削除​

課題​

差分取得（検索トリガー）は新規動画の追加のみを対象としている。以下のケースが未対応:

  1. YouTubeで動画タイトルが変更された場合、Turso の videos.title と Meilisearch のインデックスが古いままになる
  2. YouTubeから動画が削除/非公開にされた場合、検索結果に存在しない動画が表示され、埋め込みプレーヤーがエラーになる

検出方法: YouTube oEmbed API​

両方のケースを YouTube oEmbed API（https://www.youtube.com/oembed?url=...&format=json）で検出する。

  • Data API v3 のクォータを消費しない（通常の HTTP リクエスト）
  • レスポンスに title を含むため、タイトル変更の検出に利用可能
  • 動画が削除/非公開の場合は 404 を返すため、削除検出にも利用可能
  • レート制限は Data API v3 よりはるかに緩い

検出タイミング: 動画詳細ページ（/videos/:id）アクセス時​

  • ユーザーが動画詳細ページにアクセスした際に oEmbed API を呼び出す
  • 検索結果一覧ではチェックしない（検索のたびに全ヒット動画を oEmbed で確認するのはリクエスト数が多すぎる）
  • 検索結果一覧のタイトルは多少古い可能性があるが、詳細ページでは常に最新を表示する

ユーザーへの提示（メタデータ検証）​

  • oEmbed チェックは非同期で走るため、動画詳細ページのレスポンスはブロックされない
  • タイトル変更は次回アクセス以降に反映される（当該リクエストでは旧タイトルが返る）
  • 削除/非公開動画は検出後、検索結果から除外される（Meilisearch のドキュメントが削除される）
  • 一時障害（5xx）時はスキップされ、次回アクセス時に再試行される
  • FAQ に「YouTube 側で動画が削除・非公開になった場合は？」エントリを設け、自動検出される旨を説明する
代替案不採用理由
差分取得時に全動画のメタデータを playlistItems.list で再取得API クォータ消費が大きい（1000本のチャンネルで20ユニット）。差分取得は検索のたびに走るため、クォータを圧迫する
週次バッチで全動画を一括チェック動画数が増えると oEmbed API へのリクエスト数が膨大になる。詳細ページアクセス時の遅延チェックで十分

メタデータ更新・削除検出フロー​

タイトル変更時の処理​

  1. oEmbed API のレスポンスから title を取得
  2. Turso の videos.title と比較
  3. 差異がある場合:
    • videos.title を更新
    • Meilisearch のドキュメントの title フィールドを更新
    • videos.updated_at を更新

動画削除/非公開時の処理​

  1. oEmbed API が 404 を返した場合、動画が削除/非公開と判断
  2. 即座に実行:
    • videos.subtitle_status を deleted に更新
    • Meilisearch から該当動画のドキュメントを削除（検索結果に表示されなくなる）
  3. ユーザーへの表示: 「この動画は YouTube から削除されたか非公開になっています」メッセージ
  4. 月次バッチで物理削除:
    • subtitle_status = 'deleted' の videos レコードを Turso から DELETE
    • 対応する Meilisearch のドキュメントを削除

videos.subtitle_status 状態遷移（更新）​

※ deleted 状態の動画は Meilisearch から即座に削除されるため、検索結果には表示されない。Turso のデータは月次バッチで物理削除する。

月次バッチへの追加​

既存の月次バッチ（技術スタック 参照）に以下のジョブを追加:

#処理概要
4削除済み動画の物理削除videos で subtitle_status = 'deleted' のレコードを Turso から DELETE し、対応する Meilisearch ドキュメントを削除

※ 処理順序は 1 → 2 → 3 → 4（既存ジョブの後に追加）

削除済み動画の月次物理削除フロー​

注意事項​

  • oEmbed API の呼び出しは動画詳細ページのレスポンスをブロックしない（非同期で実行し、結果を反映）
  • oEmbed API が一時的にエラー（5xx）を返した場合は、タイトル更新・削除検出をスキップする（次回アクセス時に再試行）
  • チャンネル全体が削除された場合のチャンネルレベルの対応は、初期フェーズではスコープ外とする（個別動画の検出で十分カバーされる）

チャンネル字幕の初回取得フロー​

トリガー​

  • ユーザーが検索時にチャンネルURL/IDを指定し、そのチャンネルがMeilisearchに未登録の場合に初回取得を開始
  • 同じチャンネルへの同時リクエストは1回の取得に集約（重複排除）

取得フロー​

  1. YouTube Data API v3 でチャンネルの動画一覧を取得（動画数・各動画の長さを含む）
  2. 動画数と総再生時間から推定完了時間を算出し、クライアントに返す
  3. youtube-transcript-plus で各動画の字幕を順次取得
  4. 取得完了した字幕を順次 Meilisearch にインデックス
  5. インデックス済みの動画は即座に検索対象になる（部分的な検索結果を先に表示可能）

進捗通知の方式​

  • SSE（Server-Sent Events） でリアルタイム進捗をクライアントに送信
    • 送信データ: { completed: number, total: number, estimatedRemainingSeconds: number }
    • SSEを選ぶ理由: サーバー→クライアントの一方向通知で十分。WebSocketより実装がシンプル

推定完了時間の算出​

  • 1動画あたりの字幕取得時間を約3秒と仮定（youtube-transcript-plusのリトライ含む）
  • 推定時間 = 動画数 × 3秒（初回表示時）→ 実際の取得速度で動的に補正

インプロセスキューの設計​

  • Honoサーバーのプロセス内でキュー（Map<channelId, FetchJob>）を管理
  • 同時に字幕取得を実行するチャンネル数を制限（YouTube APIのレート制限を考慮）
  • 採用理由: 初期フェーズではインフラを追加せずシンプルに実装できる。スケールが必要になった時点でRedis/外部キューに移行可能
代替案不採用理由
Redis + BullMQ外部依存が増える。初期フェーズではインプロセスで十分
Cloudflare QueuesバックエンドがFly.ioなのに字幕取得だけCloudflareにする利点がない

SSE切断時の挙動​

  • クライアントがSSE接続を切断しても、バックグラウンドで字幕取得を継続する
  • 理由: 字幕データはサービス全体の共有リソースであり、途中で止めるとMeilisearchに不完全なデータが残る。他のユーザーも同じチャンネルを検索する可能性がある
  • クライアントが再接続した場合: channels テーブルの subtitle_status と videos テーブルの取得状況を参照し、現在の進捗をSSEで返す
  • 取得完了後に再接続した場合: subtitle_status === 'completed' なので即座に検索結果を返す

同時リクエストの重複排除​

  • インメモリの Map<channelId, FetchJob> で管理。FetchJob は進捗情報とSSEクライアントリストを保持
  • 新しいリクエストが来た時の分岐:
    1. Map にジョブが存在 → 既存ジョブのSSEクライアントリストに追加し、現在の進捗を返す（新規取得を開始しない）
    2. Map にジョブがない & DBの subtitle_status === 'completed' → 差分チェックに進む
    3. Map にジョブがない & DBにレコードがない → 新規取得を開始
    4. Map にジョブがない & DBの subtitle_status === 'processing' → サーバー再起動後のケース。取得を再開
  • SSEの「合流」: 既存ジョブに複数のSSEクライアントを接続し、進捗更新を全クライアントにブロードキャストする

YouTube API クォータ管理戦略​

クォータ消費の全体像​

操作使用APIクォータ消費タイミング
チャンネル情報取得channels.list (Data API v3)1ユニット/リクエスト初回取得時
動画一覧取得playlistItems.list (Data API v3)1ユニット/リクエスト（50件ずつ）初回取得・差分チェック
字幕取得youtube-transcript-plus（非公式）消費しない初回取得・差分取得
Meilisearch検索なし消費しない検索時

youtube-transcript-plus がクォータを消費しない理由​

youtube-transcript-plus は YouTube Data API v3 を使用せず、YouTube の非公式 Innertube API を通じて字幕を取得する。具体的には以下の3段階の HTTP リクエストで動作する:

  1. YouTube 動画ページの HTML を GET で取得
  2. Innertube API（/youtubei/v1/player）に POST でキャプショントラック情報を取得
  3. キャプショントラックの URL から字幕データを GET で取得

これらはいずれもブラウザが動画を再生する際と同じリクエストであり、Google Cloud Console の API クォータとは無関係に動作する。そのため、字幕取得がどれだけ増えても Data API v3 の 10,000 ユニット/日の制限には影響しない。

非公式API（youtube-transcript-plus）のリスクと対策​

字幕取得は Data API v3 のクォータを消費しないが、非公式APIであるため以下のリスクがある:

リスク説明対策
429 レート制限短時間に大量リクエストを送るとYouTubeが429を返すyoutube-transcript-plus 内蔵の exponential backoff で自動リトライ。加えてインプロセスキューでの並列数制限（同時取得チャンネル数を制限）で発生頻度を下げる
IP BAN過度なスクレイピングとみなされサーバーIPがブロックされるリクエスト間隔を設ける（動画間に1〜3秒のインターバル）。Fly.io のIPが固定のため、BANされた場合はIPアドレスの変更（再デプロイ or 別リージョン）で対応
非公式APIの仕様変更YouTube が Innertube API のエンドポイントやレスポンス形式を変更するyoutube-transcript-plus のアップデートに追従。ライブラリが対応するまで字幕取得は一時停止し、既存データでの検索は継続可能
代替案不採用理由
プロキシローテーション初期フェーズでは過剰。IP BAN が実際に問題になった時点で検討する
YouTube Data API v3 の captions.download動画の所有者しか字幕をダウンロードできないため利用不可

Data API v3 クォータの消費見積もり​

  • channels.list: チャンネル情報の取得 → 1チャンネルあたり 1ユニット（初回のみ）
  • playlistItems.list: 動画一覧の取得 → 50件ごとに 1ユニット
    • 動画100本のチャンネル → 2ユニット
    • 動画1,000本のチャンネル → 20ユニット
    • 動画5,000本のチャンネル → 100ユニット

1日あたりの処理可能チャンネル数の見積もり（10,000ユニット上限）:

チャンネル規模初回取得差分チェック
100本（小規模）〜3,300チャンネル/日〜5,000チャンネル/日
500本（中規模）〜900チャンネル/日〜5,000チャンネル/日
1,000本（大規模）〜470チャンネル/日〜5,000チャンネル/日

※ 差分チェックは publishedAfter で新規動画のみ取得するため、ほとんどのケースで1〜2リクエスト（1〜2ユニット）で済む

初回取得と差分チェックの優先順位​

クォータが逼迫した場合の優先順位:

  1. 差分チェック（優先）: 1チャンネルあたり1〜2ユニットと低コスト。既存ユーザーの検索体験を維持するために優先
  2. 初回取得: 大規模チャンネルだと数十〜数百ユニットを消費する可能性がある。クォータ残量が少ない場合は新規チャンネルの初回取得をキューに入れたまま翌日に持ち越す

クォータ残量の追跡​

  • サーバー内でインメモリカウンター（日次リセット）により消費ユニット数を追跡
  • YouTube Data API v3 のレスポンスヘッダーではクォータ残量を取得できないため、自前でカウントする
  • サーバー再起動時にカウンターは0にリセットされるが、安全側に倒れる（実消費より少なくカウント → 枯渇検知が遅れる可能性があるが、枯渇時は API が 403 を返すため検出可能）
代替案不採用理由
DB永続化でカウンター管理サーバー再起動は稀。403エラーで枯渇を検出できるため、正確な追跡は不要
Google Cloud Console API で残量取得追加のAPI呼び出しが必要。インメモリカウンターで十分

クォータ枯渇時のフォールバック​

Data API v3 が 403（quotaExceeded）を返した場合の挙動:

  • 既存チャンネルの検索: 影響なし。Meilisearch への検索は Data API v3 を使わないため、通常通り動作する
  • 差分チェック: 停止。ユーザーには通知せず、既存のインデックス済み動画で検索結果を返す（「最新の動画が反映されていない可能性があります」等のバナーは表示しない。24時間以内に自動復旧するため）
  • 新規チャンネルの初回取得: 停止。ユーザーにエラーメッセージ:「現在新しいチャンネルの登録を一時停止しています。しばらく経ってからもう一度お試しください」
  • 翌日の太平洋時間 0:00 にクォータが自動リセットされ、キューに残っているジョブから処理を再開
代替案不採用理由
複数 API キーのローテーションGoogle の利用規約上グレーゾーン。初期フェーズでは単一キーで十分な処理量がある
YouTube Data API の有料クォータ拡張申請初期フェーズでは 10,000 ユニット/日で十分。ユーザー数が増加した時点で申請を検討

サーバー再起動時の挙動​

  • インメモリのキューは失われるが、DBの channels.subtitle_status に状態が残る
  • subtitle_status === 'processing' のままのレコードは、次回検索時に検出:
    • videos テーブルで subtitle_status === 'pending' の動画から取得を再開
    • Meilisearchに既にインデックスされた動画はスキップ
  • 採用理由: サーバー再起動は稀なイベントであり、自動復旧ではなく次回検索時のリカバリで十分

取得失敗時のリカバリ​

  • 個別動画の失敗: videos.subtitle_status を failed に更新してスキップ。チャンネル全体の取得は継続
  • 字幕が存在しない動画: videos.subtitle_status を unavailable に更新。リトライ対象外
  • チャンネル全体の失敗（YouTube API制限等）:
    • channels.subtitle_status を failed に更新
    • ユーザーにエラーメッセージ:「字幕の取得に失敗しました。しばらく経ってからもう一度検索してください」
    • 次回検索時に subtitle_status === 'failed' を検出し、取得を再試行する

アカウント削除フロー​

削除方式: 即時削除（猶予期間なし）​

  • アカウント削除リクエスト後、即時にデータを物理削除する
  • 採用理由:
    • サービスの性質上、ユーザーが生成するデータは少ない（お気に入り・アクセス履歴程度）。誤削除のリカバリ需要が低い
    • 字幕データ自体はチャンネル単位でMeilisearchに保持されており、ユーザー削除の影響を受けない
    • GDPRの「削除権（Right to Erasure）」に最もシンプルに対応できる
    • 猶予期間を設けると、猶予中のクレジット消費・ログイン可否・Stripe課金停止タイミング等の複雑な状態管理が必要になる
代替案不採用理由
30日間の猶予期間（論理削除 → 物理削除）猶予中の中間状態（ログイン可否、クレジット消費可否、Stripe課金停止タイミング）が複雑。ユーザーデータが少ないため復旧需要も低い
論理削除のみ（deleted_at カラム追加）GDPRの物理削除要件を満たさない。全クエリに WHERE deleted_at IS NULL が必要になり複雑化

処理フロー（単一トランザクション + 外部API）​

  1. 確認UI: 削除確認ダイアログで「アカウントを削除すると、すべてのデータが即座に削除され、元に戻せません。」と表示。パスワード入力やメールアドレス再入力は不要（Google Auth のみのため）。代わりに「削除する」ボタンを2段階にする（1回目で確認文表示、2回目で実行）
  2. Stripeサブスクリプションの即時解約（課金ユーザーのみ）
    • stripe.subscriptions.cancel() で即時キャンセル（cancel_at_period_end ではなく即時）
    • 理由: アカウント削除はユーザーの明確な意思表示であり、期間終了まで待つ必要がない。日割り返金はStripeのデフォルト動作に従う（返金なし）
    • Stripe API呼び出しが失敗した場合 → 削除処理全体を中止し、エラーメッセージを表示（「しばらく経ってからもう一度お試しください」）
  3. DBデータの物理削除（単一トランザクション）
    • 削除順序（FK制約を考慮）:
      1. favorite_channels — user_id で DELETE（または ON DELETE CASCADE で自動）
      2. user_video_access — user_id で DELETE
      3. billing_accounts — user_id で DELETE
      4. sessions — Better Auth のセッションを user_id で DELETE
      5. accounts — Better Auth のOAuth連携を user_id で DELETE
      6. users — ユーザーレコードを DELETE
    • 途中で失敗した場合は全てロールバック（Stripeは既に解約済みだが、後述の整合性チェックで対応）
  4. セッション無効化: Better Auth のセッションを破棄し、クライアントをトップページにリダイレクト

Stripe解約済み + DB削除失敗時の整合性​

  • Stripeは既に解約済みだがDB削除が失敗した場合:
    • ユーザーにエラーを表示し、再試行を促す
    • 再試行時にStripeのサブスクリプション状態を確認し、既にキャンセル済みならStep 2をスキップしてStep 3から実行
    • Stripeのサブスクリプションがキャンセル済みかどうかは stripe.subscriptions.retrieve() で status === 'canceled' を確認

GDPR・データ削除要件への対応​

  • 削除権（Right to Erasure, GDPR第17条）: 即時物理削除で対応
  • 削除対象データ:
    • users: メールアドレス、名前、プロフィール画像URL
    • accounts: OAuth プロバイダーID
    • sessions: セッショントークン
    • user_video_access: 視聴履歴（どの動画にアクセスしたか）
    • billing_accounts: プラン・クレジット残高・Stripe 顧客
    • favorite_channels: お気に入りチャンネル
  • 削除対象外データ:
    • Meilisearch の字幕データ: チャンネル単位で保持されており、個人データではない
    • Stripeの顧客情報: Stripe側に残る（Stripeの保持ポリシーに従う。必要に応じて stripe.customers.del() で削除可能だが、経理・税務上の理由で保持が推奨される）
  • 削除ログ: 削除したユーザーIDと削除日時をサーバーログに記録する（「いつ削除リクエストに対応したか」の証跡。個人情報自体はログに含めない）

ライブ配信チャットコメント検索（v0.6）​

取得フロー​

YouTube ライブ配信のアーカイブに残るチャットコメント（Chat Replay）を検索対象に加える。

1. 動画メタ取得時に liveStreamingDetails パートを併せて取得し、`videos.video_type` を
   "regular" / "live_archive" に分類（追加 YouTube Quota 消費なし）
2. video_type='live_archive' の新規動画は upsertVideos 時に
   chat_status='pending' で挿入
3. ChatFetchQueue が pending の videoId を消費して masterchat
   （Innertube API live_chat/get_live_chat_replay）から Chat Replay を取得
4. 取得結果を Meilisearch chat_messages インデックスに addDocuments で投入
   （1000件単位のバッチ）
5. 完了後 videos.chat_status='completed' + videos.chat_message_count を更新

保存方式​

  • Meilisearch インデックス: chat_messages（字幕とは別）
  • ドキュメント主キー: {videoId}_chat_{連番}
  • searchable: message, authorName, videoTitle
  • filterable: videoId, channelId, authorChannelId, type, publishedAt
  • sortable: offsetMs, publishedAt

コスト試算​

規模1配信あたり備考
1配信のチャット件数数千〜数十万件人気配信は数十万に達する
1メッセージのドキュメント~300Bmessage + author + timestamp + meta
1配信あたり Meilisearch 容量1〜30MB字幕より1桁以上大きい

字幕の 5〜50KB と比べて 1〜3 桁大きいため、ストレージ・処理コスト的にチャット込み検索は字幕のみの 2倍クレジット（calculateCreditCost(duration, { includeChat: true })）を消費する設計とした。

差分取得が不要な理由​

  • ライブ配信のアーカイブ Chat Replay は配信終了後に確定（後から増減しない）
  • 一度 chat_status='completed' になればポーリング不要
  • 字幕と異なりチャンネル単位の差分追跡は不要（ライブ配信が新しく公開される都度 fetch-channel-diff が新規 video として検出する）

制約事項​

  • Chat Replay 無効な配信: 配信者が Replay を無効化している場合は chat_status='unavailable' で確定し、リトライ対象外（永続的扱い）
  • 取得失敗: ChatFetchQueue が in-memory で 3 回まで指数バックオフリトライし、 全失敗で chat_status='failed'（次回 Worker 再起動後の pending 再投入で復旧）
  • 大規模配信の上限: fetchChatReplay は maxMessages で取得上限を設けられる （メモリ消費・Meilisearch 投入コストの暴発防止）