pr_01m47d15m3e54sn21z27rpy5n9/services/identity/migrations/0008_workspaces.sql
| 1 | -- Workspaces own repositories and are the first segment of their URLs. |
| 2 | CREATE TABLE workspaces ( |
| 3 | id TEXT PRIMARY KEY, |
| 4 | slug TEXT NOT NULL UNIQUE, |
| 5 | name TEXT NOT NULL, |
| 6 | created_by TEXT NOT NULL REFERENCES users (id), |
| 7 | created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) |
| 8 | ); |
| 9 | |
| 10 | CREATE TABLE workspace_members ( |
| 11 | workspace_id TEXT NOT NULL REFERENCES workspaces (id) ON DELETE CASCADE, |
| 12 | user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE, |
| 13 | -- 'owner' or 'member' |
| 14 | role TEXT NOT NULL, |
| 15 | created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), |
| 16 | PRIMARY KEY (workspace_id, user_id) |
| 17 | ); |
| 18 | CREATE INDEX workspace_members_user ON workspace_members (user_id); |
| 19 | |
| 20 | -- Repositories created before workspaces existed live under their owner's |
| 21 | -- username, so each existing account gets a workspace of that name. |
| 22 | INSERT INTO workspaces (id, slug, name, created_by) |
| 23 | SELECT 'wsp_' || lower(hex(randomblob(13))), username, username, id FROM users; |
| 24 | |
| 25 | INSERT INTO workspace_members (workspace_id, user_id, role) |
| 26 | SELECT id, created_by, 'owner' FROM workspaces; |