CREATE TABLE UserAccount (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
surname VARCHAR(100) NOT NULL,
phone_number VARCHAR(20) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP,
CONSTRAINT chk_user_updated_at
CHECK (updated_at IS NULL OR updated_at >= created_at),
CONSTRAINT chk_email
CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
CONSTRAINT chk_phone
CHECK (phone_number ~ '^\+?[0-9]{7,15}$'),
CONSTRAINT chk_user_created_at
CHECK (created_at <= CURRENT_TIMESTAMP)
);
CREATE TABLE Location (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
city VARCHAR(100) NOT NULL,
latitude DECIMAL(9,6) NOT NULL,
longitude DECIMAL(9,6) NOT NULL,
CONSTRAINT uq_location_cords
UNIQUE (latitude, longitude),
CONSTRAINT chk_latitude
CHECK (latitude BETWEEN -90 AND 90),
CONSTRAINT chk_longitude
CHECK (longitude BETWEEN -180 AND 180)
);
CREATE TABLE Category (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
category_name VARCHAR(100) NOT NULL UNIQUE,
description VARCHAR(500),
parent_category_id INT,
CONSTRAINT fk_category_parent
FOREIGN KEY (parent_category_id)
REFERENCES Category(id)
ON DELETE RESTRICT
);
CREATE TABLE NotificationType (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description VARCHAR(500)
);
CREATE TABLE Badge (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
badge_name VARCHAR(100) NOT NULL,
description VARCHAR(500),
category_id INT NOT NULL,
min_price INT,
max_price INT,
tier_level INT,
CONSTRAINT chk_min_price
CHECK (min_price >= 0),
CONSTRAINT chk_max_price
CHECK (max_price >= 0),
CONSTRAINT chk_price_range
CHECK (min_price <= max_price),
CONSTRAINT chk_tier_range
CHECK (tier_level BETWEEN 1 AND 5),
CONSTRAINT fk_badge_category
FOREIGN KEY (category_id)
REFERENCES Category(id)
ON DELETE CASCADE
);
CREATE TABLE Worker (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id INT NOT NULL,
work_mode VARCHAR(20) NOT NULL,
service_radius_km INT NOT NULL,
location_id INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP,
CONSTRAINT chk_worker_created_at
CHECK (created_at <= CURRENT_TIMESTAMP),
CONSTRAINT chk_worker_updated_at
CHECK (updated_at IS NULL OR updated_at >= created_at),
CONSTRAINT chk_radius
CHECK (service_radius_km > 0),
CONSTRAINT chk_worker_work_mode
CHECK (work_mode IN ('HYBRID', 'ONSITE', 'REMOTE')),
CONSTRAINT fk_worker_user
FOREIGN KEY (user_id)
REFERENCES UserAccount(id)
ON DELETE CASCADE,
CONSTRAINT fk_worker_location
FOREIGN KEY (location_id)
REFERENCES Location(id)
ON DELETE RESTRICT
);
CREATE TABLE Client (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id INT NOT NULL,
CONSTRAINT fk_client_user
FOREIGN KEY (user_id)
REFERENCES UserAccount(id)
ON DELETE CASCADE
);
CREATE TABLE Favourite (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
worker_id INT NOT NULL,
client_id INT NOT NULL,
CONSTRAINT fk_favourite_worker
FOREIGN KEY (worker_id)
REFERENCES Worker(id)
ON DELETE CASCADE,
CONSTRAINT fk_favourite_client
FOREIGN KEY (client_id)
REFERENCES Client(id)
ON DELETE CASCADE,
CONSTRAINT unq_worker_client
UNIQUE (worker_id, client_id)
);
CREATE TABLE WorkerCategory (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
worker_id INT NOT NULL,
category_id INT NOT NULL,
CONSTRAINT unq_worker_category
UNIQUE (worker_id, category_id),
CONSTRAINT fk_worker_category_worker
FOREIGN KEY (worker_id)
REFERENCES Worker(id)
ON DELETE CASCADE,
CONSTRAINT fk_worker_category_category
FOREIGN KEY (category_id)
REFERENCES Category(id)
ON DELETE CASCADE
);
CREATE TABLE WorkerBadge (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
badge_id INT NOT NULL,
worker_id INT NOT NULL,
date_acquired DATE,
is_active BOOLEAN,
CONSTRAINT unq_worker_badge
UNIQUE (badge_id, worker_id),
CONSTRAINT fk_worker_badge_badge
FOREIGN KEY (badge_id)
REFERENCES Badge(id)
ON DELETE CASCADE,
CONSTRAINT fk_worker_badge_worker
FOREIGN KEY (worker_id)
REFERENCES Worker(id)
ON DELETE CASCADE
);
CREATE TABLE TaskRequest (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
client_id INT NOT NULL,
description VARCHAR(1000) NOT NULL,
work_mode VARCHAR(20) NOT NULL,
status VARCHAR(20)
CHECK (status IN ('OPEN', 'CLOSED')),
category_id INT NOT NULL,
location_id INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP,
CONSTRAINT chk_task_req_work_mode
CHECK (work_mode IN ('HYBRID', 'ONSITE', 'REMOTE')),
CONSTRAINT chk_task_req_created_at
CHECK (created_at <= CURRENT_TIMESTAMP),
CONSTRAINT chk_task_req_updated_at
CHECK (
updated_at IS NULL OR
(updated_at >= created_at
AND updated_at <= CURRENT_TIMESTAMP)
),
CONSTRAINT fk_task_request_client
FOREIGN KEY (client_id)
REFERENCES Client(id)
ON DELETE CASCADE,
CONSTRAINT fk_task_request_category
FOREIGN KEY (category_id)
REFERENCES Category(id)
ON DELETE RESTRICT,
CONSTRAINT fk_task_request_location
FOREIGN KEY (location_id)
REFERENCES Location(id)
ON DELETE RESTRICT
);
CREATE TABLE Offer (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
worker_id INT NOT NULL,
task_request_id INT NOT NULL,
price INT NOT NULL,
offer_status VARCHAR(20) NOT NULL,
initiated_by VARCHAR(10) NOT NULL
CHECK (initiated_by IN ('CLIENT', 'WORKER')),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP,
CONSTRAINT chk_price
CHECK (price > 0),
CONSTRAINT chk_offer_status
CHECK (offer_status IN ('PENDING', 'ACCEPTED', 'REJECTED')),
CONSTRAINT chk_offer_created_at
CHECK (created_at <= CURRENT_TIMESTAMP),
CONSTRAINT chk_task_off_updated_at
CHECK (
updated_at IS NULL OR
(updated_at >= created_at
AND updated_at <= CURRENT_TIMESTAMP)
),
CONSTRAINT fk_offer_worker
FOREIGN KEY (worker_id)
REFERENCES Worker(id)
ON DELETE CASCADE,
CONSTRAINT fk_offer_task_request
FOREIGN KEY (task_request_id)
REFERENCES TaskRequest(id)
ON DELETE CASCADE
);
CREATE TABLE Task (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
offer_id INT NOT NULL,
status VARCHAR(20) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP,
CONSTRAINT chk_task_status
CHECK (status IN ('ACTIVE', 'COMPLETED', 'CANCELLED')),
CONSTRAINT chk_created_at
CHECK (created_at <= CURRENT_TIMESTAMP),
CONSTRAINT chk_timeline
CHECK (updated_at IS NULL OR updated_at >= created_at),
CONSTRAINT fk_task_offer
FOREIGN KEY (offer_id)
REFERENCES Offer(id)
ON DELETE RESTRICT
);
CREATE TABLE Message (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
text VARCHAR(1000) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
task_id INT NOT NULL,
sender_id INT,
CONSTRAINT chk_sent_at
CHECK (created_at <= CURRENT_TIMESTAMP),
CONSTRAINT fk_message_task
FOREIGN KEY (task_id)
REFERENCES Task(id)
ON DELETE CASCADE,
CONSTRAINT fk_message_sender
FOREIGN KEY (sender_id)
REFERENCES UserAccount(id)
ON DELETE SET NULL
);
CREATE TABLE Review (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
comment TEXT,
rating INT NOT NULL,
task_id INT NOT NULL,
reviewed_id INT NOT NULL,
reviewer_id INT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT chk_rating
CHECK (rating BETWEEN 1 AND 5),
CONSTRAINT chk_created
CHECK (created_at <= CURRENT_TIMESTAMP),
CONSTRAINT fk_review_task
FOREIGN KEY (task_id)
REFERENCES Task(id)
ON DELETE CASCADE,
CONSTRAINT fk_review_reviewed
FOREIGN KEY (reviewed_id)
REFERENCES UserAccount(id)
ON DELETE CASCADE,
CONSTRAINT fk_review_reviewer
FOREIGN KEY (reviewer_id)
REFERENCES UserAccount(id)
ON DELETE SET NULL,
CONSTRAINT unq
UNIQUE (task_id, reviewer_id)
);
CREATE TABLE Complaint (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
reason VARCHAR(255) NOT NULL,
description VARCHAR(1000),
status VARCHAR(20) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP,
task_id INT NOT NULL,
client_id INT NOT NULL,
worker_id INT NOT NULL,
CONSTRAINT chk_complaint_status
CHECK (status IN ('OPEN', 'RESOLVED', 'DISMISSED')),
CONSTRAINT chk_complaint_created_at
CHECK (created_at <= CURRENT_TIMESTAMP),
CONSTRAINT chk_timeline
CHECK (updated_at IS NULL OR updated_at >= created_at),
CONSTRAINT fk_complaint_task
FOREIGN KEY (task_id)
REFERENCES Task(id)
ON DELETE CASCADE,
CONSTRAINT fk_complaint_client
FOREIGN KEY (client_id)
REFERENCES Client(id)
ON DELETE CASCADE,
CONSTRAINT fk_complaint_worker
FOREIGN KEY (worker_id)
REFERENCES Worker(id)
ON DELETE CASCADE
);
CREATE TABLE ComplaintAttachment (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
file_url VARCHAR(500) NOT NULL,
description VARCHAR(500),
complaint_id INT NOT NULL,
user_id INT,
CONSTRAINT fk_complaint_attachment_complaint
FOREIGN KEY (complaint_id)
REFERENCES Complaint(id)
ON DELETE CASCADE,
CONSTRAINT fk_complaint_attachment_user
FOREIGN KEY (user_id)
REFERENCES UserAccount(id)
ON DELETE SET NULL
);
CREATE TABLE Payment (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount INT NOT NULL,
payment_method VARCHAR(50) NOT NULL,
status VARCHAR(20) NOT NULL,
task_id INT,
client_id INT,
worker_id INT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP,
CONSTRAINT chk_amount
CHECK (amount > 0),
CONSTRAINT chk_status
CHECK (status IN ('PENDING', 'PAID', 'FAILED')),
CONSTRAINT chk_payment_method
CHECK (payment_method IN ('CARD','CASH','PAYPAL')),
CONSTRAINT chk_p_created_at
CHECK (created_at <= CURRENT_TIMESTAMP),
CONSTRAINT chk_task_p_updated_at
CHECK (
updated_at IS NULL OR
(updated_at >= created_at
AND updated_at <= CURRENT_TIMESTAMP)
),
CONSTRAINT fk_payment_task
FOREIGN KEY (task_id)
REFERENCES Task(id)
ON DELETE SET NULL,
CONSTRAINT fk_payment_client
FOREIGN KEY (client_id)
REFERENCES Client(id)
ON DELETE SET NULL,
CONSTRAINT fk_payment_worker
FOREIGN KEY (worker_id)
REFERENCES Worker(id)
ON DELETE SET NULL
);
CREATE TABLE NotificationPreference (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
is_enabled BOOLEAN NOT NULL,
channel VARCHAR(10) NOT NULL,
user_id INT NOT NULL,
notification_type_id INT NOT NULL,
CONSTRAINT fk_notification_preference_user
FOREIGN KEY (user_id)
REFERENCES UserAccount(id)
ON DELETE CASCADE,
CONSTRAINT fk_notification_preference_type
FOREIGN KEY (notification_type_id)
REFERENCES NotificationType(id)
ON DELETE CASCADE,
CONSTRAINT chk_channel
CHECK (channel IN ('EMAIL','SMS','PUSH'))
);
CREATE TABLE Notification (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title VARCHAR(255) NOT NULL,
body VARCHAR(1000) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
user_id INT NOT NULL,
notification_type_id INT,
task_id INT,
offer_id INT,
payment_id INT,
CONSTRAINT chk_notification_time
CHECK (created_at <= CURRENT_TIMESTAMP),
CONSTRAINT fk_notification_user
FOREIGN KEY (user_id)
REFERENCES UserAccount(id)
ON DELETE CASCADE,
CONSTRAINT fk_notification_type
FOREIGN KEY (notification_type_id)
REFERENCES NotificationType(id)
ON DELETE SET NULL,
CONSTRAINT fk_notification_task
FOREIGN KEY (task_id)
REFERENCES Task(id)
ON DELETE SET NULL,
CONSTRAINT fk_notification_offer
FOREIGN KEY (offer_id)
REFERENCES Offer(id)
ON DELETE SET NULL,
CONSTRAINT fk_notification_payment
FOREIGN KEY (payment_id)
REFERENCES Payment(id)
ON DELETE SET NULL
);
CREATE TABLE cities (
city VARCHAR(25),
latitude DECIMAL,
longitude DECIMAL
);
