Blame · Line-by-line history

`0071_personal_semantic.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.

0071_personal_semantic.sqlBlame73 lines · 1 contributor

Normal view Raw

ee7e577Claude	1	-- Gluecron migration 0071: Personal cross-repo semantic index opt-in.
	2	--
	3	-- Today the semantic index (drizzle/0057) is per-repo: searchSemantic()
	4	-- only ranks rows where code_embeddings.repository_id matches one repo.
	5	-- This migration adds:
	6	--
	7	-- 1. users.personal_semantic_index_enabled — opt-in flag. Off by
	8	-- default. The personal cross-repo search refuses to run until the
	9	-- user explicitly enables it via /settings/personal-semantic-toggle.
	10	-- 2. personal_chats — user-scoped chat threads (no repository_id).
	11	-- Same shape as repo_chats but owner_user_id is the only scope key.
	12	-- 3. personal_chat_messages — mirrors repo_chat_messages exactly so
	13	-- the streaming + citations contract is shared.
	14	--
	15	-- All statements wrapped in DO blocks so the migration is idempotent and
	16	-- degrades cleanly on partial replays.
	17
	18	--> statement-breakpoint
	19	DO $$
	20	BEGIN
	21	ALTER TABLE "users"
	22	ADD COLUMN IF NOT EXISTS "personal_semantic_index_enabled" boolean NOT NULL DEFAULT false;
	23	EXCEPTION WHEN OTHERS THEN
	24	RAISE NOTICE 'users.personal_semantic_index_enabled add failed (%); cross-repo chat will be disabled', SQLERRM;
	25	END $$;
	26
	27	--> statement-breakpoint
	28	DO $$
	29	BEGIN
	30	CREATE TABLE IF NOT EXISTS "personal_chats" (
	31	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	32	"owner_user_id" uuid NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
	33	"title" text,
	34	"created_at" timestamptz NOT NULL DEFAULT now(),
	35	"updated_at" timestamptz NOT NULL DEFAULT now()
	36	);
	37	EXCEPTION WHEN OTHERS THEN
	38	RAISE NOTICE 'personal_chats create failed (%); personal chat will be unavailable', SQLERRM;
	39	END $$;
	40
	41	--> statement-breakpoint
	42	DO $$
	43	BEGIN
	44	CREATE INDEX IF NOT EXISTS "personal_chats_owner_updated"
	45	ON "personal_chats" ("owner_user_id", "updated_at" DESC);
	46	EXCEPTION WHEN OTHERS THEN
	47	RAISE NOTICE 'personal_chats_owner_updated index failed (%)', SQLERRM;
	48	END $$;
	49
	50	--> statement-breakpoint
	51	DO $$
	52	BEGIN
	53	CREATE TABLE IF NOT EXISTS "personal_chat_messages" (
	54	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	55	"chat_id" uuid NOT NULL REFERENCES "personal_chats"("id") ON DELETE CASCADE,
	56	"role" text NOT NULL,
	57	"content" text NOT NULL,
	58	"citations" jsonb NOT NULL DEFAULT '[]'::jsonb,
	59	"token_cost" integer NOT NULL DEFAULT 0,
	60	"created_at" timestamptz NOT NULL DEFAULT now()
	61	);
	62	EXCEPTION WHEN OTHERS THEN
	63	RAISE NOTICE 'personal_chat_messages create failed (%); personal chat will be unavailable', SQLERRM;
	64	END $$;
	65
	66	--> statement-breakpoint
	67	DO $$
	68	BEGIN
	69	CREATE INDEX IF NOT EXISTS "personal_chat_messages_chat_created"
	70	ON "personal_chat_messages" ("chat_id", "created_at" ASC);
	71	EXCEPTION WHEN OTHERS THEN
	72	RAISE NOTICE 'personal_chat_messages_chat_created index failed (%)', SQLERRM;
	73	END $$;