001_init.sql (2686B)
1 CREATE TABLE realm_settings ( 2 id INTEGER PRIMARY KEY CHECK (id = 1), 3 slug TEXT NOT NULL UNIQUE, 4 display_name TEXT NOT NULL, 5 kind TEXT NOT NULL DEFAULT 'realm' CHECK (kind IN ('realm', 'system')), 6 status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'disabled')), 7 branding TEXT NOT NULL DEFAULT '{}', 8 allow_session_duration_choice INTEGER NOT NULL DEFAULT 1 CHECK (allow_session_duration_choice IN (0, 1)), 9 created_at INTEGER NOT NULL, 10 updated_at INTEGER NOT NULL 11 ); 12 13 CREATE TABLE realm_memberships ( 14 user_id TEXT PRIMARY KEY, 15 status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'invited')), 16 created_at INTEGER NOT NULL, 17 updated_at INTEGER NOT NULL 18 ); 19 20 CREATE TABLE roles ( 21 id TEXT PRIMARY KEY, 22 slug TEXT NOT NULL UNIQUE, 23 name TEXT NOT NULL, 24 created_at INTEGER NOT NULL 25 ); 26 27 CREATE TABLE membership_roles ( 28 user_id TEXT NOT NULL, 29 role_id TEXT NOT NULL REFERENCES roles(id) ON DELETE CASCADE, 30 PRIMARY KEY (user_id, role_id) 31 ); 32 33 CREATE INDEX membership_roles_role ON membership_roles(role_id); 34 35 CREATE TABLE identity_providers ( 36 id TEXT PRIMARY KEY, 37 slug TEXT NOT NULL UNIQUE, 38 display_name TEXT NOT NULL, 39 issuer TEXT NOT NULL, 40 client_id TEXT NOT NULL, 41 client_secret TEXT, 42 scopes TEXT NOT NULL DEFAULT 'openid email profile', 43 enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0, 1)), 44 created_at INTEGER NOT NULL, 45 updated_at INTEGER NOT NULL 46 ); 47 48 CREATE TABLE email_domain_providers ( 49 domain TEXT PRIMARY KEY, 50 provider_id TEXT NOT NULL REFERENCES identity_providers(id) ON DELETE CASCADE, 51 created_at INTEGER NOT NULL 52 ); 53 54 CREATE INDEX email_domain_providers_provider ON email_domain_providers(provider_id); 55 56 CREATE TABLE oidc_clients ( 57 id TEXT PRIMARY KEY, 58 client_id TEXT NOT NULL UNIQUE, 59 name TEXT NOT NULL, 60 redirect_uris TEXT NOT NULL DEFAULT '[]', 61 scopes TEXT NOT NULL DEFAULT 'openid profile email', 62 access_mode TEXT NOT NULL DEFAULT 'members' CHECK (access_mode IN ('internal', 'members', 'public')), 63 enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0, 1)), 64 created_at INTEGER NOT NULL, 65 updated_at INTEGER NOT NULL 66 ); 67 68 CREATE TABLE oidc_client_roles ( 69 client_id TEXT NOT NULL REFERENCES oidc_clients(id) ON DELETE CASCADE, 70 role_id TEXT NOT NULL REFERENCES roles(id) ON DELETE CASCADE, 71 PRIMARY KEY (client_id, role_id) 72 ); 73 74 CREATE TABLE sessions ( 75 id TEXT PRIMARY KEY, 76 user_id TEXT NOT NULL, 77 ttl INTEGER NOT NULL, 78 created_at INTEGER NOT NULL, 79 expires_at INTEGER NOT NULL, 80 last_seen_at INTEGER NOT NULL, 81 user_agent TEXT, 82 ip TEXT 83 ); 84 85 CREATE INDEX sessions_user ON sessions(user_id); 86 CREATE INDEX sessions_expires ON sessions(expires_at);