105 lines
4 KiB
SQL
105 lines
4 KiB
SQL
-- ==============================================================================
|
|
-- Migration: 001_initial_schema.sql
|
|
-- Description: Production PostgreSQL schema for Telegram Pipeline & Curation
|
|
-- ==============================================================================
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- Enum Types
|
|
DO $$ BEGIN
|
|
CREATE TYPE draft_status AS ENUM ('pending', 'approved', 'edited', 'rejected', 'published');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE queue_status AS ENUM ('queued', 'publishing', 'success', 'failed');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE media_type_enum AS ENUM ('none', 'photo', 'video', 'document', 'animation');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
-- 1. Channels Table
|
|
CREATE TABLE IF NOT EXISTS channels (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
telegram_handle VARCHAR(128) NOT NULL UNIQUE,
|
|
title VARCHAR(255),
|
|
niche VARCHAR(64) NOT NULL,
|
|
subscriber_count INT DEFAULT 0,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
last_scraped_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_channels_niche ON channels (niche);
|
|
CREATE INDEX IF NOT EXISTS idx_channels_active_scraped ON channels (is_active, last_scraped_at);
|
|
|
|
-- 2. Raw Posts Table
|
|
CREATE TABLE IF NOT EXISTS raw_posts (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
channel_id UUID NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
|
|
message_id BIGINT NOT NULL,
|
|
original_text TEXT,
|
|
post_url TEXT NOT NULL,
|
|
media_type media_type_enum NOT NULL DEFAULT 'none',
|
|
media_url TEXT,
|
|
views INT DEFAULT 0,
|
|
reactions INT DEFAULT 0,
|
|
posted_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT uq_channel_message UNIQUE (channel_id, message_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_raw_posts_views ON raw_posts (views DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_raw_posts_posted_at ON raw_posts (posted_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_raw_posts_channel ON raw_posts (channel_id);
|
|
|
|
-- 3. Content Drafts Table
|
|
CREATE TABLE IF NOT EXISTS content_drafts (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
raw_post_id UUID NOT NULL REFERENCES raw_posts(id) ON DELETE CASCADE,
|
|
ai_generated_text TEXT NOT NULL,
|
|
current_text TEXT NOT NULL,
|
|
status draft_status NOT NULL DEFAULT 'pending',
|
|
brand_target VARCHAR(64) NOT NULL DEFAULT '@NetVibe_VPN',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_drafts_status ON content_drafts (status);
|
|
CREATE INDEX IF NOT EXISTS idx_drafts_created_at ON content_drafts (created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_drafts_raw_post ON content_drafts (raw_post_id);
|
|
|
|
-- 4. Feedback Logs Table (Active Learning Memory)
|
|
CREATE TABLE IF NOT EXISTS feedback_logs (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
draft_id UUID NOT NULL REFERENCES content_drafts(id) ON DELETE CASCADE,
|
|
original_ai_text TEXT NOT NULL,
|
|
final_human_text TEXT NOT NULL,
|
|
diff_analysis JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
rejection_reason TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_feedback_logs_created ON feedback_logs (created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_feedback_logs_draft ON feedback_logs (draft_id);
|
|
|
|
-- 5. Publish Queue Table
|
|
CREATE TABLE IF NOT EXISTS publish_queue (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
draft_id UUID NOT NULL UNIQUE REFERENCES content_drafts(id) ON DELETE CASCADE,
|
|
scheduled_time TIMESTAMPTZ NOT NULL,
|
|
status queue_status NOT NULL DEFAULT 'queued',
|
|
telegram_msg_id BIGINT,
|
|
retry_count INT NOT NULL DEFAULT 0,
|
|
error_log TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_queue_dispatch ON publish_queue (status, scheduled_time ASC) WHERE status = 'queued';
|