Warning: Due to various recent migrations, viewing non-HEAD refs may be broken.

/schema.sql (raw)

CREATE TABLE User (
	id INTEGER PRIMARY KEY,
	username TEXT NOT NULL UNIQUE,
	name TEXT,
	email TEXT,
	password_hash TEXT,
	admin INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE Client (
	id INTEGER PRIMARY KEY,
	client_id TEXT NOT NULL UNIQUE,
	client_secret_hash BLOB,
	owner INTEGER REFERENCES User(id) ON DELETE CASCADE,
	redirect_uris TEXT,
	client_name TEXT,
	client_uri TEXT,
	pkce_requirement TEXT
);

CREATE TABLE AccessToken (
	id INTEGER PRIMARY KEY,
	hash BLOB NOT NULL UNIQUE,
	user INTEGER NOT NULL REFERENCES User(id) ON DELETE CASCADE,
	client INTEGER REFERENCES Client(id) ON DELETE CASCADE,
	scope TEXT,
	issued_at datetime NOT NULL,
	expires_at datetime NOT NULL,
	auth_time datetime,
	refresh_hash BLOB UNIQUE,
	refresh_expires_at datetime
);

CREATE TABLE AuthCode (
	id INTEGER PRIMARY KEY,
	hash BLOB NOT NULL UNIQUE,
	created_at datetime NOT NULL,
	user INTEGER NOT NULL REFERENCES User(id) ON DELETE CASCADE,
	client INTEGER NOT NULL REFERENCES Client(id) ON DELETE CASCADE,
	redirect_uri TEXT,
	scope TEXT,
	nonce TEXT,
	code_challenge TEXT,
	code_challenge_method TEXT
);

CREATE TABLE SigningKey (
	id INTEGER PRIMARY KEY,
	kid TEXT NOT NULL UNIQUE,
	algorithm TEXT NOT NULL,
	private_key BLOB NOT NULL,
	created_at datetime NOT NULL
);

CREATE INDEX signing_key_created_at ON SigningKey(created_at);