pulls/pr_01m47d24b0e6n91zwymwxg0vpx

public

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

88 lines3,396 bytesCode Blame

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 repos4d ago	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.
Work service in Rust, with RFC 3339 timestamps4d ago	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.
RFC 3339 timestamps in identity and repos4d ago	8	PRAGMA defer_foreign_keys = on;
	9
	10	CREATE 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	);
	18	INSERT INTO users_new (id, username, email, password_hash, email_verified_at, created_at)
	19	SELECT 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')
	22	FROM users;
	23
	24	-- id is the SHA-256 of the session token.
	25	CREATE TABLE sessions_new (
	26	id TEXT PRIMARY KEY,
Work service in Rust, with RFC 3339 timestamps4d ago	27	user_id TEXT NOT NULL REFERENCES users_new (id) ON DELETE CASCADE,
RFC 3339 timestamps in identity and repos4d ago	28	expires_at TEXT NOT NULL
	29	);
	30	INSERT INTO sessions_new (id, user_id, expires_at)
	31	SELECT id, user_id, strftime('%Y-%m-%dT%H:%M:%fZ', expires_at, 'unixepoch') FROM sessions;
	32
	33	CREATE TABLE access_tokens_new (
	34	id TEXT PRIMARY KEY,
Work service in Rust, with RFC 3339 timestamps4d ago	35	user_id TEXT NOT NULL REFERENCES users_new (id) ON DELETE CASCADE,
RFC 3339 timestamps in identity and repos4d ago	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	);
	42	INSERT INTO access_tokens_new (id, user_id, name, token_hash, created_at, expires_at)
	43	SELECT 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')
	46	FROM access_tokens;
	47
	48	CREATE TABLE ssh_keys_new (
	49	id TEXT PRIMARY KEY,
Work service in Rust, with RFC 3339 timestamps4d ago	50	user_id TEXT NOT NULL REFERENCES users_new (id) ON DELETE CASCADE,
RFC 3339 timestamps in identity and repos4d ago	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	);
	56	INSERT INTO ssh_keys_new (id, user_id, title, public_key, fingerprint, created_at)
	57	SELECT id, user_id, title, public_key, fingerprint,
	58	strftime('%Y-%m-%dT%H:%M:%fZ', created_at, 'unixepoch')
	59	FROM ssh_keys;
	60
	61	-- One-time links sent by email. id is the SHA-256 of the token in the link.
	62	CREATE TABLE email_tokens_new (
	63	id TEXT PRIMARY KEY,
Work service in Rust, with RFC 3339 timestamps4d ago	64	user_id TEXT NOT NULL REFERENCES users_new (id) ON DELETE CASCADE,
RFC 3339 timestamps in identity and repos4d ago	65	-- 'verify' or 'reset'
	66	kind TEXT NOT NULL,
	67	expires_at TEXT NOT NULL
	68	);
	69	INSERT INTO email_tokens_new (id, user_id, kind, expires_at)
	70	SELECT id, user_id, kind, strftime('%Y-%m-%dT%H:%M:%fZ', expires_at, 'unixepoch')
	71	FROM email_tokens;
	72
	73	DROP TABLE sessions;
	74	DROP TABLE access_tokens;
	75	DROP TABLE ssh_keys;
	76	DROP TABLE email_tokens;
	77	DROP TABLE users;
	78
	79	ALTER TABLE users_new RENAME TO users;
	80	ALTER TABLE sessions_new RENAME TO sessions;
	81	ALTER TABLE access_tokens_new RENAME TO access_tokens;
	82	ALTER TABLE ssh_keys_new RENAME TO ssh_keys;
	83	ALTER TABLE email_tokens_new RENAME TO email_tokens;
	84
	85	CREATE UNIQUE INDEX users_email ON users (email) WHERE email IS NOT NULL;
	86	CREATE INDEX access_tokens_user ON access_tokens (user_id);
	87	CREATE INDEX ssh_keys_user ON ssh_keys (user_id);
	88	CREATE INDEX email_tokens_user ON email_tokens (user_id, kind);