Project tracker

PostgreSQL ER diagram · 4 tables · 5 declared foreign keys

—

users Table

Column	SQL type	Keys
id	bigint NOT NULL · PK	PK
email	varchar(255) NOT NULL	UQ

Primary key: id

project_members Table

Column	SQL type	Keys
project_id	bigint NOT NULL	PK¹FK
user_id	bigint NOT NULL	PK¹FK
role	varchar(20) NOT NULL

¹ Composite PK: (project_id, user_id)
The pair is unique; neither column is individually unique.

tasks Table

Column	SQL type	Keys
id	bigint NOT NULL · PK	PK
project_id	bigint NOT NULL	FK
assignee_id	bigint NULL allowed	FK
title	varchar(200) NOT NULL
external_ticket_id	varchar(80) NULL allowed
reviewer_id	bigint NULL allowed	FK

external_ticket_id identifies an external issue; it is not a local FK.

projects Table

Column	SQL type	Keys
id	bigint NOT NULL · PK	PK
name	varchar(120) NOT NULL

Primary key: id

Declared relationships

project_members.user_id → users.id Each membership has exactly 1 user; each user has 0..* memberships.
project_members.project_id → projects.id Each membership has exactly 1 project; each project has 0..* memberships.
tasks.project_id → projects.id Each task has exactly 1 project; each project has 0..* tasks.
tasks.assignee_id → users.id Each task has 0..1 assignee; each user has 0..* assigned tasks. Unchanged by the migration.
tasks.reviewer_id → users.id Each task has 0..1 reviewer; each user has 0..* tasks to review. Added by the migration.

Reading the diagram

PK Primary key, unique and non-null. FK Declared foreign key. UQ Declared single-column UNIQUE. PK¹ identifies members of one composite primary key.

1 exactly one · 0..1 optional one · 0..* zero or many. A number beside a card counts rows in that table for one row at the other end. Line labels name the referencing FK column.

Required FKs reference exactly one parent. Both assignee_id and reviewer_id allow NULL. No FK column is individually unique, so each parent can have many referencing rows. No constraint requires a parent to have any children.

Migration: Applying add-reviewer.sql after schema.sql appends nullable tasks.reviewer_id bigint, referencing users.id. The tasks card is taller and the two user relationships have separate lines. All existing columns and constraints remain unchanged.

Only declared FKs are drawn. Neither assignee nor reviewer must belong to the task’s project. The same user may fill both roles, and either role may be unset independently. Additional application rules are not established by these SQL files.