| 1 | -- Coordinating work (src/coordination/): sessions of people's own harnesses, |
| 2 | -- advisory claims on issues, and which pairs of pull requests have been |
| 3 | -- announced as overlapping. |
| 4 | |
| 5 | -- One run of someone's own harness (a Claude Code session, a Codex run) on |
| 6 | -- a repository, by a person's token. `state` is stored as active or ended; |
| 7 | -- idle (15 minutes without a heartbeat) is worked out when read, and two |
| 8 | -- hours without one ends it as `timed_out`. |
| 9 | CREATE TABLE sessions ( |
| 10 | id TEXT PRIMARY KEY, |
| 11 | repo_id TEXT NOT NULL, |
| 12 | -- The issue and pull request it works on, by number, if it said. |
| 13 | issue INTEGER, |
| 14 | pull INTEGER, |
| 15 | branch TEXT, |
| 16 | actor_id TEXT NOT NULL, |
| 17 | actor_name TEXT NOT NULL, |
| 18 | -- claude_code, codex, cursor, copilot or other. |
| 19 | harness TEXT NOT NULL, |
| 20 | -- The harness's own id for it: starting it again returns this row. |
| 21 | harness_session_id TEXT, |
| 22 | title TEXT, |
| 23 | state TEXT NOT NULL DEFAULT 'active', |
| 24 | head_commit TEXT, |
| 25 | started_at TEXT NOT NULL, |
| 26 | last_seen_at TEXT NOT NULL, |
| 27 | ended_at TEXT, |
| 28 | end_reason TEXT |
| 29 | ); |
| 30 | CREATE UNIQUE INDEX sessions_by_harness_id ON sessions (repo_id, actor_id, harness, harness_session_id) |
| 31 | WHERE harness_session_id IS NOT NULL; |
| 32 | CREATE INDEX sessions_by_repo ON sessions (repo_id, state, last_seen_at); |
| 33 | CREATE INDEX sessions_by_issue ON sessions (repo_id, issue) WHERE issue IS NOT NULL; |
| 34 | CREATE INDEX sessions_by_pull ON sessions (repo_id, pull) WHERE pull IS NOT NULL; |
| 35 | |
| 36 | -- A session's entries live beside the ones recorded on a pull request, so |
| 37 | -- `pull_id` may now be empty: an entry belongs to a pull request, a |
| 38 | -- session, or both. SQLite cannot loosen NOT NULL in place, so the table |
| 39 | -- is rebuilt with the same columns plus: |
| 40 | -- |
| 41 | -- session_id the session it was recorded in. |
| 42 | -- session_seq its place in that session. |
| 43 | -- key the harness's idempotency key: recording an entry with |
| 44 | -- a key already recorded in the session does nothing. |
| 45 | -- |
| 46 | -- An entry of a session that is on a pull request also takes the next |
| 47 | -- `seq` of that pull request, so it shows in the pull request's session as |
| 48 | -- the entries recorded on it do. One recorded before the session named |
| 49 | -- its pull request has `pull_id` NULL and `seq` = `session_seq` until then. |
| 50 | CREATE TABLE session_entries_next ( |
| 51 | pull_id TEXT REFERENCES pulls (id), |
| 52 | seq INTEGER NOT NULL, |
| 53 | kind TEXT NOT NULL, |
| 54 | text TEXT NOT NULL, |
| 55 | tool TEXT, |
| 56 | "commit" TEXT, |
| 57 | at TEXT NOT NULL, |
| 58 | session_id TEXT, |
| 59 | session_seq INTEGER, |
| 60 | key TEXT, |
| 61 | CHECK (pull_id IS NOT NULL OR session_id IS NOT NULL) |
| 62 | ); |
| 63 | INSERT INTO session_entries_next (pull_id, seq, kind, text, tool, "commit", at) |
| 64 | SELECT pull_id, seq, kind, text, tool, "commit", at FROM session_entries; |
| 65 | DROP TABLE session_entries; |
| 66 | ALTER TABLE session_entries_next RENAME TO session_entries; |
| 67 | CREATE UNIQUE INDEX session_entries_by_pull ON session_entries (pull_id, seq) WHERE pull_id IS NOT NULL; |
| 68 | CREATE UNIQUE INDEX session_entries_by_session ON session_entries (session_id, session_seq) |
| 69 | WHERE session_id IS NOT NULL; |
| 70 | CREATE UNIQUE INDEX session_entries_by_key ON session_entries (session_id, key) |
| 71 | WHERE session_id IS NOT NULL AND key IS NOT NULL; |
| 72 | |
| 73 | -- An advisory, expiring "I'm working on #N": never a lock. Active until |
| 74 | -- released, handed off, or past `expires_at` (noticed when next read, and |
| 75 | -- then announced as expired). Claiming again, or a heartbeat of the |
| 76 | -- session it was made from, renews it by `ttl_minutes`. |
| 77 | CREATE TABLE claims ( |
| 78 | id TEXT PRIMARY KEY, |
| 79 | repo_id TEXT NOT NULL, |
| 80 | issue INTEGER NOT NULL, |
| 81 | session_id TEXT, |
| 82 | actor_id TEXT NOT NULL, |
| 83 | actor_name TEXT NOT NULL, |
| 84 | harness TEXT, |
| 85 | note TEXT, |
| 86 | -- active, released, expired or handed_off. |
| 87 | state TEXT NOT NULL DEFAULT 'active', |
| 88 | ttl_minutes INTEGER NOT NULL, |
| 89 | created_at TEXT NOT NULL, |
| 90 | expires_at TEXT NOT NULL, |
| 91 | ended_at TEXT, |
| 92 | end_reason TEXT, |
| 93 | -- For a hand-off: the username it is for; NULL for anyone. |
| 94 | handoff_to TEXT |
| 95 | ); |
| 96 | CREATE INDEX claims_by_issue ON claims (repo_id, issue, state); |
| 97 | CREATE INDEX claims_by_state ON claims (repo_id, state, expires_at); |
| 98 | CREATE INDEX claims_by_session ON claims (session_id, state) WHERE session_id IS NOT NULL; |
| 99 | |
| 100 | -- Pairs of pull requests announced as changing the same files |
| 101 | -- (`overlap.detected`), lower number first: announced once, and again only |
| 102 | -- after the pair stopped overlapping and this row went. |
| 103 | CREATE TABLE overlap_pairs ( |
| 104 | repo_id TEXT NOT NULL, |
| 105 | low INTEGER NOT NULL, |
| 106 | high INTEGER NOT NULL, |
| 107 | -- JSON array of the paths both changed when it was announced. |
| 108 | files TEXT NOT NULL, |
| 109 | noticed_at TEXT NOT NULL, |
| 110 | PRIMARY KEY (repo_id, low, high) |
| 111 | ); |