Chats model

Status	Implementation
✅ Shipped	src/infra/db/schema/chat.schema.ts

chats

Column	Type	Notes
id	UUID PK	defaultRandom()
type	enum chat_type	approved_group, direct (direct planned)
created_at	timestamptz

CREATE TYPE chat_type AS ENUM ('approved_group', 'direct');

Source: chat.schema.ts.

chat_participants

Column	Type	Notes
chat_id	UUID FK → chats.id ON DELETE CASCADE
user_id	UUID FK → auth.users.id
joined_at	timestamptz

CREATE UNIQUE INDEX chat_participants_uniq
  ON chat_participants (chat_id, user_id);
CREATE INDEX chat_participants_user_idx
  ON chat_participants (user_id);

messages

Column	Type	Notes
id	UUID PK	defaultRandom()
chat_id	UUID FK → chats.id ON DELETE CASCADE
sender_id	UUID FK → auth.users.id
content	text	Message content
created_at	timestamptz
updated_at	timestamptz
deleted_at	timestamptz	Soft delete

CREATE INDEX messages_chat_created_idx
  ON messages (chat_id, created_at DESC);

CREATE INDEX messages_sender_idx ON messages (sender_id);

RLS

Per the conventions in api/docs/RLS-NOTES.md:

• chat_participants: a user may SELECT only rows where user_id = auth.uid(). All cross-membership reads (e.g. "who else is in this chat") happen through the API as service_role.
• messages: SELECT allowed when the caller has a non-left_at row in chat_participants for the message's chat_id. Same rule for INSERT (with sender_id = auth.uid()).
• chats: same membership rule via a SECURITY DEFINER helper.

These RLS predicates are what makes Supabase Realtime safe — the realtime layer applies the same RLS, so a client subscribed to postgres_changes on messages automatically only receives rows for chats they participate in.

Relationship overview