mirror of
https://github.com/tiennm99/goclaw.git
synced 2026-10-12 22:14:37 +00:00
viettranx
7985aa5793
Migration 55 added vault_documents_scope_consistency as NOT VALID, which skips existing rows but still re-checks every subsequent UPDATE. Migration 56's two backfill UPDATEs then aborted on legacy rows with mismatched scope/agent_id/team_id (pre-M46 when agent_id was NOT NULL, pre-M43 before team_id existed), leaving the schema dirty at version 56 on upgrade. Wrap the UPDATEs with DROP CONSTRAINT IF EXISTS at the start and re-ADD ... NOT VALID at the end so fresh installs proceed cleanly. Idempotent for DBs that already applied 56 successfully. Stuck users still need manual recovery (drop constraint, force 55, upgrade) since migrate cannot advance past dirty 56.
94 lines
5.6 KiB
SQL
94 lines
5.6 KiB
SQL
-- Add chat_id to vault_documents for cross-chat isolation within isolated teams.
|
|
-- NULL = team-wide doc (shared mode or legacy); non-NULL = scoped to specific chat.
|
|
ALTER TABLE vault_documents ADD COLUMN IF NOT EXISTS chat_id TEXT;
|
|
|
|
-- Composite index for team + chat filtering (primary query pattern for isolated teams).
|
|
CREATE INDEX IF NOT EXISTS idx_vault_docs_team_chat
|
|
ON vault_documents(team_id, chat_id)
|
|
WHERE team_id IS NOT NULL;
|
|
|
|
-- Drop scope-consistency CHECK before backfill UPDATEs. Constraint was added
|
|
-- NOT VALID in migration 55, which skips existing rows but still re-checks
|
|
-- on every UPDATE. Legacy data (pre-M46 when agent_id was NOT NULL, pre-M43
|
|
-- before team_id existed) often has rows that violate the invariant, causing
|
|
-- the backfill UPDATEs below to abort and leave migration 56 in a dirty
|
|
-- state (issue #1035). Drop now, re-add at end so fresh installs proceed
|
|
-- cleanly. Constraint stays NOT VALID — legacy bad rows still tolerated;
|
|
-- a future migration can clean + VALIDATE.
|
|
ALTER TABLE vault_documents DROP CONSTRAINT IF EXISTS vault_documents_scope_consistency;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- Backfill 1: team-scoped docs (scope='team', team_id set).
|
|
-- Two path layouts:
|
|
-- master tenant: teams/<team_uuid>/<chat>/...
|
|
-- non-master tenant: tenants/<slug>/teams/<team_uuid>/<chat>/...
|
|
-- Chat segments starting with '.' (e.g. '.goclaw') are config dirs, not real chats — skip.
|
|
-- -----------------------------------------------------------------------------
|
|
UPDATE vault_documents vd
|
|
SET chat_id = (regexp_match(vd.path, '^(?:tenants/[^/]+/)?teams/[^/]+/([^/]+)/'))[1]
|
|
FROM agent_teams t
|
|
WHERE vd.team_id = t.id
|
|
AND (t.settings->>'workspace_scope' IS NULL OR t.settings->>'workspace_scope' != 'shared')
|
|
AND vd.path ~ '^(?:tenants/[^/]+/)?teams/[^/]+/[^.][^/]*/';
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- Backfill 2: legacy docs from before team scope (team_id IS NULL) with chat
|
|
-- identifiers embedded in their path. Without chat_id these leak across chats
|
|
-- in isolated-team search because the `searchChatFilter` predicate cannot
|
|
-- distinguish them.
|
|
--
|
|
-- Subsystem vocabulary — any channel integration or delivery surface the
|
|
-- gateway writes under. Must match the channel names used as path segments
|
|
-- in internal/channels/* and workspace resolver (v2 + v3 layouts).
|
|
-- Channels: telegram | discord | zalo | feishu | lark | whatsapp | slack |
|
|
-- line | messenger | wechat | viber
|
|
-- Transports: ws (browser / WS direct) | api (HTTP) | delegate (subagent)
|
|
--
|
|
-- Path layouts handled (in order of COALESCE priority):
|
|
-- <subsystem>/group_<anything>_<chat>/... (Telegram-style group prefix)
|
|
-- <subsystem>/<chat>/... (bare legacy)
|
|
-- <agent_key>/<subsystem>/group_<anything>_<chat>/... (agent-owned group)
|
|
-- <agent_key>/<subsystem>/<chat>/... (agent-owned direct)
|
|
-- tenants/<slug>/<subsystem>/<chat>/... (non-master tenant)
|
|
-- <agent_key>/<botname>/group_<botname>_<chat>/... (legacy bot channel)
|
|
-- <agent_key>/<botname>/<chat>/... (bot + numeric/ws chat)
|
|
--
|
|
-- Chat IDs: numeric (Telegram/Discord/Zalo), oc_xxx (Feishu/Lark), sanitized
|
|
-- JID (WhatsApp: "123_c_us"), `system`, user handles, UUIDs. Sanitizer
|
|
-- (workspace_resolver.go) replaces everything outside [a-zA-Z0-9_-] with `_`,
|
|
-- so the captured character class matches what's actually on disk.
|
|
--
|
|
-- Only populate when chat_id IS NULL so interceptor-stamped values survive.
|
|
-- -----------------------------------------------------------------------------
|
|
UPDATE vault_documents
|
|
SET chat_id = COALESCE(
|
|
(regexp_match(path, '^(?:telegram|discord|zalo|feishu|lark|whatsapp|slack|line|messenger|wechat|viber)/group_[^/]+_(-?[0-9]+)/'))[1],
|
|
(regexp_match(path, '^(?:telegram|discord|zalo|feishu|lark|whatsapp|slack|line|messenger|wechat|viber|ws|delegate|api)/([a-zA-Z0-9_-]+)/'))[1],
|
|
(regexp_match(path, '^[^/]+/(?:telegram|discord|zalo|feishu|lark|whatsapp|slack|line|messenger|wechat|viber)/group_[^/]+_(-?[0-9]+)/'))[1],
|
|
(regexp_match(path, '^[^/]+/(?:telegram|discord|zalo|feishu|lark|whatsapp|slack|line|messenger|wechat|viber|ws|delegate|api)/([a-zA-Z0-9_-]+)/'))[1],
|
|
(regexp_match(path, '^tenants/[^/]+/(?:telegram|discord|zalo|feishu|lark|whatsapp|slack|line|messenger|wechat|viber|ws|delegate|api)/([a-zA-Z0-9_-]+)/'))[1],
|
|
(regexp_match(path, '^group_[^/]+_(-?[0-9]+)/'))[1],
|
|
(regexp_match(path, '^[^/]+/[^/]+/group_[^/]+_(-?[0-9]+)/'))[1],
|
|
(regexp_match(path, '^[^/]+/[^/]+/([a-zA-Z0-9_-]+)/'))[1]
|
|
)
|
|
WHERE chat_id IS NULL
|
|
AND team_id IS NULL
|
|
AND (
|
|
path ~ '^(?:[^/]+/)?(?:telegram|discord|zalo|feishu|lark|whatsapp|slack|line|messenger|wechat|viber|ws|delegate|api)/[^/]+/'
|
|
OR path ~ '^tenants/[^/]+/(?:telegram|discord|zalo|feishu|lark|whatsapp|slack|line|messenger|wechat|viber|ws|delegate|api)/[^/]+/'
|
|
OR path ~ '^group_[^/]+_-?[0-9]+/'
|
|
OR path ~ '^[^/]+/[^/]+/(group_[^/]+_-?[0-9]+|[0-9]+)/'
|
|
);
|
|
|
|
-- Re-add the scope-consistency constraint dropped above. NOT VALID matches
|
|
-- migration 55's original semantics — existing rows pass without validation,
|
|
-- new INSERT/UPDATE are checked. Run `VALIDATE CONSTRAINT` after audit cleanup.
|
|
ALTER TABLE vault_documents
|
|
ADD CONSTRAINT vault_documents_scope_consistency
|
|
CHECK (
|
|
(scope = 'personal' AND agent_id IS NOT NULL AND team_id IS NULL) OR
|
|
(scope = 'team' AND team_id IS NOT NULL AND agent_id IS NULL) OR
|
|
(scope = 'shared' AND agent_id IS NULL AND team_id IS NULL) OR
|
|
scope = 'custom'
|
|
) NOT VALID;
|