Adds storage for project-level instructions in Coder Agents chat projects and injects them into every chat in the project. This is the first of four stacked PRs that replace #30349. A new `chat_project_instructions` table (migration 000613) holds at most one row per project, scoped to the project's organization. It records `updated_by` and cascades on project delete. The dbauthz wrappers authorize against the parent project: read access to get the instructions, update access to upsert or delete them. No new RBAC resource is added; project RBAC comes in a later PR. chatd loads the instructions on every turn, so edits reach existing chats on their next turn. They are added to the system prompt as a `<project-instructions>` block after the memory guidance and before personal instructions, so a user's personal instructions still win a conflict. Subagent chats carry no `project_id`, so they use their root chat's project. A load error other than no rows is logged, and the turn continues without instructions. **What to scrutinize:** the prompt ordering in `buildSystemPrompt`, and the choice to inject per turn rather than snapshot at chat creation. Per-turn injection costs one prompt-cache miss after each edit; the org instructions in #30272 use a creation-time snapshot instead. **Generated** (`make gen` from the migration and `queries/chatprojectinstructions.sql`): `dump.sql`, `models.go`, `querier.go`, `queries.sql.go`, `dbmetrics/querymetrics.go`, `dbmock/dbmock.go`, and the `*_constraint.go` files. `coderd/x/chatd/ARCHITECTURE.md` gets two TODOs for the PR author to replace with the real description, per AGENTS.md. **Validation:** `make gen` leaves no diff. `go test ./coderd/database/dbauthz -run TestMethodTestSuite/TestChats`, `go test ./coderd/x/chatd -run 'SystemPrompt|ProjectInstructions|Memory'`, and `go test ./coderd/database/migrations/...` pass. --- Stack (merge top to bottom). #30377 through #29583 are f0ssel's split of the original #29583, and this stack sits on #29583: - #30377 data layer and project dialog (f0ssel) - #30378 new-chat form locked to a project (f0ssel) - #30379 project page route (f0ssel) - #29583 sidebar folders (f0ssel), the base of this stack - #30291 composer frame - #30292 project chat list caches - #30293 project page header - #30294 project chats list - #30367 instructions storage and prompt injection 👈 - #30368 instructions API - #30369 instructions editor dialog - #30370 instructions Details panel --------- Co-authored-by: Garrett Delfosse <delfossegarrett@gmail.com> Co-authored-by: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
39 lines
1.3 KiB
SQL
39 lines
1.3 KiB
SQL
-- name: GetChatProjectInstructionsByProjectID :one
|
|
SELECT
|
|
sqlc.embed(chat_project_instructions),
|
|
users.username AS updated_by_username,
|
|
users.name AS updated_by_name,
|
|
users.avatar_url AS updated_by_avatar_url
|
|
FROM chat_project_instructions
|
|
-- Users are soft deleted, so ON DELETE SET NULL never clears updated_by.
|
|
-- Excluding deleted users here reports their edits as anonymous.
|
|
LEFT JOIN users ON users.id = chat_project_instructions.updated_by
|
|
AND users.deleted = false
|
|
WHERE chat_project_instructions.project_id = @project_id::uuid;
|
|
|
|
-- name: UpsertChatProjectInstructions :one
|
|
-- The organization comes from the project row, so the stored scope always
|
|
-- matches the project's organization.
|
|
INSERT INTO chat_project_instructions (
|
|
project_id,
|
|
organization_id,
|
|
instructions,
|
|
updated_by
|
|
)
|
|
SELECT
|
|
chat_projects.id,
|
|
chat_projects.organization_id,
|
|
@instructions::text,
|
|
@updated_by::uuid
|
|
FROM chat_projects
|
|
WHERE chat_projects.id = @project_id::uuid
|
|
ON CONFLICT (project_id) DO UPDATE SET
|
|
instructions = EXCLUDED.instructions,
|
|
updated_by = EXCLUDED.updated_by,
|
|
updated_at = now()
|
|
RETURNING *;
|
|
|
|
-- name: DeleteChatProjectInstructionsByProjectID :exec
|
|
DELETE FROM chat_project_instructions
|
|
WHERE project_id = @project_id::uuid;
|