nkey
ca460453af
All checks were successful
hufs-notice-crawler-cicd / build_push_deploy (push) Successful in 8m35s
31 lines
1.0 KiB
SQL
31 lines
1.0 KiB
SQL
CREATE TABLE IF NOT EXISTS scraped_posts (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
board_key VARCHAR(32) NOT NULL,
|
|
board_name VARCHAR(100) NOT NULL,
|
|
board_id INTEGER NOT NULL,
|
|
article_id INTEGER NOT NULL,
|
|
title VARCHAR(500) NOT NULL,
|
|
post_url TEXT NOT NULL,
|
|
author VARCHAR(100),
|
|
published_at TIMESTAMP,
|
|
summary TEXT,
|
|
content_text TEXT,
|
|
attachments JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT uq_scraped_posts_board_article UNIQUE (board_key, article_id),
|
|
CONSTRAINT ck_scraped_posts_board_key CHECK (board_key IN ('notice', 'archive', 'jobs'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_scraped_posts_board_key_created_at
|
|
ON scraped_posts (board_key, created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS crawl_runs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
started_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
finished_at TIMESTAMP,
|
|
status VARCHAR(20) NOT NULL,
|
|
discovered_count INTEGER NOT NULL DEFAULT 0,
|
|
inserted_count INTEGER NOT NULL DEFAULT 0,
|
|
error_message TEXT
|
|
);
|