g1t/services/identity/migrations/0007_rfc3339_timestamps.sql
| 1 | -- Every timestamp becomes RFC 3339 UTC text (2026-10-02T05:16:19.000Z) |
| 2 | -- instead of Unix seconds. SQLite cannot change a column's type, so each |
| 3 | -- table is rebuilt and its rows converted. |
| 4 | PRAGMA defer_foreign_keys = on; |
| 5 | |
| 6 | CREATE TABLE users_new ( |
| 7 | id TEXT PRIMARY KEY, |
| 8 | username TEXT NOT NULL UNIQUE, |
| 9 | email TEXT, |
| 10 | password_hash TEXT NOT NULL, |
| 11 | email_verified_at TEXT, |
| 12 | created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) |
| 13 | ); |
| 14 | INSERT INTO users_new (id, username, email, password_hash, email_verified_at, created_at) |
| 15 | SELECT id, username, email, password_hash, |
| 16 | strftime('%Y-%m-%dT%H:%M:%fZ', email_verified_at, 'unixepoch'), |
| 17 | strftime('%Y-%m-%dT%H:%M:%fZ', created_at, 'unixepoch') |
| 18 | FROM users; |
| 19 | |
| 20 | -- id is the SHA-256 of the session token. |
| 21 | CREATE TABLE sessions_new ( |
| 22 | id TEXT PRIMARY KEY, |
| 23 | user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE, |
| 24 | expires_at TEXT NOT NULL |
| 25 | ); |
| 26 | INSERT INTO sessions_new (id, user_id, expires_at) |
| 27 | SELECT id, user_id, strftime('%Y-%m-%dT%H:%M:%fZ', expires_at, 'unixepoch') FROM sessions; |
| 28 | |
| 29 | CREATE TABLE access_tokens_new ( |
| 30 | id TEXT PRIMARY KEY, |
| 31 | user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE, |
| 32 | name TEXT NOT NULL, |
| 33 | token_hash TEXT NOT NULL UNIQUE, |
| 34 | created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), |
| 35 | -- Null means the token does not expire. |
| 36 | expires_at TEXT |
| 37 | ); |
| 38 | INSERT INTO access_tokens_new (id, user_id, name, token_hash, created_at, expires_at) |
| 39 | SELECT id, user_id, name, token_hash, |
| 40 | strftime('%Y-%m-%dT%H:%M:%fZ', created_at, 'unixepoch'), |
| 41 | strftime('%Y-%m-%dT%H:%M:%fZ', expires_at, 'unixepoch') |
| 42 | FROM access_tokens; |
| 43 | |
| 44 | CREATE TABLE ssh_keys_new ( |
| 45 | id TEXT PRIMARY KEY, |
| 46 | user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE, |
| 47 | title TEXT NOT NULL, |
| 48 | public_key TEXT NOT NULL, |
| 49 | fingerprint TEXT NOT NULL UNIQUE, |
| 50 | created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) |
| 51 | ); |
| 52 | INSERT INTO ssh_keys_new (id, user_id, title, public_key, fingerprint, created_at) |
| 53 | SELECT id, user_id, title, public_key, fingerprint, |
| 54 | strftime('%Y-%m-%dT%H:%M:%fZ', created_at, 'unixepoch') |
| 55 | FROM ssh_keys; |
| 56 | |
| 57 | -- One-time links sent by email. id is the SHA-256 of the token in the link. |
| 58 | CREATE TABLE email_tokens_new ( |
| 59 | id TEXT PRIMARY KEY, |
| 60 | user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE, |
| 61 | -- 'verify' or 'reset' |
| 62 | kind TEXT NOT NULL, |
| 63 | expires_at TEXT NOT NULL |
| 64 | ); |
| 65 | INSERT INTO email_tokens_new (id, user_id, kind, expires_at) |
| 66 | SELECT id, user_id, kind, strftime('%Y-%m-%dT%H:%M:%fZ', expires_at, 'unixepoch') |
| 67 | FROM email_tokens; |
| 68 | |
| 69 | DROP TABLE sessions; |
| 70 | DROP TABLE access_tokens; |
| 71 | DROP TABLE ssh_keys; |
| 72 | DROP TABLE email_tokens; |
| 73 | DROP TABLE users; |
| 74 | |
| 75 | ALTER TABLE users_new RENAME TO users; |
| 76 | ALTER TABLE sessions_new RENAME TO sessions; |
| 77 | ALTER TABLE access_tokens_new RENAME TO access_tokens; |
| 78 | ALTER TABLE ssh_keys_new RENAME TO ssh_keys; |
| 79 | ALTER TABLE email_tokens_new RENAME TO email_tokens; |
| 80 | |
| 81 | CREATE UNIQUE INDEX users_email ON users (email) WHERE email IS NOT NULL; |
| 82 | CREATE INDEX access_tokens_user ON access_tokens (user_id); |
| 83 | CREATE INDEX ssh_keys_user ON ssh_keys (user_id); |
| 84 | CREATE INDEX email_tokens_user ON email_tokens (user_id, kind); |