113 lines
3.8 KiB
SQL
113 lines
3.8 KiB
SQL
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
DROP TABLE IF EXISTS dev_users;
|
|
|
|
DROP TABLE IF EXISTS client_users;
|
|
|
|
DROP TABLE IF EXISTS clients;
|
|
|
|
DROP TABLE IF EXISTS projects;
|
|
|
|
DROP TABLE IF EXISTS client_user_project_joins;
|
|
|
|
DROP TABLE IF EXISTS dev_user_project_joins;
|
|
|
|
DROP TABLE IF EXISTS statuses;
|
|
|
|
DROP TABLE IF EXISTS tasks;
|
|
|
|
DROP TABLE IF EXISTS entry_logs;
|
|
|
|
CREATE TABLE IF NOT EXISTS dev_users (
|
|
id UUID PRIMARY KEY DEFAULT (uuid_generate_v4()),
|
|
first_name character varying(255) NOT NULL,
|
|
last_name character varying(255) NOT NULL,
|
|
email character varying(255) UNIQUE NOT NULL,
|
|
phone_number character varying(255),
|
|
created_at timestamp WITH time zone DEFAULT NOW(),
|
|
updated_at timestamp WITH time zone DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS clients (
|
|
id UUID PRIMARY KEY DEFAULT (uuid_generate_v4()),
|
|
name character varying(255) NOT NULL,
|
|
abbreviation character varying(255),
|
|
website character varying(255),
|
|
phone_number character varying(255),
|
|
created_at timestamp WITH time zone DEFAULT NOW() NOT NULL,
|
|
updated_at timestamp WITH time zone DEFAULT NOW() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS client_users (
|
|
id UUID PRIMARY KEY DEFAULT (uuid_generate_v4()),
|
|
first_name character varying(255) NOT NULL,
|
|
last_name character varying(255) NOT NULL,
|
|
email character varying(255) UNIQUE NOT NULL,
|
|
phone_number character varying(255),
|
|
created_at timestamp WITH time zone DEFAULT NOW(),
|
|
updated_at timestamp WITH time zone DEFAULT NOW(),
|
|
client_id UUID REFERENCES clients(id),
|
|
role_in_company character varying(255) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id UUID PRIMARY KEY DEFAULT (uuid_generate_v4()),
|
|
name character varying(255) entry_logsNOT NULL,
|
|
abbreviation character varying(255),
|
|
description TEXT,
|
|
created_at timestamp WITH time zone DEFAULT NOW(),
|
|
updated_at timestamp WITH time zone DEFAULT NOW(),
|
|
client_id UUID REFERENCES clients(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS client_user_project_joins (
|
|
id UUID PRIMARY KEY DEFAULT (uuid_generate_v4()),
|
|
client_id UUID NOT NULL REFERENCES clients(id),
|
|
project_id UUID NOT NULL REFERENCES projects(id),
|
|
created_at timestamp WITH time zone DEFAULT NOW(),
|
|
updated_at timestamp WITH time zone DEFAULT NOW(),
|
|
is_stakeholder BOOLEAN DEFAULT FALSE,
|
|
notes TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS dev_user_project_joins (
|
|
id UUID PRIMARY KEY DEFAULT (uuid_generate_v4()),
|
|
dev_user_id UUID NOT NULL REFERENCES dev_users(id),
|
|
project_id UUID NOT NULL REFentry_logsERENCES projects(id),
|
|
created_at timestamp WITH time zone DEFAULT NOW(),
|
|
updated_at timestamp WITH time zone DEFAULT NOW(),
|
|
is_lead BOOLEAN DEFAULT FALSE,
|
|
notes TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS statuses (
|
|
id UUID PRIMARY KEY DEFAULT (uuid_generate_v4()),
|
|
name character varying(255) NOT NULL,
|
|
description TEXT,
|
|
created_at timestamp WITH time zone DEFAULT NOW(),
|
|
updated_at timestamp WITH time zone DEFAULT NOW(),
|
|
deleted_at timestamp WITH time zone
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
|
id UUID PRIMARY KEY DEFAULT (uuid_generate_v4()),
|
|
project_id UUID NOT NULL REFERENCES projects(id),
|
|
status_id UUID NOT NULL REFERENCES statuses(id),
|
|
created_at timestamp WITH time zone DEFAULT NOW(),
|
|
updated_at timestamp WITH time zone DEFAULT NOW(),
|
|
expected_delivery_date timestamp WITH time zone,
|
|
final_delivery_date timestamp WITH time zone,
|
|
description TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS entry_logs (
|
|
id UUID PRIMARY KEY DEFAULT (uuid_generate_v4()),
|
|
project_id UUID NOT NULL REFERENCES projects(id),
|
|
task_id UUID NOT NULL REFERENCES tasks(id),
|
|
created_at timestamp WITH time zone DEFAULT NOW(),
|
|
updated_at timestamp WITH time zone DEFAULT NOW(),
|
|
start_time timestamp WITH time zone,
|
|
end_time timestamp WITH time zone,
|
|
description TEXT
|
|
);
|