HavenDOCS
Back to Home

Database Schema (Haven 2026)

Haven uses a modular Supabase PostgreSQL architecture built for high concurrency and granular permission control.

🗺️ Principal Tables

| Table | Usage | Security (RLS) | | :--- | :--- | :--- | | profiles | Users and Levels. | Public (Read Only) | | study_rooms | Sanctuary room records (internal name: study_rooms). | Public (Read Only) | | room_participants | Active room members. | Joined Users Only | | room_roles | Customizable Discord-style roles. | Creator Only (Edit) | | room_user_roles | Map users to roles within a room. | Creator Only (Edit) | | chat_sessions | Stores metadata for 1-on-1 matches. | Participants Only | | study_connections | Connections (internal name: study_connections) for friend requests and active buddies. | Involved Users Only |

🔑 Critical System Functions

We use specialized PostgreSQL functions to handle complex permission logic while maintaining performance.

check_room_permission_internal

This SECURITY DEFINER function is the core of our permission system.

  • Logic: It cross-checks if a user is the owner_id (creator) OR if they have a specific room_role in room_user_roles with the required permissions.
  • Why: Allows us to bypass strict RLS checks within the database to make permission decisions instantly.
  • Fix (2026): Updated to correctly reference owner_id and created_by in study_rooms (Sanctuary core) after a major schema refactor.

get_user_highest_role

Returns the role with the lowest position (where 0 is highest) for a user in a given room.

  • UI Use: Determines the color and icon displayed next to the user's name in the ParticipantList.

🚥 RLS Policies Example

/* Example: Only members can read participants */
CREATE POLICY "Users can view participants in their joined rooms"
ON room_participants
FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM study_rooms 
    WHERE id = room_participants.room_id 
    AND (is_public = true OR check_room_permission_internal(auth.uid(), id, 'VIEW_MEMBERS'))
  )
);

⚡ Optimization & Indexes

  • Trigram Index: idx_profiles_username_trgm on profiles(username) for fast fuzzy searching of members.
  • Composite Index: idx_room_messages_channel_id on room_messages(channel_id, created_at DESC) ensures instant chat history loading.
  • Partial Index: idx_profiles_is_online on profiles where is_online = true for high-speed online user counts.

Maintained by the Antigravity Data Team.