| 1 |
-- ZephyrFS Authentication Database Schema |
| 2 |
|
| 3 |
-- Users table with email registration and OAuth support |
| 4 |
CREATE TABLE users ( |
| 5 |
id TEXT PRIMARY KEY, |
| 6 |
email TEXT UNIQUE NOT NULL, |
| 7 |
username TEXT UNIQUE, |
| 8 |
password_hash TEXT, |
| 9 |
user_type TEXT CHECK (user_type IN ('backup', 'volunteer', 'admin')) NOT NULL DEFAULT 'backup', |
| 10 |
email_verified BOOLEAN DEFAULT FALSE, |
| 11 |
email_verification_token TEXT, |
| 12 |
email_verification_expires_at DATETIME, |
| 13 |
password_reset_token TEXT, |
| 14 |
password_reset_expires_at DATETIME, |
| 15 |
github_id TEXT UNIQUE, |
| 16 |
github_username TEXT, |
| 17 |
github_avatar_url TEXT, |
| 18 |
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, |
| 19 |
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, |
| 20 |
last_login_at DATETIME, |
| 21 |
profile_data TEXT -- JSON storage for additional profile info |
| 22 |
); |
| 23 |
|
| 24 |
-- OAuth accounts table for multiple providers |
| 25 |
CREATE TABLE oauth_accounts ( |
| 26 |
id TEXT PRIMARY KEY, |
| 27 |
user_id TEXT NOT NULL, |
| 28 |
provider TEXT NOT NULL, |
| 29 |
provider_account_id TEXT NOT NULL, |
| 30 |
access_token TEXT, |
| 31 |
refresh_token TEXT, |
| 32 |
expires_at DATETIME, |
| 33 |
token_type TEXT, |
| 34 |
scope TEXT, |
| 35 |
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, |
| 36 |
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, |
| 37 |
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, |
| 38 |
UNIQUE(provider, provider_account_id) |
| 39 |
); |
| 40 |
|
| 41 |
-- User sessions table |
| 42 |
CREATE TABLE user_sessions ( |
| 43 |
id TEXT PRIMARY KEY, |
| 44 |
user_id TEXT NOT NULL, |
| 45 |
session_token TEXT UNIQUE NOT NULL, |
| 46 |
expires_at DATETIME NOT NULL, |
| 47 |
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, |
| 48 |
last_access_at DATETIME DEFAULT CURRENT_TIMESTAMP, |
| 49 |
ip_address TEXT, |
| 50 |
user_agent TEXT, |
| 51 |
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE |
| 52 |
); |
| 53 |
|
| 54 |
-- Email verification attempts tracking |
| 55 |
CREATE TABLE email_verifications ( |
| 56 |
id TEXT PRIMARY KEY, |
| 57 |
user_id TEXT NOT NULL, |
| 58 |
email TEXT NOT NULL, |
| 59 |
token TEXT NOT NULL, |
| 60 |
attempts INTEGER DEFAULT 0, |
| 61 |
verified_at DATETIME, |
| 62 |
expires_at DATETIME NOT NULL, |
| 63 |
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, |
| 64 |
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE |
| 65 |
); |
| 66 |
|
| 67 |
-- Password reset attempts tracking |
| 68 |
CREATE TABLE password_resets ( |
| 69 |
id TEXT PRIMARY KEY, |
| 70 |
user_id TEXT NOT NULL, |
| 71 |
token TEXT NOT NULL, |
| 72 |
attempts INTEGER DEFAULT 0, |
| 73 |
used_at DATETIME, |
| 74 |
expires_at DATETIME NOT NULL, |
| 75 |
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, |
| 76 |
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE |
| 77 |
); |
| 78 |
|
| 79 |
-- User onboarding progress tracking |
| 80 |
CREATE TABLE user_onboarding ( |
| 81 |
id TEXT PRIMARY KEY, |
| 82 |
user_id TEXT NOT NULL, |
| 83 |
step TEXT NOT NULL, |
| 84 |
completed BOOLEAN DEFAULT FALSE, |
| 85 |
data TEXT, -- JSON storage for step-specific data |
| 86 |
completed_at DATETIME, |
| 87 |
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, |
| 88 |
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE |
| 89 |
); |
| 90 |
|
| 91 |
-- Indexes for performance |
| 92 |
CREATE INDEX idx_users_email ON users (email); |
| 93 |
CREATE INDEX idx_users_username ON users (username); |
| 94 |
CREATE INDEX idx_users_github_id ON oauth_accounts (provider_account_id); |
| 95 |
CREATE INDEX idx_sessions_token ON user_sessions (session_token); |
| 96 |
CREATE INDEX idx_sessions_user_id ON user_sessions (user_id); |
| 97 |
CREATE INDEX idx_email_verifications_token ON email_verifications (token); |
| 98 |
CREATE INDEX idx_password_resets_token ON password_resets (token); |
| 99 |
|
| 100 |
-- Insert default admin user |
| 101 |
INSERT INTO users ( |
| 102 |
id, |
| 103 |
email, |
| 104 |
username, |
| 105 |
password_hash, |
| 106 |
user_type, |
| 107 |
email_verified |
| 108 |
) VALUES ( |
| 109 |
'admin', |
| 110 |
'admin@zephyrfs.org', |
| 111 |
'admin', |
| 112 |
'$2b$10$rKGHZ0aB0qKhP0DqW8qZHu2wF2nF.FqD3tYw1pV6gB2wZ8vY0gG4u', -- 'admin' hashed |
| 113 |
'admin', |
| 114 |
TRUE |
| 115 |
); |
| 116 |
|
| 117 |
INSERT INTO users ( |
| 118 |
id, |
| 119 |
email, |
| 120 |
username, |
| 121 |
password_hash, |
| 122 |
user_type, |
| 123 |
email_verified |
| 124 |
) VALUES ( |
| 125 |
'demo', |
| 126 |
'demo@zephyrfs.org', |
| 127 |
'demo', |
| 128 |
'$2b$10$rKGHZ0aB0qKhP0DqW8qZHu2wF2nF.FqD3tYw1pV6gB2wZ8vY0gG4u', -- 'demo' hashed |
| 129 |
'backup', |
| 130 |
TRUE |
| 131 |
); |