| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- S41a workflow secrets — per-repo or per-org encrypted secrets the |
| 4 | -- workflow engine surfaces to runners as env vars on the job container. |
| 5 | -- |
| 6 | -- Encryption uses internal/auth/secretbox (ChaCha20Poly1305, AEAD) |
| 7 | -- with the master key from cfg.Auth.TOTPKeyB64 — same trust domain as |
| 8 | -- webhook secrets (S33). The (ciphertext, nonce) pair lives here; |
| 9 | -- plaintext is never stored. |
| 10 | -- |
| 11 | -- Owner is XOR: exactly one of repo_id / org_id is non-NULL. The |
| 12 | -- check constraint enforces; partial unique indexes give scope-local |
| 13 | -- name uniqueness without a separate composite key. |
| 14 | -- |
| 15 | -- Names are case-insensitive (citext) so `MY_SECRET` and `my_secret` |
| 16 | -- collide. Mirrors GHA semantics where secrets are uppercased by |
| 17 | -- convention but the lookup is case-insensitive. |
| 18 | -- |
| 19 | -- Wired by S41c: CRUD handlers under settings/secrets/actions, plus |
| 20 | -- the runner-API surface that resolves secret bindings into the |
| 21 | -- per-job env on dispatch. |
| 22 | |
| 23 | -- +goose Up |
| 24 | |
| 25 | CREATE TABLE workflow_secrets ( |
| 26 | id bigserial PRIMARY KEY, |
| 27 | repo_id bigint REFERENCES repos(id) ON DELETE CASCADE, |
| 28 | org_id bigint REFERENCES orgs(id) ON DELETE CASCADE, |
| 29 | name citext NOT NULL, |
| 30 | ciphertext bytea NOT NULL, |
| 31 | nonce bytea NOT NULL, |
| 32 | created_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL, |
| 33 | created_at timestamptz NOT NULL DEFAULT now(), |
| 34 | updated_at timestamptz NOT NULL DEFAULT now(), |
| 35 | |
| 36 | CONSTRAINT workflow_secrets_owner_xor CHECK ( |
| 37 | (repo_id IS NOT NULL AND org_id IS NULL) OR |
| 38 | (repo_id IS NULL AND org_id IS NOT NULL) |
| 39 | ), |
| 40 | CONSTRAINT workflow_secrets_name_length CHECK (char_length(name::text) BETWEEN 1 AND 100), |
| 41 | CONSTRAINT workflow_secrets_name_format CHECK (name::text ~ '^[A-Za-z_][A-Za-z0-9_]*$'), |
| 42 | CONSTRAINT workflow_secrets_nonce_length CHECK (octet_length(nonce) = 12), |
| 43 | CONSTRAINT workflow_secrets_ciphertext_nonempty CHECK (octet_length(ciphertext) > 0) |
| 44 | ); |
| 45 | |
| 46 | CREATE UNIQUE INDEX workflow_secrets_repo_name_idx |
| 47 | ON workflow_secrets (repo_id, name) WHERE repo_id IS NOT NULL; |
| 48 | CREATE UNIQUE INDEX workflow_secrets_org_name_idx |
| 49 | ON workflow_secrets (org_id, name) WHERE org_id IS NOT NULL; |
| 50 | |
| 51 | CREATE TRIGGER set_updated_at BEFORE UPDATE ON workflow_secrets |
| 52 | FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at(); |
| 53 | |
| 54 | |
| 55 | -- +goose Down |
| 56 | DROP TABLE IF EXISTS workflow_secrets; |
| 57 |