Data Model

June 22, 2026 · View on GitHub

This document describes the key database tables and their conceptual relationships. It is a mental model for developers, not a full schema reference — see database/migrations/ for authoritative column definitions.

Core Message Tables
User Tables
FTN Network Tables
Real-Time Tables
Supporting Tables
Entity Relationship Overview

Core Message Tables

`echomail`

The central table. Stores every public FTN message received or posted.

Column	Notes
`id`	Primary key
`echoarea_id`	FK → `echoareas.id`
`from_name`	Sender's display name (FTN real name)
`from_address`	Sender's FTN address (zone:net/node.point)
`to_name`	Addressee (usually `All` for public posts)
`subject`	Message subject line
`body`	Full message text
`date_written`	Timestamp from the FTN packet header (unreliable — sender's clock)
`date_received`	Server-side timestamp set at import (`NOW() AT TIME ZONE 'UTC'`) — always reliable
`message_id`	The FTN `@MSGID` kludge value; used for deduplication and threading
`reply_to_id`	FK → `echomail.id`; self-referential parent for thread walks
`kludge_lines`	Raw kludge lines from the original packet (includes `CHRS`, `TZUTC`, etc.)
`message_charset`	Normalized charset for encoding/decoding (e.g. `CP437`, `UTF-8`)
`art_format`	Set when the message is ANSI, Sixel, RIPscrip, etc.

Key rule: prefer date_received for display ordering; show date_written only as supplementary information (it can be wrong or in the future if the sender's clock is off). Future-dated date_written values are suppressed from message list queries until they are no longer in the future.

`netmail`

Private point-to-point FTN messages. Structure mirrors echomail but without an echoarea_id. Has to_address (the recipient's FTN address) and is_read, is_deleted per-message state. Attachments are stored as files referenced by attachment_filename.

`echoareas`

One row per echo area (conference/forum).

Column	Notes
`id`	Primary key
`tag`	Area tag (e.g. `GENERAL`) — case-insensitive in queries
`domain`	Network domain (e.g. `fidonet`, `lovlynet`) — allows the same tag in multiple networks
`description`	Human-readable area name
`is_local`	When true, messages are never forwarded to uplinks
`is_active`	Inactive areas are hidden from all queries and API responses
`is_sysop_only`	When true, only admin users can see the area
`moderator`	FTN address of the area moderator, if any
`missing_chrs_charset`	Optional fallback charset used only when inbound FTN messages for this area have no `CHRS` kludge

The (tag, domain) pair is the logical key. Code that looks up areas by tag must also filter by domain when multiple networks are connected.

User Tables

`users`

Column	Notes
`id`	Primary key
`username`	Login identifier — unique, case-insensitive
`real_name`	FTN display name used in message headers — unique, case-insensitive
`password_hash`	bcrypt hash
`is_active`	Soft-delete / pending approval flag
`is_approved`	Set by admin after registration review
`is_admin`	Full sysop access
`credit_balance`	Current credit balance (modified only via `user_transactions`)
`last_login`	Timestamp of most recent login
`referral_code`	For the referral system

Both username and real_name are enforced as unique to prevent impersonation. A database trigger fires on insert/update to catch collisions across both columns simultaneously.

`users_meta`

Key-value store for arbitrary user metadata.

Column	Notes
`user_id`	FK → `users.id`
`keyname`	String key (e.g. `mcp_serverkey`, `user_settings`)
`valname`	String value (may be JSON for structured data)

Used for: MCP bearer keys, per-user AI and display settings, daily login tracking, and other extensible preferences. Unique index on (user_id, keyname).

`user_transactions`

Append-only credit ledger. Every credit change goes through here via UserCredit::transact(). The users.credit_balance column is the running total; user_transactions is the audit trail.

Column	Notes
`user_id`	FK → `users.id`
`amount`	Positive for credit, negative for debit
`description`	Human-readable reason (e.g. `Daily login bonus`, `Netmail sent`)
`created_at`	Transaction timestamp

FTN Network Tables

`echoareas`

See Core Message Tables above.

`user_echoarea_subscriptions`

Many-to-many join between users and echo areas. Controls which areas appear in a user's message list and which areas the system exports to each uplink.

`nodelist` / `nodelist_metadata` / `nodelist_flags`

Imported FTN nodelist data. nodelist holds one row per node (zone, net, node, point, name, location, sysop, phone, baud, flags). nodelist_metadata tracks the import date and nodelist edition. nodelist_flags normalizes the per-node capability flags.

`binkp_session_log`

One row per completed binkp session (inbound or outbound). Records duration, bytes exchanged, files transferred, and outcome. Used by the admin analytics dashboard.

Real-Time Tables

`sse_events`

The inter-process event bus. Any code can insert a row; BinkStream delivers it to connected browsers. Defined as UNLOGGED so inserts are fast — rows are transient delivery state, not persistent data.

Column	Notes
`id`	Monotonic sequence — used as the stream cursor
`event_type`	String type name (e.g. `chat_message`, `new_echomail`)
`payload`	JSONB — fat payload containing all fields needed to render the event
`user_id`	FK → `users.id` — if set, only that user receives the event
`admin_only`	When true, only admin users receive the event
`created_at`	Pruned after one hour by the admin daemon

See BinkStreamChannel.md for the full architecture.

Supporting Tables

Table	Purpose
`message_read_status`	Tracks which echomail/netmail messages each user has read
`saved_messages`	User-bookmarked messages
`shared_messages`	Webshare links for publicly accessible messages (see key columns below)
`drafts`	Saved message drafts
`address_book`	Per-user FTN address book entries
`chat_messages`	Local shoutbox and MRC chat history
`mrc_rooms` / `mrc_messages` / `mrc_users`	MRC multi-relay chat state
`dosbox_doors` / `door_sessions`	Door game definitions and active session tracking
`webdoor_sessions`	WebDoor session tokens
`fileareas`	File area definitions (tag, domain, description, path)
`shared_files`	Files shared via the webshare system
`freq_log` / `freq_outbound`	File request (FREQ) history and outbound queue
`qwk_conference_state` / `qwk_message_index`	QWK offline mail reader state
`interests` / `interest_echoareas` / `user_interest_subscriptions`	Topic-based area groupings
`ai_requests`	Per-request AI usage accounting
`ai_bots` / `ai_bot_activities`	AI bot definitions and activity log
`auto_feed_sources` / `auto_feed_source_echoareas`	Auto Feed source definitions, posting metadata, and target echo area fan-out
`packet_bbs_nodes` / `packet_bbs_sessions`	PacketBBS node registrations and radio sessions; `packet_bbs_sessions.session_state` stores flexible JSON command context such as current area, current message, and guided-flow state
`meshcore_contacts`	MeshCore companion contact list; rows are created by the bridge or pre-registered by users; `pub_key_full` is unique when known (partial unique index); `user_id` links to the owning BBS user
`meshcore_device_commands`	Queue of pending commands to be executed on a MeshCore radio by the bridge (e.g. `remove_contact`); populated on contact deletion; bridge polls and ACKs each row
`bulletins` / `bulletin_reads`	Sysop bulletin board
`bbs_directory`	Network BBS directory populated by echomail robots
`gateway_tokens`	SSO tokens for external service authentication
`password_reset_tokens`	Time-limited password reset tokens
`activity_categories` / `activity_types` / `user_activity_log`	User activity analytics

`shared_messages`

Column	Type	Description
`share_key`	`TEXT`	32-char hex identifier, used in `/shared/{shareKey}` URLs
`message_id` / `message_type`	`INTEGER` / `TEXT`	References the shared echomail or netmail row
`shared_by_user_id`	`INTEGER`	FK → `users.id`
`area_identifier`	`TEXT`	Echoarea tag (used in friendly-URL slugs)
`slug`	`TEXT`	Human-readable URL slug (e.g. `hello-world`)
`og_image_path`	`TEXT`	Absolute filesystem path to the uploaded OG preview image
`og_image_slug`	`TEXT`	Filename with extension (e.g. `abc123….jpg`); used as the URL parameter for `/shared-image/{og_image_slug}`
`ai_og_summary`	`TEXT`	AI-generated summary injected into `og:description`
`is_active`	`BOOLEAN`	Whether the share link is live
`is_public`	`BOOLEAN`	Whether anyone can view without authentication
`expires_at`	`TIMESTAMPTZ`	Optional expiry; `NULL` means never expires
`access_count`	`INTEGER`	Running count of page views

The og_image_slug is stored as the basename of og_image_path and is the canonical URL parameter for /shared-image/. Because it includes the file extension, social media crawlers can infer the image format from the URL.

`auto_feed_sources` / `auto_feed_source_echoareas`

auto_feed_sources stores the source-level Auto Feed configuration: feed URL, optional display name, source type, poster name, polling state, deduplication marker, and posting counters.

auto_feed_source_echoareas is the many-to-many join table that maps one source row to one or more target echoareas rows. Auto Feed polls one source row, then fans each newly discovered article out to every linked target area.

Entity Relationship Overview

users ──────────────────────────────────────────────────────────────┐
  │                                                                  │
  ├── users_meta (key-value preferences and secrets)                 │
  │                                                                  │
  ├── user_transactions (credit ledger)                              │
  │                                                                  │
  ├── user_echoarea_subscriptions ──── echoareas                     │
  │                                        │                         │
  │                                        └── echomail ─────────────┤
  │                                                │                 │
  │                                                └── (reply_to_id) │
  │                                                    (self-ref)    │
  ├── netmail (to/from users or FTN addresses)                       │
  │                                                                  │
  ├── message_read_status (echomail + netmail)                       │
  │                                                                  │
  ├── saved_messages                                                 │
  │                                                                  │
  ├── door_sessions ──── dosbox_doors                                │
  │                                                                  │
  ├── meshcore_contacts (user_id optional; bridge_node_id → packet_bbs_nodes)
  │                                                                  │
  └── ai_requests                                                    │
                                                                     │
sse_events (user_id optional FK → users) ───────────────────────────┘
nodelist (imported FTN network directory, no FK to users)
binkp.json (FTN uplink configuration — stored as JSON, not in the database)

The most important relationship for day-to-day development is users → user_echoarea_subscriptions → echoareas → echomail. Almost every message-related query joins these four tables.