Database Schema

All tables live in a single Postgres public schema for Alpha. Production will move to per-bounded-context schemas once Supabase Auth is wired and the auth.users table linkage is owned.

ORM: Prisma 5 — schema at apps/api/prisma/schema.prismaDB: Supabase Postgres (pg 16) + pgvector + RLS

---

Identity Context

users

Primary user record. One row per registered account.

Column	Type	Notes
id	UUID PK	Internal identifier
email	text UNIQUE	Login email
username	text UNIQUE	Optional @handle
password_hash	text	bcrypt
first_name	text
last_name	text
date_of_birth	date
country	text
city	text
sex	text?	Optional
phone	text?	Optional
about	text?	Bio
profile_pic_url	text?	Cloudflare R2 URL
role	text	user | moderator | admin (default user)
banned_at	timestamptz?	Non-null = banned
invite_code_id	UUID FK → invite_codes	Code used to register
inviter_user_id	UUID?	Denormalised inviter ID
created_at	timestamptz
updated_at	timestamptz

---

invite_codes

Invite tokens in REG-XXXXXX format.

Column	Type	Notes
id	UUID PK
code	text UNIQUE	REG-[A-Z0-9]{6}
status	text	active | reserved | used | expired | revoked
generated_by_user_id	UUID?	Owner
reserved_by_session_id	text?	Session that reserved it
reserved_until	timestamptz?	TTL 30 min
used_by_user_id	UUID?	Who registered
used_at	timestamptz?
expires_at	timestamptz	Code validity window (30 days)
created_at	timestamptz

Indexes: status, generated_by_user_id

---

personas

Sub-identities per user, max 5. One is is_default = true.

Column	Type	Notes
id	UUID PK
user_id	UUID FK → users CASCADE
name	varchar(40)
about	varchar(280)?
accent	varchar(20)?	Token: ink | gold | sage | azure
profile_pic_url	text?
is_default	bool
created_at	timestamptz
updated_at	timestamptz

Unique: (user_id, name). Index: user_id

---

password_resets

Column	Type	Notes
token	text PK	Opaque short token
user_id	UUID FK → users
expires_at	timestamptz
used_at	timestamptz?
created_at	timestamptz

---

user_blocks / user_mutes / user_follows

All three use composite PK (actor_id, target_id).

Column	Type
blocker_id / muter_id / follower_id	UUID FK → users
blocked_id / muted_id / following_id	UUID FK → users
created_at	timestamptz

Note: user_follows has a DB CHECK constraint preventing self-follows.

---

push_tokens

Column	Type	Notes
id	UUID PK
user_id	UUID FK → users
token	text UNIQUE	ExponentPushToken[...]
platform	text	ios | android | web
device_name	text?
last_seen_at	timestamptz	Updated on each notification attempt

---

muted_keywords

Column	Type	Notes
id	UUID PK
user_id	UUID FK → users
term	text	Lower-cased; case-insensitive LIKE match
created_at	timestamptz

Unique: (user_id, term)

---

Interests Context

interests_catalog

The full interest hierarchy.

Column	Type	Notes
id	UUID PK
slug	text UNIQUE	URL-safe identifier
name	text	Display name
path	text	Dotted ltree-style: philosophy.existentialism
parent_id	UUID? FK → self	Null for L1 roots
level	int	1=category, 2=sub-interest, 3=deep-interest
accent_color	text?	Palette key: ink|gold|sage|coral|cyan|plum
is_custom	bool	False for catalog entries
created_at	timestamptz

Indexes: parent_id, level

---

user_interests

Per-user selections with engagement depth.

Column	Type	Notes
user_id	UUID FK → users CASCADE
interest_id	UUID FK → interests_catalog CASCADE
depth	int	1–5: Curious → Reading → Practicing → Deep → Lifework
expertise	int	1–5: self-rated expertise, distinct from depth
description	text?	Personal note
created_at	timestamptz
updated_at	timestamptz

PK: (user_id, interest_id). Indexes: user_id, interest_id

---

Social Context

circles

Column	Type	Notes
id	UUID PK
owner_id	UUID FK → users CASCADE
kind	text	general | family | work | inner | custom
name	text
accent	text?	Color token
created_at / updated_at	timestamptz

Unique: (owner_id, kind, name). Index: owner_id

---

circle_members

Column	Type	Notes
circle_id	UUID FK → circles CASCADE
member_id	UUID FK → users CASCADE
bond_level	text	inner-circle | acquaintance
note	text?
created_at	timestamptz

PK: (circle_id, member_id)

---

circle_invitations

Pending invite rows (created on invite, deleted/updated on respond).

Column	Type	Notes
id	UUID PK
circle_id	UUID FK
inviter_id	UUID FK
invitee_id	UUID FK
bond_level	text
status	text	pending | accepted | declined | revoked
message	text?
created_at	timestamptz
responded_at	timestamptz?

Unique: (circle_id, invitee_id, status) — one open invite per pair.

---

connection_intents

One active row per user (upserted on update).

Column	Type	Notes
user_id	UUID PK FK → users
intent	text	networking | friends | romantic | mentor | mentee | collab
note	text?	Discovery card text
is_active	bool	False = hidden from discovery
updated_at / created_at	timestamptz

---

Posts Context

posts

Column	Type	Notes
id	UUID PK
author_id	UUID FK → users CASCADE
interest_id	UUID? FK → interests	Primary interest tag
format	text	note|essay|photo|video|quote|thread|repost
title	text
excerpt	text?
body	text?
media_url	text?	R2 URL
quoted_post_id	UUID? FK → posts	For quote/repost
reactions	int	Materialised counter
replies	int	Materialised counter
shares	int	Materialised counter
bookmarks	int	Materialised counter
views	int	Materialised counter (fire-and-forget)
visibility	text	public | circles | inner
hidden_at	timestamptz?	Moderator hide
publish_at	timestamptz?	Scheduled publish
scheduled_emitted_at	timestamptz?	Prevents double-emit on restart
deleted_at	timestamptz?	Author soft-delete
persona_id	UUID?	Optional persona attribution
depth	int	0–5 content depth (default 0)
boost_score	float	Materialised boost multiplier
boost_expires_at	timestamptz?
pinned_at	timestamptz?	Profile pin, max 3 per user
is_journalism	bool	#journalism tag (rep gate ≥ 20)
created_at / updated_at	timestamptz

Indexes: author_id, (interest_id, created_at), created_at, quoted_post_id, persona_id

---

post_replies

Column	Type	Notes
id	UUID PK
post_id	UUID FK → posts CASCADE
author_id	UUID FK → users CASCADE
body	text
kind	text	reply|reaction|criticism|contribution
hidden_at	timestamptz?	Moderator hide
created_at	timestamptz

---

post_reactions

Column	Type	Notes
post_id	UUID FK
user_id	UUID FK
kind	text	heart | spark | bookmark — only heart counted in reactions counter
created_at	timestamptz

PK: (post_id, user_id, kind)

---

post_interests (junction)

Column	Type
post_id	UUID FK → posts CASCADE
interest_id	UUID FK → interests_catalog CASCADE
is_primary	bool

---

post_co_authors

Column	Type	Notes
id	UUID PK
post_id	UUID FK
user_id	UUID FK
split_share	int	0–100; server enforces sum of accepted ≤ 90
status	varchar(20)	pending | accepted | declined
invited_at	timestamptz
responded_at	timestamptz?

---

post_series

Column	Type
id	UUID PK
author_id	UUID FK → users
title	varchar(120)
description	varchar(500)?
created_at / updated_at	timestamptz

post_series_items

Column	Type
series_id	UUID FK CASCADE
post_id	UUID FK CASCADE
position	int (1-based)

Unique: (series_id, position)

series_follows

Column	Type
user_id	UUID FK → users
series_id	UUID FK → post_series
created_at	timestamptz

post_boosts

Column	Type	Notes
id	UUID PK
post_id	UUID
user_id	UUID
tier	int	1=×1.5/24h, 2=×2/48h
amount	int	Mana spent
expires_at	timestamptz
created_at	timestamptz	Append-only

post_bookmarks / post_mutes

Both composite PK (post_id, user_id).

---

Media

media_assets

Column	Type	Notes
id	UUID PK
owner_id	UUID FK → users
bucket	text	R2 bucket name
key	text	Object key
mime_type	text
size_bytes	int
width / height	int?	Stamped lazily
duration_ms	int?	For video/audio
status	text	pending until client confirms; committed after
created_at / committed_at	timestamptz

Unique: (bucket, key)

---

Reputation Context

reputation_events

Append-only ledger. Never UPDATE.

Column	Type	Notes
id	UUID PK
user_id	UUID
interest_id	UUID?	Null = platform-level nudge
delta	int	Positive or negative
reason	text	e.g. posts.created, peer_vote
context	jsonb?	Extra metadata
created_at	timestamptz

Indexes: user_id, (user_id, interest_id), created_at

---

reputation_snapshots

Materialised SUM per (user, interest).

Column	Type
user_id	UUID
interest_id	UUID
value	int (default 0)
updated_at	timestamptz

PK: (user_id, interest_id)

---

reputation_votes

Column	Type	Notes
id	UUID PK
voter_id	UUID
target_user_id	UUID
interest_id	UUID
value	int	-1 | 0 | +1
comment	varchar(280)?
created_at / updated_at	timestamptz

Unique: (voter_id, target_user_id, interest_id)

---

reputation_appeals

Column	Type	Notes
id	UUID PK
appellant_id	UUID FK → users
interest_id	UUID FK → interests
reason	varchar(500)
status	varchar(20)	pending | accepted | rejected
restore_delta	int?	Optional admin-set restoration amount
resolved_by_id	UUID?	Admin who resolved
resolved_at	timestamptz?
admin_note	varchar(500)?
created_at / updated_at	timestamptz

Partial unique index (in migration SQL): one pending appeal per (appellant, interest).

---

Invite Tree Context

invite_tree_nodes

Column	Type	Notes
user_id	UUID PK
inviter_user_id	UUID?
path	text	Dot-separated UUIDs root→node
depth	int	0 for root
created_at	timestamptz

Indexes: inviter_user_id, path

---

Messaging Context

conversations

Column	Type
id	UUID PK
kind	text (dm | group)
title	text?
created_at / updated_at	timestamptz

conversation_members

Column	Type	Notes
conversation_id	UUID FK CASCADE
user_id	UUID FK CASCADE
role	text	owner | member
joined_at	timestamptz
last_read_at	timestamptz?
muted_at	timestamptz?	Per-conversation mute

PK: (conversation_id, user_id)

messages

Column	Type
id	UUID PK
conversation_id	UUID FK CASCADE
author_id	UUID FK CASCADE
body	text
created_at	timestamptz

message_reactions

PK (message_id, user_id, emoji). emoji validated (1–8 graphemes).

---

Notifications Context

notifications

Column	Type	Notes
id	UUID PK
user_id	UUID FK → users
kind	text	e.g. reputation.changed | circle.invited
title	text
body	text?
link	text?	Deep link
payload	jsonb?	Extra structured data
read_at	timestamptz?	Null = unread
created_at	timestamptz

Indexes: (user_id, read_at), created_at

notification_preferences

Column	Type	Notes
user_id	UUID PK FK → users
channels	jsonb	{"push":true,"email":true,"inApp":true}
kinds	jsonb	{"reputation.changed": false}
updated_at	timestamptz

---

AI Assistant Context

assistant_sessions

Column	Type	Notes
id	UUID PK
user_id	UUID FK → users
status	text	in_progress | completed | abandoned
topic	text?	Starting interest slug
summary	text?	LLM-generated summary
started_at / completed_at	timestamptz

assistant_turns

Column	Type	Notes
id	UUID PK
session_id	UUID FK CASCADE
step	int	1–5
prompt	text	Assistant question
answer	text?	User reply
created_at / answered_at	timestamptz

Unique: (session_id, step)

assistant_suggestions

Column	Type	Notes
id	UUID PK
session_id	UUID FK CASCADE
interest_id	UUID FK → interests
confidence	float	0–1
depth	int	1–5
reason	varchar(280)?	LLM reasoning
applied	bool	True once user accepted
created_at	timestamptz

---

Economy Context

emoji_wallets

Column	Type	Notes
user_id	UUID PK FK → users
balance	int	Current mana
lifetime_earned	int
lifetime_spent	int
last_regen_at	timestamptz	Guard for nightly regen
updated_at	timestamptz

emoji_payments

Append-only ledger. Never UPDATE.

Column	Type	Notes
id	UUID PK
sender_id	UUID
receiver_id	UUID
post_id	UUID?	Payment context
reply_id	UUID?
emotion	varchar(20)	One of 8 canonical emotions
tier	int	0–5
amount	int	Mana transferred
reversed	bool	True = reversal row
created_at	timestamptz

Indexes: (sender_id, created_at), (receiver_id, created_at), post_id, reply_id

---

Contribution Context

contribution_events

Append-only. Never UPDATE.

Column	Type	Notes
id	UUID PK
user_id	UUID
kind	varchar(40)	post_created | reply_created | reaction_given | emoji_pay_sent
delta	int	Positive; reversals use negative delta
ref_id	UUID?	Reference ID for audit
created_at	timestamptz

contribution_snapshots

Column	Type
user_id	UUID PK
total	int
updated_at	timestamptz

---

Wiki Context

wiki_entries

Column	Type
id	UUID PK
interest_id	UUID FK
content	text
summary	varchar(280)?
author_id	UUID FK
version	int
created_at / updated_at	timestamptz

Unique: (interest_id, version)

wiki_proposals

Column	Type	Notes
id	UUID PK
interest_id	UUID FK
content	text
summary	varchar(280)?
proposer_id	UUID FK
status	varchar(20)	pending | approved | rejected
net_votes	int	Materialised counter
created_at / updated_at	timestamptz

wiki_votes

Column	Type
id	UUID PK
proposal_id	UUID FK → wiki_proposals
voter_id	UUID
value	int (+1 | -1)
created_at	timestamptz

Unique: (proposal_id, voter_id)

---

System Tables

feature_flags

Column	Type	Notes
key	varchar(60) PK	e.g. emoji-pay
enabled	bool
label	varchar(120)	Admin UI label
description	varchar(280)?
updated_at	timestamptz
updated_by	UUID?	Admin who last toggled

audit_log

Append-only. Never UPDATE/DELETE.

Column	Type	Notes
id	UUID PK
actor_id	UUID	Admin/moderator
action	text	e.g. user.ban | post.hide
target_type	text	user | post | circle
target_id	text?	Resource UUID (string — not a real FK)
payload	jsonb	Prior/next state, reason
ip	text?	Best-effort from CF-Connecting-IP
user_agent	text?
created_at	timestamptz

Indexes: (actor_id, created_at), (action, created_at), (target_type, target_id)

reports

Column	Type	Notes
id	UUID PK
reporter_id	UUID FK → users
target_type	text	post | reply | user
target_id	UUID
reason	text	spam | harassment | illegal | misinfo | other
note	text?
status	text	open | reviewed | dismissed
reviewed_by_id	UUID?
reviewed_at	timestamptz?
created_at	timestamptz