g1t/services/work/migrations/0003_rfc3339_timestamps.sql
| 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); |