Building an insurance claims agent: the plan and the data layer | Part 1
Why I started this project¶
AI is taking over everywhere, and fast. I am already working on an AI-based system for the claims handlers at my job, but that work does not cover everything related to AI. I need more. That is why I decided to build this PoC, which is related to my job, and it gives me an opportunity to study agentic AI and get hands-on experience building it.
I split the project into smaller pieces, because the goal was never to build the PoC as fast as I can, but to understand every part of it.
What is the project about?¶
The idea is to build an AI assistant with LangGraph that queries the policy and claims tables through MCP, searches documents with RAG, and puts a human in the loop before it recommends a payout.
Infrastructure & tools¶
For this project, I am using:
- Docker
- PostgreSQL 18
- Python 3.14
- Other tools (TBC)
Source¶
All the data is synthetic and modelled on the Irish market: amounts in euro, Irish counties, and Irish names.
There are three home-insurance products. Each one covers a set of perils, and each peril has its own limit and deductible:
| Peril | HOME-BASIC | HOME-STD | HOME-PLUS |
|---|---|---|---|
| fire | €150,000 / €500 | €300,000 / €350 | €500,000 / €250 |
| theft | €10,000 / €250 | €25,000 / €250 | €50,000 / €150 |
| storm | €50,000 / €500 | €100,000 / €350 | €200,000 / €250 |
| water_damage | not covered | €30,000 / €350 | €60,000 / €250 |
| accidental_damage | not covered | not covered | €15,000 / €150 |
The values are limit / deductible.
The limit is the most the insurer pays for a single claim, and the deductible is the part of every claim that the customer pays themselves. Put together, the payout rule is:
amount_paid = max(min(loss_amount, limit_amount) - deductible, 0)
For example, a HOME-STD customer with a €6,400 storm loss has a €100,000 limit and a €350 deductible, so the payout is €6,050.
The LLM never does this calculation; Python does. LLMs can get arithmetic wrong, and a payout has to be right every time.
The plan¶
I divided the work into seven steps:
- Prepare the data layer: the database, the schema, and the synthetic data.
- Make the database safe for the agent.
- Write the RAG source documents: the policy wording and the claims guide.
- Build the RAG pipeline with pgvector.
- Build an MCP server that gives the agent its SQL tools.
- Build the agent with LangGraph, including a human approval step.
- Build an evaluation runner that compares the agent's decisions with the expected ones.
Steps 1 and 2 are done, and the next sections explain them.
Step 1: the data layer¶
The infrastructure¶
Docker Compose starts two services: PostgreSQL 18 with the pgvector extension, and Adminer, a web interface to browse the database. The agent does not need pgvector yet, but the RAG pipeline in step 4 will.
The first time the database starts, three SQL scripts run: one creates the schema, one loads the data from the CSV files, and one creates the database roles.
The schema¶
All the tables live in the insurance schema:
| Table | Key | Contents |
|---|---|---|
customers |
customer_id |
The policyholders. |
policies |
policy_id |
One annual policy per customer: product, status, dates, premium, and sum insured. |
coverages |
(policy_id, peril) |
One row per covered peril, with its limit and deductible. |
claims_history |
claim_id |
Past claims: loss date, peril, loss amount, amount paid, and outcome. |
The synthetic data¶
A Python script uses Faker to create 1,000 customers, each with one policy. Because it uses a fixed seed and a fixed reference date, it produces exactly the same files on every run.
To make the data realistic, I followed a few rules:
- Dates match the status. The script picks the status first (85% active, 10% lapsed, and 5% cancelled) and then picks dates that make sense for it. A claim never happens outside the coverage period or after a cancellation.
- Premiums match the product. Each premium is the sum insured multiplied by a product-specific rate.
- Claims match the peril. Each peril has its own claim frequency. Most losses are small, with only a few large ones, and the loss ratio (claims paid divided by premiums) ends up between 0.6 and 0.8.
Step 2: make the database safe¶
A read-only role¶
The agent connects to the database with its own role, which can only read the tables. It cannot insert, update, or delete rows, and it cannot create tables. There are tests to prove it.
This matters. If a prompt ever tells the agent to drop a table, the database refuses. The security lives in the database, not in the prompt.
Credentials in a .env file¶
The credentials live in a .env file that git ignores. If the file is missing, Docker Compose fails straight away with an error, which is much better than a database that quietly starts with the wrong credentials.
Constraints¶
The schema also has CHECK constraints that make sure:
- The status and the outcome only take allowed values.
- A policy's end date comes after its start date.
- The amount paid is never more than the loss amount.
On top of that, a composite foreign key links claims_history (policy_id, peril) to coverages, so every past claim must be for a peril that its policy actually covers.
With these constraints in place, a bug in the generator makes the data load fail, instead of letting bad data slip into the database unnoticed.
The evaluation set¶
An agent without an evaluation is just a demo, so I built the evaluation set before writing a single line of the agent.
The set has 12 scenarios. Each one plants rows in the database with an SCN marker (for example, POL-SCN-LAPSED), plus one incoming claim and its expected result:
| Scenario | Expected decision | What it tests |
|---|---|---|
| SCN-01-CLEAN | approve | A covered peril, below the limit. |
| SCN-02-BURST-PIPE | approve | Sudden water damage is covered. |
| SCN-03-GRADUAL-LEAK | deny | An exclusion that only appears in the policy wording. |
| SCN-04-NOT-COVERED | deny | A peril the product does not include. |
| SCN-05-OVER-LIMIT | partial | A loss above the limit. |
| SCN-06-BELOW-DEDUCTIBLE | deny | A loss smaller than the deductible. |
| SCN-07-LAPSED | deny | A loss after the policy ended. |
| SCN-08-CANCELLED | deny | A loss after the cancellation. |
| SCN-09-EARLY-CLAIM | escalate | A claim within 30 days of the policy start (fraud signal). |
| SCN-10-FREQUENT | escalate | Three or more claims in 12 months (fraud signal). |
| SCN-11-MISSING-INFO | request_info | A claim with no loss date or amount. |
| SCN-12-UNKNOWN-POLICY | request_info | A policy number that does not exist. |
Here is one scenario from the file, with a few fields removed to keep it short:
{
"scenario_id": "SCN-01-CLEAN",
"input": {
"policy_id": "POL-SCN-CLEAN",
"loss_date": "2026-11-05",
"reported_peril": "storm",
"claimed_amount": 6400,
"description": "Storm Bram blew several slates off the roof and rain came into the attic."
},
"expected": {
"decision": "approve",
"payout": 6050,
"reason": "covered_within_limit",
"required_tools": ["get_policy", "get_coverage"],
"required_docs": []
}
}
The expected results live only in a JSONL file and never go into the database, so the agent cannot find the answer through SQL.
Each scenario also lists the tools and documents that the agent must use. That way, the evaluation checks not only the decision, but also how the agent got there.
The project has 46 tests so far. They check the dates, the payout rule, the primary and foreign keys, and the reproducibility of the data. They also make sure that each expected decision really follows from its scenario's data.
What comes next¶
The next step is writing the RAG source documents. Without them, the agent cannot decide four of the scenarios correctly. For example, the only difference between a burst pipe (SCN-02) and a gradual leak (SCN-03) is in the policy wording.
There are still a few decisions to make:
- Which embedding model to use, and how big each chunk should be.
- Whether the MCP server uses stdio or HTTP.
- Which LLM provider and model to use.
- Where to store the checkpoints for the human approval step: PostgreSQL or SQLite.
I will write a post about each step as I finish it.
mathTosetto/python-postgresql-rag-insurance-ai-agenticSynthetic claims agent built with LangGraph. It retrieves documents via RAG, queries a SQL policy table through a tool (exposed via MCP), and requests human approval before recommending payment. - ...GitHub