DEV Community: Manoj Sethi

Your speech-to-text sends the recording. It only needed the words.

Manoj Sethi — Thu, 22 Oct 2026 13:23:05 +0000

A speech feature has one job: turn a voice into a paragraph of text. Most of them do it by uploading the voice to a company neither you nor the speaker has heard of. There is a second way, where the model runs on your own server and only the paragraph travels. This is how that is built, what it costs, and the part of it that is still uncomfortable.

Check it yourself. ffmpeg -i clip.webm -ar 16000 -ac 1 -c:a pcm_s16le clip.wav && ls -l clip.*

Start with a school and a cupboard full of cassettes. Students have answered questions out loud, a teacher has recorded them, and now somebody has to type out what each student said so it can be marked.

There are two ways to get that typing done.

You can put the cassettes in an envelope and post them to a typing bureau in another city. They are good at typing. They are fast and they are cheap. A week later the typed pages come back, and they are correct. But the bureau has the cassettes. They have a shelf, and the shelf has your students’ voices on it, and what happens to that shelf is now a question about the bureau’s policies rather than yours.

Or you can put a typist in the school office. The cassettes never leave the building. The typed pages come out of the same room they went into. The typist is slower than the bureau and you had to find a desk for them, and that is the whole of the trade.

Both ways give you the same page of text, and the page of text is the only thing the marking ever reads. This piece is about building the second one in software, in a platform where the students are real and the recordings are of children and young adults taking a practice exam.

Two ways to get the same page of text

In a browser, a spoken answer becomes an audio file the moment the student stops talking. From that point it has to reach a model that can read it. There are exactly two places that model can be.

Both routes end with the same paragraph. Only one of them puts the voice itself on a machine you do not own.

Nobody chooses the first route because they want to share the audio. They choose it because it is four lines of code and the other one is an afternoon of work. So it is worth being precise about what the four lines actually do.

Why everyone calls the API

A hosted speech API is genuinely excellent. It is more accurate than a small model on your own hardware, it handles accents better, it costs a fraction of a cent a minute, and it scales to a thousand students without you thinking about it once. If you are building a voice note feature for a to-do app, call the API. That is the right answer and this piece is not arguing with it.

It stops being the right answer when three things are true at the same time. The audio is of a person who did not choose the vendor. The audio is of them being tested, which means it captures them at their least polished. And the organisation running the test has to be able to answer, in writing, what happens to it.

An exam platform hits all three. A student sits down to practise, speaks for two minutes about their home town, and has no idea that a copy of their voice is now on a server in a country they have never visited, under an agreement they never read. The institute cannot tell them what happens to it either, because the institute does not know.

The question that decides it

If a parent wrote in and asked where their child’s voice recordings go, could you answer with a sentence rather than a link to somebody else’s privacy policy? If not, the audio is in the wrong place.

What the model actually needs

Here is the part that makes the local route obvious once you see it. The model does not want the file the browser produced. It wants something much plainer, and much bigger.

A browser records compressed audio, because compressed audio is small and small is good on a network. The model cannot read compression. It wants raw samples: the height of the sound wave measured sixteen thousand times a second, two bytes per measurement, one channel. Multiply that out for a minute of speech and you get 16,000 × 2 × 60, which is 1,920,000 bytes. Nearly two megabytes for one minute of a person talking.

And what comes back out of the model is a paragraph.

One minute of speech, measured three ways. The last bar is the only one the scoring step ever reads.

Look at that gap for a moment. The thing you would be uploading is roughly two thousand times larger than the thing you actually wanted, and it is the only one of the three that can be played back to identify a person. The text cannot be played. Nobody can listen to a paragraph and recognise a voice in it.

Which means the audio is not the output. It is scaffolding. It exists for the length of one function call and it has no reason to travel.

The seven steps, in order

This is the path a single spoken answer takes in the platform. On the original article you can click through the steps one at a time. The image below shows all seven.

Every step up to the transcript happens inside infrastructure the institute controls. Only the last one sends anything to an AI vendor, and what it sends is text.

The first step is the cheapest and the one most people skip. A browser will sometimes hand over a recording that contains no sound at all, only the wrapper around it. Feed that to ffmpeg and it fails with a parsing error, and you have spent a process spawn and a log line on nothing.

// speaking-stt.service.ts
// A container stub with no audio in it. ffmpeg cannot parse these
// ("EBML header parsing failed"), so there is no point spawning it.
const MIN_AUDIO_BYTES = 1024;
if (audioBuffer.length < MIN_AUDIO_BYTES) {
  slog("STT", "Skipping empty audio buffer", { bufferBytes: audioBuffer.length });
  return "";
}

A thousand and twenty-four bytes is not a clever threshold. It is simply smaller than any real recording and larger than any empty one, and it was picked after watching what actually arrived.

Then the conversion. This is the whole of it:

// speaking-stt.service.ts - webmToWav()
// WebM/Opus from the browser → the only shape the model will read.
await execFileAsync(ffmpegPath, [
  "-i", inputPath,
  "-ar", "16000",      // 16,000 samples a second
  "-ac", "1",          // one channel, not two
  "-c:a", "pcm_s16le", // plain 16-bit samples, no compression
  "-y",
  outputPath,
]);

Three flags carry all the meaning. -ar 16000 sets the sample rate, -ac 1 collapses stereo to one channel because a microphone in a laptop was never really recording two, and -c:a pcm_s16le says store the raw numbers with no compression at all. The output is the two-megabyte file from the chart above, sitting on local disk, going nowhere.

Then the model reads it, in the same process that wrote the file:

// speaking-stt.service.ts
// Loaded once, kept in memory for the life of the process.
const { pipeline } = await import("@huggingface/transformers");
whisperPipeline = await pipeline("automatic-speech-recognition", env.WHISPER_MODEL, {
  // Use fp32 for CPU compatibility
  dtype: "fp32",
});

// And the call itself. No network in this line.
const result = await whisper(wavPath, {
  language: "english",
  return_timestamps: false,
  chunk_length_s: 30,
});

Two details worth naming. fp32 means full-precision numbers, which is slower than the half-precision a graphics card would use, and it is chosen because this runs on an ordinary CPU with no graphics card at all. chunk_length_s: 30 means the model reads the audio in thirty-second pieces, which is what lets a two-minute answer work at all rather than running out of memory.

And then the temporary files go, in a finally, so they go even when the step above them threw:

// speaking-stt.service.ts
} catch (err) {
  slogError("STT", "Transcription failed", err, { provider: env.STT_ROUTING });
  return ""; // Return empty rather than crashing session
} finally {
  try { if (fs.existsSync(webmPath)) fs.unlinkSync(webmPath); } catch { /* ignore */ }
  try { if (wavPath && fs.existsSync(wavPath)) fs.unlinkSync(wavPath); } catch { /* ignore */ }
}

Notice the catch. A failed transcription returns an empty string rather than an error. A student in the middle of a spoken exam should not see their session die because a model had a bad minute. We will come back to what happens to that empty string, because it is the reason for one of the least comfortable parts of this design.

There is a switch, and you hold it

Honesty first: this system can also send the audio away. There is one environment variable, and it has two branches.

// the routing
// The whole routing decision. Two branches, one env var.
if (env.STT_ROUTING === "local") {
  text = await transcribeLocal(wavPath);   // Whisper, in this process
} else {
  text = await transcribeViaRemote(wavPath); // Groq or OpenRouter
}

Set STT_ROUTING to local and the model runs in the API process. Set it to anything else and the WAV goes to Groq or OpenRouter, whichever is currently selected. That branch is real, it works, and it is faster than the local one.

A design that pretended otherwise would be a worse design. The switch exists because an institute running a thousand mocks a week on a small server has a genuine reason to want the remote path, and because a platform with no escape hatch is a platform that fails on the day the CPU cannot keep up.

What matters is where the switch lives. It is a server-side setting in the institute’s own deployment. It is not a default, it is not buried in a vendor’s dashboard, and it does not change by itself when somebody upgrades a library. If the recordings are going out, somebody made that decision and can say so.

What this costs you

Now the honest bill. Running the model yourself is slower, and it is slower in a specific way that will surprise you if nobody says it out loud.

// the concurrency gate
// One transcription at a time. Every call waits for the last one.
let transcriptionQueue: Promise<void> = Promise.resolve();

async function runExclusive<T>(task: () => Promise<T>): Promise<T> {
  const previous = transcriptionQueue;
  let release: () => void = () => {};
  transcriptionQueue = new Promise<void>((resolve) => { release = resolve; });

  await previous.catch(() => {});
  try {
    return await task();
  } finally {
    release();
  }
}

That is a queue of one. Every transcription waits for the one before it. It is there because a Whisper model on a CPU will happily use every core it can reach, and four of them running at once do not go four times faster, they go slower and take the rest of the API down with them.

So when twenty students finish a spoken section at the same time, the twentieth transcript is produced after nineteen others. On this platform that is acceptable, and the reason is worth understanding rather than assuming. Transcription does not happen while a student is waiting. It happens after a turn ends, in the background, while the student is already being asked the next question. The number that has to stay small is the time before the student sees a band and written feedback at the end, and that has minutes of room in it, not seconds.

If your product needs live captions on a call, this design is wrong for you and no amount of tuning will fix it. The queue of one is not a limitation to work around. It is the shape of the decision.

The recording is still kept

Here is the part that is easy to oversell and should not be. Running the model locally does not mean the audio disappears. The original recording is uploaded to the institute’s own object storage with a private access rule, and it stays there.

It is kept on purpose, and this is why:

// speaking-session.service.ts - retranscribeMissingTurns()
// Why the original recording is kept. Scoring runs this first.
const transcript = await transcribeAudio(Buffer.from(bytes));
if (transcript.trim().length === 0) {
  // transcribeAudio swallows provider errors and returns "" — still failing,
  // leave the turn empty; the next scoring attempt / manual rescore retries.
  continue;
}

Remember the empty string from earlier. Before a session is scored, the system looks for turns that have a recording but no transcript, fetches the recording back out of storage, and tries again. A student who lost a transcript to a bad minute gets their answer marked anyway, and never finds out anything went wrong. That recovery is only possible because the audio was kept.

So the claim this architecture earns is narrow and worth stating exactly. The recording is stored in storage the institute controls, under an access rule the institute sets, and it is never handed to a third-party model vendor. It is not “the audio is deleted”. Anyone telling you a speech feature keeps no audio at all should be asked how it retries.

Where this breaks

Accuracy. A small model on a CPU is less accurate than the best hosted one, particularly on strong accents, and on an exam platform the accent is the whole point. This is the real cost and it is not fully solved. A larger model is one setting away and considerably slower.

The scoring step still leaves. The transcript is sent to a language model for a band and written feedback. That is a genuine external call and the piece would be dishonest without saying so. What crosses is text, which cannot be played back and does not carry a voice, but it is not nothing.

The first request is slow. The model loads once and is held in memory. The request that triggers the load waits for it. After a deploy, somebody pays that cost.

It consumes the server you already have. Transcription competes with everything else the API is doing. On a small box under real load, that is felt.

ffmpeg is a dependency with opinions. It ships as a binary, it has to be executable in your container, and the day it is not, every spoken answer silently produces an empty transcript.

Check it on your own machine

You do not have to take the size argument on trust. Record anything in a browser, save the file, and run the same conversion this platform runs:

# terminal
ffmpeg -i clip.webm -ar 16000 -ac 1 -c:a pcm_s16le clip.wav
ls -l clip.*

The WAV will be roughly fifteen times the size of the WebM, and it will be about 1.9 megabytes for every minute. That is the file a hosted API receives. Then look at the transcript it gives back and count the characters.

And if you are choosing a speech vendor rather than building one, there is a shorter version of the same test. Ask them one question: after the transcript is returned, is the audio still on your systems, and for how long? A vendor who can answer that in a sentence is fine. A vendor who sends you a policy page is telling you the honest answer without meaning to.

The uncomfortable truth in all of this is that the local route is not better engineering. It is slower, less accurate and more work, and a good engineer looking only at the code would pick the API. It wins on the one axis that does not appear in the code at all, which is that the person whose voice it is never got a say. That is not a technical argument, and it is still the right one.

Questions about this

Can you run speech to text without sending audio to a cloud API?

Yes. A Whisper model runs inside your own API process. The recording is converted to 16 kHz mono WAV on your server, read by the model in the same process, and only the resulting text leaves the machine. In the platform this piece is drawn from, the model is openai/whisper-small running on CPU in fp32.

What audio format does Whisper need?

Mono PCM at 16,000 samples per second. Browser recordings arrive as compressed WebM or Opus, so ffmpeg converts them first with -ar 16000 -ac 1 -c:a pcm_s16le. That conversion makes the file much larger, which is one reason the conversion belongs on the same machine as the model.

Is local speech to text slower than a cloud API?

On CPU, yes. The platform described here runs one transcription at a time behind a promise queue, so twenty students finishing together are transcribed in sequence rather than in parallel. It is acceptable because transcription happens after a spoken turn ends, not while the student is waiting to speak.

Does running the model locally mean the audio is deleted?

No, and it should not. The original recording is stored in the institute’s own object storage with a private ACL, because a failed transcription is retried from it later. Local processing changes who the audio is shared with, not whether it is kept.

Originally published on Boffin Coders. If you need speech, OCR or scoring that runs on hardware you control, see how AI automation is built at Boffin Coders.

Why your RAG chatbot ignores the rules you gave it

Manoj Sethi — Tue, 20 Oct 2026 13:23:05 +0000

Your support bot has one rule: never promise a refund. A customer asks for a refund. The bot promises one. The rule was in the knowledge base the whole time - it just was not in the part the model was given.

Check it yourself. context budget ÷ knowledge base size - usually under 30%

Measured in one system


Knowledge base	42,144 tokens
Was sending	first 38,000 characters - 23%
Per request	12,764 tokens
Now	136 chunks, ~3,700 tokens
Always sent	10 pinned chunks, 1,262 tokens
Scoring	BM25, k1 1.5, b 0.75

Here is a situation worth picturing before anything technical. You run a support bot. Somewhere in its knowledge base is one line: never promise a refund - refunds are decided by the billing team. A customer asks for a refund. The bot promises one. You now have a customer holding a chat transcript, and a rule that was in the file the whole time.

Nothing was jailbroken. Nobody wrote a clever prompt. The rule simply was not in the text the model received for that question, and this piece is about why that happens.

Most guides about retrieval stop at two topics. The first is chunking, which means cutting your documents into smaller pieces so that a single piece can be sent to the model. The second is embeddings, which means converting those pieces into numbers, so that a computer can measure how similar two pieces of text are. Both topics are useful and both are worth learning.

But a system that is already running in production does not usually fail at either of them. It fails somewhere else, and it fails silently. Let us go through how that happens, step by step.

Your knowledge base keeps growing. Somebody adds a new FAQ, a new policy page, a new product description. This is normal and it is a good sign. Meanwhile, a language model can only read a fixed amount of text in one request. That limit is called the context budget, and it is fixed by your provider and your plan, not by you.

At some point your knowledge base becomes bigger than that budget. From that day onward, some part of your content has to be left out of every single request. There is no way around it. The text simply does not fit.

So now something has to decide which parts get sent and which parts get dropped. This is the most important decision in your entire system, because the model can only answer using the text it actually receives. And in most systems, this decision is not made by any real selection logic. It is made by one line of code that takes the first few thousand characters of a file and throws away the rest.

Measure what your bot actually sees

Before changing anything, find out how big the problem is in your own system. You need two numbers. The first is the total size of the knowledge base you have written. The second is the size of the portion you send with each question.

# the five-minute check
# What the model actually receives, versus what you wrote.
wc -c KNOWLEDGE.md          # 164,098
# and in the route:
KNOWLEDGE_CHAR_LIMIT        # 38,000

# 38,000 / 164,098 = 23%.
# The other 77% has never been in a single request.

In the system these numbers are taken from, the result was 23 per cent. Read that again, because it is the important part. Seventy-seven per cent of everything that had been written for that assistant had never been sent to the model. Not once, for any question, by any user.

The cause was not carelessness. The provider allowed 12,000 tokens per minute. Each request was using 12,764 tokens, which is above that limit. So somebody reduced the amount of knowledge until the request fitted. Under a hard limit, that is a reasonable thing to do. The problem is that it was never revisited, and it was never called what it actually is: throwing away three quarters of your content.

Run this division on your own system before you continue reading. It takes five minutes and most teams are surprised by the answer.

Why nobody notices this

A shortened knowledge base does not produce error messages. It produces confident answers, built from whatever text happened to be included. Those answers look exactly like confident answers built from your full content. Your logs show a successful request either way. There is no alert, no exception and no red line in a dashboard. This is why the problem can sit in a live system for months.

Taking the first part of a file is not choosing

This single line decides what your model knows:

// the common default
// The version almost everyone ships first.
// It is not a bug. It is a budget, spent in file order.
return knowledge.slice(0, KNOWLEDGE_CHAR_LIMIT)

It takes the first N characters of the file and stops there. Nothing else is considered. Which sections end up inside those characters depends only on the order in which the file was assembled by your build script.

Think about what that means in practice. A visitor asks a question about video streaming. The model receives whichever pages the build script happened to write first - perhaps your company overview, your pricing page and two service pages that have nothing to do with video. The section that actually answers the question is sitting at character 140,000 of the file, and nothing in this code will ever reach it.

Notice what decides the outcome here. It is the position of the text inside the file. Something written near the top gets sent. The same text, written near the bottom, never gets sent. The question the visitor asked plays no part in it at all.

The same twelve sections, cut two ways. Switch between a fixed prefix and a score against the question.

Scoring, and why not embeddings

The fix is to give each section a score against the question that was asked, and then send only the sections with the highest scores. For a knowledge base of a few hundred sections, an algorithm called BM25 does this job well.

BM25 works on a simple idea. If a section contains the words from the question many times, it is probably relevant. But that idea alone is not enough, so BM25 applies two corrections to it.

The first correction handles repetition. If a word appears twenty times instead of five, the section is not four times more relevant. So after a certain point, extra repetitions stop adding to the score.

The second correction handles length. A very long document will naturally contain more of your words, just because it contains more words in total. So BM25 divides by document length, which stops long pages from winning simply for being long.

// the whole scorer
// Okapi BM25. Term frequency, saturated; document length, normalised.
// k1 caps how much repetition can help. b decides how much length hurts.
for (const term of queryTerms) {
  const f = doc.filter((t) => t === term).length
  if (f === 0) continue

  const n = docFrequency.get(term) ?? 0
  const idf = Math.log(1 + (docCount - n + 0.5) / (n + 0.5))

  score += idf * ((f * (K1 + 1)) / (f + K1 * (1 - B + (B * doc.length) / avgLen)))
}

That is the complete algorithm. There is nothing hidden behind it. It has been the standard baseline in information retrieval since the 1990s, it needs no model, no vector database and no API call, and over 136 sections it finishes in under a millisecond.

You may be wondering why we did not use embeddings here, since that is what most articles recommend. Embeddings compare meaning rather than words, and they are genuinely better when you have a very large number of documents - a hundred thousand or more. At that size, matching words simply stops working.

But at a few hundred sections, the list is short enough to score exactly. Adding embeddings at this size means adding a monthly bill, one more dependency in your project, and a separate index that has to be rebuilt every time your content changes. You would be paying all of that to sort a list that BM25 already sorts correctly. Add embeddings on the day you can show BM25 failing on real questions from real users, and not before that day.

BM25 does have one clear weakness, and it is worth knowing. It compares words, not meaning. If a customer types "how cheap is it", that question shares no word with a section titled "Pricing". The score will be zero. The practical fix is not complicated: write down ten or twenty synonyms for the terms your customers actually use - cheap, cost, price, budget, rate - and expand the question before scoring it. This costs nothing and closes most of the gap.

Your rules are documents too

Now back to the refund.

Your knowledge base contains rules. Never promise a refund. Never quote a price you cannot confirm. Never name another client. Say you do not know, rather than guessing. These are the most important sentences in the file, and in most systems they sit in that file as ordinary paragraphs, next to everything else.

Here is the problem. As soon as you add relevance scoring, those rules become documents like any other document in the file. They have no special status. They compete for space against product descriptions and FAQ answers, and they are judged by exactly the same measure: how many words they share with the question.

Watch what the scorer does with "can I get a refund?". Your refund policy page scores high - it is about refunds and it uses the word repeatedly. Your billing FAQ scores high for the same reason. Your cancellation terms score well too.

And the rule? Never promise a refund - refunds are decided by the billing team. Eleven words. It mentions refunds once. Against three pages that discuss refunds in detail, it ranks near the bottom. The budget fills up before it is reached, so it is not sent.

The model receives three documents explaining how refunds work, and nothing telling it who decides. It then does the reasonable thing with what it was given.

Four sections scored against “can I get a refund?”. The rule that decides the answer is the one left out.

It is worth pausing here, because the first instinct is to blame the model or to rewrite the prompt. Neither of those was at fault. The rule was stored as ordinary reference material, and it lost a relevance contest to the very documents it was written to control.

There is something uncomfortable in this. The problem gets worse as your retrieval gets better. A tighter budget and a sharper scoring algorithm both push in the same direction - more space for text that matches the question, and less space for text that does not. So a carefully tuned system is more likely to drop its own rules than a careless one. Improving the retrieval, on its own, makes this failure more likely rather than less.

Pinned context and scored context

The answer is not a better scoring algorithm. You can spend a month tuning the scores and the refund rule will still lose, because the rule barely mentions refunds. It has almost no words in common with the question. No scoring method will rank it highly, and that is not a bug in the scoring.

So the answer is to stop scoring some things at all.

Divide your knowledge base into two groups. Put in the first group everything that must be present in every request, whatever the customer asks. Your prices. Your verified numbers. Every rule about what the assistant must not say - including the refund rule.

Put everything else in the second group. Product descriptions, FAQs, guides, policy pages, help articles.

Now send the first group every time, without scoring it. Score only the second group and send whatever fits in the space that is left.

// two categories
// Two categories, not one ranked list.
const pinned = chunks.filter((c) => c.pinned)   // facts, prices, prohibitions
const pool   = chunks.filter((c) => !c.pinned)  // everything else

const selected = [...pinned]                    // never scored, never dropped
let used = pinned.reduce((n, c) => n + c.text.length, 0)

for (const { chunk, score } of scoreAgainst(question, pool)) {
  if (score <= 0) break
  if (used + chunk.text.length > budget) continue
  selected.push(chunk)
  used += chunk.text.length
}

In the system measured here, the first group is ten sections and about 1,262 tokens. That is roughly one third of every request. That one third is now reserved, permanently, for the text that must never go missing.

Now the refund question works. The refund policy, the billing FAQ and the cancellation terms all score well and get sent, exactly as before. And the rule goes too, because it was never in the competition. The model can now see both the policy and the limit on what it may promise.

Be careful not to claim too much for this. Pinning does not make a model obey you. A model can read your rule and still ignore it. That is a different problem, and it needs different solutions - testing, evaluation, and a human check on anything expensive.

What pinning fixes is narrower than that. It fixes the case where the rule was never sent at all, so the model had no chance to follow it. That is a smaller promise than the word "guardrails" usually implies. But you can check this one yourself in a minute, by printing the text that was sent and searching it for your rule. Most guardrail claims cannot be checked that easily.

A test you can run today

Take the question your strictest rule was written for. For a support bot, that is usually the refund question, or the one about cancelling a contract. Ask it. Then print the text that was retrieved for that question, and search that text for your rule. If the rule is not there, then for this question your rule does not exist. It does not matter how carefully you wrote it, how clear the wording is, or where it sits in your knowledge base.

Where this breaks

Scoring only helps when there is something to find. Some questions match nothing at all.

A customer opens with "hi, can you help me?". There is nothing to score against. A customer sends a follow-up made only of pronouns - "what about that one?" - and again there is nothing. Or somebody asks about a topic your company does not cover, so no section matches because no section exists.

In all three cases every section scores zero. The model then receives only your pinned text and nothing else. That is worse than the simple version you started with, because that one at least sent something every time.

Two protections handle this, and neither takes long to build.

The first is to build the search query from the last two customer messages instead of only the latest one. Then a short follow-up like "what about that one?" still carries the subject from the message before it.

The second is to set a floor. When scoring returns very little, keep adding sections in file order until you reach a minimum amount of text. In the system measured here, a question that matches nothing still sends about 2,950 tokens, against 3,700 for a question that matches well. The floor makes sure the model is never left with almost nothing.

Pinning also has a cost, and you should keep an eye on it. Every pinned section is space the question cannot use. If your pinned group keeps growing, it will slowly squeeze out the scored content, and you will be back to sending a fixed block of text no matter what was asked.

Keeping the pinned group near one third of the budget is a reasonable target. If yours is above one half, look at what is in there. Some of it is probably reference material that somebody pinned because it felt important, not because it is a rule.

Finally, be clear about what none of this does. It does not make an assistant truthful, and it does not remove the need to test one properly. It removes one failure that was previously silent. We build retrieval-grounded assistants on the assumption that grounding is something you have to keep checking, not something you switch on once and forget about.

Check your own system

Three steps. None of them takes long.

Step one. Do the sum. Find your context budget - the amount of text you send with each question. Then find the total size of your knowledge base. Divide the first by the second. That percentage is how much of your own content the model ever sees. If it is low, everything in this article applies to you.

Step two. Look at what one question pulls. Pick any question and print the list of sections that were selected for it. Do not read the answer. Read the list. The answer will look fine either way, which is why it tells you nothing.

# what a question retrieves
$ explain "can I get a refund?"

  23.56  Refund policy
  22.13  Billing FAQ
  22.12  Cancellation terms
  16.08  Plans and limits
   2.31  Rule: never promise a refund      ← not sent
  → 3,625 tokens sent

$ explain "what about that?"

  → 2,950 tokens sent   (nothing scored; the floor filled it)

Step three. Test two specific questions. These two are the ones that matter.

First, think of your strictest rule. For a support bot it is usually the refund rule, or the one about cancelling a contract. Now ask the question a customer would ask when that rule applies - "can I get a refund?". Print the list. Is your rule in it?

Second, type the vaguest thing a real customer might open with. Something like "hi, I need some help". Print the list again. Did anything come back at all?

The first test tells you whether your rules are reaching the model. The second tells you what your bot does when it has nothing to work with.

Most teams get a surprise from at least one of these two, and that is exactly why it is worth doing. Neither failure can be seen from outside. A model working with half the text it needed still replies in full sentences, and it still sounds completely sure of itself.

One idea to take away. Your knowledge base holds two different kinds of text. Some of it is material the model should look at when a question calls for it. The rest is rules that must be there every time, whether they look relevant or not. Score the first kind. Never score the second.

Questions about this

Why does my RAG chatbot ignore its instructions?

Often the instruction never reached the model. If your rules are stored in the same knowledge base that retrieval selects from, they compete for space with everything else. On a question where the rule matters, the rule itself may contain none of the question’s terms, so it scores low and is left out.

How much of my knowledge base does the model actually see?

Divide your context budget by the size of your knowledge base. Many systems send under 30 per cent, and the part that is sent is often just the first N characters of the file rather than the sections that answer the question.

Do I need embeddings, or is BM25 enough?

BM25 is enough for a few hundred sections and needs no model, vector database or API call. Embeddings earn their cost at very large document counts. Add them when you can show BM25 failing on real questions from real users.

How do I stop a chatbot dropping its own rules?

Split the knowledge base into pinned and scored. Facts, prices and prohibitions go into every request without being scored. Everything else competes on relevance. Keep the pinned block to roughly a third of the budget so retrieval still has room.

Originally published on Boffin Coders. If your assistant has rules that must reach the model on every question, see how generative AI and LLM development is built so that grounding can be printed and checked.

Your rebuild will drop URLs. Google will tell you in six weeks.

Manoj Sethi — Thu, 15 Oct 2026 13:23:08 +0000

A rebuild changes how pages are made. The old addresses stop existing without anyone deciding they should, and Google notices before you do. Here is the check that proves no URL moved, the four things it cannot see, and the failure that cost more than any of them - from a 10,723-page site that went through it.

Check it yourself. curl the sitemap before and after, then diff the URL sets

The migration


Site	timezones.in - 10,723 pages
Templates	six
Migration	to Next.js 16, one week
URLs lost	none
Snapshots	6 September 2026, either side

The site itself

Nobody rebuilds a website intending to lose its pages. It happens anyway, and it happens in a specific way: an address gets tidied because the new one reads better, a template gets a cleaner path, a folder is dropped because the new system does not need it. Every one of those decisions is sensible on its own. Together they are why traffic falls off a cliff six weeks after a launch that nobody thought was risky.

One word before the example. A page’s URL is its address - the part after the domain name, like /word/serendipity. Google’s memory of a page is tied to that exact address. Change it, and as far as Google is concerned the old page has died and a new, unknown page has appeared. The years of ranking the old address earned do not move across by themselves.

A dictionary site rebuilds

Take an online dictionary. It has forty thousand pages, one per word, and none of them was written by hand. A template builds each page from a database row: the word, its meaning, its pronunciation. The address of each page is built the same way - a small function that takes the word and turns it into /word/serendipity.

The site is rebuilt on a new framework. The developer, tidying as she goes, notices that the old addresses have an untidy trailing part that the new system does not need, and simplifies the function. /word/serendipity-1 becomes /word/serendipity. Cleaner. Better. And forty thousand addresses have just changed at once, because they all come from one line of code.

Nothing warns her. The build passes. The new site looks right, because it is right on its own terms. The old addresses simply stop existing, and the only party who notices is a search engine that will not say anything for weeks.

Why nobody notices for six weeks

A dropped address does not throw an error. No test goes red. The people who built the site are looking at the new site, where every link works. The people who would hit the old addresses - visitors arriving from Google, from old bookmarks, from links on other sites - are strangers, and strangers do not file bug reports. They see “page not found” and leave.

Google does notice, slowly. It re-visits the old addresses, finds nothing, and after a few weeks removes them. The traffic graph bends downwards. By then the team has shipped a fortnight of other changes, and the question “which of these did it?” has no cheap answer. The fix has to happen before the launch, or it costs ten times as much afterwards.

Why “keep the URLs” is not a plan

Everyone says it at the start of a rebuild. On a twenty-page site it even works, because twenty is a number a person can hold in their head and check by eye before launch.

On a site with thousands of pages built from templates, it is exactly where things go wrong. The templates are the danger: change one line in the function that builds an address and every page in that family moves at once. The blast radius of a one-character edit is the whole site. So “keep the URLs” has to become something a machine checks, not something a person promises.

There are two halves to that, and they are different jobs. The first is a test inside the build that fails when an address, a title or a heading changes - that protects you while you work. The second is proving, from outside, that the site which actually went live kept them. This piece is about the second, because it is the half you can run against any site, including one you did not build.

The check: two lists, compared

It is deliberately simple. Every site that cares about search already publishes a complete list of its own addresses. It is called a sitemap, it lives at /sitemap.xml, and search engines read it to learn what the site contains. Because it is a full list of addresses, it doubles as a record of what the site looked like on a given day. Save it before the launch, and again after:

# two snapshots
# Before the deploy, and again after it. Nothing clever  - 
# the sitemap is the list of URLs you already publish.
curl -s https://example.com/sitemap.xml > before.xml

# ...deploy...

curl -s https://example.com/sitemap.xml > after.xml

Then compare the two lists. Not the totals - the lists. A total that matches can still hide one page dropped and another added, which is the case that hurts most, because the number looks reassuring.

# diff.py - twelve lines
import re, sys

def urls(path):
    xml = open(path, encoding='utf-8', errors='ignore').read()
    return set(re.findall(r'<loc>([^<]+)</loc>', xml))

before, after = urls(sys.argv[1]), urls(sys.argv[2])

print('before:', len(before))
print('after: ', len(after))
print('gone:  ', len(before - after))

# The count is reassurance. The list is the answer.
for u in sorted(before - after):
    print('  DROPPED', u)

Three numbers and a list. The list is the part that matters: it names every address that existed before the launch and does not exist after it, which is the question everyone asks two months too late.

Family	Before	After
/convert/	9,045	9,045
/time/	1,000	1,000
/dst/	248	248
/country/	247	247
/abbreviation/	136	136
/utc/	38	38
static	8	9
Total (one page added, none removed)	10,722	10,723

Before and after, per template family, from a real migration. One page appeared - a new static route - and nothing was removed.

What the check cannot see

A passing diff should not make anyone relax, and this is the part usually left out. An address is one of at least five things a rebuild can change, and the sitemap only knows about one of them.

Everything a rebuild touches	Detail	Sitemap diff
The URL	`/convert/EST_to_FJT`	a diff catches this
The page title	what the result shows in search	a diff does not
The H1	the heading Google matches against the query	a diff does not
The canonical	which version of the page counts	a diff does not
Internal links	how authority moves between pages	a diff does not
The redirect map	what happens to anything that did move	a diff does not

A sitemap lists addresses. Everything else that decides whether a page keeps its rankings is invisible to it.

A page can keep its address and lose its title, its main heading, its “canonical” tag - the line in the page that tells Google which address is the official one when several show the same content - and every internal link that pointed at it. The diff returns zero and the rankings still go. A build test can cover titles and headings too. The canonical tag and the links between pages are usually guarded by nothing but attention, which is not a guarantee and should not be described as one.

Where this breaks

The sitemap must come from the same place as the pages. If it is written by hand, or built by a separate job, it can describe a site that no longer exists and the diff will happily confirm a fiction. It only works as evidence when the file is generated from the same data that builds the pages.

An address in the sitemap can still be broken. Present in the file, missing on the server: the diff passes. So take a sample of the survivors and check what they actually return:

# sample forty, expect 200
# A URL can survive the diff and still be broken: present in the
# sitemap, 404 at the server. Sample the survivors.
shuf -n 40 <(grep -o '<loc>[^<]*' after.xml | sed 's/<loc>//') |
  xargs -P 8 -I{} sh -c 'printf "%s %s\n" "$(curl -s -o /dev/null -w %{http_code} "{}")" "{}"' |
  grep -v '^200' || echo "all sampled URLs returned 200"

Pages outside the sitemap are outside the check. Old campaign pages, PDFs, anything only ever linked from an email - none of it is in the file, so none of it is protected. For those, the equivalent snapshot is a Search Console export of every page that had impressions, taken before the work starts.

And none of it matters if the site is down. The site these numbers come from was unreachable for about a week shortly before the migration - not a proper “we are down, come back later” answer, which tells a search engine to wait, but a dead connection, which tells it nothing. Every address was preserved perfectly throughout, and preserved addresses on an unreachable server are worth exactly nothing. The fix afterwards was ordinary: the web server now shows a maintenance page with a 503 status and a “retry after” header, so an outage looks like an outage rather than an absence. That was the more expensive lesson of the two.

The site these numbers come from

The dictionary is made up. The migration is not. On the day of the move, timezones.in had 10,723 pages generated from six templates - 9,045 of them time-conversion pages built from one address function, which is exactly the one-line blast radius described above. It moved to Next.js 16 in one week in September 2026. The first sitemap snapshot was taken on the morning of 6 September and the second after the deploy the same day: 10,722 addresses before, 10,723 after, none dropped. The build test stopped the addresses moving; the diff proved they had not; and the second of those is checkable by anyone, which is the next section. Whether the rankings held is a separate claim. The site was only verified in Search Console that month, so there is no “before” to compare against - an honest gap, and one worth writing down rather than leaving out.

Run it on your own rebuild

The whole method is two downloads and twelve lines of Python, and it works on any site with a sitemap - including one you are being asked to take over from another agency. Take the first snapshot before anyone touches anything. It is worthless afterwards, and afterwards is when you will want it.

You can run the same check against the site above. Its sitemap is public and split by template family: timezones.in/sitemap.xml. The fuller story of that site - what it is, what it cost, what else went wrong - is on its project page.

The habit to take away is smaller than the tooling. Before any change that touches how pages are made, save the list of addresses the site publishes today. After the change, compare. If the list is the same, you have proved one thing and only one thing - that the addresses survived - and that is still the one thing most teams find out the hard way, from a graph, six weeks after it was cheap to check.

Questions about this

Why did my site lose traffic after a redesign?

Usually because page addresses changed. A new framework or template tidies a URL, the old address stops existing, and Google drops the page from its index. Nothing in the build fails, so the first sign is a traffic graph a few weeks later.

How do I check that a website migration kept all its URLs?

Download the sitemap before the change and again after, then compare the two lists of addresses - not the totals. Every address in the first list that is missing from the second is a page you have lost. Twelve lines of Python do it.

Is comparing sitemaps enough to protect rankings?

No. A sitemap only lists addresses. A page can keep its address and lose its title, its heading, its canonical tag or the links pointing at it, and rankings go with them. The sitemap check catches one of at least five things a rebuild can move.

What is a sitemap?

A file, usually at /sitemap.xml, in which a website lists every page it wants search engines to know about. Because it is a complete list of addresses, it doubles as a record of what the site looked like on a given day.

Originally published on Boffin Coders. If you are about to move a large site to a new framework, you can hire a Next.js developer who has run this check on a live migration.

How to Tell Whether Google Actually Indexed What You Published

Manoj Sethi — Thu, 08 Oct 2026 13:23:06 +0000

Publishing a new blog, landing page, or product page is only the first step. Before expecting organic traffic, one must find out if Google has actually found, crawled and indexed the published page.

One should note that crawling and indexing are two different terms. Google may find a URL but may not add it to its search index. Google also does not guarantee the indexing of every crawled page.

So, how can one check their new page's status? There are some easy methods of the published page's status verification.

Crawling vs. Indexing: What Is the Difference?

Google Search generally works through three stages: crawling, indexing, and serving results.

Stage	What happens?
Crawling	Googlebot discovers and visits your URL
Indexing	Google processes the page and decides whether to add it to its index
Serving	Google may show the indexed page in search results for relevant searches

A page can therefore be live on your website without being indexed. Meeting Google's technical requirements also does not automatically guarantee indexing.

1. Use Google Search Console First

The best approach to check Google index status of a page is to use Google Search Console.

First open the Search Console and use the URL inspection tool. In this case, copy and paste the URL of the page.

Some insights that could be received via this tool are:

Whether the given URL is indexed
When the last crawling was done
The canonical version of the same page
Some crawling and indexing issues
Whether it’s possible to run the test of the page as the live one

For understanding the index status of an individual page, it is suggested by Google to use the URL Inspection tool. For website owners, Google Search Console can also serve as a reliable Google indexing checker because it provides information directly from Google's systems.

What the Result Means

Search Console result	What it tells you
URL is on Google	Google has indexed the URL
URL is not on Google	The page is currently not indexed
URL has issues	A technical or indexing problem may need attention
URL is available to Google	Google can potentially crawl the page, but this does not guarantee indexing

Don't confuse "Google can access this page" with "Google has indexed this page." They are not the same thing.

2. Try a site: Search

You can conduct a fast manual verification by searching with Google:

site:yourdomain.com/page-url

Another search you can perform is

site: yourdomain.com

Using these searches will enable you to see which pages of your website have been indexed. Google itself supports the use of the site operator as a conventional method of finding out if pages have been indexed.

But it is crucial to point out that using the site search does not guarantee that every single URL will be visible. Thus, Google suggests using the URL Inspection tool as the more suitable method for resolving page-related issues.

Therefore, you should consider the site search as a preliminary check only.

3. Don't Assume "Not Ranking" Means "Not Indexed"

It is a frequent misconception about SEO. This is why using a Google indexing checker should be the first step before assuming that a page has an indexing problem.

The process of indexing indicates that Google has taken the page into its index, which doesn’t mean the page will immediately appear at the top of search results.

Google employs a wide range of technologies and signals to establish which indexed pages will appear for specific queries.

How Are Indexing and Ranking Different

Question	What it means
Is my page indexed?	Can Google include the page in its search index?
Is my page ranking?	Is Google showing it for relevant searches?
Is my page getting traffic?	Are people actually clicking and visiting it?

Keeping these three questions separate makes SEO reporting much easier.

4. Check the Page Indexing Report

Managing a larger website can result in the impracticality of monitoring all the URLs individually.

To make things easier, the Google Page Indexing report from Google Search Console gives you a wider overview of the pages that Google has crawled, indexed, or disregarded.

Thus, you can spot things like

Pages that are not indexed.
Pages that are not crawled properly.
Duplicate URLs.
Pages affected by the noindex attribute.
URLs discovered by Google but not indexed.

This report is especially useful after publishing multiple blogs or introducing a new section of your website.

5. Check for Common Indexing Problems

If you cannot check Google index status successfully, start by reviewing the technical factors that may be preventing Google from accessing or processing the page.

Checklist For Technical Issues

Can Googlebot access the page?
Does the URL show a responsible HTTP 200 response?
Is there a noindex instruction?
Is the page banned by robots.txt?
Does the page include materials that can be indexed?
Is the canonical URL leading to another location?
Is Google able to reach the URL using the sitemap or links within the site?

As described by Google's standards, Google needs to get access to the page, have it function fine, and provide materials that can be indexed.

6. Use Your Sitemap to Help Google Discover Pages

Sitemaps provide Google access to important URLs of the website and facilitate URL discovery on websites that are large or new but submitting a URL through a Sitemap does not provide a guarantee of indexing.

A Sitemap serves the purpose of allowing Google to discover your content.

Rather, you can think of a Sitemap as a tool to help Google to find and discover the content you have created instead of a tool to ensure that it actively indexes the URLs.

7. Request Indexing When Appropriate

If you've updated or published a major webpage, you can ask Google to crawl that page through the URL Inspection tool in Search Console.

While this works on a page-by-page basis, please keep in mind that there is no guarantee regarding the timing of indexing.

Google states that crawling can take between a few days and a few weeks, and making multiple requests will not speed up Google’s crawlers.

A Simple Google Indexing Checklist

Before assuming that your newly published page is invisible to Google, run through this checklist:

Check	What to do
URL Inspection	Check the URL in Search Console
Google Search	Try a site search
Google Page Indexing	Look for exclusions or errors
Technical setup	Check robots.txt, status code, and noindex
Internal links	Make sure relevant pages link to the new URL
Sitemap	Confirm the URL is included where appropriate
Wait and monitor	Give Google time to crawl and process the page

Need Help With Your Website's Indexing?

While regularly publishing content may be an important factor, technical SEO is what plays a crucial role in enabling search engines to find and understand that content. If your website suffers from a series of indexing issues, unexplained exclusions or a number of pages that are not indexed in Google, a technical audit will identify the causes.

At Boffin Coders, we create websites that take into account the company’s SEO, development, structure, performance, and future growth. Our team of in-house specialists is ready to examine the technical aspects of your website and help improve the digital infrastructure of your web resource.

If you want to get assistance with the development of a website with SEO in mind or make technical improvements, don’t hesitate to turn to Boffin Coders.

Originally published on Boffin Coders. If you want a technical team to find out why your pages are not indexed, see SEO and digital marketing at Boffin Coders.

You do not need a vector database for your first RAG chatbot

Manoj Sethi — Tue, 06 Oct 2026 13:23:02 +0000

You want a chatbot on your website that answers questions from your own help pages. There are forty of them. You search for how to build it, and every tutorial starts the same way: convert your pages into embeddings, store them in a vector database, pay for both every month. Before you do any of that, read this. For forty pages - or four hundred - you probably do not need it.

What a RAG chatbot actually does

RAG stands for retrieval-augmented generation. The plain-English version: the AI model does not know your content. So before it answers, your system finds the few pages that match the question and pastes them into the prompt along with the question. The model reads those pages and answers from them.

That is the whole idea. Find, then answer. Everything else is detail. And the only real engineering question inside it is the first word: how do you find the right pages?

Two ways to find

Take a real question a customer types: “can I get a refund?”

Keyword scoring. Count how often the question’s words appear on each page. The refund policy page mentions “refund” six times, so it scores highest. The billing FAQ mentions it three times. The cancellation page twice. Send the top three to the model. This is the same maths that has powered search boxes since the 1990s. It is called BM25, it is about forty lines of code, and it runs on your own server in under a millisecond.

Embeddings. Turn every page into a long list of numbers that represents its meaning, do the same to the question, and find the pages whose numbers are closest. To do that you call an embedding API (your text goes to the provider), store the numbers in a vector database, and query it for every question.

Look at what embeddings buy you in that picture. One row. The “money-back guarantee” page does not contain the word “refund”, so keyword scoring misses it and embeddings find it. That is the advantage - pages that mean the same thing in different words. It is a real advantage. It is also a smaller one than the tutorials suggest, and there is a cheaper fix for most of it.

What the vector database costs you

Not just money, though there is that. Here is what you take on the day you add one:

An API call for every page and every question. Your content and your customers’ questions leave your server to be turned into numbers. For a German or Dutch client, that sentence alone can end the project.
A database to run or rent. One more service with its own password, its own outage, its own bill.
Re-embedding when you edit. Change a page, and its numbers are stale until you regenerate them. Forget once, and the bot answers from last month’s policy.
Debugging you cannot see. When keyword scoring picks the wrong page, you can print the scores and see why. When embeddings pick the wrong page, you get a similarity of 0.81 and a shrug.

None of these is fatal. All of them are work that a forty-page chatbot does not need.

Numbers from a system that runs this way

The assistant on this website answers from 136 chunks of text - service pages, prices, hiring terms, a few rules. Retrieval is BM25 with the standard settings (k1 of 1.5, b of 0.75), a small synonym list so “price” also matches “cost” and “rate”, and a title boost so a chunk whose heading matches the question scores higher. A typical question sends about 3,700 tokens to the model. Retrieval takes under a millisecond. There is no vector database, no embedding API, and nothing about your question leaves the server until the final call to the model.

One more thing that design gets right, and it matters more than the retrieval method: the rules do not compete for space.

Prices, hours and the rule “never promise a refund” sit in a pinned block that is sent with every request and never scored. Only the remaining 126 chunks compete for the rest of the budget. If you skip this, a rule can lose its place to an FAQ page on the very question where the rule mattered. The full story of that failure is in why RAG chatbots ignore your rules.

When you do need embeddings

Be honest about the crossover, because it exists:

Thousands of documents. Past a few thousand chunks, keyword scoring starts returning too many near-ties and you want meaning to break them.
Users who paraphrase heavily. Support for a product with lots of jargon, where customers describe the problem in their own words and never use your terms.
More than one language. A German question against English pages will not match on keywords.
You measured it. This is the real test. Log a hundred real questions from real users. For each one, look at which chunks BM25 returned. Count the misses. If it is under ten, you are done. If it is thirty, you have earned the vector database, and you will know exactly why.

Until you have that log, adding embeddings is guessing with a monthly bill attached.

The cheap fix for paraphrase

Most of the “different words, same meaning” problem in a small knowledge base is a synonym list. Twenty lines:

// synonyms.js
const SYNONYMS = {
  refund: ['money back', 'money-back', 'reimburse'],
  price: ['cost', 'rate', 'fee', 'charge'],
  start: ['onboard', 'begin', 'kick off'],
}
// Expand the question before scoring it.

Every time you find a missed question in your log, add a line. After a month the list stops growing, because your customers use a smaller vocabulary than you fear.

Start here

Split your pages into chunks of a few hundred words, one idea each.
Score with BM25. Any language has a library. It is also small enough to write yourself.
Put rules, prices and prohibitions in a pinned block that is always sent.
Log every question and the chunks it returned.
Add synonyms as the log shows misses.
Add embeddings only when the log proves the misses are about meaning, not words.

That order costs nothing to run, keeps the data on your own machine, and gives you a working assistant this week. The vector database will still be there when you need it, and by then you will be able to say why - which is the difference between building a system and following a tutorial. If you would like the version of this that runs on your own documents, that is what our generative AI work looks like, and it is built by our AI engineers.

Originally published on Boffin Coders. If you want to see what happens when the rules are left to compete with the scored pages, read why RAG chatbots ignore your rules.

How to split large Excel files in Node.js: a beginner's guide

Manoj Sethi — Sat, 03 Oct 2026 06:18:33 +0000

You have an Excel file that is too large to open comfortably, import or email, and you need it as several smaller files, each with the same header row at the top. In Node.js that takes one short script. This guide builds it step by step, then gives you the version to use when a file is so large that reading it all at once runs out of memory.

What you need

Node.js installed. Any current long-term support release works.
A basic idea of how to run a JavaScript file from the terminal.
The Excel file you want to split. The examples call it input_file.xlsx.

Step 1: set up the project

Create a folder for the project and start a Node.js project inside it:

mkdir split-excel
cd split-excel
npm init -y

Then install SheetJS, the library that reads and writes Excel files. Install it the way the SheetJS installation guide says, from its own CDN. The copy on the public npm registry stopped at version 0.18.5, so npm install xlsx gives you an old release. Copy the install line from the guide, which always carries the current version. It looks like this:

npm install https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz

Step 2: write the script

Create a file called split_excel.js in the folder and paste this in:

// split_excel.js
const fs = require('fs')
const path = require('path')
const XLSX = require('xlsx')

function splitExcelFile(filePath, rowsPerFile = 1000, outDir = 'output') {
  if (!fs.existsSync(filePath)) throw new Error(`File not found: ${filePath}`)

  // cellDates keeps dates as dates instead of serial numbers
  const workbook = XLSX.readFile(filePath, { cellDates: true })
  const sheetName = workbook.SheetNames[0]
  const rows = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName], {
    header: 1, // an array per row, so the header is row 0
    defval: '', // keep empty cells, so columns do not shift
  })
  if (rows.length < 2) throw new Error('The first sheet has no data rows')

  const [header, ...data] = rows
  const baseName = path.parse(filePath).name
  fs.mkdirSync(outDir, { recursive: true })

  const totalFiles = Math.ceil(data.length / rowsPerFile)
  for (let i = 0; i < totalFiles; i++) {
    const chunk = data.slice(i * rowsPerFile, (i + 1) * rowsPerFile)
    const sheet = XLSX.utils.aoa_to_sheet([header, ...chunk], { cellDates: true })
    const book = XLSX.utils.book_new()
    XLSX.utils.book_append_sheet(book, sheet, sheetName)

    const outFile = path.join(outDir, `${baseName}_part_${i + 1}.xlsx`)
    XLSX.writeFile(book, outFile, { compression: true })
    console.log(`Created ${outFile} (${chunk.length} rows)`)
  }
  return totalFiles
}

const [, , input = 'input_file.xlsx', rows = '1000'] = process.argv
try {
  splitExcelFile(input, Number(rows))
} catch (err) {
  console.error(err.message)
  process.exit(1)
}

What each part does:

readFile with cellDates: true opens the workbook and keeps date cells as dates. Without it, dates come back as serial numbers such as 45583, and the split files show numbers where the dates were.
sheet_to_json with header: 1 returns each row as an array, so the first array is the header row.
defval: '' keeps empty cells. Without it, a blank cell in the middle of a row is dropped and every value after it moves one column to the left.
The loop takes the data in slices of rowsPerFile rows, puts the header back on top of each slice, and writes each slice as a new workbook in the output folder.
compression: true makes the output files smaller. SheetJS writes uncompressed files unless you ask.

Step 3: run it

Put your Excel file in the same folder, then run the script with the file name and the number of rows you want in each file:

node split_excel.js input_file.xlsx 10000

On a file with 25,000 data rows it prints:

Created output/input_file_part_1.xlsx (10000 rows)
Created output/input_file_part_2.xlsx (10000 rows)
Created output/input_file_part_3.xlsx (5000 rows)

Each file opens in Excel with the original header on the first row. If the file name is wrong, the script stops with "File not found" instead of a long error.

When the file is too large to load at once

The script above reads the whole workbook into memory before it writes anything. That is fine for most files. For a very large one, the process can run out of memory, or take so long that it looks stuck.

We measured both approaches on the same test file: 300,000 rows, five columns, 62 MB, on a laptop running Node.js 22. The script above peaked at about 990 MB of memory and took 11.6 seconds. The streaming version below peaked at about 220 MB and took 4.8 seconds. Its memory stays roughly flat as the file grows, because it holds one row at a time instead of the whole sheet.

Measured on one test file

Streaming holds one row, not the whole sheet

	Read the whole file (SheetJS)	Stream row by row (ExcelJS)
Peak memory	About 990 MB	About 220 MB
Time	11.6 seconds	4.8 seconds
As the file grows	Memory grows with it	Memory stays roughly flat
Script length	41 lines	60 lines
Use it for	Most files, and quick changes	Very large files, or a small server

Streaming needs a different library, ExcelJS, which can read and write a workbook one row at a time. Its documentation covers the streaming reader and writer. Install it in the same folder:

npm install exceljs

Then create split_excel_stream.js:

// split_excel_stream.js
const fs = require('fs')
const path = require('path')
const ExcelJS = require('exceljs')

async function splitLargeExcelFile(filePath, rowsPerFile = 100000, outDir = 'output') {
  if (!fs.existsSync(filePath)) throw new Error(`File not found: ${filePath}`)
  fs.mkdirSync(outDir, { recursive: true })

  const baseName = path.parse(filePath).name
  const reader = new ExcelJS.stream.xlsx.WorkbookReader(filePath, {
    sharedStrings: 'cache', // resolve text cells
    styles: 'cache', // needed to recognise dates
    hyperlinks: 'ignore',
    worksheets: 'emit',
  })
  let header = null
  let outFile = ''
  let writer = null
  let sheet = null
  let part = 0
  let count = 0

  async function closePart() {
    if (!writer) return
    sheet.commit()
    await writer.commit()
    console.log(`Created ${outFile} (${count} rows)`)
  }

  async function openPart() {
    await closePart()
    part += 1
    count = 0
    outFile = path.join(outDir, `${baseName}_part_${part}.xlsx`)
    writer = new ExcelJS.stream.xlsx.WorkbookWriter({ filename: outFile })
    sheet = writer.addWorksheet('Sheet1')
    sheet.addRow(header).commit()
  }

  for await (const worksheet of reader) {
    for await (const row of worksheet) {
      if (!header) {
        header = row.values // the first row is the header
        continue
      }
      if (!writer || count === rowsPerFile) await openPart()
      sheet.addRow(row.values).commit() // written to disk, then released
      count += 1
    }
    break // first sheet only
  }
  await closePart()
  return part
}

const [, , input = 'input_file.xlsx', rows = '100000'] = process.argv
splitLargeExcelFile(input, Number(rows)).catch((err) => {
  console.error(err.message)
  process.exit(1)
})

How it differs from the first script:

The reader is a stream. WorkbookReader hands you one worksheet, then one row, at a time. styles: 'cache' lets it recognise date cells, and sharedStrings: 'cache' turns text cells back into text.
Each row is committed as soon as it is added. commit() writes the row to the output file and releases it, so memory does not grow with the file.
A new output file opens every rowsPerFile rows, with the header written first.

Run it the same way:

node split_excel_stream.js input_file.xlsx 100000

Which one to use

Start with the first script. It is shorter and easier to change. Move to the streaming version when loading the file takes more memory than the machine can spare, or when the script runs on a small server next to other work.

Common problems

Dates come out as numbers. Read with cellDates: true, as the first script does. In the streaming version, keep styles: 'cache'.
Values move one column to the left. A blank cell was dropped. Keep defval: '' in sheet_to_json.
You need a different sheet. Both scripts split the first sheet only. In the first script, change SheetNames[0] to the position of the sheet you want.
The script slows down your web server. Splitting a file is CPU work, and in a server process it holds up every other request. Run it in a worker thread or a separate process. How to move CPU-heavy work off the Node.js event loop shows how.

The code

The original script is in our public repository, boffincoders/excel-splitter. The versions on this page add the output folder, the command-line arguments, date and blank-cell handling, a clear error when the file is missing, and the streaming option. Both were run against the test files described above before they went on this page.

If splitting files is one step in something larger, such as a nightly import, a report that feeds another system, or spreadsheets that customers upload every week, the script is the easy part, and the work is making it run unattended: retries when a file arrives half written, a log that shows which rows failed, and an alert when it stops. That is the kind of job Node.js developers by the month take on inside your own repository, and it is usually a few days of work rather than a project.

Originally published on Boffin Coders. If you want a job like this running unattended inside your own repository, you can hire Node.js developers by the month.