tihao-ai/server/db/schema.sql

CREATE TABLE IF NOT EXISTS creator_profile_clean (
  id BIGSERIAL PRIMARY KEY,
  platform TEXT NOT NULL,
  platform_user_id TEXT NOT NULL,
  display_name TEXT NOT NULL,
  gender TEXT,
  fans_count BIGINT DEFAULT 0,
  liked_collect_count BIGINT DEFAULT 0,
  content_type TEXT,
  content_tags JSONB DEFAULT '[]'::jsonb,
  persona_tags JSONB DEFAULT '[]'::jsonb,
  city TEXT,
  geo_location TEXT,
  profile_url TEXT,
  cooperation_method TEXT,
  image_price INTEGER DEFAULT 0,
  video_price INTEGER DEFAULT 0,
  min_price INTEGER DEFAULT 0,
  cooperation_status TEXT,
  source_kind TEXT NOT NULL DEFAULT 'uploaded',
  source_provider TEXT NOT NULL DEFAULT 'local-upload',
  source_file TEXT,
  source_confidence INTEGER DEFAULT 90,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  embedding_text TEXT NOT NULL DEFAULT '',
  embedding DOUBLE PRECISION[] NOT NULL DEFAULT '{}',
  UNIQUE(platform, platform_user_id)
);

CREATE TABLE IF NOT EXISTS provider_raw_cache (
  id BIGSERIAL PRIMARY KEY,
  provider TEXT NOT NULL,
  endpoint TEXT NOT NULL,
  request_params JSONB NOT NULL DEFAULT '{}'::jsonb,
  response_body JSONB NOT NULL DEFAULT '{}'::jsonb,
  fetched_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at TIMESTAMPTZ NOT NULL DEFAULT now() + interval '30 days',
  normalized_status TEXT NOT NULL DEFAULT 'pending',
  archive_path TEXT
);

CREATE TABLE IF NOT EXISTS provider_creator_raw_cache (
  id BIGSERIAL PRIMARY KEY,
  provider TEXT NOT NULL,
  endpoint TEXT NOT NULL,
  request_params JSONB NOT NULL DEFAULT '{}'::jsonb,
  platform TEXT NOT NULL,
  platform_user_id TEXT NOT NULL,
  display_name TEXT NOT NULL DEFAULT '',
  fans_count BIGINT DEFAULT 0,
  liked_collect_count BIGINT DEFAULT 0,
  content_type TEXT,
  content_tags JSONB DEFAULT '[]'::jsonb,
  persona_tags JSONB DEFAULT '[]'::jsonb,
  city TEXT,
  geo_location TEXT,
  profile_url TEXT,
  cooperation_method TEXT,
  image_price INTEGER DEFAULT 0,
  video_price INTEGER DEFAULT 0,
  min_price INTEGER DEFAULT 0,
  cooperation_status TEXT,
  raw_creator JSONB NOT NULL DEFAULT '{}'::jsonb,
  fetched_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at TIMESTAMPTZ NOT NULL DEFAULT now() + interval '30 days',
  normalized_status TEXT NOT NULL DEFAULT 'raw',
  embedding_text TEXT NOT NULL DEFAULT '',
  embedding DOUBLE PRECISION[] NOT NULL DEFAULT '{}',
  UNIQUE(provider, platform, platform_user_id)
);

CREATE TABLE IF NOT EXISTS creator_retrieval_event (
  id BIGSERIAL PRIMARY KEY,
  task_id TEXT,
  query_text TEXT NOT NULL DEFAULT '',
  criteria JSONB NOT NULL DEFAULT '{}'::jsonb,
  local_hit_count INTEGER NOT NULL DEFAULT 0,
  provider_hit_count INTEGER NOT NULL DEFAULT 0,
  final_count INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_creator_clean_platform ON creator_profile_clean(platform);
CREATE INDEX IF NOT EXISTS idx_creator_clean_city ON creator_profile_clean(city);
CREATE INDEX IF NOT EXISTS idx_creator_clean_updated_at ON creator_profile_clean(updated_at);
CREATE INDEX IF NOT EXISTS idx_creator_clean_content_tags ON creator_profile_clean USING GIN(content_tags);
CREATE INDEX IF NOT EXISTS idx_provider_raw_provider_time ON provider_raw_cache(provider, fetched_at DESC);
CREATE INDEX IF NOT EXISTS idx_provider_raw_expires_at ON provider_raw_cache(expires_at);
CREATE INDEX IF NOT EXISTS idx_provider_raw_request_params ON provider_raw_cache USING GIN(request_params);
CREATE INDEX IF NOT EXISTS idx_provider_creator_raw_platform ON provider_creator_raw_cache(platform);
CREATE INDEX IF NOT EXISTS idx_provider_creator_raw_city ON provider_creator_raw_cache(city);
CREATE INDEX IF NOT EXISTS idx_provider_creator_raw_expires_at ON provider_creator_raw_cache(expires_at);
CREATE INDEX IF NOT EXISTS idx_provider_creator_raw_content_tags ON provider_creator_raw_cache USING GIN(content_tags);

CREATE OR REPLACE VIEW creator_profile_retrieval_view AS
SELECT
  'clean'::text AS cache_layer,
  source_provider,
  platform,
  platform_user_id,
  display_name,
  fans_count,
  liked_collect_count,
  content_type,
  content_tags,
  persona_tags,
  city,
  geo_location,
  profile_url,
  cooperation_method,
  image_price,
  video_price,
  min_price,
  cooperation_status,
  updated_at AS cached_at,
  NULL::timestamptz AS expires_at
FROM creator_profile_clean
UNION ALL
SELECT
  'provider_raw'::text AS cache_layer,
  provider AS source_provider,
  platform,
  platform_user_id,
  display_name,
  fans_count,
  liked_collect_count,
  content_type,
  content_tags,
  persona_tags,
  city,
  geo_location,
  profile_url,
  cooperation_method,
  image_price,
  video_price,
  min_price,
  cooperation_status,
  fetched_at AS cached_at,
  expires_at
FROM provider_creator_raw_cache
WHERE expires_at > now();

CREATE OR REPLACE FUNCTION cosine_similarity(a DOUBLE PRECISION[], b DOUBLE PRECISION[])
RETURNS DOUBLE PRECISION
LANGUAGE SQL
IMMUTABLE
AS $$
  WITH pairs AS (
    SELECT
      av.val AS av,
      bv.val AS bv
    FROM unnest(a) WITH ORDINALITY AS av(val, idx)
    JOIN unnest(b) WITH ORDINALITY AS bv(val, idx)
      ON av.idx = bv.idx
  ),
  sums AS (
    SELECT
      SUM(av * bv) AS dot,
      SQRT(SUM(av * av)) AS norm_a,
      SQRT(SUM(bv * bv)) AS norm_b
    FROM pairs
  )
  SELECT CASE
    WHEN norm_a = 0 OR norm_b = 0 OR norm_a IS NULL OR norm_b IS NULL THEN 0
    ELSE dot / (norm_a * norm_b)
  END
  FROM sums;
$$;