01 | THE BRIEF: A TASK PLANNER THAT REMEMBERS

A robotics club needs to group tasks by project, record due dates and mark work complete. Yesterday’s tasks must still exist after the program closes. Build a command-line planner with projects and tasks stored in SQLite.

The application supports project, projects, task, list, done, reopen, overdue, counts and quit commands. It uses only Python’s standard library, so Requests is not required for this project. Python 3.10 or later is the course baseline.

This is a local single-user learning application, not a hosted account system. It does not implement authentication, encrypted storage, simultaneous team editing or schema migrations. Use fictional task data while learning.

02 | CHOOSE THE RECORDS AND RELATIONSHIP

A project row has id and name. A task row has id, project_id, title, due_day and done. The task’s project_id refers to its project, allowing many tasks to share one project name without copying that name into every task record.

The primary keys identify records even if two task titles match. Project names are unique under the database’s default text comparison; this project does not promise case-insensitive name uniqueness.

The Boolean-like done field stores 0 or 1. Due dates are validated in Python as real calendar dates and stored in ISO YYYY-MM-DD form. With the same fixed format, ordinary text ordering matches chronological order.

03 | GIVE THE FILES SEPARATE RESPONSIBILITIES

store.py owns validation, connections, SQL and transactions. app.py handles prompts and readable messages. test_store.py checks rules using temporary databases so real project data is untouched.

Keeping database operations outside input() makes them directly testable. The console converts a typed ID to int, while the store still checks that its public methods receive valid values.

The database file is created beside app.py as planner.sqlite3. It is not included in the download. Running the project from a different working folder should not silently create another database elsewhere.

04 | CREATE THE ENVIRONMENT AND RUN

Extract the project and enter its folder. You may use a .venv for consistent interpreter selection even though the application needs no third-party package. Run app.py, create a project and note the displayed ID.

Then add a task using that project ID. Type a real ISO date, such as 2026-10-15. list asks for all, open or completed. Enter quit, restart and list again to see persistence.

TERMINAL | ACTIVATED PROJECT ENVIRONMENT

python --version
python app.py
python -m unittest discover -s . -v

EXAMPLE CONSOLE INPUT | ONE RESPONSE PER PROMPT

project
Robotics
task
1
Check drive motors
2026-10-15
list
all
quit

05 | TRACE AN ACCEPTED SAVE

add_task first validates project ID, title and date. It opens a connection with foreign-key enforcement, checks that the project exists, inserts with placeholders and returns the new task ID.

The with con block commits before control leaves the method successfully. The outer closing block closes the connection. If a failure escapes the transaction block, the pending operation rolls back.

The returned ID is a stable record reference; it is not the task’s position in a sorted report. Report order is explicit: due date, then ID. This makes equal-date reports predictable.

PYTHON | CALLING THE STORE FROM A SEPARATE CHECK FILE

from pathlib import Path
from store import TaskStore

store = TaskStore(Path("practice.sqlite3"))
project_id = store.add_project("Robotics")
task_id = store.add_task(project_id, "Check drive motors", "2026-10-15")
print("Created task:", task_id)

06 | LOOK CLOSELY AT THE STORE

Read the checked date parser before the SQL. It requires the fixed format, then asks date.fromisoformat to check calendar validity. This rejects 2026-02-30 rather than treating its appearance as proof of a valid date.

Exact-int checks reject True as a record ID. set_done separately requires a real Boolean argument and stores its 0/1 representation. The database constraints add foreign-key, uniqueness and done-value enforcement.

The schema does not fully enforce the application’s calendar policy or every exact Python type. Keep the method boundary checks and database constraints together; neither is a reason to discard the other.

import re
from datetime import date

def parse_day(text: str) -> str:
    if type(text) is not str:
        raise ValueError("Supply date text.")
    text = text.strip()
    if not re.fullmatch(r"[0-9]{4}-[0-9]{2}-[0-9]{2}", text):
        raise ValueError("Use YYYY-MM-DD.")
    return date.fromisoformat(text).isoformat()

07 | REPORTS NEED PRECISE RULES

overdue(check_day) returns only open tasks with due_day strictly before the check day. A task due on October fifteenth is not late on that date; it is overdue on the sixteenth under this project’s policy. Completed tasks are excluded.

counts uses LEFT JOIN so a project with no tasks still appears. COUNT(t.id) counts matching task rows; COUNT(*) would count the retained empty-project row as though it were a task. COALESCE turns a missing sum into zero.

The status filter uses SQL fragments selected from an internal fixed allowlist. It never interpolates a user-supplied WHERE clause. Parameter placeholders handle values, while structural choices remain controlled.

08 | TRY THE WORKFLOW IN A BROWSER MODEL

This simplified browser model keeps tasks only while this page is open. It does not use SQLite and cannot demonstrate persistence after reload. The downloaded Python project stores committed records in a real database file.

09 | TEST THE RULES, NOT JUST ONE HAPPY RUN

The bundled suite contains 14 test methods. It checks persistence through a fresh store instance, duplicate-project rollback, apostrophes and SQL-looking titles, foreign-key enforcement, filters, completion and reopening, date validity, ID policies, overdue boundaries and counts including an empty project.

TemporaryDirectory creates isolated test folders, which the tests clean up. They do not erase your planner.sqlite3. Run the suite after every store change.

Also test the console manually: try a nonnumeric ID, a missing project, a duplicate name and a bad date, then make a valid save. Expected rejection must not prevent subsequent valid work. Close and restart the whole program to verify the user-facing persistence story.

10 | COMMIT AND HAND OVER

Initialise Git, stage the source, README and tests, then inspect the staged diff. Ignore .venv, caches, local database files and sidecars. Commit a passing version with a meaningful message.

Ask a partner to create an empty installation from the download. They should be able to run it, create records, quit, restart and see them again. Explain which file holds their data and how to back it up after closing the app.

Extension: add priorities or deletion only after writing clear rules and tests. Changing table definitions requires a migration plan for existing data. Avoid silently dropping the database to make new code appear to work.

PUT YOUR SKILLS TO WORK

Use the Section 7 challenges to practise these ideas independently. Work in your Python editor, predict results and keep a short test record.