1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
|
import { Database } from "bun:sqlite";
import { existsSync, mkdirSync } from "node:fs";
import { homedir } from "node:os";
import { isAbsolute, join } from "node:path";
/**
* Returns the directory for persistent Dispatch data, following XDG Base
* Directory spec on Linux: `$XDG_DATA_HOME/dispatch` (defaults to
* `~/.local/share/dispatch`).
*/
function getDataDir(): string {
const xdg = process.env.XDG_DATA_HOME;
const base = xdg && isAbsolute(xdg) ? xdg : join(homedir(), ".local", "share");
return join(base, "dispatch");
}
let _db: Database | null = null;
/**
* Get (or create) the singleton SQLite database.
*
* - Creates the data directory if it doesn't exist.
* - Creates `dispatch.db` if it doesn't exist.
* - Enables WAL journal mode for concurrent read performance.
*/
export function getDatabase(): Database {
if (_db) return _db;
const dir = getDataDir();
if (!existsSync(dir)) {
mkdirSync(dir, { recursive: true });
}
const dbPath = join(dir, "dispatch.db");
_db = new Database(dbPath, { create: true });
// WAL mode: better concurrent read performance, safe for single-writer
_db.run("PRAGMA journal_mode = WAL;");
// Recommended for WAL: normal synchronous is safe and faster
_db.run("PRAGMA synchronous = NORMAL;");
// Enable foreign keys
_db.run("PRAGMA foreign_keys = ON;");
// Create tables
_db.run(`CREATE TABLE IF NOT EXISTS credentials (
key_id TEXT PRIMARY KEY,
provider TEXT NOT NULL,
access_token TEXT NOT NULL,
refresh_token TEXT NOT NULL,
expires_at INTEGER NOT NULL,
subscription_type TEXT,
source_file TEXT,
imported_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL
)`);
// Wake schedule: 4 rows per marked hour (one per :00 / :15 / :30 / :45 probe
// slot). The PK is (hour, slot_minute). Destructive migration off the legacy
// single-row-per-hour schema: detect by absence of the `slot_minute` column
// and drop the old table. Other tables (credentials, api_keys, usage_cache,
// settings, tabs, chunks) are NOT touched.
const legacyWakeSchema = (() => {
try {
const cols = _db.query("PRAGMA table_info(wake_schedule)").all() as Array<{ name: string }>;
if (cols.length === 0) return false; // table doesn't exist yet
return !cols.some((c) => c.name === "slot_minute");
} catch {
return false;
}
})();
if (legacyWakeSchema) {
_db.run("DROP TABLE IF EXISTS wake_schedule");
}
_db.run(`CREATE TABLE IF NOT EXISTS wake_schedule (
hour INTEGER NOT NULL CHECK (hour BETWEEN 0 AND 23),
slot_minute INTEGER NOT NULL CHECK (slot_minute IN (0, 15, 30, 45)),
next_wake_at INTEGER NOT NULL,
PRIMARY KEY (hour, slot_minute)
)`);
_db.run(`CREATE TABLE IF NOT EXISTS usage_cache (
key_id TEXT PRIMARY KEY,
provider TEXT NOT NULL,
cached_at INTEGER NOT NULL,
report_json TEXT NOT NULL
)`);
_db.run(`CREATE TABLE IF NOT EXISTS api_keys (
key_id TEXT PRIMARY KEY,
provider TEXT NOT NULL,
api_key TEXT NOT NULL,
imported_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL
)`);
_db.run(`CREATE TABLE IF NOT EXISTS tabs (
id TEXT PRIMARY KEY,
title TEXT NOT NULL,
key_id TEXT,
model_id TEXT,
parent_tab_id TEXT,
status TEXT NOT NULL DEFAULT 'idle',
is_open INTEGER NOT NULL DEFAULT 1,
position INTEGER NOT NULL DEFAULT 0,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL
)`);
try {
_db.run("ALTER TABLE tabs ADD COLUMN parent_tab_id TEXT");
} catch {
// Column already exists — ignore
}
// ─── Append-only chunk log (replaces the old `messages` blob table) ──
//
// A conversation is stored as a flat, append-only stream of chunk rows
// keyed by a per-tab monotonic `seq`. "Message" and "turn" are DERIVED
// groupings (see db/chunks.ts), never stored containers. This is what
// powers per-chunk frontend pagination AND the stable per-step wire
// format that fixes Anthropic prompt-cache churn (see notes/plan-chunk-log.md).
//
// role : 'user' | 'assistant' | 'tool' | 'system'
// type : 'text' | 'thinking' | 'tool_call' | 'tool_result' | 'error' | 'system'
// step : LLM round-trip index within a turn (user/system rows = 0)
// data_json: the type-specific payload (see ChunkData in types)
_db.run(`CREATE TABLE IF NOT EXISTS chunks (
id TEXT PRIMARY KEY,
tab_id TEXT NOT NULL,
seq INTEGER NOT NULL,
turn_id TEXT NOT NULL,
step INTEGER NOT NULL DEFAULT 0,
role TEXT NOT NULL,
type TEXT NOT NULL,
data_json TEXT NOT NULL,
created_at INTEGER NOT NULL
)`);
_db.run(`CREATE INDEX IF NOT EXISTS idx_chunks_tab_seq ON chunks(tab_id, seq)`);
// One-shot migration off the legacy `messages` blob model. Beta software,
// no backward compatibility: the old chat history is destroyed (tabs +
// messages), while settings / credentials / api_keys / usage_cache /
// wake_schedule are preserved. Detect the old schema by the presence of
// the `messages` table; once dropped, this branch never runs again.
const hasLegacyMessages = _db
.query("SELECT name FROM sqlite_master WHERE type='table' AND name='messages'")
.get() as { name: string } | null;
if (hasLegacyMessages) {
_db.run("DROP TABLE IF EXISTS messages");
// Clear conversation containers too (fresh slate for the new model).
_db.run("DELETE FROM tabs");
_db.run("DELETE FROM chunks");
}
_db.run(`CREATE TABLE IF NOT EXISTS settings (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
)`);
return _db;
}
/** Close the database connection (e.g. on shutdown). */
export function closeDatabase(): void {
if (_db) {
_db.close();
_db = null;
}
}
/** Returns the path where the database file lives (or will live). */
export function getDatabasePath(): string {
if (_db) return _db.filename;
const dir = getDataDir();
return join(dir, "dispatch.db");
}
|
