posts

Overview

The posts table stores all user-created content. Every post carries an intent label that signals the poster's communication purpose -- this is a core invariant of CommonPlace. Posts are never ranked or scored; they appear in chronological order within the slow-feed pacing system.

Posts support text content, optional images (via post_images), voice narration, audience scoping (friends-only or circle-specific), and optional sharing/archiving controls.

Relevant Invariants

• Invariant #4: "Intent Precedes Interpretation" -- Every post carries an intent field
• Invariant #6: "No Public Comparative Metrics" -- No like counts, view counts, or rankings
• Invariant #8: "Feed Is a View, Not a Goal" -- Posts are not optimized for engagement
• Invariant #12: "Slowness Is Baked In" -- Posts surface through batched, paced loading

Schema

-- Pre-existing table with additions from migrations
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id) NOT NULL,
  content TEXT,
  image_url TEXT,
  intent TEXT DEFAULT 'thinking_out_loud',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  sharing_allowed BOOLEAN DEFAULT TRUE,  -- Added by 20260208_sharing_allowed_column.sql
  shared_post_id UUID REFERENCES posts(id),
  builds_on_post_id UUID REFERENCES posts(id),
  -- Voice narration (20260131_voice_narration.sql)
  voice_url TEXT,
  voice_duration_ms INTEGER,
  voice_mime TEXT,
  -- Circle audience (20260131_circles_schema.sql)
  audience TEXT DEFAULT 'public' CHECK (audience IN ('public', 'friends', 'circles')),
  -- Data retention (20260205_data_retention.sql)
  auto_archive_at TIMESTAMPTZ,
  is_archived BOOLEAN DEFAULT FALSE
);

Columns

Column	Type	Nullable	Default	Description
id	uuid	No	uuid_generate_v4()	Primary key
user_id	uuid	No	--	Author, references auth.users
content	text	Yes	--	Post text content
image_url	text	Yes	--	Legacy single image URL
intent	text	Yes	'thinking_out_loud'	One of 6 intent types
created_at	timestamptz	No	NOW()	Creation timestamp
updated_at	timestamptz	No	NOW()	Last edit timestamp
sharing_allowed	boolean	Yes	TRUE	Whether post can be shared (added by 20260208_sharing_allowed_column.sql)
content_note	text	Yes	NULL	Optional content note/warning set by author
is_share	boolean	No	FALSE	Whether this post is a share of another post
share_note	text	Yes	--	Optional note from the sharer
quoted_excerpt	text	Yes	--	Quoted excerpt for build-on posts
shared_post_id	uuid	Yes	--	If sharing another post, reference to original
builds_on_post_id	uuid	Yes	--	Reference to post this builds upon
voice_url	text	Yes	--	URL to voice narration audio file
voice_duration_ms	integer	Yes	--	Duration of voice clip in milliseconds
voice_mime	text	Yes	--	MIME type of voice audio
audience	text	Yes	'public'	Visibility scope: 'public', 'friends', or 'circles'
auto_archive_at	timestamptz	Yes	--	Scheduled archive timestamp
is_archived	boolean	No	FALSE	Whether post is archived

RLS Policies

-- SELECT: Audience-aware visibility with admin bypass (20260208_post_visibility_rls.sql)
-- Checks: not hidden, not blocked (bidirectional), audience-based access
CREATE POLICY "Posts viewable with audience check"
  ON posts FOR SELECT TO authenticated
  USING (
    is_admin() OR (
      NOT COALESCE(is_hidden, false)
      AND NOT EXISTS (SELECT 1 FROM blocks WHERE blocker_id = posts.user_id AND blocked_id = auth.uid())
      AND NOT EXISTS (SELECT 1 FROM blocks WHERE blocker_id = auth.uid() AND blocked_id = posts.user_id)
      AND (
        posts.user_id = auth.uid()
        OR posts.audience IS NULL OR posts.audience = 'public'
        OR (posts.audience = 'friends' AND EXISTS (
          SELECT 1 FROM friendships WHERE status = 'accepted'
          AND ((requester_id = posts.user_id AND addressee_id = auth.uid())
            OR (requester_id = auth.uid() AND addressee_id = posts.user_id))
        ))
        OR (posts.audience = 'circles' AND EXISTS (
          SELECT 1 FROM post_circles pc JOIN circle_members cm ON cm.circle_id = pc.circle_id
          WHERE pc.post_id = posts.id AND cm.user_id = auth.uid() AND cm.status = 'member'
        ))
      )
    )
  );

-- INSERT: Authenticated users can create posts (20260208_post_delete_policy.sql)
CREATE POLICY "Authenticated users can create posts"
  ON posts FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = user_id);

-- UPDATE: Users and admins can update posts (20260202_admin_rls_policies.sql)
CREATE POLICY "Users and admins can update posts"
  ON posts FOR UPDATE TO authenticated
  USING (auth.uid() = user_id OR is_admin());

-- DELETE: Users and admins can delete posts (20260208_post_delete_policy.sql)
CREATE POLICY "Users and admins can delete posts"
  ON posts FOR DELETE TO authenticated
  USING (auth.uid() = user_id OR is_admin());

Related

• profiles -- Author profile via user_id
• comments -- Responses to this post
• post_images -- Multiple images per post
• post_circles -- Circle targeting for circle-audience posts
• saved_posts -- Users saving posts to shelves

---

Last updated: 2026-02-07