Skip to main content

Section 5-3 : Case Study SuiteCRM

How EasyManage AI helped get insights and workflows path sorted for SuiteCRM​

AI Case Study​

This tutorial is based on case study, where AI data solution is used as in a real-world example of a customer using it.

Please review important aspects and key considerations on How To Use AI Data Solutions in previous chapter.

CRM App SuiteCRM Case Study​

SuiteCRM App widely used for CRM.

Summary Highlights​

The case study sought to leverage the power of AI models to unlock the full potential of SuiteCRM system data with EasyManage help.

Successfully achieved key objectives:

1.Get Data modeling suggestions from AI✅
2.Ask AI For Sensitive Data Analysis✅
3.Make SuiteCRM data AI-Ready in 10 Min✅
4.Build, deploy and Customize MCP Server✅
5.AI Model Analysis performed on SuiteCRM data✅
6.Ask AI What Insights and Trends Can Get✅
7.Ask AI What AI Workflows Can Implement✅

Brief Synopsis​

Leverage opportunities for AI data solutions such as AI-driven insights & trends and AI workflow automation for CRM App SuiteCRM as demo installation.

Steps​

Following steps were taken towards implementing solutions using EasyManage AI.

Import Tables And Download Schema​

  • Import Tables
    • App Tables from MySQL/mariaDB cloud database imported into EasyManage.
info
  • SuiteCRM database uses tinyint column which will map properly if used tinyInt1isBit=false parameter in URL connection, e.g. use:
    • jdbc:mysql://<host>:3306/<database>?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false
  • Search tables, listed

import_tables_list.png

  • Tables Importing Options

import_tables_opt.png

  • Tables Imported

import_tables_imported.png

  • Download Table Schema from EasyManage.

table_schema_download.png

Get Data modeling suggestions from AI​

  • Open Cursor and start new project workspace at e.g. C:\suitecrm
  • Copy TableSchemaDnld_suitecrm.sql to project root.
  • Using above table schema SQL file as context, Ask AI model
Give suggestions for data modeling i.e. table joins up to 4 nested levels, specify one-to-many or one-to-one

  • Context given, and asked

  • Asked for data modeling suggestions

dm_suggest_chat_input.png

  • Response for data modeling suggestions

dm_suggest_response.png

Configure Data Modeling​

  • Data models selected based on suggested High‑value join patterns
Accounts → Contacts → Cases → Case Updates

Accounts → Opportunities → Quotes → Quote Line Items

For next module, below can take:

Accounts → Invoices → Line Item Groups → Line Items

  • Define and configure data modeling, table joins in EasyManage Builder Studio for

    • Contacts → Cases → Case Updates
    • Accounts → Contacts

  • Data modeling configured for Contacts → Cases → Case Updates

    • contacts ←→ contacts_cases (via case_id)
    • contacts_cases ←→ cases (via case_id)
    • cases ←→ aop_case_updates (via case_id)

data_modeling_01.png

  • Data modeling configure for Accounts → Contacts
    • accounts ←→ accounts_contacts (via account_id)
    • accounts_contacts ←→ contacts (via contact_id)

data_modeling_02.png

Ask AI For Sensitive Data Analysis​

  • Giving as context TableSchemaDnld_suitecrm.sql, Ask AI model
Analyze the SuiteCRM database schema to identify columns that contain sensitive data, PII, confidential business information, or data revealing sensitive personal information
  • Chat input

ask_sensitive_data_01.png

info
  • How to implement the sensitive data recommendations?
    • Before generating code, use EasyManage field hide option to exclude columns, having sensitive data, from API and MCP Layers, so that AI models will not have access to them. Use via menu links:
      • Data Table Explorer → navigate to folder having table → Select option on right side of table listed → Field Prop - Hide → Set for required columns → Field Prompt Properties → Hide [Yes] and Data Display Properties → Hide [Yes]

EasyManage Builder Studio Project Configure Settings​

proj_configure_settings_01.png

proj_configure_settings_02.png

Scoping Of AI Data Solution Module​

It is recommended to scope and define small AI Data Solution Modules, to tackle problems like:

  • Exceeding Total Tools Limit
  • Context rot (the accuracy drop LLMs exhibit as input context grow)

MCP Server Module Bundling for V1​

It is recommended to keep 5-10 tables in one MCP Server module. Along with data modeling of those tables.

  • Select tables for AI solution module building.
  • Bookmarked tables for generating code
  • Build Selection

build_selected_tables.png

info

All tables used in configure data modeling, table joins relations must be selected in build, else will get compile errors.

  • Create Folder structure for easymanage and cursor workspace.

folder_structure.png

caution

Challenge:

After going thru steps from

  • Build/Generate, to
  • APIs REST : Open project, Build

Encountered error condition: "compile errors" due to tables having key column named id which causes function clash with JPA repository internal functions.

Solution:

  • Create and use views for each table, adding the "_vw" suffix to each view name. Create simple views that select all columns from each table, with aliasing only id column as idCol.

Create Views for each table Selected in Build​

  • Use Cursor GenAI help to accomplish this task.
  • Create copy TableSchemaDnld_suitecrm_views.sql, keeping only build selected tables in it.
  • Give this file as context and ask in chat:
generate sql create view statement for each table with name suffix added as "_vw"

ask_create_views.png

Generate And Use Test Data​

Its recommended to use realistic test data for testing AI data solutions.

  • Generate SQL Insert statements For Realistic Test Data, i.e. dummy/synthetic data
  • Use Cursor GenAI help to accomplish this task.
  • Previous copy TableSchemaDnld_suitecrm_views.sql
  • Give this file as context and ask in chat:
generate sql insert statements for the tables, for dummy or synthetic data 5-10 records

ask_test_data.png

Import tables For The Views​

  • EasyManage Builder:

    • Perform Import tables for the views.

  • Search tables, listed

import_tables_list_vw.png

  • Tables Importing Options

import_tables_opt_vw.png

  • Tables Imported

import_tables_imported_vw.png

  • Performed Tables Imported For Views - List in Studio

import_tables_views.png

  • EasyManage Builder:
    • Configure data modeling for table joins on views instead of base tables.
    • Delete data modeling for table joins on base tables.

MCP Server Module Bundling for V2​

  • Select tables for AI solution module building.
  • Bookmarked tables for generating code
  • Build Selection

build_selected_views.png

  • Build/Generate : Included will be MCP Server REST with APIs REST.
    • This will be version V2.

build_generate_code.png

  • Download generated code.

download_gen_code.png

  • Updated Folder structure for easymanage and cursor workspace.

folder_structure_upd.png

  • Copy below projects from extracted code to workspace suitecrm_workspace.

    • backend\spring-java\emapi
    • mcp\spring-java\emmcp

  • APIs REST : Open project, Build

emapi_proj_01.png

  • Run APIs - CMD Prompt
    • cd suitecrm_workspace
    • java -jar emapi\app\dbrest\target\dbrest-1.0-SNAPSHOT.jar

emapi_run.png

emapi_swagger_01.png

  • Run APIs - Check Swagger endpoint

emapi_swagger_02.png

Customize MCP Server as V3​

tip

Its recommended to customize and disable Tools to avoid getting Exceeding Total Tools Limit warning in Cursor.

  • Customize MCP Server REST V2, disable some table level tools globally.

    • Please see section: How to Enable/Disable Tools in README-mcprest.md

  • Verify and Disable Tools as indicated below and try out.

    • Table Level - Keep Disabled
      • TOOL-ViewAllPaged
    • Table Level - Keep
      • TOOL-ViewAll
      • TOOL-SelectWhere
      • TOOL-Query
    • AI Transactions - Disable
      • AI-TRAN-ADD
    • AI Transactions - Keep Disabled
      • AI-TRAN-EDIT
      • AI-TRAN-DEL

  • Disable Tools using VS Code

emmcp_tools_disable.png

  • MCP Server REST : Open project, Build

emmcp_proj_01.png

Use MCP Server V3 in Cursor​

  • Clean Start

    • Move/Remove all *.sql files from project root
    • Close/Re-Open Cursor
    • We want AI model to call tool get_all_table_schema once available via MCP.

  • Configure and Use MCP Server V3 in Cursor.

  • Sample mcp.json

{
  "mcpServers": {
    "easymanage": {
      "command": "C:/ProgFiles/Java/jdk-17/bin/java.exe",
      "args": 
      ["-jar", 
      "path_to_workspace/suitecrm_workspace/emmcp/app/mcprest/target/mcprest-1.0-SNAPSHOT.jar"
      ],
      "env": {
       "em.mcp.apiUrl": "http://127.0.0.1:9080/emdbrest",
       "em.mcp.authType": "None",
       "em.mcp.authToken": ""
      }
    }
  }
}
  • Configured MCP Server in Cursor.
    • Shows: Found 32 Tools

emmcp_tools_configured.png

AI Model Analysis​

Results from AI analysis is given in response docs below.

Presented below and listed are few analysis and solutions being recommended by AI models.

easymanage get and study all table schema and suggest what insights and trends can be derived from tables
  • Chat input

emmcp_tools_ask_insights.png

Please refer to response doc

Ask AI What AI Workflows Can Implement​

easymanage get and study all table schema and suggest what AI workflows can be implemented based on tables
  • Chat input

emmcp_ask_workflows_01.png

  • Chat Response

emmcp_ask_workflows_02.png

Please refer to response doc

Next Steps​

After AI Transaction Tools enabled, can ask AI What AI Transactions Can Implement.

Enable AI Transaction Tools​

  • Customize MCP Server REST V3 to build V4, where in enable AI transaction table level tools globally.
    • Please see section: How to Enable/Disable Tools in README-mcprest.md

Ask AI What AI Transactions Can Implement​

easymanage get and study all table schema and suggest what AI transactions can be implemented