Your Name
47 lines
1.5 KiB
SQL
47 lines
1.5 KiB
SQL
CREATE TABLE IF NOT EXISTS schema_migrations (
|
|
version TEXT PRIMARY KEY,
|
|
applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS articles (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
external_id TEXT NOT NULL UNIQUE,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
title TEXT NOT NULL,
|
|
lead TEXT NOT NULL DEFAULT '',
|
|
body TEXT NOT NULL,
|
|
category_name TEXT NOT NULL,
|
|
category_tag TEXT NOT NULL,
|
|
producer_name TEXT NOT NULL,
|
|
producer_tag TEXT NOT NULL,
|
|
source_url TEXT,
|
|
seo_title TEXT NOT NULL DEFAULT '',
|
|
seo_description TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published')),
|
|
published_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_articles_feed
|
|
ON articles(status, published_at DESC, id DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_articles_category
|
|
ON articles(category_tag, status, published_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_articles_producer
|
|
ON articles(producer_tag, status, published_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS article_media (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
article_id BIGINT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
|
|
media_type TEXT NOT NULL CHECK (media_type IN ('image', 'video')),
|
|
url TEXT NOT NULL,
|
|
alt_text TEXT NOT NULL DEFAULT '',
|
|
width INTEGER,
|
|
height INTEGER,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_article_media_order
|
|
ON article_media(article_id, sort_order, id);
|