-- drizzle-migration-linter: allow-breaking
-- drizzle-migration-linter: reason=Scopes no-project files to a conversation. (1) Index swap is collision-free on existing data so no dedup is needed: both new partial unique indexes are satisfied by anything the old files_user_filename_uidx (user_id, filename WHERE project_id IS NULL) already allowed -- (user_id, conversation_id, filename) adds a column on a subset of rows (strictly more permissive), and (user_id, filename) WHERE conversation_id IS NULL is the old rule on a subset of rows (already unique under the old full-table index). (2) The DELETE is a one-time cleanup of files that became unreachable when no-project files moved from a global per-user set (My Files) to conversation scope: a no-project file with no conversation (project_id IS NULL AND conversation_id IS NULL) is surfaced by no tool, route, or UI -- these are leftover headless writes or files left behind by pre-fix conversation deletes (SET NULL), inert and undeletable by users. Any conversation_file_touches rows pointing at them are removed automatically through their files foreign key. For db-provider rows the bytes are inline and go away with the row; the filesystem provider stores no rows today, so no external bytes are left behind.
DROP INDEX "files_user_filename_uidx";--> statement-breakpoint
CREATE UNIQUE INDEX "files_user_conversation_filename_uidx" ON "files" USING btree ("user_id","conversation_id","filename") WHERE "files"."project_id" IS NULL AND "files"."conversation_id" IS NOT NULL;--> statement-breakpoint
CREATE UNIQUE INDEX "files_user_filename_orphan_uidx" ON "files" USING btree ("user_id","filename") WHERE "files"."project_id" IS NULL AND "files"."conversation_id" IS NULL;--> statement-breakpoint
DELETE FROM "files" WHERE "project_id" IS NULL AND "conversation_id" IS NULL;
