profiles

Overview

The profiles table stores all user-facing identity and preference data. Each row corresponds to one authenticated user and is created automatically on signup via a Supabase trigger. This table is central to the entire application -- nearly every query joins against it.

Profiles also hold account moderation status, privacy curtain settings, account tier, data retention preferences, and relationship encryption keys.

Relevant Invariants

• Invariant #6: "No Public Comparative Metrics" -- Profile does not expose follower counts, post counts, or engagement stats
• Invariant #14: "Privacy Is Infrastructure" -- Privacy curtain fields, auto-lock, retention preferences are stored here
• Invariant #18: "Defaults Are Moral Decisions" -- Default values favor least exposure and least pressure

Schema

-- Pre-existing table (inferred from frontend code and migrations)
CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id),
  username TEXT UNIQUE,
  display_name TEXT,
  bio TEXT,
  avatar_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  -- Voice preferences (20260131_voice_narration.sql)
  viewer_disable_audio BOOLEAN DEFAULT FALSE,
  -- Admin system (20260202_admin_system.sql)
  account_status TEXT DEFAULT 'active',
  status_reason TEXT,
  status_until TIMESTAMPTZ,
  -- Privacy curtain (20260203_privacy_curtain.sql)
  privacy_curtain_enabled BOOLEAN DEFAULT FALSE,
  privacy_curtain_timeout_minutes INTEGER DEFAULT 5,
  privacy_curtain_style TEXT DEFAULT 'fade',
  -- Account tiers (20260205_account_tiers.sql)
  account_tier TEXT DEFAULT 'new' CHECK (account_tier IN ('new', 'established', 'trusted')),
  tier_upgraded_at TIMESTAMPTZ,
  -- Data retention (20260205_data_retention.sql)
  message_retention_days INTEGER DEFAULT 90,
  auto_archive_posts BOOLEAN DEFAULT FALSE,
  -- Relationship encryption (20260205_relationship_encryption.sql)
  relationship_key_salt TEXT
);

Columns

Column	Type	Nullable	Default	Description
id	uuid	No	auth.users(id)	Primary key, references auth user
username	text	Yes	--	Unique username for URL routing
display_name	text	Yes	--	Display name shown in UI
bio	text	Yes	--	User biography text
avatar_url	text	Yes	--	URL to avatar image in storage
created_at	timestamptz	No	NOW()	Account creation timestamp
updated_at	timestamptz	No	NOW()	Last profile update timestamp
viewer_disable_audio	boolean	No	FALSE	If true, disables voice narration playback
account_status	text	No	'active'	Moderation status: active, warned, suspended, banned
status_reason	text	Yes	--	Reason for non-active status
status_until	timestamptz	Yes	--	When temporary status expires
privacy_curtain_enabled	boolean	No	FALSE	Whether privacy curtain auto-activates
privacy_curtain_timeout_minutes	integer	No	5	Minutes of inactivity before curtain
privacy_curtain_style	text	No	'fade'	Visual style of curtain overlay: 'fade', 'blur', or 'black'
account_tier	text	No	'new'	Trust tier: new, established, trusted
tier_upgraded_at	timestamptz	Yes	--	When tier was last upgraded
message_retention_days	integer	Yes	90	Auto-delete messages after N days (valid values: 30, 60, 90, 180, 365, or NULL)
auto_archive_posts	boolean	No	FALSE	Auto-archive old posts
relationship_key_salt	text	Yes	--	Salt for client-side relationship encryption

RLS Policies

-- SELECT: Block-aware profile visibility (from 20260205_block_enhancement.sql)
-- Replaces earlier permissive policies. Blocked users see nothing (404 behavior).
CREATE POLICY "Profiles visible to authenticated users"
  ON profiles FOR SELECT TO authenticated
  USING (
    -- Always allow viewing own profile
    auth.uid() = id
    OR
    -- Allow viewing others if they haven't blocked you with prevent_profile_view
    NOT EXISTS (
      SELECT 1 FROM blocks
      WHERE blocker_id = profiles.id
      AND blocked_id = auth.uid()
      AND prevent_profile_view = TRUE
    )
  );

-- UPDATE: Users can only update their own profile
CREATE POLICY "Users can update own profile"
  ON profiles FOR UPDATE
  USING (auth.uid() = id);

Related

• posts -- Posts reference profiles via user_id
• friendships -- Friend connections between profiles
• blocks -- Block relationships between profiles
• home_room_settings -- HomeRoom configuration per profile
• mebook_responses -- MeBook answers per profile

---

Last updated: 2026-02-07