Nice, let’s keep rolling. 💪

Right now the issue is:

* Your **API views have data**, but the **homepage template still thinks there’s “No data yet”** because `latest` isn’t being pulled from the DB and passed into `dashboard.html`.

Let’s get the Agent to wire the dashboard to the real Supabase data and double-check the endpoints.

---

## 🔧 Replit Agent Prompt (copy–paste this)

Paste **everything in this box** into the Agent text area in Replit:

---

**Prompt for Replit Agent**

You’re working in my Replit project **“Audi Finance Renewal App”**.
FastAPI + Jinja templates. Supabase Postgres is already connected.

**Goal:**
Use the real Supabase views to power the dashboard instead of “No data yet” / demo mode. Specifically:

* Monthly KPI tiles + chart should use `v_retention_pen_monthly_v2`
* Retention pen table should use `v_retention_book_current`
* The home route `/` must pass a `latest` row into `dashboard.html` so the KPI tiles render

The views in Supabase are already created with these columns:

1. `v_retention_pen_monthly_v2`:

   * `month_key` (date)
   * `contact_rate` (numeric)
   * `reply_rate` (numeric)
   * `retention_pen_pct` (numeric)
   * `total_contacts` (int)
   * `total_replies` (int)
   * `drop_off_total` (int)

2. `v_retention_book_current`:

   * `payments_remaining` (int)
   * `payment_month` (date)
   * `customer_name` (text)
   * `reg` (text)
   * `agreement_number` (text)

Please do the following steps carefully:

---

### 1) Update database helpers in `app/db.py`

1. Open `app/db.py` and locate the async helpers for:

   * `fetch_monthly_summary`
   * `fetch_retention_pen`
   * (and `fetch_campaigns` if present)

2. Replace the implementation of **`fetch_monthly_summary`** with a version that:

   * Queries `v_retention_pen_monthly_v2`
   * Returns up to the **last 12 months**, sorted oldest → newest so the chart looks right
   * Returns a list of dicts with keys:

     ```python
     [
       "month_key",
       "contact_rate",
       "reply_rate",
       "retention_pen_pct",
       "total_contacts",
       "total_replies",
       "drop_off_total",
     ]
     ```

   Example shape of the query (adapt to existing pool/conn style):

   ```sql
   SELECT
     month_key,
     contact_rate,
     reply_rate,
     retention_pen_pct,
     total_contacts,
     total_replies,
     drop_off_total
   FROM v_retention_pen_monthly_v2
   ORDER BY month_key ASC
   LIMIT 12;
   ```

3. Replace **`fetch_retention_pen`** so that it queries `v_retention_book_current` and returns a list of dicts with keys:

   ```python
   [
     "customer_name",
     "reg",
     "payments_remaining",
     "payment_month",
     "agreement_number",
   ]
   ```

   Suggested query:

   ```sql
   SELECT
     customer_name,
     reg,
     payments_remaining,
     payment_month,
     agreement_number
   FROM v_retention_book_current
   ORDER BY payment_month ASC, payments_remaining ASC;
   ```

4. Make sure both helpers use the existing `asyncpg` connection/pool pattern already used in `db.py` (don’t introduce a new client, just reuse what’s there).

---

### 2) Wire the home route `/` to real data in `app/main.py`

1. Open `app/main.py` and find the FastAPI route for `/` that renders `dashboard.html`.

2. Update that route so it:

   * Uses `fetch_monthly_summary()` to load the monthly rows
   * Picks the **latest** row (the last one in date order) and passes it to the template context as `latest`
   * Still passes `request` as required by Jinja/FastAPI

   For example (adapt to current structure):

   ```python
   from fastapi import FastAPI, Request
   from fastapi.responses import HTMLResponse
   from . import db
   from .templates import templates  # or however templates are imported

   @app.get("/", response_class=HTMLResponse)
   async def dashboard(request: Request):
       rows = await db.fetch_monthly_summary()
       latest = rows[-1] if rows else None

       return templates.TemplateResponse(
           "dashboard.html",
           {
               "request": request,
               "latest": latest,
           },
       )
   ```

3. Ensure there is **no hard-coded demo `latest`** variable left anywhere in `main.py`.

---

### 3) Confirm `dashboard.html` correctly uses `latest` and real data

1. Open `app/templates/dashboard.html`.

2. Verify that:

   * The KPI tiles are rendered inside `{% if latest %} ... {% endif %}`, as they already are.
   * At the top of the `<script>` block, `const DEMO_MODE = false;` (if it is still `true`, set it to `false`).
   * In `loadTrendChart()`, values are parsed as numbers and sorted by month as we already added:

     ```javascript
     rows.sort((a, b) => (a.month_key || "").localeCompare(b.month_key || ""));

     const labels = rows.map((r) => (r.month_key || "").slice(0, 7));
     const contactRate = rows.map((r) => parseFloat(r.contact_rate) || 0);
     const replyRate = rows.map((r) => parseFloat(r.reply_rate) || 0);
     const retentionPen = rows.map((r) => parseFloat(r.retention_pen_pct) || 0);
     ```

3. For now, you can leave the “Demo data preview” pill in the UI even though we’re using real data; it’s just cosmetic. Do **not** re-enable demo mode.

---

### 4) Test the endpoints and the dashboard

1. In the Replit shell, call:

   ```bash
   curl -s http://localhost:8000/api/monthly-summary | jq
   curl -s http://localhost:8000/api/retention-pen | jq | head
   ```

   Confirm that:

   * `/api/monthly-summary` returns the two rows we saw earlier with `month_key`, `contact_rate`, etc.
   * `/api/retention-pen` returns customer rows with `customer_name`, `reg`, `payments_remaining`, `payment_month`, `agreement_number`.

2. Restart the FastAPI server if needed, and reload the Preview tab.

3. Confirm that on `/`:

   * The **KPI tiles** now show real numbers instead of “No data yet”.
   * The **6-month trend chart** renders using the API data.
   * The **Retention Pen** table shows real rows from `v_retention_book_current` (or falls back gracefully if empty).

If anything fails, please fix the errors in place and re-run tests until everything passes and the dashboard renders real data with no console/server errors.

When you’re done, summarise the code changes you made (files + a one-line description each).

---

That’s the full task.