# Lumenco Catalog (Phase 1 scraper + Phase 2 MCP)

This repository has two layers:

1. **Phase 1** scrapes `https://en.staging.lumenco.ca/` into PostgreSQL.
2. **Phase 2** exposes that catalog through a **read-only** Model Context Protocol server so Claude can retrieve products, specifications, listings, and recommendation *candidates* without browsing Lumenco.

```
CLAUDE
  │ MCP / HTTPS
  ▼
Lumenco Product Database (Streamable HTTP)
  │ tools → services → repositories
  ▼
PostgreSQL  (Phase 1 catalog)
```

**Phase 1 scrapes. Phase 2 exposes. Claude reasons.**

The MCP server never scrapes Lumenco, never downloads specification PDFs, never calls an LLM, and never writes to the database.

## What the site looks like

Lumenco staging is a Magento 2 storefront.

| Area | Behaviour |
| --- | --- |
| Brands | `https://en.staging.lumenco.ca/brand` lists every brand (Amasty Brands). Brand cards on that page often point at `staging.lumenco.ca`; the scraper rewrites them to the English host. |
| Brand listings | `https://en.staging.lumenco.ca/brand/{slug}` with Magento pagination `?p=2` (24 products per page). Total page count is in `#am-page-count`. |
| Products | Canonical URLs such as `/aaled-aa-900018-1x4-bl.html`. Server-rendered HTML includes JSON-LD, SKU, price, stock, spec table, and a **Specification Sheet** link. |
| Spec sheets | Usually same-origin PDFs under `/dev/*.pdf`. |
| Sitemap | `/sitemap.xml` currently errors with HTTP 500. The crawler still tries known sitemap paths, then falls back to brand + category discovery. |
| GraphQL | `/graphql` exists but the staging schema is broken (`Config element "String" is not declared`). HTML crawling is the reliable source. |
| Fetching | Product pages are server-rendered. Scrapling's HTTP `FetcherSession` is the default. `AsyncDynamicSession` is registered as a lazy fallback if a product page is missing required fields. |

The crawler stays on `en.staging.lumenco.ca`. External Specification Sheet PDFs may be downloaded as product documents. Ads, analytics, cart, checkout, and social URLs are ignored.

`robots.txt` is written for public search engines (`User-agent: *` disallows most paths except `/brand` and a few CMS pages). This scraper is an authorized catalog ingest against staging, so `ROBOTS_TXT_OBEY` defaults to `false`. Set it to `true` if you want Scrapling to honour that file.

## Project layout

```
scraper/                    Phase 1 Scrapling crawler
  config.py
  spider.py
  discovery.py
  fetcher.py
  cli.py
  selectors/
  parsers/
  pipelines/
  database/                 shared SQLAlchemy models + repositories
  utils/
app/                        Phase 2 read-only MCP server
  server.py                 Streamable HTTP + /health
  config.py
  auth/middleware.py        bearer token (replaceable with OAuth)
  tools/                    MCP tool layer
  services/                 catalog / product / search / recommendations
  repositories/             read-only queries over Phase 1 tables
  schemas/
  database/session.py       pooled, read-only sessions
alembic/                    PostgreSQL migrations
tests/
scripts/create_readonly_user.sql
```

## 1. Install dependencies

Python 3.10+ is required.

```bash
python -m venv .venv
# Windows
.venv\Scripts\activate
# macOS / Linux
source .venv/bin/activate

pip install -r requirements.txt
```

Scrapling's HTTP/browser extras are included via `scrapling[fetchers]`. If you need browser fallback (DynamicFetcher), install browser binaries:

```bash
scrapling install
```

Optional OCR for scanned/image-only specification PDFs:

```bash
pip install pytesseract Pillow
# plus a Tesseract OCR engine on the host
```

OCR is off by default (`ENABLE_OCR=false`). Image-based PDFs are stored and marked `ocr_required` rather than saved as empty text.

## 2. Configure PostgreSQL

The fastest local setup:

```bash
docker compose up -d postgres
```

That starts PostgreSQL 16 with:

- user: `lumenco`
- password: `lumenco`
- database: `lumenco`
- host port: `5433` (container port stays `5432`; 5433 avoids a Windows PostgreSQL install already using 5432)

Copy environment config:

```bash
copy .env.example .env   # Windows
cp .env.example .env     # macOS / Linux
```

Default connection string:

```
DATABASE_URL=postgresql+psycopg2://lumenco:lumenco@127.0.0.1:5433/lumenco
LUMENCO_BASE_URL=https://en.staging.lumenco.ca/
```

Create tables (either approach works):

```bash
python -m scraper init-db
python -m alembic upgrade head
```

## 3. Run a 5-product test crawl

```bash
python -m scraper crawl --limit 5
```

This discovers products from the live site, processes only the first 5, downloads their Specification Sheets, stores rows in PostgreSQL, and prints a crawl report.

You can also pin a brand:

```bash
python -m scraper crawl --limit 5 --url https://en.staging.lumenco.ca/brand/aaled
```

Or a single product:

```bash
python -m scraper crawl --url https://en.staging.lumenco.ca/aaled-aa-900018-1x4-bl.html
```

## 4. Run the complete crawl

```bash
python -m scraper crawl
```

This walks all brands (and category listings), follows every pagination page, and scrapes every discoverable product. Do not confuse `--limit` with a production catalog cap — `--limit` is development-only.

Rate limiting is built in: concurrency, per-domain caps, download delay, retries with exponential backoff, and optional AutoThrottle. Tune them in `.env`:

```
MAX_CONCURRENCY=5
CONCURRENT_REQUESTS_PER_DOMAIN=3
DOWNLOAD_DELAY=0.5
RETRY_COUNT=3
AUTOTHROTTLE_ENABLED=true
```

## 5. Resume a crawl

Scrapling checkpointing is enabled via `CRAWL_DIR` (default `./data/crawl`). Press Ctrl+C once for a graceful pause. Run again with:

```bash
python -m scraper crawl --resume
```

Resume behaviour:

- Scrapling restores pending requests from `CRAWL_DIR`.
- Products already stored with `scrape_status=success` are skipped unless you pass `--force`.
- Failed products are retried.
- Specification PDFs are not re-extracted when the document hash is unchanged.

## 6. Inspect the database

```bash
python -m scraper stats
python -m scraper validate
python -m scraper product --sku aa-900018-1x4-bl
```

Or with `psql`:

```bash
psql postgresql://lumenco:lumenco@127.0.0.1:5433/lumenco
```

Useful queries:

```sql
SELECT count(*) FROM products;
SELECT sku, product_name, price, brand FROM products ORDER BY last_scraped_at DESC LIMIT 20;

SELECT p.sku, d.filename, d.extraction_status, left(d.extracted_text, 200)
FROM specification_documents d
JOIN products p ON p.id = d.product_id
WHERE d.extraction_status = 'extracted'
LIMIT 10;
```

## 7. How Specification Sheets are processed

For every product page the parser looks for:

- `a.document-item-link` (Lumenco's "Specification Sheet" control)
- Equivalent labels: Specification Sheet, Spec Sheet, Specifications, Technical Data, PDF, Fiche technique, etc.

Then the pipeline:

1. Stores the document URL.
2. Downloads the file with `httpx` (not a browser).
3. Validates PDF magic bytes (`%PDF`).
4. Saves a deterministic copy: `data/specifications/{sku}_{hash16}.pdf`.
5. Extracts text with PyMuPDF.
6. Cleans whitespace while keeping page/section breaks.
7. Stores extracted text, SHA-256 hash, method, and status.
8. Parses `Label: Value` lines from the PDF **without inventing fields**.
9. Merges PDF specs with product-page specs, preserving source:

```json
{
  "Voltage": {
    "value": "120-277V",
    "source": "product_page",
    "raw": "120-277V",
    "normalized": {"min": 120, "max": 277, "unit": "V"}
  }
}
```

If a PDF has little or no text, status is `ocr_required` (or OCR is attempted when `ENABLE_OCR=true`). Empty successful extractions are not silently stored.

Unchanged PDFs are skipped on later crawls by content hash.

## 8. Troubleshooting failed products

| Symptom | What to do |
| --- | --- |
| `python -m scraper validate` reports issues | Read the JSON `issues` list (`missing_name`, `invalid_url`, `empty_extracted_text`, …). |
| Product failed HTTP 5xx / timeout | Re-run `python -m scraper crawl --resume`. Failures are in `crawl_errors`. |
| Missing Specification Sheet | Expected for some SKUs. Status is `not_found`; the product row is still stored. |
| PDF marked `ocr_required` | Enable OCR extras or inspect the saved file under `data/specifications/`. |
| PDF marked `invalid_pdf` | The linked file was not a PDF (HTML error page, etc.). Check `specification_documents.error_message`. |
| Duplicate products | Should not happen: unique `product_url` / `canonical_url` / `sku` plus upsert. Run `validate`. |
| Brand pages look empty | Confirm you are on `en.staging.lumenco.ca`, not the French host. The spider rewrites this automatically. |
| DynamicFetcher errors | Run `scrapling install`. HTTP fetching is enough for current staging HTML. |
| Database connection errors | Check `DATABASE_URL`, `docker compose ps`, and `python -m scraper init-db`. |

Structured logs look like:

```
[INFO] PRODUCT_FETCH url=https://en.staging.lumenco.ca/aaled-aa-900018-1x4-bl.html sku=aa-900018-1x4-bl status=success
[INFO] SPEC_SHEET sku=aa-900018-1x4-bl status=extracted duration=0.84s
[ERROR] SPEC_SHEET sku=... status=failed error=...
```

## Tests

```bash
pytest
```

Coverage includes URL normalization, product/SKU/price parsing, spec-sheet detection, PDF extraction, database upsert / duplicate prevention, listing membership order, recommendation scoring, and MCP tool integration.

## CLI reference

```bash
python -m scraper crawl --limit 100
python -m scraper crawl --mode development --limit 100 --url https://en.staging.lumenco.ca/brand/aaled
python -m scraper crawl --resume
python -m scraper reprocess-specs
python -m scraper embeddings --limit 100
python -m scraper embedding-stats
python -m scraper recommend --sku ABC123 --type related --limit 5
python -m scraper recommendation-eval
python -m scraper validate
python -m scraper stats
python -m scraper sample
python -m scraper product --sku ABC123
python -m scraper init-db
python -m app.server
```

Default crawl mode is **development**: at most 100 successfully processed products, brands only (no category walk). A full-catalog crawl is refused unless you pass `--mode full --limit N` or `--mode full --confirm-full`.

# Phase 2.5 — 100-product data quality

This project currently targets a **controlled ~100-product Lumenco dataset**. The live catalog has 30,000+ SKUs; full-catalog crawling is intentionally out of scope.

## Pipeline

Scrapling → product extraction → PDF download → PDF text or OCR → specification normalization → PostgreSQL → read-only MCP

PDF text extraction is attempted first. OCR (Tesseract via pytesseract) runs only when the PDF has no meaningful text. Set `ENABLE_OCR=true` and install Tesseract plus `pip install pytesseract Pillow`.

Normalized specifications keep source and conflict flags. Raw spec-sheet text is stored on `specification_documents.extracted_text`. MCP `get_product` returns compact structured specs; `get_product_specifications` can include raw text when `include_raw_text=true`.

French Magento category URLs (for example `/eclairage-interieur` and `/electricite`) still appear in the shared header on the English host. They 404 there. The crawler **does not enqueue** those paths. New crawls also use an isolated Scrapling checkpoint directory (`data/crawl/run-<id>`) so an old pause file cannot resume thousands of category URLs. Use `--resume` only to continue the shared `data/crawl` checkpoint.

French Magento category URLs rewritten onto the English host are classified as `expected_404` and are not counted as product failures.

After a crawl:

```bash
python -m scraper stats
python -m scraper validate
python -m scraper sample
python -m scraper product --sku L0110TUT8002020
```

# Phase 3A — Vector search + product embeddings

Phase 3A adds semantic product representations with **PostgreSQL + pgvector**. It does **not** implement Related/Upsell/Cross-sell ranking (that is Phase 3B).

## Architecture

```
~100 product dataset
        ↓
Canonical product text (cleaned, no HTML)
        ↓
EmbeddingService (OpenAI-compatible API)
        ↓
product_embeddings (pgvector)
        ↓
VectorSearchService
        ↓
MCP tool: search_similar_products
```

## Setup

1. Use a Postgres image with pgvector (`docker-compose.yml` uses `pgvector/pgvector:pg16`).
2. Set embedding env vars in `.env` (see `.env.example`).
3. Migrate:

```bash
python -m alembic upgrade head
```

4. Generate embeddings for the development catalog:

```bash
python -m scraper embeddings --limit 100
python -m scraper embedding-stats
```

Unchanged products are skipped via `content_hash`. Use `--force` to regenerate everything.

### Index strategy

HNSW on cosine distance (`vector_cosine_ops`, `m=16`, `ef_construction=64`) — good for the ~100-product dataset and still usable as the catalog grows. IVFFlat can be considered later for much larger catalogs.

### MCP

New read-only tool: `search_similar_products`. It only reads stored vectors; it does not call the embedding API or scrape Lumenco. Existing recommendation tools are unchanged.


# Phase 3B — Hybrid recommendation engine

Recommendations combine **pgvector similarity** with **structured product rules**. Vector similarity alone is not enough: an 18W T8 tube, a 30W T8 tube, and a T8 fixture may all be semantically close, but they map to Related, Upsell, and Cross-sell respectively.

```
Product → vector candidates + structured neighbors
                ↓
        hard exclusions
                ↓
   Related / Upsell / Cross-sell scorers
                ↓
     scores + confidence + reasons → MCP
```

| Type | Meaning |
|------|---------|
| **Related** | Similar use case / category / specs |
| **Upsell** | Same family **and** measurable improvement (not price alone) |
| **Cross-sell** | Complementary (driver, trim, housing, fixture↔tube) |

No LLM is used inside ranking. MCP tools `find_related_products`, `find_upsell_products`, and `find_cross_sell_products` call `RecommendationService` (read-only).

### CLI

```bash
python -m scraper recommend --sku L0110TUT8002020 --type related --limit 5
python -m scraper recommend --sku L0110TUT8002020 --type upsell --limit 5 --debug
python -m scraper recommend --sku L0110TUT8002020 --type cross-sell --limit 5
python -m scraper recommendation-eval --sample-size 10 --limit 3
```

Weights are configurable via env vars such as `RELATED_VECTOR_WEIGHT`, `UPSELL_TECHNICAL_WEIGHT`, `CROSS_SELL_COMPATIBILITY_WEIGHT` (see `.env.example`).


# Phase 3C — Claude + MCP workflow

```
User → Claude → MCP (/mcp) → PostgreSQL + pgvector + RecommendationService → Claude → User
```

**Responsibilities**

| Layer | Does |
|-------|------|
| Scrapling | Crawl / store |
| PostgreSQL + pgvector | Source of truth + vectors |
| RecommendationService | Deterministic Related/Upsell/Cross-sell ranking |
| MCP | Read-only retrieval (no scrape, no writes, no LLM) |
| Claude | Conversation, tool selection, explanation |

### Claude skill

Project skill: `.cursor/skills/lumenco-product-mcp/SKILL.md`

### End-to-end prompts

See [`docs/claude-e2e-tests.md`](docs/claude-e2e-tests.md).

### Connect Claude / Inspector

1. `docker compose up -d postgres`
2. `python -m app.server`
3. Point the client at `http://localhost:8000/mcp` (Streamable HTTP)
4. Optional: `MCP_AUTH_TOKEN` + `Authorization: Bearer …`

For remote deployment later: expose only the MCP HTTPS endpoint; keep PostgreSQL private.

### Development dataset

Current catalog: **~100 products**. Full Lumenco catalog (30k+) is intentionally out of scope.


# Phase 2 — Lumenco Product Database MCP

Read-only Streamable HTTP MCP server named **Lumenco Product Database**.

## Architecture

```
Claude
  │ MCP / Streamable HTTP
  ▼
Lumenco MCP Server   (/mcp, /health)
  │
  ▼
MCP Tool Layer
  │
  ▼
Service Layer          catalog / product / search / similarity / recommendation
  │
  ▼
Repository Layer       SQLAlchemy, no raw SQL in tools
  │
  ▼
PostgreSQL + pgvector  products, specs, listings, product_embeddings
```

## Local setup

1. Complete Phase 1 setup (PostgreSQL + `.env` + `python -m alembic upgrade head`).
2. Run a crawl so the catalog is populated.
3. Install MCP extras if they are not already in `requirements.txt`:

```bash
pip install -r requirements.txt
```

4. Set MCP variables in `.env`:

```
MCP_HOST=0.0.0.0
MCP_PORT=8000
MCP_AUTH_TOKEN=replace-with-a-long-random-token
DATABASE_URL=postgresql+psycopg2://lumenco:lumenco@127.0.0.1:5433/lumenco
DB_POOL_SIZE=10
DB_MAX_OVERFLOW=20
DB_POOL_TIMEOUT=30
```

For production, create a SELECT-only role:

```bash
psql postgresql://lumenco:lumenco@127.0.0.1:5433/lumenco -f scripts/create_readonly_user.sql
```

Then point `DATABASE_URL` at `lumenco_mcp`.

## Running

```bash
python -m app.server
```

Or:

```bash
uvicorn app.server:app --host 0.0.0.0 --port 8000
```

Docker:

```bash
docker compose up --build mcp
```

### MCP endpoint

`http://localhost:8000/mcp`

### Health

`GET http://localhost:8000/health`

```json
{
  "status": "ok",
  "service": "lumenco-product-mcp",
  "database": "connected"
}
```

## MCP Inspector

```bash
npx -y @modelcontextprotocol/inspector
```

Connect to `http://localhost:8000/mcp` with transport **Streamable HTTP**. If `MCP_AUTH_TOKEN` is set, add:

```
Authorization: Bearer <token>
```

Confirm all eight tools are listed and executable.

## Available tools

All tools read PostgreSQL only. None fetch Lumenco URLs.

### `get_catalog_status`

Catalog size and latest crawl freshness. No input.

### `get_listing_products`

Products on a brand/category listing URL, in original listing position.

| Input | Required | Notes |
| --- | --- | --- |
| `listing_url` | yes | Normalized and used as a database key |
| `limit` | no | Default 20, max 100 |
| `offset` | no | Default 0 |

### `get_product`

Complete product record by `product_id` and/or `sku`.

### `get_product_specifications`

Structured specs plus stored Specification Sheet text. Does not download PDFs.

### `search_products`

Local catalog search (SKU, name, brand, category, description, specifications).

Optional filters: `brand`, `category`, `subcategory`, `sku`, `min_price`, `max_price`.

### `search_similar_products`

Semantic neighbors from stored pgvector embeddings (cosine similarity). Does not generate embeddings or call an LLM.

Optional filters: `brand`, `category`, `subcategory`, `min_price`, `max_price`.

### `find_related_products`

Hybrid **Related** candidates (vector + category/application/specs). Includes `match_score`, `confidence`, `score_breakdown`, and `match_reasons`. Optional `debug=true`.

### `find_upsell_products`

Hybrid **Upsell** candidates. Requires measurable improvement (not price alone). Reasons in `upgrade_reasons`.

### `find_cross_sell_products`

Hybrid **Cross-sell** candidates. Compatibility dominates; same-family alternatives are excluded.

Recommendation tools exclude the source product and de-duplicate candidates. Claude should request a candidate pool, then choose the final 3 Related / 4 Upsell / 7 Cross-sell itself.

## Example workflow

User: analyze the first 10 products from `https://en.staging.lumenco.ca/brand/aaled` and give 3 Related, 4 Upsell, 7 Cross-sell.

1. `get_listing_products(listing_url=..., limit=10)`
2. `get_product(product_id=...)` for each source
3. `find_related_products` / `find_upsell_products` / `find_cross_sell_products` with `limit=10`
4. Claude selects the final set from the candidate pools

## Production deployment

Expose only the MCP HTTPS endpoint. Keep PostgreSQL private.

```
Internet → HTTPS → MCP server → private PostgreSQL
```

Suitable hosts: Railway, Render, Google Cloud Run, AWS, Cloudflare.

Requirements:

- HTTPS terminator in front of `uvicorn` / the Docker image
- `MCP_AUTH_TOKEN` set (bearer middleware is isolated so OAuth can replace it later)
- read-only `DATABASE_URL`
- health check on `/health`

Do not publish port 5432.

## Claude custom connector

After the server is reachable at a public HTTPS URL:

1. In Claude, add a custom connector.
2. MCP URL: `https://your-host/mcp`
3. Server name should appear as **Lumenco Product Database**.
4. Configure bearer authentication with `MCP_AUTH_TOKEN`, or OAuth if you replace the middleware.
5. Ask: "How many products are currently in the Lumenco database?" Claude should call `get_catalog_status`.

Temporary public HTTPS for local testing: Cloudflare Tunnel, ngrok, or similar in front of `localhost:8000`.

## Security

- No `execute_sql`, `fetch_url`, `run_command`, or crawl tools
- SQLAlchemy parameterized queries only
- Query limits enforced
- Sessions open `SET TRANSACTION READ ONLY` on PostgreSQL
- Secrets are not returned in tool errors