team_core/
mailbox.rs

1//! SQLite mailbox schema shared by `team-mcp` and integration tests.
2//!
3//! The actual connection handling lives in `team-mcp`; this module defines
4//! the schema + migrations so both crates agree on the shape of the data.
5
6/// Idempotent schema bootstrap. Safe to run on every connect.
7pub const SCHEMA: &str = r#"
8-- NOTE: pragmas (journal_mode=WAL, busy_timeout, foreign_keys) are set by
9-- the connection opener *before* this batch runs — concurrent openers race
10-- if we set them here.
11
12CREATE TABLE IF NOT EXISTS projects (
13    id   TEXT PRIMARY KEY,
14    name TEXT NOT NULL
15);
16
17CREATE TABLE IF NOT EXISTS agents (
18    id         TEXT PRIMARY KEY,          -- "<project>:<agent>"
19    project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
20    role       TEXT NOT NULL,
21    runtime    TEXT NOT NULL,
22    is_manager INTEGER NOT NULL DEFAULT 0,
23    reports_to TEXT                        -- short name, resolved within project
24);
25
26CREATE INDEX IF NOT EXISTS agents_project_idx ON agents(project_id);
27
28CREATE TABLE IF NOT EXISTS messages (
29    id           INTEGER PRIMARY KEY AUTOINCREMENT,
30    project_id   TEXT NOT NULL,
31    sender       TEXT NOT NULL,            -- "<project>:<agent>" or "user:<handle>" or "cli"
32    recipient    TEXT NOT NULL,            -- "<project>:<agent>" or "channel:<project>:<name>"
33    text         TEXT NOT NULL,
34    thread_id    TEXT,
35    sent_at      REAL NOT NULL,
36    delivered_at REAL,
37    acked_at     REAL
38);
39
40CREATE INDEX IF NOT EXISTS messages_recipient_idx
41    ON messages(recipient, acked_at);
42CREATE INDEX IF NOT EXISTS messages_project_idx
43    ON messages(project_id, sent_at);
44
45-- Channels + subscriptions + per-agent ACLs.
46CREATE TABLE IF NOT EXISTS channels (
47    id         TEXT PRIMARY KEY,               -- "<project>:<name>"
48    project_id TEXT NOT NULL,
49    name       TEXT NOT NULL,
50    wildcard   INTEGER NOT NULL DEFAULT 0       -- 1 iff members = "*"
51);
52
53CREATE TABLE IF NOT EXISTS channel_members (
54    channel_id TEXT NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
55    agent_id   TEXT NOT NULL,
56    PRIMARY KEY (channel_id, agent_id)
57);
58
59CREATE INDEX IF NOT EXISTS channel_members_agent_idx
60    ON channel_members(agent_id);
61
62CREATE TABLE IF NOT EXISTS agent_acls (
63    agent_id        TEXT PRIMARY KEY REFERENCES agents(id) ON DELETE CASCADE,
64    can_dm_json     TEXT NOT NULL DEFAULT '[]',    -- ["dev","critic"]
65    can_bcast_json  TEXT NOT NULL DEFAULT '[]'     -- ["product","all"]
66);
67
68-- Inter-project manager bridges.
69CREATE TABLE IF NOT EXISTS bridges (
70    id           INTEGER PRIMARY KEY AUTOINCREMENT,
71    from_agent   TEXT NOT NULL,             -- "<project>:<agent>", must be a manager
72    to_agent     TEXT NOT NULL,             -- "<project>:<agent>", must be a manager
73    topic        TEXT NOT NULL,
74    opened_by    TEXT NOT NULL,             -- "user:<handle>" or "cli"
75    opened_at    REAL NOT NULL,
76    expires_at   REAL NOT NULL,
77    closed_at    REAL
78);
79
80CREATE INDEX IF NOT EXISTS bridges_open_idx
81    ON bridges(expires_at, closed_at);
82
83-- Human-in-the-loop permission fabric.
84CREATE TABLE IF NOT EXISTS approvals (
85    id             INTEGER PRIMARY KEY AUTOINCREMENT,
86    project_id     TEXT NOT NULL,
87    agent_id       TEXT NOT NULL,
88    action         TEXT NOT NULL,          -- "publish", "deploy", ...
89    scope_tag      TEXT,                   -- optional narrower tag
90    summary        TEXT NOT NULL,
91    payload_json   TEXT,
92    status         TEXT NOT NULL,          -- pending | approved | denied | expired
93    requested_at   REAL NOT NULL,
94    decided_at     REAL,
95    decided_by     TEXT,
96    decision_note  TEXT,
97    expires_at     REAL NOT NULL
98);
99
100CREATE INDEX IF NOT EXISTS approvals_pending_idx
101    ON approvals(status, expires_at);
102
103-- Budget ledger. Rows are appended by interface adapters and by runtime
104-- cost parsers. `teamctl budget` aggregates per project/day.
105CREATE TABLE IF NOT EXISTS budget (
106    id          INTEGER PRIMARY KEY AUTOINCREMENT,
107    project_id  TEXT NOT NULL,
108    agent_id    TEXT,
109    runtime     TEXT,
110    usd         REAL NOT NULL DEFAULT 0,
111    input_tok   INTEGER NOT NULL DEFAULT 0,
112    output_tok  INTEGER NOT NULL DEFAULT 0,
113    observed_at REAL NOT NULL
114);
115
116CREATE INDEX IF NOT EXISTS budget_project_day_idx
117    ON budget(project_id, observed_at);
118
119-- Rate-limit events. Written by `teamctl rl-watch` whenever a runtime
120-- emits a rate-limit signature. Hooks (notify, webhook, run) run off these
121-- rows; the wrapper loop sleeps until `resets_at` before respawning.
122CREATE TABLE IF NOT EXISTS rate_limits (
123    id          INTEGER PRIMARY KEY AUTOINCREMENT,
124    agent_id    TEXT NOT NULL,
125    runtime     TEXT NOT NULL,
126    hit_at      REAL NOT NULL,
127    resets_at   REAL,                  -- nullable: sometimes we can't parse
128    raw_match   TEXT NOT NULL,
129    handled_at  REAL
130);
131
132CREATE INDEX IF NOT EXISTS rate_limits_agent_idx
133    ON rate_limits(agent_id, hit_at);
134"#;
team_core/mailbox.rs

team_core/
mailbox.rs
