Files
Jaayden Halko 1f1aa6ae3c feat: add chat project template schema and queries (#32309)
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
2026-11-17 21:59:01 +07:00

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;