g1t/services/work/migrations/0003_rfc3339_timestamps.sql
Pick any line to see why it is the way it is: the commit, the pull request and issue it came from, and what the agent was thinking.
| Work service in Rust, with RFC 3339 timestamps | 1 | -- Every timestamp becomes RFC 3339 UTC text instead of Unix milliseconds. |
| 2 | -- SQLite cannot change a column's type, so each table is rebuilt. | |
| 3 | -- | |
| 4 | -- The new tables reference each other's new names; the renames at the end | |
| 5 | -- carry those references along. | |
| 6 | PRAGMA defer_foreign_keys = on; | |
| 7 | ||
| 8 | CREATE TABLE intents_new ( | |
| 9 | id TEXT PRIMARY KEY, | |
| 10 | repo_id TEXT NOT NULL, | |
| 11 | number INTEGER NOT NULL, | |
| 12 | title TEXT NOT NULL, | |
| 13 | brief TEXT NOT NULL, | |
| 14 | -- JSON array of commands. | |
| 15 | checks TEXT NOT NULL DEFAULT '[]', | |
| 16 | status TEXT NOT NULL DEFAULT 'open', | |
| 17 | author_id TEXT NOT NULL, | |
| 18 | author_name TEXT NOT NULL, | |
| 19 | created_at TEXT NOT NULL, | |
| 20 | UNIQUE (repo_id, number) | |
| 21 | ); | |
| 22 | INSERT INTO intents_new | |
| 23 | SELECT id, repo_id, number, title, brief, checks, status, author_id, author_name, | |
| 24 | strftime('%Y-%m-%dT%H:%M:%fZ', created_at / 1000.0, 'unixepoch') | |
| 25 | FROM intents; | |
| 26 | ||
| 27 | CREATE TABLE attempts_new ( | |
| 28 | id TEXT PRIMARY KEY, | |
| 29 | intent_id TEXT NOT NULL REFERENCES intents_new (id), | |
| 30 | repo_id TEXT NOT NULL, | |
| 31 | number INTEGER NOT NULL, | |
| 32 | agent TEXT NOT NULL, | |
| 33 | runtime TEXT NOT NULL, | |
| 34 | status TEXT NOT NULL DEFAULT 'working', | |
| 35 | summary TEXT, | |
| 36 | fork_repo_id TEXT NOT NULL UNIQUE, | |
| 37 | fork_namespace TEXT NOT NULL, | |
| 38 | fork_name TEXT NOT NULL, | |
| 39 | head_commit TEXT, | |
| 40 | -- What the branch pointed to before a shipped attempt landed. | |
| 41 | landed_base TEXT, | |
| 42 | started_by_id TEXT NOT NULL, | |
| 43 | started_by_name TEXT NOT NULL, | |
| 44 | created_at TEXT NOT NULL, | |
| 45 | updated_at TEXT NOT NULL, | |
| 46 | UNIQUE (intent_id, number) | |
| 47 | ); | |
| 48 | INSERT INTO attempts_new | |
| 49 | SELECT id, intent_id, repo_id, number, agent, runtime, status, summary, | |
| 50 | fork_repo_id, fork_namespace, fork_name, head_commit, landed_base, | |
| 51 | started_by_id, started_by_name, | |
| 52 | strftime('%Y-%m-%dT%H:%M:%fZ', created_at / 1000.0, 'unixepoch'), | |
| 53 | strftime('%Y-%m-%dT%H:%M:%fZ', updated_at / 1000.0, 'unixepoch') | |
| 54 | FROM attempts; | |
| 55 | ||
| 56 | CREATE TABLE session_entries_new ( | |
| 57 | attempt_id TEXT NOT NULL REFERENCES attempts_new (id), | |
| 58 | seq INTEGER NOT NULL, | |
| 59 | kind TEXT NOT NULL, | |
| 60 | text TEXT NOT NULL, | |
| 61 | tool TEXT, | |
| 62 | -- The fork's head when the entry was recorded: links reasoning to code. | |
| 63 | "commit" TEXT, | |
| 64 | at TEXT NOT NULL, | |
| 65 | PRIMARY KEY (attempt_id, seq) | |
| 66 | ); | |
| 67 | INSERT INTO session_entries_new | |
| 68 | SELECT attempt_id, seq, kind, text, tool, "commit", | |
| 69 | strftime('%Y-%m-%dT%H:%M:%fZ', at / 1000.0, 'unixepoch') | |
| 70 | FROM session_entries; | |
| 71 | ||
| 72 | DROP TABLE session_entries; | |
| 73 | DROP TABLE attempts; | |
| 74 | DROP TABLE intents; | |
| 75 | ||
| 76 | ALTER TABLE intents_new RENAME TO intents; | |
| 77 | ALTER TABLE attempts_new RENAME TO attempts; | |
| 78 | ALTER TABLE session_entries_new RENAME TO session_entries; | |
| 79 | ||
| 80 | CREATE INDEX attempts_starter ON attempts (started_by_id, status); |