# jewellery-mcp

Cross-brand customer/purchase database for three independent jewellery retail
brands — **Austen and Blake**, **Diamonds Factory**, and **SACET** — each with
its own stores and customers across international geographies. No global
customer ID exists across brands in real life, so each brand's `customers`
are kept independent; a separate `customer_identities` /
`customer_identity_links` pair pre-resolves the same real person's records
across brands (matched on full name + phone and/or full name + date of
birth).

Ships the database layer (schema, seed data, `db.py` connection helper) plus
an MCP server (`src/jewellery_mcp/server.py`) exposing 10 read-only tools for
working real customer cases:

| Tool | Purpose |
|---|---|
| `search_customers` | Find a customer across all brands by name/phone/DOB — the entry point |
| `get_customer_identity` | Full resolved cross-brand profile for an identity |
| `get_customer_purchase_history` | What they bought, where, for what occasion |
| `get_customer_occasion_calendar` | Their recurring personal dates, soonest first |
| `find_cross_brand_opportunities` | Occasions bought at one brand but never another (single customer or portfolio-wide) |
| `find_upcoming_occasion_opportunities` | Who has a birthday/anniversary coming up and hasn't bought for it yet |
| `get_customer_lifetime_value` | Cross-brand spend normalized to one currency via `fx_rates` |
| `list_top_cross_brand_customers` | Rank cross-brand identities by normalized total spend |
| `get_occasion_coverage_by_brand` | Portfolio view: % of a brand's customers who've ever bought for each occasion |
| `recommend_products_for_occasion` | Turn an identified opportunity into concrete products to pitch |

## Schema

See [sql/001_schema.sql](sql/001_schema.sql):
`brands`, `stores`, `products`, `occasions`, `customers`, `purchases`,
`customer_occasion_dates`, `customer_identities`, `customer_identity_links`.

- [sql/002_seed_brands_stores_products.sql](sql/002_seed_brands_stores_products.sql) —
  hand-written: 3 brands, 9 standard occasions, stores, products.
- [sql/003_seed_customers.sql](sql/003_seed_customers.sql),
  [sql/004_seed_purchases_occasion_dates.sql](sql/004_seed_purchases_occasion_dates.sql),
  [sql/005_seed_customer_identities.sql](sql/005_seed_customer_identities.sql) —
  generated by [scripts/generate_seed.py](scripts/generate_seed.py) (deterministic,
  seeded RNG). Regenerate with:

  ```bash
  python3 scripts/generate_seed.py
  ```

## Setup

1. Start local Postgres (runs on `localhost:5434`; schema + seed data load
   automatically on first start):

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

2. Create a virtualenv and install:

   ```bash
   python3 -m venv .venv
   source .venv/bin/activate
   pip install -e .
   ```

3. Copy the env file (defaults already match the docker-compose Postgres):

   ```bash
   cp .env.example .env
   ```

## Connecting a client (Claude Code / Claude Desktop)

Add to your MCP client config (e.g. `.mcp.json` for Claude Code — already
present in this repo):

```json
{
  "mcpServers": {
    "jewellery-mcp": {
      "command": "/Users/rahuldeshmukh/Claudecode/JewelleryBrandsMCP/.venv/bin/jewellery-mcp",
      "env": {
        "DATABASE_URL": "postgresql://jewellery_admin:jewellery_admin_pw@localhost:5434/jewellery_db"
      }
    }
  }
}
```

## Opportunity / gap analysis

Because `occasions` is a normalized reference table and `customer_identities`
pre-resolves people across brands, "which occasion has this customer bought
for elsewhere but never at brand X" is a plain SQL query, exposed as the
`find_cross_brand_opportunities` tool:

```sql
SELECT DISTINCT i.id AS identity_id, o.name AS occasion, b.name AS opportunity_brand
FROM customer_identities i
CROSS JOIN occasions o
JOIN customer_identity_links l ON l.identity_id = i.id
JOIN brands b ON b.id = l.brand_id
WHERE NOT EXISTS ( -- hasn't bought this occasion at a brand where they're a customer
    SELECT 1 FROM purchases p WHERE p.customer_id = l.customer_id AND p.occasion_id = o.id
)
AND EXISTS ( -- but has bought it at another brand they also shop at
    SELECT 1 FROM purchases p
    JOIN customer_identity_links l2 ON l2.customer_id = p.customer_id
    WHERE l2.identity_id = i.id AND p.occasion_id = o.id
);
```

Against the seed data this returns 124 distinct (identity, occasion, brand)
opportunity rows.