Skip to content

DATA-FORMAT.md

Latest commit

 

History

History
117 lines (92 loc) · 3.96 KB

DATA-FORMAT.md

File metadata and controls

117 lines (92 loc) · 3.96 KB
Raw

Taskly Data Format (Contract)

Version 4 (PRAGMA user_version = 4). Canonical for all platforms. Any schema change must land in this file and in all platform apps in the same release. Never break an existing .db file.

1. File locations (all desktop platforms)

Item Path
App directory ~/.taskly/ (created on demand)
Default database ~/.taskly/tasks.db
Config ~/.taskly/config.ini
Home resolution Windows: USERPROFILE → HOME; macOS/Linux: HOME

Resolution order for the active DB: --db argument → config last-db-path → ~/.taskly/tasks.db. Keeping everything under one folder is deliberate: users sync .taskly/ (or any folder holding their .db) via iCloud / OneDrive / Dropbox.

2. Pragmas

  • PRAGMA journal_mode = WAL; on every connect (persistent once set; much safer when the file lives in a cloud-synced folder).
  • PRAGMA user_version is written outside any transaction (a no-op inside one).

3. Schema (fresh install creates v4 directly)

CREATE TABLE lists (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    icon TEXT,
    color INTEGER,
    created_at TEXT NOT NULL
);
CREATE TABLE tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    list_id INTEGER,
    text TEXT NOT NULL,
    due_date TEXT,
    due_time TEXT,
    completed INTEGER DEFAULT 0,
    created_at TEXT NOT NULL,
    notes TEXT,
    FOREIGN KEY (list_id) REFERENCES lists (id)
);
CREATE INDEX IF NOT EXISTS idx_tasks_list_id ON tasks(list_id);
CREATE INDEX IF NOT EXISTS idx_tasks_completed ON tasks(completed);
CREATE INDEX IF NOT EXISTS idx_tasks_due_date ON tasks(due_date);
INSERT INTO lists (name, icon, color, created_at)
VALUES ('工作', '📋', -4104388, '<created_at>');
  • Default list name is the literal 工作 (not localized).
  • TodoList.defaultIcon = "📋"; TodoList.defaultColor = ARGB 0xFFC15F3C stored as signed 32-bit = -4104388. List colors are signed ARGB ints; every platform must bit-cast through Int32/uint, never through a sign- preserving wider integer.

4. Storage formats

Column Format
due_date TEXT yyyy-MM-dd (local time)
due_time TEXT HH:mm (24h)
created_at TEXT ISO-8601 round-trip with local offset (e.g. 2026-09-15T14:23:45.1234567+08:00; fractional digits may vary by platform)
completed INTEGER 0/1
color INTEGER signed ARGB

"Today" is decided locally: date(t.due_date) = '<yyyy-MM-dd of now>'.

5. Migrations

Run in an explicit transaction (DDL + INSERT atomic); set user_version after commit. Detection is idempotent (PRAGMA table_info before ALTER TABLE ADD COLUMN).

oldVersion < Action
2 create the three indexes above
3 lists.icon TEXT, lists.color INTEGER
4 tasks.due_time TEXT, tasks.notes TEXT

Fresh DBs (oldVersion == 0) create v4 directly and seed the default list. After connect, re-ensure lists.icon/lists.color exist (belt-and-braces, failures logged only).

6. Row ↔ model mapping

tasks + LEFT JOIN lists l ON t.list_id = l.id (l.name AS list_name):

id, list_id, text, due_date, due_time, completed (true iff ==1), created_at, notes, list_name (derived, never persisted).

7. Config (config.ini)

INI, no sections; key = value; ;/# comments; first = splits; both sides trimmed; keys case-insensitive. Writer emits two fixed header lines:

# Taskly configuration
# This file is automatically generated and managed by Taskly.
Key Meaning Default
last-db-path last opened DB (read also accepts legacy last_db_path) —
language zh / en zh
last-selected-list-id int as string; unparseable → 0 0

8. Conformance

Each platform's test suite must: create a fresh DB and assert the schema above; open a fixture DB written by another platform and round-trip every column; migrate a user_version=0..3 fixture to 4 idempotently.