Blame · Line-by-line history

`0060_repo_chats.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.

0060_repo_chats.sqlBlame69 lines · 1 contributor

Normal view Raw

38d31d3Claude	1	-- Gluecron migration 0060: AI repo rubber-duck chat tables.
	2	--
	3	-- Per-repo, per-user conversational threads grounded in the semantic
	4	-- index (see src/lib/semantic-index.ts). Distinct from the older
	5	-- `ai_chats` table, which is a JSON-blob single-row design that pre-
	6	-- dates streaming. The new model stores one row per message so we
	7	-- can stream partials, attach per-message citations, and track token
	8	-- cost without rewriting the whole blob on each turn.
	9	--
	10	-- Wrapped in DO blocks so the migration is safe to re-run and
	11	-- gracefully ignores duplicates / missing parents on partial replays.
	12
	13	--> statement-breakpoint
	14	DO $$
	15	BEGIN
	16	CREATE TABLE IF NOT EXISTS "repo_chats" (
	17	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	18	"repository_id" uuid NOT NULL REFERENCES "repositories"("id") ON DELETE CASCADE,
	19	"owner_user_id" uuid NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
	20	"title" text,
	21	"created_at" timestamptz NOT NULL DEFAULT now(),
	22	"updated_at" timestamptz NOT NULL DEFAULT now()
	23	);
	24	EXCEPTION WHEN OTHERS THEN
	25	RAISE NOTICE 'repo_chats create failed (%); repo chat will be unavailable', SQLERRM;
	26	END $$;
	27
	28	--> statement-breakpoint
	29	DO $$
	30	BEGIN
	31	CREATE INDEX IF NOT EXISTS "repo_chats_owner_updated"
	32	ON "repo_chats" ("owner_user_id", "updated_at" DESC);
	33	EXCEPTION WHEN OTHERS THEN
	34	RAISE NOTICE 'repo_chats_owner_updated index failed (%)', SQLERRM;
	35	END $$;
	36
	37	--> statement-breakpoint
	38	DO $$
	39	BEGIN
	40	CREATE INDEX IF NOT EXISTS "repo_chats_repo"
	41	ON "repo_chats" ("repository_id");
	42	EXCEPTION WHEN OTHERS THEN
	43	RAISE NOTICE 'repo_chats_repo index failed (%)', SQLERRM;
	44	END $$;
	45
	46	--> statement-breakpoint
	47	DO $$
	48	BEGIN
	49	CREATE TABLE IF NOT EXISTS "repo_chat_messages" (
	50	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	51	"chat_id" uuid NOT NULL REFERENCES "repo_chats"("id") ON DELETE CASCADE,
	52	"role" text NOT NULL,
	53	"content" text NOT NULL,
	54	"citations" jsonb NOT NULL DEFAULT '[]'::jsonb,
	55	"token_cost" integer NOT NULL DEFAULT 0,
	56	"created_at" timestamptz NOT NULL DEFAULT now()
	57	);
	58	EXCEPTION WHEN OTHERS THEN
	59	RAISE NOTICE 'repo_chat_messages create failed (%); repo chat will be unavailable', SQLERRM;
	60	END $$;
	61
	62	--> statement-breakpoint
	63	DO $$
	64	BEGIN
	65	CREATE INDEX IF NOT EXISTS "repo_chat_messages_chat_created"
	66	ON "repo_chat_messages" ("chat_id", "created_at" ASC);
	67	EXCEPTION WHEN OTHERS THEN
	68	RAISE NOTICE 'repo_chat_messages_chat_created index failed (%)', SQLERRM;
	69	END $$;