CREATE TABLE IF NOTEXISTS users ( id TEXT PRIMARY KEYNOT NULL, name TEXT, avatar_color TEXT, email TEXT NOT NULLUNIQUE, email_verified BOOLEANNOT NULLDEFAULT0, master_password_hash TEXT NOT NULL, master_password_hint TEXT, password_salt TEXT, -- Salt for server-side PBKDF2 hashing (NULL for legacy users pending migration) key TEXT NOT NULL, -- The encrypted symmetric key private_key TEXT NOT NULL, -- encrypted asymmetric private_key public_key TEXT NOT NULL, -- asymmetric public_key kdf_type INTEGERNOT NULLDEFAULT0, -- 0 for PBKDF2, 1 for Argon2id kdf_iterations INTEGERNOT NULLDEFAULT600000, kdf_memory INTEGER, -- Argon2 memory parameter in MB (15-1024), NULL for PBKDF2 kdf_parallelism INTEGER, -- Argon2 parallelism parameter (1-16), NULL for PBKDF2 security_stamp TEXT, totp_recover TEXT, -- Recovery code for 2FA created_at TEXT NOT NULL, updated_at TEXT NOT NULL );
1 2 3 4 5 6 7 8 9 10 11 12 13 14
CREATE TABLE IF NOTEXISTS ciphers ( id TEXT PRIMARY KEYNOT NULL, user_id TEXT, organization_id TEXT, type INTEGERNOT NULL, data TEXT NOT NULL, -- JSON blob of all encrypted fields (name, notes, login, etc.) favorite BOOLEANNOT NULLDEFAULT0, folder_id TEXT, deleted_at TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ONDELETE CASCADE, FOREIGN KEY (folder_id) REFERENCES folders(id) ONDELETESETNULL );
1 2 3 4 5 6 7 8
CREATE TABLE IF NOTEXISTS folders ( id TEXT PRIMARY KEYNOT NULL, user_id TEXT NOT NULL, name TEXT NOT NULL, -- Encrypted folder name created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ONDELETE CASCADE );
1 2 3 4 5 6 7 8 9 10 11 12
CREATE TABLE IF NOTEXISTS attachments ( id TEXT PRIMARY KEYNOT NULL, cipher_id TEXT NOT NULL, file_name TEXT NOT NULL, file_size INTEGERNOT NULL, akey TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, organization_id TEXT, FOREIGN KEY (cipher_id) REFERENCES ciphers(id) ONDELETE CASCADE ); CREATE INDEX IF NOTEXISTS idx_attachments_cipher ON attachments(cipher_id);
1 2 3 4 5 6 7 8 9 10 11 12 13
CREATE TABLE IF NOTEXISTS attachments_pending ( id TEXT PRIMARY KEYNOT NULL, cipher_id TEXT NOT NULL, file_name TEXT NOT NULL, file_size INTEGERNOT NULL, akey TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, organization_id TEXT, FOREIGN KEY (cipher_id) REFERENCES ciphers(id) ONDELETE CASCADE ); CREATE INDEX IF NOTEXISTS idx_attachments_pending_cipher ON attachments_pending(cipher_id); CREATE INDEX IF NOTEXISTS idx_attachments_pending_created_at ON attachments_pending(created_at);
1 2 3 4 5 6 7 8 9 10
CREATE TABLE IF NOTEXISTS twofactor ( uuid TEXT PRIMARY KEYNOT NULL, user_uuid TEXT NOT NULL, atype INTEGERNOT NULL, enabled INTEGERNOT NULLDEFAULT1, data TEXT NOT NULL, -- JSON data specific to the 2FA type (e.g., TOTP secret) last_used INTEGERNOT NULLDEFAULT0, -- Unix timestamp or TOTP time step FOREIGN KEY (user_uuid) REFERENCES users(id) ONDELETE CASCADE, UNIQUE(user_uuid, atype) );