ehsan-dashboards/dashboards/VPN_chanel/backend/migrations/001_initial_schema.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';