Database Schema

All tables, enums, and relationships in the Supa Hub database.

Updated 11d ago

The entire schema is defined in setup.sql at the repository root. It is idempotent and safe to run multiple times.

Enums

Enum	Values
hub_post_status_enum	visible, hidden, suspended, deleted
hub_space_status_enum	visible, hidden, suspended, deleted
hub_attachment_type_enum	image, youtube, link, carousel
hub_aspect_ratio_enum	16/9, 1/1, 3/4
hub_space_role_enum	admin, moderator, member, guest, suspended, banned
notification_type	comment_on_post, reply, milestone, space_role, mention, new_follower, follower_post

Auth Tables

profiles

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.

Column	Type	Notes
id	uuid PK
comment_id	uuid
mentioned_user_id	uuid
created_at	timestamptz

hub_user_follows

User-to-user follow relationships. Powers follower counts, profile widgets, and follow notifications.

Column	Type	Notes
follower_id	uuid	PK (composite). References profiles(id)
following_id	uuid	PK (composite). References profiles(id)
created_at	timestamptz

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.

Triggers & Functions

Enum

Values

hub_post_status_enum

visible, hidden, suspended, deleted

hub_space_status_enum

visible, hidden, suspended, deleted

hub_attachment_type_enum

image, youtube, link, carousel

hub_aspect_ratio_enum

16/9, 1/1, 3/4

hub_space_role_enum

admin, moderator, member, guest, suspended, banned

notification_type

comment_on_post, reply, milestone, space_role, mention, new_follower, follower_post

Column

Type

Notes

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

Column

Type

Notes

text PK

Role name

rank

int

Numeric rank for permission checks

description

text

Human-readable description

rank

description

admin

100

Site administrator

moderator

Content moderation

member

Regular user

suspended

Signed-in but limited

banned

No access

Column

Type

Notes

user_id

uuid PK

References auth.users(id)

role_id

text

References roles(id)

assigned_at

timestamptz

Column

Type

Notes

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

Column

Type

Notes

space_id

uuid

PK (composite)

user_id

uuid

PK (composite)

role_id

hub_space_role_enum

Default member

Column

Type

Notes

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