Conversation database design gets expensive when every message rebuilds the customer’s state from scratch. A customer replies “yes”, and the backend loads their profile, all past messages, every cart line and the business settings again.
The response may need only the current question and a small cart summary. Let’s identify that working set before reaching for a cache or a larger database server.
Write down what the next decision uses
Take one handler and list the fields it actually reads. For a checkout confirmation, that might be the current proposal ID, cart version, confirmation state and display currency.
Keep the history for audit and recovery, but don’t make the normal reply path scan it unnecessarily. A working-state row is a projection of relevant history, not a replacement for the underlying order and message records.
Separate authoritative values from convenient display copies. A cached product name can help compose a reply. A stock commitment still belongs in the inventory service.
Build a bounded conversation row
Use a key scoped by business and customer. Store the current task, proposal reference, compact summary and revision. Keep large message bodies and media in their own records.
If the cart can grow large, store its lines separately and fetch the required subset or aggregate. Embedding an unbounded array only moves the repeated work into a larger document read.
MongoDB’s modelling guidance treats access patterns as part of schema design. The same question applies to SQL: which data belongs together for this particular operation? Data modelling reference.
Inspect a real query plan
The example creates actual SQLite tables, inserts a small synthetic message history and compares the plan before and after adding a suitable index. It also reads the bounded context row.
import assert from 'node:assert/strict';
import {DatabaseSync} from 'node:sqlite';
const db=new DatabaseSync(':memory:');
db.exec(`CREATE TABLE messages(business TEXT,customer TEXT,seq INTEGER,text TEXT);
CREATE TABLE context(business TEXT,customer TEXT,revision INTEGER,summary TEXT,PRIMARY KEY(business,customer));
INSERT INTO context VALUES('b','c1',2,'Confirming quote q1');BEGIN;`);
const insert=db.prepare('INSERT INTO messages VALUES(?,?,?,?)');
for(let i=0;i<2000;i++)insert.run('b',`c${i%20}`,i,'Synthetic message');
db.exec('COMMIT');
const sql="SELECT text FROM messages WHERE business='b' AND customer='c1' ORDER BY seq DESC LIMIT 5";
const before=db.prepare('EXPLAIN QUERY PLAN '+sql).all().map(r=>r.detail).join(' | ');
db.exec('CREATE INDEX recent ON messages(business,customer,seq DESC)');
const after=db.prepare('EXPLAIN QUERY PLAN '+sql).all().map(r=>r.detail).join(' | ');
assert.match(before,/SCAN/);assert.match(after,/SEARCH/);
assert.equal(db.prepare(sql).all().length,5);
assert.equal(db.prepare("SELECT summary FROM context WHERE business=? AND customer=?").get('b','c1').summary,'Confirming quote q1');
db.close();console.log(JSON.stringify({before,after}));
console.log('PASS: real query-plan change and bounded conversation context');
The assertions check the access path rather than claiming a fixed speedup. An index can remove a scan for this query shape; its actual latency and write cost depend on your data and workload.
This is an in-memory SQLite fixture. It doesn’t measure disk latency or establish production database performance. Use the same approach against your deployment database with representative data.
Maintain the projection without overwriting new state
A summary worker can finish after the customer has already changed their request. Include the source conversation revision in its update. If the current revision is newer, reject or rebuild the summary instead of overwriting it.
For correctness-sensitive state, update the source record and projection transactionally where practical. If you update through events, keep source versions and a replay path so the projection can recover after lag or failure.
Make rebuilding possible from retained source records. A derived summary shouldn’t be the only surviving evidence of what the customer confirmed.
Remove repeated reads inside one request
After fixing the query shape, pass loaded context through the handler rather than having each helper fetch it again. A request-scoped cache of read promises can be useful when several helpers are called independently.
Scope that cache by business, customer and relevant version. Don’t reuse one customer’s result for another request merely because the helper takes no explicit tenant argument.
Watch ORM behaviour. A single high-level call may issue many SQL queries, especially when iterating relationships. Inspect query counts and plans rather than judging the code by how short the ORM expression looks.
Add indexes for the query you actually run
If recent messages are filtered by business and customer and ordered by acceptance sequence, an index beginning with those filter columns can support that access pattern. Check the chosen plan and whether an additional sort remains.
Don’t add every possible index pre-emptively. Each one consumes storage and adds work to inserts and updates. Measure the hot query and the write path together.
For pagination, a stable sequence or cursor can be more predictable than deep offsets. Keep the ordering unique so retries and concurrent inserts don’t cause confusing page boundaries.
Keep current state different from business truth
The conversation may say the customer wants three items. The order service may have accepted only two. Load the verified result when reporting a completed action.
Similarly, a summary saying “payment complete” isn’t proof of payment. Store references to verified operations and use their state when generating factual completion claims.
This separation lets the conversation stay quick without turning a convenient read model into an accidental accounting system.
Test changes against realistic requests
Measure queries per message, context size and execution time for short replies, long conversations and large carts. Include cold cache and projection-lag cases.
Check that a rejected stale update leaves current instructions intact. Then rebuild a conversation from source history and compare it with the stored projection.
Start with the fields your next reply needs. A smaller, well-keyed working set often removes more unnecessary work than caching the original oversized query.
Run the example and adapt it
Save the code as 21-conversation-read-model.mjs and run it with Node.js 24 or later:
node 21-conversation-read-model.mjs
The code above includes assertions you can run locally. It tests the local decision logic with controlled inputs; it doesn’t connect to a live provider or establish production performance. Keep that boundary when adapting it to your application.
