DEV Community

huynhtanan
huynhtanan

Posted on

Build a hotel rate-parity watcher with Claude Desktop and SQLite

#claudeai #mcp #python #sqlite

Rate parity is the agreement every hotel signs with each OTA: no booking site can list the room cheaper than our website. In practice it breaks constantly — one site runs a flash discount, another forgets to resync a package rate. If you own a hotel, catching that drift in 24 hours instead of 10 days is money.

This post wires the Google Hotels MCP server to a 20-line SQLite logger. Claude Desktop asks the question, the script writes the diff. You end up with a daily table like this:

hotel                          date         booking   expedia   agoda    delta
Ritz Paris                     2026-11-15   €1,480    €1,520    €1,480   -2.6%
Hotel Lutetia                  2026-11-15   €820      €820      €795     -3.1%
Mandarin Oriental Paris        2026-11-15   €1,650    €1,650    €1,650    0.0%
Enter fullscreen mode Exit fullscreen mode

Disclosure: the MCP server (huynhtanan/google-hotels-mcp, open source) calls three paid Apify actors I wrote. Apify's free tier ships $5 of credit per month — about 60 full-city searches — before you need to upgrade. No card required to sign up.

1. Install the MCP server (2 minutes)

Same install as the previous post. Add this block to claude_desktop_config.json:

{
  "mcpServers": {
    "google-hotels": {
      "command": "uvx",
      "args": ["--from", "git+https://github.com/huynhtanan/google-hotels-mcp.git@v0.1.2", "google-hotels-mcp"],
      "env": { "APIFY_TOKEN": "apify_api_..." }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

Restart Claude Desktop. Three tools appear: search_hotels, hotel_price_calendar, search_vacation_rentals.

2. One SQLite file, two tables 

import sqlite3

db = sqlite3.connect("rate_parity.db")
db.executescript("""
CREATE TABLE IF NOT EXISTS snapshot (
  captured_at TEXT NOT NULL,
  hotel       TEXT NOT NULL,
  stay_date   TEXT NOT NULL,
  ota         TEXT NOT NULL,
  price       REAL NOT NULL,
  currency    TEXT NOT NULL,
  PRIMARY KEY (captured_at, hotel, stay_date, ota)
);
CREATE INDEX IF NOT EXISTS snapshot_recent
  ON snapshot (hotel, stay_date, captured_at DESC);
""")
db.commit()
Enter fullscreen mode Exit fullscreen mode

Keeping currency as a column (instead of baking EUR into the field name) means the same table can hold USD, GBP, or BRL snapshots when you expand beyond one region.

3. Ask Claude in plain English

"Snapshot the top 20 Paris hotels across the next 30 check-in dates: for each date, call search_hotels with query="hotels in Paris", include_provider_prices=true, max_results=20, two adults, EUR. Insert each (hotel, stay_date, ota, price) tuple into the snapshot table in rate_parity.db."

Claude issues one search_hotels call per date (30 calls total), each returning ~20 hotels and their providers array. That's 30 × 20 = 600 (hotel, date) rows per snapshot. The whole thing runs in your Claude Desktop session — no external cron.

Here is what the MCP tool actually returns for one hotel on one date (lightly trimmed):

{
  "name": "Hotel Lutetia",
  "checkIn": "2026-11-15",
  "checkOut": "2026-11-16",
  "providers": [
    {"name": "Booking.com", "price": 820, "currency": "EUR"},
    {"name": "Expedia",     "price": 820, "currency": "EUR"},
    {"name": "Agoda",       "price": 795, "currency": "EUR"},
    {"name": "Hotels.com",  "price": 820, "currency": "EUR"}
  ],
  "rating": 4.6,
  "reviewsCount": 2341
}
Enter fullscreen mode Exit fullscreen mode

4. The alert query

Once snapshot rows exist, a single SQL query surfaces drift:

WITH latest AS (
  SELECT hotel, stay_date, ota, price, currency,
         ROW_NUMBER() OVER (PARTITION BY hotel, stay_date, ota ORDER BY captured_at DESC) AS rn
  FROM snapshot
),
today AS (SELECT * FROM latest WHERE rn = 1),
agg AS (
  SELECT hotel, stay_date, currency,
         MIN(price) AS cheapest, MAX(price) AS reference,
         COUNT(DISTINCT ota) AS ota_count
  FROM today GROUP BY hotel, stay_date, currency
  HAVING ota_count >= 2
)
SELECT hotel, stay_date, currency, cheapest, reference,
       ROUND((cheapest - reference) * 100.0 / reference, 2) AS delta_pct
FROM agg
WHERE (cheapest - reference) * 100.0 / reference <= -3.0
ORDER BY delta_pct ASC
LIMIT 50;
Enter fullscreen mode Exit fullscreen mode

The ota_count >= 2 guard is important — a hotel with only one OTA listed is not a parity signal; it is just a single price point.

Any row that comes back is a rate-parity violation at least 3% deep. Pipe it into Slack, email, or just a daily markdown digest.

5. One more Claude question

"Of today's drift rows, which 3 would cost the hotel the most if a corporate traveler booked 10 nights on the cheapest OTA instead of the reference OTA?"

Claude runs the arithmetic directly against the drift rows and writes a one-paragraph summary. The whole feedback loop — scrape, diff, prioritize, explain — runs inside one chat window.

Cost at steady state

  • 20 hotels × 30 dates = 600 (hotel, date) rows per snapshot
  • search_hotels pricing: $0.002/hotel + $0.002/provider-prices row → 600 × $0.004 ≈ $2.40/day for 20 hotels across 30 forward dates
  • Apify's free tier ($5/mo credit) covers about 2 days of this; the smaller examples/rate_parity/ run (20 hotels × 7 nights = $0.56/day) stays in the free tier for ~9 days a month
  • If you only watch your own property across 30 forward nights: ~30 rows × $0.004 ≈ $0.12/day, well inside the free tier

The actor is pay-per-result — you pay only for rows that actually came back, not for failed runs or empty days.

Code

The MCP server, the SQLite schema, and the drift query are in huynhtanan/google-hotels-mcp under examples/rate_parity/. One-file CLI, no framework — the same script can also run from a cron, which is handy if you want nightly snapshots without keeping Claude Desktop open.

If you have a single hotel and want to try this against your own property — ping me (comments are open). I'm interested in which fields the parity teams actually care about so I can bake them into the next cut of the server.

Top comments (0)