Data model
One PostgreSQL database, managed by Drizzle ORM. Migrations live in server/db/migrations and apply automatically on startup.
Entities
| Table | Columns (essentials) |
|---|---|
users | id, name, email (unique), password_hash (scrypt, salt:hex), default_rate ($/h), theme (jsonb), session_version (int, revocation stamp) |
orgs | id, name |
org_members | org_id + user_id (PK), role (owner | admin | member), rate (per-member override) |
clients | id, org_id, name, rate?, deleted_at?, delete_batch_id? |
projects | id, org_id, name, client_id?, rate?, billable_default, estimate_minutes?, visibility (private | public), archived, deleted_at?, delete_batch_id? |
tasks | id, org_id, name, project_id?, rate?, estimate_minutes?, done, deleted_at?, delete_batch_id? |
tags | id, org_id, name (lowercase, unique per org), deleted_at? |
time_entries | id, org_id, user_id, name, ref_type (client | project | task | null), ref_id?, billable, rate_override?, start, end?, deleted_at?, delete_batch_id? |
entry_tags | entry_id + tag_id (PK) |
delete_batches | id, org_id, relinked (jsonb), created_at — one cascade delete, so undo needs only this id |
invites | id, org_id, email, role (owner | admin | member), token (unique), expires_at, accepted_at? |
password_reset_tokens | id, user_id, token (unique), expires_at, used_at? |
delete_batch_id is how one cascade delete is undone whole: every row it trashes is stamped with the delete_batches id, and the batch row carries the relink snapshot for the rows it detached (those stay alive, so nothing on them records which cascade cleared their parent). The stamp is cleared whenever a row leaves the trash, so a later delete is never undone by an older batch. Batches are dropped with the rest of the trash after 30 days.
All timestamps are timestamptz. A task's rate applies whether the task belongs to a project or stands alone, and it overrides the project chain until cleared (Rule 2). Note what is otherwise absent: entries have no project_id/client_id columns — that's Rule 1.
role on org_members and invites, visibility on projects and ref_type on time_entries are text columns with CHECK constraints rather than enums, so the allowed values live in one place and migrate cheaply.
Sessions and revocation
Sessions are sealed cookies — there is no session table. users.session_version is what makes revocation possible: the version is stamped into the cookie at login, every authenticated request compares the stamp against the live column, and a password reset or change increments it. Cookies carrying an older stamp are cleared and answered 401 on their next request. A password change re-stamps the calling session so only other devices are logged out; a reset logs out everything. See Security.
password_reset_tokens rows are single-use and live one hour. Finishing a reset marks the used token and every other outstanding token for that user as used, so an older link in an inbox cannot be replayed.
Running timers
A timer is not a separate concept: it is a time_entries row with end IS NULL. A user may have up to 10 of them at once, and every timer route names one by its entry id.
Ten is a guardrail, not an invariant, so no index enforces it: a partial index (time_entries_user_running_idx on user_id, start where end is null and deleted_at is null) makes the running set cheap to read, and POST /api/timers counts and inserts inside one transaction holding pg_advisory_xact_lock(hashtext(user_id)), so simultaneous starts can't overshoot the cap. The eleventh start gets HTTP 409.
The rules
Rule 1 — deepest reference only. An entry stores one of client | project | task | nothing in ref_type/ref_id. Project and client are derived at read time (task → project → client), never copied. Re-parenting a task re-files its history.
Rule 2 — rate inheritance, first non-null wins.entry.rate_override → task.rate → project.rate → client.rate → org_member.rate → user.default_rate.
A task's rate applies whether the task belongs to a project or is standalone; clearing it falls back to the project chain, same as clients and projects today. billable defaults from project.billable_default when a project resolves, else true. amount = hours × rate, only when billable. Resolution happens server-side; DTOs carry resolvedRate, rateOverridden and (for projects and tasks) rateSource.
Rule 3 — cascade delete. Client/project deletion cascades downward via explicit checkboxes (Projects ⇒ Tasks ⇒ Entries). Unchecked survivors are detached, not deleted: projects lose client_id, tasks lose project_id, entries pointing at a deleted object get ref_type = null. Time is never lost silently.
Rule 4 — soft deletion. Every delete sets deleted_at and lands in the 30-day trash; hard-purge happens automatically after 30 days (on the next trash load) or explicitly via Delete forever. Deleting a tag only strips labels (entry_tags rows), never time. Every delete API returns a DeleteResult snapshot (deleted ids per entity + detached counts) that POST /api/restore can undo exactly; cascade deletes return a CascadeDeleteResult, which adds a relinked snapshot of the references they cleared and a batch_id. Undo a cascade by posting that one id — an id snapshot cannot describe a delete of tens of thousands of entries inside a 1MB request. The undo window itself is a per-browser preference — 8 seconds by default, 3–30 in Settings → Profile.
DTOs
API responses use shared DTO shapes (see shared/types/): EntryDto (with resolved ref chain, resolvedRate, durationSec, amount, tags), ClientDto / ProjectDto / TaskDto / TagDto (with usage counts and tracked totals), TimerState, DashboardSummary, ReportSummary, ImportPreview / ImportCommitResult, the settings DTOs (OrgInfoDto, OrgMemberDto, InviteDto, InviteLookupDto, TrashDto) and the mail DTOs (InviteCreateDto with its token + emailSent, ForgotResponseDto, ResetResponseDto). Clients never compute chains or rates — the server resolves everything.