Adds the database layer for attaching workspace templates to chat projects: the schema, the queries, their authorization, and the audit type. Nothing calls the new queries yet, and the existing create and update handlers pass the new parameters unset. The API, the limit's enforcement, and the agent tools follow in the PRs above. - Attachments live in a `chat_project_templates` table (`project_id`, `template_id`, `created_by`, `created_at`), deleted with the project or the template. `chat_projects` gains `limit_templates` (default `false`), `default_template_id`, `default_template_version_preset_id`, and `default_parameters jsonb`. A project stores a default preset or default parameter values, not both. - `InsertChatProjectTemplates` needs project `update` and `use` on every template. It skips templates that are already attached, and templates outside the project's organization, so an attachment never crosses organizations. `DeleteChatProjectTemplate` needs project `update` and template `read`, and clears the project's default when it detaches the default template. Both checks are in dbauthz. - `GetChatProjectTemplatesByProjectIDs` is authorized through the project, and leaves out deleted, deprecated, and agent-disabled templates. It does not check the caller's access to each template, so callers do that before they expose one. - `UpdateChatProjectByID` changes the template settings only when the request sets them, so an update that omits them cannot write back a stale snapshot. - `AuditableChatProjectTemplates` is the audit form of a project's attached templates, logged as a write to the project. The new `chat_projects` columns are tracked in the project's own audit diff, with the default parameter values masked because they may hold credentials. **NOTE**: the migration adds one table and four columns and changes no existing rows. Existing projects keep unlimited template access. Stack (merge top to bottom): - #32309 👈 - #30754 - #32310 - #30755 - #30756 - #30898
177 lines
6.6 KiB
SQL
177 lines
6.6 KiB
SQL
-- name: InsertChatProject :one
|
|
INSERT INTO chat_projects (
|
|
id, organization_id, owner_id, name, description, icon,
|
|
limit_templates, default_template_id, default_template_version_preset_id, default_parameters
|
|
)
|
|
VALUES (
|
|
COALESCE(sqlc.narg('id')::uuid, gen_random_uuid()),
|
|
@organization_id::uuid,
|
|
@owner_id::uuid,
|
|
@name::text,
|
|
@description::text,
|
|
@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)
|
|
)
|
|
RETURNING *;
|
|
|
|
-- name: GetChatProjectByID :one
|
|
SELECT *
|
|
FROM chat_projects
|
|
WHERE id = @id::uuid;
|
|
|
|
-- 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;
|
|
|
|
-- name: IsChatProjectUsableByUserID :one
|
|
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;
|
|
|
|
-- name: UpdateChatProjectByID :one
|
|
UPDATE chat_projects
|
|
SET
|
|
name = @name::text,
|
|
description = @description::text,
|
|
icon = @icon::text,
|
|
-- 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,
|
|
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;
|
|
|
|
-- 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;
|