Database schema

This page lists the tables in schema.sql. The file is idempotent: it uses CREATE ... IF NOT EXISTS and ADD COLUMN IF NOT EXISTS, so you can run it again on an existing database to upgrade it. See Database setup.

Tables

Table	Purpose
mailboxes	One row per mailbox: owner, folder, type, limits and counters
stored_messages	Messages, with payload and flags
mailbox_acls	Per-peer access entries for shared mailboxes
reader_cursors	Per-reader position in a public mailbox
documents	Current version of each document
document_versions	Retained earlier versions of documents
document_acls	Reader list of a shared document
directory_listings	Peer directory entries
feeds	Append-only feeds
feed_entries	Feed entries
feed_acls	Reader list of a shared feed
collections	Keyed collections
collection_items	Collection items as JSONB
collection_acls	Reader list of a shared collection
block_store	Reserved for content-addressed payload storage; unused in 1.1.0

mailboxes

Column	Type	Notes
id	BIGSERIAL	Primary key
owner_peer_id	TEXT	Owner; indexed
folder_path	TEXT	Unique with owner_peer_id
mailbox_type	SMALLINT	0 private, 1 shared, 2 public
created_at, last_access_at	TIMESTAMPTZ
max_messages	INTEGER	Default 1000
retention_days	INTEGER	Default 30
retention_count	INTEGER	Optional
current_sequence	INTEGER	Last sequence number assigned
message_count, message_bytes	INTEGER, BIGINT	Maintained counters; a statement trigger lowers them on delete

stored_messages

Column	Type	Notes
id	BIGSERIAL	Primary key
mailbox_id	BIGINT	References mailboxes, cascade delete
sequence_number	INTEGER	Indexed with mailbox_id
message_id	TEXT	Unique
recipient_peer_id, sender_peer_id	TEXT
payload	BYTEA	Stored as sent; ciphertext when the client encrypted it
priority	SMALLINT	0 to 3; indexed with mailbox_id and sequence_number
created_at, expires_at	TIMESTAMPTZ	expires_at indexed for the cleanup sweep
hop_count	INTEGER
flags_bitmap	INTEGER	IMAP-style message flags
sf_flags	INTEGER	Protocol flags
persistent	BOOLEAN
folder_path	TEXT

A trigger, trg_stored_messages_deleted, runs stored_messages_deleted() after each delete statement to update mailboxes.message_count and message_bytes.

mailbox_acls and reader_cursors

Table	Columns
mailbox_acls	id, mailbox_id (cascade), peer_id (indexed), access_mode (0 read-only, 1 write-only, 2 read-write), granted_at; unique (mailbox_id, peer_id)
reader_cursors	id, mailbox_id (cascade), reader_peer_id, last_sequence_read, last_access_at; unique (mailbox_id, reader_peer_id)

documents and document_versions

Table	Columns
documents	id, owner_peer_id, path (unique with owner), content (BYTEA), content_type, content_hash (the ETag; indexed), created_at, updated_at, updated_by_peer_id, version_number, history_enabled, max_history_versions, version_vector, visibility (default 0, private)
document_versions	id, document_id (cascade; indexed), version_number (unique with document_id), content, content_hash, content_type, created_at, created_by_peer_id
document_acls	document_id (cascade), peer_id, granted_at; primary key (document_id, peer_id)

directory_listings

Column	Type	Notes
id	BIGSERIAL	Primary key
owner_peer_id	TEXT	Unique
display_name, bio, avatar_hash	TEXT	display_name and bio are full-text indexed (English)
listed_at, updated_at	TIMESTAMPTZ	(updated_at DESC, owner_peer_id DESC) indexed for cursor paging
extras	JSONB

feeds and feed_entries

Table	Columns
feeds	id, owner_peer_id (indexed), path (unique with owner), title, description, entry_content_type (default application/json), created_at, last_entry_at, current_sequence, max_entries, max_age_days, collaborative_mode, visibility (default 2, public)
feed_entries	id, feed_id (cascade), sequence_number (unique with feed_id), content (BYTEA), content_hash, created_at, created_by_peer_id, entry_type; indexed by (feed_id, sequence_number), (feed_id, entry_type) and (feed_id, created_at)
feed_acls	feed_id (cascade), peer_id, granted_at; primary key (feed_id, peer_id)

collections and collection_items

Table	Columns
collections	id, owner_peer_id (indexed), path (unique with owner), name, created_at, last_modified_at, record_count, visibility (default 0, private)
collection_items	id, collection_id (cascade), key (unique with collection_id), content (JSONB, GIN-indexed), content_hash, version, created_at, updated_at, updated_by_peer_id
collection_acls	collection_id (cascade), peer_id, granted_at; primary key (collection_id, peer_id)

block_store

Column	Type	Notes
id	BIGSERIAL	Primary key
cid	TEXT	Unique
payload_bytes	BYTEA
collection	TEXT	Partial index where not null
metadata	JSONB	GIN-indexed
tombstoned	BOOLEAN	Partial index where false
created_at	TIMESTAMPTZ

Role and grants

The schema creates a ricochet login role without a password when none exists, and grants it SELECT, INSERT, UPDATE, DELETE on all tables and USAGE, SELECT on all sequences in public, including tables created later.