Katkat
Back

Design a Nearest-Timestamp Status Record Service

AxonSoftware Engineer · Technical Screen
HardSoftware EngineerTechnical ScreenSystem Design

Last updated

Design a high-throughput time-series ingestion and query service that handles out-of-order, duplicate officer telemetry and returns the record closest in time to a requested query timestamp.

Scenario

Public safety devices (body cameras, smart holsters, vehicle trackers) continuously emit operational status records (e.g., GPS coordinates, battery level, recording state, holster sensor status). Due to intermittent cellular connectivity and batch uploads over Wi-Fi, records often arrive out of order, delayed by hours or days, or duplicated across network retries.

You are tasked with designing a backend service that ingests these status records and allows forensic auditing applications to retrieve an officer's exact status at any historical incident timestamp.

Requirements

Functional Requirements

  1. Ingest Status Updates: Accept telemetry events containing officer_id, device_id, device_timestamp, and arbitrary status payloads.
  2. Nearest-Timestamp Query: Given officer_id and a target timestamp T_target, return the single retained record whose device_timestamp is closest to T_target. If two records are equidistant (|t1 - T_target| == |t2 - T_target|), break ties by returning the earlier record (min(t1, t2)).
  3. Deduplication: Silently drop or overwrite duplicate records emitted due to device or network retries.
  4. Rolling Retention: Automatically purge records older than a configured retention period (e.g., 90 days).

Non-Functional Requirements

  • Scale: 100,000 concurrently active officers generating one record every 5 seconds (20,000 writes/sec peak). Average record size is 500 bytes.
  • Low Read Latency: p99 read latency < 50 ms for nearest-neighbor lookups.
  • Fault Tolerance & Consistency: Ingested data must be durably stored. Late-arriving data (arriving up to 7 days late) must be correctly incorporated into query results without corrupting historical ordering.

Key Design Deliverables

  1. API Design: Define the ingestion endpoint and the query interface.
  2. Data Storage & Indexing: Choose and justify the underlying database engine, primary key structure, and partition/clustering keys.
  3. Nearest-Record Resolution: Explain the exact query strategy used to retrieve the nearest neighbor (e.g., floor and ceiling searches) efficiently.
  4. Handling Out-of-Order & Late Data: Address how out-of-order arrivals affect partitioning and write paths.
  5. Clock Skew & Edge Cases: Address device clock drift relative to true time and deduplication strategies.

Hints

Hint 1

To resolve the nearest record efficiently without scanning large ranges, execute two targeted queries: the greatest record with a timestamp = target.

Hint 2

Derive a deterministic event ID (such as a hash of officer_id, device_id, and device_timestamp) to enable idempotent writes and deduplication at the storage layer.

Hint 3

Separate storage by partitioning on officer_id and a coarse time bucket (e.g., monthly) to keep indexes balanced while facilitating rolling retention drops.

Follow-up questions

  • How would you detect and handle a device whose internal clock was skewed by several hours or reset to the Unix epoch?
  • How does the partition and indexing strategy change if you must query nearest status for an entire unit or team rather than a single officer?
  • How would you implement a tiered storage strategy that archives data older than 90 days to object storage (e.g., Parquet on S3) while maintaining the ability to run occasional cold queries?