192 lines
7.4 KiB
SQL
192 lines
7.4 KiB
SQL
-- content-atom: schema для MVP stage 1
|
|
-- Postgres (atom-db) + pgvector (расширение "vector")
|
|
|
|
CREATE EXTENSION IF NOT EXISTS vector;
|
|
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
CREATE EXTENSION IF NOT EXISTS btree_gin;
|
|
|
|
-- Проекты (домены)
|
|
CREATE TABLE projects (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
slug TEXT UNIQUE NOT NULL,
|
|
name TEXT NOT NULL,
|
|
kind TEXT NOT NULL, -- site | service | social | podcast
|
|
niche TEXT,
|
|
role TEXT NOT NULL DEFAULT 'satellite', -- flagship | satellite | experiment | service
|
|
locale TEXT DEFAULT 'ru',
|
|
settings JSONB DEFAULT '{}',
|
|
status TEXT DEFAULT 'active', -- active | paused | archived
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
CREATE INDEX ON projects (role, niche);
|
|
|
|
-- Атомы
|
|
CREATE TABLE atoms (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
|
|
type TEXT NOT NULL, -- claim | fact | stat | quote | definition | instruction | case | comparison | qa | opinion | hook | transition | cta | media_ref
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
|
title TEXT,
|
|
summary TEXT,
|
|
body JSONB NOT NULL,
|
|
plain_text TEXT,
|
|
language TEXT DEFAULT 'ru',
|
|
entities JSONB DEFAULT '[]',
|
|
topics TEXT[] DEFAULT '{}',
|
|
intent TEXT,
|
|
audience TEXT,
|
|
confidence NUMERIC(3,2),
|
|
quality_score NUMERIC(4,2),
|
|
word_count INT,
|
|
source_refs JSONB DEFAULT '[]',
|
|
embedding VECTOR(1024),
|
|
generator JSONB,
|
|
cost JSONB,
|
|
pipeline_stage TEXT,
|
|
parent_atom_id UUID,
|
|
lineage_root_id UUID,
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
updated_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
CREATE INDEX ON atoms (project_id, type, status);
|
|
CREATE INDEX ON atoms USING gin (plain_text gin_trgm_ops);
|
|
CREATE INDEX ON atoms USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
|
|
|
|
-- Версии под каналы
|
|
CREATE TABLE atom_versions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
atom_id UUID REFERENCES atoms(id) ON DELETE CASCADE,
|
|
channel TEXT NOT NULL, -- web_article | telegram | youtube_script | podcast | newsletter | service_card
|
|
format TEXT NOT NULL, -- markdown | html | plain | srt | ssml | json
|
|
content TEXT NOT NULL,
|
|
meta JSONB DEFAULT '{}',
|
|
quality_score NUMERIC(4,2),
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
UNIQUE (atom_id, channel, format)
|
|
);
|
|
CREATE INDEX ON atom_versions (channel, created_at DESC);
|
|
|
|
-- Задачи пайплайна + стоимость
|
|
CREATE TABLE jobs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
kind TEXT NOT NULL, -- generate | edit | adapt | publish | index
|
|
atom_id UUID REFERENCES atoms(id),
|
|
status TEXT DEFAULT 'queued',
|
|
attempts INT DEFAULT 0,
|
|
max_attempts INT DEFAULT 3,
|
|
payload JSONB DEFAULT '{}',
|
|
error TEXT,
|
|
scheduled_at TIMESTAMPTZ DEFAULT now(),
|
|
started_at TIMESTAMPTZ,
|
|
finished_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE runs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
job_id UUID REFERENCES jobs(id) ON DELETE CASCADE,
|
|
stage TEXT NOT NULL,
|
|
model TEXT,
|
|
tokens_in INT,
|
|
tokens_out INT,
|
|
seconds NUMERIC,
|
|
cost_usd NUMERIC DEFAULT 0,
|
|
quality NUMERIC(4,2),
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
-- Индексы
|
|
CREATE INDEX ON jobs (status, scheduled_at);
|
|
CREATE INDEX ON runs (stage, created_at DESC);
|
|
|
|
-- Связи между атомами (дедup + lineage)
|
|
CREATE TABLE atom_relations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
atom_id_a UUID REFERENCES atoms(id) ON DELETE CASCADE,
|
|
atom_id_b UUID REFERENCES atoms(id) ON DELETE CASCADE,
|
|
relation_type TEXT NOT NULL, -- duplicate | related | superset | contradiction | parent_of
|
|
similarity NUMERIC(4,3), -- cosine similarity (0-1)
|
|
shared_topics TEXT[] DEFAULT '{}',
|
|
confidence NUMERIC(3,2),
|
|
source TEXT, -- semantic | lexical | manual
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
UNIQUE (atom_id_a, atom_id_b, relation_type)
|
|
);
|
|
CREATE INDEX ON atom_relations (atom_id_a, relation_type);
|
|
CREATE INDEX ON atom_relations (atom_id_b, relation_type);
|
|
CREATE INDEX ON atom_relations (similarity DESC);
|
|
|
|
-- Медиа-активы (stage 3)
|
|
CREATE TABLE assets (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
|
|
asset_type TEXT NOT NULL, -- image | audio | video | subtitle
|
|
format TEXT NOT NULL, -- mp4 | mp3 | wav | jpg | png | srt
|
|
uri TEXT, -- путь или URL
|
|
title TEXT,
|
|
duration_sec NUMERIC,
|
|
transcript TEXT,
|
|
meta JSONB DEFAULT '{}',
|
|
size_bytes BIGINT,
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
CREATE INDEX ON assets (project_id, asset_type);
|
|
|
|
-- Привязка атомов к медиа (stage 3)
|
|
CREATE TABLE atom_assets (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
atom_id UUID REFERENCES atoms(id) ON DELETE CASCADE,
|
|
asset_id UUID REFERENCES assets(id) ON DELETE CASCADE,
|
|
start_time_sec NUMERIC, -- таймкод начала
|
|
end_time_sec NUMERIC, -- таймкод конца
|
|
role TEXT, -- narration | b-roll | caption
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
UNIQUE (atom_id, asset_id, start_time_sec)
|
|
);
|
|
CREATE INDEX ON atom_assets (asset_id);
|
|
CREATE INDEX ON atom_assets (atom_id);
|
|
|
|
-- Композиции (stage 3)
|
|
CREATE TABLE compositions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
|
|
channel TEXT NOT NULL, -- youtube | podcast | newsletter | web | telegram
|
|
slug TEXT,
|
|
title TEXT,
|
|
status TEXT DEFAULT 'draft', -- draft | building | ready | scheduled | published
|
|
recipe JSONB NOT NULL, -- правила сборки: типы, темы, порядок
|
|
atom_ids UUID[] NOT NULL, -- порядок атомов в композиции
|
|
content TEXT,
|
|
meta JSONB DEFAULT '{}',
|
|
scheduled_at TIMESTAMPTZ,
|
|
published_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
UNIQUE (project_id, channel, slug)
|
|
);
|
|
CREATE INDEX ON compositions (project_id, channel, status);
|
|
|
|
-- Публикации (stage 3)
|
|
CREATE TABLE publications (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
composition_id UUID REFERENCES compositions(id) ON DELETE CASCADE,
|
|
target TEXT NOT NULL, -- URL, channel_id, video_id, list_id
|
|
external_id TEXT,
|
|
published_at TIMESTAMPTZ DEFAULT now(),
|
|
metrics JSONB DEFAULT '{}' -- views, clicks, conversions, revenue
|
|
);
|
|
CREATE INDEX ON publications (composition_id);
|
|
|
|
-- Промпты как данные (stage 3)
|
|
CREATE TABLE prompts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
slug TEXT NOT NULL,
|
|
version TEXT NOT NULL DEFAULT 'v1',
|
|
description TEXT,
|
|
system_prompt TEXT,
|
|
user_prompt TEXT,
|
|
params JSONB DEFAULT '{}', -- model, temperature, max_tokens
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
updated_at TIMESTAMPTZ DEFAULT now(),
|
|
UNIQUE (slug, version)
|
|
);
|