BrevFeed
Sign in

55. Capstone: the database layer for an app

📖 Reading · 6 min

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;

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)?
← Previous Back to overview →