mirror of
https://github.com/multica-ai/multica.git
synced 2026-08-03 19:20:07 +02:00
afc7ac4bd0
Squashed history of PR #4892 (pr-4784-fix). Makes spaces the primary navigation and working surface, on the "associations bind at creation time only" model. Model - Issue <-> space is the only enforced ownership (per-space numbering). Parent/child and project<->space associations only seed defaults at creation; cross-space/child and project-association validations are removed. - Moving an issue renumbers it and records the old identifier in issue_identifier_alias; API/CLI lookups and GitHub branch/PR auto-linking fall back to the alias, so old identifiers resolve forever. - Membership drives only the sidebar and personal defaults — never access. Anyone can configure any space's member set wholesale (PUT /api/spaces/{id}/members); saving an empty set archives the space behind a confirm. - Per-user space order (workspace_space_member.sort_order, fractional): drag-sorted sidebar, "my first space" is the personal issue-creation default; the workspace default space backs headless creation (agents/CLI/Slack) and system placement. Surfaces - Sidebar: joined-spaces section (drag reorder, row -> space page, per-group persisted collapse), Workspace group with a More menu, Settings demoted to a footer icon. - /space/:key/{issues,projects,autopilots,settings} — space surfaces reuse shared page components; a routed /space/new create page (replacing the earlier create-space modal), reserved key "NEW" so it can never collide with a real space's /space/:key detail page. - Issue detail moves to /issue/:id (identifier-first, Linear-style; old /issues/:id redirects); create dialogs lead with a required space pill. - Agent runtime brief now carries Space context (id/key/name) through the daemon claim -> TaskContextForEnv -> prompt pipeline, with a "## Space Context" section and --space on issue create/update in both brief renderers, matching Project's existing treatment. - zh-Hans: Space translated to 空间 across locales and conventions.zh.mdx. Fixes along the way - Cache membership judgment gains the space dimension + space_changed WS flag. - Silent skip on default-space lookup during invite acceptance is now a hard failure (no space-less members). - Backfilled space_id into ~28 raw-SQL Go test fixtures across internal/handler and cmd/server that predated migration 132's NOT NULL cutover. - Fixed a resolve-loop bug in the IssueDetail identifier wrapper (mount/ unmount cycle on resolution failure) and gave it its own loading skeleton instead of a blank screen while resolving. - reserved-slugs generator's stale hardcoded doc-comment example synced back to /create-space. Verification - go build ./..., go vet ./..., go test ./... all clean. - pnpm typecheck (core/views/web/desktop) clean. - packages/views: 162 files / 1656 tests passing. - pnpm generate:reserved-slugs produces no diff. Follow-ups tracked in docs/follow-ups/space-rollout.md. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
375 lines
16 KiB
SQL
375 lines
16 KiB
SQL
-- name: ListIssues :many
|
|
-- involves_user_id widens the assignee filter to surface issues where the user
|
|
-- is *indirectly* the assignee — via an owned agent or a squad they belong to /
|
|
-- lead / have an agent inside. The semantics intentionally exclude direct
|
|
-- member assignment (`assignee_type='member' AND assignee_id=involves_user_id`)
|
|
-- because that is already the meaning of the `assignee_id` filter (tab 1
|
|
-- "Assigned to me"), and the two filters must produce disjoint result sets.
|
|
SELECT i.id, i.workspace_id, i.space_id, i.title, i.description, i.status, i.priority,
|
|
i.assignee_type, i.assignee_id, i.creator_type, i.creator_id,
|
|
i.parent_issue_id, i.position, i.start_date, i.due_date, i.created_at, i.updated_at, i.number, i.project_id, i.metadata, i.stage,
|
|
COALESCE(wt.key, '')::text AS space_key, COALESCE(wt.name, '')::text AS space_name
|
|
FROM issue i
|
|
LEFT JOIN workspace_space wt ON wt.id = i.space_id AND wt.workspace_id = i.workspace_id
|
|
WHERE i.workspace_id = $1
|
|
AND (sqlc.narg('space_id')::uuid IS NULL OR i.space_id = sqlc.narg('space_id'))
|
|
AND (sqlc.narg('space_ids')::uuid[] IS NULL OR i.space_id = ANY(sqlc.narg('space_ids')::uuid[]))
|
|
AND (sqlc.narg('status')::text IS NULL OR i.status = sqlc.narg('status'))
|
|
AND (sqlc.narg('priority')::text IS NULL OR i.priority = sqlc.narg('priority'))
|
|
AND (sqlc.narg('assignee_id')::uuid IS NULL OR i.assignee_id = sqlc.narg('assignee_id'))
|
|
AND (sqlc.narg('assignee_ids')::uuid[] IS NULL OR i.assignee_id = ANY(sqlc.narg('assignee_ids')::uuid[]))
|
|
AND (sqlc.narg('creator_id')::uuid IS NULL OR i.creator_id = sqlc.narg('creator_id'))
|
|
AND (sqlc.narg('project_id')::uuid IS NULL OR i.project_id = sqlc.narg('project_id'))
|
|
AND (sqlc.narg('scheduled')::bool IS NULL OR (i.start_date IS NOT NULL OR i.due_date IS NOT NULL))
|
|
AND (sqlc.narg('metadata_filter')::jsonb IS NULL OR i.metadata @> sqlc.narg('metadata_filter')::jsonb)
|
|
AND (
|
|
sqlc.narg('involves_user_id')::uuid IS NULL
|
|
-- (1) assignee is an agent owned by the user
|
|
OR (i.assignee_type = 'agent' AND i.assignee_id IN (
|
|
SELECT a.id FROM agent a
|
|
WHERE a.workspace_id = $1
|
|
AND a.owner_id = sqlc.narg('involves_user_id')::uuid
|
|
))
|
|
-- (2)(3)(4) assignee is a squad related to the user — three relations
|
|
OR (i.assignee_type = 'squad' AND i.assignee_id IN (
|
|
-- (2) the user is a human member of the squad
|
|
SELECT sm.squad_id
|
|
FROM squad_member sm
|
|
JOIN squad s ON s.id = sm.squad_id
|
|
WHERE s.workspace_id = $1
|
|
AND sm.member_type = 'member'
|
|
AND sm.member_id = sqlc.narg('involves_user_id')::uuid
|
|
UNION
|
|
-- (3) the squad's canonical leader is an agent owned by the user.
|
|
-- We read squad.leader_id directly rather than relying on a
|
|
-- squad_member row, because the leader copy in squad_member is
|
|
-- best-effort (see squad.go AddSquadMember error handling).
|
|
SELECT s.id
|
|
FROM squad s
|
|
JOIN agent a ON a.id = s.leader_id
|
|
WHERE s.workspace_id = $1
|
|
AND a.workspace_id = $1
|
|
AND a.owner_id = sqlc.narg('involves_user_id')::uuid
|
|
UNION
|
|
-- (4) the squad has an agent member owned by the user
|
|
SELECT sm.squad_id
|
|
FROM squad_member sm
|
|
JOIN squad s ON s.id = sm.squad_id
|
|
JOIN agent a ON a.id = sm.member_id
|
|
WHERE s.workspace_id = $1
|
|
AND sm.member_type = 'agent'
|
|
AND a.workspace_id = $1
|
|
AND a.owner_id = sqlc.narg('involves_user_id')::uuid
|
|
))
|
|
)
|
|
ORDER BY i.position ASC, i.created_at DESC
|
|
LIMIT $2 OFFSET $3;
|
|
|
|
-- name: GetIssue :one
|
|
SELECT * FROM issue
|
|
WHERE id = $1;
|
|
|
|
-- name: GetIssueInWorkspace :one
|
|
SELECT * FROM issue
|
|
WHERE id = $1 AND workspace_id = $2;
|
|
|
|
-- name: CreateIssue :one
|
|
INSERT INTO issue (
|
|
workspace_id, space_id, title, description, status, priority,
|
|
assignee_type, assignee_id, creator_type, creator_id,
|
|
parent_issue_id, position, start_date, due_date, number, project_id,
|
|
stage
|
|
) VALUES (
|
|
$1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16,
|
|
sqlc.narg('stage')
|
|
) RETURNING *;
|
|
|
|
-- name: GetIssueBySpaceKeyAndNumber :one
|
|
-- issue.space_id is NOT NULL as of migration 132, which ships in the same
|
|
-- release as this query, so no null-space fallback is needed.
|
|
SELECT i.* FROM issue i
|
|
JOIN workspace_space issue_space ON issue_space.id = i.space_id AND issue_space.workspace_id = i.workspace_id
|
|
WHERE i.workspace_id = $1
|
|
AND lower(issue_space.key) = lower($2)
|
|
AND i.number = $3;
|
|
|
|
-- name: UpdateIssue :one
|
|
UPDATE issue SET
|
|
title = COALESCE(sqlc.narg('title'), title),
|
|
description = COALESCE(sqlc.narg('description'), description),
|
|
status = COALESCE(sqlc.narg('status'), status),
|
|
priority = COALESCE(sqlc.narg('priority'), priority),
|
|
assignee_type = sqlc.narg('assignee_type'),
|
|
assignee_id = sqlc.narg('assignee_id'),
|
|
position = COALESCE(sqlc.narg('position'), position),
|
|
start_date = sqlc.narg('start_date'),
|
|
due_date = sqlc.narg('due_date'),
|
|
parent_issue_id = sqlc.narg('parent_issue_id'),
|
|
project_id = sqlc.narg('project_id'),
|
|
stage = sqlc.narg('stage'),
|
|
updated_at = now()
|
|
WHERE id = $1
|
|
RETURNING *;
|
|
|
|
-- name: UpdateIssueStatus :one
|
|
-- Workspace_id in the WHERE clause is a SQL-layer tenant guard; see DeleteIssue.
|
|
UPDATE issue SET
|
|
status = $2,
|
|
updated_at = now()
|
|
WHERE id = $1 AND workspace_id = $3
|
|
RETURNING *;
|
|
|
|
-- name: CreateIssueWithOrigin :one
|
|
INSERT INTO issue (
|
|
workspace_id, space_id, title, description, status, priority,
|
|
assignee_type, assignee_id, creator_type, creator_id,
|
|
parent_issue_id, position, start_date, due_date, number, project_id,
|
|
origin_type, origin_id, stage
|
|
) VALUES (
|
|
$1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16,
|
|
sqlc.narg('origin_type'), sqlc.narg('origin_id'), sqlc.narg('stage')
|
|
) RETURNING *;
|
|
|
|
-- name: LockIssueDuplicateKey :exec
|
|
SELECT pg_advisory_xact_lock(hashtextextended($1::text, 0));
|
|
|
|
-- name: FindActiveDuplicateIssue :one
|
|
SELECT * FROM issue
|
|
WHERE workspace_id = $1
|
|
AND status NOT IN ('done', 'cancelled')
|
|
AND space_id = sqlc.arg('space_id')::uuid
|
|
AND project_id IS NOT DISTINCT FROM sqlc.arg('project_id')::uuid
|
|
AND parent_issue_id IS NOT DISTINCT FROM sqlc.arg('parent_issue_id')::uuid
|
|
AND lower(btrim(regexp_replace(title, '[[:space:]]+', ' ', 'g'))) = sqlc.arg('normalized_title')
|
|
ORDER BY created_at ASC
|
|
LIMIT 1;
|
|
|
|
-- name: FindRecentAutopilotDuplicateIssue :one
|
|
SELECT i.* FROM issue i
|
|
WHERE i.workspace_id = $1
|
|
AND i.status NOT IN ('done', 'cancelled')
|
|
AND i.origin_type = 'autopilot'
|
|
AND i.origin_id = $2
|
|
AND i.project_id IS NOT DISTINCT FROM sqlc.arg('project_id')::uuid
|
|
AND lower(btrim(regexp_replace(i.title, '[[:space:]]+', ' ', 'g'))) = sqlc.arg('normalized_title')
|
|
AND i.created_at >= sqlc.arg('created_after')::timestamptz
|
|
AND EXISTS (
|
|
SELECT 1
|
|
FROM autopilot_run r
|
|
WHERE r.issue_id = i.id
|
|
AND r.autopilot_id = i.origin_id
|
|
AND r.status IN ('issue_created', 'running', 'completed')
|
|
)
|
|
ORDER BY i.created_at ASC
|
|
LIMIT 1;
|
|
|
|
-- name: DeleteIssue :exec
|
|
-- Defense-in-depth: the workspace_id predicate makes the tenant invariant a
|
|
-- SQL-layer guarantee rather than a handler-layer one. Handler loaders
|
|
-- (loadIssueForUser / GetIssueInWorkspace) already enforce membership today,
|
|
-- but a future loader bypass or a new caller skipping the loader would be
|
|
-- silently catastrophic without this guard. See incident #1661.
|
|
DELETE FROM issue WHERE id = $1 AND workspace_id = $2;
|
|
|
|
-- name: ListOpenIssues :many
|
|
-- See ListIssues for the semantics of involves_user_id (mirrors the 4-branch
|
|
-- filter; member-direct assignment is intentionally excluded).
|
|
SELECT i.id, i.workspace_id, i.space_id, i.title, i.description, i.status, i.priority,
|
|
i.assignee_type, i.assignee_id, i.creator_type, i.creator_id,
|
|
i.parent_issue_id, i.position, i.start_date, i.due_date, i.created_at, i.updated_at, i.number, i.project_id, i.metadata, i.stage,
|
|
COALESCE(wt.key, '')::text AS space_key, COALESCE(wt.name, '')::text AS space_name
|
|
FROM issue i
|
|
LEFT JOIN workspace_space wt ON wt.id = i.space_id AND wt.workspace_id = i.workspace_id
|
|
WHERE i.workspace_id = $1
|
|
AND (sqlc.narg('space_id')::uuid IS NULL OR i.space_id = sqlc.narg('space_id'))
|
|
AND (sqlc.narg('space_ids')::uuid[] IS NULL OR i.space_id = ANY(sqlc.narg('space_ids')::uuid[]))
|
|
AND i.status NOT IN ('done', 'cancelled')
|
|
AND (sqlc.narg('priority')::text IS NULL OR i.priority = sqlc.narg('priority'))
|
|
AND (sqlc.narg('assignee_id')::uuid IS NULL OR i.assignee_id = sqlc.narg('assignee_id'))
|
|
AND (sqlc.narg('assignee_ids')::uuid[] IS NULL OR i.assignee_id = ANY(sqlc.narg('assignee_ids')::uuid[]))
|
|
AND (sqlc.narg('creator_id')::uuid IS NULL OR i.creator_id = sqlc.narg('creator_id'))
|
|
AND (sqlc.narg('project_id')::uuid IS NULL OR i.project_id = sqlc.narg('project_id'))
|
|
AND (sqlc.narg('metadata_filter')::jsonb IS NULL OR i.metadata @> sqlc.narg('metadata_filter')::jsonb)
|
|
AND (
|
|
sqlc.narg('involves_user_id')::uuid IS NULL
|
|
OR (i.assignee_type = 'agent' AND i.assignee_id IN (
|
|
SELECT a.id FROM agent a
|
|
WHERE a.workspace_id = $1
|
|
AND a.owner_id = sqlc.narg('involves_user_id')::uuid
|
|
))
|
|
OR (i.assignee_type = 'squad' AND i.assignee_id IN (
|
|
SELECT sm.squad_id
|
|
FROM squad_member sm
|
|
JOIN squad s ON s.id = sm.squad_id
|
|
WHERE s.workspace_id = $1
|
|
AND sm.member_type = 'member'
|
|
AND sm.member_id = sqlc.narg('involves_user_id')::uuid
|
|
UNION
|
|
SELECT s.id
|
|
FROM squad s
|
|
JOIN agent a ON a.id = s.leader_id
|
|
WHERE s.workspace_id = $1
|
|
AND a.workspace_id = $1
|
|
AND a.owner_id = sqlc.narg('involves_user_id')::uuid
|
|
UNION
|
|
SELECT sm.squad_id
|
|
FROM squad_member sm
|
|
JOIN squad s ON s.id = sm.squad_id
|
|
JOIN agent a ON a.id = sm.member_id
|
|
WHERE s.workspace_id = $1
|
|
AND sm.member_type = 'agent'
|
|
AND a.workspace_id = $1
|
|
AND a.owner_id = sqlc.narg('involves_user_id')::uuid
|
|
))
|
|
)
|
|
ORDER BY i.position ASC, i.created_at DESC;
|
|
|
|
-- name: CountIssues :one
|
|
-- See ListIssues for the semantics of involves_user_id.
|
|
SELECT count(*) FROM issue i
|
|
WHERE i.workspace_id = $1
|
|
AND (sqlc.narg('space_id')::uuid IS NULL OR i.space_id = sqlc.narg('space_id'))
|
|
AND (sqlc.narg('space_ids')::uuid[] IS NULL OR i.space_id = ANY(sqlc.narg('space_ids')::uuid[]))
|
|
AND (sqlc.narg('status')::text IS NULL OR i.status = sqlc.narg('status'))
|
|
AND (sqlc.narg('priority')::text IS NULL OR i.priority = sqlc.narg('priority'))
|
|
AND (sqlc.narg('assignee_id')::uuid IS NULL OR i.assignee_id = sqlc.narg('assignee_id'))
|
|
AND (sqlc.narg('assignee_ids')::uuid[] IS NULL OR i.assignee_id = ANY(sqlc.narg('assignee_ids')::uuid[]))
|
|
AND (sqlc.narg('creator_id')::uuid IS NULL OR i.creator_id = sqlc.narg('creator_id'))
|
|
AND (sqlc.narg('project_id')::uuid IS NULL OR i.project_id = sqlc.narg('project_id'))
|
|
AND (sqlc.narg('scheduled')::bool IS NULL OR (i.start_date IS NOT NULL OR i.due_date IS NOT NULL))
|
|
AND (sqlc.narg('metadata_filter')::jsonb IS NULL OR i.metadata @> sqlc.narg('metadata_filter')::jsonb)
|
|
AND (
|
|
sqlc.narg('involves_user_id')::uuid IS NULL
|
|
OR (i.assignee_type = 'agent' AND i.assignee_id IN (
|
|
SELECT a.id FROM agent a
|
|
WHERE a.workspace_id = $1
|
|
AND a.owner_id = sqlc.narg('involves_user_id')::uuid
|
|
))
|
|
OR (i.assignee_type = 'squad' AND i.assignee_id IN (
|
|
SELECT sm.squad_id
|
|
FROM squad_member sm
|
|
JOIN squad s ON s.id = sm.squad_id
|
|
WHERE s.workspace_id = $1
|
|
AND sm.member_type = 'member'
|
|
AND sm.member_id = sqlc.narg('involves_user_id')::uuid
|
|
UNION
|
|
SELECT s.id
|
|
FROM squad s
|
|
JOIN agent a ON a.id = s.leader_id
|
|
WHERE s.workspace_id = $1
|
|
AND a.workspace_id = $1
|
|
AND a.owner_id = sqlc.narg('involves_user_id')::uuid
|
|
UNION
|
|
SELECT sm.squad_id
|
|
FROM squad_member sm
|
|
JOIN squad s ON s.id = sm.squad_id
|
|
JOIN agent a ON a.id = sm.member_id
|
|
WHERE s.workspace_id = $1
|
|
AND sm.member_type = 'agent'
|
|
AND a.workspace_id = $1
|
|
AND a.owner_id = sqlc.narg('involves_user_id')::uuid
|
|
))
|
|
);
|
|
|
|
-- name: ListChildIssues :many
|
|
-- Order by number ASC so sub-issues display in stable creation order
|
|
-- (oldest first), matching how a parent's plan reads top-to-bottom. The
|
|
-- position column is computed per-(workspace, status) by NextTopPosition,
|
|
-- not relative to siblings, so ordering by it interleaves children
|
|
-- unpredictably across batches and statuses; number is a per-workspace
|
|
-- monotonic counter and is sibling-stable.
|
|
SELECT * FROM issue
|
|
WHERE parent_issue_id = $1
|
|
ORDER BY number ASC;
|
|
|
|
-- name: ListChildrenByParents :many
|
|
-- Batched variant of ListChildIssues: returns all children for the given
|
|
-- parent set in one round trip. Used by Swimlane to avoid an N+1 fan-out
|
|
-- (one request per visible parent lane). Result is grouped client-side by
|
|
-- parent_issue_id; the workspace filter is also enforced so callers can't
|
|
-- enumerate children of parents in workspaces they don't belong to.
|
|
-- Within each parent, order by number ASC for the same sibling-stable
|
|
-- creation order as ListChildIssues.
|
|
SELECT * FROM issue
|
|
WHERE workspace_id = sqlc.arg('workspace_id')
|
|
AND parent_issue_id = ANY(sqlc.arg('parent_ids')::uuid[])
|
|
ORDER BY parent_issue_id, number ASC;
|
|
|
|
-- name: GetIssueByOrigin :one
|
|
-- Finds the issue stamped with a specific (origin_type, origin_id) pair.
|
|
-- Used by quick-create completion to deterministically locate the issue
|
|
-- produced by a given agent_task_queue.id — robust against concurrent
|
|
-- issue creates by the same agent (assignment task + quick-create both
|
|
-- running with max_concurrent_tasks > 1).
|
|
SELECT * FROM issue
|
|
WHERE workspace_id = $1
|
|
AND origin_type = $2
|
|
AND origin_id = $3
|
|
LIMIT 1;
|
|
|
|
-- name: CountCreatedIssueAssignees :many
|
|
-- Count assignees on issues created by a specific user.
|
|
SELECT
|
|
assignee_type,
|
|
assignee_id,
|
|
COUNT(*)::bigint as frequency
|
|
FROM issue
|
|
WHERE workspace_id = $1
|
|
AND creator_id = $2
|
|
AND creator_type = 'member'
|
|
AND assignee_type IS NOT NULL
|
|
AND assignee_id IS NOT NULL
|
|
GROUP BY assignee_type, assignee_id;
|
|
|
|
-- name: ChildIssueProgress :many
|
|
SELECT parent_issue_id,
|
|
COUNT(*)::bigint AS total,
|
|
COUNT(*) FILTER (WHERE status IN ('done', 'cancelled'))::bigint AS done
|
|
FROM issue
|
|
WHERE workspace_id = $1
|
|
AND parent_issue_id IS NOT NULL
|
|
GROUP BY parent_issue_id;
|
|
|
|
-- SearchIssues: moved to handler (dynamic SQL for multi-word search support).
|
|
|
|
-- name: SetIssueMetadataKey :one
|
|
-- Atomically sets a single key in the issue's metadata JSONB. The
|
|
-- workspace_id filter is the authorization gate — handler resolves the
|
|
-- issue first so this is also the tenant check.
|
|
UPDATE issue SET
|
|
metadata = jsonb_set(metadata, ARRAY[sqlc.arg('key')::text], sqlc.arg('value')::jsonb),
|
|
updated_at = now()
|
|
WHERE id = sqlc.arg('id') AND workspace_id = sqlc.arg('workspace_id')
|
|
RETURNING *;
|
|
|
|
-- name: DeleteIssueMetadataKey :one
|
|
-- Atomically removes a single key from the issue's metadata JSONB.
|
|
-- Deleting a missing key is a no-op (still returns the row).
|
|
UPDATE issue SET
|
|
metadata = metadata - sqlc.arg('key')::text,
|
|
updated_at = now()
|
|
WHERE id = sqlc.arg('id') AND workspace_id = sqlc.arg('workspace_id')
|
|
RETURNING *;
|
|
|
|
-- name: MarkIssueFirstExecuted :one
|
|
-- Flips first_executed_at from NULL to now() atomically. Returns the row if
|
|
-- this was the first time the issue was executed; no rows otherwise. The
|
|
-- analytics issue_executed event fires exactly when this returns a row —
|
|
-- retries and re-assignments hit the WHERE clause and no-op.
|
|
UPDATE issue
|
|
SET first_executed_at = now()
|
|
WHERE id = $1 AND first_executed_at IS NULL
|
|
RETURNING id, workspace_id, creator_type, creator_id, first_executed_at;
|
|
|
|
-- name: MoveIssueToSpace :one
|
|
-- Moving an issue between spaces renumbers it: numbers are allocated
|
|
-- per-space (uq_issue_space_number), so the caller passes a fresh number from
|
|
-- IncrementSpaceIssueCounter and a fresh position in the destination column.
|
|
UPDATE issue
|
|
SET space_id = $2,
|
|
number = $3,
|
|
position = $4,
|
|
updated_at = now()
|
|
WHERE id = $1
|
|
RETURNING *;
|