005_sessions.sql (1398B)
1 CREATE TABLE session_groups ( 2 id TEXT PRIMARY KEY, 3 host TEXT NOT NULL, 4 active_session_id TEXT, 5 created_at INTEGER NOT NULL, 6 last_seen_at INTEGER NOT NULL 7 ); 8 9 CREATE INDEX session_groups_host ON session_groups(host); 10 11 CREATE TABLE sessions ( 12 id TEXT PRIMARY KEY, 13 public_id TEXT NOT NULL UNIQUE, 14 group_id TEXT NOT NULL REFERENCES session_groups(id) ON DELETE CASCADE, 15 user_id TEXT NOT NULL, 16 scope_type TEXT NOT NULL CHECK (scope_type IN ('global', 'realm', 'client')), 17 scope_host TEXT NOT NULL, 18 realm_id TEXT REFERENCES realms(id) ON DELETE CASCADE, 19 client_id TEXT, 20 ttl INTEGER NOT NULL, 21 auth_time INTEGER NOT NULL, 22 created_at INTEGER NOT NULL, 23 expires_at INTEGER NOT NULL, 24 last_seen_at INTEGER NOT NULL, 25 user_agent TEXT, 26 ip TEXT, 27 UNIQUE (group_id, user_id) 28 ); 29 30 CREATE INDEX sessions_group ON sessions(group_id); 31 CREATE INDEX sessions_user ON sessions(user_id); 32 CREATE INDEX sessions_expires ON sessions(expires_at); 33 CREATE INDEX sessions_scope ON sessions(scope_type, scope_host); 34 35 CREATE TABLE session_realms ( 36 session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE, 37 realm_id TEXT NOT NULL REFERENCES realms(id) ON DELETE CASCADE, 38 created_at INTEGER NOT NULL, 39 last_seen_at INTEGER NOT NULL, 40 revoked_at INTEGER, 41 revoked_by TEXT, 42 PRIMARY KEY (session_id, realm_id) 43 ); 44 45 CREATE INDEX session_realms_realm ON session_realms(realm_id);