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.
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.