# Lifetrace Database Schema: How Todos Are Stored in SQLite

> Explore the Lifetrace database schema and discover how todos are stored in SQLite. Learn about the comprehensive todos table supporting iCalendar VTODO standards and more.

- Repository: [FreeU-group/lifetrace](https://github.com/freeu-group/lifetrace)
- Tags: internals
- Published: 2026-03-02

---

**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`](https://github.com/freeu-group/lifetrace/blob/main/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`](https://github.com/freeu-group/lifetrace/blob/main/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`](https://github.com/freeu-group/lifetrace/blob/main/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`](https://github.com/freeu-group/lifetrace/blob/main/add_todo_end_time_001.py)** – Introduces the `end_time` DATETIME column.
- **[`add_todo_timezone_all_day_001.py`](https://github.com/freeu-group/lifetrace/blob/main/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`](https://github.com/freeu-group/lifetrace/blob/main/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`](https://github.com/freeu-group/lifetrace/blob/main/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

```python
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

```python
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

```python
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`](https://github.com/freeu-group/lifetrace/blob/main/lifetrace/storage/models.py) | Defines the `Todo` SQLModel class and all column definitions. |
| [`lifetrace/storage/database_base.py`](https://github.com/freeu-group/lifetrace/blob/main/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`](https://github.com/freeu-group/lifetrace/blob/main/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`](https://github.com/freeu-group/lifetrace/blob/main/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`](https://github.com/freeu-group/lifetrace/blob/main/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`](https://github.com/freeu-group/lifetrace/blob/main/lifetrace/migrations/versions/add_todo_reminder_offsets_001.py) | Adds the `reminder_offsets` JSON column. |
| [`lifetrace/util/path_utils.py`](https://github.com/freeu-group/lifetrace/blob/main/lifetrace/util/path_utils.py) | Contains `get_database_path()` to resolve the SQLite file location. |
| [`lifetrace/util/settings.py`](https://github.com/freeu-group/lifetrace/blob/main/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`](https://github.com/freeu-group/lifetrace/blob/main/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`](https://github.com/freeu-group/lifetrace/blob/main/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`](https://github.com/freeu-group/lifetrace/blob/main/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.