31 lines
1.3 KiB
SQL
31 lines
1.3 KiB
SQL
|
-- Browser sessions + enriched crawl storage
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS browser_sessions (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
url TEXT NOT NULL,
|
||
|
|
final_url TEXT,
|
||
|
|
title TEXT,
|
||
|
|
task TEXT,
|
||
|
|
status VARCHAR(32) DEFAULT 'running',
|
||
|
|
content_text TEXT,
|
||
|
|
content_html TEXT,
|
||
|
|
screenshot_b64 TEXT,
|
||
|
|
links JSONB DEFAULT '[]',
|
||
|
|
metadata JSONB DEFAULT '{}',
|
||
|
|
site_id INT REFERENCES monitored_sites(id) ON DELETE SET NULL,
|
||
|
|
error_message TEXT,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
|
completed_at TIMESTAMPTZ
|
||
|
|
);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_browser_sessions_created ON browser_sessions(created_at DESC);
|
||
|
|
|
||
|
|
ALTER TABLE crawled_pages ADD COLUMN IF NOT EXISTS site_id INT REFERENCES monitored_sites(id) ON DELETE SET NULL;
|
||
|
|
ALTER TABLE crawled_pages ADD COLUMN IF NOT EXISTS final_url TEXT;
|
||
|
|
ALTER TABLE crawled_pages ADD COLUMN IF NOT EXISTS content_html TEXT;
|
||
|
|
ALTER TABLE crawled_pages ADD COLUMN IF NOT EXISTS screenshot_b64 TEXT;
|
||
|
|
ALTER TABLE crawled_pages ADD COLUMN IF NOT EXISTS links JSONB DEFAULT '[]';
|
||
|
|
ALTER TABLE crawled_pages ADD COLUMN IF NOT EXISTS metadata JSONB DEFAULT '{}';
|
||
|
|
|
||
|
|
ALTER TABLE monitored_sites ADD COLUMN IF NOT EXISTS last_title TEXT;
|
||
|
|
ALTER TABLE monitored_sites ADD COLUMN IF NOT EXISTS last_snapshot_id INT REFERENCES browser_sessions(id) ON DELETE SET NULL;
|