toolkit
Private Social DB Architecture
PostgreSQL + Supabase
What It Is
A relational database architecture for social platforms where content visibility is not binary (public/private) but controlled per-post, per-group, and per-user. Built for apps where the data model is the product — where a wrong visibility rule means a real privacy breach, not just a bug.
How It Works
Schema Design
Core entities: users, groups, memberships, posts, post_visibility. Each post links to one or more visibility targets (a group, a user list, or a single recipient).
Row-Level Security
Supabase RLS policies enforce visibility at the database layer — not just in application code. A user can only SELECT posts where they appear in the resolved visibility set.
Group Membership Logic
Membership states (invited, active, removed) are tracked with timestamps. A removed member loses access to all posts shared after their removal date, retroactively enforced by the policy.
Scalability
Indexes on user_id, group_id, and created_at keep queries fast as content volume grows. Denormalised counters (view counts, member counts) reduce expensive aggregation queries.
When to Use It
When building any social or collaboration product where 'who can see this' is a core feature — not an afterthought. When GDPR or data residency requirements mean privacy needs to be enforced at the infrastructure level, not just the UI.
Tech Stack