# Home Credit Default Risk

Query 307,511 loan applications in plain English — or in SQL, or over the Model Context
Protocol.

This project loads the Kaggle [Home Credit Default Risk][kaggle] dataset into SQLite and
puts three interfaces on top of it: a Streamlit app, a set of command-line tools, and an
MCP server that lets any LLM client query the data without touching the raw files.

Every path into the data is read-only. The SQLite connection opens with `?mode=ro`, and
generated SQL passes a keyword guard before it runs.

[kaggle]: https://www.kaggle.com/competitions/home-credit-default-risk/data

---

## What's in the data

| | |
|---|---|
| Applications | 307,511 |
| Columns | 122 (`application_train`), 130 in the `v_application` view |
| Defaults | 24,825 — **8.07%** of applicants |
| `SK_ID_CURR` range | 100,002 – 456,255 |

`TARGET` is the outcome: 1 means the client had payment difficulties, 0 means they repaid.
The class imbalance is the point — 8% means a model that predicts "no default" every time
scores 92% accuracy and is worthless.

---

## Quickstart

**1. Install the dependencies.**

```bash
py -3.13 -m pip install -r requirements.txt
```

**2. Get the data.** The CSVs are not in this repository — they're 2.7 GB and Kaggle's to
distribute. Download them from the [competition page][kaggle] and put them in the project
folder. Only `application_train.csv` is needed to start.

**3. Build the database.**

```bash
py -3.13 build_db.py
```

That reads the CSV, infers a SQLite type per column, indexes the columns worth indexing,
and creates the `v_application` view with the derived fields. The result is a 199 MB
`home_credit.db`.

Name tables explicitly to load more, or pass `--all` to load every CSV in the folder:

```bash
py -3.13 build_db.py application_train bureau previous_application
py -3.13 build_db.py --all
```

**4. Run something.**

```bash
py -3.13 -m streamlit run app.py
```

The database is all you need for SQL, applicant lookup and the charts. The
natural-language features additionally want a [Groq][groq] API key — free, and read from
`GROQ_API_KEY` in the environment or a `.env` file beside the scripts. `.env` is
gitignored; no key is ever committed.

[groq]: https://console.groq.com/

---

## Three ways to ask

| Path | You write | How it works |
|---|---|---|
| `query.py` | SQL | Runs it against a read-only connection. No network, no API key. |
| `nl2sql.py` | English | Sends the *schema* to Groq, gets SQL back, runs it locally. |
| `mcp_server.py` | Anything | Publishes 7 tools over MCP; the client's own model drives them. |

The middle path is the one worth being precise about. What leaves this machine is column
names, column types, and the distinct values of seven categorical columns — enough for the
model to write `'Cash loans'` rather than guess `'cash_loan'`. **The applicant rows never
leave.** The SQL comes back and executes against the database on disk.

The one exception is `--explain`, which sends the (usually aggregated) result rows back to
Groq for a written summary. It's opt-in for that reason.

---

## The app

```bash
py -3.13 -m streamlit run app.py
```

Six tabs:

- **Agent** — tool-calling agent. Reads its tools from `tool_calling_prompt.md`, so editing
  that markdown changes the agent's behaviour without touching Python.
- **MCP** — the same idea, but the tools are *discovered over the wire* from
  `mcp_server.py`. Add a tool to the server and it appears here with no change to `app.py`.
- **Ask in English** — one question, one query. Prints the SQL above the result.
- **Applicant lookup** — one applicant, grouped and readable, placed against the population.
- **Explore** — prebuilt breakdowns and charts.
- **SQL console** — write it yourself.

---

## Command line

Each script runs standalone and documents itself with `--help`-style docstrings.

```bash
py -3.13 query.py "SELECT COUNT(*) FROM application_train"
py -3.13 query.py --tables
py -3.13 query.py --schema
```

```bash
py -3.13 nl2sql.py "which education level defaults most?"
py -3.13 nl2sql.py --explain "are bigger loans riskier?"
py -3.13 nl2sql.py --sql-only "how many applicants own a car?"
py -3.13 nl2sql.py                    # interactive
```

```bash
py -3.13 customer.py 100002 --compare
py -3.13 customer.py --find "AMT_INCOME_TOTAL > 500000 AND TARGET = 1"
py -3.13 customer.py --random 5
```

```bash
py -3.13 agent.py --trace "what does EXT_SOURCE_1 mean, and how does it relate to default?"
py -3.13 mcp_client.py "break the default rate down by occupation"
```

---

## Checks on generated SQL

Because a model writes the SQL in three of these paths, it doesn't run unchecked:

- Statements containing `INSERT`, `UPDATE`, `DELETE`, `DROP`, `ALTER`, `CREATE`, `REPLACE`,
  `ATTACH`, `DETACH`, `PRAGMA`, `VACUUM`, `REINDEX` or `TRUNCATE` are refused. The scan runs
  on the whole candidate *before* any prose prefix is trimmed, so it fails closed.
- Multiple statements are refused — `ATTACH DATABASE 'x' AS y; SELECT 1` is rejected whole,
  not trimmed to its harmless tail.
- The connection is opened read-only, so a write fails at the database level regardless.
- Column names reaching `default_rate_by` are validated against `PRAGMA table_info` instead
  of being interpolated into SQL.
- Arithmetic is evaluated from an AST, never `eval()`.

The SQL is printed above every result. Read it before trusting a number — a model can pick
a reasonable-looking wrong column and the output will look perfectly plausible.

---

## Notes on the data

These are the traps that produce confidently wrong answers if you miss them:

- **`DAYS_EMPLOYED` is `365243` on 55,374 rows (18%).** It's a placeholder for pensioners
  and the unemployed, not 1,000 years of service. Averaging it un-filtered is the single
  most common mistake on this dataset. `v_application.YEARS_EMPLOYED` is already NULL for
  these, and `EMPLOYED_ANOMALY` flags them.
- **All `DAYS_*` columns are negative offsets** from the application date. Age is
  `-DAYS_BIRTH / 365.25`.
- **`AMT_INCOME_TOTAL` maxes at 117,000,000** against a mean of 168,798. Use the median
  (147,150).
- **`EXT_SOURCE_1` is the strongest single predictor** and is missing for 56% of rows.
- **`CODE_GENDER` has 4 rows marked `XNA`.**
- **The dataset has no interest rate, loan term, payment history, credit score, names or
  addresses.** If a question needs one of those, it can't be answered here — and the honest
  answer is to say so rather than substitute a proxy.

---

## Files

| File | Purpose |
|---|---|
| `build_db.py` | CSV → SQLite, derived columns, indexes, the `v_application` view |
| `query.py` | Read-only SQL runner |
| `nl2sql.py` | English → SQL via Groq. Owns the connection, `clean_sql`, the schema card |
| `agent.py` | Tool-calling agent over the column dictionary, SQL and a calculator |
| `customer.py` | Single-applicant profile with population percentiles |
| `mcp_server.py` | MCP server — 7 tools, 4 resources, 1 prompt, over stdio |
| `mcp_client.py` | MCP client; also a CLI |
| `app.py` | Streamlit front end |
| `tool_calling_prompt.md` | System prompt + tool schemas for `agent.py` |
| `HomeCredit_columns_description.csv` | The 220-field Kaggle data dictionary |
| `home_credit_eda.ipynb` | Exploratory analysis |
| `home_credit_model.ipynb` | Modelling |

Guards live in exactly one place and are imported, never reimplemented — `nl2sql.py` owns
SQL validation, `agent.py` owns the dictionary search and calculator, `customer.py` owns the
percentiles. Validation logic written twice is validation logic that eventually disagrees
with itself.

---

## MCP server

`mcp_server.py` exposes the project's tooling to any MCP client — Claude Desktop, Claude
Code, an SDK agent. Six of its seven tools need no API key.

```bash
py -3.13 mcp_client.py --tools
```

Setup, the full tool table, design notes and the end-to-end verification results are in
**[README_MCP.md](README_MCP.md)**.

---

## Stack

Python 3.13 · SQLite · pandas · Streamlit · Altair · `mcp` 2.0 · Groq
(`openai/gpt-oss-120b`, `llama-3.3-70b-versatile`)

The Groq calls use `urllib` from the standard library — there's no vendor SDK in the
dependency list.