forked from EduCraft/curriculum-project-hub
hongjr03
49e1e2f19e
parentId RESTRICT prevented Organization.deleteMany from clearing nested folder trees during test resetDb, leaving half-wiped rows and breaking subsequent upserts. Service still refuses non-empty folder deletes; DB cascade only unblocks org teardown / full wipe.
34 lines
2.1 KiB
SQL
34 lines
2.1 KiB
SQL
-- ADR-0028 agent configuration folder tree. One org-scoped transparent folder
|
|
-- tree shared by Agent roles and skills (management-surface grouping only);
|
|
-- skill name / roleId uniqueness, role→skill bindings, run-time skill loading
|
|
-- and Feishu slash commands never reference folders. A folder deletes only
|
|
-- when empty (service-enforced); item `folderId` references are SetNull as
|
|
-- backstop.
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "OrganizationAgentConfigFolder" (
|
|
"id" TEXT NOT NULL,
|
|
"organizationId" TEXT NOT NULL,
|
|
"parentId" TEXT,
|
|
"name" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "OrganizationAgentConfigFolder_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "OrganizationAgentSkill" ADD COLUMN "folderId" TEXT;
|
|
ALTER TABLE "OrganizationAgentRole" ADD COLUMN "folderId" TEXT;
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "OrganizationAgentConfigFolder_organizationId_parentId_idx" ON "OrganizationAgentConfigFolder"("organizationId", "parentId");
|
|
CREATE INDEX "OrganizationAgentSkill_organizationId_folderId_idx" ON "OrganizationAgentSkill"("organizationId", "folderId");
|
|
CREATE INDEX "OrganizationAgentRole_organizationId_folderId_idx" ON "OrganizationAgentRole"("organizationId", "folderId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "OrganizationAgentConfigFolder" ADD CONSTRAINT "OrganizationAgentConfigFolder_organizationId_fkey" FOREIGN KEY ("organizationId") REFERENCES "Organization"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "OrganizationAgentConfigFolder" ADD CONSTRAINT "OrganizationAgentConfigFolder_parentId_fkey" FOREIGN KEY ("parentId") REFERENCES "OrganizationAgentConfigFolder"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "OrganizationAgentSkill" ADD CONSTRAINT "OrganizationAgentSkill_folderId_fkey" FOREIGN KEY ("folderId") REFERENCES "OrganizationAgentConfigFolder"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
ALTER TABLE "OrganizationAgentRole" ADD CONSTRAINT "OrganizationAgentRole_folderId_fkey" FOREIGN KEY ("folderId") REFERENCES "OrganizationAgentConfigFolder"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|