External IDs Migrated to Array Format

Context and Problem Statement

The original externalIds ADR chose a nested object structure { marketplace: { finish: id } } to store per-finish marketplace IDs. While correct, this structure required $or queries across all possible finish types (10+) to find a product by external ID — slow and hard to index.

All game importers (not just SWU) needed efficient indexed lookups on external IDs for price matching and marketplace integration.

Decision Drivers

• Query performance: $or across 10+ finish keys is slow and un-indexable
• MongoDB multikey indexes work well on arrays but not on dynamic object keys
• All games need the same indexed lookup pattern, not just SWU
• Existing data in production needs migration

Decision Outcome

Migrate from nested objects to arrays of { finish, id } entries:

// Before (nested object)
externalIds: {
  TCGplayer: { Standard: 12345, Foil: 12346 },
  Cardmarket: { Standard: 67890 },
}
 
// After (array format)
externalIds: {
  TCGplayer: [{ finish: "Standard", id: 12345 }, { finish: "Foil", id: 12346 }],
  Cardmarket: [{ finish: "Standard", id: 67890 }],
}

Enables a single indexed query: { "externalIds.TCGplayer.id": productId } using MongoDB multikey array indexes.

Impacted Files

Consequences

Positive

• Single indexed query to find product by marketplace ID
• Consistent structure across all games
• MongoDB multikey indexes on .id field

Negative

• Breaking change requiring data migration in production
• Schema uses Mixed type to preserve InferSchemaType compatibility

More Information

• Original decision: Nested externalIds by Finish
• PR: #2719