238 lines
11 KiB
SQL
238 lines
11 KiB
SQL
|
-- ============================================================
|
|||
|
|
-- 001_init.sql — 《军事科技》栏目内部工作台 数据库初始化脚本
|
|||
|
|
-- 一次性建完所有核心表骨架（Phase 1-4 全部表）
|
|||
|
|
-- 执行方式: psql -U postgres -d milsci_dev -f 001_init.sql
|
|||
|
|
-- ============================================================
|
|||
|
|
|
|||
|
|
-- 扩展: pgvector（向量检索）
|
|||
|
|
CREATE EXTENSION IF NOT EXISTS vector;
|
|||
|
|
|
|||
|
|
-- ============================================================
|
|||
|
|
-- 1. users — 用户表（Phase 1 用）
|
|||
|
|
-- ============================================================
|
|||
|
|
CREATE TABLE IF NOT EXISTS users (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|||
|
|
display_name VARCHAR(100) NOT NULL,
|
|||
|
|
password_hash VARCHAR(128) NOT NULL,
|
|||
|
|
-- 角色: zhipianren=制片人/管理员, zebian=责编, biandao=编导
|
|||
|
|
role VARCHAR(20) NOT NULL DEFAULT 'biandao'
|
|||
|
|
CHECK (role IN ('zhipianren', 'zebian', 'biandao')),
|
|||
|
|
profile_text TEXT,
|
|||
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_users_role ON users (role);
|
|||
|
|
|
|||
|
|
-- ============================================================
|
|||
|
|
-- 2. episodes — 节目期次（Phase 2 用，字段完整按 dev_plan.md 8.1 节）
|
|||
|
|
-- ============================================================
|
|||
|
|
CREATE TABLE IF NOT EXISTS episodes (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
episode_number INTEGER NOT NULL UNIQUE,
|
|||
|
|
program_name VARCHAR(200) NOT NULL,
|
|||
|
|
air_date DATE NOT NULL,
|
|||
|
|
-- 编导（软引用 + 快照模式）
|
|||
|
|
editor_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|||
|
|
editor_name_snapshot VARCHAR(100) NOT NULL,
|
|||
|
|
-- 收视数据
|
|||
|
|
audience_share NUMERIC(6,4),
|
|||
|
|
audience_rating NUMERIC(6,4),
|
|||
|
|
-- 重播相关
|
|||
|
|
is_rerun BOOLEAN NOT NULL DEFAULT FALSE,
|
|||
|
|
original_episode_id INTEGER REFERENCES episodes(id) ON DELETE SET NULL,
|
|||
|
|
is_time_sensitive BOOLEAN NOT NULL DEFAULT FALSE,
|
|||
|
|
-- 外拍信息
|
|||
|
|
is_outdoor_shoot BOOLEAN NOT NULL DEFAULT FALSE,
|
|||
|
|
outdoor_contact_person VARCHAR(100),
|
|||
|
|
outdoor_contact_info VARCHAR(200),
|
|||
|
|
-- 线索
|
|||
|
|
lead_source VARCHAR(300),
|
|||
|
|
-- 责编备注
|
|||
|
|
notes TEXT,
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_episodes_air_date ON episodes (air_date);
|
|||
|
|
CREATE INDEX idx_episodes_editor_id ON episodes (editor_id);
|
|||
|
|
CREATE INDEX idx_episodes_is_rerun ON episodes (is_rerun);
|
|||
|
|
|
|||
|
|
-- ============================================================
|
|||
|
|
-- 3. yearly_targets — 年度收视目标（Phase 2 用，只增不改）
|
|||
|
|
-- ============================================================
|
|||
|
|
CREATE TABLE IF NOT EXISTS yearly_targets (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
year INTEGER NOT NULL UNIQUE,
|
|||
|
|
base_target NUMERIC(6,4) NOT NULL,
|
|||
|
|
stretch_target NUMERIC(6,4) NOT NULL,
|
|||
|
|
issued_date DATE,
|
|||
|
|
notes TEXT,
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- ============================================================
|
|||
|
|
-- 4. topics — 选题 / 报题单（Phase 4a 用）
|
|||
|
|
-- ============================================================
|
|||
|
|
CREATE TABLE IF NOT EXISTS topics (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
title VARCHAR(200) NOT NULL,
|
|||
|
|
-- 内容概要（双段结构: 引子 + 列举）
|
|||
|
|
content_summary TEXT,
|
|||
|
|
-- 编导关联（软引用 + 快照）
|
|||
|
|
editor_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|||
|
|
editor_name_snapshot VARCHAR(100),
|
|||
|
|
-- 选题状态: draft=草稿, submitted=已提交, reviewing=审阅中, approved=通过, rejected=退回
|
|||
|
|
status VARCHAR(20) NOT NULL DEFAULT 'draft'
|
|||
|
|
CHECK (status IN ('draft', 'submitted', 'reviewing', 'approved', 'rejected')),
|
|||
|
|
-- AI 反馈（JSON 格式，含查重/画像/知识库参考等模块结果）
|
|||
|
|
ai_feedback JSONB,
|
|||
|
|
-- 关联的 episodes / knowledge_items id 列表
|
|||
|
|
related_episodes JSONB,
|
|||
|
|
related_knowledge JSONB,
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_topics_editor_id ON topics (editor_id);
|
|||
|
|
CREATE INDEX idx_topics_status ON topics (status);
|
|||
|
|
|
|||
|
|
-- ============================================================
|
|||
|
|
-- 5. topic_embeddings — 选题语义向量（Phase 4a 用，pgvector）
|
|||
|
|
-- ============================================================
|
|||
|
|
CREATE TABLE IF NOT EXISTS topic_embeddings (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
topic_id INTEGER NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
|
|||
|
|
embedding vector(1536) NOT NULL,
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_topic_embeddings_topic_id ON topic_embeddings (topic_id);
|
|||
|
|
-- pgvector 向量索引（IVFFlat，数据量上去后可用 HNSW）
|
|||
|
|
-- CREATE INDEX idx_topic_embeddings_vector ON topic_embeddings USING ivfflat (embedding vector_cosine_ops);
|
|||
|
|
|
|||
|
|
-- ============================================================
|
|||
|
|
-- 6. schedules — 排期（Phase 4b 用）
|
|||
|
|
-- ============================================================
|
|||
|
|
CREATE TABLE IF NOT EXISTS schedules (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
episode_id INTEGER REFERENCES episodes(id) ON DELETE SET NULL,
|
|||
|
|
planned_air_date DATE NOT NULL,
|
|||
|
|
status VARCHAR(20) NOT NULL DEFAULT 'planned'
|
|||
|
|
CHECK (status IN ('planned', 'confirmed', 'completed', 'cancelled')),
|
|||
|
|
editor_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|||
|
|
notes TEXT,
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_schedules_air_date ON schedules (planned_air_date);
|
|||
|
|
|
|||
|
|
-- ============================================================
|
|||
|
|
-- 7. knowledge_items — 知识库条目（Phase 3 用，2.0 兼容预埋）
|
|||
|
|
-- ============================================================
|
|||
|
|
CREATE TABLE IF NOT EXISTS knowledge_items (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
title VARCHAR(300) NOT NULL,
|
|||
|
|
-- Markdown 格式正文（向 2.0 Karpathy LLM Wiki 形态靠拢）
|
|||
|
|
content_md TEXT,
|
|||
|
|
-- 来源信息
|
|||
|
|
source_type VARCHAR(30) NOT NULL DEFAULT 'manual'
|
|||
|
|
CHECK (source_type IN ('baoti', 'manuscript', 'military_report', 'manual', 'rss')),
|
|||
|
|
source_file_name VARCHAR(300),
|
|||
|
|
source_url VARCHAR(1000),
|
|||
|
|
-- 作者 / 编导
|
|||
|
|
author VARCHAR(100),
|
|||
|
|
publish_date DATE,
|
|||
|
|
-- ⚠️ 2.0 兼容预埋字段（1.0 不用但要建）
|
|||
|
|
tags JSONB DEFAULT '[]'::jsonb,
|
|||
|
|
related_entities JSONB DEFAULT '[]'::jsonb,
|
|||
|
|
related_concepts JSONB DEFAULT '[]'::jsonb,
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_knowledge_items_source_type ON knowledge_items (source_type);
|
|||
|
|
CREATE INDEX idx_knowledge_items_tags ON knowledge_items USING gin (tags);
|
|||
|
|
|
|||
|
|
-- ============================================================
|
|||
|
|
-- 8. knowledge_embeddings — 知识库向量（Phase 3 用，pgvector）
|
|||
|
|
-- ============================================================
|
|||
|
|
CREATE TABLE IF NOT EXISTS knowledge_embeddings (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
knowledge_id INTEGER NOT NULL REFERENCES knowledge_items(id) ON DELETE CASCADE,
|
|||
|
|
chunk_index INTEGER NOT NULL DEFAULT 0,
|
|||
|
|
chunk_text TEXT NOT NULL,
|
|||
|
|
embedding vector(1536) NOT NULL,
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_knowledge_embeddings_knowledge_id ON knowledge_embeddings (knowledge_id);
|
|||
|
|
-- pgvector 向量索引
|
|||
|
|
-- CREATE INDEX idx_knowledge_embeddings_vector ON knowledge_embeddings USING ivfflat (embedding vector_cosine_ops);
|
|||
|
|
|
|||
|
|
-- ============================================================
|
|||
|
|
-- 9. review_comments — 审片意见（Phase 4b 用）
|
|||
|
|
-- ============================================================
|
|||
|
|
CREATE TABLE IF NOT EXISTS review_comments (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
episode_id INTEGER NOT NULL REFERENCES episodes(id) ON DELETE CASCADE,
|
|||
|
|
comment_text TEXT NOT NULL,
|
|||
|
|
reviewer VARCHAR(100),
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_review_comments_episode_id ON review_comments (episode_id);
|
|||
|
|
|
|||
|
|
-- ============================================================
|
|||
|
|
-- 10. shooting_resources — 拍摄资源 / 人脉（Phase 4b 用）
|
|||
|
|
-- ============================================================
|
|||
|
|
CREATE TABLE IF NOT EXISTS shooting_resources (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
contact_person VARCHAR(100) NOT NULL,
|
|||
|
|
contact_info VARCHAR(200),
|
|||
|
|
organization VARCHAR(200),
|
|||
|
|
-- 资源类型: personnel=人员, location=场地, equipment=设备, other=其他
|
|||
|
|
resource_type VARCHAR(20) DEFAULT 'other'
|
|||
|
|
CHECK (resource_type IN ('personnel', 'location', 'equipment', 'other')),
|
|||
|
|
notes TEXT,
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- ============================================================
|
|||
|
|
-- 11. radar_items — 热点雷达条目（Phase 4c 用）
|
|||
|
|
-- ============================================================
|
|||
|
|
CREATE TABLE IF NOT EXISTS radar_items (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
title VARCHAR(300) NOT NULL,
|
|||
|
|
source_url VARCHAR(1000),
|
|||
|
|
source_name VARCHAR(100),
|
|||
|
|
summary TEXT,
|
|||
|
|
-- AI 处理结果
|
|||
|
|
ai_summary TEXT,
|
|||
|
|
style_summary TEXT,
|
|||
|
|
published_at TIMESTAMPTZ,
|
|||
|
|
fetched_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- ============================================================
|
|||
|
|
-- 12. radar_user_actions — 编导对热点的操作（Phase 4c 用）
|
|||
|
|
-- ============================================================
|
|||
|
|
CREATE TABLE IF NOT EXISTS radar_user_actions (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
radar_item_id INTEGER NOT NULL REFERENCES radar_items(id) ON DELETE CASCADE,
|
|||
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|||
|
|
-- 操作类型: deep_dive=深入了解, bookmark=收藏, ignore=忽略
|
|||
|
|
action_type VARCHAR(20) NOT NULL
|
|||
|
|
CHECK (action_type IN ('deep_dive', 'bookmark', 'ignore')),
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
UNIQUE (radar_item_id, user_id, action_type)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- ============================================================
|
|||
|
|
-- 初始化完成
|
|||
|
|
-- ============================================================
|