This document describes the initial target SQLite tables. The schema will be implemented through migrations in later milestones.
Stores local and provider accounts.
idprovider: local, google, outlook, caldav, or future task
providers such as todoistdisplay_nameemailcreated_atupdated_atStores calendars visible to the app.
idaccount_idproviderprovider_calendar_idnamecolortimezoneis_primaryis_read_onlyis_selectedsync_enabledcreated_atupdated_atStores calendar events used for rendering and sync reconciliation.
idcalendar_idproviderprovider_event_idprovider_etagical_uidtitledescriptionlocationstart_atend_atstart_timezoneend_timezoneis_all_daystatusshow_asvisibilityrecurrence_rulerecurrence_jsonrecurring_event_idoriginal_start_atsync_statusdeleted_atcreated_atupdated_atFor ICS import, ical_uid is used as the duplicate key within a local
calendar. Imports skip events when another event in the same calendar already
has the same ical_uid; they do not update existing events yet. For ICS export,
events missing an ical_uid receive a stable generated UID before the file is
written.
Stores attendees for meetings and provider-backed events.
idevent_idemaildisplay_nameroleresponse_statusis_resourceStores event reminders.
idevent_idmethodminutes_beforeStores links from calendar events to documents, tasks, provider URLs, or other resources.
idevent_idtitleurllink_typeStores per-account or per-calendar sync cursors.
idaccount_idcalendar_idprovidersync_tokendelta_linkpage_tokenwindow_startwindow_endlast_full_sync_atlast_incremental_sync_atcreated_atupdated_atStores queued provider mutations for outbox-style sync.
idprovidercalendar_idevent_idoperation_typepayload_jsonstatusattemptslast_errornext_attempt_atcreated_atupdated_atStores encrypted provider credential payloads for connected accounts. The
payload is encrypted by main-process TokenVault before it is written; renderer
code must never receive this row or the decrypted payload.
account_idproviderencrypted_payloadcreated_atupdated_atStores first-class tasks as a separate domain from calendar events. A task is the parent unit of work and owns completion state. Provider-backed tasks can later sync task content and completion with Todoist, but task calendar placement is modeled separately.
idaccount_idprovider: local or future todoistprovider_task_idprovider_project_idprovider_section_idprovider_parent_idprovider_etagtitledescriptionstatus: open, completed, deleted, or archivedprioritylabels_jsondue_atdeadline_atduration_minutesis_recurringcompleted_atdeleted_atsync_statusraw_provider_jsoncreated_atupdated_atStores calendar work sessions for tasks. These are not calendar events and must
not be persisted through FullCalendar or the events table. One task can have
many task_blocks, and every block points back to exactly one parent task.
Completing the parent task makes all of its blocks render completed.
idtask_idcalendar_idstart_atend_atis_all_daystatus: scheduled, skipped, or rescheduledsource: manual, todoist_due, or auto_suggestedis_primary_todoist_schedulecreated_atupdated_atTodoist sync will apply to the parent task. If a Todoist due time creates a calendar placement, it should create or update one primary task block. Extra Dainvo blocks remain local scheduling data unless a later sync policy says otherwise.
Stores provider-specific task project metadata for future Todoist sync.
idaccount_idproviderprovider_project_idnamecoloris_inboxis_archivedis_deletedcreated_atupdated_atStores account-level task sync cursors for future Todoist incremental sync.
idaccount_idprovidersync_tokenlast_full_sync_atlast_incremental_sync_atcreated_atupdated_atStores queued task-provider mutations for future outbox-style Todoist sync.
idaccount_idtask_idprovideroperation_typepayload_jsonstatusattemptslast_errornext_attempt_atcreated_atupdated_atStores project documents or tasks that can be linked to calendar time blocks.
idtitleurldue_atstatuscreated_atupdated_atStores scheduled work blocks linked to documents and calendar events.
iddocument_idevent_idtitlestart_atend_atstatuscreated_atupdated_at