60 lines
1.6 KiB
SQL
60 lines
1.6 KiB
SQL
/* API Tokens */
|
|
CREATE TABLE IF NOT EXISTS api_token
|
|
( selector TEXT PRIMARY KEY
|
|
, verifier BLOB NOT NULL
|
|
, expiration INTEGER NOT NULL /* TAI64N */
|
|
, description TEXT
|
|
) STRICT;
|
|
|
|
/* General User Management */
|
|
CREATE TABLE IF NOT EXISTS users
|
|
( uuid BLOB PRIMARY KEY
|
|
, username TEXT NOT NULL
|
|
, passhash TEXT NOT NULL
|
|
) STRICT;
|
|
|
|
CREATE TABLE IF NOT EXISTS pass_reset
|
|
( selector TEXT PRIMARY KEY
|
|
, verifier BLOB NOT NULL
|
|
, expiration INTEGER NOT NULL
|
|
, uuid BLOB NOT NULL REFERENCES users ON DELETE CASCADE
|
|
) STRICT;
|
|
|
|
/* OAuth & OIDC */
|
|
CREATE TABLE IF NOT EXISTS oidc_rs256
|
|
( kid TEXT UNIQUE NOT NULL
|
|
, keyblob BLOB NOT NULL
|
|
) STRICT;
|
|
|
|
CREATE TABLE IF NOT EXISTS oidc_hs256
|
|
( kid TEXT UNIQUE NOT NULL
|
|
, keyblob BLOB NOT NULL
|
|
) STRICT;
|
|
|
|
CREATE TABLE IF NOT EXISTS oidc_generation
|
|
( tokentype TEXT NOT NULL
|
|
, not_before INTEGER NOT NULL
|
|
) STRICT;
|
|
|
|
CREATE TABLE IF NOT EXISTS oidc_clients
|
|
( uuid BLOB PRIMARY KEY
|
|
, name TEXT NOT NULL
|
|
, subdomains TEXT NOT NULL
|
|
, kid TEXT NOT NULL
|
|
, is_secret INTEGER NOT NULL
|
|
) STRICT;
|
|
|
|
CREATE TABLE IF NOT EXISTS oidc_session_tokens
|
|
( tokenhash BLOB PRIMARY KEY
|
|
, uuid BLOB REFERENCES users ON DELETE CASCADE,
|
|
, generation INTEGER NOT NULL
|
|
, expiration INTEGER NOT NULL
|
|
) STRICT;
|
|
|
|
CREATE TABLE IF NOT EXISTS oidc_refresh_tokens
|
|
( tokenhash BLOB PRIMARY KEY
|
|
, uuid BLOB REFERENCES users ON DELETE CASCADE
|
|
, generation INTEGER NOT NULL
|
|
, expiration INTEGER NOT NULL
|
|
) STRICT;
|