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.

Event-sourced lifecycle table (immutable history)

Model each state change as an append-only event (manufactured, installed, removed, scrapped). This preserves a complete audit trail and avoids lossy “current state” overwrites; current state becomes a derived view.

SELECT part_id, event_type, event_ts
FROM part_events
WHERE part_id = 12345
ORDER BY event_ts;

Temporal modeling for installations (valid_from/valid_to)

Represent installation intervals with start/end timestamps (or start + open-ended NULL end). This enables “as-of” queries and supports constraints to prevent overlapping intervals for the same part or the same slot position.

SELECT *
FROM part_installations
WHERE engine_id = 9001
  AND TIMESTAMP '2026-01-15 10:00:00' >= installed_at
  AND (removed_at IS NULL OR TIMESTAMP '2026-01-15 10:00:00' < removed_at);

Hierarchical BOM / assembly graph with recursive CTEs

Rocket engines are nested assemblies. A parent-child table (assembly_components) supports traversing from engine to sub-assemblies to leaf parts; recursive CTEs reconstruct the full lineage path for audits and impact analysis.

WITH RECURSIVE bom AS (
  SELECT parent_serial, child_serial, 1 AS depth
  FROM assembly_components
  WHERE parent_serial = 'ENG-9001'
  UNION ALL
  SELECT ac.parent_serial, ac.child_serial, bom.depth + 1
  FROM assembly_components ac
  JOIN bom ON ac.parent_serial = bom.child_serial
)
SELECT * FROM bom;

Window functions to derive latest state

When you store immutable events, you often need the latest event per part (or per part+engine). ROW_NUMBER() or LAST_VALUE() can produce current status without mutating base tables.

SELECT part_id, event_type, event_ts
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY part_id ORDER BY event_ts DESC) AS rn
  FROM part_events
) x
WHERE rn = 1;

Constraints + indexes for integrity and performance

Foreign keys enforce lineage correctness; unique constraints prevent duplicate serials; partial indexes speed up “currently installed” queries (removed_at IS NULL). For very large event tables, partition by time and cluster by part_id/engine_id.

CREATE INDEX idx_install_open
ON part_installations(engine_id, installed_at)
WHERE removed_at IS NULL;