schema.sql

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE USERS (
	user_id UUID DEFAULT gen_random_uuid() NOT NULL,
	firstname VARCHAR(100) NOT NULL,
	lastname VARCHAR(100) NOT NULL,
	email VARCHAR(255) NOT NULL CONSTRAINT UK_USERS_EMAIL UNIQUE,
	password VARCHAR(255) NOT NULL,
	created_at TIMESTAMPTZ NOT NULL,
	updated_at TIMESTAMPTZ,
	PRIMARY KEY (user_id)
);

CREATE TABLE ROLES (
	role_id INT GENERATED ALWAYS AS IDENTITY,
	label VARCHAR(50) NOT NULL,
	PRIMARY KEY (role_id)
);

CREATE TABLE BOARDS (
	board_id INT GENERATED ALWAYS AS IDENTITY,
	label VARCHAR(50) NOT NULL,
	created_at TIMESTAMPTZ NOT NULL,
	PRIMARY KEY (board_id)
);

CREATE TABLE USER_BOARDS (
	user_id UUID NOT NULL,
	board_id INT NOT NULL,
	role_id INT NOT NULL,
	PRIMARY KEY (user_id, board_id),
	FOREIGN KEY (user_id) REFERENCES USERS(user_id) ON DELETE CASCADE,
	FOREIGN KEY (board_id) REFERENCES BOARDS(board_id) ON DELETE CASCADE,
	FOREIGN KEY (role_id) REFERENCES ROLES(role_id)
);

CREATE TABLE WEBHOOKS (
	webhook_id INT GENERATED ALWAYS AS IDENTITY,
	webhook_type VARCHAR(50) NOT NULL,
	link VARCHAR(255) NOT NULL,
	is_active BOOLEAN NOT NULL DEFAULT TRUE,
	board_id INT NOT NULL,
	PRIMARY KEY (webhook_id),
	FOREIGN KEY (board_id) REFERENCES BOARDS(board_id) ON DELETE CASCADE
);

CREATE TABLE COLUMNS (
	column_id INT GENERATED ALWAYS AS IDENTITY,
	label VARCHAR(50) NOT NULL,
	position INT NOT NULL,
	board_id INT NOT NULL,
	PRIMARY KEY (column_id),
	FOREIGN KEY (board_id) REFERENCES BOARDS(board_id) ON DELETE CASCADE
);

CREATE TABLE CARDS (
	card_id INT GENERATED ALWAYS AS IDENTITY,
	label VARCHAR(50) NOT NULL,
	position INT NOT NULL,
	is_archived BOOLEAN NOT NULL DEFAULT FALSE,
	created_at TIMESTAMPTZ NOT NULL,
	updated_at TIMESTAMPTZ,
	start_date TIMESTAMPTZ,
	end_date TIMESTAMPTZ,
	column_id INT NOT NULL,
	board_id INT NOT NULL,
	PRIMARY KEY (card_id),
	FOREIGN KEY (column_id) REFERENCES COLUMNS(column_id) ON DELETE CASCADE,
	FOREIGN KEY (board_id) REFERENCES BOARDS(board_id)
);

CREATE TABLE TAGS (
	tag_id INT GENERATED ALWAYS AS IDENTITY,
	label VARCHAR(50) NOT NULL,
	board_id INT NOT NULL,
	PRIMARY KEY (tag_id),
	FOREIGN KEY (board_id) REFERENCES BOARDS(board_id) ON DELETE CASCADE
);

CREATE TABLE CARD_TAGS (
	tag_id INT NOT NULL,
	card_id INT NOT NULL,
	PRIMARY KEY (tag_id, card_id),
	FOREIGN KEY (tag_id) REFERENCES TAGS(tag_id) ON DELETE CASCADE,
	FOREIGN KEY (card_id) REFERENCES CARDS(card_id) ON DELETE CASCADE
);

CREATE TABLE CARD_HISTORIES (
	history_id INT GENERATED ALWAYS AS IDENTITY,
	action_type VARCHAR(50) NOT NULL,
	created_at TIMESTAMPTZ NOT NULL,
	card_id INT NOT NULL,
	user_id UUID NOT NULL,
	PRIMARY KEY (history_id),
	FOREIGN KEY (card_id) REFERENCES CARDS(card_id) ON DELETE CASCADE,
	FOREIGN KEY (user_id) REFERENCES USERS(user_id) ON DELETE CASCADE
);

CREATE TABLE USER_CARDS (
	user_id UUID NOT NULL,
	card_id INT NOT NULL,
	PRIMARY KEY(user_id, card_id),
	FOREIGN KEY (card_id) REFERENCES CARDS(card_id) ON DELETE CASCADE,
	FOREIGN KEY (user_id) REFERENCES USERS(user_id) ON DELETE CASCADE
);