Building semantic search over your TestRail cases

I've written about why a test portfolio needs semantic search and what the pattern looks like. The question I get after that one is always the same: fine, but what does it actually take to build?

Less than people expect. What follows is the shape of the implementation I run — trimmed to the parts that matter and rewritten against public APIs, so you can lift it into your own stack. Python, a local embedding model, ChromaDB. Swap TestRail for Xray, Zephyr, qTest or a homegrown suite and the structure doesn't change.

Before the code, the part worth reading if you're deciding whether to fund this at all: what you get is a search that answers "have we tested this before?" across a portfolio nobody can hold in their head anymore. On a portfolio of 900+ cases, that's the difference between a real coverage picture and one where a ~45% gap sits unnoticed for months because nobody phrased the search the way the case was titled.

The one architectural decision that matters

Don't embed straight from the API. Pull your cases into local storage first — SQLite is plenty — keeping each record's raw JSON payload alongside the normalized columns. The vector index is then built from that local copy, not from the network.

This looks like an extra step and pays for itself immediately. Re-indexing doesn't re-hammer the API. You can change your document format and rebuild in seconds. And when a retrieval result looks wrong, you have the exact payload that produced it sitting on disk. The vector store becomes a derived artifact — throwaway and rebuildable — rather than a second source of truth you have to keep honest.

So: read-only pull into SQLite, then everything below runs locally.

Step 1 — The document format

This is the step that decides whether the whole thing works, and it's the one people skip. Embedding just the title gives you a search barely better than keywords, because a title like "Payment declined — stale card token" carries almost none of the behavior being tested. The steps do.

def encode_case(raw: dict[str, Any]) -> str:
    """Return a plain-text document for a TestRail case row."""
    title = str(raw.get("title", "")).strip()
    refs = str(raw.get("refs", "") or "").strip()
    steps = _steps_to_plain(raw.get("custom_steps_separated"))
    preconds = strip_html(str(raw.get("custom_preconds", "") or ""))

    parts = [f"Title: {title}"]
    if refs:
        parts.append(f"Refs: {refs}")
    if preconds:
        parts.append(f"Preconditions: {preconds[:300]}")
    if steps:
        parts.append(f"Steps: {steps[:600]}")
    return "\n".join(parts)


def _steps_to_plain(steps_json: str | None) -> str:
    """Flatten the custom_steps_separated JSON array into text."""
    if not steps_json:
        return ""
    try:
        steps = json.loads(steps_json) if isinstance(steps_json, str) else steps_json
    except (json.JSONDecodeError, TypeError):
        return ""

    parts: list[str] = []
    for step in steps[:6]:  # cap at 6 steps to keep doc size reasonable
        content = strip_html(str(step.get("content", "")))
        expected = strip_html(str(step.get("expected", "")))
        if content:
            parts.append(content)
        if expected:
            parts.append(f"Expected: {expected}")
    return " ".join(parts)

Four decisions in there worth stealing. Label the sections — Title:, Steps:, Expected: — because the labels themselves carry signal and keep a long precondition from reading like part of a step. Strip the HTML, since test management tools happily store markup in text fields and <p> tags are pure noise in an embedding. Cap everything: six steps, 300 characters of preconditions, 600 of steps. A 40-step end-to-end case embedded in full drifts toward a mush that matches everything and means nothing; the first few steps carry the identity of the test. And include the refs — the linked ticket keys are a cheap bridge between a case and the requirement it came from.

What to leave out: priority, assignee, last run status. They add tokens and no meaning, and they pull unrelated cases together in vector space just because they share a priority.

Step 2 — The store

One collection, cosine distance, embedding function attached to the collection so queries can be plain text.

class VectorStore:
    """Persistent ChromaDB store with sentence-transformers embeddings."""

    def __init__(self, chroma_path: Path, model_name: str = "all-MiniLM-L6-v2") -> None:
        import chromadb
        from chromadb.utils.embedding_functions import SentenceTransformerEmbeddingFunction

        self._ef = SentenceTransformerEmbeddingFunction(model_name=model_name)
        self._client = chromadb.PersistentClient(path=str(chroma_path))
        self._cases = self._client.get_or_create_collection(
            name="cases",
            embedding_function=self._ef,
            metadata={"hnsw:space": "cosine"},
        )

A general-purpose sentence-transformer running locally is good enough. You're not chasing a leaderboard, you're beating "zero hits on a paraphrase" — and local means no test-case content leaves your network, which removes an entire conversation with security.

Step 3 — Sync, not rebuild

Here's the part that turns a demo into something you can run on a schedule: a change sentinel. Store a hash of each record's remote payload in the vector metadata, and on every sync compare before you re-embed.

    def sync_cases(self, conn: sqlite3.Connection) -> dict[str, int]:
        """Embed new/changed cases from SQLite. Returns {embedded, skipped, deleted}."""
        rows = conn.execute(
            "SELECT id, title, refs, custom_steps_separated, raw_json, remote_sha256 "
            "FROM cases WHERE COALESCE(is_deleted, 0) = 0"
        ).fetchall()

        existing = self._cases.get(include=["metadatas"])
        existing_sha = {
            doc_id: (meta.get("sha256", "") if meta else "")
            for doc_id, meta in zip(existing["ids"], existing["metadatas"])
        }

        ids, docs, metas, current_ids = [], [], [], set()
        for case_id, title, refs, steps, raw_json_str, sha256 in rows:
            doc_id = f"case_{case_id}"
            current_ids.add(doc_id)
            if existing_sha.get(doc_id) == (sha256 or ""):
                continue  # unchanged — skip the expensive part

            # raw_json is the source of truth; normalized columns fill the gaps
            raw = json.loads(raw_json_str) if raw_json_str else {}
            raw.setdefault("title", title)
            raw.setdefault("refs", refs)
            raw.setdefault("custom_steps_separated", steps)

            ids.append(doc_id)
            docs.append(encode_case(raw))
            metas.append({
                "case_id": case_id,
                "title": str(title or ""),
                "refs": str(refs or ""),
                "sha256": sha256 or "",
            })

        if ids:
            self._cases.upsert(ids=ids, documents=docs, metadatas=metas)

        # drop vectors whose source case no longer exists
        stale = [i for i in existing_sha if i not in current_ids]
        if stale:
            self._cases.delete(ids=stale)

        return {"embedded": len(ids), "skipped": len(rows) - len(ids), "deleted": len(stale)}

Two things in there are the difference between a toy and a tool. upsert keyed on the case ID means a re-run updates in place instead of quietly creating a parallel copy of your whole portfolio. And the stale sweep at the end deletes vectors whose source case is gone — without it, deleted cases keep surfacing in search results forever, which is a special kind of maddening when someone acts on a case that no longer exists.

The sentinel also makes the honest report possible: {embedded, skipped, deleted} per run. When that first number stays high on a no-op sync, your hashing is wrong.

Step 4 — Query

    def search_cases(self, query: str, *, limit: int = 10) -> list[dict[str, Any]]:
        """Return top-N cases by semantic similarity."""
        results = self._cases.query(
            query_texts=[query],
            n_results=min(limit, self._cases.count() or 1),
            include=["documents", "metadatas", "distances"],
        )
        return [
            {
                "case_id": meta.get("case_id"),
                "title": meta.get("title"),
                "refs": meta.get("refs"),
                "similarity": round(1 - float(dist), 4),
            }
            for meta, dist in zip(results["metadatas"][0], results["distances"][0])
        ]

Two small things that bite. n_results above the collection size errors out, so clamp it — an empty index on a fresh checkout is exactly when you don't want a stack trace. And convert distance to similarity (1 - distance in cosine space) before showing it to anyone; "0.86 similar" is a number a human can act on, "0.14 distant" is one they'll misread.

Resist hard-coding a "this is a duplicate" threshold from a blog post — mine or anyone's. Those cutoffs are portfolio-specific. Run twenty queries you already know the answer to, see where real matches stop and noise starts, calibrate from that. In practice the signal is in the top 3–5 results regardless of the absolute number, which is why this returns a ranked list rather than a verdict.

What changes for other sources

The same store handles Jira issues and chat threads; only the document format changes, and each source has one non-obvious rule.

For chat, chunk per thread, not per message. An individual message is too short and too contextless to embed usefully — "yeah but not on stage" retrieves nothing and means nothing alone. A thread is how a human reads the exchange back, so that's the unit. And resolve <@U04ABCDEF> mentions to display names before embedding: a raw user ID is meaningless to the model, while "@maria: we decided not to support that" is a sentence with content. For Jira, flatten the rich-text description to plain text and keep only the first few comments — status-update noise buries the signal fast.

The part I'd refuse to skip

Everything above is read-only, and that's a deliberate stopping point. The obvious next step — "let it clean up the duplicates it found" — is where this stops being a weekend project. A slightly-too-loose semantic match plus an automated bulk edit is how you lose test cases nobody notices are gone. When you get there, every write goes through the same chain: dry run, a diffable and hashed plan, an explicit human confirm. Retrieval can afford to be approximately right. Writes can't.

Build the read path first. It's most of the value, and it's the half that can't hurt you.

If you're weighing whether this belongs in your stack at all — or you'd rather have someone build it, hand it over, and train the team on it — that's the kind of thing I do in a QA audit or embedded engagement.