Build a Task Tracker

Tutorial 1 of 5

This tutorial builds a small project-management database from scratch: projects, tasks, labels, and assignments. By the end you will have used SERIAL primary keys, foreign keys, CHECK constraints, ON CONFLICT upserts, RETURNING, and basic CRUD operations—all through a standard psql connection.

1. Design the schema

Three tables: projects to group work, tasks with a status constraint, and labels that can be attached to tasks.

CREATE TABLE projects (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL UNIQUE
);

CREATE TABLE tasks (
    id          SERIAL PRIMARY KEY,
    project_id  INT NOT NULL REFERENCES projects(id),
    title       TEXT NOT NULL,
    status      TEXT NOT NULL DEFAULT 'todo'
                CHECK (status IN ('todo', 'in_progress', 'done')),
    priority    INT DEFAULT 0,
    created_at  DATE DEFAULT '2025-01-15'
);

CREATE TABLE labels (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL UNIQUE
);

CREATE TABLE task_labels (
    task_id   INT NOT NULL REFERENCES tasks(id),
    label_id  INT NOT NULL REFERENCES labels(id)
);

The CHECK constraint on status limits values to exactly three strings. The foreign key on project_id ensures every task belongs to a real project.

2. Seed the data

INSERT INTO projects (name) VALUES
    ('Website Redesign'),
    ('Mobile App'),
    ('API Platform');

INSERT INTO labels (name) VALUES
    ('bug'), ('feature'), ('urgent'), ('backend'), ('frontend');

INSERT INTO tasks (project_id, title, status, priority, created_at) VALUES
    (1, 'Update landing page',    'in_progress', 2, '2025-01-10'),
    (1, 'Fix nav overflow',       'todo',        3, '2025-01-12'),
    (1, 'Add dark mode',          'todo',        1, '2025-01-14'),
    (2, 'Login screen',           'done',        2, '2025-01-05'),
    (2, 'Push notifications',     'in_progress', 3, '2025-01-08'),
    (2, 'Offline sync',           'todo',        1, '2025-01-15'),
    (3, 'Rate limiting',          'todo',        3, '2025-01-11'),
    (3, 'OAuth2 integration',     'in_progress', 2, '2025-01-09'),
    (3, 'Batch endpoint',         'done',        1, '2025-01-06');

INSERT INTO task_labels (task_id, label_id) VALUES
    (1, 5), (2, 1), (2, 5), (2, 3),
    (3, 2), (4, 2), (5, 2), (5, 4),
    (6, 2), (6, 4), (7, 4), (7, 3),
    (8, 4), (9, 4);

3. Query tasks by project

List all tasks for the “Website Redesign” project, ordered by priority.

SELECT t.title, t.status,
       t.priority
FROM tasks t
JOIN projects p
  ON t.project_id = p.id
WHERE p.name = 'Website Redesign'
ORDER BY t.priority DESC;

4. Tasks with their labels

Join through the many-to-many table to show each task’s labels.

SELECT t.title,
       l.name AS label
FROM tasks t
JOIN task_labels tl
  ON t.id = tl.task_id
JOIN labels l
  ON tl.label_id = l.id
WHERE t.status = 'todo'
ORDER BY t.priority DESC,
         t.title;

5. Update status with RETURNING

Mark a task as done and get the updated row back in one round-trip.

UPDATE tasks
SET status = 'done'
WHERE title = 'Fix nav overflow'
RETURNING id, title, status;

6. Upsert a label

Insert a label, or if it already exists, do nothing. Then insert one that does not exist yet.

INSERT INTO labels (name) VALUES ('bug')
ON CONFLICT (name) DO NOTHING;

INSERT INTO labels (name) VALUES ('design')
ON CONFLICT (name) DO NOTHING;

SELECT * FROM labels
ORDER BY id;

The “bug” row was silently skipped (it already existed with id=1). The “design” row was inserted as id=6.

7. Upsert with DO UPDATE

Bump a task’s priority if it already exists, using ON CONFLICT DO UPDATE.

INSERT INTO tasks
  (id, project_id, title,
   status, priority)
VALUES
  (7, 3, 'Rate limiting',
   'todo', 5)
ON CONFLICT (id) DO UPDATE
  SET priority = 5;

SELECT id, title, priority
FROM tasks
WHERE id = 7;

8. Delete with RETURNING

Remove completed tasks and see what was deleted.

DELETE FROM task_labels
WHERE task_id IN (
  SELECT id FROM tasks
  WHERE status = 'done'
);

DELETE FROM tasks
WHERE status = 'done'
RETURNING id, title;

9. Project summary

Count remaining tasks per project and status.

SELECT p.name AS project,
       t.status,
       COUNT(*) AS tasks
FROM tasks t
JOIN projects p
  ON t.project_id = p.id
GROUP BY p.name, t.status
ORDER BY p.name, t.status;

What you learned

In this tutorial you built a task tracker with four tables and exercised the core CRUD workflow:

• SERIAL + PRIMARY KEY for auto-incrementing IDs
• REFERENCES for foreign-key integrity
• CHECK constraints to limit column values
• ON CONFLICT DO NOTHING for idempotent inserts
• ON CONFLICT DO UPDATE for true upserts
• RETURNING to get affected rows in one round-trip
• Multi-table JOINs through a many-to-many relationship