Core Entities
User: user_id, name, email, role (EMPLOYEE, MANAGER, ADMIN), hourly_rate (default), timezone, created_at. Project: project_id, name, client_id, status (ACTIVE, COMPLETED, ARCHIVED), billing_type (HOURLY, FIXED, NON_BILLABLE), budget_hours, budget_amount, currency, created_at. Task: task_id, project_id, name, estimated_hours, is_billable (override project default). TimeEntry: entry_id, user_id, project_id, task_id (nullable), description, start_time (TIMESTAMPTZ), end_time (TIMESTAMPTZ, nullable for running timers), duration_seconds (computed on stop), is_billable, hourly_rate (snapshot at time of entry, for historical accuracy), tags (text array), created_at, updated_at. Invoice: invoice_id, client_id, project_id, status (DRAFT, SENT, PAID, OVERDUE), total_amount, currency, invoice_date, due_date, line_items (JSONB). InvoiceLineItem: linked time entries, hours billed, rate, amount.
Timer Start/Stop with Overlap Prevention
class TimeTrackingService:
def start_timer(self, user_id: int, project_id: int,
task_id: int, description: str) -> TimeEntry:
# Enforce: only one running timer per user at a time
running = self.db.query_one(
"SELECT entry_id FROM time_entries "
"WHERE user_id = %s AND end_time IS NULL",
user_id
)
if running:
raise ActiveTimerExists(running.entry_id)
# Validate project access
if not self._user_has_project_access(user_id, project_id):
raise ProjectAccessDenied()
rate = self._resolve_rate(user_id, project_id)
entry = self.db.insert("time_entries", {
"user_id": user_id,
"project_id": project_id,
"task_id": task_id,
"description": description,
"start_time": datetime.utcnow(),
"end_time": None,
"hourly_rate": rate,
"is_billable": self._is_billable(project_id, task_id)
})
return entry
def stop_timer(self, user_id: int) -> TimeEntry:
entry = self.db.query_one(
"SELECT * FROM time_entries "
"WHERE user_id = %s AND end_time IS NULL FOR UPDATE",
user_id
)
if not entry:
raise NoActiveTimer()
end = datetime.utcnow()
duration = int((end - entry.start_time).total_seconds())
# Enforce minimum entry (discard < 1 minute as accidental)
if duration float:
# Priority: project-specific user rate > user default rate > project default rate
override = self.db.query_one(
"SELECT hourly_rate FROM project_user_rates "
"WHERE project_id=%s AND user_id=%s", project_id, user_id
)
if override:
return override.hourly_rate
user = self.db.get_user(user_id)
return user.hourly_rateManual Entry and Edit with Audit Log
def create_manual_entry(self, user_id: int, project_id: int,
start_time: datetime, end_time: datetime,
description: str) -> TimeEntry:
if end_time 86400:
raise EntryTooLong() # max 24h per entry
# Check for overlapping entries
overlap = self.db.query_one(
"SELECT entry_id FROM time_entries "
"WHERE user_id = %s AND end_time IS NOT NULL "
"AND start_time %s",
user_id, end_time, start_time
)
if overlap:
raise OverlappingEntry(overlap.entry_id)
entry = self.db.insert("time_entries", {
"user_id": user_id,
"project_id": project_id,
"start_time": start_time,
"end_time": end_time,
"duration_seconds": int((end_time - start_time).total_seconds()),
"hourly_rate": self._resolve_rate(user_id, project_id),
"is_billable": self._is_billable(project_id)
})
self._audit_log("CREATE", entry.entry_id, user_id, None, entry)
return entryReporting and Invoice Generation
Report types: (1) Timesheet report: all entries for a user/date range, grouped by day. SQL: SELECT DATE(start_time), SUM(duration_seconds), project_id FROM time_entries WHERE user_id=? AND start_time BETWEEN ? AND ? GROUP BY DATE(start_time), project_id. (2) Project summary: total hours per task, billable vs non-billable split, budget utilization. (3) Client invoice: aggregate billable entries by project, compute amount = hours * rate per entry (use snapshot rate, not current rate). Invoice generation steps: query all uninvoiced billable entries for a project in the billing period, group by task, compute subtotals, create Invoice record with status=DRAFT, create InvoiceLineItems linking back to time entries, mark entries as invoiced (invoice_id FK). Idempotency: entries can only be on one invoice (invoice_id NOT NULL constraint once invoiced). PDF generation: render the invoice as PDF via a template engine; store in S3 with invoice_id as key.
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How do you prevent overlapping time entries in a time tracking system?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Before creating a manual entry, query for any existing entries for the same user where start_time < new_end_time AND end_time > new_start_time (overlap condition). If found, return an OverlappingEntry error with the conflicting entry ID. For running timers, enforce a single-running-timer constraint: query for any entry with end_time IS NULL before starting a new timer. Use SELECT FOR UPDATE on the running timer check to prevent race conditions.”}},{“@type”:”Question”,”name”:”How should hourly rates be stored in a time tracking system for historical accuracy?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Store a rate snapshot on each time entry at the time of creation. Do not compute amount from the current user or project rate, because rates change over time. Rate resolution priority: (1) project-specific user rate override, (2) user default rate, (3) project default rate. This ensures that invoices generated months later reflect the rate agreed upon at the time work was performed, not the current rate.”}},{“@type”:”Question”,”name”:”How do you generate an invoice from time entries?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Query all billable, uninvoiced time entries for a project in the billing period. Group by task to create line items. For each entry, compute amount = duration_seconds / 3600 * hourly_rate (use the snapshot rate on the entry). Create an Invoice record in DRAFT status with a JSONB line_items array. Mark entries as invoiced by setting their invoice_id FK. This is idempotent: once an entry has invoice_id set, it cannot be double-invoiced due to a NOT NULL constraint check.”}},{“@type”:”Question”,”name”:”What is the data model for a time entry that supports both timer and manual entry modes?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”A single TimeEntry table handles both modes. Timer mode: end_time is NULL while running, set on stop. Manual entry mode: both start_time and end_time are provided on creation. The duration_seconds column is computed on stop (timer) or on creation (manual) and stored for fast reporting queries without recomputing. A CHECK constraint enforces end_time > start_time when end_time is not NULL.”}},{“@type”:”Question”,”name”:”How do you handle time zones in a time tracking system?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Store all timestamps as TIMESTAMPTZ (timestamp with time zone) in UTC in the database. Store each user's preferred timezone. For display, convert UTC timestamps to the user's timezone in the application layer. For daily grouping in reports, convert to user timezone before truncating to date: DATE(start_time AT TIME ZONE user_timezone). Never store local times without timezone information, as daylight saving time transitions can cause gaps and overlaps.”}}]}
Atlassian products include time tracking features in Jira. See system design questions asked at Atlassian interview: time tracking and Jira-style LLD.
Stripe interviews cover billing and invoicing system design. Review design patterns for Stripe interview: invoicing and billing system design.
Databricks interviews test analytics and reporting system design. See common patterns for Databricks interview: analytics and reporting system design.