78 lines
2.5 KiB
SQL
78 lines
2.5 KiB
SQL
CREATE EXTENSION IF NOT EXISTS vector;
|
|
|
|
CREATE TABLE IF NOT EXISTS schema_migrations (
|
|
version text PRIMARY KEY,
|
|
applied_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS knowledge_documents (
|
|
id uuid PRIMARY KEY,
|
|
external_id text NOT NULL UNIQUE,
|
|
title text NOT NULL,
|
|
language text NOT NULL CHECK (language IN ('ru','kk')),
|
|
region_code text NOT NULL DEFAULT 'global' CHECK (length(region_code) > 0),
|
|
category text NOT NULL DEFAULT 'general',
|
|
source_type text NOT NULL,
|
|
source_uri text,
|
|
source_hash text NOT NULL,
|
|
status text NOT NULL DEFAULT 'published' CHECK (status IN ('draft','published','archived')),
|
|
content text NOT NULL,
|
|
question text,
|
|
short_answer text,
|
|
full_answer text,
|
|
keywords text[] NOT NULL DEFAULT '{}',
|
|
source text,
|
|
metadata jsonb NOT NULL DEFAULT '{}',
|
|
valid_from timestamptz,
|
|
valid_to timestamptz,
|
|
priority int NOT NULL DEFAULT 0,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS knowledge_chunks (
|
|
id uuid PRIMARY KEY,
|
|
document_id uuid NOT NULL REFERENCES knowledge_documents(id) ON DELETE CASCADE,
|
|
chunk_index int NOT NULL,
|
|
language text NOT NULL CHECK (language IN ('ru','kk')),
|
|
region_code text NOT NULL,
|
|
title text NOT NULL,
|
|
content text NOT NULL,
|
|
content_hash text NOT NULL,
|
|
token_estimate int NOT NULL DEFAULT 0,
|
|
char_count int NOT NULL DEFAULT 0,
|
|
embedding vector(1536),
|
|
embedding_model text NOT NULL DEFAULT 'fake',
|
|
embedding_provider text NOT NULL DEFAULT 'fake',
|
|
metadata jsonb NOT NULL DEFAULT '{}',
|
|
tsv tsvector,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE(document_id, chunk_index)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS knowledge_ingest_runs (
|
|
id uuid PRIMARY KEY,
|
|
source_path text NOT NULL,
|
|
status text NOT NULL,
|
|
documents_seen int NOT NULL DEFAULT 0,
|
|
documents_ingested int NOT NULL DEFAULT 0,
|
|
chunks_created int NOT NULL DEFAULT 0,
|
|
skipped int NOT NULL DEFAULT 0,
|
|
error text,
|
|
started_at timestamptz NOT NULL DEFAULT now(),
|
|
finished_at timestamptz
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS knowledge_search_logs (
|
|
id uuid PRIMARY KEY,
|
|
call_id text,
|
|
query text NOT NULL,
|
|
language text NOT NULL,
|
|
region_code text NOT NULL,
|
|
result_count int NOT NULL,
|
|
top_score double precision,
|
|
cross_language_fallback_used boolean NOT NULL DEFAULT false,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|