124 lines
5.0 KiB
SQL
124 lines
5.0 KiB
SQL
-- ============================================================
|
|
-- willhaben-tracker — consolidated schema (single source of truth)
|
|
-- Merged from: 01-init.sql, 02-image-and-pricing.sql, 03-global-keywords.sql
|
|
-- ============================================================
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 1. users (whitelisted Telegram users)
|
|
-- -----------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
telegram_id bigint UNIQUE NOT NULL,
|
|
username text,
|
|
first_name text,
|
|
is_admin boolean NOT NULL DEFAULT false,
|
|
is_active boolean NOT NULL DEFAULT true,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 2. keywords (global search keywords — deduplicated across users)
|
|
-- -----------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS keywords (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
keyword text NOT NULL,
|
|
interval_minutes int NOT NULL DEFAULT 60,
|
|
is_active boolean NOT NULL DEFAULT true,
|
|
initial_loaded boolean NOT NULL DEFAULT false,
|
|
last_scraped_at timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_keywords_unique_lower
|
|
ON keywords(LOWER(keyword));
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 3. keyword_subscriptions (many-to-many: user ↔ keyword)
|
|
-- -----------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS keyword_subscriptions (
|
|
keyword_id uuid REFERENCES keywords(id) ON DELETE CASCADE NOT NULL,
|
|
user_id uuid REFERENCES users(id) ON DELETE CASCADE NOT NULL,
|
|
PRIMARY KEY (keyword_id, user_id),
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 4. ads (raw ad snapshots, globally deduplicated by wh_ad_id)
|
|
-- -----------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS ads (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
wh_ad_id text UNIQUE NOT NULL,
|
|
raw_json jsonb NOT NULL,
|
|
title text NOT NULL,
|
|
price numeric,
|
|
location text,
|
|
url text,
|
|
published_at timestamptz,
|
|
first_seen_at timestamptz NOT NULL DEFAULT now(),
|
|
main_image_url text,
|
|
postcode text,
|
|
modified_at timestamptz
|
|
);
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 5. price_history (track price changes per ad)
|
|
-- -----------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS price_history (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
ad_id uuid REFERENCES ads(id) ON DELETE CASCADE NOT NULL,
|
|
old_price numeric NOT NULL,
|
|
new_price numeric NOT NULL,
|
|
changed_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (ad_id, old_price, new_price)
|
|
);
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 6. notifications (audit log of sent Telegram messages)
|
|
-- -----------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS notifications (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id uuid REFERENCES users(id) ON DELETE CASCADE NOT NULL,
|
|
ad_id uuid REFERENCES ads(id) ON DELETE SET NULL,
|
|
message_id int,
|
|
sent_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 7. scrape_logs (worker health / debugging)
|
|
-- -----------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS scrape_logs (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
keyword_id uuid REFERENCES keywords(id) ON DELETE CASCADE NOT NULL,
|
|
status text NOT NULL CHECK (status IN ('success', 'error', 'rate_limited')),
|
|
ads_found int NOT NULL DEFAULT 0,
|
|
new_ads int NOT NULL DEFAULT 0,
|
|
error_message text,
|
|
scraped_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ============================================================
|
|
-- Indexes
|
|
-- ============================================================
|
|
|
|
-- Keywords: fast lookup for active keywords ordered by last scrape time
|
|
CREATE INDEX IF NOT EXISTS idx_keywords_active_scraped
|
|
ON keywords(is_active, last_scraped_at) WHERE is_active = true;
|
|
|
|
-- Keyword subscriptions: find all subscribers of a keyword
|
|
CREATE INDEX IF NOT EXISTS idx_keyword_subscriptions_user_id
|
|
ON keyword_subscriptions(user_id);
|
|
|
|
-- Ads: fast lookup by willhaben ad ID (unique constraint already implies an index)
|
|
|
|
-- Price history: look up changes for a specific ad
|
|
CREATE INDEX IF NOT EXISTS idx_price_history_ad_id
|
|
ON price_history(ad_id);
|
|
|
|
-- Notifications: recent messages per user
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_user_sent
|
|
ON notifications(user_id, sent_at DESC);
|
|
|
|
-- Scrape logs: latest runs per keyword
|
|
CREATE INDEX IF NOT EXISTS idx_scrape_logs_keyword_at
|
|
ON scrape_logs(keyword_id, scraped_at DESC);
|