messaging.db Reference

Purpose: Inter-session coordination for multi-LLM environments Location: ~/PROJECTS/.coditect-data/context-storage/messaging.db Governance: ADR-160, ADR-173 Python API: scripts/core/session_message_bus.py Task: J.21.5.1

---

Overview

The messaging database enables coordination between concurrent LLM sessions (Claude, Codex, Gemini). It provides:

• Session registry — Track which LLM sessions are active, their projects, and heartbeat status
• Structured messaging — Pub/sub channels with delivery tracking (ADR-173)
• File locking — Advisory locks preventing concurrent edits to the same file
• Task claiming — Exclusive task ownership preventing duplicate work
• Conflict detection — Automatic alerts when sessions overlap on projects/files

---

Statistics (as of 2026-02-19)

Metric	Value
File Size	1.2 MB
Tables	9 (6 active + 2 legacy + 1 dedup)
Session Registry Entries	48 (historical)
Messages (v2)	423
Messages (v1 legacy)	73
Active Task Claims	2
Vacuum Reports	151
Journal Mode	WAL

---

Tables

session_registry

Active LLM session registration with heartbeat tracking. Sessions are auto-cleaned when heartbeat expires (default 300s stale timeout).

CREATE TABLE session_registry (
    session_id TEXT PRIMARY KEY,          -- e.g., "claude-29583"
    llm_vendor TEXT NOT NULL,             -- claude, codex, gemini
    llm_model TEXT,                       -- opus-4.6, o3, gemini-2.0-flash
    tty TEXT,                             -- Terminal device
    pid INTEGER,                          -- OS process ID
    project_id TEXT,                      -- Current project
    task_id TEXT,                         -- Currently claimed task
    active_files TEXT,                    -- JSON array of files being edited
    heartbeat_at TEXT NOT NULL,           -- Last heartbeat (stale detection)
    registered_at TEXT NOT NULL DEFAULT (datetime('now')),
    status TEXT DEFAULT 'active',         -- active, stale
    cwd TEXT,                             -- Working directory
    machine_uuid TEXT,                    -- Machine identifier
    hostname TEXT,                        -- Machine hostname
    last_active_at TEXT,                  -- Last activity
    session_uuid TEXT,                    -- UUID for /continue siblings
    user_id TEXT                          -- User identifier
);

session_messages

Structured inter-session messages with delivery tracking (ADR-173 v2). Supports channels, priority levels, directed messaging, and reply threading.

CREATE TABLE session_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sender_id TEXT NOT NULL,              -- Sending session
    sender_session_uuid TEXT,             -- Sender UUID
    recipient_id TEXT,                    -- Target (null = broadcast)
    channel TEXT NOT NULL,                -- session_lifecycle, task_broadcast, operator_alert, direct
    message_type TEXT NOT NULL,           -- started, ended, conflict, claim, etc.
    priority INTEGER NOT NULL DEFAULT 0,  -- 0=routine, 1=normal, 2=high, 3=critical
    status TEXT NOT NULL DEFAULT 'pending', -- pending, delivered, read
    project_id TEXT,                      -- Project context
    task_id TEXT,                         -- Related task
    activity TEXT,                        -- Activity description
    payload TEXT,                         -- JSON payload (already dict from API)
    reply_to INTEGER REFERENCES session_messages(id),
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    delivered_at TEXT,                    -- When marked delivered
    read_at TEXT,                         -- When marked read
    expires_at TEXT NOT NULL              -- Message expiration
);

Channels:

Channel	Purpose	Example Types
session_lifecycle	Session start/end events	started, ended, resumed
task_broadcast	Task claim/release notifications	claimed, released, completed
operator_alert	Conflict and coordination alerts	project_conflict, cwd_overlap, file_conflict
direct	Point-to-point messages	Any custom type

Delivery Tracking (ADR-173):

1. pending — Published, not yet seen
2. delivered — Displayed to user (e.g., by /session-status)
3. read — User took action (e.g., acknowledged via /session-conflicts)

file_locks

Advisory file locking. Released automatically when sessions become stale.

CREATE TABLE file_locks (
    file_path TEXT PRIMARY KEY,
    session_id TEXT NOT NULL,             -- Lock holder
    lock_type TEXT DEFAULT 'advisory',
    locked_at TEXT NOT NULL DEFAULT (datetime('now'))
);

task_claims

Exclusive task ownership. Prevents two sessions from working on the same TRACK task.

CREATE TABLE task_claims (
    task_id TEXT PRIMARY KEY,             -- e.g., "H.13.7"
    session_id TEXT NOT NULL,             -- Claiming session
    claimed_at TEXT NOT NULL DEFAULT (datetime('now')),
    status TEXT DEFAULT 'claimed'
);

message_dedup

Deduplication tracking with configurable coalescing windows.

CREATE TABLE message_dedup (
    sender_id TEXT NOT NULL,
    channel TEXT NOT NULL,
    message_type TEXT NOT NULL,
    task_id TEXT NOT NULL DEFAULT '',
    last_message_id INTEGER NOT NULL,
    last_sent_at TEXT NOT NULL,
    coalesce_window_seconds INTEGER NOT NULL DEFAULT 30,
    PRIMARY KEY (sender_id, channel, message_type, task_id)
);

vacuum_reports

Orphan detection and cleanup tracking for stale sessions, locks, and claims.

CREATE TABLE vacuum_reports (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sweep_type TEXT NOT NULL,             -- scheduled, manual
    created_at TEXT NOT NULL,
    task_id TEXT NOT NULL,
    orphan_type TEXT NOT NULL,            -- stale_session, orphan_lock, orphan_claim
    last_activity TEXT,
    last_session_id TEXT,
    track TEXT,
    description TEXT,
    evidence TEXT,
    resolution TEXT,
    resolved_at TEXT,
    resolved_by TEXT
);

Legacy Tables

Table	Status	Description
inter_session_messages	Legacy (v1)	Pre-ADR-173 unstructured messages
inter_session_messages_v1	Legacy (v1)	Backup of v1 messages

---

Python API

from scripts.core.session_message_bus import get_session_message_bus

bus = get_session_message_bus()

# Session management
bus.register_session(session_id, llm_vendor, pid, ...)
bus.start_heartbeat_thread()

# File locking
bus.lock_file("path/to/file.py")
bus.unlock_file("path/to/file.py")
locks = bus.get_file_locks()

# Task claiming
bus.claim_task("H.13.7", session_id="claude-29583")
bus.release_task("H.13.7")
claims = bus.get_task_claims()

# Messaging (ADR-173 v2)
bus.publish(channel="task_broadcast", message_type="claimed", task_id="H.13.7")
bus.send(recipient_id="codex-41200", message_type="ping")
messages = bus.poll("session_lifecycle", since_id=0, limit=20)
bus.mark_delivered(message_id)
bus.mark_read(message_id)

# Status
status = bus.get_cross_llm_status()
alerts = bus.get_operator_alerts(unread_only=True)
unread = bus.get_unread(recipient_id="claude-29583")
stats = bus.stats()

---

Related Commands

Command	Purpose
/session-status	Dashboard of all active sessions
/session-register	Register current session
/session-conflicts	Check for coordination conflicts
/session-rescue	Recover hung sessions

---

Related Documentation

• DATABASE-SCHEMA.md — Overview of all databases
• ER-DIAGRAMS.md — Visual ER diagrams
• DATA-DICTIONARY.md — Field-level documentation
• ADR-160 — Inter-session messaging architecture
• ADR-173 — Structured message schema
• Multi-Session Coordination Guide — Usage guide

---

Generated: 2026-02-19 Task: J.21.5.1