Ecommerce Price Monitoring: Build Your Own Price Tracker

const response = await fetch('https://api.robotscraping.com/extract', {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json',
    'x-api-key': process.env.API_KEY
  },
  body: JSON.stringify({
    url: 'https://example.com/product/laptop-123',
    fields: [
      'product_title',
      'current_price',
      'original_price',
      'currency',
      'availability',
      'in_stock',
      'discount_percentage',
      'promotion_text'
    ],
    instructions: 'Extract the main product title, the current selling price, any crossed-out original price, the currency symbol, whether the item is in stock, and any promotional text or discount badges.'
  })
});

const priceData = await response.json();
// {
//   product_title: "Dell XPS 15 Laptop",
//   current_price: "999.99",
//   original_price: "1299.99",
//   currency: "$",
//   availability: "In Stock",
//   in_stock: true,
//   discount_percentage: "23% off",
//   promotion_text: "Flash Sale - Limited Time"
// }

body: JSON.stringify({
  url: 'https://example.com/product/tshirt',
  fields: ['variants'],
  instructions: 'Extract all product variants. For each variant, return the size, color, price, and whether it is in stock. Return as an array of objects.'
})

-- Products table (items you're tracking)
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  sku TEXT,
  name TEXT,
  category TEXT,
  brand TEXT,
  created_at TIMESTAMP
);

-- Competitor URLs for each product
CREATE TABLE competitor_urls (
  id INTEGER PRIMARY KEY,
  product_id INTEGER,
  competitor_name TEXT,
  url TEXT UNIQUE,
  is_active BOOLEAN,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Price observations (time series data)
CREATE TABLE price_observations (
  id INTEGER PRIMARY KEY,
  competitor_url_id INTEGER,
  price_cents INTEGER,
  currency TEXT,
  original_price_cents INTEGER,
  availability TEXT,
  in_stock BOOLEAN,
  promotion_text TEXT,
  observed_at TIMESTAMP,
  raw_html_reference TEXT,
  FOREIGN KEY (competitor_url_id) REFERENCES competitor_urls(id)
);

-- Price change alerts
CREATE TABLE price_alerts (
  id INTEGER PRIMARY KEY,
  competitor_url_id INTEGER,
  old_price_cents INTEGER,
  new_price_cents INTEGER,
  change_percent REAL,
  alert_type TEXT,
  sent_at TIMESTAMP,
  acknowledged BOOLEAN
);

async function recordPriceObservation(db, competitorUrlId, priceData) {
  const priceCents = Math.round(parseFloat(priceData.current_price) * 100);
  const originalPriceCents = priceData.original_price
    ? Math.round(parseFloat(priceData.original_price) * 100)
    : null;

  await db.prepare(`
    INSERT INTO price_observations (
      competitor_url_id, price_cents, currency,
      original_price_cents, availability, in_stock,
      promotion_text, observed_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  `).bind(
    competitorUrlId,
    priceCents,
    priceData.currency || 'USD',
    originalPriceCents,
    priceData.availability || 'Unknown',
    priceData.in_stock ?? true,
    priceData.promotion_text || null,
    Date.now()
  ).run();
}

async function detectPriceChanges(db, threshold = 0.05) {
  // Get the most recent price for each competitor URL
  const latestPrices = await db.prepare(`
    SELECT DISTINCT ON (competitor_url_id)
      competitor_url_id,
      price_cents,
      observed_at
    FROM price_observations
    ORDER BY competitor_url_id, observed_at DESC
  `).all();

  // Get the previous price for comparison
  for (const latest of latestPrices) {
    const previous = await db.prepare(`
      SELECT price_cents
      FROM price_observations
      WHERE competitor_url_id = ?
        AND observed_at < ?
      ORDER BY observed_at DESC
      LIMIT 1
    `).bind(latest.competitor_url_id, latest.observed_at).first();

    if (previous) {
      const changePercent =
        (latest.price_cents - previous.price_cents) / previous.price_cents;

      if (Math.abs(changePercent) >= threshold) {
        await createPriceAlert(db, {
          competitorUrlId: latest.competitor_url_id,
          oldPrice: previous.price_cents,
          newPrice: latest.price_cents,
          changePercent: changePercent * 100,
          alertType: changePercent < 0 ? 'price_drop' : 'price_increase'
        });
      }
    }
  }
}

async function createPriceAlert(db, alertData) {
  await db.prepare(`
    INSERT INTO price_alerts (
      competitor_url_id, old_price_cents, new_price_cents,
      change_percent, alert_type, sent_at, acknowledged
    ) VALUES (?, ?, ?, ?, ?, ?, ?)
  `).bind(
    alertData.competitorUrlId,
    alertData.oldPrice,
    alertData.newPrice,
    alertData.changePercent,
    alertData.alertType,
    Date.now(),
    false
  ).run();
}

// Detect prices that deviate significantly from historical average
async function detectPriceAnomalies(db, stdDevThreshold = 2) {
  const stats = await db.prepare(`
    SELECT
      competitor_url_id,
      AVG(price_cents) as mean_price,
      STDDEV(price_cents) as std_dev,
      MAX(observed_at) as last_observed
    FROM price_observations
    WHERE observed_at > datetime('now', '-30 days')
    GROUP BY competitor_url_id
  `).all();

  for (const stat of stats) {
    const latest = await db.prepare(`
      SELECT price_cents FROM price_observations
      WHERE competitor_url_id = ?
      ORDER BY observed_at DESC LIMIT 1
    `).bind(stat.competitor_url_id).first();

    if (latest) {
      const zScore = (latest.price_cents - stat.mean_price) / stat.std_dev;
      if (Math.abs(zScore) > stdDevThreshold) {
        // Flag as anomalous
        console.log(`Anomaly detected for URL ${stat.competitor_url_id}: Z-score = ${zScore.toFixed(2)}`);
      }
    }
  }
}

// Create a schedule for price monitoring
const response = await fetch('https://api.robotscraping.com/schedules', {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json',
    'x-api-key': process.env.API_KEY
  },
  body: JSON.stringify({
    name: 'daily-price-monitoring',
    cron: '0 */4 * * *', // Every 4 hours
    urls: [
      'https://amazon.com/dp/B08X5...',
      'https://walmart.com/ip/12345...',
      'https://bestbuy.com/site/...'
    ],
    config: {
      fields: ['current_price', 'original_price', 'availability', 'in_stock'],
      instructions: 'Extract current price, original price if shown, and stock availability.'
    },
    webhook_url: 'https://your-app.com/api/price-webhook'
  })
});

// Your webhook handler receives extraction results
app.post('/api/price-webhook', async (req, res) => {
  const { job_id, url, result, timestamp } = req.body;

  // Verify webhook signature
  const signature = req.headers['x-webhook-signature'];
  if (!verifySignature(signature, req.body, WEBHOOK_SECRET)) {
    return res.status(401).send('Invalid signature');
  }

  // Store the price observation
  const competitorUrl = await findCompetitorUrl(url);
  await recordPriceObservation(db, competitorUrl.id, result);

  // Check for price changes
  await detectPriceChanges(db);

  res.sendStatus(200);
});