flabk/migrate/1662887862_schema.up.sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
	id CHAR(22) NOT NULL PRIMARY KEY,
	username TEXT NOT NULL,
	host TEXT,
	display_name TEXT,
	email TEXT NOT NULL,
	password_hash TEXT NOT NULL,
	avatar_uri TEXT,
	bio TEXT,
);

CREATE UNIQUE INDEX u_username_host ON users (username, host);
CREATE UNIQUE INDEX u_username_local ON users (username) WHERE host IS NULL;

	--id UUID NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(),
CREATE TABLE keys (
	key TEXT NOT NULL PRIMARY KEY,
	value TEXT NOT NULL
);


CREATE TABLE follows (
	user_id CHAR(22) NOT NULL REFERENCES users(id),
	follows_id CHAR(22) NOT NULL REFERENCES users(id),
	created_at TIMESTAMP NOT NULL DEFAULT now(),

	PRIMARY KEY (user_id, follows_id)
);

CREATE TABLE follow_requests(
	user_id CHAR(22) NOT NULL REFERENCES users(id),
	follows_id CHAR(22) NOT NULL REFERENCES users(id),
	created_at TIMESTAMP NOT NULL DEFAULT now(),

	PRIMARY KEY (user_id, follows_id)
);