Rocket Engine Part Traceability Schema

Context

You’re on the data platform team at a commercial space company building reusable rocket engines. Each engine contains thousands of serialized parts (turbopumps, valves, sensors), and regulators plus internal safety teams require end-to-end traceability: for any engine that flies, you must be able to reconstruct where every part came from, which sub-assemblies it was installed into, which tests it passed/failed, and when it was removed/reworked/scrapped. The system must support audits years later and handle high write volume (manufacturing events streaming in near real time) while still enabling analytics queries.

Core Question

How would you design a relational database schema to track the history of every part used in a rocket engine?

In your answer, cover:

1. Entities and relationships: parts, part revisions, suppliers/lots, engines, assemblies/sub-assemblies (BOM), and work orders.
2. Lifecycle history: how you model events like manufactured, received, inspected, installed, removed, reworked, scrapped, and test results.
3. Temporal correctness: how you represent “as-of” state (what was installed at a specific timestamp) and prevent overlapping installations.
4. Lineage queries: how you would query (a) all parts currently installed in an engine, (b) all engines impacted by a defective supplier lot, and (c) the full chain from a top-level engine down to leaf parts.
5. Scale/performance: indexing strategy, partitioning, and constraints to keep queries fast and data consistent.

Scope Guidance

Assume millions of event rows per month, multi-site manufacturing, and strict audit requirements (immutability and reproducibility). The interviewer expects a normalized design, clear reasoning about trade-offs (event-sourcing vs SCD Type 2), and concrete examples of tables/keys and the SQL patterns you’d use (joins, recursive CTEs, window functions) to answer traceability questions.