g1t/services/identity/migrations/0018_invites.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 | -- Invite-only registration: invite codes, the extra invites staff grant, |
| 2 | -- the waitlist, and the counters that rate-limit them. Every timestamp is | |
| 3 | -- RFC 3339 UTC. See src/invites.rs. Safe to apply twice. | |
| 4 | ||
| 5 | -- One invite code. The code itself is never stored: only its SHA-256, to | |
| 6 | -- find it, and a copy sealed under IDENTITY_KEY that the person who made it | |
| 7 | -- can see again while it is pending. | |
| 8 | CREATE TABLE IF NOT EXISTS invites ( | |
| 9 | id TEXT PRIMARY KEY, | |
| 10 | -- SHA-256 of the code's 32 characters, lowercase, without `g1t-` or | |
| 11 | -- hyphens. | |
| 12 | code_hash TEXT NOT NULL UNIQUE, | |
| 13 | -- The code's first group, such as `g1t-k7m2`: enough to recognise it, | |
| 14 | -- far too little to use it. | |
| 15 | hint TEXT NOT NULL, | |
| 16 | -- AES-256-GCM under IDENTITY_KEY, bound to the id. Null when no key is | |
| 17 | -- set, and once the invite is used, revoked or expired. | |
| 18 | sealed_code TEXT, | |
| 19 | -- When set, only an account with this address (lowercase) can use it. | |
| 20 | email TEXT, | |
| 21 | -- account: makes a new account (and joins workspace_id, if set). | |
| 22 | -- workspace: an existing account joins workspace_id; it never makes one. | |
| 23 | kind TEXT NOT NULL, | |
| 24 | -- The workspace using it joins, if any. | |
| 25 | workspace_id TEXT, | |
| 26 | -- The person who made it. Null when staff minted it in sudo. | |
| 27 | inviter_id TEXT, | |
| 28 | -- The staff member who minted or approved it, by email. | |
| 29 | staff TEXT, | |
| 30 | -- Whose allowance it uses: user (inviter_id's), workspace | |
| 31 | -- (charged_workspace_id's, granted by staff), or none. | |
| 32 | charged_to TEXT NOT NULL, | |
| 33 | charged_workspace_id TEXT, | |
| 34 | created_at TEXT NOT NULL, | |
| 35 | expires_at TEXT NOT NULL, | |
| 36 | revoked_at TEXT, | |
| 37 | -- The account that used it: for new accounts, the "invited by" tree. | |
| 38 | redeemed_by TEXT, | |
| 39 | redeemed_at TEXT | |
| 40 | ); | |
| 41 | CREATE INDEX IF NOT EXISTS invites_inviter ON invites (inviter_id, created_at); | |
| 42 | CREATE INDEX IF NOT EXISTS invites_workspace ON invites (workspace_id, created_at); | |
| 43 | CREATE INDEX IF NOT EXISTS invites_charged_workspace ON invites (charged_workspace_id); | |
| 44 | CREATE INDEX IF NOT EXISTS invites_redeemed_by ON invites (redeemed_by); | |
| 45 | CREATE INDEX IF NOT EXISTS invites_email ON invites (email); | |
| 46 | CREATE INDEX IF NOT EXISTS invites_hint ON invites (hint); | |
| 47 | ||
| 48 | -- Invites staff granted beyond the default allowance (INVITES_PER_USER): | |
| 49 | -- to a person, or to a workspace, whose owners share them. A negative | |
| 50 | -- amount takes some back. | |
| 51 | CREATE TABLE IF NOT EXISTS invite_grants ( | |
| 52 | id TEXT PRIMARY KEY, | |
| 53 | -- user or workspace. | |
| 54 | target_kind TEXT NOT NULL, | |
| 55 | target_id TEXT NOT NULL, | |
| 56 | amount INTEGER NOT NULL, | |
| 57 | note TEXT, | |
| 58 | -- The staff member, by email. | |
| 59 | granted_by TEXT NOT NULL, | |
| 60 | created_at TEXT NOT NULL | |
| 61 | ); | |
| 62 | CREATE INDEX IF NOT EXISTS invite_grants_target ON invite_grants (target_kind, target_id); | |
| 63 | ||
| 64 | -- People asking for access while registration is invite-only. One row per | |
| 65 | -- address; asking again updates it. | |
| 66 | CREATE TABLE IF NOT EXISTS waitlist ( | |
| 67 | id TEXT PRIMARY KEY, | |
| 68 | email TEXT NOT NULL UNIQUE, | |
| 69 | -- What they said they will build, if anything. | |
| 70 | about TEXT, | |
| 71 | -- waiting, invited or dismissed. | |
| 72 | status TEXT NOT NULL, | |
| 73 | -- The invite staff sent when approving. | |
| 74 | invite_id TEXT, | |
| 75 | decided_by TEXT, | |
| 76 | decided_at TEXT, | |
| 77 | created_at TEXT NOT NULL, | |
| 78 | updated_at TEXT NOT NULL | |
| 79 | ); | |
| 80 | CREATE INDEX IF NOT EXISTS waitlist_status ON waitlist (status, created_at); | |
| 81 | ||
| 82 | -- Fixed-window counters: `key` such as `invite.create:usr_…`, `bucket` the | |
| 83 | -- window's number since the epoch. | |
| 84 | CREATE TABLE IF NOT EXISTS rate_limits ( | |
| 85 | key TEXT PRIMARY KEY, | |
| 86 | bucket INTEGER NOT NULL, | |
| 87 | hits INTEGER NOT NULL | |
| 88 | ); | |
| 89 | CREATE INDEX IF NOT EXISTS rate_limits_bucket ON rate_limits (bucket); |