message_reactions

Overview

The message_reactions table stores reactions to messages. Unlike typical social media, CommonPlace provides only a curated set of 6 reaction types: heart, smile, thinking, lightbulb, hug, and laugh. This keeps reactions meaningful rather than performative.

Relevant Invariants

• Invariant #6: "No Public Comparative Metrics" -- Reaction counts are visible only to conversation participants
• Invariant #18: "Defaults Are Moral Decisions" -- Limited reaction set prevents performative behavior

Schema

-- From 20260201_message_reactions.sql
CREATE TABLE message_reactions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  reaction_type TEXT NOT NULL CHECK (reaction_type IN (
    'heart', 'smile', 'thinking', 'lightbulb', 'hug', 'laugh'
  )),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(message_id, user_id, reaction_type)
);

Columns

Column	Type	Nullable	Default	Description
id	uuid	No	gen_random_uuid()	Primary key
message_id	uuid	No	--	Message being reacted to
user_id	uuid	No	--	User who reacted
reaction_type	text	No	--	One of: heart, smile, thinking, lightbulb, hug, laugh
created_at	timestamptz	No	NOW()	Reaction timestamp

RLS Policies

-- SELECT: Conversation participants can see reactions
CREATE POLICY "Participants can view reactions"
  ON message_reactions FOR SELECT
  USING (EXISTS (
    SELECT 1 FROM messages
    JOIN conversation_participants ON conversation_participants.conversation_id = messages.conversation_id
    WHERE messages.id = message_reactions.message_id
    AND conversation_participants.user_id = auth.uid()
  ));

-- INSERT: Participants can add reactions
CREATE POLICY "Participants can add reactions"
  ON message_reactions FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- DELETE: Users can remove their own reactions
CREATE POLICY "Users can remove own reactions"
  ON message_reactions FOR DELETE
  USING (auth.uid() = user_id);

Related

• messages -- Message being reacted to
• profiles -- Reacting user

---

Last updated: 2026-02-07