Parts Catalog

What is a parts compatibility catalog?

A parts compatibility catalog lets users find which parts fit their specific product (vehicle, appliance, or device model). The engineering challenge is not storing parts data; that part is easy.

It is the many-to-many compatibility relationship: a single part can fit thousands of vehicle configurations, and each vehicle can have thousands of compatible parts. Naive modeling creates hundreds of millions of fitment rows; attribute-based rules can collapse this to a smaller rule set while still answering exact compatibility queries within the stated latency target. The design therefore has to balance expressive rules, exact answers, and a read-heavy cache path.

TL;DR

Model compatibility as explicit relationships or, when the domain permits, attribute-based rules such as a make/model/year range with nullable “all variants” fields. Keep PostgreSQL as the authoritative catalog, materialize vehicle-to-part membership in Redis for reads, and use an in-process Bloom filter only to reject definite negative badge checks before an exact Redis membership test.

Catalog writes should commit a durable change and publish an outbox event. An invalidation worker resolves affected vehicle configurations, rebuilds or versions their cache entries, and reconciles missed events. The detailed sections retain the fitment schema, SQL predicates, REST APIs, cache calculations, Bloom-filter behavior, ACES/PIES import path, and alternative materialization strategies.

Scope and assumptions

The main path covers exact compatibility search, product-page fitment checks, saved vehicle context, catalog-manager updates, and cache invalidation. Inventory, price, checkout, and full-text search are separate systems. The part counts, fitment-row counts, DAU, latency targets, cache hit rates, memory estimates, and read/write ratios below are illustrative interview assumptions; validate them with the catalog's actual vehicle distribution, rule overlap, payload size, and load tests.

Functional Requirements

Core Requirements

1. Users can search for parts compatible with a specific vehicle (year, make, model, submodel).
2. Product pages show a "fits your vehicle" badge when a user's garage vehicle is set.
3. Catalog managers can add, update, and remove fitment data.
4. Results show only parts confirmed compatible with the exact vehicle configuration.

Below the Line (out of scope)

• Inventory management and stock levels
• Pricing and checkout
• Full-text search across part descriptions

The hardest part in scope: Fitment modeling. Explicitly storing one row per part-vehicle fitment pair for an automotive catalog produces hundreds of millions of rows. Attribute-based fitment rules collapse this dramatically while remaining queryable with a simple composite index lookup.

Inventory management is below the line because stock levels change independently of compatibility data. To add it: join the parts table with a separate inventory service and expose availability as a distinct API call. Keeping them decoupled prevents inventory writes from contending with the high-read compatibility path.

Pricing is below the line because it belongs to a separate pricing microservice with a part_id lookup. The catalog service stays read-only and does not need to understand promotional tiers or regional pricing rules.

Full-text search is below the line because it requires a dedicated search index. To add it: index part text fields into Elasticsearch, back the search service with the catalog database as source of truth, and run the two services independently. The catalog database is still the authoritative record; Elasticsearch is a derived read model.

Non-Functional Requirements

Core Requirements

• Scale: 10 million distinct parts, 300 million fitment relationships (or 5 million attribute-based fitment rules). 50 million DAU browsing products.
• Latency: Compatibility check returns under 100ms p99 end-to-end. Catalog updates reflected within 5 minutes.
• Availability: 99.9% uptime. Availability over consistency: a 5-minute lag on new fitment data is acceptable; returning wrong compatibility results is not.
• Read/write skew: Product page loads trigger compatibility checks far more than catalog managers add fitment data. The read path is the hot path.

Below the Line

• Sub-10ms compatibility checks (achievable with more aggressive caching but adds operational cost)
• Multi-region catalog replication

Read/write ratio: Every product page load triggers at least one compatibility check (read) for users with a saved garage vehicle. Catalog managers write fitment data in bulk imports, at most a few times per week. The read-to-write ratio is roughly 1,000:1. This makes the cache tier the most important component in the design, not the database. Almost every architectural decision in this article traces back to this number.

30-second answer / outline

1. Resolve the requested year/make/model/submodel to a canonical vehicle configuration and evaluate an attribute-based fitment rule or explicit relationship.
2. Keep PostgreSQL authoritative for parts, vehicles, rules, and imports; expose cursor-based compatibility search and a small synchronous fits-check API.
3. Materialize vehicle:{vehicle_id} Redis sets for compatible part IDs and use SISMEMBER for the badge path; hydrate part details separately.
4. Use a Bloom filter only as a negative pre-check, since a false positive is safe but a stale or incorrect filter must never reject a valid part.
5. Commit catalog changes through an outbox, invalidate/version affected vehicle sets, rebuild filters, and monitor cache generation, lag, false positives, and rule-evaluation cost.

5-minute explanation

The data model begins with parts, canonical vehicle configurations, and fitment rules. Explicit part-to-vehicle rows are simple but expand combinatorially. Attribute rules express ranges and wildcard dimensions compactly; their query must still enforce exact year, make, model, submodel, and engine semantics.

Compatibility search is a read-heavy path. A cache-aside Redis inverted index turns a vehicle lookup into a set read, while the fits endpoint uses a Bloom filter to avoid a network call for definite negatives and an exact SISMEMBER for possible matches. A cache miss evaluates the rules, populates the set, and returns part IDs for separate detail hydration.

On writes, the database transaction records the rule and an outbox event. An invalidation worker consumes that event, finds affected vehicles, and rebuilds or atomically swaps versioned cache entries. Missing or stale projections must be detectable and repairable; otherwise a fast cache can return the wrong compatibility answer. The deep dives compare explicit rows, JSONB, materialized views, Redis sets, and Bloom-filter memory trade-offs.

45-minute interview approach

This is a time-boxed interview plan, not a promise that the article should be read in 45 minutes.

• 0–5 minutes — Clarify the contract: Confirm product dimensions, wildcard semantics, exact versus advisory fitment, saved-vehicle behavior, update freshness, inventory separation, and whether full-text search is included.
• 5–10 minutes — Establish scale: Use the illustrative part/rule/DAU, 1,000:1 read-skew, latency, update-lag, and cache-size assumptions; ask how many vehicle configurations are hot.
• 10–15 minutes — Define entities and APIs: Walk through parts, vehicles, rules, garages, compatibility search, fits, admin writes, pagination, and authorization for catalog changes.
• 15–22 minutes — Draw the read path: Show gateway, compatibility service, canonical vehicle resolver, Bloom filter, Redis set, PostgreSQL fallback, and part-detail hydration.
• 22–30 minutes — Deep dive on modeling: Compare explicit rows, JSONB arrays, attribute rules, range predicates, wildcard fields, indexes, and rule-import validation.
• 30–35 minutes — Deep dive on cache correctness: Explain cache-aside, exact membership, invalidation scope, outbox delivery, versioned rebuilds, stale/missing projections, and false-positive behavior.
• 35–41 minutes — Scaling, reliability, and operations: Cover hot vehicles, stampede control, read replicas, bulk imports, lag monitoring, rebuild backfills, memory limits, and fail-open versus fail-closed behavior for a compatibility answer.
• 41–45 minutes — Trade-offs and close: Compare materialized views with Redis sets, discuss non-automotive dimensions, and restate that speed is useful only if stale projections cannot return false compatibility.

Core Entities

• Part: A catalog entry for a specific component or accessory (part_id, name, brand, part_number, category).
• Vehicle: A specific vehicle configuration (vehicle_id, year, make, model, submodel, engine).
• FitmentRule: The compatibility bridge between a Part and a set of Vehicles (either an explicit part_id + vehicle_id row, or an attribute-based rule row covering year_start, year_end, make, model, submodel).
• UserGarage: A user's saved vehicles (user_id, vehicle_id, nickname).

Schema design (column types, indexes, partitioning) is deferred to the deep dives. Keep the entity list this concise in an interview. These four entities are sufficient to drive the API design and High-Level Design.

API Design

FR1 + FR4: Search compatible parts for a vehicle:

GET /parts/compatible?year=2019&make=Ford&model=F-150&submodel=XLT&category=brake_pads
Response: { parts: [{ part_id, name, brand, part_number }], total, next_cursor }

Cursor-based pagination over offset: with 10 million parts, offset pagination requires scanning and discarding rows. Cursor-based pagination is O(1) per page regardless of depth.

FR2: Check "fits your vehicle" for a specific part:

GET /parts/{part_id}/fits?vehicle_id=veh_382
Response: { fits: true, notes?: "Fits 2019-2021 F-150 XLT with 5.0L engine" }

This endpoint is the hot path. It fires on every product page load for users with a saved garage vehicle. It must complete under 10ms at the application layer to stay within the 100ms end-to-end budget. A direct DB query here is a trap.

FR3: Add fitment data (catalog manager / admin):

POST /fitment
Body: { part_id, rule: { year_start, year_end, make, model, submodel } }
Response: { fitment_id }

Use rule (attribute-based) as the default in the API contract, not an explicit vehicle_id. This forces catalog managers to think in rules from day one. If a single vehicle_id is needed, the rule degenerates to year_start = year_end = specific_year with exact make, model, submodel values.

High-Level Design

1. Basic compatibility search (FR1 and FR4)

The read path is the system: a compatibility query must resolve to the correct list of compatible parts in under 100ms, even at 50 million DAU.

On the naive approach, a catalog manager stores one row per part-vehicle combination in a fitments table. A compatibility query selects all part_ids where vehicle_id matches. This works fine at thousands of parts.

Components (naive):

• Client: Web or mobile app. Sends a compatibility search with year, make, model, submodel.
• Catalog Service: Translates query params into a vehicle_id, queries fitments, returns the matching part list.
• PostgreSQL (fitments): Stores part_id + vehicle_id rows. An index on vehicle_id makes the lookup fast at small scale.

Request walkthrough (naive):

1. Client sends GET /parts/compatible?year=2019&make=Ford&model=F-150&submodel=XLT.
2. Catalog Service resolves the vehicle_id from the query parameters.
3. Catalog Service queries: SELECT part_id FROM fitments WHERE vehicle_id = 'veh_382'.
4. Catalog Service fetches part details for the returned part_id list.
5. Response returns to the client.

At 300 million fitment rows, the index scan for one vehicle still returns thousands of part_id rows across a massive index tree. Query time climbs past 200ms at peak concurrent load. The database becomes the bottleneck before the application server does.