2.0.2_twofa_v2.sql

main 41 lines · 1.6 KB Raw
Alex Alex Commit Initial commit 01/10/2026 09:20
1-- ---------------------------------------------------------------------------
2-- ZnoteX 2.0.2 - Website 2FA v2
3--
4-- Independent of the game engine: nothing here touches `accounts`, so it works
5-- the same on TFS, Canary, otHire or BlackTek. The old TFS-tied system
6-- (accounts.secret, znote_accounts.secret) is untouched and keeps working.
7-- ---------------------------------------------------------------------------
8
9CREATE TABLE IF NOT EXISTS `znote_2fa` (
10 `account_id` int NOT NULL,
11 `totp_secret` varchar(64) DEFAULT NULL,
12 `totp_enabled` tinyint(1) NOT NULL DEFAULT '0',
13 `email_otp_enabled` tinyint(1) NOT NULL DEFAULT '0',
14 `recovery_codes` text,
15 `session_version` int NOT NULL DEFAULT '1',
16 `updated_at` int NOT NULL DEFAULT '0',
17 PRIMARY KEY (`account_id`)
18) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
19
20CREATE TABLE IF NOT EXISTS `znote_2fa_email_codes` (
21 `account_id` int NOT NULL,
22 `code_hash` varchar(64) NOT NULL,
23 `expires_at` int NOT NULL,
24 `attempts` int NOT NULL DEFAULT '0',
25 PRIMARY KEY (`account_id`)
26) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
27
28CREATE TABLE IF NOT EXISTS `znote_2fa_trusted_devices` (
29 `id` int NOT NULL AUTO_INCREMENT,
30 `account_id` int NOT NULL,
31 `token_hash` varchar(64) NOT NULL,
32 `label` varchar(255) NOT NULL DEFAULT '',
33 `ip` varchar(45) NOT NULL DEFAULT '',
34 `created_at` int NOT NULL,
35 `expires_at` int NOT NULL,
36 `last_used_at` int NOT NULL DEFAULT '0',
37 PRIMARY KEY (`id`),
38 UNIQUE KEY `token_hash` (`token_hash`),
39 KEY `account_id` (`account_id`)
40) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
41
Top