Chat Analytics Platform

Topics: Search indexes, batch analysis, versioned results, recovery

Understanding the problem

We are designing an internal analytics platform for a company with AI chat. Analysts need to search chat history, measure what people discuss, and run jobs that find similar groups of conversations.

A user asks, "What running shoes work for flat feet?" The AI replies with several paragraphs mentioning Nike, Reebok, and Adidas. Does that count as a user discussing Adidas? The answer depends on what we are measuring. Our system must preserve who said what throughout storage, search, and analysis.

This is separate from chat serving. A chat response must remain fast while an analyst runs a broad query or a clustering job takes hours.

We receive about 2 million messages each day. Traffic lands over eight hours, roughly 70 messages per second today. At 50 percent quarterly growth, that grows to about 5x after four quarters, or about 350 messages per second during those eight hours. We plan for a 3x peak of roughly 1,000 messages per second next year. That peak is an assumption to test, not a measured fact.

The bytes are lopsided. A conversation has roughly three to five user messages of about 50 bytes and three to five AI messages of about 2 KB. One AI message carries about 40 times the text of one user message. A system that treats all text as equal will count recommendations as user interest.

The first year adds about 1.5 TB of raw text, roughly 1.48 billion messages across 185 million conversations. That is enough to make one decision early: broad historical analysis is batch work. We should not promise that every question over a year of text is a two-second request.

The throughput alone is not what makes this problem hard. A single PostgreSQL node can ingest 1,000 writes per second without strain. The hard part is that the system must preserve who said what across storage, search, and analysis. It must keep search fresh without coupling it to ingestion. And it must make reported numbers reproducible after models and data change underneath them. Those semantic and consistency problems are where the design decisions live.

Functional requirements

1. Append messages and retrieve an ordered conversation history.
2. Search, filter, count, and group conversations. Analysts can select user text, assistant text, or both.
3. Submit clustering or approved custom-analysis jobs, then search their published assignments.

Non-functional requirements

1. Ingest is durable and duplicate-safe. We target p95 acknowledgement below 500 ms. P95 means 95 percent of requests finish within that time. It is a target, not a benchmark.
2. Support 100 concurrent searches. We assume p95 first page under two seconds and search freshness within five minutes. Broad exact aggregations become jobs.
3. Retain one year of history and make batch analysis reproducible. A job may take minutes or hours.

Out of scope are chat generation, tenant isolation, privacy implementation, and an instant global recluster after every message. We chose batch analysis because real-time clustering is not worth the cost it imposes on the write path.

An interviewer may ask why analysis is not real time. Say what the product loses and gains. The product loses an instant group label. It gains a design where the chat path is independent from slow model work and where an analyst can explain where a number came from.

The setup

Core entities

AnalysisJob and ClusterRun are separate because they have different lifecycles. A job is a request that can fail, retry, or be cancelled. A run is a completed result that persists after the job finishes. One job produces one run on success; a failed job produces no run. Keeping them apart means a failed job never leaves a half-built run for search to find.

API

Search is a POST because the request body carries a structured query with Boolean logic, role selection, date ranges, filters, and grouping instructions. That payload does not fit cleanly into URL parameters, and a POST body avoids URL length limits that would silently truncate a complex query.

text_source is a parameter rather than a separate endpoint because the query logic is identical for user text, assistant text, or both. Only the field being searched changes. Separate endpoints would duplicate the search API for each role combination.

POST /conversations/{id}/messages
Body: { message_id, sequence, role, text, occurred_at }
 
GET  /conversations/{id}
 
POST /search
Body: { query, text_source, date_range, filters, group_by }
 
POST /analysis-jobs
Body: { pipeline, pipeline_version, population, parameters }
 
GET  /analysis-jobs/{id}

An analyst might send:

POST /search
{ query: '"running shoes" AND Reebok AND NOT Adidas',
  text_source: 'user', group_by: 'week' }

At first, this is literal phrase and term matching. It does not mean the vague semantic topic of running shoes. The result counts distinct conversations, not matching messages. Nike and Reebok can both count for one conversation.

With text_source: user, NOT Adidas means Adidas appears nowhere in that conversation's user messages. The assistant mentioning Adidas does not exclude the conversation. For this design, date_range and weekly grouping use conversation creation time. That is a simple definition an analyst can understand and repeat.

High-level design

We will build this in the order the data moves. Start with a durable conversation. Then make it searchable. Then add analysis.

1) Append and read ordered history

The producer sends user message m17 for conversation c42 with sequence 1. PostgreSQL owns this path because it can commit related changes together.

message_id is the idempotency key. Idempotency means a retry has the same result as the first request. Store a unique pair of conversation and message ID, and a unique pair of conversation and sequence. A retry of identical m17 returns the first receipt. Reusing m17 with different text returns HTTP 409. Sending another message at an occupied sequence also returns 409.

The producer supplies the sequence. A late sequence 2 can still be valid after sequence 10. The system orders history by the accepted sequence.

-- conceptual transaction
BEGIN;
INSERT message(c42, m17, sequence=1, role='user', text=...);
-- identical message_id returns the old receipt; conflicting data returns 409
UPDATE conversation SET source_revision = source_revision + 1 WHERE id = c42;
INSERT outbox_event(c42, source_revision, 'conversation_changed');
COMMIT;

Only acknowledge after the commit. Candidates often acknowledge first because it looks faster. It creates the worst kind of data bug: the producer believes the message exists, and the database does not.

source_revision advances for any accepted change that search can show, such as a new message or a published group assignment. It lets a later search write win over an older one.

Messages and outbox events commit together; ordered history reads from PostgreSQL.

PostgreSQL is the source of truth for ordered messages. The history API reads it directly, which also lets a user see a just-written message before search catches up.

2) Search conversations

An analyst should not join billions of message rows for every search. Search needs one record per conversation with fields shaped for the questions we support. This is where we add Elasticsearch.

An indexer reads the committed conversation and writes one search record with separate user_text and assistant_text fields. The first contains the flat-feet question. The second contains the AI recommendation that mentioned several brands.

An analyst can ask what users discuss without silently counting the assistant's recommendations. It also lets the analyst explicitly search assistant recommendations when that is the question.

At low traffic, a records scan in PostgreSQL can prove the search semantics. It stops being a useful interactive approach once the year fills up. The search deep dive explains the index.

We also need a durable handoff from writing to indexing. Notice the outbox_event row in the transaction above. If the write process dies after commit, that row is how a publisher knows to retry. The publisher sends each event to an index queue, which buffers work so a slow indexer does not block ingestion. The freshness deep dive covers the full pattern and the failure it prevents.

Committed changes flow through a queue into a conversation-shaped search index.

The opening example now has a clean answer. Search user text for Adidas and c42 does not match. Search assistant text and it does. Tell an interviewer this rule before choosing a search engine. The rule matters more than the engine.

3) Submit and publish analysis

An analyst now submits a running-shoe clustering job. The job API stores its pipeline name, pipeline version, population filter, parameters, and status in PostgreSQL. Workers claim jobs with a lease so a crashed worker does not hold one forever.

The analysis worker needs a fixed dataset. It reads immutable Parquet files from S3. S3 is object storage, a service for storing and retrieving files by name. Parquet is a column-oriented file format that works well for large batch reads because a query that needs three fields can skip the rest. A separate export queue feeds an exporter that writes conversation versions to those files as they change and periodically records a completed manifest. A manifest is a list of files that make up one fixed dataset. A job pins one completed manifest. It may exclude messages still waiting for export.

The manifest can lag. Record its coverage and lag, and show a failed export rather than hiding it. This is a good place to show restraint in an interview. You do not need a streaming platform, a warehouse, and a workflow engine to explain one batch export.

The worker writes the result and its method back to durable storage. A finished global run can become searchable. A scoped running-shoe job can publish a scoped result, but it does not replace the global grouping that search uses by default.

Analysis workers use frozen inputs while ingestion and interactive search continue independently.

The job is deliberately asynchronous. The analyst gets an ID, polls GET /analysis-jobs/{id}, and sees queued, running, succeeded, or failed. That is more honest than holding an HTTP request open while model work runs.

Four deep dives

Each deep dive follows the same interview move. Name the failure. Show the first reasonable answer. Improve it. Then choose the design and explain the cost it accepts.

1) How do we search a year of conversations without scanning everything?

Problem

A row-per-message scan reads assistant-heavy text, reconstructs conversations, and removes duplicates on every request. It may be fine for an audit. It will not give a reliable first page over a year of history.

Phrase semantics are another trap. If a user says running in one message and shoes in the next, a literal search for "running shoes" should not match by accident.

SELECT count(DISTINCT conversation_id)
FROM messages
WHERE role = 'user' AND text ILIKE '%Reebok%';

SELECT conversation_id
FROM conversation_search_records
WHERE user_text_tsv @@ to_tsquery('Reebok & !Adidas');

user_matches = phrase(user_text, "running shoes")
             ∩ term(user_text, "Reebok")
             − term(user_text, "Adidas")

Do we need a separate analytics database for aggregations?

Probably not yet. The tempting move is to add a columnar warehouse such as ClickHouse or BigQuery for broad aggregations. But the batch export to Parquet already gives us a read-friendly historical dataset for expensive jobs, and Elasticsearch handles interactive search. Adding a third query engine creates a third copy of every conversation to keep consistent. Start with the interactive cap that routes broad queries to the job system, and add a warehouse only if the job queue becomes a bottleneck for common analytical questions.

2) How do we group conversations whose intent keeps changing?

Problem

Start with one conversation. The user says, I need running shoes. Later they add, I need them for rehabilitation after a knee injury. The conversation may reopen next week.

Its intent changed. It started as shoe shopping and became rehabilitation support. There is no useful permanent-close rule based on 30 minutes of silence.

The expensive bad answer is to rebuild all global groups after every message. It spends model work on every edit and keeps redefining every group. Splitting the data by shard and clustering each shard independently is also wrong for global groups. Group 7 on one shard has no relationship to group 7 on another.

group = nearest_center(embed(user_text + assistant_text))

summary = summarize_user_intent(messages, assistant_context=True)
vector = embed(summary)
group = nearest_center(vector, active_global_run.centers)

What if a conversation belongs to two groups?

It can. A user who asks about injury prevention and then budget options has two intents. The first version assigns the conversation to its nearest single center, which is a known simplification. Two practical extensions are soft assignment (recording distance to the top-k nearest centers) and splitting by user turn (embedding each user question independently). Both add complexity to the search view, so start with single assignment, measure how often reviewers disagree with the chosen group, and add soft assignment if the disagreement rate is high.

What if the nightly run produces worse groups than the current ones?

Do not publish automatically. Run the new centers against the held-out sample and compare group coherence with the current run. If the new groups are worse, or if too many conversations land far from every center, keep the current run active and flag the new one for review. The publication step is a gate, not a timer.

3) How do we keep search fresh without blocking ingestion?

Problem

The message can commit to PostgreSQL, then the write process can die before it tells the indexer. History is correct while search misses the conversation. A slow old indexing task can also arrive after a newer one and overwrite it.

commit message
queue.send(conversation_id)

document = read_current_conversation(conversation_id)
index(document, external_version=document.source_revision)
ack queue message after success or duplicate rejection

What if the queue itself goes down?

Outbox events stay in PostgreSQL until marked delivered. If the queue is unavailable, the publisher retries. Messages continue to be ingested because the write path does not depend on the queue. Search freshness degrades until the queue recovers and the backlog drains, but no work is lost. If the queue is down for hours, monitor the outbox table size and alert, because it will grow until delivery resumes.

Search freshness and cluster freshness are not the same thing

This is the place where two deep dives collide, and interviewers notice whether you see it. Search can show a new knee-injury message within minutes. The hourly cluster assignment has not run yet, so the conversation still carries its old group label. If the search result displays both the fresh text and the stale group as if they came from the same analysis, the analyst draws a wrong conclusion.

The fix is a pending state. When the indexed conversation version is newer than the version the cluster assignment was computed from, mark the group as pending in the search document. An analyst who filters by group sees only conversations whose assignment matches their current text. A conversation in flux is excluded from group counts rather than counted under a label that no longer fits.

This is a concrete example of a broader principle. Derived facts must carry the version of their input and the version of their method.

4) How do we change models without corrupting old reports?

Problem

An analyst saves a report that says a rehabilitation group contains 12,000 conversations. A week later, a better prompt or new centers produce 19,000. Both numbers may be useful. A silent overwrite makes the first report impossible to explain.

There is a second failure. A new analysis can crash halfway through. It must never mix half of the new groups with the old published groups.

UPDATE memberships SET group_id = :group WHERE conversation_id = :id;

INSERT INTO memberships (run_id, conversation_id, group_id, distance)
VALUES (:run, :conversation, :group, :distance);

CREATE TABLE cluster_runs (
    run_id          UUID PRIMARY KEY,
    job_id          UUID REFERENCES analysis_jobs(job_id),
    manifest_id     UUID,          -- which export snapshot was the input
    summary_prompt  TEXT,          -- frozen prompt version
    model_version   TEXT,          -- embedding model used
    n_clusters      INTEGER,
    parameters      JSONB,         -- random seed, batch size, etc.
    status          TEXT,          -- 'building', 'validated', 'active', 'retired'
    created_at      TIMESTAMPTZ
);
 
CREATE TABLE cluster_assignments (
    run_id              UUID REFERENCES cluster_runs(run_id),
    conversation_id     UUID,
    conversation_version INTEGER,  -- which version of the conversation was analyzed
    group_id            INTEGER,
    distance            FLOAT,
    PRIMARY KEY (run_id, conversation_id)
);

candidate = run_analysis(frozen_input, model_and_code_version)
if candidate.is_complete_and_valid():
    publish(candidate.search_view)   -- alias swap
else:
    keep_current_search_view()       -- old result stays live

What happens to in-flight searches during a publish?

An analyst whose search started before the alias switch continues reading the old index. Elasticsearch keeps a point-in-time reader attached to the segments it opened, even after the alias moves. New searches land on the new view. We keep the old index alive briefly for these in-flight reads, then delete it. There is no moment when a search sees half of one run and half of another.

Final design

The complete design separates durable ingestion, interactive search, and reproducible batch analysis.

Trace the opening conversation. The write API accepts user m17 at sequence 1. It commits the message and an outbox event. It later accepts assistant m18 at sequence 2. The indexer builds one record with the flat-feet question in user_text and the brand recommendations in assistant_text.

An analyst searches user text for Adidas. c42 does not count, because only the assistant said Adidas. That result surprises people at first. It is also the whole point of preserving roles.

Now trace the running-shoes job. The worker pins a completed export manifest, writes intent summaries and embeddings, and fits its 30-group scoped result. It saves the assignments and method. That scoped result may become searchable on its own without disturbing the global groups.

A global job follows the same path with 200 groups. It builds a complete new view and publishes it only after validation. A saved weekly report records the literal query, role selection, input, and assignments. An analyst can inspect those records when a count looks strange.

Additional deep dives

1. Semantic search. Add a related-conversation mode that embeds the query and returns nearby intent summaries. Label it related, not an exact count. It is a separate feature from literal Boolean search. The interesting design question is how to combine a semantic score with metadata filters without scanning the full vector space. An interviewer may also ask how you keep the embedding model consistent between the conversation summaries and the query.

2. Deletion and privacy. A deletion request must remove source messages and invalidate search records, snapshots, and published artifacts under a defined policy. Authorization, encryption, tenant boundaries, and legal holds need their own design. The hard part is that derived data lives in multiple stores. A search document, a Parquet export, and a cluster assignment can all reference a deleted conversation, and each needs a different removal path with its own timing guarantees.

3. Multi-tenant isolation. Carry tenant identity through every source record, queue, file, and index route. A tenant filter in a search request is not an authorization boundary. The design challenge is that clustering needs a global population to discover global groups, but tenants may not want their data grouped alongside other tenants. That tension between analytical value and isolation is worth naming even if you do not solve it.

4. Backfill without downtime. Build a new search view, replay missed changes, compare source and index revisions, validate, then switch the alias. Scheduling and rollback need an operations runbook. The nontrivial part is that new messages keep arriving while the backfill runs. The new view must catch up to live traffic before the switch, and the switch itself must not lose any writes that landed during the gap.

What is expected at each level

Mid-level

Show a complete path for one message. The client writes a sequenced message. PostgreSQL commits it before acknowledgement. The history API reads it back in order. You do not need to name every constraint, but say that retries cannot create a second message.

Show one conversation-shaped search record with separate user and assistant text. If an interviewer asks whether Adidas counts, ask which speaker the analyst means. That question shows the interviewer you understood the data before picking a tool.

Use an asynchronous job for clustering. Explain that a job may take minutes or hours and cannot block chat ingestion. A reasonable answer can stop with PostgreSQL, Elasticsearch, a queue, and batch files. Do not spend your time inventing a custom vector database or a fleet of model services.

Expect a follow-up about a crash after message commit. Name the outbox and explain it in one sentence. The message and the work notice commit together, so a later publisher can recover the notice. I do not need you to draw the outbox publisher. I need you to know why the gap exists.

Senior

Derive the scale and use it. A senior answer turns 2 million daily messages, AI-heavy text, and one-year retention into an argument for conversation records, inverted search, and batch aggregation. It does not claim index size or latency numbers that nobody measured.

Make query meaning precise. State that counts are distinct conversations, brand buckets overlap, literal phrases do not cross messages, and exclusions apply to the selected role. If asked for a broad weekly aggregation, say when you would return a background job rather than a partial interactive answer.

Drive the recovery discussion before the interviewer has to. Explain the commit-then-notify gap, the outbox, duplicate delivery, and versioned index writes. You do not need to recite queue internals. The signal is that retries and stale work cannot corrupt the search view.

For clustering, explain the role-aware summary, reusable centers, hourly assignment, and nightly rediscovery. Be ready for Why not rebuild all groups after each message? and Why not train each shard? The answers are changing definitions, unnecessary model work, and groups that cannot be compared globally. I do not mind if your first instinct is regular KMeans. Just be able to tell me why retraining from scratch on every update is expensive and what reusable centers buy you.

Staff+

Start by asking what a reported number means. Is it a live operational estimate, a saved report, or an exact historical claim? Those products have different consistency and cost needs. Staff candidates draw that line before picking a storage product.

Show how definitions change safely. A new prompt, embedding model, or grouping method creates a new result. Build it away from the live view, validate it, then publish it in one switch. Keep the old result inspectable. The useful follow-up is, What happens if the job dies halfway through? The answer is that the old complete view remains live. If you can walk through that failure without hesitation, you understand the invariant.

Explain the difference between search freshness and analysis freshness. New text can be searchable within minutes while its group assignment waits for the hourly pass. Show the pending state instead of pretending the two facts came from the same input.

Challenge needless machinery. A streaming recluster, separate shard-local groups, or a new distributed service for each stage may sound impressive and make the semantics worse. A staff answer chooses the smallest system that preserves who said what, handles recovery, and keeps reports reproducible.

Finally, name the measurements you would collect before changing the design. Search query mix, queue lag, export coverage, group review quality, assignment cost, and the rate of pending assignments tell you whether to add workers, change the schedule, or revise the representation. The strongest signal at this level is knowing which number would make you add a component.