Data model

Every table the database has, from the migration snapshot drizzle-kit writes out of the schema — so this page cannot disagree with the schema, and a column added without a migration does not appear here because it does not exist.

54 tables.

Table	Columns	Belongs to a user
`account`	13	yes
`activities`	9	yes
`api_tokens`	12	yes
`audit_events`	7	yes
`billing_checkouts`	7	yes
`billing_events`	8	—
`calendar_feeds`	9	yes
`categories`	5	yes
`client_errors`	7	yes
`daily_wins`	6	yes
`data_points`	10	yes
`data_streams`	14	yes
`diary_entries`	9	yes
`diary_entry_tags`	4	yes
`entry_people`	4	yes
`exceptional_tasks`	18	yes
`goal_areas`	6	yes
`goal_links`	6	yes
`goals`	17	yes
`habit_occurrences`	6	yes
`habits`	7	yes
`idea_tags`	4	yes
`ideas`	8	yes
`invites`	9	—
`mail_failures`	11	—
`media`	9	yes
`notebooks`	8	yes
`people`	12	yes
`plan_members`	5	—
`planning_schemes`	5	yes
`plugin_manifests`	8	yes
`price_points`	6	yes
`push_subscriptions`	9	yes
`quotes`	5	yes
`recipe_images`	7	yes
`recipe_items`	8	yes
`recipes`	12	yes
`recurring_tasks`	19	yes
`reminders`	10	yes
`scheme_slots`	11	yes
`session`	9	yes
`shopping_categories`	7	yes
`shopping_items`	12	yes
`subscribers`	7	—
`subscriptions`	15	yes
`suppressed_slots`	5	yes
`tags`	3	yes
`task_records`	15	yes
`todo_tasks`	15	yes
`user`	11	—
`user_settings`	4	yes
`verification`	6	—
`webhook_subscriptions`	11	yes
`weekly_reviews`	7	yes

account

Column	Type	Null	Default	Notes
`id`	text	not null	—	primary key
`account_id`	text	not null	—	—
`provider_id`	text	not null	—	—
`user_id`	text	not null	—	→ `user.id`
`access_token`	text	null	—	—
`refresh_token`	text	null	—	—
`id_token`	text	null	—	—
`access_token_expires_at`	integer	null	—	—
`refresh_token_expires_at`	integer	null	—	—
`scope`	text	null	—	—
`password`	text	null	—	—
`created_at`	integer	not null	`(cast(unixepoch('subsecond') * 1000 as integer))`	—
`updated_at`	integer	not null	—	—

Indexes:

account_userId_idx on user_id

activities

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`name`	text	not null	—	—
`category_id`	integer	not null	—	→ `categories.id`
`description`	text	null	`''`	—
`color`	text	null	`''`	—
`active`	integer	not null	`true`	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—
`updated_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

activities_user_idx on user_id
activities_category_idx on category_id
activities_active_idx on active

api_tokens

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`name`	text	not null	—	—
`token_hash`	text	not null	—	—
`prefix`	text	not null	—	—
`plaintext`	text	null	—	—
`scopes`	text	not null	`''`	—
`last_used_at`	text	null	—	—
`expires_at`	text	null	—	—
`revoked_at`	text	null	—	—
`created_at`	text	not null	—	—
`updated_at`	text	not null	—	—

Indexes:

api_tokens_hash_unique on token_hash — unique
api_tokens_user_idx on user_id

audit_events

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`actor_id`	text	null	—	—
`event`	text	not null	—	—
`detail`	text	not null	`'{}'`	—
`ip`	text	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

audit_events_user_idx on user_id
audit_events_created_idx on created_at

billing_checkouts

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`provider`	text	not null	—	—
`provider_transaction_id`	text	not null	—	—
`settled_at`	text	null	—	—
`settled_by`	text	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

billing_checkouts_user_idx on user_id
billing_checkouts_transaction_unique on provider_transaction_id — unique

billing_events

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`provider`	text	not null	—	—
`event_id`	text	not null	—	—
`event_type`	text	not null	—	—
`payload`	text	not null	—	—
`processed_at`	text	null	—	—
`error`	text	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

billing_events_unique on provider, event_id — unique

calendar_feeds

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`name`	text	not null	—	—
`url`	text	not null	—	—
`color`	text	not null	`'#6b7280'`	—
`body`	text	null	—	—
`fetched_at`	text	null	—	—
`last_error`	text	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

calendar_feeds_user_idx on user_id

client_errors

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	null	—	—
`message`	text	not null	—	—
`url`	text	null	—	—
`stack`	text	null	—	—
`user_agent`	text	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

client_errors_created_idx on created_at

daily_wins

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`for_date`	text	not null	—	—
`position`	integer	not null	—	—
`content`	text	not null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

daily_wins_user_date_idx on user_id, for_date
daily_wins_slot_unique on user_id, for_date, position — unique

data_points

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`stream_id`	integer	not null	—	→ `data_streams.id`
`external_id`	text	not null	—	—
`at`	text	not null	—	—
`local_date`	text	not null	—	—
`value_num`	real	null	—	—
`value_text`	text	null	—	—
`meta`	text	not null	`'{}'`	—
`created_at`	text	not null	—	—

Indexes:

data_points_stream_external_unique on stream_id, external_id — unique
data_points_stream_at_idx on stream_id, at
data_points_user_idx on user_id
data_points_stream_local_date_idx on stream_id, local_date

data_streams

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`slug`	text	not null	—	—
`name`	text	not null	—	—
`source`	text	not null	—	—
`kind`	text	not null	—	—
`unit`	text	not null	`''`	—
`display`	text	not null	`'list'`	—
`config`	text	not null	`'{}'`	—
`show_on_dashboard`	integer	not null	`false`	—
`retention_days`	integer	null	—	—
`archived_at`	text	null	—	—
`created_at`	text	not null	—	—
`updated_at`	text	not null	—	—

Indexes:

data_streams_user_slug_unique on user_id, slug — unique
data_streams_user_idx on user_id

diary_entries

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`seq`	integer	not null	`0`	—
`notebook_seq`	integer	null	—	—
`content`	text	not null	—	—
`for_date`	text	null	—	—
`notebook_id`	integer	null	—	→ `notebooks.id`
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—
`updated_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

diary_entries_user_idx on user_id
diary_entries_created_idx on created_at
diary_entries_for_date_idx on for_date
diary_entries_notebook_idx on notebook_id
diary_entries_user_seq_unique on user_id, seq — unique
diary_entries_notebook_seq_unique on notebook_id, notebook_seq — unique

diary_entry_tags

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`entry_id`	integer	not null	—	→ `diary_entries.id`
`tag_id`	integer	not null	—	→ `tags.id`

Indexes:

diary_entry_tags_user_idx on user_id
diary_entry_tags_entry_idx on entry_id
diary_entry_tags_tag_idx on tag_id

entry_people

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`entry_id`	integer	not null	—	→ `diary_entries.id`
`person_id`	integer	not null	—	→ `people.id`

Indexes:

entry_people_user_idx on user_id
entry_people_entry_idx on entry_id
entry_people_person_idx on person_id
entry_people_unique on entry_id, person_id — unique

exceptional_tasks

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`date`	text	not null	—	—
`start_time`	text	not null	—	—
`duration_minutes`	integer	not null	`60`	—
`mode`	text	not null	—	—
`category_id`	integer	null	—	→ `categories.id`
`activity_id`	integer	null	—	→ `activities.id`
`label`	text	null	`''`	—
`active`	integer	not null	`true`	—
`remind_lead_minutes`	integer	null	—	—
`notebook_id`	integer	null	—	→ `notebooks.id`
`urgency`	integer	null	—	—
`interest`	integer	null	—	—
`energy`	integer	null	—	—
`meta`	text	not null	`'{}'`	—
`recipe_id`	integer	null	—	→ `recipes.id`
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

exceptional_tasks_user_date_idx on user_id, date
exceptional_tasks_notebook_idx on notebook_id

Checks — enforced by the database, not only by the service layer:

exceptional_urgency_range: "exceptional_tasks"."urgency" IS NULL OR "exceptional_tasks"."urgency" BETWEEN 1 AND 5
exceptional_interest_range: "exceptional_tasks"."interest" IS NULL OR "exceptional_tasks"."interest" BETWEEN 1 AND 5
exceptional_energy_range: "exceptional_tasks"."energy" IS NULL OR "exceptional_tasks"."energy" BETWEEN 1 AND 5
exceptional_mode_category: "exceptional_tasks"."mode" != 'category' OR "exceptional_tasks"."category_id" IS NOT NULL
exceptional_mode_activity: "exceptional_tasks"."mode" != 'activity' OR "exceptional_tasks"."activity_id" IS NOT NULL

goal_areas

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`name`	text	not null	—	—
`color`	text	not null	`'#6b7280'`	—
`sort_order`	integer	not null	`0`	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

goal_areas_user_idx on user_id
goal_areas_user_name_unique on user_id, name — unique

goal_links

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`goal_id`	integer	not null	—	→ `goals.id`
`slot_id`	integer	null	—	→ `recurring_tasks.id`
`todo_id`	integer	null	—	→ `todo_tasks.id`
`activity_id`	integer	null	—	→ `activities.id`

Indexes:

goal_links_user_idx on user_id
goal_links_goal_idx on goal_id

Checks — enforced by the database, not only by the service layer:

goal_link_has_exactly_one_target: (CASE WHEN "goal_links"."slot_id" IS NULL THEN 0 ELSE 1 END) + (CASE WHEN "goal_links"."todo_id" IS NULL THEN 0 ELSE 1 END) + (CASE WHEN "goal_links"."activity_id" IS NULL THEN 0 ELSE 1 END) = 1

goals

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`area_id`	integer	null	—	→ `goal_areas.id`
`notebook_id`	integer	null	—	→ `notebooks.id`
`parent_id`	integer	null	—	—
`title`	text	not null	—	—
`notes`	text	null	`''`	—
`horizon`	text	not null	—	—
`period_start`	text	not null	—	—
`target_value`	real	null	—	—
`current_value`	real	not null	`0`	—
`unit`	text	null	`''`	—
`status`	text	not null	`'open'`	—
`outcome`	text	null	`''`	—
`closed_at`	text	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—
`updated_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

goals_user_idx on user_id
goals_period_idx on user_id, horizon, period_start
goals_parent_idx on parent_id
goals_area_idx on area_id
goals_notebook_idx on notebook_id

Checks — enforced by the database, not only by the service layer:

goals_target_positive: "goals"."target_value" IS NULL OR "goals"."target_value" > 0

habit_occurrences

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`habit_id`	integer	not null	—	→ `habits.id`
`date`	text	not null	—	—
`notes`	text	null	`''`	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

habit_occurrences_user_idx on user_id
habit_occurrences_habit_idx on habit_id
habit_occurrences_date_idx on date

habits

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`name`	text	not null	—	—
`description`	text	null	`''`	—
`type`	text	not null	`'bad'`	—
`scheduled_days`	text	null	`''`	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

habits_user_idx on user_id

idea_tags

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`idea_id`	integer	not null	—	→ `ideas.id`
`tag_id`	integer	not null	—	→ `tags.id`

Indexes:

idea_tags_user_idx on user_id
idea_tags_idea_idx on idea_id
idea_tags_tag_idx on tag_id

ideas

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`content`	text	not null	—	—
`is_applied`	integer	not null	`false`	—
`applied_note`	text	null	—	—
`favorite`	integer	not null	`false`	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—
`updated_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

ideas_user_idx on user_id
ideas_created_idx on created_at

invites

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`code`	text	not null	—	—
`note`	text	null	`''`	—
`created_by`	text	not null	—	—
`expires_at`	text	null	—	—
`grants_until`	text	null	—	—
`used_at`	text	null	—	—
`used_by`	text	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

invites_code_unique on code — unique

mail_failures

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`kind`	text	not null	—	—
`to_email`	text	not null	—	—
`subject`	text	not null	—	—
`error`	text	not null	—	—
`attempts`	integer	not null	`1`	—
`body_text`	text	null	—	—
`body_html`	text	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—
`last_attempt_at`	text	not null	`(CURRENT_TIMESTAMP)`	—
`resolved_at`	text	null	—	—

Indexes:

mail_failures_open_idx on resolved_at

media

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`mime`	text	not null	—	—
`filename`	text	not null	`''`	—
`alt`	text	not null	`''`	—
`byte_size`	integer	not null	—	—
`bytes`	blob	not null	—	—
`sha256`	text	not null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

media_user_idx on user_id
media_user_sha_unique on user_id, sha256 — unique

Checks — enforced by the database, not only by the service layer:

media_size_positive: "media"."byte_size" > 0

notebooks

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`title`	text	not null	—	—
`description`	text	null	`''`	—
`shared_with_family`	integer	not null	`false`	—
`closed_at`	text	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—
`updated_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

notebooks_user_idx on user_id
notebooks_user_title_unique on user_id, title — unique

people

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`name`	text	not null	—	—
`relationship`	text	not null	`'other'`	—
`birthday`	text	null	—	—
`remind_on_birthday`	integer	not null	`true`	—
`phone`	text	null	—	—
`email`	text	null	—	—
`notes`	text	null	`''`	—
`picture_id`	integer	null	—	→ `media.id`
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—
`updated_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

people_user_idx on user_id
people_user_name_unique on user_id, name — unique

plan_members

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`owner_id`	text	not null	—	→ `user.id`
`member_id`	text	not null	—	→ `user.id`
`accepted_at`	text	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

plan_members_owner_idx on owner_id
plan_members_member_unique on member_id — unique

planning_schemes

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`name`	text	not null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—
`updated_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

schemes_user_idx on user_id
schemes_user_name_unique on user_id, name — unique

plugin_manifests

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`source`	text	not null	—	—
`name`	text	not null	—	—
`description`	text	null	`''`	—
`homepage`	text	null	`''`	—
`meta_keys`	text	not null	`'[]'`	—
`updated_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

plugin_manifests_user_source_unique on user_id, source — unique
plugin_manifests_user_idx on user_id

price_points

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`item_id`	integer	not null	—	→ `shopping_items.id`
`price_cents`	integer	not null	—	—
`for_date`	text	not null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

price_points_user_item_idx on user_id, item_id
price_points_date_idx on for_date

push_subscriptions

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`endpoint`	text	not null	—	—
`p256dh`	text	not null	—	—
`auth`	text	not null	—	—
`label`	text	null	—	—
`failures`	integer	not null	`0`	—
`last_push_at`	text	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

push_subscriptions_user_idx on user_id
push_subscriptions_endpoint_idx on endpoint — unique

quotes

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`text`	text	not null	—	—
`author`	text	null	`''`	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

quotes_user_idx on user_id

recipe_images

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`recipe_id`	integer	not null	—	→ `recipes.id`
`media_id`	integer	not null	—	→ `media.id`
`position`	integer	not null	`0`	—
`is_main`	integer	not null	`false`	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

recipe_images_user_idx on user_id
recipe_images_recipe_idx on recipe_id
recipe_images_once_unique on recipe_id, media_id — unique
recipe_images_one_main_unique on recipe_id — unique

recipe_items

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`recipe_id`	integer	not null	—	→ `recipes.id`
`item_id`	integer	not null	—	→ `shopping_items.id`
`quantity`	real	null	—	—
`unit`	text	null	`''`	—
`note`	text	null	`''`	—
`sort_order`	integer	not null	`0`	—

Indexes:

recipe_items_user_idx on user_id
recipe_items_recipe_idx on recipe_id
recipe_items_item_idx on item_id
recipe_items_unique on recipe_id, item_id — unique

Checks — enforced by the database, not only by the service layer:

recipe_items_quantity_positive: "recipe_items"."quantity" IS NULL OR "recipe_items"."quantity" > 0

recipes

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`title`	text	not null	—	—
`method`	text	not null	`''`	—
`notes`	text	null	`''`	—
`servings`	integer	null	—	—
`minutes`	integer	null	—	—
`source`	text	null	`''`	—
`last_cooked_at`	text	null	—	—
`archived_at`	text	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—
`updated_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

recipes_user_idx on user_id

Checks — enforced by the database, not only by the service layer:

recipes_servings_positive: "recipes"."servings" IS NULL OR "recipes"."servings" > 0
recipes_minutes_positive: "recipes"."minutes" IS NULL OR "recipes"."minutes" > 0

recurring_tasks

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`weekday`	integer	not null	—	—
`recurrence`	text	not null	`'weekly'`	—
`start_time`	text	not null	—	—
`duration_minutes`	integer	not null	`60`	—
`mode`	text	not null	—	—
`category_id`	integer	null	—	→ `categories.id`
`activity_id`	integer	null	—	→ `activities.id`
`label`	text	null	`''`	—
`active`	integer	not null	`true`	—
`remind_lead_minutes`	integer	null	—	—
`urgency`	integer	null	—	—
`interest`	integer	null	—	—
`energy`	integer	null	—	—
`meta`	text	not null	`'{}'`	—
`recipe_id`	integer	null	—	→ `recipes.id`
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—
`updated_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

slots_user_idx on user_id
slots_weekday_idx on weekday
slots_weekday_time_idx on weekday, start_time

Checks — enforced by the database, not only by the service layer:

slots_urgency_range: "recurring_tasks"."urgency" IS NULL OR "recurring_tasks"."urgency" BETWEEN 1 AND 5
slots_interest_range: "recurring_tasks"."interest" IS NULL OR "recurring_tasks"."interest" BETWEEN 1 AND 5
slots_energy_range: "recurring_tasks"."energy" IS NULL OR "recurring_tasks"."energy" BETWEEN 1 AND 5
slots_weekday_range: "recurring_tasks"."weekday" >= 0 AND "recurring_tasks"."weekday" <= 6
slots_mode_category: "recurring_tasks"."mode" != 'category' OR "recurring_tasks"."category_id" IS NOT NULL
slots_mode_activity: "recurring_tasks"."mode" != 'activity' OR "recurring_tasks"."activity_id" IS NOT NULL

reminders

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`subject_kind`	text	not null	`'free'`	—
`subject_id`	integer	null	—	—
`remind_at`	text	not null	—	—
`message`	text	not null	—	—
`delivered_at`	text	null	—	—
`pushed_at`	text	null	—	—
`dismissed_at`	text	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

reminders_user_idx on user_id
reminders_due_idx on user_id, delivered_at, remind_at
reminders_subject_idx on subject_kind, subject_id

scheme_slots

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`scheme_id`	integer	not null	—	→ `planning_schemes.id`
`weekday`	integer	not null	—	—
`start_time`	text	not null	—	—
`duration_minutes`	integer	not null	`60`	—
`mode`	text	not null	—	—
`category_id`	integer	null	—	→ `categories.id`
`activity_id`	integer	null	—	→ `activities.id`
`label`	text	null	`''`	—
`active`	integer	not null	`true`	—

Indexes:

scheme_slots_user_idx on user_id
scheme_slots_scheme_idx on scheme_id

session

Column	Type	Null	Default	Notes
`id`	text	not null	—	primary key
`expires_at`	integer	not null	—	—
`token`	text	not null	—	—
`created_at`	integer	not null	`(cast(unixepoch('subsecond') * 1000 as integer))`	—
`updated_at`	integer	not null	—	—
`ip_address`	text	null	—	—
`user_agent`	text	null	—	—
`user_id`	text	not null	—	→ `user.id`
`impersonated_by`	text	null	—	—

Indexes:

session_token_unique on token — unique
session_userId_idx on user_id

shopping_categories

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`name`	text	not null	—	—
`shared_with_family`	integer	not null	`false`	—
`is_food`	integer	not null	`false`	—
`sort_order`	integer	not null	`0`	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

shopping_categories_user_idx on user_id
shopping_categories_user_name_unique on user_id, name — unique

shopping_items

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`name`	text	not null	—	—
`type`	text	not null	—	—
`shopping_category_id`	integer	null	—	→ `shopping_categories.id`
`notes`	text	null	`''`	—
`bought`	integer	not null	`false`	—
`bought_at`	text	null	—	—
`price_cents`	integer	null	—	—
`snoozed`	integer	not null	`false`	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—
`updated_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

shopping_items_user_idx on user_id
shopping_items_type_idx on type
shopping_items_bought_idx on bought
shopping_items_snoozed_idx on snoozed
shopping_items_category_idx on shopping_category_id

subscribers

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`email`	text	not null	—	—
`token`	text	not null	—	—
`source`	text	not null	`'site'`	—
`confirmed_at`	text	null	—	—
`unsubscribed_at`	text	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

subscribers_email_unique on email — unique
subscribers_token_unique on token — unique
subscribers_confirmed_idx on confirmed_at

subscriptions

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`plan`	text	not null	`'none'`	—
`status`	text	not null	`'trialing'`	—
`provider`	text	not null	`'none'`	—
`provider_customer_id`	text	null	—	—
`provider_subscription_id`	text	null	—	—
`current_period_end`	text	null	—	—
`trial_ends_at`	text	null	—	—
`trial_notice_sent_at`	text	null	—	—
`cancel_at`	text	null	—	—
`portal_url`	text	null	—	—
`seats`	integer	not null	`1`	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—
`updated_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

subscriptions_user_unique on user_id — unique
subscriptions_provider_idx on provider_subscription_id

suppressed_slots

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`date`	text	not null	—	—
`slot_id`	integer	not null	—	→ `recurring_tasks.id`
`moved_to_id`	integer	null	—	→ `exceptional_tasks.id`

Indexes:

suppressed_slots_user_date_idx on user_id, date
suppressed_slots_unique on user_id, date, slot_id — unique

task_records

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`slot_id`	integer	null	—	→ `recurring_tasks.id`
`exceptional_slot_id`	integer	null	—	→ `exceptional_tasks.id`
`scheduled_at`	text	not null	—	—
`status`	text	not null	`'todo'`	—
`completed_at`	text	null	—	—
`notes`	text	null	`''`	—
`resolved_activity_id`	integer	null	—	→ `activities.id`
`duration_override`	integer	null	—	—
`label_override`	text	null	—	—
`urgency_override`	integer	null	—	—
`interest_override`	integer	null	—	—
`energy_override`	integer	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

instances_user_idx on user_id
instances_slot_idx on slot_id
instances_exceptional_idx on exceptional_slot_id
instances_scheduled_idx on scheduled_at
instances_status_idx on status
instances_slot_scheduled_idx on slot_id, scheduled_at
instances_exceptional_unique on exceptional_slot_id — unique

Checks — enforced by the database, not only by the service layer:

instance_has_exactly_one_source: ("task_records"."slot_id" IS NULL) != ("task_records"."exceptional_slot_id" IS NULL)

todo_tasks

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`title`	text	not null	—	—
`notes`	text	null	`''`	—
`completed`	integer	not null	`false`	—
`category_id`	integer	null	—	→ `categories.id`
`notebook_id`	integer	null	—	→ `notebooks.id`
`scheduled_date`	text	null	—	—
`status`	text	not null	`'todo'`	—
`sort_order`	integer	not null	`0`	—
`urgency`	integer	null	—	—
`interest`	integer	null	—	—
`energy`	integer	null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—
`updated_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

todo_tasks_user_idx on user_id
todo_tasks_scheduled_idx on user_id, scheduled_date
todo_tasks_notebook_idx on notebook_id

Checks — enforced by the database, not only by the service layer:

todos_urgency_range: "todo_tasks"."urgency" IS NULL OR "todo_tasks"."urgency" BETWEEN 1 AND 5
todos_interest_range: "todo_tasks"."interest" IS NULL OR "todo_tasks"."interest" BETWEEN 1 AND 5
todos_energy_range: "todo_tasks"."energy" IS NULL OR "todo_tasks"."energy" BETWEEN 1 AND 5

user

Column	Type	Null	Default	Notes
`id`	text	not null	—	primary key
`name`	text	not null	—	—
`email`	text	not null	—	—
`email_verified`	integer	not null	`false`	—
`image`	text	null	—	—
`role`	text	not null	`'member'`	—
`banned`	integer	null	`false`	—
`ban_reason`	text	null	—	—
`ban_expires`	integer	null	—	—
`created_at`	integer	not null	`(cast(unixepoch('subsecond') * 1000 as integer))`	—
`updated_at`	integer	not null	`(cast(unixepoch('subsecond') * 1000 as integer))`	—

Indexes:

user_email_unique on email — unique

user_settings

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`key`	text	not null	—	—
`value`	text	not null	—	—

Indexes:

user_settings_user_key_unique on user_id, key — unique
user_settings_user_idx on user_id

verification

Column	Type	Null	Default	Notes
`id`	text	not null	—	primary key
`identifier`	text	not null	—	—
`value`	text	not null	—	—
`expires_at`	integer	not null	—	—
`created_at`	integer	not null	`(cast(unixepoch('subsecond') * 1000 as integer))`	—
`updated_at`	integer	not null	`(cast(unixepoch('subsecond') * 1000 as integer))`	—

Indexes:

verification_identifier_idx on identifier

webhook_subscriptions

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`url`	text	not null	—	—
`events`	text	not null	—	—
`secret`	text	not null	—	—
`last_delivery_at`	text	null	—	—
`last_status`	integer	null	—	—
`fail_count`	integer	not null	`0`	—
`disabled_at`	text	null	—	—
`created_at`	text	not null	—	—
`updated_at`	text	not null	—	—

Indexes:

webhook_subscriptions_user_idx on user_id

weekly_reviews

Column	Type	Null	Default	Notes
`id`	integer	not null	—	primary key, auto
`user_id`	text	not null	—	→ `user.id`
`week_start`	text	not null	—	—
`position`	integer	not null	—	—
`content`	text	not null	—	—
`created_at`	text	not null	`(CURRENT_TIMESTAMP)`	—
`updated_at`	text	not null	`(CURRENT_TIMESTAMP)`	—

Indexes:

weekly_reviews_user_week_idx on user_id, week_start
weekly_reviews_slot_unique on user_id, week_start, position — unique