Build a Chat Application in Lovable

Audit and test the participant RLS to confirm that users cannot see conversations or messages they are not a participant in.

1. Confirm the is_participant() function exists with SECURITY DEFINER:
   SELECT proname, prosecdef FROM pg_proc WHERE proname = 'is_participant';
   If prosecdef is false, the function is NOT running as the function owner and will not bypass RLS on participants. Recreate it with SECURITY DEFINER.

2. Confirm every SELECT policy on conversations, participants, and messages uses is_participant(), NOT a self-referencing subquery.

3. Test with two separate browser sessions (User A and User B):
   - User A creates a conversation with User B
   - Open a third incognito window as User C (a different account)
   - Sign in as User C and navigate to /chat — the conversation between A and B must NOT appear
   - Try fetching /rest/v1/messages with User C's JWT — zero rows must be returned

4. Fix any policy that uses auth.uid() = sender_id globally (too permissive — allows cross-conversation reads). Replace with: USING (is_participant(conversation_id) AND is_deleted = false).

Deliverable: Two separate users cannot see each other's conversations or messages. A third user with a valid JWT cannot access any conversation they were not added to.

Add typing indicators using Supabase Realtime broadcast channels — no database writes needed for this.

In the MessageComposer component:
1. On the textarea onChange event (debounced with a 500ms timeout), broadcast a typing event to a channel named 'typing:' + conversationId:
   await supabase.channel('typing:' + conversationId).send({type: 'broadcast', event: 'typing', payload: {user_id: currentUser.id, display_name: currentUser.display_name}})
2. Do NOT send on every keystroke — debounce so it fires at most once per 500ms of continuous typing.

In the MessageThread component:
1. Subscribe to the same channel and listen for 'typing' events:
   supabase.channel('typing:' + conversationId).on('broadcast', {event: 'typing'}, ({payload}) => { if (payload.user_id !== currentUser.id) { setTypingUser(payload.display_name); clearTimeout(typingTimeout); typingTimeout = setTimeout(() => setTypingUser(null), 3000); } }).subscribe()
2. Show the TypingIndicator component below the message list when typingUser is not null, with text '[Name] is typing...' and the animated three-dot pulse.
3. Clear the indicator after 3 seconds of no new typing broadcasts.

Important: Realtime broadcast channels are ephemeral — they do not require any DB table or RLS setup. They are separate from the postgres_changes subscription for messages.

Deliverable: When User A types in a conversation, User B sees '[Name] is typing...' with animated dots in the MessageThread. The indicator disappears 3 seconds after User A stops typing.

Add read receipts by tracking participants.last_read_at and computing unread counts.

1. Update participants.last_read_at whenever a user opens (or scrolls to the bottom of) a conversation:
   await supabase.from('participants').update({last_read_at: new Date().toISOString()}).eq('conversation_id', conversationId).eq('user_id', currentUser.id)
   Call this in the MessageThread component's useEffect when the conversation is mounted and when the user scrolls to the bottom.

2. In ConversationList, compute the unread count for each conversation:
   For each conversation, query: SELECT COUNT(*) FROM messages WHERE conversation_id = id AND created_at > (SELECT last_read_at FROM participants WHERE conversation_id = id AND user_id = auth.uid()) AND sender_id != auth.uid()
   Or create a Postgres RPC: get_unread_counts(p_user_id uuid) RETURNS TABLE(conversation_id uuid, unread_count int) for efficiency (one query instead of N).

3. Render UnreadBadge on ConversationListItem when unread_count > 0. Make the count bold the conversation title in the list.

4. Subscribe to Realtime on conversations table (already in the publication) to refresh the conversation list when last_message_at updates — this keeps the sort order live without polling.

Deliverable: Each conversation in the sidebar shows an unread message count. The badge clears when the user opens and reads the conversation.

Send email notifications to participants who have been inactive for more than 5 minutes when a new message arrives.

1. Add RESEND_API_KEY to Cloud tab → Secrets.

2. Create a Postgres trigger on messages AFTER INSERT that calls an Edge Function via pg_net:
   CREATE OR REPLACE FUNCTION notify_offline_participants() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN PERFORM net.http_post(url := 'https://[your-project-ref].supabase.co/functions/v1/notify-offline-participants', headers := jsonb_build_object('Authorization', 'Bearer ' || current_setting('app.service_role_key'), 'Content-Type', 'application/json'), body := jsonb_build_object('message_id', NEW.id, 'conversation_id', NEW.conversation_id, 'sender_id', NEW.sender_id)); RETURN NEW; END; $$;
   CREATE TRIGGER after_message_insert AFTER INSERT ON messages FOR EACH ROW EXECUTE FUNCTION notify_offline_participants();

3. Create Edge Function supabase/functions/notify-offline-participants/index.ts:
   - Read message_id, conversation_id, sender_id from request body
   - Query participants WHERE conversation_id = p_conversation_id AND user_id != p_sender_id AND (last_read_at IS NULL OR last_read_at < now() - interval '5 minutes')
   - For each inactive participant, fetch their email from profiles
   - Send via Resend API (POST https://api.resend.com/emails): Subject 'New message from [sender name]', body with first 200 chars of message, CTA link to /chat/:conversationId
   - Return early without throwing if RESEND_API_KEY is not set

4. Add an unsubscribe mechanism: create a GET /unsubscribe?token=... route that sets a participant's email_notifications=false column.

Deliverable: Users who have not been active in the conversation for 5+ minutes receive an email notification when a new message arrives.

Add file and image attachment support in conversations.

1. Create the Storage policy on the chat-attachments bucket (you created it in setup):
   CREATE POLICY chat_attachments_access ON storage.objects FOR ALL TO authenticated USING (
     bucket_id = 'chat-attachments'
     AND (storage.foldername(name))[1] IN (
       SELECT conversation_id::text FROM participants WHERE user_id = auth.uid()
     )
   ) WITH CHECK (
     bucket_id = 'chat-attachments'
     AND (storage.foldername(name))[1] IN (
       SELECT conversation_id::text FROM participants WHERE user_id = auth.uid()
     )
   );
   Files must be uploaded with path pattern: {conversationId}/{userId}-{timestamp}-{filename}

2. Update MessageComposer: add a paperclip icon button that opens a hidden file input (accept='image/*,application/pdf,.doc,.docx'). On file select, validate size < 10MB. Upload to supabase.storage.from('chat-attachments').upload(path, file). On success, include attachment_path in the messages INSERT.

3. Update MessageBubble: if message.attachment_path is set:
   - For image files (ends in .jpg, .jpeg, .png, .gif, .webp): render an img tag using supabase.storage.from('chat-attachments').getPublicUrl(attachment_path).data.publicUrl — but since the bucket is private, use createSignedUrl(path, 3600) instead and cache the signed URL in component state
   - For other file types: render a download link with file name and size

4. Show an upload progress indicator in MessageComposer while the file is uploading (0–100%).

Deliverable: Users can attach images and files up to 10MB in conversations. Images render inline. Other files show as download links. Only conversation participants can access the attachments via the Storage policy.

Add an optional AI assistant bot that participants can @mention in any conversation.

1. Add is_bot bool DEFAULT false column to participants table.

2. Create a bot user row in auth.users (or use a service account user ID) and add it as a participant to every new conversation via the conversation creation flow. Set its is_bot = true.

3. Create Edge Function supabase/functions/ai-bot-reply/index.ts:
   - Triggered via Postgres trigger on messages AFTER INSERT when body ILIKE '%@assistant%'
   - Fetch last 10 messages in the conversation as context
   - Call Lovable AI panel (Gemini 3 Flash) via the built-in AI connector, OR call Anthropic API (Claude Haiku 4.5 for speed) with ANTHROPIC_API_KEY from Secrets
   - Prompt: 'You are a helpful assistant in a group chat. Here is the conversation history: [last 10 messages]. The latest message is: [body]. Reply concisely in 1-3 sentences.'
   - Insert the bot's reply as a new message row with sender_id = bot_user_id

4. Update MessageBubble: if the sender is a bot (look up participants.is_bot), render a subtle robot icon and 'AI Assistant' name instead of the user's display name.

5. Add ANTHROPIC_API_KEY to Cloud tab → Secrets if using Claude Haiku 4.5 directly.

Deliverable: Typing '@assistant' in any message triggers an AI reply from the bot participant within 2–5 seconds. The reply appears in the thread like any other message.