Database Schema

Image of Schema

Users

Column Name	Data Type	Details
id	integer	not null, primary key
fullName	string	not null
email	string	not null, unique
profileImage	string
hashedPassword	string	not null
createdAt	timestamp	not null
updatedAt	timestamp	not null

Projects

Column Name	Data Type	Details
id	integer	not null, primary key
title	string	not null
description	string
ownerId	integer	not null, foreign key
color	string	not null, default: 'blue'
icon	string	not null, default: 'fa-project-diagram'
createdAt	timestamp	not null
updatedAt	timestamp	not null

• ownerId references Users table

ProjectMembers

Column Name	Data Type	Details
id	integer	not null, primary key
projectId	integer	not null, foreign key
memberId	integer	not null, foreign key
createdAt	timestamp	not null

• projectId references Projects table
• memberId references Users table

Sections

Column Name	Data Type	Details
id	integer	not null, primary key
projectId	integer	not null, foreign key
boardColumn	integer	not null
title	string	not null

• projectId references Projects table

Tasks

Column Name	Data Type	Details
id	integer	not null, primary key
title	string	not null
description	string
ownerId	integer	not null, foreign key
assigneeId	integer	not null, foreign key
sectionId	integer	not null, foreign key
status	timestamp	not null
priority	timestamp	not null
startDate	date
endDate	date
completed	boolean	not null, default: false
createdAt	timestamp	not null
updatedAt	timestamp	not null

• ownerId references Users table
• assigneeId references Users table
• sectionId references Sections table

Comments

Column Name	Data Type	Details
id	integer	not null, primary key
userId	integer	not null, foreign key
comment	text	not null
createdAt	timestamp	not null
updatedAt	timestamp	not null

• userId references Users table

TaskComments

Column Name	Data Type	Details
id	integer	not null, primary key
taskId	integer	not null, foreign key
commentId	integer	not null, foreign key

• taskId references Tasks table
• commentId references Comments table