| 1 | -- A package's own settings beside its repository's: who has a role on it, |
| 2 | -- whether a linked one takes its repository's roles, which repositories' |
| 3 | -- workflows may use it, and packages and versions deleted but restorable |
| 4 | -- for 30 days (docs: guides/packages). |
| 5 | |
| 6 | -- 1: a linked package takes its repository's roles, and its own grants add |
| 7 | -- to them. 0: only its own grants and the workspace's owners. |
| 8 | ALTER TABLE packages ADD COLUMN inherit_access INTEGER NOT NULL DEFAULT 1; |
| 9 | |
| 10 | -- Set when a package is deleted: gone from the registries and listings, |
| 11 | -- its name kept so nobody else takes it, until it is restored or the purge |
| 12 | -- removes it 30 days on. `deleted_by` is the username that deleted it. |
| 13 | ALTER TABLE packages ADD COLUMN deleted_at TEXT; |
| 14 | ALTER TABLE packages ADD COLUMN deleted_by TEXT; |
| 15 | CREATE INDEX packages_deleted ON packages (deleted_at) WHERE deleted_at IS NOT NULL; |
| 16 | |
| 17 | -- The same for one version. Its files and tags stay until the purge, so a |
| 18 | -- restore brings it back whole; its version string cannot be published |
| 19 | -- again meanwhile. |
| 20 | ALTER TABLE versions ADD COLUMN deleted_at TEXT; |
| 21 | ALTER TABLE versions ADD COLUMN deleted_by TEXT; |
| 22 | CREATE INDEX versions_deleted ON versions (deleted_at) WHERE deleted_at IS NOT NULL; |
| 23 | |
| 24 | -- People and teams with a role on a package: read pulls, write publishes, |
| 25 | -- admin deletes, restores and changes its settings. |
| 26 | CREATE TABLE package_access ( |
| 27 | package_id TEXT NOT NULL REFERENCES packages (id) ON DELETE CASCADE, |
| 28 | -- user or team. |
| 29 | grantee_kind TEXT NOT NULL, |
| 30 | -- usr_… or the team's id. |
| 31 | grantee_id TEXT NOT NULL, |
| 32 | -- The username, or the team's slug, as it was last seen. |
| 33 | grantee_name TEXT NOT NULL, |
| 34 | -- read, write or admin. |
| 35 | role TEXT NOT NULL, |
| 36 | created_by TEXT, |
| 37 | created_at TEXT NOT NULL, |
| 38 | PRIMARY KEY (package_id, grantee_kind, grantee_id) |
| 39 | ); |
| 40 | CREATE INDEX package_access_grantee ON package_access (grantee_kind, grantee_id); |
| 41 | |
| 42 | -- Repositories of the package's workspace whose workflow jobs' tokens may |
| 43 | -- use it, besides the repository it is linked to (which always may write). |
| 44 | CREATE TABLE package_actions_access ( |
| 45 | package_id TEXT NOT NULL REFERENCES packages (id) ON DELETE CASCADE, |
| 46 | repo_id TEXT NOT NULL, |
| 47 | -- Its name in the workspace, kept by repo.renamed. |
| 48 | repo_name TEXT NOT NULL, |
| 49 | -- read or write. |
| 50 | role TEXT NOT NULL, |
| 51 | created_by TEXT, |
| 52 | created_at TEXT NOT NULL, |
| 53 | PRIMARY KEY (package_id, repo_id) |
| 54 | ); |
| 55 | CREATE INDEX package_actions_access_repo ON package_actions_access (repo_id); |