データベーススキーマ

Subseek で使用する全テーブルの定義。ORM は Drizzle ORM、DB は Turso（SQLite）。

認証（users / sessions / accounts / verifications）と Stripe サブスクリプション（subscriptions）は Better Auth が管理するテーブル。アプリ側のマイグレーションでスキーマは管理されるが、書き込みは原則として Better Auth のフロー経由で行われる（users.country / users.credit_balance のようなアプリ固有カラム除く）。

全体の位置付け（Turso と Meilisearch の使い分け）は データベース概要 を参照。各テーブルの「カラムがいつ更新されるか」を俯瞰するには Turso 更新タイミング を参照。

ER図

テーブル定義

users

Better Auth が管理するテーブル。Better Auth Stripe プラグイン導入に伴い、stripe_customer_id と credit_balance は旧 billing_accounts テーブルから本テーブルに移行されている（マイグレーション 0007）。

カラム	型	制約	デフォルト	説明
id	TEXT	PK, NOT NULL	-	ユーザーID（Better Auth 採番）
name	TEXT	NOT NULL	-	表示名（Google OAuth から取得）
email	TEXT	NOT NULL, UNIQUE	-	メールアドレス
email_verified	INTEGER (bool)	NOT NULL	false	メール検証フラグ
image	TEXT	-	-	プロフィール画像 URL
role	TEXT	NOT NULL	'member'	権限ロール（member / admin）。DMCA テイクダウン等の管理操作に利用
stripe_customer_id	TEXT	-	-	Stripe Customer ID。Better Auth Stripe プラグイン createCustomerOnSignUp で自動生成
credit_balance	INTEGER	NOT NULL	50 (Free プラン上限)	現在のクレジット残高。プラン変更時・月次バッチ・クレジット消費時に更新
bonus_credit	INTEGER	NOT NULL	0	ボーナスクレジット残高。紹介報酬等で付与され、通常クレジットより先に消費される。月初リセットの対象外で、使い切るまで残り続ける
referral_code	TEXT	UNIQUE	-	自分の紹介コード（URL-safe 8 文字）。ユーザー作成時に databaseHooks.user.create.after で生成・設定。ユーザー間での共有はコード文字列のまま（URL 形式での共有は現状サポートしない）
referred_by	TEXT	FK → users.id	-	紹介者の users.id。Plus 加入前の「紹介コード入力」フォームで確定する。自己参照 FK、ON DELETE SET NULL（紹介者が退会しても被紹介者のアカウントは残す）。自己参照禁止（referred_by != id）はアプリ層で検証
referral_rewarded_at	TEXT	-	-	紹介報酬の付与日時（ISO 8601）。Plus 加入完了の Stripe Webhook で現在時刻を設定。referred_by IS NOT NULL AND referral_rewarded_at IS NULL の行のみ付与対象となり、IS NOT NULL なら冪等にスキップ
country	TEXT	-	-	ユーザーの国（ISO 3166-1 alpha-2、例: "JP", "US"）。後述の自動設定ルール参照
created_at	INTEGER (ms)	NOT NULL	unixepoch('subsecond') * 1000	作成日時（Unix ミリ秒）
updated_at	INTEGER (ms)	NOT NULL	同上、$onUpdate あり	更新日時（Drizzle が自動更新）

country の自動設定ルール:

• 供給元: Cloudflare Workers の CF-IPCountry ヘッダー（ISO 3166-1 alpha-2）
• 設定タイミング: 認証済み API リクエスト時に countryBackfillMiddleware が実行され、country IS NULL の場合にのみ書き込む（初回書き込みのみ）
• 冪等性: 既に値が入っているユーザーは上書きしない（where(and(eq(users.id, userId), isNull(users.country))) で絞り込み）
• NULL のままになるケース:
  • CF-IPCountry が XX（Cloudflare が判別不能） または T1（Tor exit ノード）を返した場合
  • ローカル開発環境など CF-IPCountry ヘッダーが供給されない場合
• DB 更新失敗時の挙動: エラーは console.warn で記録のみ。認証フローをブロックしない

更新タイミング:

カラム	書き込まれるフロー	備考
id / name / email / image / email_verified	Google OAuth サインアップ時（Better Auth）	以降は Better Auth updateUser or プロフィール同期時
role	作成時 'member'	admin 手動で admin に昇格
stripe_customer_id	Better Auth Stripe createCustomerOnSignUp	初回のみ。以降は変更なし
credit_balance	① 消費時（consumeCreditsTransaction で優先的に減算。不足分はその後 bonus_credit から） ② プラン変更時（onSubscriptionComplete / onSubscriptionUpdate / onSubscriptionDeleted が resetCredits を呼ぶ） ③ 月次バッチ（resetAllCredits が全ユーザーを 50/500/2000 に一括 UPDATE）	consumeCreditsInTransaction で同一トランザクション内に減算
bonus_credit	① 消費時（consumeCreditsTransaction が credit_balance を使い切った後の不足分を減算） ② 付与時（grantBonusCredits が加算。紹介報酬成立・キャンペーン等）	月次バッチ（resetAllCredits）の対象外で温存される
referral_code	作成時（databaseHooks.user.create.after で自動生成、UNIQUE 衝突時は最大 3 回リトライ）	既存ユーザーはバックフィルスクリプトで付与。生成後は再生成しない
referred_by	紹介コード入力時（POST /api/referrals/claim で自分の referred_by をセット）	既に referred_by がセット済みのユーザーは上書き不可。自己参照（referred_by = id）はアプリ層で拒否
referral_rewarded_at	Plus 以上の新規加入時（Stripe Webhook onSubscriptionComplete 内で grantBonusCredits 呼び出しと同時に設定）	IS NOT NULL の行は報酬付与済みとして以降の Webhook ではスキップ
country	認証済みリクエスト初回のみ（countryBackfillMiddleware）	既存値は上書きしない
created_at	作成時	変更なし
updated_at	Drizzle の $onUpdate が任意 UPDATE 時に自動更新	-

ボーナスクレジット

users.bonus_credit は通常クレジット（credit_balance）とは独立した残高。紹介報酬（#111）をはじめ、キャンペーン・補填など汎用的なボーナス管理基盤として用いる。

Why 通常クレジットと分離するか:

• 紹介報酬のように付与されるクレジットは、月初リセットの対象外にしたい。credit_balance と同じカラムで管理すると「リセット時に区別して温存する」ロジックが必要になり、月次バッチが複雑化する
• bonus_credit を別カラムに分離すると、resetAllCredits は credit_balance のみを UPDATE すればよく、bonus_credit は自動的にリセット対象外になる

消費順序（通常 → ボーナス）:

クレジット消費時は credit_balance を優先的に減算し、不足分を bonus_credit から引く。

• 1 動画のコスト（例: calculateCreditCost(duration) = 3）が通常残高を超える場合も、通常を使い切ってからボーナスから残りを引く（credit 単位で分割可能）
• 通常クレジットは月初リセットで失われる（use-it-or-lose-it）ため先に消費し、永続するボーナスを温存する設計
• ボーナスはヘビー利用月のバッファとして機能し、Plus 解約 → Free ダウングレード後も残るため、churn prevention 的な価値も持つ
• 実装: apps/api/src/features/credit/model/consume-credits-transaction/consume-credits-transaction.ts

付与方法:

grantBonusCredits(db, { userId, amount }) で加算する。

条件	挙動
amount > 0 の整数	users.bonus_credit += amount を実行
amount === 0	no-op（DB アクセスなし）
amount < 0 / 非整数 / NaN / Infinity	Error を throw
対象ユーザーが存在しない	Error を throw（rowsAffected === 0 を検知）

本関数は排他ロックを持たない。紹介報酬（#111）のように Webhook 経由で呼ぶ場合は、呼び出し側で 冪等性チェック（例: referrals.status === 'completed' の行はスキップ）を行うことで二重加算を防ぐ。

月初リセットでの扱い:

resetAllCredits（月次バッチ batch-monthly-reset.yml から呼ばれる）は credit_balance のみを UPDATE する。bonus_credit は一切触らないため、使い切るまで残高が維持される。

用途:

• 紹介報酬（#111）: 紹介された側が Plus 以上に加入した際、紹介者と被紹介者の双方に 100Cr を付与
• キャンペーン / 補填: 運用上の判断で特定ユーザーにクレジットを付与する場合も同じ関数を使える

紹介機能

紹介者が自分のリンクをシェアし、紹介された側が Plus 以上に加入すると双方にボーナスクレジット 100Cr が付与される仕組み。users テーブルの 3 カラム（referral_code / referred_by / referral_rewarded_at）のみで実装される。

Why referrals テーブルではなくカラム方式:

• F1 方式（Plus 加入前のフォームで紹介コード入力）では、pending 状態を別テーブルで管理する必要が薄い
• 「誰が誰を紹介したか」の中間履歴は不要で、最終的な関係（referred_by）と付与済みフラグ（referral_rewarded_at）さえあれば MVP 要件を満たせる
• 紹介者側の「何人紹介したか」は SELECT COUNT(*) FROM users WHERE referred_by = ? AND referral_rewarded_at IS NOT NULL で算出できる

紹介コード（referral_code）:

• URL-safe アルファベット（A-Za-z0-9_-）で 8 文字のランダム文字列（REFERRAL_CODE_LENGTH）
• ユーザー作成時に databaseHooks.user.create.after で自動生成
• UNIQUE 制約に衝突した場合は最大 3 回リトライ（64^8 ≈ 2.8 × 10^14 通りで実用上衝突はほぼ発生しない）
• 一度生成したら再生成しない（シェア済みのコードが無効化されないように）

紹介の成立フロー:

1. 紹介者 A が自分の `referral_code` をシェア（メッセージ・SNS・メール等）
2. 被紹介者 B がサイトに来てサインアップ（普通の Google OAuth フロー。Cookie / middleware による追跡はしない）
3. B が Plus 加入画面で「紹介コード（任意）」フォームに A のコードを入力
4. POST /api/referrals/claim { code } が呼ばれる
   - サーバー側: referral_code から A を特定し、B 自身の referred_by に A.id をセット
   - 自己参照（A.id === B.id）や既に referred_by がある場合はエラー
5. B が Stripe チェックアウトに遷移して Plus に加入
6. onSubscriptionComplete（Stripe Webhook）が発火
   - B.referred_by IS NOT NULL かつ B.referral_rewarded_at IS NULL のとき:
     - grantBonusCredits(A, REFERRAL_REWARD_CREDITS) → A.bonus_credit += 100
     - grantBonusCredits(B, REFERRAL_REWARD_CREDITS) → B.bonus_credit += 100
     - B.referral_rewarded_at = now()

冪等性:

• ステップ 6 は referral_rewarded_at IS NULL を条件にするため、Webhook が再送された場合や既に付与済みの場合はスキップされる
• トランザクション内で「B の referral_rewarded_at を UPDATE → rowsAffected をチェック」で原子性を担保し、並行 Webhook による二重付与を防ぐ

自己参照 / 既存ユーザーの扱い:

• B が自分のコードを入力しても referred_by = id になるため claim API 側で拒否する（DB レベルの CHECK 制約は置かない。アプリ層で検証してユーザーに分かりやすいエラーを返す）
• referred_by が既に設定済みの B は claim API 側で上書き拒否する
• 紹介コード入力は Plus 未加入のユーザーのみ可能（加入後は報酬付与されないため UI で隠す + API でも拒否）

channels

YouTube チャンネルのメタデータを管理する。

カラム	型	制約	デフォルト	説明
id	TEXT	PK, NOT NULL	-	YouTube チャンネルID（UCxxxxxx）
title	TEXT	NOT NULL	-	チャンネル名
handle	TEXT	-	-	カスタムURL（@handle）
thumbnail_url	TEXT	-	-	サムネイル画像URL
subtitle_status	TEXT	NOT NULL	'pending'	字幕取得状態: pending / processing / completed / failed / deleted / opted_out
video_count	INTEGER	NOT NULL	0	動画数（YouTube API から取得）
country	TEXT	-	-	運営者の所在国（ISO 3166-1 alpha-2、例: "JP"、運営者未公開の場合 null）
subscriber_count	INTEGER	-	-	登録者数（YouTube 側で非公開設定のチャンネルは null）
created_at	TEXT	NOT NULL	new Date().toISOString()	作成日時
updated_at	TEXT	NOT NULL	new Date().toISOString()	更新日時

インデックス:

インデックス名	カラム
idx_channels_title	title
idx_channels_handle	handle
idx_channels_subtitle_status	subtitle_status

更新タイミング:

カラム	初回登録時	24h 差分取得時（#305 / fetchChannelDiff）	備考
id	設定	変更なし	-
title	設定	再フェッチ値で UPSERT	オーナーによる変更に追従
handle	設定	再フェッチ値で UPSERT	カスタム URL 変更に追従
thumbnail_url	設定	再フェッチ値で UPSERT	アイコン変更に追従
subtitle_status	'pending'	触らない	state machine は字幕取得フロー側で遷移
video_count	設定	再フェッチ値で UPSERT	動画追加/削除に追従
country	設定	再フェッチ値で UPSERT	オーナーが後から設定しても追従（#305 以降）
subscriber_count	設定（statistics.subscriberCount、非公開時 null）	再フェッチ値で UPSERT	channels.list?part=statistics が既存フローで呼ばれており追加クォータコスト 0。値が変動した場合は当該チャンネルの全 SubtitleDocument も updateDocuments（partial update）で追従更新（#84）
created_at	設定	変更なし	-
updated_at	設定	成功時のみ now に更新	クォータ失敗時は触らず 24h ロックを解除

videos

動画メタデータ・字幕取得状態を管理する。

カラム	型	制約	デフォルト	説明
id	TEXT	PK, NOT NULL	-	YouTube 動画ID（11文字）
channel_id	TEXT	NOT NULL, FK → channels.id	-	所属チャンネルID
title	TEXT	NOT NULL	-	動画タイトル
thumbnail_url	TEXT	-	-	サムネイル画像URL
duration_seconds	INTEGER	NOT NULL	-	動画の長さ（秒）
view_count	INTEGER	-	-	再生回数（videos.list?part=statistics。取得失敗時 null）
like_count	INTEGER	-	-	高評価数（統計非公開の動画は null）
published_at	TEXT	NOT NULL	-	公開日時（ISO 8601）
category_id	TEXT	-	-	YouTube カテゴリ ID（snippet.categoryId。例: "1", "10"）
subtitle_status	TEXT	NOT NULL	'pending'	字幕取得状態: pending / completed / failed / unavailable / deleted
subtitle_type	TEXT	-	-	字幕タイプ（"manual" / "auto" / null）
created_at	TEXT	NOT NULL	new Date().toISOString()	作成日時
updated_at	TEXT	NOT NULL	new Date().toISOString()	更新日時

インデックス:

インデックス名	カラム
idx_videos_channel_id	channel_id
idx_videos_published_at	published_at
idx_videos_subtitle_status	subtitle_status
idx_videos_channel_subtitle	channel_id, subtitle_status

subtitle_status 状態遷移:

更新タイミング:

カラム	初回取込時（upsertVideos）	以降の更新	備考
id / channel_id / published_at	設定	不変	動画に固有の属性
title	設定	更新されない	オーナーによるタイトル変更には追従しない（再インデックスで反映）
thumbnail_url	設定	更新されない	同上
duration_seconds	設定	不変	動画の長さは変動しない
view_count / like_count	取得値で設定（欠損時 null）	動画単位の定期リフレッシュは未実装	「1 年前の値」のまま固定されるため、これを使ったソートは現状ミスリードになる。更新戦略は別 Issue で議論予定
category_id	設定	更新されない	カテゴリ変更は稀なため無視
subtitle_status	'pending'	字幕取得フロー（subtitle-fetch-orchestrator）で状態遷移	pending → completed/failed/unavailable、YouTube 側削除検知で deleted
subtitle_type	null	字幕取得完了時に manual or auto を設定	completed のレコードには必ずいずれかが入る想定
created_at	設定	変更なし	-
updated_at	設定	Drizzle の $onUpdate が自動更新	-

user_video_access

ユーザーの動画アクセス権（クレジット消費履歴）を管理する。expires_at を超過したレコードは月次バッチで物理削除される。

カラム	型	制約	説明
user_id	TEXT	PK, NOT NULL, FK → users.id	ユーザーID
video_id	TEXT	PK, NOT NULL, FK → videos.id	動画ID
credits	INTEGER	NOT NULL	消費したクレジット数（動画長で加重）
created_at	TEXT	NOT NULL	作成日時
updated_at	TEXT	NOT NULL	更新日時
expires_at	TEXT	NOT NULL	アクセス権の有効期限（created_at + 4週間）

主キー: (user_id, video_id) の複合主キー

更新タイミング:

カラム	書き込まれるフロー	備考
user_id / video_id	クレジット消費時（consumeCreditsTransaction）	UPSERT の ON CONFLICT キー
credits	消費時 or 期限切れ後再消費時に UPSERT で上書き	calculateCreditCost(durationSeconds) の結果
created_at	初回消費時	UPSERT による再消費では触らない
updated_at	消費時 / 再消費時	Drizzle の $onUpdate が自動更新
expires_at	消費時 / 再消費時	now + ACCESS_TTL_MS(4週間)。再消費で延長される

削除タイミング:

• 月次バッチ（cleanupExpiredAccess、batch-monthly-reset.yml の一部）で expires_at < now の行を物理削除する

subscriptions

Better Auth Stripe プラグインが管理するサブスクリプション状態テーブル（Better Auth Stripe ドキュメント）。書き込みは全て Better Auth の Webhook ハンドラが行う。

アプリ側の users.credit_balance のリセット（プラン変更時・月次）でも参照される。

カラム	型	制約	デフォルト	説明
id	TEXT	PK, NOT NULL	Better Auth 採番	Subscription ID
plan	TEXT	NOT NULL	-	プラン名（"plus" / "pro"。"free" はこのテーブルに行を持たない）
reference_id	TEXT	NOT NULL	-	所有者の users.id（将来 organization 導入に備え FK 名を汎用化）
stripe_customer_id	TEXT	-	-	Stripe Customer ID
stripe_subscription_id	TEXT	-	-	Stripe Subscription ID
status	TEXT	-	'incomplete'	Stripe status（active / canceled / incomplete / past_due / trialing / unpaid 等）
period_start	INTEGER (ms)	-	-	現在の請求期間の開始
period_end	INTEGER (ms)	-	-	現在の請求期間の終了
trial_start	INTEGER (ms)	-	-	トライアル開始
trial_end	INTEGER (ms)	-	-	トライアル終了
cancel_at_period_end	INTEGER (bool)	-	false	期間終了時に解約するフラグ
cancel_at	INTEGER (ms)	-	-	解約予定日時
canceled_at	INTEGER (ms)	-	-	実際の解約日時
ended_at	INTEGER (ms)	-	-	サブスクリプション終了日時
seats	INTEGER	-	-	チームプランの席数（将来の組織プラン用、現在未使用）
billing_interval	TEXT	-	-	請求サイクル（"month" / "year"）
stripe_schedule_id	TEXT	-	-	Stripe Subscription Schedule ID

更新タイミング:

カラム	書き込まれるフロー	備考
全カラム	Better Auth Stripe プラグインが Stripe Webhook を受信した際に UPSERT	アプリコードからは直接更新しない（Better Auth 経由で操作）
status / period_* / cancel_* / canceled_at / ended_at	Stripe 側の状態変化を Webhook で反映	active → canceled 等の遷移はすべて Stripe イベント駆動

関連フロー:

• onSubscriptionComplete / onSubscriptionUpdate: 契約完了／プラン変更時に users.credit_balance を新プラン上限にリセット
• onSubscriptionDeleted: 解約時に users.credit_balance を Free プラン上限（50）に戻す
• 月次バッチ resetAllCredits: subscriptions.status IN ('active','trialing') + plan を参照して users.credit_balance を再計算

favorite_channels

ユーザーのお気に入りチャンネルを管理する。

カラム	型	制約	説明
user_id	TEXT	PK, NOT NULL, FK → users.id (CASCADE)	ユーザーID
channel_id	TEXT	PK, NOT NULL, FK → channels.id	チャンネルID
created_at	TEXT	NOT NULL	作成日時

主キー: (user_id, channel_id) の複合主キー

インデックス:

インデックス名	カラム
idx_favorite_channels_user_created	user_id, created_at

更新タイミング:

カラム	書き込まれるフロー	備考
全カラム	ユーザーのお気に入り追加時 INSERT、削除時 DELETE	UPDATE はしない
CASCADE 削除	親 users.id 削除時に自動削除	ユーザーアカウント削除で一緒に消える

search_histories

ユーザーの検索履歴を管理する。同一検索条件（userId, query, channels, target, sort, semantic, sr）に対してユニーク制約を持ち、重複する検索条件は searched_at を更新する。

カラム	型	制約	デフォルト	説明
id	INTEGER	PK, NOT NULL, AUTOINCREMENT	-	検索履歴ID
user_id	TEXT	NOT NULL, FK → users.id (CASCADE)	-	ユーザーID
query	TEXT	NOT NULL	-	検索キーワード
channels	TEXT	NOT NULL	''	検索対象チャンネルID一覧（カンマ区切り。全チャンネル検索時は空文字）
target	TEXT	NOT NULL	'description,subtitle,title'	検索対象（ソート済みカンマ区切り）
sort	TEXT	NOT NULL	'relevance'	並び順
semantic	INTEGER	NOT NULL	0	セマンティック検索 ON/OFF（0 / 1）
sr	REAL	NOT NULL	DEFAULT_SEMANTIC_RATIO	セマンティック強度（0〜1、無効時はデフォルト値）
searched_at	TEXT	NOT NULL	-	検索実行日時（ISO 8601）
created_at	TEXT	NOT NULL	new Date().toISOString()	作成日時
updated_at	TEXT	NOT NULL	new Date().toISOString()	更新日時

ユニーク制約:

制約名	カラム
uq_search_histories_condition	user_id, query, channels, target, sort, semantic, sr

インデックス:

インデックス名	カラム
idx_search_histories_user_searched	user_id, searched_at

更新タイミング:

カラム	書き込まれるフロー	備考
id	INSERT 時自動採番	-
user_id / query / channels / target / sort / semantic / sr	検索実行時 INSERT	ユニーク制約で同条件の 2 回目以降は UPSERT に分岐
searched_at	INSERT / 同条件再検索時の UPSERT 両方で最新時刻に更新	「最後に検索した時刻」を表す
created_at	初回 INSERT のみ	UPSERT 時は触らない
updated_at	Drizzle の $onUpdate が UPSERT 時に自動更新	-
CASCADE 削除	親 users.id 削除時に自動削除	-

ad_slots

ダイレクト広告のスロットマスタ。4 種類の slot（検索 midroll / 検索 bottom / 動画詳細 プレイヤー下 / 動画詳細 字幕下）を管理する。詳細は ダイレクト広告仕様書 §3 を参照。

カラム	型	制約	デフォルト	説明
id	TEXT	PK, NOT NULL, enum	-	search-result-midroll / search-result-bottom / video-detail-below-player / video-detail-below-subtitle
display_name	TEXT	NOT NULL	-	admin 向け表示名
created_at	TEXT	NOT NULL	new Date().toISOString()	作成日時
updated_at	TEXT	NOT NULL	new Date().toISOString()	更新日時

注記

CLS 対策用の最小表示高さは packages/shared/src/ads/ad-slot/ad-slot.ts の AD_CARD_MIN_HEIGHT = 300 を単一ソースとして参照する。§3.3 の 4-position カードグリッド統一に伴い slot 別の値を使う余地がないため、DB カラムでは持たない。将来 slot ごとに差が必要になったら Record<AdSlotId, number> 化して対応する。

更新タイミング:

カラム	書き込まれるフロー	備考
全カラム	pnpm seed 実行時 INSERT	onConflictDoNothing で冪等。運用中は変更しない静的マスタ

advertiser_profiles

広告主プロフィールマスタ。1 user が複数プロフィール（個人/法人の使い分け、代理店的な運用）を保有できる。予約作成時はこのマスタから snapshot を ad_reservations にコピーする（契約時点で固定）。詳細は ダイレクト広告仕様書 §5.2 / §10.2 を参照。

カラム	型	制約	デフォルト	説明
id	TEXT	PK, NOT NULL	-	プロフィールID (UUID)。独立 PK（将来 Organization 移行時に所有者を付け替えやすくするため）
user_id	TEXT	NOT NULL, FK → users.id	-	所有者ユーザーID（CASCADE しない、UNIQUE にしない: 1 user が複数 profile を持てる）
business_legal_name	TEXT	NOT NULL	-	法人名 or 個人名
business_address	TEXT	NOT NULL	-	事業所住所
contact_name	TEXT	NOT NULL	-	担当者氏名
contact_email	TEXT	NOT NULL	-	担当者メール
business_category	TEXT	NOT NULL, enum	-	業種カテゴリ（BUSINESS_CATEGORIES、15 種類）
invoice_registration_number	TEXT	-	-	インボイス番号 (nullable)
created_at	TEXT	NOT NULL	new Date().toISOString()	作成日時
updated_at	TEXT	NOT NULL	new Date().toISOString()	更新日時

インデックス:

インデックス名	カラム
idx_advertiser_profiles_user	user_id

更新タイミング:

カラム	書き込まれるフロー	備考
全カラム	出稿フォーム「広告主情報」の初回入力時 INSERT、同リピート時は既存 profile を選択	編集時（住所変更等）は UPDATE で当行を更新するが、既存予約の snapshot は不変

ad_reservations

ダイレクト広告の予約テーブル。4 区分（① 掲載枠 / ② クリエイティブ / ③ 広告主情報 / ④ 運用）の情報を保持する。③ 広告主情報は advertiser_profiles マスタから契約時点でコピーした snapshot を持ち、マスタ側の後続更新によっては変化しない（特商法対応等のため）。詳細は ダイレクト広告仕様書 §5 / §10.2 を参照。

カラム	型	制約	デフォルト	説明
id	TEXT	PK, NOT NULL	-	予約ID (UUID)
user_id	TEXT	NOT NULL, FK → users.id	-	広告主ユーザーID（CASCADE しない: 履歴として残す）
slot_id	TEXT	NOT NULL, FK → ad_slots.id, enum	-	スロットID
position	INTEGER	NOT NULL	-	ポジション 1〜4
country	TEXT	NOT NULL	-	国コード (ISO 3166-1 alpha-2)
start_month	TEXT	NOT NULL	-	掲載開始月 (YYYY-MM)
end_month	TEXT	NOT NULL	-	掲載終了月 (YYYY-MM, inclusive)
advertiser_name	TEXT	NOT NULL	-	広告主表示名（最大 30 文字、禁則ワードチェックあり、仕様書 §4.5）
image_url	TEXT	NOT NULL	-	広告画像 URL (R2)
redirect_url	TEXT	NOT NULL	-	クリック時リダイレクト先 URL
advertiser_profile_id	TEXT	NOT NULL, FK → advertiser_profiles.id	-	広告主プロフィール ID。以降の business_, contact_, business_category, invoice_registration_number はここからコピーした snapshot 値
business_legal_name	TEXT	NOT NULL	-	法人名 or 個人名 (snapshot)
business_address	TEXT	NOT NULL	-	事業所住所 (snapshot)
contact_name	TEXT	NOT NULL	-	担当者氏名 (snapshot)
contact_email	TEXT	NOT NULL	-	担当者メール (snapshot、停止通知送信先)
business_category	TEXT	NOT NULL, enum	-	業種カテゴリ (snapshot、BUSINESS_CATEGORIES、15 種類)
invoice_registration_number	TEXT	-	-	インボイス番号 (snapshot, nullable)
status	TEXT	NOT NULL, enum	pending_review	pending_review / active / stopped / expired
stopped_at	TEXT	-	-	停止日時 (nullable, stopped 時のみ)
stopped_reason	TEXT	-	-	停止理由 (nullable, admin 記述)
stripe_payment_intent_id	TEXT	-	-	Stripe PaymentIntent ID (nullable)
created_at	TEXT	NOT NULL	new Date().toISOString()	作成日時
updated_at	TEXT	NOT NULL	new Date().toISOString()	更新日時

ユニーク制約:

制約名	カラム	備考
uniq_ad_reservations_slot_position_country_start	slot_id, position, country, start_month	end_month は含まない。期間重複はアプリ層の getOccupiedPositions で判定（仕様書 §5.1）

インデックス:

インデックス名	カラム
idx_ad_reservations_country_status	country, status
idx_ad_reservations_slot_country	slot_id, country
idx_ad_reservations_user	user_id
idx_ad_reservations_advertiser_profile	advertiser_profile_id

更新タイミング:

カラム	書き込まれるフロー	備考
全カラム（除くステータス）	出稿フォーム送信時 INSERT	status = pending_review で作成
status	admin 審査時 UPDATE (→ active) / 強制停止時 UPDATE (→ stopped) / バッチで期限切れ UPDATE (→ expired)	仕様書 §10.4
stopped_at / stopped_reason	stopped に変更時のみ UPDATE	-
stripe_payment_intent_id	Stripe Checkout 成功時 UPDATE	-

ad_click_logs

ダイレクト広告のクリックログ。GET /api/ad-click/:reservationId で記録される（仕様書 §7）。

カラム	型	制約	デフォルト	説明
id	TEXT	PK, NOT NULL	-	ログID (UUID)
ad_reservation_id	TEXT	NOT NULL, FK → ad_reservations.id	-	広告予約ID（CASCADE しない、予約削除後もログは残す）
country	TEXT	NOT NULL	-	クリック元の国コード（CF-IPCountry 由来、非 ISO は "unknown"）
user_agent	TEXT	-	-	User-Agent (nullable, bot 判定用)
created_at	TEXT	NOT NULL	new Date().toISOString()	作成日時

インデックス:

インデックス名	カラム
idx_ad_click_logs_reservation_created	ad_reservation_id, created_at

更新タイミング:

カラム	書き込まれるフロー	備考
全カラム	クリック計測 API 呼び出し時 INSERT	リダイレクト前に必ず記録（UPDATE なし）

ad_impression_daily

ダイレクト広告のインプレッション日次集計。CTR 算出用（仕様書 §7.3）。行数を抑えるため日次粒度で UPSERT INCREMENT する。

カラム	型	制約	デフォルト	説明
id	TEXT	PK, NOT NULL	-	集計行ID (UUID)
ad_reservation_id	TEXT	NOT NULL, FK → ad_reservations.id	-	広告予約ID
date	TEXT	NOT NULL	-	日付 (YYYY-MM-DD)
country	TEXT	NOT NULL	-	国コード（解決不可時は "unknown"）
impressions	INTEGER	NOT NULL	0	当日のインプレッション数
created_at	TEXT	NOT NULL	new Date().toISOString()	作成日時
updated_at	TEXT	NOT NULL	new Date().toISOString()	更新日時

ユニーク制約:

制約名	カラム
uniq_ad_impression_daily_reservation_date_country	ad_reservation_id, date, country

更新タイミング:

カラム	書き込まれるフロー	備考
全カラム	広告表示時に UPSERT で impressions += 1	incrementImpression 関数経由

注記

旧 ad_pricing テーブルは廃止。価格は Stripe Dashboard で管理する方針に変更（ダイレクト広告仕様書 §9）。国コード → Stripe Price ID のマッピングは env var (STRIPE_AD_PRICE_JP_ID 等) で定義し、fetchAdPrice 関数（apps/api/src/features/ad-pricing/）で Stripe API から現行価格を取得する。契約時の金額は Stripe の PaymentIntent が immutable に保持するため、ローカル DB（ad_reservations）には price カラムを持たない。金額参照が必要になったら ad_reservations.stripe_payment_intent_id 経由で Stripe から取得する。

スキーマ定義ファイル

実装は packages/db/src/schema/schema.ts を参照。