g1t/services/identity/migrations/0017_github.sql
Pick any line to see why it is the way it is: the commit, the pull request and issue it came from, and what the agent was thinking.
| Invite-only launch: sign in with GitHub, repository access and lifecycle, many emails, a new look | 1 | -- Signing in with GitHub, through g1t's GitHub App. Every timestamp is |
| 2 | -- RFC 3339 UTC. See src/github.rs. | |
| 3 | ||
| 4 | -- A person's linked GitHub account, one each way. A GitHub account is | |
| 5 | -- known by its numeric id: its login can change, and is kept for showing. | |
| 6 | CREATE TABLE github_accounts ( | |
| 7 | user_id TEXT PRIMARY KEY REFERENCES users (id) ON DELETE CASCADE, | |
| 8 | github_id INTEGER NOT NULL UNIQUE, | |
| 9 | login TEXT NOT NULL, | |
| 10 | -- The app's user access and refresh tokens, as JSON sealed with | |
| 11 | -- AES-256-GCM under IDENTITY_KEY and bound to the account. Null when g1t | |
| 12 | -- holds none: never stored, refresh failed, or the person revoked it. | |
| 13 | -- Opaque text of any length. | |
| 14 | tokens TEXT, | |
| 15 | created_at TEXT NOT NULL, | |
| 16 | updated_at TEXT NOT NULL | |
| 17 | ); | |
| 18 | ||
| 19 | -- A trip to GitHub under way: usable once, for ten minutes. | |
| 20 | CREATE TABLE github_states ( | |
| 21 | -- SHA-256 of the state, which the browser also holds in a cookie. | |
| 22 | id TEXT PRIMARY KEY, | |
| 23 | -- The PKCE code verifier; GitHub was sent its S256 challenge. | |
| 24 | verifier TEXT NOT NULL, | |
| 25 | -- sign_in or link. | |
| 26 | purpose TEXT NOT NULL, | |
| 27 | -- The account that asked to link. | |
| 28 | user_id TEXT, | |
| 29 | -- Exactly the redirect_uri GitHub was sent, sent again with the code. | |
| 30 | redirect_uri TEXT NOT NULL, | |
| 31 | next TEXT NOT NULL, | |
| 32 | -- An invite code for a new account, while g1t is invite-only. | |
| 33 | invite_code TEXT, | |
| 34 | expires_at TEXT NOT NULL | |
| 35 | ); | |
| 36 | ||
| 37 | -- A GitHub sign-in waiting on the person: to pick a username (kind | |
| 38 | -- username), or to sign in to the account that has its email (kind link). | |
| 39 | -- For thirty minutes. | |
| 40 | CREATE TABLE github_pending ( | |
| 41 | -- SHA-256 of the token the browser holds in a cookie. | |
| 42 | id TEXT PRIMARY KEY, | |
| 43 | github_id INTEGER NOT NULL, | |
| 44 | login TEXT NOT NULL, | |
| 45 | -- Its verified primary email. | |
| 46 | email TEXT NOT NULL, | |
| 47 | kind TEXT NOT NULL, | |
| 48 | suggestion TEXT, | |
| 49 | -- Sealed as in github_accounts, bound to this row's id. | |
| 50 | tokens TEXT, | |
| 51 | next TEXT NOT NULL, | |
| 52 | invite_code TEXT, | |
| 53 | expires_at TEXT NOT NULL | |
| 54 | ); |