1 Commits

Author SHA1 Message Date
user
f2af55e4a5 docs: update README schema section to match actual database schema
All checks were successful
check / check (push) Successful in 1m13s
Update the Schema section and related references throughout README.md to
accurately reflect the current 001_initial.sql migration:

- Rename 'users' table to 'sessions' with new columns: uuid, password_hash,
  signing_key, away_message
- Add new 'clients' table (uuid, session_id FK, token, created_at, last_seen)
- Add topic_set_by and topic_set_at columns to 'channels' table
- Update channel_members FK from user_id to session_id
- Add params column to messages table
- Update client_queues FK from user_id to client_id
- Update Queue Architecture diagram labels and surrounding text
- Update In-Memory Broker description to use client_id terminology
- Update Multi-Client Model MVP note to reflect sessions/clients split
2026-03-17 04:55:41 -07:00

View File

@@ -301,8 +301,8 @@ The server implements HTTP long-polling for real-time message delivery:
- The client disconnects (connection closed, no response needed) - The client disconnects (connection closed, no response needed)
**Implementation detail:** The server maintains an in-memory broker with **Implementation detail:** The server maintains an in-memory broker with
per-user notification channels. When a message is enqueued for a user, the per-client notification channels. When a message is enqueued for a client, the
broker closes all waiting channels for that user, waking up any blocked broker closes all waiting channels for that client, waking up any blocked
long-poll handlers. This is O(1) notification — no polling loops, no database long-poll handlers. This is O(1) notification — no polling loops, no database
scanning. scanning.
@@ -460,28 +460,28 @@ The entire read/write loop for a client is two endpoints. Everything else
│ │ │ │ │ │
┌─────────▼──┐ ┌───────▼────┐ ┌──────▼─────┐ ┌─────────▼──┐ ┌───────▼────┐ ┌──────▼─────┐
│client_queue│ │client_queue│ │client_queue│ │client_queue│ │client_queue│ │client_queue│
user_id=1 │ │ user_id=2 │ │ user_id=3 client_id=1│ │ client_id=2│ │ client_id=3│
│ msg_id=N │ │ msg_id=N │ │ msg_id=N │ │ msg_id=N │ │ msg_id=N │ │ msg_id=N │
└────────────┘ └────────────┘ └────────────┘ └────────────┘ └────────────┘ └────────────┘
alice bob carol alice bob carol
Each message is stored ONCE. One queue entry per recipient. Each message is stored ONCE. One queue entry per recipient client.
``` ```
The `client_queues` table contains `(user_id, message_id)` pairs. When a The `client_queues` table contains `(client_id, message_id)` pairs. When a
client polls with `GET /messages?after=<queue_id>`, the server queries for client polls with `GET /messages?after=<queue_id>`, the server queries for
queue entries with `id > after` for that user, joins against the messages queue entries with `id > after` for that client, joins against the messages
table, and returns the results. The `queue_id` (auto-incrementing primary table, and returns the results. The `queue_id` (auto-incrementing primary
key of `client_queues`) serves as a monotonically increasing cursor. key of `client_queues`) serves as a monotonically increasing cursor.
### In-Memory Broker ### In-Memory Broker
The server maintains an in-memory notification broker to avoid database The server maintains an in-memory notification broker to avoid database
polling. The broker is a map of `user_id → []chan struct{}`. When a message polling. The broker is a map of `client_id → []chan struct{}`. When a message
is enqueued for a user: is enqueued for a client:
1. The handler calls `broker.Notify(userID)` 1. The handler calls `broker.Notify(clientID)`
2. The broker closes all waiting channels for that user 2. The broker closes all waiting channels for that client
3. Any goroutines blocked in `select` on those channels wake up 3. Any goroutines blocked in `select` on those channels wake up
4. The woken handler queries the database for new queue entries 4. The woken handler queries the database for new queue entries
5. Messages are returned to the client 5. Messages are returned to the client
@@ -1936,45 +1936,51 @@ The database schema is managed via embedded SQL migration files in
**Current tables:** **Current tables:**
#### `sessions` #### `sessions`
| Column | Type | Description | | Column | Type | Description |
|----------------|----------|-------------| |-----------------|----------|-------------|
| `id` | INTEGER | Primary key (auto-increment) | | `id` | INTEGER | Primary key (auto-increment) |
| `uuid` | TEXT | Unique session UUID | | `uuid` | TEXT | Unique session UUID |
| `nick` | TEXT | Unique nick | | `nick` | TEXT | Unique nick |
| `password_hash`| TEXT | bcrypt hash (empty string for anonymous sessions) | | `password_hash` | TEXT | bcrypt hash (empty string for anonymous sessions) |
| `signing_key` | TEXT | Public signing key (empty string if unset) | | `signing_key` | TEXT | Public signing key (empty string if unset) |
| `away_message` | TEXT | Away message (empty string if not away) | | `away_message` | TEXT | Away message (empty string if not away) |
| `created_at` | DATETIME | Session creation time | | `created_at` | DATETIME | Session creation time |
| `last_seen` | DATETIME | Last API request time | | `last_seen` | DATETIME | Last API request time |
Index on `(uuid)`.
#### `clients` #### `clients`
| Column | Type | Description | | Column | Type | Description |
|-------------|----------|-------------| |--------------|----------|-------------|
| `id` | INTEGER | Primary key (auto-increment) | | `id` | INTEGER | Primary key (auto-increment) |
| `uuid` | TEXT | Unique client UUID | | `uuid` | TEXT | Unique client UUID |
| `session_id`| INTEGER | FK → sessions.id (cascade delete) | | `session_id` | INTEGER | FK → sessions.id (cascade delete) |
| `token` | TEXT | Unique auth token (SHA-256 hash of 64 hex chars) | | `token` | TEXT | Unique auth token (SHA-256 hash of 64 hex chars) |
| `created_at`| DATETIME | Client creation time | | `created_at` | DATETIME | Client creation time |
| `last_seen` | DATETIME | Last API request time | | `last_seen` | DATETIME | Last API request time |
Indexes on `(token)` and `(session_id)`.
#### `channels` #### `channels`
| Column | Type | Description | | Column | Type | Description |
|-------------|----------|-------------| |---------------|----------|-------------|
| `id` | INTEGER | Primary key (auto-increment) | | `id` | INTEGER | Primary key (auto-increment) |
| `name` | TEXT | Unique channel name (e.g., `#general`) | | `name` | TEXT | Unique channel name (e.g., `#general`) |
| `topic` | TEXT | Channel topic (default empty) | | `topic` | TEXT | Channel topic (default empty) |
| `created_at`| DATETIME | Channel creation time | | `topic_set_by`| TEXT | Nick of the user who set the topic (default empty) |
| `updated_at`| DATETIME | Last modification time | | `topic_set_at`| DATETIME | When the topic was last set |
| `created_at` | DATETIME | Channel creation time |
| `updated_at` | DATETIME | Last modification time |
#### `channel_members` #### `channel_members`
| Column | Type | Description | | Column | Type | Description |
|-------------|----------|-------------| |--------------|----------|-------------|
| `id` | INTEGER | Primary key (auto-increment) | | `id` | INTEGER | Primary key (auto-increment) |
| `channel_id`| INTEGER | FK → channels.id | | `channel_id` | INTEGER | FK → channels.id (cascade delete) |
| `user_id` | INTEGER | FK → users.id | | `session_id` | INTEGER | FK → sessions.id (cascade delete) |
| `joined_at` | DATETIME | When the user joined | | `joined_at` | DATETIME | When the user joined |
Unique constraint on `(channel_id, user_id)`. Unique constraint on `(channel_id, session_id)`.
#### `messages` #### `messages`
| Column | Type | Description | | Column | Type | Description |
@@ -1984,6 +1990,7 @@ Unique constraint on `(channel_id, user_id)`.
| `command` | TEXT | IRC command (`PRIVMSG`, `JOIN`, etc.) | | `command` | TEXT | IRC command (`PRIVMSG`, `JOIN`, etc.) |
| `msg_from` | TEXT | Sender nick | | `msg_from` | TEXT | Sender nick |
| `msg_to` | TEXT | Target (`#channel` or nick) | | `msg_to` | TEXT | Target (`#channel` or nick) |
| `params` | TEXT | JSON-encoded IRC-style positional parameters |
| `body` | TEXT | JSON-encoded body (array or object) | | `body` | TEXT | JSON-encoded body (array or object) |
| `meta` | TEXT | JSON-encoded metadata | | `meta` | TEXT | JSON-encoded metadata |
| `created_at`| DATETIME | Server timestamp | | `created_at`| DATETIME | Server timestamp |
@@ -1994,11 +2001,11 @@ Indexes on `(msg_to, id)` and `(created_at)`.
| Column | Type | Description | | Column | Type | Description |
|-------------|----------|-------------| |-------------|----------|-------------|
| `id` | INTEGER | Primary key (auto-increment). Used as the poll cursor. | | `id` | INTEGER | Primary key (auto-increment). Used as the poll cursor. |
| `user_id` | INTEGER | FK → users.id | | `client_id` | INTEGER | FK → clients.id (cascade delete) |
| `message_id`| INTEGER | FK → messages.id | | `message_id`| INTEGER | FK → messages.id (cascade delete) |
| `created_at`| DATETIME | When the entry was queued | | `created_at`| DATETIME | When the entry was queued |
Unique constraint on `(user_id, message_id)`. Index on `(user_id, id)`. Unique constraint on `(client_id, message_id)`. Index on `(client_id, id)`.
The `client_queues.id` is the monotonically increasing cursor used by The `client_queues.id` is the monotonically increasing cursor used by
`GET /messages?after=<id>`. This is more reliable than timestamps (no clock `GET /messages?after=<id>`. This is more reliable than timestamps (no clock