users

Base user table for local accounts; password stored as a bcrypt hash

Design Notes

============================================================
@table-comment users Base user table for local accounts; password stored as a bcrypt hash
Source: auth.yaml → register, login, change_password
============================================================

Fields

	Name	Type	Constraints	Nullable	Default	Comment
PKAI	id	INTEGER	-	NO	-	User primary key; auto-incrementing integer
	username	TEXT	-	NO	-	Username, 2–20 characters, globally unique, used for login
	password	TEXT	-	NO	-	Plaintext passwords must not be stored
	created_at	TEXT	-	NO	(datetime('now'))	Account creation time; TEXT in ISO 8601 format, e.g. '2026-04-07T10:30:00'
	updated_at	TEXT	-	NO	(datetime('now'))	Each UPDATE must set this to datetime('now') in SQL manually

Indexes

Name	Type	Fields
-	UNIQUE	username

categories

Task categories; user-defined; name unique per user

Design Notes

Unique index on username (S02.1 Step 9: look up user by username; created automatically by UNIQUE constraint)

============================================================
@table-comment categories Task categories; user-defined; name unique per user
Source: categories.yaml → create_category, delete_category
============================================================

Fields

	Name	Type	Constraints	Nullable	Default	Comment
PKAI	id	INTEGER	-	NO	-	Category primary key; auto-incrementing integer
	user_id	INTEGER	FK `→ users(id) ON DELETE CASCADE`	NO	-	ON DELETE CASCADE: when a user is deleted, all their categories are deleted
	name	TEXT	CHECK `length(name) >= 1 AND length(name) <= 20`	NO	-	Category name, 1–20 characters; must be unique per user
	created_at	TEXT	-	NO	(datetime('now'))	Category creation time; TEXT in ISO 8601 format

Indexes

Name	Type	Fields
idx_categories_user_id	INDEX	user_id
-	UNIQUE	user_id, name

tasks

Tasks; completion status and optional category link

Design Notes

============================================================
@table-comment tasks Tasks; completion status and optional category link
Source: tasks.yaml → create_task, update_task, update_task_status, delete_task
============================================================

Fields

	Name	Type	Constraints	Nullable	Default	Comment
PKAI	id	INTEGER	-	NO	-	Task primary key; auto-incrementing integer
	user_id	INTEGER	FK `→ users(id) ON DELETE CASCADE`	NO	-	ON DELETE CASCADE: when a user is deleted, all their tasks are deleted
	category_id	INTEGER	FK `→ categories(id)`	YES	-	ON DELETE SET NULL: when a category is deleted, tasks remain and this column becomes NULL
	name	TEXT	CHECK `length(name) >= 1 AND length(name) <= 100`	NO	-	Task name, 1–100 characters; required
	note	TEXT	CHECK `note IS OR length(note) <= 500`	YES	-	Task notes; may be NULL; up to 500 characters
	done	INTEGER	CHECK `done IN (0, 1)`	NO	0	INTEGER (0/1) as boolean; SQLite has no native BOOLEAN type
	created_at	TEXT	-	NO	(datetime('now'))	Task creation time; TEXT in ISO 8601 format
	updated_at	TEXT	-	NO	(datetime('now'))	Each UPDATE must set this to datetime('now') in SQL manually

Indexes

Name	Type	Fields
idx_tasks_user_id	INDEX	user_id
idx_tasks_user_done	INDEX	user_id, done

Tables

users

Design Notes

Fields

Indexes

categories

Design Notes

Fields

Indexes

tasks

Design Notes

Fields

Indexes