zsElectric-boot/sql/postgresql/aftersales_policy_pgvector.sql

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS aftersales_policy_doc (
  id BIGSERIAL PRIMARY KEY,
  doc_code VARCHAR(64) NOT NULL,
  title VARCHAR(256) NOT NULL,
  category VARCHAR(64) NOT NULL,
  issue_type VARCHAR(64) NOT NULL,
  version VARCHAR(32) NOT NULL,
  content TEXT NOT NULL,
  content_hash CHAR(64) NOT NULL,
  source_type VARCHAR(32) NOT NULL DEFAULT 'manual',
  source_uri VARCHAR(512),
  effective_at TIMESTAMPTZ,
  expired_at TIMESTAMPTZ,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  metadata JSONB,
  create_time TIMESTAMPTZ NOT NULL DEFAULT now(),
  update_time TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (doc_code, version)
);

CREATE INDEX IF NOT EXISTS idx_policy_doc_issue_enabled
  ON aftersales_policy_doc (issue_type, enabled);

CREATE INDEX IF NOT EXISTS idx_policy_doc_category
  ON aftersales_policy_doc (category);

CREATE TABLE IF NOT EXISTS aftersales_policy_chunk (
  id BIGSERIAL PRIMARY KEY,
  doc_id BIGINT NOT NULL REFERENCES aftersales_policy_doc(id),
  chunk_no INT NOT NULL,
  chunk_text TEXT NOT NULL,
  token_count INT,
  embedding VECTOR(1536) NOT NULL,
  content_hash CHAR(64) NOT NULL,
  issue_type VARCHAR(64) NOT NULL,
  metadata JSONB,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  create_time TIMESTAMPTZ NOT NULL DEFAULT now(),
  update_time TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (doc_id, chunk_no)
);

CREATE INDEX IF NOT EXISTS idx_policy_chunk_doc
  ON aftersales_policy_chunk (doc_id);

CREATE INDEX IF NOT EXISTS idx_policy_chunk_issue_enabled
  ON aftersales_policy_chunk (issue_type, enabled);

CREATE INDEX IF NOT EXISTS idx_policy_chunk_metadata
  ON aftersales_policy_chunk USING GIN (metadata);

CREATE INDEX IF NOT EXISTS idx_policy_chunk_embedding
  ON aftersales_policy_chunk USING hnsw (embedding vector_cosine_ops);