/**
* Migration 005: M5 Multi-Tool Orchestration Schema
*
* Adds support for:
* - Per-tool permission levels (replacing global currentLevel)
* - Shared context for cross-tool resource coordination
* - Tool name tracking in intent and permission history
*
* Backward compatible with M4: Falls back to currentLevel if toolPermissions not set
*/
-- Step 1: Add new columns for M5
ALTER TABLE conversations
ADD COLUMN IF NOT EXISTS tool_permissions JSONB DEFAULT '{}'::jsonb,
ADD COLUMN IF NOT EXISTS shared_context JSONB DEFAULT '{"resources": []}'::jsonb;
-- Step 2: Create index for tool_permissions queries
CREATE INDEX IF NOT EXISTS idx_conversations_tool_permissions
ON conversations USING gin (tool_permissions);
-- Step 3: Create index for shared_context queries
CREATE INDEX IF NOT EXISTS idx_conversations_shared_context
ON conversations USING gin (shared_context);
-- Step 4: Add toolName to intentHistory items (M5 extension)
-- Note: intentHistory is stored as JSONB array, this is handled in application code
-- Each item in intentHistory will now include:
-- { action, toolName, alignment, timestamp }
-- Step 5: Add toolName to permissionHistory items (M5 extension)
-- Note: permissionHistory is stored as JSONB array, this is handled in application code
-- Each item in permissionHistory will now include:
-- { toolName, level, scope, grantedAt }
-- Step 6: Migrate existing M4 data to M5 format
-- For conversations with currentLevel but no toolPermissions, create a default entry
UPDATE conversations
SET tool_permissions = jsonb_build_object(
'example-tool', jsonb_build_object(
'level', current_level,
'upgradedAt', EXTRACT(EPOCH FROM NOW()) * 1000
)
)
WHERE current_level IS NOT NULL
AND (tool_permissions IS NULL OR tool_permissions = '{}'::jsonb);
-- Step 7: Add comment explaining backward compatibility
COMMENT ON COLUMN conversations.current_level IS
'M4 global permission level (deprecated in M5, use tool_permissions).
Kept for backward compatibility - application falls back to this if tool_permissions empty.';
COMMENT ON COLUMN conversations.tool_permissions IS
'M5 per-tool permission levels. Format: {"tool-name": {"level": 1-3, "upgradedAt": timestamp}}.
Level 1=read, 2=write, 3=execute. Each tool has independent permission level.';
COMMENT ON COLUMN conversations.shared_context IS
'M5 shared context for cross-tool coordination. Format: {"resources": [{"name": "...", "data": {...}, "createdBy": "tool-name", "createdAt": timestamp, "updatedAt": timestamp}]}.
Resources created by one tool are accessible to all tools in the conversation.';
-- Step 8: Create function to get tool permission level (with M4 fallback)
CREATE OR REPLACE FUNCTION get_tool_permission_level(
conv_tool_permissions JSONB,
conv_current_level INTEGER,
tool_name TEXT
) RETURNS INTEGER AS $$
BEGIN
-- M5: Check tool-specific permission first
IF conv_tool_permissions ? tool_name THEN
RETURN (conv_tool_permissions->tool_name->>'level')::INTEGER;
END IF;
-- M4 fallback: Use global currentLevel
IF conv_current_level IS NOT NULL THEN
RETURN conv_current_level;
END IF;
-- Default: level 1 (read-only)
RETURN 1;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- Step 9: Create function to list all resources in shared context
CREATE OR REPLACE FUNCTION list_shared_resources(
conv_shared_context JSONB
) RETURNS TABLE(
resource_name TEXT,
created_by TEXT,
created_at BIGINT,
updated_at BIGINT
) AS $$
BEGIN
RETURN QUERY
SELECT
(resource->>'name')::TEXT,
(resource->>'createdBy')::TEXT,
(resource->>'createdAt')::BIGINT,
(resource->>'updatedAt')::BIGINT
FROM jsonb_array_elements(conv_shared_context->'resources') AS resource;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- Step 10: Create view for M5 conversation summary
CREATE OR REPLACE VIEW m5_conversation_summary AS
SELECT
conversation_id,
identity->>'toolName' AS primary_tool,
jsonb_object_keys(tool_permissions) AS tool_name,
(tool_permissions->jsonb_object_keys(tool_permissions)->>'level')::INTEGER AS permission_level,
jsonb_array_length(COALESCE(shared_context->'resources', '[]'::jsonb)) AS resource_count,
jsonb_array_length(COALESCE(intent_history, '[]'::jsonb)) AS intent_count,
updated_at
FROM conversations
WHERE tool_permissions IS NOT NULL AND tool_permissions != '{}'::jsonb;
COMMENT ON VIEW m5_conversation_summary IS
'M5 summary view showing per-tool permissions and shared resource counts';
-- Migration complete
-- Verification queries:
--
-- 1. Check tool permissions:
-- SELECT conversation_id, tool_permissions FROM conversations WHERE tool_permissions != '{}'::jsonb;
--
-- 2. Check shared resources:
-- SELECT conversation_id, shared_context->'resources' FROM conversations;
--
-- 3. Check M5 summary:
-- SELECT * FROM m5_conversation_summary;
--
-- 4. Test permission level function:
-- SELECT get_tool_permission_level(
-- '{"example-tool": {"level": 2}}'::jsonb,
-- 1,
-- 'example-tool'
-- ); -- Should return 2
--
-- 5. Test list resources function:
-- SELECT * FROM list_shared_resources(
-- '{"resources": [{"name": "test", "createdBy": "data-tool", "createdAt": 123, "updatedAt": 456}]}'::jsonb
-- );
