2026-10-01 11:49:21 -04:00
|
|
|
-- name: InsertChatProject :one
|
2026-11-17 21:59:01 +07:00
|
|
|
INSERT INTO chat_projects (
|
|
|
|
|
id, organization_id, owner_id, name, description, icon,
|
|
|
|
|
limit_templates, default_template_id, default_template_version_preset_id, default_parameters
|
|
|
|
|
)
|
2026-10-01 11:49:21 -04:00
|
|
|
VALUES (
|
|
|
|
|
COALESCE(sqlc.narg('id')::uuid, gen_random_uuid()),
|
|
|
|
|
@organization_id::uuid,
|
|
|
|
|
@owner_id::uuid,
|
|
|
|
|
@name::text,
|
|
|
|
|
@description::text,
|
2026-11-17 21:59:01 +07:00
|
|
|
@icon::text,
|
|
|
|
|
@limit_templates::boolean,
|
|
|
|
|
sqlc.narg('default_template_id')::uuid,
|
|
|
|
|
sqlc.narg('default_template_version_preset_id')::uuid,
|
|
|
|
|
COALESCE(sqlc.narg('default_parameters')::jsonb, '{}'::jsonb)
|
2026-10-01 11:49:21 -04:00
|
|
|
)
|
|
|
|
|
RETURNING *;
|
|
|
|
|
|
|
|
|
|
-- name: GetChatProjectByID :one
|
|
|
|
|
SELECT *
|
|
|
|
|
FROM chat_projects
|
|
|
|
|
WHERE id = @id::uuid;
|
|
|
|
|
|
2026-10-14 10:42:00 -04:00
|
|
|
-- name: GetChatProjectByIDForUpdate :one
|
|
|
|
|
SELECT *
|
|
|
|
|
FROM chat_projects
|
|
|
|
|
WHERE id = @id::uuid
|
|
|
|
|
FOR UPDATE;
|
|
|
|
|
|
|
|
|
|
-- name: GetChatProjectsOwnedOrSharedWithUserID :many
|
|
|
|
|
-- Entries match regardless of the actions they grant, so callers must
|
|
|
|
|
-- authorize each row.
|
|
|
|
|
SELECT chat_projects.*
|
|
|
|
|
FROM chat_projects
|
|
|
|
|
WHERE chat_projects.owner_id = @user_id::uuid
|
|
|
|
|
OR (
|
|
|
|
|
(
|
|
|
|
|
chat_projects.user_acl ? (@user_id::uuid)::text
|
|
|
|
|
OR chat_projects.group_acl ?| ARRAY(
|
|
|
|
|
SELECT group_members.group_id::text
|
|
|
|
|
FROM group_members
|
|
|
|
|
WHERE group_members.user_id = @user_id::uuid
|
|
|
|
|
UNION ALL
|
|
|
|
|
SELECT organization_members.organization_id::text
|
|
|
|
|
FROM organization_members
|
|
|
|
|
WHERE organization_members.user_id = @user_id::uuid
|
|
|
|
|
)
|
|
|
|
|
)
|
|
|
|
|
AND EXISTS (
|
|
|
|
|
SELECT 1
|
|
|
|
|
FROM organization_members
|
|
|
|
|
WHERE organization_members.user_id = @user_id::uuid
|
|
|
|
|
AND organization_members.organization_id = chat_projects.organization_id
|
|
|
|
|
)
|
|
|
|
|
)
|
|
|
|
|
ORDER BY lower(chat_projects.name), chat_projects.id;
|
|
|
|
|
|
2026-10-19 07:32:26 -04:00
|
|
|
-- name: IsChatProjectUsableByUserID :one
|
2026-10-14 10:42:00 -04:00
|
|
|
SELECT EXISTS (
|
|
|
|
|
SELECT 1
|
|
|
|
|
FROM chat_projects
|
|
|
|
|
WHERE chat_projects.id = @project_id::uuid
|
|
|
|
|
AND (
|
|
|
|
|
chat_projects.owner_id = @user_id::uuid
|
|
|
|
|
OR (
|
|
|
|
|
EXISTS (
|
|
|
|
|
SELECT 1
|
|
|
|
|
FROM organization_members
|
|
|
|
|
WHERE organization_members.user_id = @user_id::uuid
|
|
|
|
|
AND organization_members.organization_id = chat_projects.organization_id
|
|
|
|
|
)
|
|
|
|
|
AND (
|
|
|
|
|
chat_projects.user_acl -> (@user_id::uuid)::text -> 'permissions' ?| ARRAY['read', '*']
|
|
|
|
|
OR chat_projects.group_acl -> chat_projects.organization_id::text -> 'permissions' ?| ARRAY['read', '*']
|
|
|
|
|
OR EXISTS (
|
|
|
|
|
SELECT 1
|
|
|
|
|
FROM group_members
|
|
|
|
|
WHERE group_members.user_id = @user_id::uuid
|
|
|
|
|
AND chat_projects.group_acl -> group_members.group_id::text -> 'permissions' ?| ARRAY['read', '*']
|
|
|
|
|
)
|
|
|
|
|
)
|
|
|
|
|
)
|
|
|
|
|
)
|
|
|
|
|
)::boolean;
|
|
|
|
|
|
|
|
|
|
-- name: UpdateChatProjectACLByID :exec
|
|
|
|
|
UPDATE chat_projects
|
|
|
|
|
SET
|
|
|
|
|
user_acl = @user_acl,
|
|
|
|
|
group_acl = @group_acl
|
|
|
|
|
WHERE id = @id::uuid;
|
|
|
|
|
|
2026-10-01 11:49:21 -04:00
|
|
|
-- name: UpdateChatProjectByID :one
|
|
|
|
|
UPDATE chat_projects
|
|
|
|
|
SET
|
|
|
|
|
name = @name::text,
|
|
|
|
|
description = @description::text,
|
|
|
|
|
icon = @icon::text,
|
2026-11-17 21:59:01 +07:00
|
|
|
-- The template settings change only when the request sets them, so a
|
|
|
|
|
-- concurrent update that omits them cannot restore a stale snapshot.
|
|
|
|
|
limit_templates = COALESCE(sqlc.narg('limit_templates')::boolean, limit_templates),
|
|
|
|
|
default_template_id = CASE WHEN @update_default_template::boolean THEN sqlc.narg('default_template_id')::uuid ELSE default_template_id END,
|
|
|
|
|
default_template_version_preset_id = CASE WHEN @update_default_template::boolean THEN sqlc.narg('default_template_version_preset_id')::uuid ELSE default_template_version_preset_id END,
|
|
|
|
|
default_parameters = CASE WHEN @update_default_template::boolean THEN COALESCE(sqlc.narg('default_parameters')::jsonb, '{}'::jsonb) ELSE default_parameters END,
|
2026-10-01 11:49:21 -04:00
|
|
|
updated_at = now()
|
|
|
|
|
WHERE id = @id::uuid
|
|
|
|
|
RETURNING *;
|
|
|
|
|
|
|
|
|
|
-- name: DeleteChatProjectByID :exec
|
|
|
|
|
DELETE FROM chat_projects
|
|
|
|
|
WHERE id = @id::uuid;
|
|
|
|
|
|
|
|
|
|
-- name: CountChatProjectsByOwnerID :one
|
|
|
|
|
SELECT COUNT(*)::bigint
|
|
|
|
|
FROM chat_projects
|
|
|
|
|
WHERE owner_id = @owner_id::uuid;
|
2026-11-17 21:59:01 +07:00
|
|
|
|
|
|
|
|
-- name: InsertChatProjectTemplates :many
|
|
|
|
|
-- Attaches templates to a project and returns the new attachments. Templates
|
|
|
|
|
-- that are already attached are skipped, so their rows keep their original
|
|
|
|
|
-- created_by. Templates outside the project's organization are skipped too,
|
|
|
|
|
-- so an attachment never crosses organizations.
|
|
|
|
|
INSERT INTO chat_project_templates (project_id, template_id, created_by)
|
|
|
|
|
SELECT chat_projects.id, templates.id, @created_by::uuid
|
|
|
|
|
FROM chat_projects
|
|
|
|
|
JOIN templates ON templates.organization_id = chat_projects.organization_id
|
|
|
|
|
WHERE chat_projects.id = @project_id::uuid
|
|
|
|
|
AND templates.id = ANY(@template_ids::uuid[])
|
|
|
|
|
ON CONFLICT (project_id, template_id) DO NOTHING
|
|
|
|
|
RETURNING *;
|
|
|
|
|
|
|
|
|
|
-- name: DeleteChatProjectTemplate :one
|
|
|
|
|
-- Detaches a template from a project. Detaching the default template also
|
|
|
|
|
-- clears the project's defaults, which only apply to an attached template.
|
|
|
|
|
-- Nothing changes when the template is not attached.
|
|
|
|
|
WITH cleared AS (
|
|
|
|
|
UPDATE chat_projects
|
|
|
|
|
SET
|
|
|
|
|
default_template_id = NULL,
|
|
|
|
|
default_template_version_preset_id = NULL,
|
|
|
|
|
default_parameters = '{}'::jsonb
|
|
|
|
|
WHERE id = @project_id::uuid
|
|
|
|
|
AND default_template_id = @template_id::uuid
|
|
|
|
|
AND EXISTS (
|
|
|
|
|
SELECT 1
|
|
|
|
|
FROM chat_project_templates
|
|
|
|
|
WHERE project_id = @project_id::uuid
|
|
|
|
|
AND template_id = @template_id::uuid
|
|
|
|
|
)
|
|
|
|
|
)
|
|
|
|
|
DELETE FROM chat_project_templates
|
|
|
|
|
WHERE project_id = @project_id::uuid
|
|
|
|
|
AND template_id = @template_id::uuid
|
|
|
|
|
RETURNING *;
|
|
|
|
|
|
|
|
|
|
-- name: GetChatProjectTemplatesByProjectIDs :many
|
|
|
|
|
-- Lists the templates attached to the given projects, oldest attachment
|
|
|
|
|
-- first. Deleted, deprecated, and agent-disabled templates are left out
|
|
|
|
|
-- because no chat can use them, whoever asks. The rows are not filtered by
|
|
|
|
|
-- the caller's access to each template, so callers must check that before
|
|
|
|
|
-- exposing a template.
|
|
|
|
|
SELECT
|
|
|
|
|
sqlc.embed(chat_project_templates),
|
|
|
|
|
sqlc.embed(chat_projects)
|
|
|
|
|
FROM chat_project_templates
|
|
|
|
|
JOIN chat_projects ON chat_projects.id = chat_project_templates.project_id
|
|
|
|
|
JOIN templates ON templates.id = chat_project_templates.template_id
|
|
|
|
|
WHERE chat_project_templates.project_id = ANY(@project_ids::uuid[])
|
|
|
|
|
AND NOT templates.deleted
|
|
|
|
|
AND templates.deprecated = ''
|
|
|
|
|
AND templates.agents_allowed
|
|
|
|
|
ORDER BY
|
|
|
|
|
chat_project_templates.project_id,
|
|
|
|
|
chat_project_templates.created_at,
|
|
|
|
|
chat_project_templates.template_id;
|