Database Overview
CommonPlace uses PostgreSQL via Supabase with Row Level Security (RLS) on all tables. Authentication is handled through Supabase Auth. The database contains 45 tables organized into 7 categories.
Core User Tables
| Table | Purpose |
|---|---|
profiles | User identity, preferences, moderation status, privacy settings |
posts | User-created content with intent context and audience scoping |
comments | Threaded responses to posts |
post_images | Multiple image attachments per post |
saved_posts | User-saved posts with personal notes |
shelves | Folders for organizing saved posts |
user_feed_state | Per-user slow-feed pacing state |
intent_styles | Per-user visual customization for each intent type |
avatar_library | User avatar collection (max 24) |
feedback | User-submitted platform feedback |
Social / Relationship Tables
| Table | Purpose |
|---|---|
friendships | Mutual friend connections with request workflow |
blocks | User blocking with complete disappearance |
relationship_types | User-defined relationship categories |
relationship_edges | Labeled connections with optional encryption |
invite_links | Shareable invite codes with usage limits |
Circle Tables
| Table | Purpose |
|---|---|
circles | Small group spaces for organizing friends |
circle_members | Circle membership with roles |
post_circles | Junction: posts targeted to circles |
circle_feed_state | Per-user per-circle feed pacing |
charter_toggle_definitions | System-defined community norm toggles |
circle_charters | Per-circle community norm toggle states |
HomeRoom Tables
| Table | Purpose |
|---|---|
home_room_settings | Per-user room configuration, theme, layout |
home_room_modules | Content modules placed in rooms |
room_styles | CSS customization for rooms |
module_styles | CSS customization for individual modules |
profile_photos | Photo gallery for rooms |
profile_links | External links displayed in rooms |
featured_posts | Posts pinned to rooms |
guestbook_entries | Visitor messages with moderation |
MeBook Tables
| Table | Purpose |
|---|---|
mebook_sections | System-defined self-reflection sections |
mebook_questions | Questions with type and preference mapping |
mebook_question_options | Answer options for multiple-choice questions |
mebook_responses | User answers to questions |
mebook_user_progress | Section completion tracking |
Messaging Tables
| Table | Purpose |
|---|---|
conversations | DM and group chat containers |
conversation_participants | Conversation membership with step-away tracking |
messages | Individual messages with reply threading and retention |
message_reactions | Curated emoji reactions (6 types only) |
Admin & Security Tables
| Table | Purpose |
|---|---|
admin_roles | Admin/moderator role assignments |
reports | User-submitted moderation reports |
admin_audit_log | Audit trail of admin actions |
user_actions | Administrative actions against users |
rate_limits | Per-user per-action rate tracking |
rate_limit_config | Rate limit rules per action type |
security_audit_log | Security event audit trail |
Key Design Principles
- All tables use RLS -- No data is accessible without authentication and policy checks
- No public metrics -- No like counts, view counts, follower counts in the schema
- Privacy by default -- Saved posts, relationship edges, MeBook responses are private
- Pacing is structural -- Feed state tables enforce slow-feed at user and circle level
- Block = disappearance -- Blocked users see 404, not "blocked" message
- Curated reactions -- Only 6 reaction types, preventing performative behavior
- Client-side encryption -- Relationship edges support encrypted labels and notes
Security
All tables use Row Level Security (RLS) policies. See Database Security for details.
Last updated: 2026-02-07