Sign in

pulls/pr_01m47d24b0e6n91zwymwxg0vpx

public

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

88 lines3,396 bytesCodeBlame
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--
5-- The new child tables must reference users_new, not users: dropping the old
6-- users table deletes its rows, and that cascades to anything still pointing
7-- at it. The renames at the end carry the references along.
8PRAGMA defer_foreign_keys = on;
9
10CREATE TABLE users_new (
11 id TEXT PRIMARY KEY,
12 username TEXT NOT NULL UNIQUE,
13 email TEXT,
14 password_hash TEXT NOT NULL,
15 email_verified_at TEXT,
16 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
17);
18INSERT INTO users_new (id, username, email, password_hash, email_verified_at, created_at)
19SELECT id, username, email, password_hash,
20 strftime('%Y-%m-%dT%H:%M:%fZ', email_verified_at, 'unixepoch'),
21 strftime('%Y-%m-%dT%H:%M:%fZ', created_at, 'unixepoch')
22FROM users;
23
24-- id is the SHA-256 of the session token.
25CREATE TABLE sessions_new (
26 id TEXT PRIMARY KEY,
27 user_id TEXT NOT NULL REFERENCES users_new (id) ON DELETE CASCADE,
28 expires_at TEXT NOT NULL
29);
30INSERT INTO sessions_new (id, user_id, expires_at)
31SELECT id, user_id, strftime('%Y-%m-%dT%H:%M:%fZ', expires_at, 'unixepoch') FROM sessions;
32
33CREATE TABLE access_tokens_new (
34 id TEXT PRIMARY KEY,
35 user_id TEXT NOT NULL REFERENCES users_new (id) ON DELETE CASCADE,
36 name TEXT NOT NULL,
37 token_hash TEXT NOT NULL UNIQUE,
38 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
39 -- Null means the token does not expire.
40 expires_at TEXT
41);
42INSERT INTO access_tokens_new (id, user_id, name, token_hash, created_at, expires_at)
43SELECT id, user_id, name, token_hash,
44 strftime('%Y-%m-%dT%H:%M:%fZ', created_at, 'unixepoch'),
45 strftime('%Y-%m-%dT%H:%M:%fZ', expires_at, 'unixepoch')
46FROM access_tokens;
47
48CREATE TABLE ssh_keys_new (
49 id TEXT PRIMARY KEY,
50 user_id TEXT NOT NULL REFERENCES users_new (id) ON DELETE CASCADE,
51 title TEXT NOT NULL,
52 public_key TEXT NOT NULL,
53 fingerprint TEXT NOT NULL UNIQUE,
54 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
55);
56INSERT INTO ssh_keys_new (id, user_id, title, public_key, fingerprint, created_at)
57SELECT id, user_id, title, public_key, fingerprint,
58 strftime('%Y-%m-%dT%H:%M:%fZ', created_at, 'unixepoch')
59FROM ssh_keys;
60
61-- One-time links sent by email. id is the SHA-256 of the token in the link.
62CREATE TABLE email_tokens_new (
63 id TEXT PRIMARY KEY,
64 user_id TEXT NOT NULL REFERENCES users_new (id) ON DELETE CASCADE,
65 -- 'verify' or 'reset'
66 kind TEXT NOT NULL,
67 expires_at TEXT NOT NULL
68);
69INSERT INTO email_tokens_new (id, user_id, kind, expires_at)
70SELECT id, user_id, kind, strftime('%Y-%m-%dT%H:%M:%fZ', expires_at, 'unixepoch')
71FROM email_tokens;
72
73DROP TABLE sessions;
74DROP TABLE access_tokens;
75DROP TABLE ssh_keys;
76DROP TABLE email_tokens;
77DROP TABLE users;
78
79ALTER TABLE users_new RENAME TO users;
80ALTER TABLE sessions_new RENAME TO sessions;
81ALTER TABLE access_tokens_new RENAME TO access_tokens;
82ALTER TABLE ssh_keys_new RENAME TO ssh_keys;
83ALTER TABLE email_tokens_new RENAME TO email_tokens;
84
85CREATE UNIQUE INDEX users_email ON users (email) WHERE email IS NOT NULL;
86CREATE INDEX access_tokens_user ON access_tokens (user_id);
87CREATE INDEX ssh_keys_user ON ssh_keys (user_id);
88CREATE INDEX email_tokens_user ON email_tokens (user_id, kind);