Jewellery

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:

Schema

See 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 — hand-written: 3 brands, 9 standard occasions, stores, products.

• sql/003_seed_customers.sql, sql/004_seed_purchases_occasion_dates.sql, sql/005_seed_customer_identities.sql — generated by scripts/generate_seed.py (deterministic, seeded RNG). Regenerate with:

  python3 scripts/generate_seed.py
  

Setup

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

   docker compose up -d
   

2. Create a virtualenv and install:

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

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

   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):

{
  "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:

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.