Blame · Line-by-line history

`0057_semantic_index.sql`

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

0057_semantic_index.sqlBlame74 lines · 1 contributor

Normal view Raw

a686079Claude	1	-- Gluecron migration 0057: Continuous semantic index foundation.
	2	--
	3	-- Builds the pgvector-backed `code_embeddings` table that the post-receive
	4	-- hook fills on every push. Layered separately from `code_chunks`
	5	-- (migration 0012/lib/semantic-search.ts) because:
	6	-- * code_chunks: bulk whole-repo reindex, chunked by line range,
	7	-- stores embedding as JSON-text. Survives without pgvector.
	8	-- * code_embeddings: per-file row keyed by (repo, path), refreshed
	9	-- incrementally on push, stores embedding as native vector(1024)
	10	-- so we can ORDER BY embedding <-> $1 in Postgres.
	11	--
	12	-- Best-effort: if pgvector isn't installed (common on self-hosted
	13	-- Postgres without superuser access), every statement here is wrapped
	14	-- in DO blocks that swallow undefined_object/undefined_file/
	15	-- insufficient_privilege/feature_not_supported. The table degrades
	16	-- to a no-op: src/lib/semantic-index.ts probes the table existence
	17	-- and falls back to empty-result behaviour when missing.
	18
	19	--> statement-breakpoint
	20	DO $$
	21	BEGIN
	22	CREATE EXTENSION IF NOT EXISTS vector;
	23	EXCEPTION WHEN OTHERS THEN
	24	RAISE NOTICE 'pgvector unavailable (%); semantic index will degrade to no-op', SQLERRM;
	25	END $$;
	26
	27	--> statement-breakpoint
	28	DO $$
	29	BEGIN
	30	CREATE TABLE IF NOT EXISTS "code_embeddings" (
	31	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	32	"repository_id" uuid NOT NULL REFERENCES "repositories"("id") ON DELETE CASCADE,
	33	"file_path" text NOT NULL,
	34	"blob_sha" text NOT NULL,
	35	"commit_sha" text NOT NULL,
	36	"content_snippet" text NOT NULL DEFAULT '',
	37	"embedding" vector(1024),
	38	"embedding_model" text,
	39	"updated_at" timestamptz NOT NULL DEFAULT now()
	40	);
	41	EXCEPTION WHEN OTHERS THEN
	42	RAISE NOTICE 'code_embeddings create failed (%); semantic index will degrade to no-op', SQLERRM;
	43	END $$;
	44
	45	--> statement-breakpoint
	46	DO $$
	47	BEGIN
	48	CREATE UNIQUE INDEX IF NOT EXISTS "code_embeddings_repo_path_uniq"
	49	ON "code_embeddings" ("repository_id", "file_path");
	50	EXCEPTION WHEN OTHERS THEN
	51	RAISE NOTICE 'code_embeddings unique index failed (%)', SQLERRM;
	52	END $$;
	53
	54	--> statement-breakpoint
	55	DO $$
	56	BEGIN
	57	CREATE INDEX IF NOT EXISTS "code_embeddings_repo_idx"
	58	ON "code_embeddings" ("repository_id");
	59	EXCEPTION WHEN OTHERS THEN
	60	RAISE NOTICE 'code_embeddings repo index failed (%)', SQLERRM;
	61	END $$;
	62
	63	-- ANN-friendly ivfflat index on the embedding column. Only meaningful
	64	-- once a few thousand rows exist; harmless on small data. Wrapped so a
	65	-- missing pgvector type doesn't abort the migration.
	66	--> statement-breakpoint
	67	DO $$
	68	BEGIN
	69	CREATE INDEX IF NOT EXISTS "code_embeddings_vec_idx"
	70	ON "code_embeddings" USING ivfflat ("embedding" vector_cosine_ops)
	71	WITH (lists = 100);
	72	EXCEPTION WHEN OTHERS THEN
	73	RAISE NOTICE 'code_embeddings ivfflat index failed (%); cosine search will fall back to seq scan', SQLERRM;
	74	END $$;