Sign in

flagon-io/g1t

public

Where people and agents ship software together. The open-source git platform for the whole job: issues, agents, checks and deploys to the edge.

gitagentscloudflarerustforgemcp

g1t/services/identity/migrations/0018_invites.sql

89 lines3,546 bytesCodeBlame
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.
8CREATE 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);
41CREATE INDEX IF NOT EXISTS invites_inviter ON invites (inviter_id, created_at);
42CREATE INDEX IF NOT EXISTS invites_workspace ON invites (workspace_id, created_at);
43CREATE INDEX IF NOT EXISTS invites_charged_workspace ON invites (charged_workspace_id);
44CREATE INDEX IF NOT EXISTS invites_redeemed_by ON invites (redeemed_by);
45CREATE INDEX IF NOT EXISTS invites_email ON invites (email);
46CREATE 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.
51CREATE 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);
62CREATE 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.
66CREATE 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);
80CREATE 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.
84CREATE TABLE IF NOT EXISTS rate_limits (
85 key TEXT PRIMARY KEY,
86 bucket INTEGER NOT NULL,
87 hits INTEGER NOT NULL
88);
89CREATE INDEX IF NOT EXISTS rate_limits_bucket ON rate_limits (bucket);