Linked 1:1 with Supabase auth.users. Auto-created on signup via trigger.
Column
Type
Notes
id
uuid PK
References auth.users(id)
username
text UNIQUE
Display name
full_name
text
From OAuth provider
avatar_url
text
Profile picture URL
bio
text
Short bio (max 250 chars)
slug
text UNIQUE
URL handle (e.g. /u/john)
cover_url
text
Profile cover image
sites
jsonb
Array of social links
created_at
timestamptz
roles
Global role definitions with numeric ranks.
Column
Type
Notes
id
text PK
Role name
rank
int
Numeric rank for permission checks
description
text
Human-readable description
Seed data:
id
rank
description
admin
100
Site administrator
moderator
80
Content moderation
member
50
Regular user
suspended
20
Signed-in but limited
banned
0
No access
user_global_roles
Maps users to their global role. One role per user.
Column
Type
Notes
user_id
uuid PK
References auth.users(id)
role_id
text
References roles(id)
assigned_at
timestamptz
Hub Tables
hub_spaces
Community spaces that contain posts.
Column
Type
Notes
id
uuid PK
creator_id
uuid
References profiles(id)
slug
text UNIQUE
URL handle, case-insensitive unique index
name
text
Display name
description
text
cover
text
Cover image URL
avatar
text
Space avatar URL
status
hub_space_status_enum
Default visible
access
integer
Minimum rank to view (0 = public)
created_at
timestamptz
hub_space_members
Space membership with per-space roles.
Column
Type
Notes
space_id
uuid
PK (composite)
user_id
uuid
PK (composite)
role_id
hub_space_role_enum
Default member
hub_posts
Posts within spaces. Slugs are auto-generated from titles via a trigger.
Column
Type
Notes
id
uuid PK
space_id
uuid
References hub_spaces(id)
author_id
uuid
References profiles(id)
slug
text UNIQUE
Auto-generated, case-insensitive
title
text
content
text
attachment_type
hub_attachment_type_enum
Nullable
attachment_url
text
Primary attachment URL
attachment_alt
text
Alt text for images
attachment_aspect_ratio
hub_aspect_ratio_enum
attachment_urls
text[]
Carousel image URLs (max 10)
views
integer
Default 0, non-negative constraint
pinned
boolean
Default false
status
hub_post_status_enum
Default visible
access
integer
Minimum rank to view
tags
text[]
created_at
timestamptz
updated_at
timestamptz
hub_post_reactions
Emoji reactions on posts. One reaction per user per post — the PRIMARY KEY (post_id, user_id) enforces this constraint. The emoji must be one of the site-configured reaction emojis (stored in hub_site_config under the reaction_emojis key).
Column
Type
Notes
post_id
uuid PK
References hub_posts(id)
user_id
uuid PK
References profiles(id)
emoji
text
Must be in the configured reaction set (default: 👍 ❤️ 😂 😮 😢)
created_at
timestamptz
hub_comments
Threaded comments on posts. Supports replies via reply_to_comment_id.
Column
Type
Notes
id
uuid PK
post_id
uuid
content
text
user_id
uuid
reply_to_comment_id
uuid
Nullable, self-referencing
visibility
boolean
Soft-delete flag
created_at
timestamptz
updated_at
timestamptz
hub_comment_reactions
Heart reactions on comments. One per user per comment.
Column
Type
Notes
id
uuid PK
comment_id
uuid
user_id
uuid
type
text
Currently heart
created_at
timestamptz
hub_comment_mentions
Tracks @mentions in comments. Auto-populated by a trigger that parses comment content.
A self-follow constraint (follower_id <> following_id) prevents users from following themselves. Indexes on both columns for efficient lookups. RLS enabled with read-only access for authenticated users.
Configuration Tables
hub_site_rules
Singleton table storing site-wide markdown rules.
Column
Type
Notes
id
boolean PK
Always true (singleton)
markdown
text
Markdown content
hub_site_config
Key-value store for global site settings. Extensible.
Column
Type
Notes
key
text PK
Config key name
value
jsonb
Config value
updated_at
timestamptz
Current keys:
Key
Default
Description
space_creation_min_rank
80
Minimum role rank to create spaces
Notification Tables
hub_notifications
Base notification events.
Column
Type
Notes
id
uuid PK
type
notification_type
actor_id
uuid
User who triggered the notification
resource_type
text
post, comment, space, profile
resource_id
uuid
context
jsonb
Extra data (slugs, titles, etc.)
created_at
timestamptz
hub_notification_recipients
Maps notifications to recipient users.
Column
Type
Notes
notification_id
uuid
user_id
uuid
read_at
timestamptz
Null until read
delivered_at
timestamptz
hub_post_milestones
Tracks which view/reaction milestones have been achieved per post to avoid duplicate notifications.
Column
Type
Notes
post_id
uuid
PK (composite)
milestone_type
text
views or reactions
milestone_value
integer
PK (composite)
achieved_at
timestamptz
Row Level Security
All auth tables (profiles, roles, user_global_roles) have RLS enabled with no policies, meaning only the service role client can access them. All data access goes through the API handlers using the admin client.
The hub_user_follows table has RLS enabled with read-only access for authenticated users — mutations are handled exclusively through API routes.
