A design case study: why a hand-built context goes stale, and how to keep an assistant current with code, docs and schema automatically.
About this case study. This is a composite scenario. The system, tables and numbers are illustrative. It shows a design and how to measure it, not benchmark results.
The setup
ClaimFlow is the claims backend of a mid-sized insurer. It takes a submitted claim, checks it against the policy, tracks documents, sets reserves and moves the claim from submitted to paid or rejected.
Three teams work on the claims journey, and our team owns only one part:
- Claims Portal (the UI) is owned by the portal team.
- Claims Intake (preprocessing) is owned by the integration team. It cleans and enriches claims before they reach us.
- ClaimFlow (the backend) is ours: the business rules, the claim lifecycle and the claims database.
What we know about ClaimFlow lives in three places:
- Bitbucket : the service code and the database migrations.
- Confluence : feature specs written by the business team, plus runbooks and design notes.
- The database : about 80 tables (claims, claim items, policies, reserves, payments, documents, status history, reference data) with foreign keys, status codes and some conventions nobody wrote down.
A typical feature touches all three teams: a new screen in the Portal, a new validation in Intake, new rules in ClaimFlow, and new columns or tables in the database.
To help new team members, we built a chat assistant. People ask it how a process works, how tables connect, where logic lives in the code, and, most of all, for SQL to troubleshoot a claim that is stuck.
The assistant answers from one large context document. Someone collects Confluence pages, table definitions and code snippets, asks ChatGPT to summarise them, and pastes the result in. When something changes, someone does it again.

Today: three teams, three sources, and one hand-built context that someone has to remember to update.
What goes wrong
Here is a typical week.
On Tuesday, feature CLM-482, partial approval , ships. The business spec in Confluence is updated. A migration splits claim_items.amount into claimed_amount and approved_amount, and adds a new status, PARTIALLY_APPROVED. The code goes to production.
On Thursday, a new team member is asked why some claims look stuck. They ask the assistant for SQL. The assistant still has last month’s context.

Between the release and the next manual refresh, every answer comes from the old version.
Two things can happen, and the second is worse:
- Loud failure. The SQL uses amount, which no longer exists. The query fails. Annoying, but at least everyone can see it.
- Silent failure. The SQL filters on the statuses the assistant knows about. It runs fine and returns rows, but misses every PARTIALLY_APPROVED claim. The person troubleshooting now has a wrong answer that looks right.
The stale context is not just a maintenance chore. It is a design problem:
- It goes stale by default. Freshness depends on someone remembering.
- It doesn’t fit. One document can’t hold 80 tables, the codebase and every spec. Summarising to make it fit drops exactly the details people ask about.
- It blends the sources. The spec says what was intended, the code says what happens, and the schema says what exists. A summary mixes them, so nobody can tell which one a sentence came from.
- It can’t be checked. Answers carry no links back to a page, file or table.
So the rule for this design is:
Index on change, retrieve on question. Keep the sources as the truth. Keep an index that updates when they change. Fetch what each question needs, and cite it.
The design in one picture

Changes trigger sync jobs; the assistant retrieves from the indexes and cites what it used.
This case runs on Google Cloud. Webhooks and schedulers start sync jobs on Cloud Run, Pub/Sub decouples them, and Cloud SQL for PostgreSQL with pgvector holds the indexes and the schema catalog. A Gemini model on Gemini Enterprise Agent Platform (formerly Vertex AI) answers through a small set of tools. The same shape works on any cloud.
Six decisions shape it.
Decision 1: Retrieve from indexed sources, not a hand-built summary
We looked at three options:

Three ways to give the assistant its knowledge.
A managed connector is a reasonable choice for the Confluence part. Gemini Enterprise, for example, has a Confluence Cloud connector with full and incremental syncs. But our questions mix all three sources (“which table stores the field the spec calls approved amount, and where is it set in code?”), and the schema needs more than text search. So we keep our own indexes for code and schema, and keep Confluence in the same store so one answer can cite all three.
The important change is not the tool. It is that the assistant no longer reads a copy written by a person. It reads the sources, through an index that a machine keeps current.
Decision 2: Sync on change, reconcile on schedule
Each source tells us about changes in a different way, so each gets its own trigger.

One trigger per source, plus a nightly reconcile that catches anything a trigger missed.
- Bitbucket. The assistant should describe the code that is running in production , not whatever was merged last. So the deploy pipeline moves a prod tag to the commit it deploys. A webhook on repo:push fires when that tag moves and calls a Cloud Run endpoint. The endpoint checks the HMAC signature in the X-Hub-Signature header, then publishes a change event with the old and new commit.
- Confluence. Every 15 minutes, a job runs a CQL query such as space = CLM and lastmodified >= "2026-10-18 09:00" and re-indexes what changed. Confluence Cloud can send webhooks too, but polling is simpler to run, and 15 minutes is fresh enough for this use.
- Database schema. If the deployed change touches the migrations folder, a job reads the live schema (tables, columns, keys, comments) from a read replica , and reference tables such as status codes, and rebuilds the schema catalog.
- Nightly reconcile. Once a night, everything is listed in full and compared with the index. This catches missed webhooks, deleted pages and permission changes.
Three rules make the sync safe to run again and again:
- Idempotent. Each item is stored with a content hash. Unchanged content is skipped.
- Deletes are real. A deleted page or removed file must disappear from the index. Stale facts are worse than missing ones.
- Everything is versioned. Each chunk carries its source version: commit SHA, page version or schema snapshot time.
Decision 3: Each source keeps its own shape
Prose, code and schema are not the same kind of thing, so we don’t index them the same way.

Each source is stored in the form its questions need.
- Confluence is chunked by section, with the page title, space, labels and feature key added to each chunk. This is the same approach Case 01 used for HR policies.
- Code is chunked by function, class and HTTP route, not by line count, so a chunk is a unit someone would actually ask about. We also generate a short summary per module. This is allowed because it is derived and versioned : it is rebuilt by the pipeline whenever that module changes, never by hand.
- Schema is not embedded as text at all. It is a catalog of tables, columns, types, foreign keys, comments and allowed status values. The assistant reaches it through tools: describe_table, find_join_path (a shortest path over foreign keys) and list_status_values.
The schema catalog holds structure, not customer data. Claims tables contain personal and medical details. Only reference tables, such as status codes, contribute values.
Decision 4: When the sources disagree, say so
Each source is the authority on a different question:

Each source answers a different question. A good answer says which one it used.
- Schema : what exists.
- Code in production : what actually happens.
- Confluence : what was intended.
If the spec says partial approvals need a second reviewer but the code doesn’t check for one, the assistant should not pick one and hide the other. It should answer with both, cite both, and say they disagree.
The nightly reconcile also produces a drift report for the team: status values in the database that no Confluence page mentions, columns with no comment, and pages that refer to columns that no longer exist. The assistant’s sync job doubles as a documentation check.
Decision 5: Check SQL against today’s schema, and never run it
Generated SQL is the most useful output and the riskiest. So it passes a guard before anyone sees it:

Every generated query is checked against the current schema. A person decides whether to run it.
The core of the guard is short. It parses the query, rejects anything that is not a single read-only statement, and checks every table and column against the catalog built from the live schema:
import sqlglot
from sqlglot import exp
from sqlglot.errors import SqlglotError
from sqlglot.optimizer import qualify
WRITES = (exp.Insert, exp.Update,
exp.Delete, exp.Drop,
exp.Create, exp.Alter,
exp.Merge, exp.Command)
def check_sql(sql, catalog):
"""catalog: {table: {column: type}}
built from the live schema."""
try:
stmts = sqlglot.parse(
sql, read="postgres")
except SqlglotError as e:
return [f"does not parse: {e}"]
if len(stmts) != 1:
return ["one statement only"]
tree = stmts[0]
# 1. Read-only: no writes anywhere.
if not isinstance(tree, exp.Query):
return ["SELECT only"]
if any(tree.find_all(*WRITES)):
return ["SELECT only"]
# 2. Every table must exist now.
ctes = {c.alias for c
in tree.find_all(exp.CTE)}
for t in tree.find_all(exp.Table):
if (t.name not in catalog
and t.name not in ctes):
return [f"no table {t.name}"]
# 3. Every column must exist now.
try:
qualify.qualify(
tree, schema=catalog,
dialect="postgres",
validate_qualify_columns=True)
except SqlglotError as e:
return [f"unknown column: {e}"]
return []
If the guard returns a problem, the model gets the error and tries again, up to a limit. A query that passes is also run through EXPLAIN on the read replica, under a read-only role with a statement timeout, to catch type errors and very expensive plans.
The guard catches the loud failure from the CLM-482 story: amount no longer exists. The silent one, the missing status, is caught differently. The assistant gets status values from list_status_values, so the new PARTIALLY_APPROVED status appears as soon as the reference table changes.
The query is then shown, not run. A person reads it and runs it against the read replica.
Decision 6: Show freshness, and test after every sync
Every answer ends with where it came from and how current that source is:

Every answer carries its sources and their versions, so a reader can judge how far to trust it.
After each sync, a set of about 50 real questions from new joiners and past incidents runs against the new index. Each question has the source it should cite and, for SQL questions, the tables it must use. If the pass rate drops sharply, the new index is not switched on and the team is alerted. This is the same release-gate idea Case 05 covers in detail.
We track four numbers:
Two boundaries
Permissions. Some Confluence pages are restricted. Their restrictions are stored with each chunk and applied as a filter at retrieval time, so the assistant never shows a user something they couldn’t open themselves. This is the same rule as Case 01: access is decided by data, not by the model.
Other teams’ systems. The Portal and Intake are not ours, and we can’t keep their internals current. We index only their published contracts : API specs, event schemas and the tables we share. Questions about their internals get a pointer to the owning team.
Rollout

Start with the part that prevents wrong SQL; retire the hand-built context only when the new one wins.
- Schema catalog and SQL guard first. This is where stale knowledge does the most damage, and it needs no embeddings.
- Confluence next , with section chunking and the 15-minute sync.
- Code last , chunked by function, class and route, re-indexed on each production deploy.
- Compare, then switch. Run the golden questions against both the old context and the new indexes. Retire the hand-built context when the new system wins on citations and SQL validity.
What we would do differently
- Collect the golden questions on day one. Every question a new joiner asks in chat is free test data.
- Ask the business team for one habit: the feature key in the page title. It is the cheapest way to link a spec to its commits and migrations.
- Comment the schema. Column comments are the highest-value documentation in this system, because the assistant reads them on every SQL question.
Key takeaways
- A hand-built context is a copy, and copies go stale. Index the sources instead.
- Sync on change, reconcile on schedule, and treat deletes as seriously as updates.
- Give each source its own shape: sections for docs, functions for code, a catalog for schema.
- When sources disagree, show both. Spec, code and schema answer different questions.
- Check generated SQL against today’s schema, and never run it automatically.
- Put the version on every answer, and test after every sync.
Next in the series: Case 04, an LLM gateway that cut the bill (GCP). The design and decision records for this case are in the series repository.
Google Cloud and Atlassian details reflect public documentation as of October 2026. Check current docs before you build.


Top comments (0)