Database documentation · PostgreSQL

Project tracker schema

An ERD of the constraints declared in schema.sql. Query behavior is documented separately. The optional add-reviewer.sql is not applied.

4 tables 12 columns 4 declared foreign keys 1 composite primary key

PK Primary key; unique and not null FK Declared foreign key UQ Explicit UNIQUE NN NOT NULL NULL Nullable PK 1/2 Position within a composite key

Scroll horizontally and vertically to explore the full diagram on smaller screens. Endpoint counts mean related rows per one row at the opposite end: 1 exactly one, 0..1 optional one, 0..* zero or more.

projects

2 columns

Column	SQL type	Constraints
id	bigint	PK
name	varchar(120)	NN

schema.sql:7–10
Primary key: (id)

project_members

3 columns

Column	SQL type	Constraints
project_id	bigint	PK 1/2 FK NN
user_id	bigint	PK 2/2 FK NN
role	varchar(20)	NN

schema.sql:12–17
Composite PK: (project_id, user_id).
The pair is unique; neither column is individually unique.

tasks

5 columns

Column	SQL type	Constraints
id	bigint	PK
project_id	bigint	FK NN
assignee_id	bigint	FK NULL
title	varchar(200)	NN
external_ticket_id	varchar(80)	NULL

schema.sql:19–25 · Primary key: (id)
External ticket identifier has no local FK.

users

2 columns

Column	SQL type	Constraints
id	bigint	PK
email	varchar(255)	NN UQ

schema.sql:2–5
Primary key: (id) · Unique: (email)

Relationships and evidence

Cardinalities describe states permitted by the database. A non-null foreign key requires one referenced row; a nullable foreign key permits no reference. None of these foreign-key columns is individually unique, so each parent may have zero or many child rows.

Link	Declared foreign key	Parents per child	Children per parent	File-and-line evidence
R1	`project_members.project_id` → `projects.id`	1 project per membership	0..* memberships per project	schema.sql:13 — FK and NOT NULL. schema.sql:8 — target PK. schema.sql:12–17 — only the pair is unique.
R2	`project_members.user_id` → `users.id`	1 user per membership	0..* memberships per user	schema.sql:14 — FK and NOT NULL. schema.sql:3 — target PK. schema.sql:12–17 — only the pair is unique.
R3	`tasks.project_id` → `projects.id`	1 project per task	0..* tasks per project	schema.sql:21 — FK and NOT NULL. schema.sql:8 — target PK. schema.sql:19–25 — no UNIQUE on `project_id`.
R4	`tasks.assignee_id` → `users.id`	0..1 assignee user per task	0..* assigned tasks per user	schema.sql:22 — nullable FK. schema.sql:3 — target PK. schema.sql:19–25 — no UNIQUE on `assignee_id`.

Together, R1 and R2 implement a many-to-many association between projects and users through project_members. Its composite primary key allows at most one membership row for each project/user pair (schema.sql:13–16). This is a two-link association, not an additional direct foreign key.

Database constraints

Single-column primary keys are users.id, projects.id, and tasks.id (schema.sql:3, :8, :20).
project_members has the composite primary key (project_id, user_id) (schema.sql:16).
users.email is explicitly NOT NULL and UNIQUE (schema.sql:4). No other non-PK unique constraint is declared.
Only tasks.assignee_id and tasks.external_ticket_id permit NULL. All other columns are explicitly NOT NULL or part of a primary key (schema.sql:2–25).
The FK declarations specify no referential actions or deferrability options (schema.sql:13–14, :21–22). PostgreSQL defaults therefore apply: ON DELETE NO ACTION, ON UPDATE NO ACTION, and NOT DEFERRABLE.
No CHECK constraints, DEFAULT expressions, identity columns, or generated columns are declared (schema.sql:2–25).

Application checks and query behavior

The query uses LEFT JOIN users on t.assignee_id to retain unassigned tasks (queries.sql:1–5). The nullable FK in schema.sql:22 establishes the optional relationship.
A comment states that application code checks membership before assignment. The example checks whether project 42 contains user 7 (queries.sql:7–10).
There is no FK from tasks to project_members. The database can accept an existing user as an assignee even when that user is not a member of the task’s project (schema.sql:19–25; queries.sql:8).

The membership check is application behavior. It does not create a database relationship or change the ERD’s cardinalities.

Scope and uncertainties

Reviewer is excluded. add-reviewer.sql:2 would add reviewer_id bigint REFERENCES users(id), but that script is not applied. The documented tasks table has five columns.
External ticket is not a local relationship. tasks.external_ticket_id is a nullable, non-unique varchar(80). Its comment explicitly identifies an external issue tracker and says it is not a local FK (schema.sql:24, :27–28). The external system and its validation rules are unspecified.
Membership enforcement coverage is unknown. The supplied SQL shows a membership lookup and comments about application behavior. It does not establish that every assignment path performs the check, or how concurrent membership changes are handled (queries.sql:7–10).
Role meanings are unspecified. role is required and limited to varchar(20); no allowed-value CHECK or role lookup FK is declared (schema.sql:12–17).
Actual row counts are unknown. The schema supplies no application data (schema.sql:1). “Zero or many” describes permitted multiplicity, not observed usage. No relationships are inferred from column names.

Source evidence

Exact supplied SQL is embedded below for offline review. Evidence links open the corresponding source and highlight the first cited line.

schema.sql — applied source of truth

1-- PostgreSQL. A small project tracker, with no application data.2CREATE TABLE users (3    id bigint PRIMARY KEY,4    email varchar(255) NOT NULL UNIQUE5);67CREATE TABLE projects (8    id bigint PRIMARY KEY,9    name varchar(120) NOT NULL10);1112CREATE TABLE project_members (13    project_id bigint NOT NULL REFERENCES projects(id),14    user_id bigint NOT NULL REFERENCES users(id),15    role varchar(20) NOT NULL,16    PRIMARY KEY (project_id, user_id)17);1819CREATE TABLE tasks (20    id bigint PRIMARY KEY,21    project_id bigint NOT NULL REFERENCES projects(id),22    assignee_id bigint REFERENCES users(id),23    title varchar(200) NOT NULL,24    external_ticket_id varchar(80)25);2627COMMENT ON COLUMN tasks.external_ticket_id IS28    'An identifier from an external issue tracker, not a local foreign key.';

queries.sql — query examples and application-check comments

1-- Show tasks in a project, including unassigned tasks.2SELECT t.id, t.title, u.email AS assignee3FROM tasks AS t4LEFT JOIN users AS u ON u.id = t.assignee_id5WHERE t.project_id = 42;67-- Application code checks project membership before assignment.8-- The schema does not enforce that an assignee belongs to the task's project.9SELECT 1 FROM project_members10WHERE project_id = 42 AND user_id = 7;

add-reviewer.sql — optional, not applied, excluded from ERD

1-- Apply after schema.sql, in a disposable tutorial database.2ALTER TABLE tasks ADD COLUMN reviewer_id bigint REFERENCES users(id);