Domain Model & Data Schema

The domain follows the Product Brief’s definitions (§5.1) exactly; the tables below are the Drizzle definitions in src/db/schema.ts.

Entities

User — a registered person, identified by email and name (users).
List — a container for Tasks, owned by exactly one User; can be shared with Members and placed in at most one Group (lists).
Task — an entry in a List with a title and attributes (done, starred, due date, notes, assignee); can be assigned to one Member of its List (tasks).
Step — a child of a Task with only a title and done state; cannot be starred, assigned, shared, dated, or nested (steps).
Group — a container that organizes related Lists; cannot nest (groups).
View — a system-defined, read-only collection of Tasks (“My Day”, “Important”, “Planned”, “Assigned to me”, “Tasks”); aggregates across owned and member Lists and respects the authorization boundary.
Member — a User a List has been shared with (listMembers); a Member is always a User.
Tag — a user’s private label (tags); lowercase [a-z0-9-], ≤32 chars, unique per owner. Attached to Tasks via a per-user join (task_tags). On a shared Task each Member keeps their own tag set — another member’s tags are invisible, and an assignee sees the Task untagged (spec 036).
Attachment — a file attached to a Task (attachments); task-scoped and shared with every Member of the Task (the privacy inverse of Tags). The bytes live in Cloudflare R2; this row is the metadata source of truth. The uploader is recorded, and delete is restricted to the uploader or the Task owner (spec 037).

Entity relationships

The crow’s-foot diagram below covers the domain tables in src/db/schema.ts. list_members, my_day_entries, and task_tags are association tables (composite primary keys); assignee_id on tasks is an optional self-link to a User who is a Member of the Task’s List. The three operational tables — push_subscriptions and notification_preferences (both keyed by user_id, spec 029) and idempotency_keys (outbox replay dedup) — carry no domain relationships and are omitted here; see the schema table below.

erDiagram
  users ||--o{ lists : owns
  users ||--o{ groups : owns
  groups ||--o{ lists : "groups (optional)"
  lists ||--o{ tasks : contains
  tasks ||--o{ steps : has
  lists ||--o{ list_members : "shared via"
  users ||--o{ list_members : "member of"
  users ||--o{ my_day_entries : focuses
  tasks ||--o{ my_day_entries : "appears in"
  users |o--o{ tasks : "assigned (assignee_id)"
  users ||--o{ tags : owns
  tags ||--o{ task_tags : labels
  tasks ||--o{ task_tags : "tagged (per-user)"
  tasks ||--o{ attachments : has
  users ||--o{ attachments : uploaded

  users {
    text id PK
    text clerk_user_id UK
    text email UK
    text display_name
    boolean auto_delete_completed
  }
  lists {
    text id PK
    text owner_id FK
    text title
    text group_id FK "nullable"
    text rank
    text deleted_at "tombstone"
  }
  groups {
    text id PK
    text owner_id FK
    text title
    text rank
  }
  tasks {
    text id PK
    text list_id FK
    text title
    boolean done
    boolean starred
    text due_date "YYYY-MM-DD"
    text reminder_at "ISO-8601 UTC"
    text recurrence "daily|weekly|weekdays|monthly|yearly"
    text assignee_id "nullable"
    text completed_at "ISO-8601 UTC, nullable"
    text rank
  }
  steps {
    text id PK
    text task_id FK
    text title
    boolean done
    text rank
  }
  list_members {
    text list_id PK
    text user_id PK
    text rank "member's own placement"
    text group_id "member's own group, nullable"
  }
  my_day_entries {
    text user_id PK
    text task_id PK
    text added_on "device-local YYYY-MM-DD"
  }
  tags {
    text id PK
    text owner_id
    text name "lowercase [a-z0-9-], <=32"
    text deleted_at "tombstone"
  }
  task_tags {
    text task_id PK
    text tag_id PK
    text owner_id "denormalized (tagger)"
    text deleted_at "tombstone"
  }
  attachments {
    text id PK
    text task_id FK
    text owner_id "task owner"
    text uploader_id
    text filename
    text content_type
    integer size_bytes
    text storage_key "R2 object key"
    text deleted_at "tombstone"
  }

Note: tasks and steps declare an ON DELETE CASCADE foreign key (defense-in-depth); groups → lists and the association tables have no declared FK — integrity is enforced by the application’s cascade/dissolve transactions (src/db/schema.ts comments). Soft-delete (deleted_at tombstones) is what actually drives the everyday delete + sync path.

Key domain rules

A List is owned by exactly one User; only the owner changes memberships; deleting a List permanently deletes its Tasks and Steps (Product Brief §5.2).
A Task belongs to one List; deleting a Task deletes its Steps; a Task may be assigned to exactly one Member (assigning to the owner is allowed; removing the assignee clears the assignment).
Steps have only title + done; no nesting.
A Group holds zero or more Lists and cannot nest; deleting a Group ungroups its Lists rather than deleting them.
Ordering is custom per scope: per-List for Tasks, per-Task for Steps, per-User for Lists and Groups; sorting is a temporary view, drag-and-drop sets the persisted order.
The server is the single source of truth; concurrent edits to the same field reconcile last-write-wins at the field level (Product Brief §5.2–§5.3).

Task lifecycle

A Task’s primary lifecycle is Active ↔ Done → Deleted; deletion is a soft tombstone (deleted_at) so the change can propagate to every device. Attribute edits (star, due date, reminder, assignee, notes) are independent field-level updates that do not change the lifecycle state. Completing a recurring Task additionally spawns the next occurrence (deterministic child id).

Completing a Task stamps completed_at (server-set on the not-done→done transition; cleared on reopen). A per-user setting auto_delete_completed (default on) lets a daily background job soft-delete Tasks completed more than 30 days ago — Tasks only; their Steps are removed via the normal Task cascade, never on their own. Users can turn the setting off, or run an immediate “remove all completed” cleanup of their owned Tasks, in Preferences → Account (spec 038). Pre-existing completed Tasks (whose completed_at is NULL) are never swept by the job — only the manual cleanup removes them. See Operations.

stateDiagram-v2
  [*] --> Active: create
  Active --> Done: complete
  Done --> Active: reopen
  Active --> Active: edit attributes (field-level LWW)
  Done --> Active: complete recurring → spawn next occurrence
  Active --> Deleted: delete (tombstone)
  Done --> Deleted: delete (tombstone)
  Deleted --> [*]

Data schema (D1, Drizzle)

The tables defined in src/db/schema.ts:

Table	Represents	Notable columns / relationships
`users`	Registered users	identity (email, name); `autoDeleteCompleted` (per-user, default on — spec 038)
`lists`	Lists	`ownerId`, optional `groupId`, per-owner `rank`
`groups`	Groups of Lists	`rank`; Lists reference via `groupId`
`tasks`	Tasks	`listId`, `title`, `done`, `starred`, `dueDate`, `notes`, `assigneeId`, `reminderAt`, `recurrence`, `reminderNotifiedAt`, `completedAt`, `rank`
`steps`	Steps	`taskId`, `title`, `done`, `rank`
`myDayEntries`	”My Day” view membership	per-user daily-focus entries (reset each local day)
`listMembers`	List sharing	`(listId, userId)`; per-viewer order/group for shared Lists
`pushSubscriptions`	Web Push subscriptions	one row per device: `userId`, unique `endpoint`, `p256dh`/`auth` keys, `failureCount` (spec 029)
`notificationPreferences`	Per-user notification opt-ins	singleton keyed by `userId`: `reminders`, `assignments` (defaults-on; spec 029)
`idempotencyKeys`	Replay dedup	stable key per queued mutation for safe outbox replay
`tags`	Per-user private labels	`ownerId`, `name` (lowercase `[a-z0-9-]`, ≤32); partial-unique `(ownerId, name)` among live rows (spec 036)
`task_tags`	Task↔Tag attachment	`(taskId, tagId)`; denormalized `ownerId` for the owner-scoped delta (spec 036)
`attachments`	Task file attachments	`taskId`, `ownerId` (Task owner), `uploaderId`, `filename`, `contentType`, `sizeBytes`, opaque `storageKey` (R2), soft-delete tombstone (spec 037)

Per-member List placement (per-viewer order + group on listMembers) and assignment (assigneeId) come from list-sharing and task-assignment (spec 011, spec 012). Reminders + recurrence (reminderAt, recurrence, plus the server-internal delivery-dedupe marker reminderNotifiedAt) come from spec 026 / 029. pushSubscriptions + notificationPreferences back closed-app Web Push (spec 029); both are purged on account deletion (§9.7). tags + task_tags back per-user private tags (spec 036): both are owner-scoped, so a member’s tags on a shared Task are invisible to others and are cascaded on tag/task/account deletion. attachments holds the metadata for task file attachments (spec 037) — bytes live in a private R2 bucket keyed by the opaque storageKey; rows + their R2 objects are removed on task delete and account purge (no orphan bytes). tasks.completedAt + users.autoDeleteCompleted back the 30-day auto-deletion of completed Tasks (spec 038): completedAt is server-set on the done transition and is never projected into the client ?since delta (operational metadata only); autoDeleteCompleted is read server-side by the daily cleanup cron. The rank columns implement the fractional ordering described in the Fractional Rank Algorithm module guide.