backend/schema.sql

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  asin TEXT UNIQUE,
  title TEXT NOT NULL,
  category TEXT,
  description TEXT,
  price NUMERIC(10,2),
  rating NUMERIC(3,2),
  embedding vector(768),
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS products_embedding_hnsw
  ON products
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

CREATE INDEX IF NOT EXISTS products_fts
  ON products
  USING gin (to_tsvector('english', title || ' ' || COALESCE(description, '')));

CREATE INDEX IF NOT EXISTS products_category
  ON products (category);

CREATE TABLE IF NOT EXISTS customers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  segment TEXT DEFAULT 'standard',
  lifetime_value NUMERIC(12,2) DEFAULT 0,
  embedding vector(768),
  ai_metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID REFERENCES customers(id),
  amount NUMERIC(10,2),
  status TEXT DEFAULT 'completed',
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS orders_customer_created_at
  ON orders (customer_id, created_at DESC);

CREATE TABLE IF NOT EXISTS reviews (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID REFERENCES customers(id),
  product_id UUID REFERENCES products(id),
  text TEXT NOT NULL,
  sentiment_score NUMERIC(4,3),
  embedding vector(768),
  ai_metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS reviews_embedding_hnsw
  ON reviews
  USING hnsw (embedding vector_cosine_ops);

CREATE INDEX IF NOT EXISTS reviews_metadata_gin
  ON reviews USING gin (ai_metadata);

CREATE INDEX IF NOT EXISTS reviews_fts
  ON reviews USING gin (to_tsvector('english', text));

CREATE TABLE IF NOT EXISTS transactions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID REFERENCES customers(id),
  amount NUMERIC(12,2),
  merchant TEXT,
  location TEXT,
  fraud_score NUMERIC(5,4) DEFAULT 0,
  flagged BOOLEAN DEFAULT false,
  ai_metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS transactions_customer_fraud
  ON transactions (customer_id, fraud_score DESC);

DO $$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'neuralvault_readonly') THEN
    CREATE ROLE neuralvault_readonly LOGIN;
  END IF;
END
$$;

GRANT USAGE ON SCHEMA public TO neuralvault_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO neuralvault_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO neuralvault_readonly;

-- ── NeuralVault 3.0 Operational & Observability Tracing Schema (Consolidated in Postgres) ──

CREATE TABLE IF NOT EXISTS query_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  query_natural TEXT NOT NULL,
  query_sql TEXT,
  execution_success BOOLEAN DEFAULT false,
  row_count INT DEFAULT 0,
  execution_ms NUMERIC(10,2),
  validation_passed BOOLEAN DEFAULT false,
  retry_count INT DEFAULT 0,
  intent_class TEXT,
  indexes_used TEXT[],
  session_id TEXT,
  timestamp NUMERIC(16,4) DEFAULT extract(epoch from now())
);

CREATE TABLE IF NOT EXISTS search_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  query TEXT NOT NULL,
  mode TEXT NOT NULL,
  ef_search INT DEFAULT 40,
  result_count INT DEFAULT 0,
  vector_latency_ms NUMERIC(10,2),
  fts_latency_ms NUMERIC(10,2),
  total_latency_ms NUMERIC(10,2),
  top_result_score NUMERIC(10,4),
  session_id TEXT,
  source TEXT DEFAULT 'postgres',
  timestamp NUMERIC(16,4) DEFAULT extract(epoch from now())
);

CREATE TABLE IF NOT EXISTS query_traces (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  trace_id TEXT NOT NULL UNIQUE,
  query TEXT NOT NULL,
  spans JSONB NOT NULL,
  total_latency_ms NUMERIC(10,2),
  timestamp NUMERIC(16,4) DEFAULT extract(epoch from now())
);

CREATE TABLE IF NOT EXISTS evaluations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  query TEXT NOT NULL,
  response TEXT,
  metrics JSONB NOT NULL,
  timestamp NUMERIC(16,4) DEFAULT extract(epoch from now())
);

CREATE TABLE IF NOT EXISTS benchmark_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  test_runs JSONB NOT NULL,
  timestamp NUMERIC(16,4) DEFAULT extract(epoch from now())
);