| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- Personal access tokens. Authenticate API calls and git-over-HTTPS |
| 4 | -- basic-auth (username = shithub username; password = the raw token). |
| 5 | -- |
| 6 | -- token_hash is sha256 of the raw token (32 bytes b62) — token inputs |
| 7 | -- are uniform-random, so unsalted sha256 has no rainbow-table surface. |
| 8 | -- token_prefix is the first ~12 chars of the raw token (incl. the |
| 9 | -- shithub_pat_ marker), stored ONLY for display in the listing page so |
| 10 | -- users can identify which token is which. |
| 11 | -- |
| 12 | -- scopes is a Postgres text[] with values from a fixed enum at the app |
| 13 | -- layer; we don't enforce that at the DB to keep migrations cheap. |
| 14 | -- |
| 15 | -- expires_at NULL means non-expiring (UI nudges users toward 90 days). |
| 16 | -- revoked_at flips the row to denied without losing the audit trail. |
| 17 | |
| 18 | -- +goose Up |
| 19 | CREATE TABLE user_tokens ( |
| 20 | id bigserial PRIMARY KEY, |
| 21 | user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 22 | name text NOT NULL, |
| 23 | token_hash bytea NOT NULL UNIQUE, |
| 24 | token_prefix text NOT NULL, |
| 25 | scopes text[] NOT NULL DEFAULT ARRAY[]::text[], |
| 26 | expires_at timestamptz, |
| 27 | last_used_at timestamptz, |
| 28 | last_used_ip inet, |
| 29 | revoked_at timestamptz, |
| 30 | created_at timestamptz NOT NULL DEFAULT now(), |
| 31 | |
| 32 | CONSTRAINT user_tokens_name_length CHECK (char_length(name) BETWEEN 1 AND 80), |
| 33 | CONSTRAINT user_tokens_hash_size CHECK (octet_length(token_hash) = 32) |
| 34 | ); |
| 35 | |
| 36 | CREATE INDEX user_tokens_user_id_idx ON user_tokens (user_id, created_at DESC); |
| 37 | CREATE INDEX user_tokens_revoked_at_idx ON user_tokens (revoked_at) WHERE revoked_at IS NOT NULL; |
| 38 | |
| 39 | -- +goose Down |
| 40 | DROP TABLE IF EXISTS user_tokens; |
| 41 |