Sign in

flagon-io/g1t

public

g1t/services/identity/migrations/0007_rfc3339_timestamps.sql

84 lines3,160 bytesCodeBlame

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.

RFC 3339 timestamps in identity and repos1-- 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.
4PRAGMA defer_foreign_keys = on;
5
6CREATE 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);
14INSERT INTO users_new (id, username, email, password_hash, email_verified_at, created_at)
15SELECT 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')
18FROM users;
19
20-- id is the SHA-256 of the session token.
21CREATE 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);
26INSERT INTO sessions_new (id, user_id, expires_at)
27SELECT id, user_id, strftime('%Y-%m-%dT%H:%M:%fZ', expires_at, 'unixepoch') FROM sessions;
28
29CREATE 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);
38INSERT INTO access_tokens_new (id, user_id, name, token_hash, created_at, expires_at)
39SELECT 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')
42FROM access_tokens;
43
44CREATE 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);
52INSERT INTO ssh_keys_new (id, user_id, title, public_key, fingerprint, created_at)
53SELECT id, user_id, title, public_key, fingerprint,
54 strftime('%Y-%m-%dT%H:%M:%fZ', created_at, 'unixepoch')
55FROM ssh_keys;
56
57-- One-time links sent by email. id is the SHA-256 of the token in the link.
58CREATE 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);
65INSERT INTO email_tokens_new (id, user_id, kind, expires_at)
66SELECT id, user_id, kind, strftime('%Y-%m-%dT%H:%M:%fZ', expires_at, 'unixepoch')
67FROM email_tokens;
68
69DROP TABLE sessions;
70DROP TABLE access_tokens;
71DROP TABLE ssh_keys;
72DROP TABLE email_tokens;
73DROP TABLE users;
74
75ALTER TABLE users_new RENAME TO users;
76ALTER TABLE sessions_new RENAME TO sessions;
77ALTER TABLE access_tokens_new RENAME TO access_tokens;
78ALTER TABLE ssh_keys_new RENAME TO ssh_keys;
79ALTER TABLE email_tokens_new RENAME TO email_tokens;
80
81CREATE UNIQUE INDEX users_email ON users (email) WHERE email IS NOT NULL;
82CREATE INDEX access_tokens_user ON access_tokens (user_id);
83CREATE INDEX ssh_keys_user ON ssh_keys (user_id);
84CREATE INDEX email_tokens_user ON email_tokens (user_id, kind);