28 lines
980 B
SQL
28 lines
980 B
SQL
CREATE TABLE IF NOT EXISTS employee_news_links (
|
|
id SERIAL PRIMARY KEY,
|
|
employee_id INTEGER NOT NULL REFERENCES employees(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
url TEXT,
|
|
summary TEXT,
|
|
published_at TIMESTAMPTZ,
|
|
published_year INTEGER,
|
|
source_hash VARCHAR(64) NOT NULL,
|
|
raw_data JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT uq_employee_news_links_employee_url UNIQUE (employee_id, url),
|
|
CONSTRAINT uq_employee_news_links_employee_source_hash UNIQUE (employee_id, source_hash)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_employee_news_links_employee_id
|
|
ON employee_news_links (employee_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_employee_news_links_url
|
|
ON employee_news_links (url);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_employee_news_links_published_at
|
|
ON employee_news_links (published_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_employee_news_links_published_year
|
|
ON employee_news_links (published_year);
|