g1t/services/identity/migrations/0020_access.sql
| 1 | -- Repository roles: each workspace's base permission, roles given on one |
| 2 | -- repository, and invitations to collaborate on one. Every timestamp is |
| 3 | -- RFC 3339 UTC. See src/access.rs and crates/contracts/src/access.rs. |
| 4 | -- Wrangler applies it once; the tables and indexes also say IF NOT EXISTS, |
| 5 | -- so running them again changes nothing (the ALTER, like 0019's, is the |
| 6 | -- one statement that must run once). |
| 7 | |
| 8 | -- What every member gets on each of the workspace's repositories: none, |
| 9 | -- read, write or admin. Owners have admin whatever it says. Write is what |
| 10 | -- members could do before roles. |
| 11 | ALTER TABLE workspaces ADD COLUMN base_permission TEXT NOT NULL DEFAULT 'write'; |
| 12 | |
| 13 | -- A role on one repository, given to a principal directly. Today the |
| 14 | -- principal is always a person (principal_kind 'user'); a team will be |
| 15 | -- another kind, resolved into its people's roles when they sign in. |
| 16 | CREATE TABLE IF NOT EXISTS repo_grants ( |
| 17 | -- The repository, by the repos service's id: renames and transfers |
| 18 | -- never change it. |
| 19 | repo_id TEXT NOT NULL, |
| 20 | -- 'user' (later: 'team'). |
| 21 | principal_kind TEXT NOT NULL, |
| 22 | principal_id TEXT NOT NULL, |
| 23 | -- The workspace the repository is in now, and its name there, kept in |
| 24 | -- step by transfer_repo_scopes, so lists need no call to repos. |
| 25 | workspace_id TEXT NOT NULL, |
| 26 | repo_name TEXT NOT NULL, |
| 27 | -- read, triage, write, maintain or admin. |
| 28 | role TEXT NOT NULL, |
| 29 | -- Who gave it, by user id. Null when it came from an invite code. |
| 30 | granted_by TEXT, |
| 31 | created_at TEXT NOT NULL, |
| 32 | updated_at TEXT NOT NULL, |
| 33 | PRIMARY KEY (repo_id, principal_kind, principal_id) |
| 34 | ); |
| 35 | CREATE INDEX IF NOT EXISTS repo_grants_principal ON repo_grants (principal_kind, principal_id); |
| 36 | CREATE INDEX IF NOT EXISTS repo_grants_workspace ON repo_grants (workspace_id, repo_name); |
| 37 | |
| 38 | -- An invitation to collaborate on one repository: to a person with an |
| 39 | -- account, who accepts or declines it, or to an address without one, by |
| 40 | -- an invite code (invites.invite_id) that makes the account and accepts. |
| 41 | CREATE TABLE IF NOT EXISTS repo_invitations ( |
| 42 | id TEXT PRIMARY KEY, |
| 43 | repo_id TEXT NOT NULL, |
| 44 | workspace_id TEXT NOT NULL, |
| 45 | repo_name TEXT NOT NULL, |
| 46 | -- The person invited, when they have an account. |
| 47 | invitee_id TEXT, |
| 48 | -- The address invited, lowercase, when they had none. |
| 49 | email TEXT, |
| 50 | -- The invite code sent to that address (invites.id). |
| 51 | invite_id TEXT, |
| 52 | role TEXT NOT NULL, |
| 53 | inviter_id TEXT, |
| 54 | created_at TEXT NOT NULL, |
| 55 | expires_at TEXT NOT NULL, |
| 56 | accepted_at TEXT, |
| 57 | declined_at TEXT, |
| 58 | revoked_at TEXT |
| 59 | ); |
| 60 | CREATE INDEX IF NOT EXISTS repo_invitations_repo ON repo_invitations (repo_id, created_at); |
| 61 | CREATE INDEX IF NOT EXISTS repo_invitations_invitee ON repo_invitations (invitee_id, created_at); |
| 62 | CREATE INDEX IF NOT EXISTS repo_invitations_invite ON repo_invitations (invite_id); |
| 63 | CREATE INDEX IF NOT EXISTS repo_invitations_email ON repo_invitations (email); |