| 1 | -- Early access requests (lib/early-access.ts): what people send through /early-access while the |
| 2 | -- instance is invite-only. A request is unconfirmed until its email link is followed, then waits as |
| 3 | -- confirmed until a site administrator invites or declines it. Only site administrators can read |
| 4 | -- them; the audit log keeps every decision. Unconfirmed requests are deleted after 7 days, and anyone |
| 5 | -- can delete their own request through an emailed link (/early-access/remove). |
| 6 | CREATE TABLE IF NOT EXISTS early_access ( |
| 7 | id TEXT PRIMARY KEY, |
| 8 | email TEXT NOT NULL COLLATE NOCASE UNIQUE, |
| 9 | name TEXT, |
| 10 | company TEXT, |
| 11 | use_case TEXT, |
| 12 | hosting TEXT, -- where they host code today, one of a fixed list |
| 13 | state TEXT NOT NULL DEFAULT 'unconfirmed' CHECK (state IN ('unconfirmed', 'confirmed', 'invited', 'declined')), |
| 14 | confirm_hash TEXT, -- SHA-256 of the emailed confirmation token, until it is used |
| 15 | confirm_expires_at TEXT, |
| 16 | confirmed_at TEXT, |
| 17 | remove_hash TEXT, -- SHA-256 of an emailed removal token, when the address asked to be removed |
| 18 | remove_expires_at TEXT, |
| 19 | invite_id TEXT, -- the site invitation sent from it |
| 20 | decided_by TEXT, -- the site administrator's login |
| 21 | decided_at TEXT, |
| 22 | created_at TEXT NOT NULL, |
| 23 | updated_at TEXT NOT NULL |
| 24 | ); |
| 25 | CREATE INDEX IF NOT EXISTS early_access_state ON early_access(state, created_at); |
| 26 | CREATE UNIQUE INDEX IF NOT EXISTS early_access_confirm ON early_access(confirm_hash); |
| 27 | CREATE UNIQUE INDEX IF NOT EXISTS early_access_remove ON early_access(remove_hash); |
| 28 | |
| 29 | -- One row per form submission, for the daily limits only: hashes of the sender's IP address and of |
| 30 | -- the address typed, never either in the clear. Rows older than a day are deleted. |
| 31 | CREATE TABLE IF NOT EXISTS early_access_attempts ( |
| 32 | id TEXT PRIMARY KEY, |
| 33 | ip_hash TEXT NOT NULL, |
| 34 | email_hash TEXT NOT NULL, |
| 35 | emailed INTEGER NOT NULL DEFAULT 0, -- 1 when this submission sent an email |
| 36 | created_at TEXT NOT NULL |
| 37 | ); |
| 38 | CREATE INDEX IF NOT EXISTS early_access_attempts_ip ON early_access_attempts(ip_hash, created_at); |
| 39 | CREATE INDEX IF NOT EXISTS early_access_attempts_email ON early_access_attempts(email_hash, created_at); |
| 40 | CREATE INDEX IF NOT EXISTS early_access_attempts_at ON early_access_attempts(created_at); |