Page:
Database Schema
Clone
Table of Contents
This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
Database Schema
PostgreSQL 15+. UUIDs as primary keys. All timestamps are TIMESTAMPTZ. Migrations are plain SQL files in crates/adapters/postgres/migrations/ — run automatically on API server startup.
Migration Files
| File | Contents |
|---|---|
001_initial_schema.sql |
Core tables: users, thoughts, follows, top_friends, tags, thought_tags, api_keys |
002_federation_columns.sql |
AP federation columns (ap_id, inbox_url, etc.) |
003_new_tables.sql |
likes, boosts, blocks, notifications |
004_search_indexes.sql |
PostgreSQL trigram indexes for full-text search |
005_federation_tables.sql |
remote_actors, federation-specific tables |
006_remote_actor_connections.sql |
remote_actor_connections (follower/following cache) |
007_content_text.sql |
Widens content column to TEXT |
008_rename_notifications_type.sql |
Notification type column rename |
009_failed_events.sql |
failed_events table for DLQ |
010_fix_reply_fk_on_delete.sql |
Fix FK cascade on reply_to |
Core Tables
users
| Column | Type | Notes |
|---|---|---|
id |
UUID PK | gen_random_uuid() |
username |
VARCHAR(32) | UNIQUE NOT NULL |
email |
VARCHAR(255) | UNIQUE NOT NULL |
password_hash |
TEXT | Argon2 |
display_name |
VARCHAR(50) | nullable |
bio |
VARCHAR(160) | nullable |
avatar_url |
TEXT | nullable |
header_url |
TEXT | nullable |
custom_css |
TEXT | nullable, sanitized before write |
created_at |
TIMESTAMPTZ | DEFAULT NOW() |
updated_at |
TIMESTAMPTZ | DEFAULT NOW() |
Federation columns added in migration 002: ap_id, inbox_url, is_remote (boolean).
thoughts
| Column | Type | Notes |
|---|---|---|
id |
UUID PK | |
user_id |
UUID | FK → users(id) ON DELETE CASCADE |
content |
TEXT | was VARCHAR(128), widened in 007 |
reply_to |
UUID | nullable FK → thoughts(id) |
visibility |
TEXT | public / followers / private |
ap_id |
TEXT | nullable — AP object URL for remote notes |
created_at |
TIMESTAMPTZ |
follows
| Column | Type | Notes |
|---|---|---|
follower_id |
UUID | FK → users(id) ON DELETE CASCADE |
following_id |
UUID | FK → users(id) ON DELETE CASCADE |
state |
TEXT | pending / accepted (for AP follow handshake) |
| PK | (follower_id, following_id) |
top_friends
| Column | Type | Notes |
|---|---|---|
user_id |
UUID | FK → users(id) ON DELETE CASCADE |
friend_id |
UUID | FK → users(id) ON DELETE CASCADE |
position |
SMALLINT | 1–5 |
| PK | (user_id, friend_id) | |
| UNIQUE | (user_id, position) |
tags / thought_tags
tags: id SERIAL PK, name VARCHAR(50) UNIQUE NOT NULL
thought_tags: join table — (thought_id UUID, tag_id INTEGER) both FK with CASCADE.
api_keys
| Column | Type | Notes |
|---|---|---|
id |
UUID PK | |
user_id |
UUID | FK → users(id) ON DELETE CASCADE |
key_hash |
TEXT | UNIQUE |
name |
VARCHAR(50) | user-provided label |
created_at |
TIMESTAMPTZ |
Social Tables (migration 003)
likes
| Column | Type | Notes |
|---|---|---|
user_id |
UUID | FK → users(id) ON DELETE CASCADE |
thought_id |
UUID | FK → thoughts(id) ON DELETE CASCADE |
created_at |
TIMESTAMPTZ | |
| PK | (user_id, thought_id) |
boosts
Same structure as likes.
blocks
| Column | Type | Notes |
|---|---|---|
blocker_id |
UUID | FK → users(id) |
blocked_id |
UUID | FK → users(id) |
created_at |
TIMESTAMPTZ | |
| PK | (blocker_id, blocked_id) |
notifications
| Column | Type | Notes |
|---|---|---|
id |
UUID PK | |
user_id |
UUID | FK → users(id) — recipient |
type |
TEXT | like, boost, follow, reply, etc. |
actor_id |
UUID | nullable — who triggered it |
thought_id |
UUID | nullable — related thought |
read |
BOOLEAN | DEFAULT false |
created_at |
TIMESTAMPTZ |
Federation Tables (migrations 005–006)
remote_actors
Caches resolved remote AP actor profiles.
| Column | Type | Notes |
|---|---|---|
id |
UUID PK | |
ap_url |
TEXT | UNIQUE — the actor's AP ID |
username |
TEXT | |
display_name |
TEXT | nullable |
avatar_url |
TEXT | nullable |
inbox_url |
TEXT | |
fetched_at |
TIMESTAMPTZ |
remote_actor_connections
Caches follower/following pages fetched from remote outboxes.
failed_events
DLQ for events that failed delivery after retries (migration 009).
Running Migrations
Migrations run automatically when the API server starts. To run manually:
cargo run -p bootstrap
# or point DATABASE_URL at a fresh DB and run the binary