mirror of
https://github.com/multica-ai/multica.git
synced 2026-07-06 05:49:12 +02:00
81bde585ba
* MUL-3467: batch load squad roster skills Co-authored-by: multica-agent <github@multica.ai> * test: isolate redis-backed test databases Co-authored-by: multica-agent <github@multica.ai> --------- Co-authored-by: J <j@multica.ai> Co-authored-by: multica-agent <github@multica.ai>
120 lines
3.5 KiB
SQL
120 lines
3.5 KiB
SQL
-- Skill CRUD
|
|
|
|
-- name: ListSkillsByWorkspace :many
|
|
SELECT * FROM skill
|
|
WHERE workspace_id = $1
|
|
ORDER BY name ASC;
|
|
|
|
-- name: ListSkillSummariesByWorkspace :many
|
|
-- Same as ListSkillsByWorkspace but omits the SKILL.md `content` column. Used
|
|
-- by list endpoints (CLI table, web list page) where the body is never read;
|
|
-- shipping it everywhere blew up payload size on workspaces with many skills
|
|
-- and caused 15s CLI timeouts from high-latency regions (GH multica-ai/multica#2174).
|
|
SELECT id, workspace_id, name, description, config, created_by, created_at, updated_at
|
|
FROM skill
|
|
WHERE workspace_id = $1
|
|
ORDER BY name ASC;
|
|
|
|
-- name: GetSkill :one
|
|
SELECT * FROM skill
|
|
WHERE id = $1;
|
|
|
|
-- name: GetSkillInWorkspace :one
|
|
SELECT * FROM skill
|
|
WHERE id = $1 AND workspace_id = $2;
|
|
|
|
-- name: GetSkillByWorkspaceAndName :one
|
|
-- Used by agent-template materialization to implement find-or-create: when a
|
|
-- template references a skill by name that already exists in the workspace,
|
|
-- reuse the existing skill_id rather than INSERT (which would fail the
|
|
-- UNIQUE(workspace_id, name) constraint from migration 008).
|
|
SELECT * FROM skill
|
|
WHERE workspace_id = $1 AND name = $2;
|
|
|
|
-- name: CreateSkill :one
|
|
INSERT INTO skill (workspace_id, name, description, content, config, created_by)
|
|
VALUES ($1, $2, $3, $4, $5, $6)
|
|
RETURNING *;
|
|
|
|
-- name: UpdateSkill :one
|
|
UPDATE skill SET
|
|
name = COALESCE(sqlc.narg('name'), name),
|
|
description = COALESCE(sqlc.narg('description'), description),
|
|
content = COALESCE(sqlc.narg('content'), content),
|
|
config = COALESCE(sqlc.narg('config'), config),
|
|
updated_at = now()
|
|
WHERE id = $1
|
|
RETURNING *;
|
|
|
|
-- name: DeleteSkill :exec
|
|
-- Defense-in-depth: workspace_id is a SQL-layer tenant guard. See DeleteIssue.
|
|
DELETE FROM skill WHERE id = $1 AND workspace_id = $2;
|
|
|
|
-- Skill File CRUD
|
|
|
|
-- name: ListSkillFiles :many
|
|
SELECT * FROM skill_file
|
|
WHERE skill_id = $1
|
|
ORDER BY path ASC;
|
|
|
|
-- name: GetSkillFile :one
|
|
SELECT * FROM skill_file
|
|
WHERE id = $1;
|
|
|
|
-- name: UpsertSkillFile :one
|
|
INSERT INTO skill_file (skill_id, path, content)
|
|
VALUES ($1, $2, $3)
|
|
ON CONFLICT (skill_id, path) DO UPDATE SET
|
|
content = EXCLUDED.content,
|
|
updated_at = now()
|
|
RETURNING *;
|
|
|
|
-- name: DeleteSkillFile :exec
|
|
DELETE FROM skill_file WHERE id = $1;
|
|
|
|
-- name: DeleteSkillFilesBySkill :exec
|
|
DELETE FROM skill_file WHERE skill_id = $1;
|
|
|
|
-- Agent-Skill junction
|
|
|
|
-- name: ListAgentSkills :many
|
|
SELECT s.* FROM skill s
|
|
JOIN agent_skill ask ON ask.skill_id = s.id
|
|
WHERE ask.agent_id = $1
|
|
ORDER BY s.name ASC;
|
|
|
|
-- name: ListAgentSkillSummaries :many
|
|
-- Summary variant for the agent skills list endpoint — omits `content` for
|
|
-- the same reason as ListSkillSummariesByWorkspace.
|
|
SELECT s.id, s.workspace_id, s.name, s.description, s.config, s.created_by, s.created_at, s.updated_at
|
|
FROM skill s
|
|
JOIN agent_skill ask ON ask.skill_id = s.id
|
|
WHERE ask.agent_id = $1
|
|
ORDER BY s.name ASC;
|
|
|
|
-- name: ListAgentSkillNamesByAgentIDs :many
|
|
SELECT ask.agent_id, s.name
|
|
FROM agent_skill ask
|
|
JOIN skill s ON s.id = ask.skill_id
|
|
WHERE ask.agent_id = ANY(sqlc.arg('agent_ids')::uuid[])
|
|
ORDER BY ask.agent_id, s.name ASC;
|
|
|
|
-- name: AddAgentSkill :exec
|
|
INSERT INTO agent_skill (agent_id, skill_id)
|
|
VALUES ($1, $2)
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- name: RemoveAgentSkill :exec
|
|
DELETE FROM agent_skill
|
|
WHERE agent_id = $1 AND skill_id = $2;
|
|
|
|
-- name: RemoveAllAgentSkills :exec
|
|
DELETE FROM agent_skill WHERE agent_id = $1;
|
|
|
|
-- name: ListAgentSkillsByWorkspace :many
|
|
SELECT ask.agent_id, s.id, s.name, s.description
|
|
FROM agent_skill ask
|
|
JOIN skill s ON s.id = ask.skill_id
|
|
WHERE s.workspace_id = $1
|
|
ORDER BY s.name ASC;
|