How I Built RAG for My Accounting App on Bedrock and pgvector

The first version of my accounting assistant could not find a single purchase of "comida" in a database full of lunches, missed the clothing rule sitting right there in the expense policy, and tried to report the sum of 20 invoices as the yearly food total. Every one of those answers looked confident. None of them was right.

This post walks through how I rebuilt the RAG behind that assistant for FacturaFlow, my postgraduate final project: an invoice capture and bookkeeping app for PYMES Guatemalan businesses running on AWS. 

It covers where the knowledge comes from, how documents and invoices get vectorized, why I search invoices with a hybrid of SQL and pgvector, which guardrails survived testing, and how every answer gets validated and measured before anyone sees it. 

The stack is Aurora PostgreSQL Serverless v2 with pgvector, Titan Text Embeddings V2, and Amazon Nova Lite on Bedrock, all deployed with Terraform.

Three Sources, One Assistant

The assistant answers questions about three very different kinds of knowledge, and treating them the same way was my first mistake:

• Company policies. Expense rules, travel and per diem, the chart of accounts, the month-end close. Each client uploads its own, and the assistant only reads them if the client authorized it in the contract.
• Provider documents. The app's user manual and a guide to Guatemalan tax rules (VAT, electronic invoices, the fuel tax). They hold no client data, so every client gets them, and they are re-synced from Markdown in the repo on every deploy.
• Invoices. Thousands of rows read by Textract and corrected by people: dates, tax IDs, amounts, line items, and a free-text description.

Anything about money is answered by SQL. The model never writes queries. It calls nine read-only tools with typed arguments, the server sets the tenant and role scope from the token, and the tools return rows. Embeddings only come into play for the parts that are free text: policy paragraphs and invoice descriptions. Both vector indexes live in the same Aurora database as the books, which is the main reason I picked pgvector for this project (I compared the options in AI in SQL Server vs PostgreSQL): a date filter and a similarity search run in the same statement.

1. Indexing Documents by Section

When someone uploads a document, the file goes to S3 and an EventBridge event wakes an indexer Lambda. PDFs go through Textract text detection, Markdown and text are read as UTF-8. Then comes the decision that mattered most for retrieval quality: chunks are cut at Markdown headings first and only then by size, and every chunk carries "Document > Section" in front of its text.

def chunk(title, text):
    chunks = []
    for heading, body in sections(text) or [("", text)]:
        # every chunk says which document and section it came from
        prefix = f"{title} > {heading}" if heading else title
        for piece in windows(body, size=800, overlap=150):
            chunks.append(f"{prefix}n{piece}")
    return chunks

A policy section such as "Hospedaje (hotel)" usually fits in one chunk, so the hotel limits never get split across two fragments that each rank badly. The prefix also helps the embedding: a paragraph that only says "Q600 per night" means little until it says it belongs to the travel policy. Each chunk is embedded with Titan Text Embeddings V2 at 1,024 dimensions and stored with an HNSW index on cosine distance.

CREATE TABLE kb_chunks (
  id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  document_id bigint NOT NULL REFERENCES kb_documents (id),
  chunk_no    integer NOT NULL,
  content     text NOT NULL,
  embedding   vector(1024)
);
CREATE INDEX ON kb_chunks USING hnsw (embedding vector_cosine_ops);

I call Bedrock from the Lambda. If your text already lives in Aurora, generating the embeddings directly from SQL removes that hop entirely. The admin screen shows each document with its status and chunk count, so a failed PDF is visible the moment it fails.

2. Searching With the Person's Own Words

The model decides when to search documents and writes the query, and that turned out to be a weak spot. Asked whether someone could buy clothes for a client meeting, Nova Lite searched for "Política de gastos, viajes y combustible", a phrase that matches every policy a little and the clothing section not at all. Asked who approves a computer purchase, it searched "aprobación de compras" and never retrieved the technology section. It even searched "hotel expenses", in English, against Spanish policies.

The fix costs one extra embedding call of about 100 ms: the server searches with the model's query and with the person's original question, keeps the best score for each chunk, and returns the top five.

def search_documents(args, scope):
    hits = embed.search(args["query"], scope, limit=4)
    question = scope.get("question", "")
    if question and question.lower() != args["query"].lower():
        # the model shortens or translates; search the original too
        best = {(h["document_id"], h["chunk_no"]): h for h in hits}
        for h in embed.search(question, scope, limit=4):
            key = (h["document_id"], h["chunk_no"])
            if key not in best or h["similarity"] > best["similarity"]:
                best = h
        hits = sorted(best.values(), key=lambda h: h["similarity"],
                      reverse=True)[:5]
    return hits

The scope comes from the verified token. It carries the role and whether the client authorized its corporate documents; without that authorization, embed.search filters the query to the manual and the tax guide only. The model cannot widen it, because it never sees the scope as an argument.

3. Vectorizing Invoices, and Why a Threshold Was Not Enough

Questions like "how much did we spend on food?" started out as a LIKE '%comida%' over the account name, supplier, description, justification and line items. In 2026 there were 458 confirmed food invoices. Not one of them contained the word "comida". They said "Almuerzo con cliente", "Catering para capacitación", "Refacción para reunión de equipo".

So every invoice now gets a vector built from its supplier, its description and the lines the OCR read. It happens right after the OCR saves the invoice and again whenever someone edits the supplier or the description, and a failure there never blocks saving. The backfill of 5,142 invoices took about six minutes. The table stores two things on purpose: the vector of the full text, and a short label (the description) that later steps group by.

My first instinct was a similarity cutoff. I measured it first, and the numbers killed the idea. Best similarity per query, against all invoices:

• "gasolina para los carros": 0.51 (has invoices)
• "comida": 0.33 (has invoices)
• "uniformes": 0.26 to 0.34 (has no invoices)
• "internet": 0.25 (has invoices)
• "asesoría": 0.23 (has invoices)
• "ropa": 0.16 (has no invoices)

Answerable and unanswerable queries overlap, and a cutoff of 0.30 recovered only 2% of the food invoices. The ranking, on the other hand, held up: the top 10 results were mostly the right expense in 15 of the 16 answerable queries, and only "luz" missed entirely. So the hybrid search keeps the ranking and drops the cutoff.

Exact filters (period, supplier, tax ID, account, status) run first, so the similarity search only ranks invoices that could be part of the answer. Running it the other way around, a top 50 across all years can easily contain zero rows from 2026. A loose floor of 0.22 throws away the queries where nothing is even close, which is how "ropa" returns zero without asking the model anything.

-- candidate labels, ranked by meaning inside the exact filters
SELECT e.content,
       min(e.embedding <=> CAST(:v AS vector)) AS distance
FROM invoice_embeddings e
JOIN invoices i ON i.id = e.invoice_id
WHERE i.status = 'confirmed'
  AND to_char(i.fecha, 'YYYY') = :period
GROUP BY e.content
HAVING min(e.embedding <=> CAST(:v AS vector)) <= 0.78
ORDER BY 2
LIMIT 50;

Nova Lite gets those 50 labels as a numbered list and answers with the numbers that really are the requested expense: a catering is food, a cleaning service is not. The kept labels then widen the original word filter, and the summary tool adds up every invoice that matches under the same exact filters. No top-k ever touches the total.

-- the literal word still counts; the kept labels add the synonyms
AND (search_text LIKE '%' || :concepto || '%'
     OR EXISTS (
       SELECT 1 FROM invoice_embeddings e
       WHERE e.invoice_id = i.id
         AND e.content IN (
           SELECT jsonb_array_elements_text(CAST(:kept AS jsonb)))))

Two details took a second round of measuring. My first version showed the model the description plus the line items, which turned 458 food invoices into 301 distinct texts, so 30 candidates covered 44 invoices. Grouping by the description alone collapsed them into 35 labels. And when the invoice list tool hit its 20-row limit, the model happily added up those 20 rows and called it the total; now a truncated list also returns the count and sum of every match, computed in SQL.

Fuel for September matched its accounting account to the cent, and "cosas de librería" found 217 stationery invoices across 16 suppliers. Food lands at roughly three quarters of the food account, mostly because Nova Lite does not read "refacción", the Guatemalan word for an office snack, as food. I kept that gap rather than adding a synonym list, because every hand-written synonym is one more patch to maintain.

4. Guardrails That Earn Their Place

Every question goes through an Amazon Bedrock Guardrail first, and the final answer goes through it again. What stays: the prompt attack filter at high strength, content filters for hate, insults, sexual content, violence and misconduct, blocking of passwords and AWS keys, masking of card numbers, emails and phones, and a single denied topic for credentials and the system prompt. Bedrock lists Spanish as optimized for these filters in its supported languages table, which matters when your users write "dame la contraseña de María".

What went away: denied topics for entertainment and programming. They blocked core accounting questions. "Dame un resumen del último libro diario" (a summary of the latest journal) was refused as a book summary, and "¿cómo se calcula el IDP?" (how the fuel tax is computed) was refused as programming. I tested a narrower definition and then one with explicit exclusions ("does not include accounting, ledgers, taxes or how the app works") on a temporary guardrail with the same tier. The version with exclusions still blocked 4 of 12 legitimate questions, including how to export the purchase ledger to Excel.

Off-topic requests are now handled by the system prompt, which tells the model to answer with one exact sentence. The server recognizes that sentence and renders it with the same "Fuera de alcance" badge the guardrail used. The data stays safe either way, because the tools are read-only and the scope comes from the server. The worst a determined user gets is a TV show recommendation: "recomiéndame una serie" slips through, since "serie" is also a field on every Guatemalan invoice.

5. Validating Every Answer Before Anyone Reads It

The model does not know when it is wrong, so the server checks. A deterministic verify() compares the answer with everything the tools returned: every quetzal amount, invoice number and account code must appear in the data, a "no results" answer cannot sit on top of rows that came back, the period searched must match "this month" or "the last 9 weeks", and tool names or notes written for the model must never leak into the text.

Those rules cannot see a policy answer that invents a rule in plain words, so answers built only from documents get a second check: a contextual grounding check on a separate guardrail, called through ApplyGuardrail with the fragments as the source. It lives apart from the main guardrail so the code can read the scores and retry, instead of showing a block message.

# limits: 100,000 chars of source, 1,000 of query, 5,000 of answer
response = bedrock.apply_guardrail(
    guardrailIdentifier=GROUNDING_GUARDRAIL_ID,
    guardrailVersion=GROUNDING_GUARDRAIL_VERSION,
    source="OUTPUT",
    content=[
        {"text": {"text": fragments[:100_000],
                  "qualifiers": ["grounding_source"]}},
        {"text": {"text": question[:1_000], "qualifiers": ["query"]}},
        {"text": {"text": answer[:5_000],
                  "qualifiers": ["guard_content"]}},
    ],
)

Before wiring it in, I tested it in Spanish with real policy text. Faithful answers scored 0.99 to 1.0 on grounding. Invented ones, such as a Q900 hotel limit or reimbursable business clothes, scored 0.01. It adds about 0.3 seconds. The thresholds are 0.5 for grounding and 0.4 for relevance, and they live in Terraform next to the guardrail.

Any failed check withholds the answer and retries once with the same model, told what went wrong. If the retry fails too, the person sees "No pude responder" with suggested rewordings, and the admin gets the flags, the scores and the full tool transcript.

One limit worth stating plainly. Asked who approves a computer purchase, the assistant once said the director does "because it exceeds Q5,000.01". The approval table with that amount was in the fragment, so grounding scored 0.99. The policy actually says the director approves any computer regardless of amount. Grounding checks catch facts that are absent from the source; they miss a wrong inference built from facts that are present.

6. Measuring It With a Judge and a Human

That computer answer is why there is also an offline evaluation, built on the ideas from Measuring RAG Solutions. I wrote 40 questions from the policies, the manual and the tax guide: direct ones, informal ones, follow-ups, and five the documents do not answer. Each has the expected document and section, the key facts, and a reference answer.

{"pregunta": "¿me pagan el hotel si voy a ver a un cliente a Xela?",
 "documento": "Viajes, viáticos y visitas a clientes",
 "seccion": "Hospedaje (hotel)",
 "datos": ["Q600"],
 "tipo": "respuesta"}

The script asks each question through the deployed API, reads the exchange log to learn which searches ran, repeats them to get the full fragments, and scores:

• Retrieval: whether the expected section is among the five fragments (hit@5) and at what position (MRR).
• Key facts: whether Q600 or "dirección" actually appear in the answer.
• Faithfulness and correctness: Nova Pro, used only here, splits the answer into claims, marks each as supported or not by the fragments, and grades it from 1 to 5 against the reference. The judge is deliberately a bigger model than the one in production, the same reasoning as in matching the model to the task.
• Unanswerable questions: the answer says the documents do not cover it, and invents nothing.

The judge needs a check of its own. The script writes ten random judged answers to a CSV with an empty "agree" column, a person fills it in, and the agreement rate goes into the report next to the scores. The first run gave hit@5 of 77.1%, faithfulness of 93.1%, correctness of 4.56 out of 5, and 60% on the unanswerable questions. Four of the nine failures were the old guardrail topics blocking legitimate questions, which is how I found out they had to go.

Gotchas Worth Knowing

• Single ambiguous words embed poorly. "luz" (electricity, also "light") found no utility bills, while "energía eléctrica" did. Ask the model to pass the concept as a descriptive phrase.
• Contextual grounding defaults to the Classic tier when you omit the tier, and the Standard tier requires cross-Region inference. Test thresholds again if you switch, since scores differ between tiers.
• Embedding on write is cheap; failing on write is expensive. The invoice vector refresh catches every error so a throttled Bedrock call never stops someone from saving an invoice.

What I Would Tell Myself on Day One

Split the knowledge by shape before writing any retrieval code: free text gets vectors, money gets SQL, and the two meet in one database. Measure a similarity threshold before trusting one, since ranking held up across every query I tried while every cutoff failed somewhere. Keep guardrails for safety and leave topic control to the instructions, and put a deterministic check, a grounding check and an offline judge in front of the user, because each one caught failures the other two missed.

If you only add one piece, add the deterministic check on amounts. It costs nothing to run, needs no model, and it is the one that stopped a made-up yearly total from reaching an accountant.