001_init.sql (1891B)
1 CREATE TABLE realms ( 2 id TEXT PRIMARY KEY, 3 slug TEXT NOT NULL UNIQUE, 4 db_file TEXT, 5 owner_user_id TEXT, 6 created_at INTEGER NOT NULL 7 ); 8 9 CREATE TABLE realm_hosts ( 10 host TEXT PRIMARY KEY, 11 realm_id TEXT NOT NULL REFERENCES realms(id) ON DELETE CASCADE, 12 created_at INTEGER NOT NULL 13 ); 14 15 CREATE INDEX realm_hosts_realm ON realm_hosts(realm_id); 16 17 CREATE TABLE realm_email_domains ( 18 domain TEXT PRIMARY KEY, 19 realm_id TEXT NOT NULL REFERENCES realms(id) ON DELETE CASCADE, 20 verified INTEGER NOT NULL DEFAULT 0 CHECK (verified IN (0, 1)), 21 verification_token TEXT, 22 verified_at INTEGER, 23 created_at INTEGER NOT NULL 24 ); 25 26 CREATE INDEX realm_email_domains_realm ON realm_email_domains(realm_id); 27 28 CREATE TABLE users ( 29 id TEXT PRIMARY KEY, 30 username TEXT NOT NULL, 31 username_normalized TEXT NOT NULL UNIQUE, 32 display_name TEXT, 33 status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'disabled')), 34 home_realm_id TEXT REFERENCES realms(id), 35 profile TEXT NOT NULL DEFAULT '{}', 36 last_login_at INTEGER, 37 created_at INTEGER NOT NULL, 38 updated_at INTEGER NOT NULL 39 ); 40 41 CREATE INDEX users_home_realm ON users(home_realm_id); 42 43 CREATE TABLE user_emails ( 44 id TEXT PRIMARY KEY, 45 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, 46 email TEXT NOT NULL, 47 email_normalized TEXT NOT NULL UNIQUE, 48 verified INTEGER NOT NULL DEFAULT 0 CHECK (verified IN (0, 1)), 49 is_primary INTEGER NOT NULL DEFAULT 0 CHECK (is_primary IN (0, 1)), 50 created_at INTEGER NOT NULL, 51 verified_at INTEGER 52 ); 53 54 CREATE INDEX user_emails_user ON user_emails(user_id); 55 56 CREATE TABLE credentials ( 57 id TEXT PRIMARY KEY, 58 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, 59 type TEXT NOT NULL, 60 secret_hash TEXT NOT NULL, 61 created_at INTEGER NOT NULL, 62 updated_at INTEGER NOT NULL, 63 UNIQUE (user_id, type) 64 ); 65 66 CREATE INDEX credentials_user ON credentials(user_id);