pr_01m47d24b0e6n91zwymwxg0vpx/services/identity/migrations/0010_workspace_tokens.sql
| 1 | -- Access tokens can belong to a workspace instead of a person, so automation |
| 2 | -- needs no service account. A token has exactly one owner. A workspace's |
| 3 | -- token records who made it, and outlives that person's membership and |
| 4 | -- account. |
| 5 | CREATE TABLE access_tokens_new ( |
| 6 | id TEXT PRIMARY KEY, |
| 7 | user_id TEXT REFERENCES users (id) ON DELETE CASCADE, |
| 8 | workspace_id TEXT REFERENCES workspaces (id) ON DELETE CASCADE, |
| 9 | created_by TEXT REFERENCES users (id) ON DELETE SET NULL, |
| 10 | name TEXT NOT NULL, |
| 11 | token_hash TEXT NOT NULL UNIQUE, |
| 12 | created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), |
| 13 | -- Null means the token does not expire. |
| 14 | expires_at TEXT, |
| 15 | -- Kept to within a few minutes, so that using a token is not a write. |
| 16 | last_used_at TEXT, |
| 17 | CHECK ((user_id IS NULL) <> (workspace_id IS NULL)) |
| 18 | ); |
| 19 | INSERT INTO access_tokens_new (id, user_id, created_by, name, token_hash, created_at, expires_at) |
| 20 | SELECT id, user_id, user_id, name, token_hash, created_at, expires_at FROM access_tokens; |
| 21 | |
| 22 | DROP TABLE access_tokens; |
| 23 | ALTER TABLE access_tokens_new RENAME TO access_tokens; |
| 24 | CREATE INDEX access_tokens_user ON access_tokens (user_id); |
| 25 | CREATE INDEX access_tokens_workspace ON access_tokens (workspace_id); |
| 26 | |
| 27 | ALTER TABLE workspaces ADD COLUMN description TEXT; |