opensession_api_types/
db.rs

1//! Shared database schema and query definitions.
2//! Used by: Axum server, Cloudflare Worker, TUI local DB.
3
4use std::sync::LazyLock;
5
6// ─── Schema ─────────────────────────────────────────────────────────────────
7
8/// SQLite schema (compatible with D1). Does NOT include FTS5 (Axum-only).
9pub const SCHEMA: &str = "\
10CREATE TABLE IF NOT EXISTS users (
11    id          TEXT PRIMARY KEY,
12    nickname    TEXT NOT NULL UNIQUE,
13    api_key     TEXT NOT NULL UNIQUE,
14    is_admin    BOOLEAN NOT NULL DEFAULT 0,
15    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
16);
17CREATE TABLE IF NOT EXISTS teams (
18    id          TEXT PRIMARY KEY,
19    name        TEXT NOT NULL,
20    description TEXT,
21    is_public   BOOLEAN NOT NULL DEFAULT 0,
22    created_by  TEXT NOT NULL,
23    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
24);
25CREATE TABLE IF NOT EXISTS team_members (
26    team_id  TEXT NOT NULL,
27    user_id  TEXT NOT NULL,
28    role     TEXT NOT NULL DEFAULT 'member',
29    joined_at TEXT NOT NULL DEFAULT (datetime('now')),
30    PRIMARY KEY (team_id, user_id)
31);
32CREATE TABLE IF NOT EXISTS sessions (
33    id                   TEXT PRIMARY KEY,
34    user_id              TEXT,
35    team_id              TEXT NOT NULL,
36    tool                 TEXT NOT NULL,
37    agent_provider       TEXT,
38    agent_model          TEXT,
39    title                TEXT,
40    description          TEXT,
41    tags                 TEXT,
42    created_at           TEXT NOT NULL,
43    uploaded_at          TEXT NOT NULL DEFAULT (datetime('now')),
44    message_count        INTEGER DEFAULT 0,
45    task_count           INTEGER DEFAULT 0,
46    event_count          INTEGER DEFAULT 0,
47    duration_seconds     INTEGER DEFAULT 0,
48    total_input_tokens   INTEGER DEFAULT 0,
49    total_output_tokens  INTEGER DEFAULT 0,
50    body_storage_key     TEXT NOT NULL
51);
52";
53
54/// Session links schema (shared: server, worker, local).
55pub const SESSION_LINKS_SCHEMA: &str = "\
56CREATE TABLE IF NOT EXISTS session_links (
57    session_id        TEXT NOT NULL,
58    linked_session_id TEXT NOT NULL,
59    link_type         TEXT NOT NULL DEFAULT 'handoff',
60    created_at        TEXT NOT NULL DEFAULT (datetime('now')),
61    PRIMARY KEY (session_id, linked_session_id)
62);
63CREATE INDEX IF NOT EXISTS idx_session_links_linked ON session_links(linked_session_id);
64";
65
66/// Local-only SQLite schema for TUI + Daemon (not on the server).
67pub const LOCAL_SCHEMA: &str = "\
68CREATE TABLE IF NOT EXISTS local_sessions (
69    id                   TEXT PRIMARY KEY,
70    source_path          TEXT,
71    sync_status          TEXT NOT NULL DEFAULT 'local_only',
72    last_synced_at       TEXT,
73    user_id              TEXT,
74    nickname             TEXT,
75    team_id              TEXT,
76    tool                 TEXT NOT NULL,
77    agent_provider       TEXT,
78    agent_model          TEXT,
79    title                TEXT,
80    description          TEXT,
81    tags                 TEXT,
82    created_at           TEXT NOT NULL,
83    uploaded_at          TEXT,
84    message_count        INTEGER DEFAULT 0,
85    task_count           INTEGER DEFAULT 0,
86    event_count          INTEGER DEFAULT 0,
87    duration_seconds     INTEGER DEFAULT 0,
88    total_input_tokens   INTEGER DEFAULT 0,
89    total_output_tokens  INTEGER DEFAULT 0,
90    git_remote           TEXT,
91    git_branch           TEXT,
92    git_commit           TEXT,
93    git_repo_name        TEXT,
94    pr_number            INTEGER,
95    pr_url               TEXT,
96    working_directory    TEXT
97);
98CREATE TABLE IF NOT EXISTS sync_cursors (
99    team_id    TEXT NOT NULL,
100    cursor     TEXT NOT NULL,
101    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
102    PRIMARY KEY (team_id)
103);
104CREATE TABLE IF NOT EXISTS body_cache (
105    session_id  TEXT PRIMARY KEY,
106    body        BLOB,
107    cached_at   TEXT NOT NULL DEFAULT (datetime('now'))
108);
109";
110
111// ─── Column lists ───────────────────────────────────────────────────────────
112
113/// Column list for session SELECT queries (joins with users table).
114pub const SESSION_COLUMNS: &str = "\
115s.id, s.user_id, u.nickname, s.team_id, s.tool, \
116s.agent_provider, s.agent_model, s.title, s.description, s.tags, \
117s.created_at, s.uploaded_at, s.message_count, s.task_count, \
118s.event_count, s.duration_seconds, \
119s.total_input_tokens, s.total_output_tokens";
120
121/// Column list for team SELECT queries.
122pub const TEAM_COLUMNS: &str = "\
123t.id, t.name, t.description, t.is_public, t.created_by, t.created_at";
124
125/// Column list for member SELECT queries (joins with users table).
126pub const MEMBER_COLUMNS: &str = "\
127tm.user_id, u.nickname, tm.role, tm.joined_at";
128
129// ─── Assembled queries ──────────────────────────────────────────────────────
130
131/// Base SELECT for session listings. Append WHERE / ORDER BY / LIMIT at call site.
132pub static SESSION_LIST_BASE: LazyLock<String> = LazyLock::new(|| {
133    format!(
134        "SELECT {SESSION_COLUMNS} \
135         FROM sessions s LEFT JOIN users u ON u.id = s.user_id"
136    )
137});
138
139/// SELECT a single session by id.
140pub static SESSION_GET: LazyLock<String> = LazyLock::new(|| {
141    format!(
142        "SELECT {SESSION_COLUMNS} \
143         FROM sessions s LEFT JOIN users u ON u.id = s.user_id \
144         WHERE s.id = ?1"
145    )
146});
147
148/// INSERT a new session (17 params).
149pub const SESSION_INSERT: &str = "\
150INSERT INTO sessions \
151(id, user_id, team_id, tool, agent_provider, agent_model, \
152 title, description, tags, created_at, \
153 message_count, task_count, event_count, duration_seconds, \
154 total_input_tokens, total_output_tokens, body_storage_key) \
155VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14,?15,?16,?17)";
156
157/// List teams for a user (via team_members join).
158pub static TEAM_LIST_MY: LazyLock<String> = LazyLock::new(|| {
159    format!(
160        "SELECT {TEAM_COLUMNS} FROM teams t \
161         INNER JOIN team_members tm ON tm.team_id = t.id \
162         WHERE tm.user_id = ?1 ORDER BY t.created_at DESC"
163    )
164});
165
166/// SELECT a single team by id.
167pub static TEAM_GET: LazyLock<String> =
168    LazyLock::new(|| format!("SELECT {TEAM_COLUMNS} FROM teams t WHERE t.id = ?1"));
169
170/// INSERT a new team (5 params: id, name, description, is_public, created_by).
171pub const TEAM_INSERT: &str = "\
172INSERT INTO teams (id, name, description, is_public, created_by) \
173VALUES (?1, ?2, ?3, ?4, ?5)";
174
175/// INSERT a team member.
176pub const MEMBER_INSERT: &str = "\
177INSERT INTO team_members (team_id, user_id, role) VALUES (?1, ?2, ?3)";
178
179/// DELETE a team member.
180pub const MEMBER_DELETE: &str = "\
181DELETE FROM team_members WHERE team_id = ?1 AND user_id = ?2";
182
183/// List members of a team (joins with users table).
184pub static MEMBER_LIST: LazyLock<String> = LazyLock::new(|| {
185    format!(
186        "SELECT {MEMBER_COLUMNS} \
187         FROM team_members tm \
188         INNER JOIN users u ON u.id = tm.user_id \
189         WHERE tm.team_id = ?1 \
190         ORDER BY tm.joined_at ASC"
191    )
192});
193
194// ─── Session links ──────────────────────────────────────────────────────────
195
196/// INSERT a session link.
197pub const SESSION_LINK_INSERT: &str = "\
198INSERT OR IGNORE INTO session_links (session_id, linked_session_id, link_type) \
199VALUES (?1, ?2, ?3)";
200
201/// SELECT all links for a session (both directions).
202pub const SESSION_LINKS_BY_SESSION: &str = "\
203SELECT session_id, linked_session_id, link_type, created_at \
204FROM session_links \
205WHERE session_id = ?1 OR linked_session_id = ?1";
206
207// ─── Auth queries ────────────────────────────────────────────────────────────
208
209/// Find user by API key (for Bearer osk_xxx auth).
210pub const USER_BY_API_KEY: &str = "\
211SELECT id, nickname, is_admin, email FROM users WHERE api_key = ?1";
212
213/// Find user by id (for JWT auth).
214pub const USER_BY_ID: &str = "\
215SELECT id, nickname, is_admin, email FROM users WHERE id = ?1";
216
217/// Find user by email (for login).
218pub const USER_BY_EMAIL_FOR_LOGIN: &str = "\
219SELECT id, nickname, password_hash, password_salt FROM users WHERE email = ?1";
220
221/// Check email existence.
222pub const USER_EMAIL_EXISTS: &str = "\
223SELECT COUNT(*) > 0 FROM users WHERE email = ?1";
224
225/// Count users (for first-user-is-admin check).
226pub const USER_COUNT: &str = "SELECT COUNT(*) FROM users";
227
228/// Insert user with email/password.
229pub const USER_INSERT_WITH_EMAIL: &str = "\
230INSERT INTO users (id, nickname, api_key, is_admin, email, password_hash, password_salt) \
231VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)";
232
233/// Insert user (legacy, no email).
234pub const USER_INSERT: &str = "\
235INSERT INTO users (id, nickname, api_key, is_admin) VALUES (?1, ?2, ?3, ?4)";
236
237/// Insert refresh token.
238pub const REFRESH_TOKEN_INSERT: &str = "\
239INSERT INTO refresh_tokens (id, user_id, token_hash, expires_at) VALUES (?1, ?2, ?3, ?4)";
240
241/// Lookup refresh token with user join.
242pub const REFRESH_TOKEN_LOOKUP: &str = "\
243SELECT rt.id, rt.user_id, rt.expires_at, u.nickname \
244FROM refresh_tokens rt JOIN users u ON u.id = rt.user_id \
245WHERE rt.token_hash = ?1";
246
247/// Delete refresh token by hash.
248pub const REFRESH_TOKEN_DELETE: &str = "\
249DELETE FROM refresh_tokens WHERE token_hash = ?1";
250
251/// Get password hash/salt for a user.
252pub const USER_PASSWORD_FIELDS: &str = "\
253SELECT password_hash, password_salt FROM users WHERE id = ?1";
254
255/// Update password.
256pub const USER_UPDATE_PASSWORD: &str = "\
257UPDATE users SET password_hash = ?1, password_salt = ?2 WHERE id = ?3";
258
259/// Regenerate API key.
260pub const USER_UPDATE_API_KEY: &str = "\
261UPDATE users SET api_key = ?1 WHERE id = ?2";
262
263/// Get user API key and created_at (for settings).
264pub const USER_SETTINGS_FIELDS: &str = "\
265SELECT api_key, created_at FROM users WHERE id = ?1";
266
267/// Get user email and avatar (for settings).
268pub const USER_EMAIL_AVATAR: &str = "\
269SELECT email, \
270(SELECT avatar_url FROM oauth_identities WHERE user_id = ?1 LIMIT 1) \
271FROM users WHERE id = ?1";
272
273// ─── OAuth Identities ────────────────────────────────────────────────────────
274
275/// Schema for the generic OAuth identities table (used by migration).
276pub const OAUTH_IDENTITIES_SCHEMA: &str = "\
277CREATE TABLE IF NOT EXISTS oauth_identities (
278    user_id           TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
279    provider          TEXT NOT NULL,
280    provider_user_id  TEXT NOT NULL,
281    provider_username TEXT,
282    avatar_url        TEXT,
283    instance_url      TEXT,
284    created_at        TEXT NOT NULL DEFAULT (datetime('now')),
285    PRIMARY KEY (provider, provider_user_id),
286    UNIQUE (user_id, provider)
287);
288";
289
290/// Schema for OAuth state tokens (CSRF protection).
291pub const OAUTH_STATES_SCHEMA: &str = "\
292CREATE TABLE IF NOT EXISTS oauth_states (
293    state      TEXT PRIMARY KEY,
294    provider   TEXT NOT NULL,
295    created_at TEXT NOT NULL DEFAULT (datetime('now')),
296    expires_at TEXT NOT NULL,
297    user_id    TEXT
298);
299";
300
301/// Schema for refresh tokens.
302pub const REFRESH_TOKENS_SCHEMA: &str = "\
303CREATE TABLE IF NOT EXISTS refresh_tokens (
304    id         TEXT PRIMARY KEY,
305    user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
306    token_hash TEXT NOT NULL UNIQUE,
307    expires_at TEXT NOT NULL,
308    created_at TEXT NOT NULL DEFAULT (datetime('now'))
309);
310";
311
312/// UPSERT an OAuth identity (link or update).
313pub const OAUTH_IDENTITY_UPSERT: &str = "\
314INSERT INTO oauth_identities (user_id, provider, provider_user_id, provider_username, avatar_url, instance_url) \
315VALUES (?1, ?2, ?3, ?4, ?5, ?6) \
316ON CONFLICT (provider, provider_user_id) DO UPDATE SET \
317    provider_username = excluded.provider_username, \
318    avatar_url = excluded.avatar_url";
319
320/// Find a user by OAuth identity.
321pub const OAUTH_IDENTITY_FIND_BY_PROVIDER: &str = "\
322SELECT user_id, provider, provider_user_id, provider_username, avatar_url, instance_url \
323FROM oauth_identities WHERE provider = ?1 AND provider_user_id = ?2";
324
325/// Find all OAuth identities for a user.
326pub const OAUTH_IDENTITY_FIND_BY_USER: &str = "\
327SELECT user_id, provider, provider_user_id, provider_username, avatar_url, instance_url \
328FROM oauth_identities WHERE user_id = ?1";
329
330/// Insert an OAuth state token.
331pub const OAUTH_STATE_INSERT: &str = "\
332INSERT INTO oauth_states (state, provider, expires_at, user_id) VALUES (?1, ?2, ?3, ?4)";
333
334/// Validate and retrieve an OAuth state token.
335pub const OAUTH_STATE_VALIDATE: &str = "\
336SELECT state, provider, expires_at, user_id FROM oauth_states WHERE state = ?1";
337
338/// Delete a used OAuth state token.
339pub const OAUTH_STATE_DELETE: &str = "\
340DELETE FROM oauth_states WHERE state = ?1";
341
342// ─── Team helpers ───────────────────────────────────────────────────────────
343
344/// Check if a user is a member of a team.
345pub const TEAM_MEMBER_EXISTS: &str = "\
346SELECT COUNT(*) FROM team_members WHERE team_id = ?1 AND user_id = ?2";
347
348/// Get a team member's role.
349pub const TEAM_MEMBER_ROLE: &str = "\
350SELECT role FROM team_members WHERE team_id = ?1 AND user_id = ?2";
351
352/// Check if a team exists.
353pub const TEAM_EXISTS: &str = "\
354SELECT COUNT(*) FROM teams WHERE id = ?1";
355
356/// Count members in a team.
357pub const TEAM_MEMBER_COUNT: &str = "\
358SELECT COUNT(*) FROM team_members WHERE team_id = ?1";
359
360/// Find a user by nickname (returns id).
361pub const USER_BY_NICKNAME: &str = "\
362SELECT id FROM users WHERE nickname = ?1";
363
364/// Get a team's created_at timestamp.
365pub const TEAM_CREATED_AT: &str = "\
366SELECT created_at FROM teams WHERE id = ?1";
367
368/// Get a team member's joined_at timestamp.
369pub const MEMBER_JOINED_AT: &str = "\
370SELECT joined_at FROM team_members WHERE team_id = ?1 AND user_id = ?2";
371
372/// Get a team's name by id.
373pub const TEAM_NAME_BY_ID: &str = "\
374SELECT name FROM teams WHERE id = ?1";
375
376/// Update a team's name.
377pub const TEAM_UPDATE_NAME: &str = "\
378UPDATE teams SET name = ?1 WHERE id = ?2";
379
380/// Update a team's description.
381pub const TEAM_UPDATE_DESCRIPTION: &str = "\
382UPDATE teams SET description = ?1 WHERE id = ?2";
383
384/// Update a team's visibility.
385pub const TEAM_UPDATE_VISIBILITY: &str = "\
386UPDATE teams SET is_public = ?1 WHERE id = ?2";
387
388// ─── Invitations ─────────────────────────────────────────────────────────────
389
390/// Schema for team invitations (provider-agnostic).
391pub const INVITATIONS_SCHEMA: &str = "\
392CREATE TABLE IF NOT EXISTS team_invitations (
393    id                      TEXT PRIMARY KEY,
394    team_id                 TEXT NOT NULL REFERENCES teams(id),
395    email                   TEXT,
396    oauth_provider          TEXT,
397    oauth_provider_username TEXT,
398    invited_by              TEXT NOT NULL REFERENCES users(id),
399    role                    TEXT NOT NULL DEFAULT 'member',
400    status                  TEXT NOT NULL DEFAULT 'pending',
401    created_at              TEXT NOT NULL DEFAULT (datetime('now')),
402    expires_at              TEXT NOT NULL
403);
404";
405
406/// Column list for invitation SELECT queries.
407pub const INVITATION_COLUMNS: &str = "\
408i.id, i.team_id, t.name AS team_name, i.email, \
409i.oauth_provider, i.oauth_provider_username, \
410u.nickname AS invited_by_nickname, i.role, i.status, i.created_at, i.expires_at";
411
412/// INSERT a new invitation (8 params).
413pub const INVITATION_INSERT: &str = "\
414INSERT INTO team_invitations \
415(id, team_id, email, oauth_provider, oauth_provider_username, invited_by, role, expires_at) \
416VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)";
417
418/// List pending, non-expired invitations for a user by email or OAuth identity.
419/// Params: ?1 = email, ?2 = user_id (for OAuth identity lookup).
420pub static INVITATION_LIST_MY: LazyLock<String> = LazyLock::new(|| {
421    format!(
422        "SELECT {INVITATION_COLUMNS} \
423         FROM team_invitations i \
424         INNER JOIN teams t ON t.id = i.team_id \
425         INNER JOIN users u ON u.id = i.invited_by \
426         WHERE i.status = 'pending' \
427         AND i.expires_at > datetime('now') \
428         AND ((i.email IS NOT NULL AND i.email = ?1) \
429              OR (i.oauth_provider IS NOT NULL AND i.oauth_provider_username IS NOT NULL \
430                  AND EXISTS (SELECT 1 FROM oauth_identities oi \
431                              WHERE oi.user_id = ?2 \
432                              AND oi.provider = i.oauth_provider \
433                              AND lower(oi.provider_username) = lower(i.oauth_provider_username)))) \
434         ORDER BY i.created_at DESC"
435    )
436});
437
438/// Lookup an invitation by id.
439pub const INVITATION_LOOKUP: &str = "\
440SELECT id, team_id, email, oauth_provider, oauth_provider_username, role, status, expires_at \
441FROM team_invitations WHERE id = ?1";
442
443/// Update an invitation's status.
444pub const INVITATION_UPDATE_STATUS: &str = "\
445UPDATE team_invitations SET status = ?1 WHERE id = ?2";
446
447/// Check for duplicate pending invitation by email.
448pub const INVITATION_DUP_CHECK_EMAIL: &str = "\
449SELECT COUNT(*) FROM team_invitations \
450WHERE team_id = ?1 AND email = ?2 AND status = 'pending'";
451
452/// Check for duplicate pending invitation by OAuth provider.
453pub const INVITATION_DUP_CHECK_OAUTH: &str = "\
454SELECT COUNT(*) FROM team_invitations \
455WHERE team_id = ?1 AND oauth_provider = ?2 AND oauth_provider_username = ?3 AND status = 'pending'";
456
457/// Check if a user has a matching OAuth identity.
458pub const OAUTH_IDENTITY_MATCH: &str = "\
459SELECT COUNT(*) FROM oauth_identities \
460WHERE user_id = ?1 AND provider = ?2 AND lower(provider_username) = lower(?3)";
opensession_api_types/db.rs

opensession_api_types/
db.rs
