Saas-voc-server/migrations/007_listing_ai.sql

CREATE TABLE IF NOT EXISTS voc.listing_source_snapshot (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id text NOT NULL UNIQUE,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  platform text NOT NULL CHECK (platform IN ('jd')),
  shop_id text NOT NULL,
  product_id text NOT NULL,
  source_hash text NOT NULL,
  payload jsonb NOT NULL,
  detail_status text NOT NULL CHECK (detail_status IN ('available', 'empty', 'failed')),
  source_modified_at timestamptz,
  observed_at timestamptz NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (workspace_id, platform, shop_id, product_id, source_hash)
);
CREATE INDEX IF NOT EXISTS listing_source_current_idx
  ON voc.listing_source_snapshot (workspace_id, platform, product_id, observed_at DESC, id DESC);

CREATE TABLE IF NOT EXISTS voc.listing_score_result (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id text NOT NULL UNIQUE,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  product_id text NOT NULL,
  source_hash text NOT NULL,
  rubric_version text NOT NULL,
  overall_score numeric(6,2),
  coverage jsonb NOT NULL,
  result jsonb NOT NULL,
  model_info jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL,
  UNIQUE (workspace_id, product_id, source_hash, rubric_version)
);
CREATE INDEX IF NOT EXISTS listing_score_product_created_idx
  ON voc.listing_score_result (workspace_id, product_id, created_at DESC, id DESC);

CREATE TABLE IF NOT EXISTS voc.listing_score_job (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id text NOT NULL UNIQUE,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  platform text NOT NULL CHECK (platform IN ('jd')),
  idempotency_key text NOT NULL,
  request_hash text NOT NULL,
  rubric_version text NOT NULL,
  include_ai_suggestions boolean NOT NULL DEFAULT true,
  scope jsonb NOT NULL,
  status text NOT NULL CHECK (status IN ('queued', 'running', 'completed', 'partial', 'failed', 'cancelled')),
  total integer NOT NULL DEFAULT 0,
  processed integer NOT NULL DEFAULT 0,
  succeeded integer NOT NULL DEFAULT 0,
  partial integer NOT NULL DEFAULT 0,
  blocked integer NOT NULL DEFAULT 0,
  failed integer NOT NULL DEFAULT 0,
  requested_by_external_id text NOT NULL,
  requested_at timestamptz NOT NULL,
  started_at timestamptz,
  completed_at timestamptz,
  updated_at timestamptz NOT NULL,
  UNIQUE (workspace_id, idempotency_key)
);
CREATE INDEX IF NOT EXISTS listing_score_job_status_idx
  ON voc.listing_score_job (workspace_id, status, requested_at DESC, id DESC);

CREATE TABLE IF NOT EXISTS voc.listing_score_item (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id text NOT NULL UNIQUE,
  job_id bigint NOT NULL REFERENCES voc.listing_score_job(id) ON DELETE CASCADE,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  product_id text NOT NULL,
  source_hash text NOT NULL,
  status text NOT NULL CHECK (status IN ('queued', 'rules_scored', 'ai_pending', 'scored', 'partial', 'blocked', 'failed')),
  attempts integer NOT NULL DEFAULT 0,
  score_result_public_id text,
  error_code text,
  error_detail_redacted text,
  updated_at timestamptz NOT NULL,
  UNIQUE (job_id, product_id, source_hash)
);
CREATE INDEX IF NOT EXISTS listing_score_item_status_idx
  ON voc.listing_score_item (job_id, status, product_id, id);

CREATE TABLE IF NOT EXISTS voc.listing_version (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id text NOT NULL UNIQUE,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  product_id text NOT NULL,
  version_no integer NOT NULL,
  base_source_hash text NOT NULL,
  base_score_result_public_id text,
  content jsonb NOT NULL,
  status text NOT NULL CHECK (status IN ('draft', 'adopted', 'stale', 'archived')),
  created_by_external_id text NOT NULL,
  created_at timestamptz NOT NULL,
  adopted_at timestamptz,
  UNIQUE (workspace_id, product_id, version_no)
);
CREATE INDEX IF NOT EXISTS listing_version_product_idx
  ON voc.listing_version (workspace_id, product_id, created_at DESC, id DESC);