55. Capstone: the database layer for an app

The brief

You're building the database backend for a small task-tracking app (a Trello/Todoist-style tool). Requirements:

• Users can create projects. Each project belongs to exactly one owner (a user) but can have multiple collaborators.
• A project contains tasks. Each task has a title, an optional description, a status (todo, in_progress, done), a priority (1-5), an optional due date, and is assigned to exactly one collaborator on that project (or nobody, initially).
• Tasks can have comments, each posted by a user, timestamped.
• The app needs to show, per project: task counts by status, and each collaborator's current workload (open task count).
• Critical constraint: a task can only be assigned to a user who is actually a collaborator on that task's project — assigning it to someone unrelated to the project must be impossible, not just discouraged.
• The application layer (not covered here, but assumed) will connect using a least-privilege database role, and all queries from app code must be safely parameterized — never assembled by string concatenation. (Roles and client-side parameterization are app/ops concerns this course doesn't teach, but the schema below is designed to be safe under them.)

Your task

1. Design the schema — identify entities, relationships, and the one genuinely tricky constraint (the "assignee must be a collaborator" rule — this needs more than a simple foreign key, since it's a constraint that spans two tables' relationship to a third).
2. Write the DDL with appropriate constraints.
3. Write the two reporting queries the brief asks for (status counts, workload per collaborator).
4. Write one PL/pgSQL function the application would actually call — "assign a task to a collaborator," including the validation this brief demands, using RAISE EXCEPTION (Module 9's error-handling lesson) rather than relying on the application layer to check first.

Reference solution

CREATE TABLE app_user (
    user_id  serial PRIMARY KEY,
    email    text UNIQUE NOT NULL,
    name     text NOT NULL
);

CREATE TABLE project (
    project_id serial PRIMARY KEY,
    owner_id   integer NOT NULL REFERENCES app_user(user_id),
    name       text NOT NULL
);

CREATE TABLE project_collaborator (
    project_id integer NOT NULL REFERENCES project(project_id),
    user_id    integer NOT NULL REFERENCES app_user(user_id),
    PRIMARY KEY (project_id, user_id)
);

CREATE TABLE task (
    task_id     serial PRIMARY KEY,
    project_id  integer NOT NULL REFERENCES project(project_id),
    assignee_id integer REFERENCES app_user(user_id),   -- nullable: unassigned initially
    title       text NOT NULL,
    description text,
    status      text NOT NULL DEFAULT 'todo' CHECK (status IN ('todo', 'in_progress', 'done')),
    priority    integer NOT NULL CHECK (priority BETWEEN 1 AND 5),
    due_date    date
);

CREATE TABLE task_comment (
    comment_id serial PRIMARY KEY,
    task_id    integer NOT NULL REFERENCES task(task_id),
    user_id    integer NOT NULL REFERENCES app_user(user_id),
    body       text NOT NULL,
    posted_at  timestamptz NOT NULL DEFAULT now()
);

The "assignee must be a collaborator" rule cannot be a plain foreign key — a foreign key only checks "does this user exist," not "is this user a collaborator on this specific task's project." This needs either a trigger, or — the approach used here — enforcement inside the one function the application is required to call to make an assignment at all, closing off the gap by controlling the only write path:

CREATE FUNCTION assign_task(p_task_id integer, p_user_id integer)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    v_project_id integer;
    v_is_collaborator boolean;
BEGIN
    SELECT project_id INTO v_project_id FROM task WHERE task_id = p_task_id;
    IF v_project_id IS NULL THEN
        RAISE EXCEPTION 'task % does not exist', p_task_id;
    END IF;

    SELECT EXISTS (
        SELECT 1 FROM project_collaborator
        WHERE project_id = v_project_id AND user_id = p_user_id
    ) INTO v_is_collaborator;

    IF NOT v_is_collaborator THEN
        RAISE EXCEPTION 'user % is not a collaborator on project % — cannot assign task %',
            p_user_id, v_project_id, p_task_id;
    END IF;

    UPDATE task SET assignee_id = p_user_id WHERE task_id = p_task_id;
END;
$$;

The two reporting queries:

-- Task counts by status, per project.
SELECT project_id, status, count(*)
FROM task
GROUP BY project_id, status
ORDER BY project_id, status;

-- Each collaborator's current OPEN (not done) workload, per project.
SELECT pc.project_id, u.name,
    count(t.task_id) FILTER (WHERE t.status != 'done') AS open_task_count
FROM project_collaborator pc
JOIN app_user u ON u.user_id = pc.user_id
LEFT JOIN task t ON t.project_id = pc.project_id AND t.assignee_id = pc.user_id
GROUP BY pc.project_id, u.name
ORDER BY pc.project_id, open_task_count DESC;

Try it

The setup seeds a working slice of this schema: three users, two projects (Ada and Grace collaborate on project 1; Grace and Alan on project 2), and six tasks. Write the workload report: every collaborator of every project with their open (not-'done') assigned task count — including collaborators with zero open tasks, which is why the task join must be a LEFT JOIN with the assignee condition in ON, not WHERE (Check-yourself question 3 is about exactly this line). Columns: project_id, name, open_task_count; order by project_id, open_task_count descending, name.

CREATE TABLE app_user (user_id INTEGER PRIMARY KEY, name VARCHAR NOT NULL);
CREATE TABLE project_collaborator (
  project_id INTEGER NOT NULL,
  user_id    INTEGER NOT NULL,
  PRIMARY KEY (project_id, user_id)
);
CREATE TABLE task (
  task_id     INTEGER PRIMARY KEY,
  project_id  INTEGER NOT NULL,
  assignee_id INTEGER,
  title       VARCHAR NOT NULL,
  status      VARCHAR NOT NULL DEFAULT 'todo' CHECK (status IN ('todo','in_progress','done'))
);
INSERT INTO app_user VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Alan');
INSERT INTO project_collaborator VALUES (1, 1), (1, 2), (2, 2), (2, 3);
INSERT INTO task VALUES
  (1, 1, 1,    'Design schema', 'todo'),
  (2, 1, 1,    'Write DDL',     'in_progress'),
  (3, 1, 2,    'Review',        'done'),
  (4, 1, NULL, 'Deploy',        'todo'),
  (5, 2, 3,    'Prototype',     'todo'),
  (6, 2, 2,    'Ship',          'done');

-- every collaborator's open task count, zeros included
SELECT pc.project_id, u.name,
    ...
FROM project_collaborator pc
JOIN app_user u ON u.user_id = pc.user_id
LEFT JOIN task t ON ...
GROUP BY pc.project_id, u.name
ORDER BY pc.project_id, open_task_count DESC, u.name;

SELECT pc.project_id, u.name,
    count(t.task_id) FILTER (WHERE t.status != 'done') AS open_task_count
FROM project_collaborator pc
JOIN app_user u ON u.user_id = pc.user_id
LEFT JOIN task t ON t.project_id = pc.project_id AND t.assignee_id = pc.user_id
GROUP BY pc.project_id, u.name
ORDER BY pc.project_id, open_task_count DESC, u.name;

Check yourself

1. Why can't a plain foreign key alone enforce "the assignee must be a collaborator on the task's project"?
2. What alternative to the function-based approach here (hint: Module 10.2) could also enforce this rule, and what would the tradeoff be?
3. In the workload query, why does t.assignee_id = pc.user_id belong in the JOIN condition rather than a WHERE clause — what would change if it were moved to WHERE instead (recall Module 4's outer-join lesson)?