Automated Time Tracking

65 min

0) Executive Summary

We are building the industry-leading, passive, automated time tracking platform for MSP operations. The system captures technician time automatically, links it to the right work artifacts (tickets, outages, projects, docs, training, RMM sessions), pre-aggregates entries, and presents a single daily review for confirmation. It eliminates manual timers, increases capture rate, improves SLIQ/TruMethods reporting accuracy, and reduces billing leakage—while respecting privacy and minimizing workflow friction.

Core principles

Passive first: Capture from agent, RMM, chat, calendar, docs, automation, and training. The default is “already captured.”
Explainable: Every auto-link has a rule, confidence, and human-readable reason.
Noise-resistant: Sessionization, sticky gaps, and overlap policies yield meaningful blocks.
Policy-driven: Rounding, minimums, billable defaults per org.
Review once/day: One place to confirm, edit, or resolve unlinked gaps.
Privacy by design: Idle detection, opt-in screenshots, redaction, and role-based access.
Metrics-complete: All SLIQ/TruMethods categories and formulas supported out of the box.

1) Scope

In scope

Agent for foreground activity (Windows/macOS initially) with URL/title/process capture.
Ingestion APIs, Link Engine, Aggregation service, Overlap resolution, Policy engine.
Source adapters: RMM remote sessions, Chat (ticket channels), Calendar/Schedule, Docs/Knowledge, Cybersecurity training, Workflow automation halos.
UI/UX: Technician Daily Review, Unlinked Activity, Rule Manager + Tester, Policy settings, Overlap Review, Source Integrations, Admin Analytics.
Reports: Utilization, Effective Rate, Capture Rate, SLIQ/TruMethods distribution, Outage costing.

Out of scope (v1)

Mobile agents (capture via mobile browser/app); can be v1.1+.
AI auto-summarization of notes; optional backlog item.

2) Personas & Goals

Technician: Never start/stop timers. Confirm a daily sheet in < 2 minutes. Resolve the rare “unlinked” items with suggestions.
Incident Commander / Service Desk Lead: Reliable, non-overlapping time blocks for incidents and queues; visibility into progress.
Finance/Billing: Accurate, policy-compliant entries for invoicing and agreements; fewer adjustments.
vCIO/Account Manager: Clear proactive vs. reactive effort; proof of value; QBR-ready reports.
Security/CISO: Auditable trails with privacy controls and least privilege access.

Primary outcomes

95% capture rate (captured hours ÷ expected hours).
<5% unlinked minutes per tech per day.
<2 min median confirmation time per tech per day.
±1% billing variance vs. manual baselines.

3) Definitions & Entities

Activity (user_activity_log): Foreground window interval with title/URL/process (+ idle/input metrics).
Source Fact: RMM session, chat activity window, calendar slot, doc reading session, training session, workflow halo.
Link: Mapping of activity to an entity (ticket/outage/etc.) with explanation (rule, confidence, reason).
Cluster: Merged contiguous activities (≤ sticky gap) with same user + link → one time entry.
Overlap Group: Set of entries whose times intersect; resolved by deterministic policy.
Policy: Org-level rounding, min block, sticky gap, defaults for admin/training billable.

(See DDL canvas for table definitions.)

4) High-Level Architecture

[Agent]──batched activity──> [Ingestion API]──> user_activity_log │ ├─> [Source adapters] (RMM/Chat/Calendar/Docs/Training/Automation) → activity facts │ ├─> [Linking Engine] │ ├─ rules (precedence/confidence) │ └─ explanations (audit trail) │ ├─> [Sessionizer/Aggregator] │ └─ clusters → time_entry (policy: min/round/sticky) │ ├─> [Overlap Resolver] │ └─ primary/secondary/split + audit │ └─> [Rating/Billing] (downstream) [Web App] ├─ Technician Daily Review (confirm/edit/delete) ├─ Unlinked Activity (suggestions) ├─ Rule Manager + Tester ├─ Policy Settings (per org) ├─ Overlap Review └─ Analytics (SLIQ/TruMethods, Utilization, Capture Rate)

5) Data Model (Reference)

Use the DDL canvas for authoritative schemas of:

Updated:user_activity_log, time_entry
New:activity_linking_rule, activity_link_explanation, time_entry_policy, document_interaction_event

Related existing tables referenced: device, user, organization, ticket, outage (master/child), project_task, technician_schedule, chat_channel, agreement, work_type, invoice_line_item, location, time_entry_source, document_version.

6) Source Adapters (Capture)

6.1 Agent (Windows/macOS)

Responsibilities

Foreground detection; window title, process, and URL (browser extensions or accessibility APIs).
Idle detection: input event counters + OS idle time.
Batch completed intervals every 60–90s with backpressure and offline queue.
Respect privacy policies (no content keystrokes, optional screenshots via separate, opt-in mechanism).

Payload (per interval)

{ "device_id": "uuid", "user_id": "uuid", "user_session_identifier": "string", "application_name": "Google Chrome", "process_name": "chrome.exe", "window_title": "Ticket TCK-12345 – Customer X", "url": "https://app/tickets/1e36…", "start_time_utc": "2025-08-19T18:12:00Z", "end_time_utc": "2025-08-19T18:14:40Z", "duration_seconds": 160, "input_events": 97, "is_idle": false }

Reliability

Local WAL (write-ahead log) with rotating file cap.
Exponential backoff on HTTP errors; retry queue persists across reboots.
Idempotency-Key header = hash(device_id + start_time_utc + end_time_utc + process_name + window_title).

6.2 RMM Remote Sessions

Hook into remote_control_session events (join/leave, tool, device, user).
Emit source facts to ingestion with a time_entry_source of RemoteControl.

6.3 Chat (Ticket Channels)

For messages sent by a tech in a ticket channel, create activity windows: first-to-last message +/- 2min grace.

6.4 Calendar / Dispatch

technician_schedule slots create soft claims; used by linker and to generate fallback entries when no foreground activity exists (onsite/phone).

6.5 Docs/Knowledge

Web app emits document_interaction_event on viewer open/close; may include ?ticketId context.

6.6 Cybersecurity Training

Track session start/end on training content; associate with remedial_training_assignment/org.

6.7 Workflow Automation Halos

For human-in-the-loop automation steps, create short “halo” intervals (e.g., 60–120s) to represent supervision/validation.

7) Ingestion API (CRUD & Contracts)

7.1 Endpoints

POST /v1/activity — batch write of agent intervals.
POST /v1/source-facts — generic facts (RMM, Chat, Calendar, Docs, Training, Automation). Each type has a source_type field.
POST /v1/docs/interactions — document interaction events (optional separate endpoint).

Common behaviors

Validate timestamps (end ≥ start), min/max duration (e.g., drop < 3s unless from automation).
Normalize users/devices (reject unknown unless allowlist enabled).
Upsert by Idempotency-Key; never duplicate.
Store to user_activity_log (agent, chat-derived, docs) or to a side table → but for v1 we normalize all sources into user_activity_log with application_name + process_name set contextually (e.g., “Remote Control Session”).

7.2 DB Write Patterns (pseudocode)

-- Upsert activity interval INSERT INTO user_activity_log (...) ON CONFLICT (/* synthetic unique key if provided */) DO NOTHING;

8) Linking Engine

Goal: Assign linked_entity_type/id to each unlinked user_activity_log and log why.

8.1 Rule Evaluation Order

URL (platform routes): /tickets/{uuid}, /outages/{uuid}, /projects/{uuid}
Chat → Ticket: resolve channel to ticket
Device → Ticket: nearest active ticket involving this device/user
Calendar slot context
WindowTitle patterns (TCK-\d+, INC\d+)
Process heuristics (RMM tool names → device/ticket)

8.2 Algorithm (pseudocode)

for each activity where linked_entity_id is NULL: candidates = [] for rule in rules ordered by (active desc, scope, precedence asc, confidence desc): if rule.matches(activity): entity_id = resolve(rule.entity_lookup, activity) if entity_id: candidates.add({rule, entity_id, rule.confidence, reason}) choose = best(candidates) if confidence >= threshold else NULL if choose: update activity.linked_entity_type/id insert activity_link_explanation for each candidate; mark chosen=true for the winner

Thresholds

Default acceptance threshold: 70. Below → remains unlinked (shown in Review).

Examples

URL: /tickets/98f8… → type ticket, id = extracted.
Window title: TCK-12345 → select latest open ticket with that key.
Device: activity on Device A while Ticket B is “In Progress” with Device A → link to B.

9) Sessionization & Aggregation

Goal: Convert linked activities into meaningful time_entry blocks.

9.1 Clustering

Group by (user_id, linked_entity_type, linked_entity_id) and merge intervals with gaps ≤ sticky_gap_seconds (policy per org).
Propagate source (Agent/Remote/Chat/Calendar/Docs/Training/Automation) from the dominant share of intervals; if mixed, prefer higher precedence source per overlap policy.

9.2 Policy Application

Apply min_block_seconds (floor) and round_to_seconds (nearest up/down per org policy; default: round to nearest, tie → up).
If context tagged Admin or Training, set billable based on policy defaults (overridable by work_type/agreements).

9.3 Emission

Create one time_entry per cluster with start/end/duration.
Mark all clustered user_activity_log rows as processed_for_time_entry = true and set a generated activity_cluster_id on those rows.

10) Overlap Resolution

Goal: Prevent double-counting/billing.

10.1 Priority Order

Calendar/Meeting (onsite/dispatch)
Remote Control
URL-linked desktop activity
Chat activity
Automation halos
Docs/Training (lowest by default)

10.2 Policy

If two entries overlap: keep the higher priority as Primary. Lower becomes Secondary (non-billable by default) or Split (ratio, e.g., 70/30) if org enables splitting.
Persist overlap_id, overlap_resolution, and optional overlap_primary_time_entry_id.

10.3 UI

Overlap Review screen (rare use) shows groups; allows override to Split/Secondary.

11) SLIQ / TruMethods Mapping & Metrics

Work Type Taxonomy (examples)

Reactive – Remote Support (tickets)
Reactive – Incident Management (child outages → client; master outage → MSP internal)
Proactive – Maintenance/Automation
Project – Delivery
Administration (non-billable default)
Training (billable=false default)
Travel (separate flag)

Key Metrics & Formulas

Utilization % = Billable Hours ÷ Available Hours (per role policy) × 100
Capture Rate % = Captured Hours ÷ Expected Hours × 100
Reactive % / Proactive % / Project % / Admin % / Training % = Hours in class ÷ Total Hours
Effective Rate = Billable Revenue ÷ Billable Hours
Auto-Link Rate % = Auto-linked Minutes ÷ Total Captured Minutes
Unlinked Minutes (median, p95)
Confirmation Time (median per tech/day)

Reports and dashboards will expose these with filters by org, customer, team, tech, and date.

12) User Experience (Screens)

12.1 Technician – Daily Review

Header KPIs: Captured, Confirmed, Unlinked, Pending; soft goal meter (e.g., 7.5h target).
Timeline View: Chronological blocks colored by source; icons for confidence, overlap, policy rounding.
Per Entry Actions: Confirm ✓, Edit (duration/note/work_type/billable), Reassign (ticket/outage/project), Delete (marks as non-work). Undo.
Unlinked Bucket: Suggested targets ranked by confidence (URL/title/device/chat/calendar hints). One-click assign.
Nudges: “You still have 12 minutes unlinked; auto-assign to Admin?”

12.2 Rule Manager

Rule list with scope/precedence/confidence, enabled toggle.
Editor with pattern tester: paste example title/URL; see extracted entity and confidence.
Audit view: recent link explanations referencing this rule.

12.3 Policy Settings (per org)

Min block, Rounding increment, Sticky gap.
Defaults for Admin/Training billable.
Overlap handling (Secondary vs. Split ratio).

12.4 Overlap Review

Grouped by overlap_id; shows Primary/Secondary/Split; allow override.

12.5 Source Integrations

RMM tools, Chat workspace, Calendar (M365/Google), Docs, Training platform; connection status and mapping helpers.

12.6 Admin Analytics

SLIQ/TruMethods, Utilization, Capture Rate, Effective Rate; filters and exports.

12.7 Privacy & Audit

Screenshot controls (off by default); redaction; who can view; retention policy.
Access logs for time data.

13) API Surface (Selected)

Activity

POST /v1/activity (batch)
GET /v1/activity?user_id=…&from=…&to=… (admin)

Linking

POST /v1/linking/run (admin – backfill window)
GET /v1/linking/explanations?activity_id=…

Time Entries

GET /v1/time-entries?user_id&from&to
POST /v1/time-entries/{id}/confirm
PATCH /v1/time-entries/{id} (edit)
DELETE /v1/time-entries/{id}

Rules

GET /v1/linking/rules
POST /v1/linking/rules
PATCH /v1/linking/rules/{id}
POST /v1/linking/rules/test (pattern tester)

Policy

GET /v1/time-entry-policies?organization_id
POST /v1/time-entry-policies
PATCH /v1/time-entry-policies/{id}

Docs

POST /v1/docs/interactions

All endpoints require auth (JWT) + org scoping; idempotency for writes.

14) Services & Jobs

Linking Engine Worker: Streams new user_activity_log with linked_entity_id IS NULL; writes explanations and links.
Aggregator: Periodic (e.g., every 10–15 min) + on-demand; clusters by policy; writes time_entry rows.
Overlap Resolver: Runs after Aggregator; applies priority rules; sets overlap_* fields.
Backfill: Specify date windows to re-run link+aggregate after rule/policy changes.
Retention/Archival: Rotate raw activity after N days (configurable); retain time entries and explanations indefinitely.

Runtime

Queue-backed (e.g., RabbitMQ/SQS) with at-least-once delivery.
Idempotent processors (use activity ids and cluster keys).

15) Billing & Agreements

work_type + agreement determine billable coverage and rates.
Master outage → internal “Incident Management” ticket (MSP org) for costing; child outage → client ticket.
calculated_rate/bill_amount set by downstream rating engine; this system provides pristine time blocks.

16) Security & Privacy

RBAC: Only managers/billing can view others’ detailed time; screenshots (if enabled) require elevated permission.
PII/Data Minimization: No keystroke content; only counts. No page content; only URL/title/process.
Encryption: TLS in transit; row-level at rest for sensitive sources.
Compliance: Audit trail via activity_link_explanation; access logs for who viewed/edited time.
Redaction: Regex allow-list for URLs; domains blacklist; hash query strings.

17) Observability

Metrics: capture rate, auto-link rate, backlog size, job latencies, confirmation median, % overlaps.
Logs: ingestion errors, rule exceptions, policy application results.
Tracing: request → rule eval → aggregation → overlap → UI confirm.

18) Performance Targets

Ingestion: sustain 50 events/sec/agent burst, 5k agents system-wide (batching).
Linking: < 50ms p95 per activity.
Aggregation: complete a 24h window for 1k users in < 5 min.
UI: Daily Review loads < 2s p95 for a typical day (~50 blocks).

19) Edge Cases & Rules

Very long single windows (meetings): clamp to schedule end if idle for > X minutes.
Flapping windows: merge micro-intervals (< 5s) into adjacent.
Missing end times (crash/reboot): infer end using next start or heartbeat cutoff.
Multi-customer contexts open: prefer explicit URL route over heuristic.
Device shared by two users: rely on login-bound user_session_identifier.

20) QA Strategy

Unit

Rule matchers, URL parsers, window-title extractors, policy rounding, cluster logic, overlap resolver.

Integration

Agent → ingestion → link → aggregate → overlap → UI confirm.

Property-based tests

Randomized intervals with sticky gaps, overlaps, and mixed sources.

E2E Scenarios

Reactive ticket day; outage master/child; onsite calendar block; doc research + remote session; training afternoon; admin tasks.

Performance Tests

Synthetic load: 5k agents, 24h.

21) Deployment & Operations

Feature flags: per-org enablement; per-source enablement; screenshots off by default.
Rollout plan: pilot on internal MSP org → 5 friendly customers → GA.
Backfill support: from date X to Y with safe windowing and progress UI.

22) Migration Plan

Deploy new tables (DDL canvas).
Backfill policy rows for all orgs with sensible defaults.
Populate seed rules for URL routes and common ticket patterns.
Turn on Linker in dry-run (explanations only) to validate match rates.
Enable Aggregator (policy min/round) for a pilot team.
Enable Overlap resolver and Review UI.
Expand to all technicians.

23) Risk Register & Mitigations

Over-linking (false positives): Confidence thresholds, explanations, and daily review to correct; rule tester before deploy.
Under-capture on macOS URLs: Browser extension fallback; process/title heuristics.
Privacy backlash: Opt-in screenshots, clear policies, restricted viewing, redaction.
Performance regressions: Queue isolation per component; horizontal scale; backpressure signalling to agents.

24) Open Questions

Split ratios by work type? (e.g., always favor customer-facing work.)
Automatic “auto-confirm” for high-confidence, policy-compliant entries? (opt-in per org)
SLA around time availability for near-real-time dashboards (e.g., 5–10 minutes?)

25) Appendices

A) Sample Linking Rules (seed)

scope	pattern	entity_type	precedence	confidence	notes
URL	/tickets/([a-f0-9-]{36})	ticket	10	98	Primary route matcher
URL	/outages/([a-f0-9-]{36})	outage	10	98	Outage route
WindowTitle	TCK-(\d{4,7})	ticket	40	85	Map to last-open ticket with that external key
Process	`screenconnect	splashtop	anydesk`	ticket	60
Calendar	*	ticket	70	70	Use schedule context when active

B) Sessionization Pseudocode

for each user, each entity: sort intervals by start current = first for next in intervals[1:]: if gap(current.end, next.start) <= sticky_gap: current.end = next.end else: emit cluster(current) current = next emit cluster(current)

C) Rounding Logic

rounded = roundToNearest(duration, round_to_seconds) if rounded < min_block_seconds: rounded = min_block_seconds

D) Overlap Resolution Pseudocode

groups = find_overlaps(time_entries) for g in groups: sort by priority desc primary = first for e in rest: if policy.split_enabled: split_ratio = policy.split_ratio e.billable = (e.billable and split_ratio.secondary > 0) e.overlap_resolution = 'Split' else: e.billable = false e.overlap_resolution = 'Secondary' e.overlap_primary_time_entry_id = primary.id

E) SLIQ/TruMethods Mapping Matrix (example)

Work Type	Category	Billable Default
Reactive – Remote Support	Reactive	true
Reactive – Incident Management (Child)	Reactive	true
Reactive – Incident Management (Master)	Internal/MSP	false
Proactive – Maintenance/Automation	Proactive	true
Project – Delivery	Project	true
Administration	Admin	false
Training	Training	false
Travel	Travel	true

26) Next Steps

Engineering review of PRD + DDL.
Create tickets: Agent tasks, Ingestion, Linker, Aggregator, Overlap, UI screens, Integrations.
Pilot feature flag plan and initial seed rules.