File size: 4,152 Bytes
abd4352 c5f9c5f abd4352 c5f9c5f abd4352 c5f9c5f abd4352 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | -- scripts/migrate.sql
-- Run once against your Neon database to set up all required tables.
-- Requires the pgvector extension (available on all Neon plans).
-- ── Extensions ────────────────────────────────────────────────────────────────
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS vector;
-- ── Sessions ──────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sessions (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id TEXT NOT NULL,
connector_id TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- ── Query history ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS query_history (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
session_id TEXT NOT NULL,
user_query TEXT NOT NULL,
generated_code TEXT,
code_type TEXT CHECK (code_type IN ('sql','pandas')) DEFAULT 'sql',
insight_text TEXT,
chart_spec JSONB,
result_preview JSONB,
retry_count INT DEFAULT 0,
latency_ms INT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_query_history_session ON query_history(session_id);
-- ── Schema embeddings (pgvector, 384-dim for MiniLM-L6-v2) ───────────────────
CREATE TABLE IF NOT EXISTS schema_embeddings (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
connector_id TEXT NOT NULL,
table_name TEXT NOT NULL,
column_summary TEXT,
embedding vector(384),
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_schema_emb_connector
ON schema_embeddings(connector_id);
CREATE INDEX IF NOT EXISTS idx_schema_emb_hnsw
ON schema_embeddings USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- ── Memory embeddings (similar past queries) ───────────────────────────────────
CREATE TABLE IF NOT EXISTS memory_embeddings (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
session_id TEXT NOT NULL,
query TEXT NOT NULL,
insight TEXT,
table_names TEXT[],
embedding vector(384),
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_memory_emb_session
ON memory_embeddings(session_id);
CREATE INDEX IF NOT EXISTS idx_memory_emb_hnsw
ON memory_embeddings USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- ── Dashboards ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dashboards (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id TEXT NOT NULL,
name TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- ── Dashboard panels ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dashboard_panels (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id TEXT NOT NULL,
session_id TEXT,
dashboard_id UUID REFERENCES dashboards(id) ON DELETE SET NULL,
title TEXT NOT NULL,
chart_spec JSONB NOT NULL,
query TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_panels_user ON dashboard_panels(user_id);
CREATE INDEX IF NOT EXISTS idx_panels_dashboard ON dashboard_panels(dashboard_id);
|