Build an Ingestion Service and Timestamp-Indexed Query API for MCAP Driving Logs

You are asked to build a small data service over recorded vehicle drive logs, in an onsite exercise where an AI coding assistant may be used. Each recorded drive is a *run*. Runs are stored in MCAP, an open container format for timestamped, multi-topic log data. The content is driving data such as camera images or video and location information. The MCAP files define the message schemas, and each message payload is encoded with Protocol Buffers (Protobuf). Every message carries a timestamp. The logs were presented with this structure: ```text run_a camera message_1 message_2 location message_1 message_2 ``` The written specification is long and lists required and optional features, which were not reported in full. Two requirements are known: - list all runs; - query messages by timestamp, so the stored data needs an index on time. The language and storage are your choice; Python with SQLite fits the time available. Whether a UI is expected was not reported. At the end you present your work: the presentation centers on a demo, and you are asked how you worked with the AI assistant. ### Constraints and Clarifications - Message schemas travel with the data: the MCAP files define them. - Timestamps differ from message to message, including between topics. - The full feature list was not reported. Ask which required features matter most before building optional ones. ### Clarifying Questions - Is each run one MCAP file whose channels are `camera` and `location`, or a folder with separate files per topic, as the layout suggests? - Which timestamp should queries use: the time the message was logged, the time it was published (MCAP records both), or a timestamp inside the Protobuf payload? - Does a timestamp query mean a time range, an exact timestamp, or the message nearest to a given time? - How large are the runs, and do queries need the decoded payloads or only metadata? - Is a UI expected, or is an API or command-line demo enough? ### Part 1 — Ingest and store the runs Read every run, decode its messages, and store them so they can be queried. One option keeps message metadata (run, topic, timestamp) separate from the raw payloads; another puts every message, decoded, into one database. Choose, and justify the choice. ```hint Decide where the timestamps live Before splitting the data, decide which store holds the timestamps you query and what links a metadata row to its payload. That choice determines how hard keeping the two consistent becomes. ``` #### What This Part Should Cover - Reading MCAP: topics, schemas, Protobuf decoding, and which timestamp to keep - A data model for runs, topics and messages, and where large payloads such as camera images go - Single database versus split metadata and payload storage, including consistency - Ingestion that is fast and safe to re-run ### Part 2 — Listing runs and querying by timestamp Design and implement the API: list all runs, and retrieve messages by timestamp. Make the timestamp queries fast. ```hint Shape the index like the query Write down the exact filter and sort order of each timestamp query first; the index columns and their order follow from that. ``` #### Clarifying Questions for this Part - Should results include decoded payloads, raw bytes, or only metadata with a separate way to fetch payloads? - How many messages can one query return, and does the client need to page through them? #### What This Part Should Cover - Endpoints for listing runs and for timestamp queries, including paging of large results - Index design, and how to confirm the database actually uses the index - Timestamp units, precision and boundary semantics - Behavior for unknown runs or topics and for windows that contain no messages ### Part 3 — Building with an AI assistant and presenting You may use an AI coding assistant throughout, and the specification is too long to absorb quickly. Explain how you would use the assistant to explore the data and build the required features, how you make sure its output is correct, and how you would present the result: the demo, the tests and the documentation. Be ready for the question "How did you interact with the AI?" ```hint Delegate the typing, keep the judgment Separate what you would let the assistant generate from what you would decide or check yourself, and think about what evidence would show that the generated code works on this data. ``` #### What This Part Should Cover - Using the assistant to explore unfamiliar data and a long specification - Verification: tests, checks against known facts from the data, and review of generated code - Prioritizing required features over optional ones under time pressure - A clear demo and README, and a concrete account of how the work was divided between you and the assistant ### What a Strong Answer Covers - Asks which timestamp and which query shapes the API needs before choosing indexes - Timestamps stored as exact integers, with an index that matches the queries - A reasoned position on one store versus split metadata and payload storage - Working, tested code for the required features before any optional ones - A focused demo and an honest account of how AI assistance was used and checked ### Follow-up Questions - Camera frames make the database grow to many gigabytes per run. What changes in your storage design? - A user wants the location sample that matches each camera frame. How do you align topics recorded at different times and rates? - How would you make ingestion of a new run idempotent and safe to resume after a crash? - How would the design change if many engineers needed to query hundreds of runs at the same time?