04. データベーススキーマ (Supabase)

1. 概要

このドキュメントは、Supabaseプロジェクトで使用されているPostgreSQLデータベースのスキーマについて説明します。

• 設計思想:
  • オンラインWebサービス: Supabaseによって実装されるWebサービス(Publish・Explore等)において、プロジェクトの実体はコンテンツバケットとメタデータテーブルに保存されます。
  • 所有権の保護: 既存の公開枠に対する更新操作はサービス層（publishProject）で所有権（user_idの一致）が厳密に検証されます。これにより、他者の公開作品を誤って上書きするリスクを排除しています。詳細は 01_architecture.md の「データ永続化」セクションを参照してください。

クライアントサイドのIndexedDBスキーマ（Version 24）については、04_database_schema.md を参照してください。

型定義の自動生成について

重要: このドキュメントは手動で管理されていますが、TypeScript型定義は以下のコマンドで自動生成できます:

bunx supabase gen types typescript --linked > next-app/src/lib/types/supabase.ts

マイグレーション適用後の必須作業:

1. 上記コマンドで型定義を自動生成
2. このドキュメントを更新して変更内容を反映
3. 型定義とドキュメントの整合性を確認
4. TypeScriptのビルドエラーがないことを確認

詳細は /doc/system/05_migration_guide.md の「7. 型定義の同期」セクションを参照してください。

2. テーブル定義

2.1. public.profiles

ユーザーの公開プロフィール情報を格納するテーブルです。auth.usersテーブルのレコードと1対1の関係にあります。

2.2. public.published_projects

公開された作品のデータを格納するテーブルです。多言語メタデータに対応しています。

外部キー制約:

• published_projects_user_id_fkey: auth.users(id) への参照（カスケード削除）
• published_projects_user_id_profiles_fkey: public.profiles(id) への参照（カスケード削除）

注記: user_idはauth.usersとprofilesの両方を参照しています。これにより、Supabaseのクエリで.select('*, profiles(username, avatar_url)')のように、作者情報を簡単に取得できます。

2.3. public.library_items

ユーザーがブックマーク（ライブラリに追加）した作品を管理するテーブルです。

制約:

• UNIQUE(user_id, project_id): 同じユーザーが同じ作品を重複して登録できないようにするユニーク制約。
• library_items_user_id_fkey: auth.users(id) への参照（カスケード削除）
• library_items_project_id_fkey: public.published_projects(id) への参照（カスケード削除）

3. ストレージ定義

3.1. covers バケット

作品のカバー画像を格納するための公開ストレージバケットです。

3.2. contents バケット

公開された作品の本文データ（JSONファイル）を格納するためのプライベートストレージバケットです。

3.3. user_backups バケット

E2E暗号化されたデータベースバックアップを格納するためのプライベートストレージバケットです。

ファイル構造:

• パス: {user_id}/backup.enc
• 各ユーザーは自分のフォルダ内にのみアクセス可能
• ファイルはクライアント側でAES-256-GCM暗号化されており、サーバー側では復号不可能（Zero-Knowledge）

2.4. public.user_sync_state

クラウド同期のメタデータを管理するテーブルです。

• user_sync_state_user_id_fkey: auth.users(id) への参照（カスケード削除）

2.5. public.prompt_assets

ユーザーが作成したAIプロンプトアセットを格納するテーブルです。

外部キー制約:

• prompt_assets_user_id_fkey: auth.users(id) への参照（カスケード削除）

2.6. public.customers

SupabaseユーザーとStripeの顧客IDを紐付けるテーブルです。

外部キー制約:

• customers_id_fkey: auth.users(id) への参照（カスケード削除）

2.7. public.subscriptions

Stripeのサブスクリプション詳細情報を格納するテーブルです。

外部キー制約:

• subscriptions_user_id_fkey: auth.users(id) への参照（カスケード削除）

4. Row Level Security (RLS) ポリシー

4.1. public.profiles

1. Public profiles are viewable by everyone.

   • 操作: SELECT
   • 条件: USING (true)
   • 説明: すべてのユーザー（非認証ユーザーを含む）が、すべてのプロフィール情報を閲覧することを許可します。

2. Users can update their own profile.

   • 操作: UPDATE
   • 条件: USING (auth.uid() = id)
   • 説明: 認証済みのユーザーが、自身のidと一致するプロフィールレコードのみを更新することを許可します。

4.2. public.published_projects

1. Public projects are viewable by everyone.

   • 操作: SELECT
   • 条件: USING (true)
   • 説明: 公開された作品は誰でも閲覧可能です。

2. Users can insert their own projects.

   • 操作: INSERT
   • 条件: WITH CHECK (auth.uid() = user_id)
   • 説明: 認証済みのユーザーは自身のプロジェクトのみを公開できます。

3. Users can update their own projects.

   • 操作: UPDATE
   • 条件: USING (auth.uid() = user_id)
   • 説明: 認証済みのユーザーは自身のプロジェクトのみを更新できます。
   • サービス層バリデーション: RLSに加え、publishProject サービス（service.ts）において、既存の公開スロットを更新する際、そのスロットの user_id が現在の実行ユーザーと一致するかをDBから再取得してチェックします。これにより、クライアントサイドからの不正なID指定による上書きをサーバー側でも防御します。

4. Users can delete their own projects.

   • 操作: DELETE
   • 条件: USING (auth.uid() = user_id)
   • 説明: 認証済みのユーザーは自身のプロジェクトのみを削除できます。

4.3. public.library_items

1. Users can view their own library.

   • 操作: SELECT
   • 条件: USING (auth.uid() = user_id)
   • 説明: 認証済みのユーザーは自身のライブラリのみを閲覧できます。

2. Users can add items to their library.

   • 操作: INSERT
   • 条件: WITH CHECK (auth.uid() = user_id)
   • 説明: 認証済みのユーザーは自身のライブラリにのみアイテムを追加できます。

3. Users can update their library items.

   • 操作: UPDATE
   • 条件: USING (auth.uid() = user_id)
   • 説明: 認証済みのユーザーは自身のライブラリアイテム（並び順など）のみを更新できます。

4. Users can delete items from their library.

   • 操作: DELETE
   • 条件: USING (auth.uid() = user_id)
   • 説明: 認証済みのユーザーは自身のライブラリからのみアイテムを削除できます。

4.4. storage.objects (coversバケット)

1. Cover images are publicly accessible.

   • 操作: SELECT
   • 条件: USING (bucket_id = 'covers')
   • 説明: coversバケット内の画像は誰でも閲覧可能です。

2. Authenticated users can upload covers.

   • 操作: INSERT
   • 条件: WITH CHECK (bucket_id = 'covers' AND auth.role() = 'authenticated')
   • 説明: 認証済みのユーザーはcoversバケットに画像をアップロードできます。

3. Users can update their own covers.

   • 操作: UPDATE
   • 条件: USING (bucket_id = 'covers' AND auth.uid() = owner)
   • 説明: 画像の所有者のみがその画像を更新できます。

4. Users can delete their own covers.

   • 操作: DELETE
   • 条件: USING (bucket_id = 'covers' AND auth.uid() = owner)
   • 説明: 画像の所有者のみがその画像を削除できます。

4.5. storage.objects (contentsバケット)

1. Anyone can read published content.

   • 操作: SELECT
   • 条件: bucket_id = 'contents' AND EXISTS (SELECT 1 FROM published_projects WHERE content_path = storage.objects.name)
   • 説明: published_projectsテーブルのcontent_pathに登録されている（＝公開されている）ファイルであれば、誰でも（非認証ユーザー含む）読み取り可能です。これにより、Service Role Keyを使用せずに安全にコンテンツを提供できます。

2. User can manage their own content.

   • 操作: ALL (SELECT, INSERT, UPDATE, DELETE)
   • 条件: bucket_id = 'contents' AND auth.uid() = owner
   • 説明: 認証済みのユーザーは、自身が所有する（ownerが自分のuser_idである）コンテンツファイルに対して、すべての操作（読み取り、作成、更新、削除）を実行できます。

4.6. public.user_sync_state

1. Users can view their own sync state.

   • 操作: SELECT
   • 条件: USING (auth.uid() = user_id)
   • 説明: 認証済みのユーザーは自身の同期状態のみを閲覧できます。

2. Users can manage their own sync state.

   • 操作: INSERT, UPDATE, DELETE
   • 条件: WITH CHECK (auth.uid() = user_id) / USING (auth.uid() = user_id)
   • 説明: 認証済みのユーザーは自身の同期状態のみを作成・更新・削除できます。

4.7. storage.objects (user_backupsバケット)

1. Users can manage their own backups.
   • 操作: ALL (SELECT, INSERT, UPDATE, DELETE)
   • 条件: bucket_id = 'user_backups' AND auth.uid() = (storage.foldername(name))[1]::uuid
   • 説明: 認証済みのユーザーは、自分のユーザーIDフォルダ内のバックアップファイルに対してのみ、すべての操作を実行できます。パスは {user_id}/backup.enc の形式を想定しています。

注記: 将来的にPro Planを実装する場合、is_pro_user() 関数を追加し、無料ユーザーのバックアップを制限することができます。 - 説明: コンテンツファイルの所有者（作者）は、自身のファイルに対して全ての操作を行えます。

4.8. public.prompt_assets

1. Users can manage their own prompt assets.
   • 操作: ALL (SELECT, INSERT, UPDATE, DELETE)
   • 条件: USING (auth.uid() = user_id)
   • 説明: 認証済みのユーザーは、自身が作成したプロンプトアセットのみを管理できます。
   • 補足: is_public が true の場合に他人が閲覧できるポリシーは将来的に追加可能です。

5. 自動化ロジック (トリガー & 関数)

5.1. handle_new_user() 関数

• 目的: auth.usersに新しいユーザーが登録された際に、自動的にpublic.profilesテーブルに対応するレコードを作成します。
• 実装: plpgsql言語で記述された関数。
• トリガー: on_auth_user_created (AFTER INSERT ON auth.users)

5.2. handle_project_update() 関数

• 目的: published_projectsテーブルのレコードが更新された際に、updated_atカラムを自動的に現在のタイムスタンプに更新します。
• 実装: plpgsql言語で記述された関数。
• トリガー: on_project_update (BEFORE UPDATE ON published_projects)

5.3. get_tags_by_context(p_user_id, p_context) 関数

• 目的: 画面のコンテキスト（全体、ライブラリ、自分の作品）に応じて、タグの出現頻度を集計して返します。
• 引数:
  • p_user_id (uuid): ユーザーID（コンテキストがユーザー依存の場合に必須）
  • p_context (text): 'global', 'library', 'my_works' のいずれか
• 戻り値: TABLE (tag text, count bigint)
• 使用場所: Explore, Library, My Worksページのタグフィルター

5.4. is_pro_user() 関数

• 目的: 現在の認証ユーザーがProプラン会員であるかどうかを判定します。
• 戻り値: boolean（Pro会員の場合は true）
• 実装: profiles.is_pro カラムを参照して判定します。
• 使用場所:
  • RLSポリシー内での権限チェック
  • フロントエンドからのRPC呼び出し（supabase.rpc('is_pro_user')）

設計上の注意点:

• この関数は、データソース（profiles.is_pro カラム）と、判定ロジック（RPC関数）を分離する設計パターンに従っています。
• is_pro は永続化されたデータキャッシュであり、is_pro_user() はそのデータを参照するインターフェースです。
• 以前は subscriptions テーブルを直接参照していましたが、パフォーマンスと実装の簡素化のため、profiles.is_pro を参照するように変更されました。

5.5. handle_pro_status_update() 関数

• 目的: subscriptions テーブルの変更時に、対応するユーザーの profiles.is_pro フラグを自動更新します。
• トリガー: on_subscription_change (AFTER INSERT OR UPDATE OR DELETE ON public.subscriptions)
• 更新ロジック:
  • subscriptions.status が 'active' または 'trialing' の場合 → is_pro = true
  • それ以外の場合 → is_pro = false
• 設計意図: subscriptions テーブルの変更をイベントとして捉え、profiles.is_pro を正規化されたキャッシュとして維持します。これにより、Pro判定のために subscriptions テーブルを直接参照する必要がなくなります。

関連ドキュメント:

• 01_architecture.md
• 04_database_schema.md (クライアントサイドDB)
• 15_stripe_integration_guide.md (Pro会員判定ロジック)