Build a Price Monitor with AlterLab + Supabase

-- Tracked products
create table products (
  id         uuid primary key default gen_random_uuid(),
  name       text not null,
  url        text unique not null,
  created_at timestamptz default now()
);

-- Price history
create table price_history (
  id           uuid primary key default gen_random_uuid(),
  product_id   uuid references products(id) on delete cascade,
  price        numeric(10, 2),
  currency     text default 'USD',
  in_stock     boolean,
  checked_at   timestamptz default now()
);

-- Fast lookups
create index on price_history (product_id, checked_at desc);
create index on products (url);

insert into products (name, url) values
  ('Widget Pro', 'https://shop.example.com/widget-pro'),
  ('Gadget X',   'https://shop.example.com/gadget-x');

ALTERLAB_API_KEY=sk_live_your_key_here
SLACK_WEBHOOK_URL=https://hooks.slack.com/services/...  (optional)
PRICE_ALERT_THRESHOLD=50.00  (alert when price drops below this)

import { createClient } from "jsr:@supabase/supabase-js@2";

const supabase = createClient(
  Deno.env.get("SUPABASE_URL")!,
  Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!,
);

// JSON Schema for structured price extraction
const PRICE_SCHEMA = {
  type: "object",
  properties: {
    price:    { type: "number",  description: "Current price as a number, e.g. 29.99" },
    currency: { type: "string",  description: "ISO currency code, e.g. USD" },
    in_stock: { type: "boolean", description: "Whether the product is currently in stock" },
    name:     { type: "string",  description: "Product name or title" },
  },
  required: ["price"],
};

async function scrapePrice(url: string) {
  const res = await fetch("https://api.alterlab.io/api/v1/scrape", {
    method: "POST",
    headers: {
      "X-API-Key":    Deno.env.get("ALTERLAB_API_KEY")!,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      url,
      extraction_schema: PRICE_SCHEMA,
    }),
  });

  if (!res.ok) {
    throw new Error(`AlterLab error ${res.status}: ${await res.text()}`);
  }

  const data = await res.json();
  return data.extraction as {
    price: number;
    currency?: string;
    in_stock?: boolean;
    name?: string;
  };
}

async function sendSlackAlert(product: string, price: number, url: string) {
  const webhook = Deno.env.get("SLACK_WEBHOOK_URL");
  if (!webhook) return;

  await fetch(webhook, {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({
      text: `Price alert: *${product}* dropped to *$${price}*\n${url}`,
    }),
  });
}

Deno.serve(async () => {
  // Load all tracked products
  const { data: products, error } = await supabase
    .from("products")
    .select("*");

  if (error) {
    return new Response(JSON.stringify({ error: error.message }), {
      status: 500,
      headers: { "Content-Type": "application/json" },
    });
  }

  const threshold = parseFloat(Deno.env.get("PRICE_ALERT_THRESHOLD") ?? "0");
  const results = [];

  for (const product of products ?? []) {
    try {
      const extracted = await scrapePrice(product.url);

      // Store in price_history
      await supabase.from("price_history").insert({
        product_id: product.id,
        price:      extracted.price,
        currency:   extracted.currency ?? "USD",
        in_stock:   extracted.in_stock ?? true,
        checked_at: new Date().toISOString(),
      });

      // Alert if price dropped below threshold
      if (threshold > 0 && extracted.price < threshold) {
        await sendSlackAlert(product.name, extracted.price, product.url);
      }

      results.push({ name: product.name, price: extracted.price, ok: true });
    } catch (err) {
      results.push({ name: product.name, error: String(err), ok: false });
    }
  }

  return new Response(JSON.stringify({ checked: results.length, results }), {
    headers: { "Content-Type": "application/json" },
  });
});

supabase functions deploy price-monitor --no-verify-jwt

-- Check prices every hour
select cron.schedule(
  'price-monitor-hourly',
  '0 * * * *',
  $$
  select net.http_post(
    url     := current_setting('app.supabase_url')
                || '/functions/v1/price-monitor',
    headers := jsonb_build_object(
      'Authorization', 'Bearer '
                         || current_setting('app.service_role_key'),
      'Content-Type',   'application/json'
    ),
    body := '{}'::jsonb
  );
  $$
);

-- Latest price for each product
select p.name, ph.price, ph.currency, ph.in_stock, ph.checked_at
from price_history ph
join products p on p.id = ph.product_id
where ph.checked_at = (
  select max(checked_at)
  from price_history ph2
  where ph2.product_id = ph.product_id
);

-- Price history for a specific product (last 7 days)
select ph.price, ph.checked_at
from price_history ph
join products p on p.id = ph.product_id
where p.name = 'Widget Pro'
  and ph.checked_at > now() - interval '7 days'
order by ph.checked_at desc;

-- Biggest price drops in the last 24 hours
with ranked as (
  select
    p.name,
    ph.price,
    lag(ph.price) over (partition by ph.product_id order by ph.checked_at) as prev_price,
    ph.checked_at
  from price_history ph
  join products p on p.id = ph.product_id
  where ph.checked_at > now() - interval '24 hours'
)
select name, prev_price, price,
       round((price - prev_price) / prev_price * 100, 1) as pct_change
from ranked
where prev_price is not null
order by pct_change asc
limit 10;

from alterlab import AlterLabSync
from supabase import create_client
from datetime import datetime, UTC

scraper = AlterLabSync(api_key="sk_live_...")
supabase = create_client(
    "https://your-project.supabase.co",
    "your-service-role-key",
)

PRICE_SCHEMA = {
    "type": "object",
    "properties": {
        "price":    {"type": "number",  "description": "Current price"},
        "currency": {"type": "string",  "description": "ISO currency code"},
        "in_stock": {"type": "boolean", "description": "In stock status"},
    },
    "required": ["price"],
}

def check_prices():
    products = supabase.table("products").select("*").execute().data

    with scraper:
        for product in products:
            result = scraper.scrape(
                product["url"],
                extraction_schema=PRICE_SCHEMA,
            )
            extracted = result.get("extraction") or {}

            supabase.table("price_history").insert({
                "product_id": product["id"],
                "price":      extracted.get("price"),
                "currency":   extracted.get("currency", "USD"),
                "in_stock":   extracted.get("in_stock", True),
                "checked_at": datetime.now(UTC).isoformat(),
            }).execute()

            print(f"{product['name']}: ${extracted.get('price')}")

if __name__ == "__main__":
    check_prices()