Chats model

Planned

The chats, chat_participants, messages and related tables are not yet shipped. This is the design they'll follow on the new Hono + Supabase + Drizzle stack with Supabase Realtime as the transport.

chats

Column	Type	Notes
id	UUID PK	defaultRandom()
type	enum chat_type	event_authors, approved_group, direct
event_id	UUID FK → events.id	nullable, set for event-derived chats
created_at	timestamptz
updated_at	timestamptz
deleted_at	timestamptz	Soft delete

CREATE TYPE chat_type AS ENUM ('event_authors', 'approved_group', 'direct');
CREATE INDEX chats_event_idx ON chats (event_id) WHERE event_id IS NOT NULL;

chat_participants

Column	Type
chat_id	UUID FK → chats.id ON DELETE CASCADE
user_id	UUID FK → auth.users.id
joined_at	timestamptz
last_read_at	timestamptz
left_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) WHERE left_at IS NULL;

messages

Column	Type	Notes
id	UUID PK
chat_id	UUID FK → chats.id ON DELETE CASCADE
sender_id	UUID FK → auth.users.id
text	text	nullable when attachment-only
attachment_url	text	Supabase Storage path / signed URL
created_at	timestamptz
edited_at	timestamptz	sender, ≤15 min after created_at
deleted_at	timestamptz	Soft delete

CREATE INDEX messages_chat_created_idx
  ON messages (chat_id, created_at DESC)
  WHERE deleted_at IS NULL;

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