245 lines
11 KiB
SQL
245 lines
11 KiB
SQL
CREATE TABLE IF NOT EXISTS creator_settings (
|
|
id boolean PRIMARY KEY DEFAULT true CHECK (id),
|
|
lookback_days integer NOT NULL DEFAULT 30 CHECK (lookback_days > 0),
|
|
new_work_interval_seconds bigint NOT NULL DEFAULT 1800 CHECK (new_work_interval_seconds > 0),
|
|
metric_initial_interval_seconds bigint NOT NULL DEFAULT 3600 CHECK (metric_initial_interval_seconds > 0),
|
|
metric_multiplier double precision NOT NULL DEFAULT 2 CHECK (metric_multiplier > 1),
|
|
metric_max_interval_seconds bigint NOT NULL DEFAULT 86400 CHECK (metric_max_interval_seconds >= metric_initial_interval_seconds),
|
|
metric_age_seconds bigint NOT NULL DEFAULT 2592000 CHECK (metric_age_seconds > 0),
|
|
ai_provider text NOT NULL DEFAULT '',
|
|
ai_model text NOT NULL DEFAULT '',
|
|
ai_configured boolean NOT NULL DEFAULT false,
|
|
transcription_provider text NOT NULL DEFAULT '',
|
|
transcription_model text NOT NULL DEFAULT '',
|
|
transcription_configured boolean NOT NULL DEFAULT false,
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
INSERT INTO creator_settings (id) VALUES (true) ON CONFLICT (id) DO NOTHING;
|
|
|
|
CREATE TABLE IF NOT EXISTS creator_account_profile (
|
|
account_id text PRIMARY KEY REFERENCES social_account(id) ON DELETE CASCADE,
|
|
login_username text NOT NULL DEFAULT '',
|
|
password_configured boolean NOT NULL DEFAULT false,
|
|
real_name_status text NOT NULL DEFAULT 'unknown' CHECK (real_name_status IN ('unknown', 'not_real_name', 'recorded')),
|
|
real_name text NOT NULL DEFAULT '',
|
|
identity_number text NOT NULL DEFAULT '',
|
|
note text NOT NULL DEFAULT '',
|
|
business_status text NOT NULL DEFAULT 'normal' CHECK (business_status IN ('normal', 'muted', 'banned', 'deleted')),
|
|
big_account boolean NOT NULL DEFAULT false,
|
|
reply_requirements text NOT NULL DEFAULT '',
|
|
login_status text NOT NULL DEFAULT 'unknown' CHECK (login_status IN ('unknown', 'logged_in', 'needs_login', 'failed', 'manual_required')),
|
|
login_reason text NOT NULL DEFAULT '',
|
|
login_checked_at timestamptz,
|
|
cooldown_seconds bigint NOT NULL DEFAULT 86400 CHECK (cooldown_seconds > 0),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS creator_competitor (
|
|
id text PRIMARY KEY,
|
|
platform text NOT NULL CHECK (platform IN ('douyin', 'xiaohongshu')),
|
|
platform_account_key text NOT NULL,
|
|
nickname text NOT NULL DEFAULT '',
|
|
avatar_url text NOT NULL DEFAULT '',
|
|
homepage_url text NOT NULL,
|
|
enabled boolean NOT NULL DEFAULT true,
|
|
sync_status text NOT NULL DEFAULT 'idle' CHECK (sync_status IN ('idle', 'running', 'paused', 'failed', 'blocked')),
|
|
sync_cursor text NOT NULL DEFAULT '',
|
|
sync_error text NOT NULL DEFAULT '',
|
|
last_sync_at timestamptz,
|
|
next_sync_at timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (platform, platform_account_key)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS creator_work (
|
|
id text PRIMARY KEY,
|
|
platform text NOT NULL CHECK (platform IN ('douyin', 'xiaohongshu')),
|
|
work_key text NOT NULL,
|
|
source_type text NOT NULL CHECK (source_type IN ('owned', 'competitor')),
|
|
source_id text NOT NULL,
|
|
author_name text NOT NULL DEFAULT '',
|
|
title text NOT NULL DEFAULT '',
|
|
body text NOT NULL DEFAULT '',
|
|
published_at timestamptz,
|
|
published_at_status text NOT NULL DEFAULT 'verified' CHECK (published_at_status IN ('verified', 'pending_verification', 'invalid', 'future')),
|
|
original_url text NOT NULL DEFAULT '',
|
|
cover_url text NOT NULL DEFAULT '',
|
|
likes bigint,
|
|
comments_count bigint,
|
|
shares bigint,
|
|
latest_metrics_at timestamptz,
|
|
next_metric_at timestamptz,
|
|
metric_stop_reason text NOT NULL DEFAULT '',
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (platform, work_key)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS creator_work_source_idx ON creator_work (source_type, source_id, published_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS creator_work_metric (
|
|
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
work_id text NOT NULL REFERENCES creator_work(id) ON DELETE CASCADE,
|
|
collected_at timestamptz NOT NULL DEFAULT now(),
|
|
likes bigint,
|
|
comments_count bigint,
|
|
shares bigint,
|
|
UNIQUE (work_id, collected_at)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS creator_material_job (
|
|
work_id text PRIMARY KEY REFERENCES creator_work(id) ON DELETE CASCADE,
|
|
selected boolean NOT NULL DEFAULT false,
|
|
select_confirmed_at timestamptz,
|
|
download_status text NOT NULL DEFAULT 'not_started' CHECK (download_status IN ('not_started', 'running', 'succeeded', 'failed')),
|
|
video_reference text NOT NULL DEFAULT '',
|
|
audio_status text NOT NULL DEFAULT 'not_started' CHECK (audio_status IN ('not_started', 'running', 'succeeded', 'no_audio', 'failed')),
|
|
audio_reference text NOT NULL DEFAULT '',
|
|
transcription_status text NOT NULL DEFAULT 'not_started' CHECK (transcription_status IN ('not_started', 'running', 'succeeded', 'no_speech', 'failed')),
|
|
transcript text NOT NULL DEFAULT '',
|
|
failed_step text NOT NULL DEFAULT '',
|
|
failure_reason text NOT NULL DEFAULT '',
|
|
rewrite_confirmed_at timestamptz,
|
|
rewrite_requirement text NOT NULL DEFAULT '',
|
|
generated_title text NOT NULL DEFAULT '',
|
|
generated_script text NOT NULL DEFAULT '',
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS creator_comment (
|
|
id text PRIMARY KEY,
|
|
platform text NOT NULL CHECK (platform IN ('douyin', 'xiaohongshu')),
|
|
comment_key text NOT NULL,
|
|
work_id text NOT NULL REFERENCES creator_work(id) ON DELETE CASCADE,
|
|
author_uid text NOT NULL DEFAULT '',
|
|
author_name text NOT NULL DEFAULT '',
|
|
content text NOT NULL,
|
|
published_at timestamptz,
|
|
collected_at timestamptz NOT NULL DEFAULT now(),
|
|
comment_type text NOT NULL DEFAULT 'top_level' CHECK (comment_type IN ('top_level', 'reply', 'unknown')),
|
|
UNIQUE (platform, comment_key)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS creator_comment_work_idx ON creator_comment (work_id, published_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS creator_lead_rule (
|
|
id text PRIMARY KEY,
|
|
name text NOT NULL,
|
|
enabled boolean NOT NULL DEFAULT true,
|
|
source_type text NOT NULL DEFAULT 'all' CHECK (source_type IN ('all', 'owned', 'competitor')),
|
|
topic text NOT NULL,
|
|
include_keywords jsonb NOT NULL,
|
|
exclude_keywords jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
ai_requirement text NOT NULL,
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS creator_comment_rule_result (
|
|
comment_id text NOT NULL REFERENCES creator_comment(id) ON DELETE CASCADE,
|
|
rule_id text NOT NULL REFERENCES creator_lead_rule(id) ON DELETE CASCADE,
|
|
status text NOT NULL CHECK (status IN ('not_analysed', 'topic_no_match', 'keyword_no_match', 'not_lead', 'lead', 'failed')),
|
|
reason text NOT NULL DEFAULT '',
|
|
matched_keywords jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
rule_snapshot jsonb NOT NULL,
|
|
analysed_at timestamptz,
|
|
PRIMARY KEY (comment_id, rule_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS creator_relation (
|
|
big_account_id text NOT NULL REFERENCES social_account(id) ON DELETE CASCADE,
|
|
small_account_id text PRIMARY KEY REFERENCES social_account(id) ON DELETE CASCADE,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CHECK (big_account_id <> small_account_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS creator_strategy (
|
|
id text PRIMARY KEY,
|
|
big_account_id text NOT NULL REFERENCES social_account(id) ON DELETE CASCADE,
|
|
execution_account_id text NOT NULL REFERENCES social_account(id) ON DELETE RESTRICT,
|
|
position integer NOT NULL CHECK (position > 0),
|
|
enabled boolean NOT NULL DEFAULT true,
|
|
event_types jsonb NOT NULL,
|
|
action text NOT NULL CHECK (action IN ('dm', 'reply_comment', 'like_comment', 'like_work', 'follow', 'repost')),
|
|
target_type text NOT NULL DEFAULT '',
|
|
candidate_texts jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (big_account_id, position)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS creator_cooldown (
|
|
big_account_id text NOT NULL REFERENCES social_account(id) ON DELETE CASCADE,
|
|
interactor_uid text NOT NULL,
|
|
event_id text NOT NULL,
|
|
strategy_id text NOT NULL,
|
|
execution_account_id text NOT NULL,
|
|
started_at timestamptz NOT NULL DEFAULT now(),
|
|
expires_at timestamptz NOT NULL,
|
|
PRIMARY KEY (big_account_id, interactor_uid)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS creator_event (
|
|
id text PRIMARY KEY,
|
|
platform text NOT NULL CHECK (platform IN ('douyin', 'xiaohongshu')),
|
|
receiving_account_id text NOT NULL REFERENCES social_account(id) ON DELETE CASCADE,
|
|
event_key text NOT NULL,
|
|
event_type text NOT NULL,
|
|
interactor_uid text NOT NULL DEFAULT '',
|
|
comment_id text,
|
|
work_id text,
|
|
platform_event_at timestamptz,
|
|
received_at timestamptz NOT NULL DEFAULT now(),
|
|
processing_started_at timestamptz,
|
|
displayed_at timestamptz,
|
|
state text NOT NULL DEFAULT 'received' CHECK (state IN ('received', 'baseline', 'ignored', 'unmatched', 'blocked', 'processing', 'succeeded', 'failed', 'uncertain')),
|
|
reason text NOT NULL DEFAULT '',
|
|
strategy_id text,
|
|
execution_account_id text,
|
|
UNIQUE (platform, receiving_account_id, event_key)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS creator_operation (
|
|
id text PRIMARY KEY,
|
|
idempotency_key text NOT NULL UNIQUE,
|
|
source text NOT NULL CHECK (source IN ('manual', 'automatic')),
|
|
action text NOT NULL,
|
|
platform text NOT NULL CHECK (platform IN ('douyin', 'xiaohongshu')),
|
|
account_id text NOT NULL REFERENCES social_account(id) ON DELETE RESTRICT,
|
|
target_uid text NOT NULL DEFAULT '',
|
|
target_comment_id text NOT NULL DEFAULT '',
|
|
target_work_id text NOT NULL DEFAULT '',
|
|
text text NOT NULL DEFAULT '',
|
|
event_id text,
|
|
strategy_id text,
|
|
request_hash text NOT NULL,
|
|
state text NOT NULL DEFAULT 'created' CHECK (state IN ('created', 'processing', 'succeeded', 'failed', 'uncertain', 'blocked')),
|
|
evidence jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
reason text NOT NULL DEFAULT '',
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS creator_conversation (
|
|
id text PRIMARY KEY,
|
|
platform text NOT NULL CHECK (platform IN ('douyin', 'xiaohongshu')),
|
|
account_id text NOT NULL REFERENCES social_account(id) ON DELETE CASCADE,
|
|
peer_uid text NOT NULL,
|
|
peer_name text NOT NULL DEFAULT '',
|
|
last_message_at timestamptz,
|
|
UNIQUE (account_id, peer_uid)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS creator_message (
|
|
id text PRIMARY KEY,
|
|
conversation_id text NOT NULL REFERENCES creator_conversation(id) ON DELETE CASCADE,
|
|
platform_message_key text NOT NULL DEFAULT '',
|
|
direction text NOT NULL CHECK (direction IN ('inbound', 'outbound')),
|
|
message_type text NOT NULL DEFAULT 'text',
|
|
text text NOT NULL DEFAULT '',
|
|
sent_state text NOT NULL DEFAULT 'received' CHECK (sent_state IN ('received', 'pending', 'succeeded', 'failed', 'uncertain')),
|
|
message_at timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (conversation_id, platform_message_key)
|
|
);
|