Annotated entity-relationship diagram

The schema,
as it is lived in

Nineteen tables across nine domain clusters. Every relationship carries crow's-foot cardinality; every table carries the annotations that make it legible to the next engineer. The rigid frame is the diagram; the notes are the record of why. This document is the frame, specified.

19

Tables

9

Domain clusters

8

Visibility values

2

Sorted-pair invariants

00 · Legend

How to read this

Table markers

† Soft deleteCarries deleted_at; reads exclude tombstoned rows.

⇄ PairSorted-pair invariant: two same-type FKs with a<b, DB-enforced, unique per pair.

◆ HubDrawn once, centrally. Every FK→users across the schema resolves here.

Column markers

PKPrimary key. Where noted inline, the PK is a FK or a natural key — not the default id.

FKForeign key; its target is tagged → table on the same row.

PIIPersonal data — readable only by owner, staff, admin.

Cardinality key

1 exactly one

N many

0..1 zero or one (optional)

M many, in an N—M join

Omitted & assumed

Every table has id (uuid PK) plus inserted_at / updated_at — all three omitted from the diagrams. Exceptions (PK is a FK or natural key) are noted inline.
All enum columns are DB CHECK-constrained; full value lists shown inline.
Plain text everywhere — no HTML is stored anywhere in this schema.

Callout 1

diagram-level annotation

The access model

Content gates on a single visibility enum, resolved against four facts about the viewer. The site is login-walled, so every value assumes an authenticated member.

Tier

users.tier

Ordered ladder: open → community → gated. A registration code grants community.

Role

users.role

member / staff / admin / system.

Credential

users.verified_practitioner

A boolean — vouched, verified practitioners.

Relationship

friendships

An accepted friendship with the author.

visibility value

who passes

open

Any signed-in user — the site is login-walled.

community

Tier ≥ community.

gated

Tier = gated.

practitioners

verified_practitioner = true.

moderators

Moderates ≥ 1 board, or role is staff / admin.

admins

role = admin.

friends

An accepted friendship with the author.

private

The author only.

01 · Cluster

3 tables

Accounts

users

◆ Hub

Central hub · every FK→users resolves here

·

username citext unique

·

email citext PII unique

·

birthday date PII

18+ enforced at registration

·

hashed_password string

·

tier enum

open community gated

a registration code grants community

·

role enum

member staff admin system

·

verified_practitioner boolean

Note

PII fields are readable only by owner, staff, and admin. One seeded the_desk user (role = system) sends platform DMs.

tokens

PK = jti (string) — no uuid id

PK

jti string

·

subject string

·

purpose string

·

expires_at timestamp

·

extra_data jsonb

FK

user_id uuid → users

Note

Auth machinery. A daily sweep expires stale tokens.

user_blocks

FK

blocker_id uuid → users

FK

blocked_id uuid → users

unique (blocker, blocked) directional — no pair sort

Note

Blocks veto new conversations, messages, and friend requests in both directions.

Relationships — crow's-foot

users 1 N tokens

One member holds many auth tokens.

users 1 N user_blocks

As blocker — the member who initiated each block.

users 1 N user_blocks

As blocked — the member on the receiving end.

02 · Cluster

2 tables

Profiles

profiles

PK = user_id (FK→users) · 1—1

PK

user_id uuid → users

·

headline string

·

description text

plain text

·

location string

·

pronouns string

·

theme enum

brutalist cyberpunk lavender bme2005

·

modifications text[]

chip list, ≤24

FK

avatar_blob_id uuid → media_blobs nullable

profile_gallery_images

FK

user_id uuid → users

FK

blob_id uuid → media_blobs

·

position integer

unique (user, blob) ≤24 per user

Note

A single flat gallery per profile, shown below friends.

Relationships — crow's-foot

users 1 1 profiles

Exactly one profile per member.

users 1 N profile_gallery_images

A member owns many gallery images.

media_blobs 1 N profile_gallery_images

Each blob may be placed in many gallery rows.

03 · Cluster

3 tables

Diary

diary_entries

† Soft del

FK

user_id uuid → users

author

·

title string

·

body text

plain text

·

status enum

draft published

immutable published_at · per-user slug

·

visibility enum

open community gated friends private

Note

private = published but author-only (a journal) — distinct from draft.

diary_entry_media

FK

entry_id uuid → diary_entries

CASCADE

FK

blob_id uuid → media_blobs

·

position integer

unique (entry, blob) ≤24 per entry images only at MVP

diary_comments

† Soft del

FK

entry_id uuid → diary_entries

CASCADE

FK

user_id uuid → users

author

·

body text

plain text

Note

Read access follows the entry’s visibility; the author or entry owner may delete.

Relationships — crow's-foot

users 1 N diary_entries

A member authors many entries.

diary_entries 1 N diary_entry_media

An entry carries many ordered images.

diary_entries 1 N diary_comments

An entry gathers many comments.

users 1 N diary_comments

A member authors many comments.

04 · Cluster

5 tables

Forums

forum_categories

·

name string

·

subtitle string

·

position integer

·

visibility enum

open community gated practitioners moderators admins

Note

Admin-curated; four seeded categories.

forum_boards

FK

category_id uuid → forum_categories

·

name string

·

description text

·

position integer

·

visibility enum

open community gated practitioners moderators admins

Note

Board visibility is the effective gate — there is no inheritance from the category.

forum_board_moderators

FK

board_id uuid → forum_boards

FK

user_id uuid → users

unique (board, user)

Note

Grants pin / lock / delete on that board; membership in ≥1 board passes moderators visibility. Staff and admin hold these powers everywhere without a row.

forum_threads

† Soft del

FK

board_id uuid → forum_boards

FK

user_id uuid → users

starter

·

title string

·

pinned boolean

·

locked boolean

·

view_count integer

Note

No body — the opening post is post #1, created in the same transaction. Reply / view counts and last-post are live aggregates.

forum_posts

† Soft del

FK

thread_id uuid → forum_threads

FK

user_id uuid → users

author

·

body text

plain text

·

edited_at timestamp

visible "edited" stamp

FK

reply_to_post_id uuid → forum_posts nullable

self-ref · SET NULL

Note

reply_to renders a reply-context box; it is the future threading parent.

Relationships — crow's-foot

forum_categories 1 N forum_boards

A category holds many boards.

forum_boards 1 N forum_threads

A board holds many threads.

forum_threads 1 N forum_posts

A thread holds many posts.

forum_posts 0..1 N forum_posts

Self-referential reply-to; a post may answer one parent.

forum_boards N M users

Moderators — many-to-many via forum_board_moderators.

05 · Cluster

3 tables

Events

events

† Soft del

FK

user_id uuid → users

host

·

name string

·

description text

plain text

·

category enum

convention suspension_ritual workshop meetup talk_film

·

visibility enum

open community gated practitioners

·

status enum

draft published cancelled

immutable published_at

·

starts_on date

·

ends_on date

·

start_time time

+ timezone (IANA)

·

venue_name string

·

address string

·

directions text

·

location string

short display line

·

cost string

FK

image_blob_id uuid → media_blobs nullable

Note

Venue is inline — no separate venue table. Cancelled events stay visible.

event_sessions

FK

event_id uuid → events

·

day date

·

starts_at time

·

ends_at time

open-ended end allowed

·

title string

·

position integer

·

participation_tier enum

open community gated practitioners

Note

Deliberately NOT named visibility — sessions are readable by anyone who can read the parent event. This gates ATTENDANCE only.

event_rsvps

FK

event_id uuid → events

FK

user_id uuid → users

·

status enum

going interested not_attending

·

reminded_at timestamp

one email, 7 days out

unique (event, user)

Note

No row = no RSVP. Only going counts as attending.

Relationships — crow's-foot

users 1 N events

A member hosts many events.

events 1 N event_sessions

An event has many scheduled sessions.

events N M users

Attendees — many-to-many via event_rsvps.

06 · Cluster

2 tables

Messages

conversations

⇄ Pair

FK

participant_a_id uuid → users

FK

participant_b_id uuid → users

·

last_message_at timestamp

·

last_read_at_a / _b timestamp

per-side read cursor ×2

·

unread_count_a / _b integer

per-side ×2

pair: participant_a < participant_b unique pair

Note

Exactly two participants; rows are permanent (no delete).

messages

† Soft del

FK

conversation_id uuid → conversations

CASCADE

FK

sender_id uuid → users

·

body text

plain text

·

kind enum

text friend_request

·

participant_a_id / _b uuid

denormalized, write-once

Note

Deleted messages render as tombstones ("[message deleted]"). Denormalized participant ids let read policies skip a join; the desk messages members through this same pipeline.

Relationships — crow's-foot

conversations 1 N messages

A conversation holds many messages.

users 1 N messages

A member sends many messages.

users N M users

Two participants per conversation (via conversations pair).

07 · Cluster

1 table

Friendships

friendships

⇄ Pair

FK

user_a_id uuid → users

FK

user_b_id uuid → users

FK

requested_by_id uuid → users

must be a or b, DB-enforced

·

status enum

pending accepted declined

·

responded_at timestamp

pair: user_a < user_b unique pair

Note

One row per unordered pair; friend counts are live aggregates. Accepted friendships satisfy friends visibility.

Relationships — crow's-foot

users N M users

Symmetric friendship graph — one row per unordered pair.

08 · Cluster

1 table

Activity

activity_entries

PK = user_id (FK→users) · 1—1

PK

user_id uuid → users

·

last_active_at timestamp

Note

Forward-only upsert, fed ONLY by diary publishes; drives the home-page diary-update timeline ordering. Unpublish never rolls it back.

Relationships — crow's-foot

users 1 1 activity_entries

One activity row per member.

09 · Cluster

1 table

Media

The orphan reaper runs nightly, deleting any blob no row references. Consumers: profiles.avatar_blob_id, events.image_blob_id, diary_entry_media.blob_id, profile_gallery_images.blob_id.

media_blobs

FK

owner_id uuid → users

·

bucket enum

avatars event_images diary_media gallery_images

·

key string

unique per bucket

·

content_type string

·

byte_size bigint

·

covered boolean

settable by owner at upload; toggled by owner/staff/admin via a dedicated action only

Note

covered is a harm filter, orthogonal to visibility (see Callout 2). Never allowed on avatar blobs.

Relationships — crow's-foot

media_blobs 1 0..1 profiles

Avatar — a blob backs at most one profile.

media_blobs 1 0..1 events

Event image — a blob backs at most one event.

media_blobs 1 N diary_entry_media

A blob may appear in many diary media rows.

media_blobs 1 N profile_gallery_images

A blob may appear in many gallery rows.

users 1 N media_blobs

A member owns many blobs.

Callout 2

near the Media cluster

Covered is not visibility

visibility

Answers who may access a row. Resolved against tier, role, credential, and friendship (Callout 1).

media_blobs.covered

A harm filter, orthogonal to visibility. Answers blur behind a click-through. Someone allowed to access a blob may still meet it covered.

Never allowed on avatar blobs. A covered avatar would break identity across the platform.

Checklist

Users fans out to everything

Drawn once, centrally — never duplicated per cluster. Every edge below terminates on the one users hub.

→ tokens → user_blocks · ×2 · blocker + blocked → profiles · 1—1 → profile_gallery_images → diary_entries → diary_comments → forum_board_moderators · N—M → forum_threads → forum_posts → events · host → event_rsvps · N—M → conversations · ×2 · participants → messages · sender → friendships · ×3 · a, b, requested_by → activity_entries · 1—1 → media_blobs · owner

The schema,as it is lived in

How to read this

The access model

Accounts

Profiles

Diary

Forums

Events

Messages

Friendships

Activity

Media

Covered is not visibility

Users fans out to everything

The schema,
as it is lived in