Database Design

The SQLite schema is defined in db/schema.sql.

The database has one table, tasks. There are no inter-table relationships because the application stores only task records.

Table Diagram

+-----------------------------+
| tasks                       |
+-----------------------------+
| id INTEGER PK AUTOINCREMENT |
| title TEXT NOT NULL         |
| description TEXT NOT NULL   |
| due_date TEXT NOT NULL      |
| topic TEXT NOT NULL         |
| status TEXT NOT NULL        |
| archived_at TEXT NULL       |
| created_at TEXT NOT NULL    |
| updated_at TEXT NOT NULL    |
+-----------------------------+

Indexes:
  idx_tasks_active_status (archived_at, status, due_date)
  idx_tasks_topic         (topic COLLATE NOCASE)

`tasks` Columns

Column	Type	Constraints and Purpose
`id`	`INTEGER`	Primary key with `AUTOINCREMENT`; uniquely identifies each task.
`title`	`TEXT`	Required; must not be blank after trimming.
`description`	`TEXT`	Required by schema with default `''`; stores the task description.
`due_date`	`TEXT`	Required; constrained to the `YYYY-MM-DD` shape using a `GLOB` check.
`topic`	`TEXT`	Required; must not be blank after trimming.
`status`	`TEXT`	Required; defaults to `Todo`; constrained to `Todo`, `In-Progress`, or `Complete`.
`archived_at`	`TEXT`	Nullable timestamp set when a task is archived.
`created_at`	`TEXT`	Required; defaults to `CURRENT_TIMESTAMP`.
`updated_at`	`TEXT`	Required; defaults to `CURRENT_TIMESTAMP` and is updated by application code when a task changes.

Constraints

title and topic must contain non-blank text.
due_date must match the date string format YYYY-MM-DD.
status must be one of the fixed values Todo, In-Progress, or Complete.
description, created_at, and updated_at have defaults so inserted rows always have values.

Archiving

Tasks are not deleted by the application. Archiving sets archived_at on the existing task row. Active task lists exclude rows where archived_at is set, while archived tasks remain viewable when archived tasks are included.

Status and Overdue Rules

The only stored statuses are Todo, In-Progress, and Complete.

Overdue is never stored as a status or database column. It is derived when tasks are read: a task is overdue when its due date has passed, it is not Complete, and it is not archived.

Indexes

idx_tasks_active_status on (archived_at, status, due_date): supports active-task filtering, status grouping, and due-date ordering.
idx_tasks_topic on (topic COLLATE NOCASE): supports case-insensitive topic sorting.

2.7 KiB Raw Permalink Blame History