Message Schema

Complete SQLite database schema for the swarm messaging system.

Database Location

.swarm/messages.db

PRAGMAs

Set on every connection open:

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;

PRAGMA	Value	Purpose
`journal_mode`	`WAL`	Write-Ahead Logging for concurrent readers
`busy_timeout`	`5000`	Wait up to 5 seconds on lock contention before returning SQLITE_BUSY

Table: `messages`

CREATE TABLE IF NOT EXISTS messages (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    thread_id    INTEGER REFERENCES messages(id),
    reply_to     INTEGER REFERENCES messages(id),
    sender       TEXT    NOT NULL,
    recipient    TEXT    NOT NULL,
    msg_type     TEXT    NOT NULL DEFAULT 'message',
    urgency      TEXT    NOT NULL DEFAULT 'normal',
    body         TEXT    NOT NULL,
    created_at   INTEGER NOT NULL,
    delivered_at INTEGER
);

Column Details

Column	Type	Nullable	Default	Description
`id`	`INTEGER`	No	Auto-increment	Primary key
`thread_id`	`INTEGER`	Yes	`NULL`	References root message in thread
`reply_to`	`INTEGER`	Yes	`NULL`	References the message being replied to
`sender`	`TEXT`	No	—	Sending agent name or `"operator"`
`recipient`	`TEXT`	No	—	Receiving agent name
`msg_type`	`TEXT`	No	`'message'`	One of: `message`, `task`, `status`, `nudge`
`urgency`	`TEXT`	No	`'normal'`	One of: `normal`, `urgent`
`body`	`TEXT`	No	—	Message content
`created_at`	`INTEGER`	No	—	Epoch nanoseconds (creation time)
`delivered_at`	`INTEGER`	Yes	`NULL`	Epoch nanoseconds (delivery time); `NULL` = pending

Enum Values

msg_type:

message — General inter-agent communication
task — Task assignment or delegation
status — Status updates
nudge — Liveness nudge

urgency:

normal — Standard delivery on next prompt build
urgent — Triggers router interrupt

Indexes

CREATE INDEX IF NOT EXISTS idx_messages_recipient_pending
    ON messages (recipient, delivered_at)
    WHERE delivered_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_messages_urgency_pending
    ON messages (urgency, delivered_at)
    WHERE delivered_at IS NULL AND urgency = 'urgent';

CREATE INDEX IF NOT EXISTS idx_messages_thread
    ON messages (thread_id) WHERE thread_id IS NOT NULL;

Index	Columns	Filter	Used By
`idx_messages_recipient_pending`	`(recipient, delivered_at)`	`delivered_at IS NULL`	`consume()` — fetch pending messages for an agent
`idx_messages_urgency_pending`	`(urgency, delivered_at)`	`delivered_at IS NULL AND urgency = 'urgent'`	`poll_urgent()` — router polling for urgent messages
`idx_messages_thread`	`(thread_id)`	`thread_id IS NOT NULL`	`thread()` — retrieve all messages in a thread

Key Queries

Send Message

INSERT INTO messages (sender, recipient, msg_type, urgency, body, created_at)
VALUES (?1, ?2, ?3, ?4, ?5, ?6)

Reply to Message

INSERT INTO messages (thread_id, reply_to, sender, recipient, msg_type, urgency, body, created_at)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)

The thread_id is inherited from the original message's thread (or uses the original's ID as the root).

Consume Pending Messages

-- Read pending messages
SELECT * FROM messages WHERE recipient = ?1 AND delivered_at IS NULL ORDER BY created_at;

-- Mark as delivered
UPDATE messages SET delivered_at = ?1 WHERE recipient = ?2 AND delivered_at IS NULL;

Both operations happen in a single transaction for atomicity.

Poll Urgent Messages

SELECT id, recipient FROM messages
WHERE urgency = 'urgent' AND delivered_at IS NULL;

Used by the router every 100ms to find messages that need interrupt delivery.

Prune Old Messages

DELETE FROM messages
WHERE delivered_at IS NOT NULL
  AND id NOT IN (
    SELECT id FROM messages
    WHERE delivered_at IS NOT NULL
    ORDER BY delivered_at DESC
    LIMIT 1000
  );

Keeps the 1000 most recently delivered messages; runs every 300 seconds.

WAL Checkpoint

PRAGMA wal_checkpoint(TRUNCATE);

Runs every 60 seconds to reclaim WAL file space.

Messaging — Conceptual overview
ADR-002: SQLite Messaging — Design rationale