Lago
98c71d4232
- Reduce docker-compose from 6 services to 1 (worker only) - Connect via supavisor pooled port (6543) on dedicated Supabase - Add willhaben_tracker schema with search_path in asyncpg pool init - Create consolidated migration SQL for Supabase SQL Editor - Update .env.example with new connection pattern
122 lines
4.8 KiB
SQL
122 lines
4.8 KiB
SQL
CREATE SCHEMA IF NOT EXISTS willhaben_tracker;
|
|
SET search_path TO willhaben_tracker;
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 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()
|
|
);
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 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));
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 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()
|
|
);
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 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
|
|
);
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 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)
|
|
);
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 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()
|
|
);
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 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
|
|
-- ============================================================
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_keywords_active_scraped
|
|
ON keywords(is_active, last_scraped_at) WHERE is_active = true;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_keyword_subscriptions_user_id
|
|
ON keyword_subscriptions(user_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_price_history_ad_id
|
|
ON price_history(ad_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_user_sent
|
|
ON notifications(user_id, sent_at DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_scrape_logs_keyword_at
|
|
ON scrape_logs(keyword_id, scraped_at DESC);
|
|
|
|
-- -----------------------------------------------------------
|
|
-- Seed: initial admin user
|
|
-- -----------------------------------------------------------
|
|
INSERT INTO users (telegram_id, username, first_name, is_admin, is_active)
|
|
VALUES (298181113, NULL, 'Admin', true, true)
|
|
ON CONFLICT (telegram_id) DO NOTHING;
|