# Catalogue Admin Implementation Plan


**Goal:** Make the pairing graph readable and correctable by a person, so the 229 queued pairs can actually be answered.

**Architecture:** Mostly a backend change. `pairing/queries.py` learns to resolve both sides of every pair into a card in one query — today it returns bare product keys, which no human can judge. Then one route serves one self-contained HTML file with two screens. No build step, no dependencies, no frontend infrastructure added to this service.

**Tech Stack:** FastAPI, psycopg2, plain HTML/CSS/`fetch`, pytest.

**Spec:** `docs/specs/2026-08-11-catalog-admin-design.md`

**Depends on:** Phase 2 (pairing), built and live-verified. The live tenant holds 2,114 pairs across 212 products, 229 of them queued.

## Global Constraints

- **Never commit, never push, never stage.** The user commits their own work. Every task ends with `git status --short` and a report. Do not run `git commit`, `git push`, or `git add`.
- **No assistant attribution** in any file, message, or commit.
- **Comments explain WHY, not WHAT.**
- **The response change is additive.** Every key the endpoints return today must still be returned. Something may already read them.
- **One query per request.** Resolving pairs must not become an N+1 storm of per-product lookups. There is a test that counts queries.
- **No new dependencies.** No React, no bundler, no CDN, no fonts. The page must work with no network access to anything but this API.
- **No authentication is added.** These endpoints have none. A login box would imply protection that does not exist. The page is an internal tool and the spec says so.
- **Do not modify `app/services/catalog/products.py`.** Phase 4 owns that.
- **Do not change the pairing rules, the job, or the decision logic.** This phase reads and presents; it does not re-score.
- Per-tenant tables are `strategist_*` in the tenant's own schema. Interpolate identifiers with `sql.Identifier`.
- Match conventions: module-level `logger = logging.getLogger(__name__)`, snake_case, double quotes, 4-space indent, imports stdlib → third-party → `app.*`.
- Tests: `.venv/bin/python -m pytest`. Baselines: `tests/unit/` **603 passing**, `tests/integration/` **110 passing**.
- **Run the two suites in the FOREGROUND, separately.** Never in one pytest process — the unit conftest sets dummy DB credentials and mixing them fails for unrelated reasons. Never in the background.

---

## File Structure

| File | Responsibility |
|---|---|
| `app/services/pairing/queries.py` (modify) | Resolve both sides of a pair into a card, in one query |
| `app/api/catalog.py` (modify) | `GET /catalog/admin` |
| `app/static/catalog_admin.html` (new) | The two screens, self-contained |
| `tests/integration/test_pairing_queries.py` (new) | The resolve, the query count, the dropped rows |
| `tests/integration/test_catalog_routes.py` (modify) | The admin route |

---

### Task 1: Resolve pairs into cards

**Files:**
- Modify: `app/services/pairing/queries.py`
- Test: `tests/integration/test_pairing_queries.py`

**Interfaces:**
- Consumes: `get_db_connection`, `is_servable`, `load_decisions`, `PAIR_TYPES`.
- Produces:
  - `CARD_COLUMNS: tuple` — the product fields that make a card
  - `pairings_for(tenant_id, product_key) -> dict` — each row gains `neighbor`
  - `pending_pairings(tenant_id, limit=100) -> dict` — **shape changes**, see below

**Why:** today both return `{"anchor_key": "http_api:dummyjson.com:161", "neighbor_key": "…:175", …}`. Nobody can approve that. The UI needs names, images and prices, and it must not fetch them one product at a time.

**`pending_pairings` changes from a list to a dict** so it can report rows dropped
because a product was deleted since the last pairing run. A silently shorter list
looks like an empty queue. `pairings_for` keeps its dict-of-lists shape.

- [ ] **Step 1: Write the failing test**

Create `tests/integration/test_pairing_queries.py`:

```python
import pytest

from app.services.infra.database import bootstrap_tenant, get_db_connection
from app.services.pairing.queries import pairings_for, pending_pairings


def _exec(schema, statement, params=None):
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(statement.replace("{S}", f'"{schema}"'), params or ())
        conn.commit()
    finally:
        conn.close()


def _product(schema, key, name, category, price, image="https://x.example/i.png"):
    _exec(schema,
          "INSERT INTO {S}.strategist_products (product_key, name, category, "
          "price_cents, currency, image_url, product_url) "
          "VALUES (%s,%s,%s,%s,'USD',%s,%s)",
          (key, name, category, price, image, f"https://x.example/{key}"))


def _pair(schema, anchor, neighbor, pair_type, score=0.8, confidence=0.5):
    _exec(schema,
          "INSERT INTO {S}.strategist_product_neighbors (anchor_key, "
          "neighbor_key, pair_type, score, confidence, source, reasons) "
          "VALUES (%s,%s,%s,%s,%s,'attribute','[\"a reason\"]')",
          (anchor, neighbor, pair_type, score, confidence))


@pytest.fixture
def graph(temp_tenant):
    bootstrap_tenant(temp_tenant)
    _product(temp_tenant, "p:1", "iPad Pro", "tablets", 34999)
    _product(temp_tenant, "p:2", "Charging Cable", "mobile-accessories", 5897)
    _pair(temp_tenant, "p:1", "p:2", "bundle")
    return temp_tenant


def test_a_pairing_carries_the_neighbours_card(graph):
    # A merchant cannot judge "p:1 -> p:2". They need to see an iPad suggesting
    # a cable, with prices and images.
    bundle = pairings_for(graph, "p:1")["bundle"][0]
    assert bundle["neighbor"]["name"] == "Charging Cable"
    assert bundle["neighbor"]["price_cents"] == 5897
    assert bundle["neighbor"]["currency"] == "USD"
    assert bundle["neighbor"]["image_url"]
    assert bundle["neighbor"]["category"] == "mobile-accessories"


def test_the_existing_keys_all_survive(graph):
    # The change is additive: something may already read these.
    bundle = pairings_for(graph, "p:1")["bundle"][0]
    for key in ("anchor_key", "neighbor_key", "pair_type", "score",
                "confidence", "source", "reasons", "servable"):
        assert key in bundle


def test_the_queue_carries_both_sides(graph):
    row = pending_pairings(graph)["pairings"][0]
    assert row["anchor"]["name"] == "iPad Pro"
    assert row["neighbor"]["name"] == "Charging Cable"


def test_the_queue_reports_how_many_rows_it_dropped(graph):
    # The catalog changes between pairing runs. A silently shorter list looks
    # like an empty queue rather than a stale graph.
    _pair(graph, "p:1", "ghost", "bundle")
    result = pending_pairings(graph)
    assert result["dropped"] == 1
    assert all(r["neighbor_key"] != "ghost" for r in result["pairings"])


def test_a_pairing_whose_neighbour_vanished_is_dropped(graph):
    _pair(graph, "p:1", "ghost", "similar")
    assert pairings_for(graph, "p:1")["similar"] == []


def test_resolving_does_not_query_once_per_pair(graph, monkeypatch):
    # The whole reason this lives in SQL rather than in the page. With 229
    # queued pairs an N+1 would be 460 round trips per screen load.
    import app.services.pairing.queries as mod

    counter = {"n": 0}
    real = mod.get_db_connection

    def counting():
        conn = real()
        cursor_factory = conn.cursor

        def cursor(*args, **kwargs):
            cur = cursor_factory(*args, **kwargs)
            execute = cur.execute

            def counted(*a, **kw):
                counter["n"] += 1
                return execute(*a, **kw)

            cur.execute = counted
            return cur

        conn.cursor = cursor
        return conn

    monkeypatch.setattr(mod, "get_db_connection", counting)

    for i in range(3, 30):
        _product(graph, f"p:{i}", f"Product {i}", "tablets", 1000 + i)
        _pair(graph, "p:1", f"p:{i}", "similar")

    counter["n"] = 0
    result = pairings_for(graph, "p:1")
    assert len(result["similar"]) == 27
    # One query for the pairs, one for the decisions. Never per pair.
    assert counter["n"] <= 3


def test_an_empty_queue_is_not_an_error(temp_tenant):
    bootstrap_tenant(temp_tenant)
    result = pending_pairings(temp_tenant)
    assert result["pairings"] == []
    assert result["dropped"] == 0
```

- [ ] **Step 2: Run test to verify it fails**

Run: `.venv/bin/python -m pytest tests/integration/test_pairing_queries.py -v`
Expected: FAIL — rows carry no `neighbor`, and `pending_pairings` returns a list.

- [ ] **Step 3: Write the implementation**

In `app/services/pairing/queries.py`, add the card columns and join both sides.
The join is an INNER JOIN on the neighbour (and on the anchor, for the queue),
which is what drops pairs whose product has since been deleted:

```python
CARD_COLUMNS = ("name", "category", "price_cents", "currency", "image_url",
                "in_stock")


def _card(row: dict, prefix: str) -> dict:
    return {column: row.pop(f"{prefix}_{column}") for column in CARD_COLUMNS}
```

Then `pairings_for` selects the neighbour's card alongside the pair:

```python
            cur.execute(sql.SQL("""
                SELECT n.anchor_key, n.neighbor_key, n.pair_type, n.score,
                       n.confidence, n.source, n.reasons, n.computed_at,
                       p.name AS neighbor_name, p.category AS neighbor_category,
                       p.price_cents AS neighbor_price_cents,
                       p.currency AS neighbor_currency,
                       p.image_url AS neighbor_image_url,
                       p.in_stock AS neighbor_in_stock
                FROM {}.strategist_product_neighbors n
                JOIN {}.strategist_products p ON p.product_key = n.neighbor_key
                WHERE n.anchor_key = %s
                ORDER BY n.score DESC
            """).format(sql.Identifier(tenant_id), sql.Identifier(tenant_id)),
                (product_key,))
```

and after fetching, `row["neighbor"] = _card(row, "neighbor")` before the
servability step. Keep the existing grouping and `servable` logic exactly as it
is — it is correct and tested.

`pending_pairings` does the same with **two** joins, one per side, aliasing the
anchor's columns with an `anchor_` prefix. Count the drop by comparing against
an unjoined count:

```python
            cur.execute(sql.SQL(
                "SELECT COUNT(*) AS total FROM {}.strategist_product_neighbors"
            ).format(sql.Identifier(tenant_id)))
            total = cur.fetchone()["total"]
```

then `dropped = total - len(rows)` after the joined fetch, and return
`{"pairings": pending, "dropped": dropped}`.

**Note the ordering trap:** `pending_pairings` currently orders by `score DESC`
and truncates at `limit` *after* filtering. Keep that order — the queue should
show the strongest candidates first — and keep the truncation after the filter,
or the queue will show fewer than `limit` rows while more are pending.

- [ ] **Step 4: Run test to verify it passes**

Run: `.venv/bin/python -m pytest tests/integration/test_pairing_queries.py -v`
Expected: PASS, 7 tests

- [ ] **Step 5: Update the route's callers**

`app/api/catalog.py` passes `pending_pairings`' return value straight through, so
the endpoint's response shape changes from a list to
`{"pairings": [...], "dropped": n}`. Check `tests/integration/test_catalog_routes.py`
for the test that monkeypatches `pending_pairings` and update its stub to the new
shape. Do not weaken the assertion — change the fixture.

- [ ] **Step 6: Run both suites**

Run `.venv/bin/python -m pytest tests/unit/ -q`, then
`.venv/bin/python -m pytest tests/integration/ -q`, sequentially in the foreground.
Expected: no regressions against 603 / 110.

- [ ] **Step 7: Report changed files**

```bash
git status --short
```

Do not commit and do not stage.

---

### Task 2: Serve the page

**Files:**
- Modify: `app/api/catalog.py`
- Create: `app/static/catalog_admin.html` (a placeholder in this task; Task 3 writes the real page)
- Test: `tests/integration/test_catalog_routes.py`

**Interfaces:**
- Produces: `GET /catalog/admin` returning `HTMLResponse`.

**Why a route rather than opening the file directly:** `chat_ui.html` is opened as
a `file://` page, which sends `Origin: null` on every API call. This app sets
`allow_credentials=True` on its CORS middleware, which makes wildcard origins
unreliable. Serving the page from the API makes every call same-origin and
removes the problem rather than working around it.

- [ ] **Step 1: Write the failing test**

Append to `tests/integration/test_catalog_routes.py`:

```python
def test_the_admin_page_is_served():
    resp = client.get("/catalog/admin")
    assert resp.status_code == 200
    assert resp.headers["content-type"].startswith("text/html")
    assert "<html" in resp.text.lower()


def test_the_admin_page_needs_no_tenant():
    # The tenant is chosen in the page, not in the URL, so the page itself is
    # static and cacheable.
    assert client.get("/catalog/admin").status_code == 200


def test_a_missing_page_file_is_a_clear_500(monkeypatch):
    from app.api import catalog
    monkeypatch.setattr(catalog, "ADMIN_PAGE",
                        catalog.ADMIN_PAGE.parent / "does-not-exist.html")
    resp = client.get("/catalog/admin")
    assert resp.status_code == 500
    assert "admin page" in resp.text.lower()
```

- [ ] **Step 2: Run test to verify it fails**

Run: `.venv/bin/python -m pytest tests/integration/test_catalog_routes.py -v`
Expected: FAIL — the route 404s.

- [ ] **Step 3: Write the implementation**

Create `app/static/catalog_admin.html` containing only
`<html><body>placeholder</body></html>` for now — Task 3 replaces it.

In `app/api/catalog.py`:

```python
import pathlib

from fastapi.responses import HTMLResponse

# Read per request rather than at import: editing the page and refreshing is the
# whole development loop for a tool with no build step.
ADMIN_PAGE = pathlib.Path(__file__).resolve().parent.parent / "static" / "catalog_admin.html"


@router.get("/admin", response_class=HTMLResponse)
async def admin_page():
    try:
        return HTMLResponse(ADMIN_PAGE.read_text())
    except OSError:
        logger.error("Could not read the admin page at %s", ADMIN_PAGE,
                     exc_info=True)
        raise HTTPException(status_code=500, detail="admin page unavailable")
```

Register it **before** `/products/{product_key}`, alongside the `pending`
route, so nothing captures `admin` as a product key.

- [ ] **Step 4: Run the tests**

Run: `.venv/bin/python -m pytest tests/integration/test_catalog_routes.py -v`
Expected: PASS

- [ ] **Step 5: Report changed files**

```bash
git status --short
```

Do not commit and do not stage.

---

### Task 3: The two screens

**Files:**
- Modify: `app/static/catalog_admin.html` (replace the placeholder with the real page)

**Interfaces:**
- Consumes: `/catalog/categories`, `/catalog/products`, `/catalog/products/{key}/pairings`, `/catalog/pairings/pending`, `/catalog/pairings/decide`, `/catalog/pair`.
- Produces: nothing importable.

**Read `tests/chat_ui.html` first** and follow its conventions: one file, a
`:root` custom-property palette, a `prefers-color-scheme` dark block, no
dependencies, no framework, plain `fetch`. Match its visual language so the two
tools look like they belong to the same product.

**No automated test covers this file.** A single-file internal tool does not earn
a browser harness. Task 4 verifies it by using it.

- [ ] **Step 1: Build the shell and the tenant field**

A header with the product name, a tenant text input defaulting to
`org_8c32bf3e-6a18-4739-9b1c-94c0cf11125f`, and two tabs: **Catalogue** and
**Queue**. Switching tabs must not refetch what is already loaded.

A single `api(path, options)` helper wraps `fetch`, prefixes nothing (the page is
same-origin), and on a non-2xx **throws with the status code**. Every screen
renders that message into a visible banner. A silent empty state that looks like
"no pairings" when it was really a 502 is how someone concludes the pairing is
broken when the server is.

- [ ] **Step 2: The catalogue screen**

Left rail from `GET /catalog/categories` — category name and product count, the
selected one highlighted. Right pane from
`GET /catalog/products?category=…&limit=50&offset=…` as cards showing image, name,
price and stock, with paging driven by the `total` the endpoint already returns.

A product with no `image_url` gets a neutral placeholder block, never a broken
image icon.

Clicking a card loads `GET /catalog/products/{key}/pairings` and renders four
sections in the order `similar`, `complement`, `upsell`, `bundle`, each headed
with its count. Every pairing renders as a card showing:

- the neighbour's image, name, category and price (all now in the payload)
- the score to two decimals
- **every reason, verbatim, as its own line** — "same category (tablets)",
  "0.70 text similarity". The reasons are the point: a bare 0.55 tells a merchant
  nothing about why the pair was proposed.
- a **servable** or **queued** badge, so the distinction the approval rule makes
  is visible rather than implied

An empty section renders as "none", not as a missing heading — the difference
between "no upsells exist" and "upsells failed to load" must be visible.

- [ ] **Step 3: The queue screen**

`GET /catalog/pairings/pending?limit=100` returns `{pairings, dropped}`. Render
the count, and if `dropped` is non-zero say so plainly — it means the graph is
stale relative to the catalogue and the fix is to re-run pairing.

Each row shows both sides as cards with an arrow between them, the pair type, the
score, and the reasons underneath. Each row has **approve** and **reject**
buttons, plus a row checkbox and two batch actions over the visible rows.

All of them post to `POST /catalog/pairings/decide` with the batch body:

```json
{"tenant_id": "org_…",
 "decisions": [{"anchor_key": "…", "neighbor_key": "…",
                "pair_type": "bundle", "decision": "approved",
                "decided_by": "admin-ui"}]}
```

**Decided rows leave the queue immediately and the count decrements.** A queue
that does not visibly shrink is a queue nobody finishes. Do not refetch the whole
queue after every decision — remove the row locally and only refetch on demand.

A **re-run pairing** button posts `POST /catalog/pair` and shows the returned
counts. It must warn that this recomputes the graph, and it must state that
decisions survive it — that is the reassurance a person needs before pressing a
button that sounds destructive.

- [ ] **Step 4: Check it renders against the live catalogue**

```bash
.venv/bin/uvicorn app.main:app --port 8001 &
sleep 8
curl -s http://localhost:8001/catalog/admin | head -5
curl -s "http://localhost:8001/catalog/pairings/pending?tenant_id=org_8c32bf3e-6a18-4739-9b1c-94c0cf11125f&limit=2" | python3 -m json.tool | head -30
```

Confirm the page is served and that a queue row carries both sides' names and
images. Then open `http://localhost:8001/catalog/admin` in a browser and confirm
both screens paint with real data.

- [ ] **Step 5: Report changed files**

```bash
git status --short
```

Do not commit and do not stage.

---

### Task 4: Use it

**Files:** none — verification only.

**Prerequisites:** uvicorn on port 8001, the live tenant with 2,114 pairs and 229 queued.

This task is the point of the phase. Everything before it is scaffolding for a
person looking at real output.

- [ ] **Step 1: Record the starting state**

```bash
PYTHONPATH=. .venv/bin/python -c "
from app.services.infra.database import get_db_connection
T='org_8c32bf3e-6a18-4739-9b1c-94c0cf11125f'
c=get_db_connection(); cur=c.cursor()
cur.execute('SELECT count(*) FROM \"'+T+'\".strategist_pairing_decisions')
print('decisions recorded:', cur.fetchone()[0])
cur.execute('SELECT pair_type, count(*) FROM \"'+T+'\".strategist_product_neighbors GROUP BY 1 ORDER BY 1')
print('pairs by type:', cur.fetchall()); c.close()"
```

- [ ] **Step 2: Read the pairings for the two obvious categories**

Open the catalogue screen, select `smartphones`, open a product, and read its four
sections. Then do the same for `mobile-accessories`.

Judge them as a merchant would and **write down what is wrong**, not only what is
right. Phase 2 already knows a smartphone still complements `mens-watches`; the
question this step answers is what else is visible now that a person can see the
graph. This is the first time anyone has looked at more than a SQL sample.

- [ ] **Step 3: Work through part of the queue**

Approve and reject at least ten rows, using both the per-row buttons and one
batch action. Confirm the count decrements, decided rows leave, and the page does
not refetch the world after each click.

- [ ] **Step 4: Confirm decisions survive a re-run**

Press **re-run pairing**, then reload the queue.

```bash
PYTHONPATH=. .venv/bin/python -c "
from app.services.infra.database import get_db_connection
T='org_8c32bf3e-6a18-4739-9b1c-94c0cf11125f'
c=get_db_connection(); cur=c.cursor()
cur.execute('SELECT decision, count(*) FROM \"'+T+'\".strategist_pairing_decisions GROUP BY 1')
print('decisions after re-run:', cur.fetchall()); c.close()"
```

Every decision made in Step 3 must still be there, rejected pairs must not
reappear in the queue, and the queue must be smaller by exactly the number
decided. This is the invariant the whole approval design rests on, verified
through the UI a person actually uses rather than through the service layer.

- [ ] **Step 5: Report**

Summarise: changed files, test counts, what the pairings looked like for the two
categories **including what looked wrong**, how many rows were decided, and
confirmation that decisions survived the re-run. State plainly whether the
pairing quality is good enough to serve to shoppers in Phase 4 — that judgement
is the deliverable of this phase, and it is a judgement no test can make.

Remind the user to commit.

---

## Follow-on work (not in this plan)

- **Phase 4:** online ranking — `decide()` reads `product_neighbors` and honours servability, so rejected and unapproved pairs never reach a shopper. The first time this work changes what the chatbot serves, and the first time `catalog/products.py` is modified.
- **Phase 5:** measurement — impressions and clicks, which is also what would let the approval threshold be tuned on evidence rather than on one catalogue's distribution.
- **Phase 6:** the known gaps — per-variant stock, accessory-target extraction, colour coverage, and order history.
- **Bundle grouping:** the queue shows one row per bundle member, so a three-item set is three decisions. Grouping them changes the decision model — approving two of three members is a state the schema cannot express — so it is a data-model change, not a UI tweak.
- **The production merchant dashboard**, in the admin application alongside the other integration screens. This tool is not it.