1ALTER TABLE audit_log ADD COLUMN address TEXT
 2    CHECK (
 3        address IS NULL
 4        OR (length(address) BETWEEN 2 AND 45 AND address NOT GLOB '*[^0-9a-f.:]*')
 5    );
 6
 7CREATE INDEX audit_log_address_idx ON audit_log(address, occurred_at DESC, id DESC);
 8
 9CREATE TABLE reset_links (
10    user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
11    token_hash BLOB NOT NULL UNIQUE CHECK (length(token_hash) = 32),
12    created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
13    created_at INTEGER NOT NULL,
14    expires_at INTEGER NOT NULL,
15    CHECK (expires_at > created_at)
16) STRICT;
17
18ALTER TABLE invitations ADD COLUMN label TEXT NOT NULL DEFAULT ''
19    CHECK (length(CAST(label AS BLOB)) <= 128);
20ALTER TABLE invitations ADD COLUMN batch_id TEXT
21    CHECK (batch_id IS NULL OR length(batch_id) = 36);
22
23CREATE INDEX invitations_batch_id_idx ON invitations(batch_id) WHERE batch_id IS NOT NULL;
24
25CREATE TABLE invitation_groups (
26    invitation_id TEXT NOT NULL REFERENCES invitations(id) ON DELETE CASCADE,
27    group_id TEXT REFERENCES groups(id) ON DELETE SET NULL,
28    group_name TEXT NOT NULL
29        CHECK (
30            length(group_name) BETWEEN 1 AND 32
31            AND group_name = lower(group_name)
32            AND substr(group_name, 1, 1) GLOB '[a-z0-9]'
33            AND group_name NOT GLOB '*[^a-z0-9_-]*'
34        ),
35    PRIMARY KEY (invitation_id, group_name)
36) STRICT;
37
38CREATE INDEX invitation_groups_group_id_idx ON invitation_groups(group_id);
39
40CREATE TABLE pending_confirmations (
41    user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
42    code_hash BLOB NOT NULL CHECK (length(code_hash) = 32),
43    issued_at INTEGER NOT NULL,
44    expires_at INTEGER NOT NULL,
45    wrong_codes INTEGER NOT NULL DEFAULT 0 CHECK (wrong_codes >= 0),
46    CHECK (expires_at > issued_at)
47) STRICT;
48
49CREATE TRIGGER pending_confirmations_need_disabled
50BEFORE INSERT ON pending_confirmations
51WHEN (SELECT disabled FROM users WHERE id = NEW.user_id) IS NOT 1
52BEGIN
53    SELECT RAISE(ABORT, 'a pending account must be disabled');
54END;
55
56CREATE TRIGGER users_pending_stays_disabled
57BEFORE UPDATE OF disabled ON users
58WHEN NEW.disabled = 0 AND EXISTS (SELECT 1 FROM pending_confirmations WHERE user_id = NEW.id)
59BEGIN
60    SELECT RAISE(ABORT, 'a pending account is activated only by its confirmation');
61END;