Picking the Right Store for the Right Job

The last three lessons walked through three concrete decisions — Postgres for rules, Redis for the valuation cache, MongoDB for the audit log — each justified on its own terms. This lesson pulls the reasoning behind all three apart from the specifics of this service, so it travels with you into the next table you have to design, whether or not it ever touches a vehicle.

Four questions do most of the work. First: how strong does consistency need to be, and does more than one row need to change together atomically? `pricing_rule` needed both — a guardrail and a boost changing as one unit, with a foreign key that must never point at nothing. That need for multi-row atomicity and referential guarantees is the single strongest signal that data belongs in a relational database; nothing else in this checklist overrides it if the answer is 'yes, and getting this wrong causes real damage.'

Second: what is the read/write ratio, and how expensive is the write it's replacing? The valuation cache is read far more often than the expensive path (three provider calls) it stands in front of, and each read it satisfies is a read it prevents from happening the expensive way. That asymmetry — cheap-to-serve, expensive-to-recompute, tolerant of staleness — is the signature of a caching problem, and it is worth noticing what would disqualify it: if staleness were unacceptable (a bank balance, an inventory count at checkout), no read/write ratio would justify a cache-aside pattern, because the pattern's entire value proposition rests on 'a slightly old answer is still a good answer.'

Third: how stable is the schema, and who else depends on its shape staying fixed? `pricing_rule`'s columns barely move — a `NUMERIC` percentage, a date window, a foreign key — while `valuation_event` gained a fraud score and a promotion code without anyone asking permission. When a table's shape is genuinely stable and shared across systems that need to agree on it, a relational schema's rigidity is a feature, catching mistakes at write time. When a payload is expected to keep growing new, optional fields as the business asks new questions, forcing every one of those additions through a coordinated migration is friction with no corresponding safety benefit — which is the document store's case to make.

Fourth: what does the actual query look like? `SELECT ... WHERE category = ? AND effective window covers today ORDER BY priority` is a relational query in its bones — filtering and ordering over known columns, possibly joined against another table. `GET valuation:$vin` is a pure key lookup with no filtering logic at all — a cache's entire interface. `INSERT this document, and later fetch by ID or by a handful of predictable fields` is a document store's native shape. If you find yourself designing elaborate application-side logic to fake joins across a cache or a document collection, that is usually a sign the data belongs somewhere else, not a sign you need a cleverer cache key.

None of these four questions has a universally right answer — they only have a right answer for a specific slice of data doing a specific job, which is exactly why one service can and should use three databases at once without that being a sign of disorganization. The table below is this service's answers; the four questions above are what produced them, and they are what you should ask again for the next table you design, in this service or any other.

One caution worth carrying forward into Module 4: none of this changes because the calls into these stores happen from inside a suspend function. Coroutines make it cheap to fire a Mongo write without blocking a thread, or to await a Redis lookup alongside three provider calls — but they do not change which store the data belongs in. The decision in this module and the concurrency techniques in the next one are independent axes: get the store right first, then make the calls to it fast.

// Data                | Store     | Deciding factor(s) from the checklist
// ---------------------|-----------|----------------------------------------------------
// pricing_rule         | Postgres  | Multi-row atomicity + foreign keys to vehicle_category
// valuation lookup     | Redis     | High read/write ratio, expensive recompute, tolerable staleness
// valuation_event      | MongoDB   | High write volume, evolving optional fields, lookup-by-ID/filter
//
// Ask, for any new table:
// 1. Does more than one row need to change together, atomically?      -> leans relational
// 2. Is this read far more than written, and is a stale read fine?    -> leans cache
// 3. Is the shape stable and shared, or evolving and single-owner?    -> stable=relational, evolving=document
// 4. Is the query a join+filter, a pure key lookup, or fetch-by-ID?   -> matches the store's native shape

suspend fun valuate(vin: String): ValuationResult {
    // Q2 answer: cache-aside read (Redis) — cheap to serve, expensive to recompute
    valuationCache.get(vin)?.let { return it }

    // Q1 answer: transactional, foreign-keyed read (Postgres)
    val rules = ruleRepository.findActiveRulesForCategory(categoryOf(vin))

    val result = computeValuation(vin, rules, providerClients)
    valuationCache.set(vin, result, ttlWithJitter())

    // Q3/Q4 answer: append-only, schema-flexible write (MongoDB)
    eventLog.save(ValuationEvent.from(vin, rules, result))

    return result
}