Lifetrace Database Schema: How Todos Are Stored in SQLite

Lifetrace persists todos in a single SQLite file using SQLModel (a type-hinted wrapper around SQLAlchemy), defining a comprehensive todos table with over 30 columns that support iCalendar VTODO standards, hierarchical nesting, and soft-delete auditing.

The open-source Lifetrace application (freeu-group/lifetrace) stores all task data locally in an SQLite database whose path is resolved by lifetrace.util.path_utils.get_database_path(). The schema is defined declaratively in lifetrace/storage/models.py and materialized at runtime when DatabaseBase initializes the engine and invokes SQLModel.metadata.create_all(). For existing installations, Alembic migration scripts in lifetrace/migrations/versions/ incrementally add columns and indexes, ensuring the todos table always reflects the latest schema without data loss.

Database Architecture and Initialization

Lifetrace abstracts all database access through the DatabaseBase class located in lifetrace/storage/database_base.py. During application startup, this class constructs a SQLAlchemy engine pointing to the SQLite file path defined in settings.database_path, then creates all tables registered with SQLModel’s metadata.

Schema Migrations and Versioning

While new databases are created fresh via create_all(), existing databases rely on Alembic migrations to evolve the schema. Key migration files that modified the todos table include:

• add_icalendar_fields_to_todos.py – Adds uid, completed_at, percent_complete, and rrule columns while back-filling default values.
• add_todo_end_time_001.py – Introduces the end_time DATETIME column.
• add_todo_timezone_all_day_001.py – Adds time_zone, tzid, and is_all_day fields for timezone-aware scheduling.
• add_todo_reminder_offsets_001.py – Creates the reminder_offsets JSON column for storing alert timings.

These scripts ensure that users upgrading from older versions retain their data while gaining new schema capabilities.

Todo Table Schema Definition

The Todo model in lifetrace/storage/models.py defines a table that bridges native task management with full iCalendar compliance. The schema inherits timestamp auditing fields (created_at, updated_at, deleted_at) from the TimestampMixin base class and implements soft-delete logic via the deleted_at column.

Identity and Hierarchy

Every todo is uniquely identifiable and optionally nested:

• id – INTEGER PRIMARY KEY, auto-incremented SQLite identifier.
• uid – VARCHAR(64), stores the iCalendar UID (auto-generated with uuid4() if missing).
• parent_todo_id – INTEGER, self-referencing foreign key enabling sub-task hierarchies.
• item_type – VARCHAR(10), always "VTODO" for tasks, allowing polymorphic storage.

iCalendar Compliance Fields

To maintain interoperability with calendaring standards, the table stores raw iCalendar properties:

• dtstart / dtend – DATETIME fields mapped from start_time and end_time when null.
• due – DATETIME, populated from the legacy deadline field for backward compatibility.
• duration – VARCHAR(64), ISO-8601 duration string.
• dtstamp – DATETIME, auto-populated from updated_at for iCalendar DTSTAMP.
• created / last_modified – DATETIME fields synced with created_at and updated_at.
• sequence – INTEGER, tracks revision count (default 0).
• recurrence_id – DATETIME, stores RECURRENCE-ID for exception instances.
• related_to_uid / related_to_reltype – VARCHAR fields storing RELATED-TO linkage.
• rrule – VARCHAR(500), recurrence rule string (added by migration).
• rdate / exdate – TEXT, JSON-encoded arrays of recurrence dates and exclusions.

Scheduling and Timezone Support

Modern scheduling features include:

• start_time / end_time – Native DATETIME columns for task boundaries.
• deadline – Legacy DATETIME field being phased out in favor of due.
• time_zone – VARCHAR(64), IANA timezone identifier (e.g., "America/New_York").
• tzid – VARCHAR(64), alias column populated by migration to mirror time_zone.
• is_all_day – BOOLEAN flag indicating all-day events.
• location – VARCHAR(200).
• categories – TEXT, JSON-encoded list of tags.

Status, Priority, and Progress

Task lifecycle tracking uses both internal and iCalendar-standard fields:

• status – VARCHAR(20): "active", "completed", or "canceled".
• ical_status – VARCHAR(20), iCalendar STATUS derived from status.
• priority – VARCHAR(20): "high", "medium", "low", or "none".
• completed_at – DATETIME, stores iCalendar COMPLETED timestamp.
• percent_complete – INTEGER (0–100), filled from legacy status mappings.

Content and Metadata

User-facing content and application metadata:

• name – VARCHAR(200), human-readable title.
• summary – VARCHAR(200), iCalendar SUMMARY field.
• description – TEXT, long-form description.
• user_notes – TEXT, free-form user annotations.
• classification – VARCHAR(20), iCalendar CLASS (public/private).
• reminder_offsets – TEXT, JSON array of reminder lead times in minutes.
• related_activities – TEXT, JSON array of linked activity IDs.
• order – INTEGER, display sequence among sibling tasks.

Performance Indexes

The DatabaseBase._create_performance_indexes() method ensures fast lookups by creating the following indexes if absent:

• idx_todos_parent_todo_id – Optimizes hierarchical queries fetching child tasks.
• idx_todos_status – Filters active, completed, or canceled tasks efficiently.
• idx_todos_deleted_at – Supports soft-delete filtering (excludes deleted records).
• idx_todos_priority – Enables priority-based sorting.
• idx_todos_uid – Unique UID lookups for iCalendar synchronization.
• idx_todos_order – Fast sorting by UI display order.

These indexes are also declared within migration scripts to ensure consistency across deployment scenarios.

Working with Todos in Python

Creating a New Todo

from lifetrace.storage.database_base import DatabaseBase
from lifetrace.storage.models import Todo

db = DatabaseBase()
with db.get_session() as session:
    new_todo = Todo(
        name="Buy groceries",
        description="Milk, eggs, bread",
        priority="high",
        start_time=None,
        end_time=None,
    )
    session.add(new_todo)
    # `id` is populated after commit (handled by the context manager)

Querying Active Todos

from lifetrace.storage.database_base import DatabaseBase
from lifetrace.storage.models import Todo
from lifetrace.storage.sql_utils import col

db = DatabaseBase()
with db.get_session() as session:
    active = (
        session.query(Todo)
        .filter(col(Todo.status) == "active")
        .order_by(col(Todo.order))
        .all()
    )
    for t in active:
        print(t.id, t.name, t.priority)

Marking a Todo as Completed

from datetime import datetime
from lifetrace.storage.database_base import DatabaseBase
from lifetrace.storage.models import Todo
from lifetrace.storage.sql_utils import col

db = DatabaseBase()
with db.get_session() as session:
    todo = session.get(Todo, 42)          # fetch by primary key

    if todo:
        todo.status = "completed"
        todo.completed_at = datetime.utcnow()
        todo.percent_complete = 100
        session.add(todo)                 # changes are flushed on exit

Key Files

File	Role
lifetrace/storage/models.py	Defines the Todo SQLModel class and all column definitions.
lifetrace/storage/database_base.py	Creates the SQLite engine, runs SQLModel.metadata.create_all(), and builds performance indexes via _create_performance_indexes().
lifetrace/migrations/versions/add_icalendar_fields_to_todos.py	Migration adding iCalendar columns (uid, completed_at, percent_complete, rrule).
lifetrace/migrations/versions/add_todo_end_time_001.py	Adds the end_time column to the schema.
lifetrace/migrations/versions/add_todo_timezone_all_day_001.py	Introduces time_zone, tzid, and is_all_day support.
lifetrace/migrations/versions/add_todo_reminder_offsets_001.py	Adds the reminder_offsets JSON column.
lifetrace/util/path_utils.py	Contains get_database_path() to resolve the SQLite file location.
lifetrace/util/settings.py	Stores default database_path configuration values.

Summary

• SQLModel ORM: Lifetrace uses SQLModel atop SQLAlchemy to bridge Python type hints with SQLite storage, defined in lifetrace/storage/models.py.
• Single SQLite File: All data resides in one database file located via lifetrace.util.path_utils.get_database_path().
• Comprehensive Schema: The todos table contains 30+ columns supporting iCalendar VTODO standards, hierarchical nesting via parent_todo_id, and soft deletes via deleted_at.
• Performance Indexing: Six targeted indexes optimize queries for hierarchy, status, priority, UID lookups, and display ordering.
• Migration Safety: Alembic scripts in lifetrace/migrations/versions/ handle non-destructive schema updates, ensuring data persistence across application versions.

Frequently Asked Questions

What ORM does Lifetrace use for its SQLite database?

Lifetrace uses SQLModel, a library that combines Python type hints with SQLAlchemy’s ORM capabilities. This allows the Todo model in lifetrace/storage/models.py to define database tables using standard Python classes while maintaining full SQLAlchemy compatibility for queries and transactions.

How does Lifetrace handle database schema updates without losing user data?

The application uses Alembic migrations stored in lifetrace/migrations/versions/. When the app starts, existing databases are migrated incrementally—new columns like end_time or time_zone are added by specific migration scripts (e.g., add_todo_end_time_001.py) rather than recreating tables, preserving all existing todo records.

Can todos have sub-tasks or hierarchical relationships?

Yes, the schema supports hierarchical nesting through the parent_todo_id column, which is a self-referencing foreign key to the todos table. This enables parent-child relationships, and the idx_todos_parent_todo_id index ensures efficient queries when fetching sub-tasks.

How does Lifetrace handle deleted todos?

Lifetrace implements soft deletes via the deleted_at DATETIME column (provided by TimestampMixin). Rather than removing rows, the application sets this timestamp, and queries filter out records where deleted_at is not null. The idx_todos_deleted_at index ensures this filtering remains performant even with large datasets.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}