| 1 | -- Shared invite links: one link staff hand to a group (a conference's |
| 2 | -- judges, a post, a community) that makes up to max_uses new accounts, |
| 3 | -- until it expires or is revoked. Each use makes a new account, which makes |
| 4 | -- its own workspace; a shared link never joins an existing one and uses |
| 5 | -- nobody's allowance. Every timestamp is RFC 3339 UTC. See |
| 6 | -- src/shared_invites.rs. Safe to apply twice. |
| 7 | |
| 8 | CREATE TABLE IF NOT EXISTS shared_invites ( |
| 9 | id TEXT PRIMARY KEY, |
| 10 | -- Whom it is for, such as `Cloudflare judges`. Not secret: the sign-up |
| 11 | -- page shows it. |
| 12 | label TEXT NOT NULL, |
| 13 | -- As invites.code_hash: SHA-256 of the code's 32 characters. The code |
| 14 | -- itself is never stored. |
| 15 | code_hash TEXT NOT NULL UNIQUE, |
| 16 | -- The code's first group, such as `g1t-k7m2`. |
| 17 | hint TEXT NOT NULL, |
| 18 | -- AES-256-GCM under IDENTITY_KEY, bound to the id, so staff can copy |
| 19 | -- the link again. Null when no key is set, and once it is revoked or |
| 20 | -- every use is taken. |
| 21 | sealed_code TEXT, |
| 22 | max_uses INTEGER NOT NULL, |
| 23 | -- Email domains it is limited to, lowercase, comma separated. Null for |
| 24 | -- any address. |
| 25 | domains TEXT, |
| 26 | -- The staff member who made it, by email. |
| 27 | staff TEXT NOT NULL, |
| 28 | created_at TEXT NOT NULL, |
| 29 | expires_at TEXT NOT NULL, |
| 30 | revoked_at TEXT, |
| 31 | revoked_by TEXT |
| 32 | ); |
| 33 | CREATE INDEX IF NOT EXISTS shared_invites_created ON shared_invites (created_at); |
| 34 | |
| 35 | -- One row per account a shared link made: how many uses are taken (its |
| 36 | -- count, checked in the same statement that adds one), and where the |
| 37 | -- account came from. Kept when the account is purged, so a purge never |
| 38 | -- gives a use back. |
| 39 | CREATE TABLE IF NOT EXISTS shared_invite_uses ( |
| 40 | user_id TEXT PRIMARY KEY, |
| 41 | shared_invite_id TEXT NOT NULL, |
| 42 | created_at TEXT NOT NULL |
| 43 | ); |
| 44 | CREATE INDEX IF NOT EXISTS shared_invite_uses_invite ON shared_invite_uses (shared_invite_id, created_at); |