Buckler/buckler.sql

CREATE TABLE IF NOT EXISTS user_info (
	id SERIAL PRIMARY KEY,
	username TEXT NOT NULL,
	email TEXT NOT NULL,
	password_hash TEXT,
	date_created TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	active BOOLEAN NOT NULL DEFAULT FALSE,
	admin BOOLEAN NOT NULL DEFAULT FALSE,
	passwordless BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS user_session (
	user_id INTEGER references user_info(id) ON DELETE CASCADE,
	id TEXT PRIMARY KEY,
	ip_address INET NOT NULL,
	date_created TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	expires TIMESTAMP WITH TIME ZONE DEFAULT NOW() + INTERVAL '30 DAYS',
	last_used TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS invite (
	email TEXT PRIMARY KEY,
	token TEXT NOT NULL,
	date_created TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
	expires TIMESTAMP WITH TIME ZONE DEFAULT NOW() + INTERVAL '24 HOURS'
);

CREATE TABLE IF NOT EXISTS email_confirmation (
	user_id INTEGER PRIMARY KEY references user_info(id) ON DELETE CASCADE,
	token TEXT NOT NULL,
	date_created TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS app_info (
	id SERIAL PRIMARY KEY,
	name TEXT NOT NULL,
	url TEXT NOT NULL,
	key_hash TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS app_user (
	user_id INTEGER references user_info(id) ON DELETE CASCADE,
	app_id INTEGER references app_info(id) ON DELETE CASCADE,
	session_data JSON DEFAULT '{}',
	PRIMARY KEY (user_id, app_id)
);

CREATE TABLE IF NOT EXISTS user_credential (
	id SERIAL PRIMARY KEY,
	user_id INTEGER references user_info(id) ON DELETE CASCADE,
	nick TEXT NOT NULL,
	credential BYTEA NOT NULL
);