チームツールのDB設計パターン【未読管理・通知・権限をSupabaseで実装】

はじめに

チームで使うコミュニケーションツールを自作しようとすると、機能の実装より先にデータベース設計で詰まることが多いです。特に「未読管理」「通知」「権限」の3領域は、設計を誤ると後から直すのが非常に難しくなります。

この記事では、Next.js + Supabase（PostgreSQL）でチームボードを構築した際のDB設計パターンを解説します。実際のテーブル構成をもとに、なぜそう設計したのかという意図まで掘り下げます。サンプルコードはあくまで設計の参考として示しています。自身の環境に合わせて調整してください。

テーブル全体像

チームツールに必要なテーブルを整理すると、大きく4グループに分けられます。

コア】
  accounts              — ユーザーアカウント
  channels              — チャンネル・DM

【メッセージ】
  messages              — メッセージ本体
  reactions             — リアクション（絵文字）

【未読・通知】
  read_messages         — 既読管理
  notifications         — 通知
  notification_settings — 通知設定

【その他】
  bookmarks             — ブックマーク
  online_status         — オンラインステータス

9テーブル構成です。それぞれの設計意図を順に解説します。

未読管理の設計パターン

未読管理はチームツールの中で最も設計が難しい領域です。「誰が・どのメッセージを・読んだか」を記録する必要があります。

アプローチ1：既読レコードを積み上げる方式

メッセージが読まれるたびに「アカウント × メッセージ」の組み合わせをテーブルに追加していく方式です。

※以下はサンプルスキーマです。

-- サンプル：既読管理テーブル
CREATE TABLE read_messages (
  account_id  UUID NOT NULL REFERENCES accounts(id),
  message_id  UUID NOT NULL REFERENCES messages(id),
  read_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (account_id, message_id)
);

未読数の計算はSQL側で完結します。フロントで配列を操作する必要がなく、表示ロジックがシンプルになります。

※以下はサンプルクエリです。

-- サンプル：チャンネルごとの未読数を取得
SELECT
  m.channel_id,
  COUNT(*) AS unread_count
FROM messages m
LEFT JOIN read_messages r
  ON r.message_id = m.id
  AND r.account_id = '（ログインユーザーのID）'
WHERE r.message_id IS NULL   -- 既読レコードがない = 未読
GROUP BY m.channel_id;


設計上の注意点

この方式はシンプルですが、メッセージ数が増えると read_messages テーブルが大きくなります。「メンバー数 × メッセージ数」分のレコードが蓄積されるためです。運用開始前に古いレコードの定期削除ポリシーを決めておくことを推奨します。

もう一つの選択肢として、「最後に読んだメッセージIDを記録する」方式があります。レコード数を抑えられますが、メッセージの削除・編集への対応が複雑になります。小規模チームであれば既読レコード積み上げ方式の方が実装がシンプルです。

通知の設計パターン

通知テーブルの基本構造

通知は「誰に・何の・どのメッセージに関する通知か」を記録します。通知の種類（メンション・DM・リアクションなど）をカラムで区別するのが基本パターンです。

※以下はサンプルスキーマです。

-- サンプル：通知テーブル
CREATE TABLE notifications (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id  UUID NOT NULL REFERENCES accounts(id),  -- 受信者
  type        TEXT NOT NULL,   -- 'mention' | 'dm' | 'reaction' | 'reply'
  message_id  UUID REFERENCES messages(id),
  channel_id  UUID REFERENCES channels(id),
  sender_id   UUID REFERENCES accounts(id),           -- 送信者
  is_read     BOOLEAN NOT NULL DEFAULT false,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

通知設定テーブルとの組み合わせ

「このチャンネルの通知はオフにしたい」「メンションだけ受け取りたい」といったユーザーごとの設定は、別テーブルで管理します。通知を生成する前にこの設定を参照することで、不要な通知レコードの生成を抑えられます。

※以下はサンプルスキーマです。

-- サンプル：通知設定テーブル
CREATE TABLE notification_settings (
  account_id       UUID NOT NULL REFERENCES accounts(id),
  channel_id       UUID REFERENCES channels(id),  -- NULLの場合はグローバル設定
  mentions_enabled BOOLEAN NOT NULL DEFAULT true,
  dm_enabled       BOOLEAN NOT NULL DEFAULT true,
  PRIMARY KEY (account_id, channel_id)
);

Supabase Realtimeとの連携

Supabase Realtimeとは、PostgreSQLのデータ変更をWebSocketでリアルタイムにフロントエンドへ配信する機能です。通知テーブルに新しいレコードが挿入された瞬間、該当ユーザーのブラウザに即座に反映されます。WebSocketの接続管理やリトライ処理をSupabaseが担ってくれるため、自前で実装する必要がありません。

※以下はサンプルコードです。

// サンプル：通知テーブルの変更をリアルタイム購読
supabase
  .channel('notifications')
  .on('postgres_changes', {
    event: 'INSERT',
    schema: 'public',
    table: 'notifications',
    filter: `account_id=eq.（ログインユーザーのID）`,
  }, (payload) => {
    // 新着通知をUIに反映する処理
  })
  .subscribe()

権限設計のパターン

シンプルな2段階権限

小規模チームであれば、権限は「管理者 / メンバー」の2段階で十分です。複雑なロールベースアクセス制御（RBAC）は、運用開始後に必要になってから追加する方が現実的です。

※以下はサンプルスキーマです。

-- サンプル：アカウントテーブルの権限カラム
CREATE TABLE accounts (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email      TEXT NOT NULL UNIQUE,
  name       TEXT NOT NULL,
  role       TEXT NOT NULL DEFAULT 'member',  -- 'admin' | 'member'
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

RLS（Row Level Security）による制御

RLS（Row Level Security）とは、PostgreSQLのテーブルレベルでアクセス制御を設定する仕組みです。Supabaseでは標準的に利用できます。「このレコードは誰が読み書きできるか」をSQL側で定義するため、アプリのコードに漏れが生じても直接DBのデータが漏洩するリスクを下げられます。

例えば「自分のアカウント情報しか更新できない」ポリシーはRLSで定義します。

※以下はサンプルです。

-- サンプル：自分のレコードのみ更新を許可するRLSポリシー
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "自分のアカウントのみ更新可"
  ON accounts FOR UPDATE
  USING (id = auth.uid());

チャンネルの権限設計

チャンネルの削除・アーカイブは管理者のみに限定し、メッセージの投稿・編集はメンバーも可能とする設計が一般的です。「誰が作ったチャンネルか」を記録しておくことで、作成者にのみ編集権限を与えることもできます。

※以下はサンプルスキーマです。

-- サンプル：チャンネルテーブル
CREATE TABLE channels (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  type        TEXT NOT NULL DEFAULT 'channel',  -- 'channel' | 'dm'
  created_by  UUID NOT NULL REFERENCES accounts(id),
  is_archived BOOLEAN NOT NULL DEFAULT false,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

設計で気をつけたこと

論理削除より物理削除を選ぶ場面

論理削除とは、レコードを実際には消さず deleted_at カラムを付けて「削除済み」フラグを立てる方式です。チームツールのメッセージは「削除したメッセージです」と表示する仕様が多いため、論理削除が適しています。一方、通知や既読レコードは物理削除（実際に行を消す）で十分です。削除の方針はテーブルごとに用途に合わせて決めることが重要です。

インデックスの設計

未読数の計算クエリは頻繁に実行されるため、インデックスの有無がパフォーマンスに直結します。特に read_messages の account_id と message_id の複合インデックス、messages の channel_id と created_at の複合インデックスは必須です。クエリの実行計画を確認しながら必要なインデックスを追加していくのが現実的なアプローチです。

Supabase無料枠の制約

Supabaseの無料枠はストレージ500MB、DBサイズ500MBです。チームが5〜10人程度であれば当面は無料枠で収まります。ただしアクティブプロジェクトが1週間アクセスがないと一時停止される仕様があるため、定期的なアクセスを発生させるか、有料プランへの移行タイミングを事前に決めておくとよいです。

Slackと比べた自作の判断基準

Next.js + Supabaseの組み合わせなら、リアルタイム通信・認証・DBが追加コストほぼゼロで揃います。サイトのSEOや技術的な品質が気になる方は CodeQuest SEO_CHECK で診断してみてください。

まとめ

チームツールのDB設計で押さえておくべきポイントを整理します。

• 未読管理：「アカウント × メッセージ」の既読レコードを積み上げる方式がシンプル。未読数の計算はSQL側で完結させる
• 通知：通知テーブルと通知設定テーブルを分離する。Supabase Realtimeでリアルタイム配信
• 権限：小規模なら「管理者 / メンバー」の2段階で十分。RLSをDB側で設定してコード側の漏れをカバーする
• 削除方針：テーブルごとに論理削除・物理削除を使い分ける
• インデックス：頻繁に実行されるクエリのカラムには早めにインデックスを張る

よくある質問

Q. SupabaseとFirebaseはどう使い分けますか？

Supabaseはリレーショナルデータベース（PostgreSQL）ベースなので、テーブル間の結合や複雑なクエリが得意です。未読数の計算のようにSQL集計が必要な場面ではSupabaseが向いています。FirebaseはNoSQLなので、スキーマレスで高速に書き込みたい場合や、モバイルアプリとの親和性が高い場面で選ばれます。チームツールのように構造化されたデータを扱う用途ではSupabaseの方が設計しやすいです。

Q. RLSを使わずにアプリ側だけで権限管理することはできますか？

技術的には可能ですが、推奨しません。アプリのコードにバグや漏れがあった場合、DBのデータに直接アクセスされるリスクがあります。RLSはDBレベルの最後の砦として機能するため、特にSupabaseを使う場合は必ず設定しておくことをおすすめします。

Q. チームが増えてきたときにSupabaseからの移行は難しいですか？

SupabaseはPostgreSQLなので、標準のPostgreSQLにダンプして別のホスティングサービスに移行できます。PlanetScaleやNeonなどのマネージドPostgreSQLへの移行は比較的スムーズです。Realtime機能だけは代替の実装が必要になりますが、スタンドアロンのPostgresに切り替える際の障壁は低いです。