64 lines
2.7 KiB
Markdown
64 lines
2.7 KiB
Markdown
|
# 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
|
||
|
|
|
||
|
|
```text
|
||
|
|
+-----------------------------+
|
||
|
|
| 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.
|