/**
* Action Template Service Layer
*
* This service manages action templates in the CommTool system. Action templates
* define the structure and behavior of actions that can be executed by bots.
*
* Key concepts:
* - Action Templates: Reusable templates that define action structure and UI
* - Organizations: Templates belong to specific organizations
* - Bots: Can be linked to action templates to execute actions
* - Multi-tenancy: Each organization has isolated templates
*
* Database structure:
* - ObjectBase: Main table storing templates with Type='actionT'
* - Links: Junction table linking bots to action templates
*
* @module ActionTemplateService
*/
import { query, UUID2hex, HEX2uuid } from '@commtool/sql-query';
import { renderObject } from '../../utils/renderTemplates.js';
import { Templates } from '../../utils/compileTemplates.js';
import { getUID } from '../../utils/UUIDs.js';
import { updateTemplate } from '../../server.ws.js';
import { publishEvent } from '../../utils/events.js';
import { buildTranslateObject, mergeTemplateData, extractUIDefaults } from './helpers.js';
import { errorLoggerRead, errorLoggerUpdate } from '../../utils/requestLogger.js';
/**
* Get a single action template by UID
*
* Retrieves a specific action template with its complete data structure.
* Used for template editing, viewing details, and bot execution contexts.
*
* @param {string} uid - Action template UUID
* @param {string} orgUID - Organization UUID (for security validation)
* @returns {Promise<Object>} Response object with success flag and template data
*
* Security features:
* - Validates organization ownership (UIDBelongsTo=orgUID)
* - Prevents cross-organization template access
*
* Returns complete template data including:
* - Metadata (UID, Display, SortName, Title)
* - Template structure and configuration (Data)
* - Organization context
*/
export const getActionTemplate = async (uid, orgUID) => {
try {
// Query specific action template with organization validation
// Security check ensures template belongs to the requesting organization
const result = await query(`
SELECT
UID,
Display,
SortName,
Title,
Data,
dindex
FROM
ObjectBase
WHERE
UID=? AND Type='actionT' AND UIDBelongsTo=?
`, [UUID2hex(uid), UUID2hex(orgUID)], { cast: ['UUID', 'json'] });
if (result.length === 0) {
return { success: false, error: 'Action template not found or access denied' };
}
return { success: true, result: result[0] };
} catch (e) {
errorLoggerRead(e);
return { success: false, error: e.message };
}
};
/**
* Get all action templates for a specific organization
*
* Retrieves action templates that belong to an organization. These templates
* define the structure and behavior of actions that can be created within
* the organization's context.
*
* @param {string} orgUID - Organization UUID
* @param {Object} req - Express request object (for query parameters)
* @returns {Promise<Object>} Response object with success flag and results
*
* Query parameters:
* - includeData: 'true' to include template data, otherwise only metadata
*
* Database schema:
* - ObjectBase.Type='actionT' for action templates
* - UIDBelongsTo links template to organization
* - Ordered by dindex for consistent display order
*/
export const getActionTemplatesForOrg = async (orgUID, req) => {
try {
// Conditionally include template data based on query parameter
// Template data can be large, so only include when explicitly requested
let mainData = '';
if (req.query.includeData === 'true') {
mainData = ', Main.Data';
}
// Query organization-specific action templates
// Uses GROUP BY to ensure unique templates (in case of duplicates)
const result = await query(`
SELECT
Main.UID,
Main.Display,
Main.SortName
${mainData}
FROM
ObjectBase AS Main
WHERE
Main.UIDBelongsTo=? AND Main.Type='actionT'
GROUP BY
Main.UID
ORDER BY
JSON_VALUE(Main.Data, '$.category'), Main.dindex
`, [UUID2hex(orgUID)], { cast: ['UUID', 'json'] });
return { success: true, result };
} catch (e) {
// Log read errors for monitoring and debugging
errorLoggerRead(e);
return { success: false, error: e.message };
}
};
/**
* Get all action templates associated with a specific bot
*
* Retrieves action templates that are linked to a bot through the Links table.
* This enables bots to discover which action templates they can execute.
* Includes organization information for multi-tenant context.
*
* @param {string} botUID - Bot UUID
* @param {Object} req - Express request object (for query parameters)
* @returns {Promise<Object>} Response object with success flag and results
*
* Database relationships:
* - Links.UID = botUID, Links.UIDTarget = actionTemplateUID
* - Links.Type = 'actionT' identifies action template links
* - Joins with organization data for context
*
* Results include:
* - Template metadata (UID, Display, SortName)
* - Optional template data (if includeData=true)
* - Organization context (OrgUID, OrgDisplay)
*/
export const getActionTemplatesForBot = async (botUID, req) => {
try {
// Conditionally include template data - can be large
let mainData = '';
if (req.query.includeData === 'true') {
mainData = ', ActionT.Data';
}
// Query bot-linked action templates with organization context
// INNER JOIN with Links ensures only linked templates are returned
// LEFT JOIN with Org provides organization context (may be null)
const result = await query(`
SELECT
ActionT.UID,
ActionT.Display,
ActionT.SortName
${mainData},
ActionT.UIDBelongsTo AS OrgUID,
OrgMember.Display AS OrgDisplay,
gid.UID AS OrgLoginUID
FROM ObjectBase AS ActionT
INNER JOIN Links ON (Links.UIDTarget = ActionT.UID AND Links.Type = 'actionT')
INNER JOIN Member AS OrgMember ON (OrgMember.UID=ActionT.UIDBelongsTo)
LEFT JOIN Links AS gid ON (gid.UIDTarget=OrgMember.UID AND gid.Type='identifyer')
WHERE Links.UID = ? AND ActionT.Type = 'actionT'
ORDER BY OrgMember.Display, ActionT.Display
`, [UUID2hex(botUID)], { cast: ['UUID', 'json'] });
return { success: true, result };
} catch (e) {
errorLoggerRead(e);
return { success: false, error: e.message };
}
};
/**
* Get organizations by their UIDs
*
* Utility function to retrieve organization details for multiple organizations.
* Used for batch operations and providing organization context in templates.
*
* @param {string[]} organizationUIDs - Array of organization UUIDs
* @returns {Promise<Object>} Response object with organization details
*
* Returns organization data including:
* - UID: Organization identifier
* - Display: Human-readable organization name
* - Data: Organization-specific configuration (JSON)
*/
export const getOrganizationsByUIDs = async (organizationUIDs) => {
try {
// Convert UUIDs to hex format for database query
const orgUIDs = organizationUIDs.map(uid => UUID2hex(uid));
// Query multiple organizations by UID
// Type='orga' filters for organization records
const result = await query(`
SELECT ObjectBase.UID, Member.Display, Member.Data, gid.UID AS OrgLoginUID
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
INNER JOIN Links ON (Links.UIDTarget=ObjectBase.UID AND Links.Type='memberSys' AND Links.UID=ObjectBase.UID)
LEFT JOIN Links AS gid ON (gid.UIDTarget=ObjectBase.UID AND gid.Type='identifyer')
WHERE ObjectBase.Type='group' AND ObjectBase.UID IN (?)
`, [orgUIDs], { cast: ['UUID', 'json'] });
return { success: true, result };
} catch (e) {
errorLoggerRead(e);
return { success: false, error: e.message };
}
};
/**
* Create or update an action template
*
* This is the core function for managing action templates. It handles both
* creation of new templates and updates to existing ones. The function
* processes template data, applies organization-specific rendering, and
* manages internationalization support.
*
* @param {Object} req - Express request object (contains session and query data)
* @param {Object} templateData - Template configuration and structure
* @returns {Promise<Object>} Response with created/updated template data
*
* Process flow:
* 1. Generate or retrieve template UID
* 2. Check if template exists (for updates vs creation)
* 3. Merge new data with existing data (if updating)
* 4. Render template with organization context
* 5. Build internationalization structure
* 6. Save to database with upsert operation
* 7. Notify UI of template changes via WebSocket
*
* Query parameters:
* - initialize: If undefined, always use new data; if defined, merge with existing
*
* Features:
* - Template rendering with organization context
* - Data merging for incremental updates
* - Internationalization support (translate object)
* - Real-time UI updates via WebSocket notifications
* - Full-text indexing for search functionality
*/
/**
* Create a new action template
*
* Creates a new action template for the current organization.
* This is used when registering templates from bots.
*
* @param {Object} req - Express request object with session information
* @param {Object} templateData - Template configuration data
* @returns {Promise<Object>} Response object with success flag and created template data
*/
export const createActionTemplate = async (req, templateData) => {
try {
// Generate new UID for the action template
const [{UIDaction}] = await query(`SELECT UIDV1() AS UIDaction`, []);
// Get the template renderer for the current organization
const template = Templates[req.session.root].actionTemplate;
// Render template with organization-specific context and data
const object = await renderObject(template, templateData, req);
// Validate numeric fields to prevent NaN errors
if (isNaN(object.dindex) || object.dindex === null || object.dindex === undefined) {
object.dindex = 0;
}
// Prepare data for database storage (remove UID from data to avoid duplication)
object.Data = { ...templateData, UID: undefined };
// Set up translation structure for internationalization
if (object.Data.UIaction) {
// Initialize translation object if not present
object.Data.translate = object.Data.translate || {};
// Build translation keys from UI action structure
buildTranslateObject(object.Data.UIaction, object.Data.translate);
}
// Insert template to database
await query(`
INSERT INTO ObjectBase(UID,Type,UIDBelongsTo,Title,Display,SortName,FullTextIndex,dindex,Data)
VALUES (?,'actionT',?,?,?,?,?,?,?)
`, [UUID2hex(UIDaction), UUID2hex(req.session.root), object.Title, object.Display, object.SortIndex, object.FullTextIndex, object.dindex, JSON.stringify(object.Data)]);
// Notify UI clients that templates have been updated for this organization
updateTemplate(req.session.root);
return {
success: true,
result: { ...object, UID: HEX2uuid(UIDaction) },
UIDaction
};
} catch (e) {
errorLoggerUpdate(e);
return { success: false, error: e.message };
}
};
/**
* Update an existing action template
*
* Updates an existing action template identified by UID.
* Requires the template to exist and belong to the current organization.
*
* @param {Object} req - Express request object with session information
* @param {Object} templateData - Template configuration data (must include UID)
* @returns {Promise<Object>} Response object with success flag and updated template data
*/
export const updateActionTemplate = async (req, templateData) => {
try {
if (!templateData.UID) {
return { success: false, error: 'UID is required for updating action template' };
}
const UIDaction = UUID2hex(templateData.UID);
// Verify template exists and belongs to current organization
const existingTemplate = await query(
`SELECT Data FROM ObjectBase WHERE UID=? AND Type='actionT' AND UIDBelongsTo=?`,
[UIDaction, UUID2hex(req.session.root)]
);
if (existingTemplate.length === 0) {
return { success: false, error: 'Action template not found or access denied' };
}
// Get the template renderer for the current organization
const template = Templates[req.session.root].actionTemplate;
// Parse existing template data
let existingData = {};
try {
existingData = JSON.parse(existingTemplate[0].Data);
} catch (e) {
console.log('Existing template has invalid data, using empty object');
}
// Direct update: only update fields present in request body, preserve the rest (partial update)
// templateData has structure { UID, Name, Pattern, Data: {...} }, extract .Data for merging
// Keep UID for renderObject (it expects data.UID in string format)
const Data = { ...existingData, ...(templateData.Data || templateData), UID: templateData.UID };
// Render template with organization-specific context and data
const object = await renderObject(template, Data, req);
// Validate numeric fields to prevent NaN errors
if (isNaN(object.dindex) || object.dindex === null || object.dindex === undefined) {
object.dindex = 0;
}
// Prepare data for database storage (remove UID from data to avoid duplication)
object.Data = { ...Data, UID: undefined };
// Set up translation structure for internationalization
if (object.Data.UIaction) {
// Initialize translation object if not present
object.Data.translate = object.Data.translate || {};
// Build translation keys from UI action structure
buildTranslateObject(object.Data.UIaction, object.Data.translate);
}
// Update template in database
await query(`
UPDATE ObjectBase
SET Title=?, Display=?, SortName=?, FullTextIndex=?, dindex=?, Data=?
WHERE UID=? AND Type='actionT' AND UIDBelongsTo=?
`, [object.Title, object.Display, object.SortIndex, object.FullTextIndex, object.dindex, JSON.stringify(object.Data), UIDaction, UUID2hex(req.session.root)]);
// Propagate changed defaults to all existing actions (virgin fields only)
await propagateDefaultsToActions(templateData.UID);
// Notify UI clients that templates have been updated for this organization
updateTemplate(req.session.root);
return {
success: true,
result: { ...object, UID: HEX2uuid(UIDaction) },
UIDaction: HEX2uuid(UIDaction)
};
} catch (e) {
errorLoggerUpdate(e);
return { success: false, error: e.message };
}
};
export const createOrUpdateActionTemplate = async (req, templateData, botUID) => {
try {
let UIDaction;
// Check if bot already has an action template registered in this organization
// by looking for an existing actionT link from the bot to a template
const existingLink = await query(
`SELECT Links.UIDTarget AS UID
FROM Links
INNER JOIN ObjectBase ON (ObjectBase.UID = Links.UIDTarget AND ObjectBase.Type='actionT' AND ObjectBase.UIDBelongsTo=?)
WHERE Links.UID = ? AND Links.Type = 'actionT'`,
[UUID2hex(req.session.root), UUID2hex(botUID)],
{ cast: ['UUID'], log: true}
);
if (existingLink.length > 0) {
// Bot already has a template registered in this organization - update it
// Convert UUID string to hex format for database queries
UIDaction = UUID2hex(existingLink[0].UID);
} else {
// No template found for this bot in this organization - create new one
[{UIDaction}] = await query(`SELECT UIDV1() AS UIDaction`, []);
}
// Get the template renderer for the current organization
const template = Templates[req.session.root].actionTemplate;
// Check if template already exists in database
const action = await query(`SELECT Data FROM ObjectBase WHERE UID=? `, [UIDaction]);
let aData = {};
// Parse existing template data if found
if (action.length === 1) {
try {
aData = JSON.parse(action[0].Data);
} catch (e) {
console.log('action without data');
// Template exists but has no valid JSON data
}
}
let Data;
// Register endpoint always merges when template exists to preserve admin customizations
if (action.length === 0) {
// New template - use data as-is (extract Data field from template structure)
// Add UID for renderObject (it expects data.UID in string format)
Data = { ...(templateData.Data || templateData), UID: HEX2uuid(UIDaction) };
} else {
// Existing template -always merge to preserve admin customizations
// templateData.Data is the new bot data, aData is existing data from DB
Data = { ...mergeTemplateData(templateData.Data || templateData, aData), UID: HEX2uuid(UIDaction) };
}
// Render template with organization-specific context and data
const object = await renderObject(template, Data, req);
// Validate numeric fields to prevent NaN errors
if (isNaN(object.dindex) || object.dindex === null || object.dindex === undefined) {
object.dindex = 0;
}
// Prepare data for database storage (remove UID from data to avoid duplication)
object.Data = { ...Data, UID: undefined };
// Set up translation structure for internationalization
if (object.Data.UIaction) {
// Initialize translation object if not present
object.Data.translate = object.Data.translate || {};
// Build translation keys from UI action structure
// This adds new keys but preserves existing translations (admin customizations)
buildTranslateObject(object.Data.UIaction, object.Data.translate);
}
// Check if data actually changed to avoid unnecessary updates on system-versioned tables
let needsUpdate = true;
if (action.length === 1 && aData) {
// Compare stringified data to see if anything changed
const existingDataStr = JSON.stringify(aData);
const newDataStr = JSON.stringify(object.Data);
needsUpdate = existingDataStr !== newDataStr;
}
// Only update database if this is a new template or data actually changed
if (action.length === 0 || needsUpdate) {
// Insert template to database
// Uses ON DUPLICATE KEY UPDATE for atomic create-or-update operation
await query(`
INSERT INTO ObjectBase(UID,Type,UIDBelongsTo,Title,Display,SortName,FullTextIndex,dindex,Data)
VALUES (?,'actionT',?,?,?,?,?,?,?)
ON DUPLICATE KEY UPDATE Title=VALUE(Title),Display=VALUE(Display),SortName=VALUE(SortName),FullTextIndex=VALUE(FullTextIndex),dindex=VALUE(dindex),Data=VALUE(Data)
`, [UUID2hex(UIDaction), UUID2hex(req.session.root), object.Title, object.Display, object.SortIndex, object.FullTextIndex, object.dindex, JSON.stringify(object.Data)]);
// Propagate changed defaults to all existing actions (virgin fields only)
if (action.length > 0) {
await propagateDefaultsToActions(HEX2uuid(UIDaction));
}
// Notify UI clients that templates have been updated for this organization
// Uses WebSocket to provide real-time updates to connected clients
updateTemplate(req.session.root);
}
return {
success: true,
result: { ...object, UID: HEX2uuid(UIDaction) },
UIDaction: HEX2uuid(UIDaction)
};
} catch (e) {
errorLoggerUpdate(e);
return { success: false, error: e.message };
}
};
/**
* Create a link between a bot and an action template
*
* This function establishes the relationship that allows a bot to execute
* actions based on a specific action template. The relationship is stored
* in the Links table and enables the bot discovery system.
*
* @param {string} botUID - Bot UUID that will be linked to the template
* @param {string} actionTemplateUID - Action template UUID to link
* @returns {Promise<Object>} Response object with success status
*
* Database operation:
* - Uses ON DUPLICATE KEY UPDATE to handle existing links gracefully
* - Links.UID = botUID (the bot that can execute actions)
* - Links.UIDTarget = actionTemplateUID (the template that defines the action)
* - Links.Type = 'actionT' (identifies this as an action template link)
*
* Use cases:
* - Bot registration: When a bot starts up and registers its capabilities
* - Template assignment: When administrators assign templates to bots
* - Multi-tenant isolation: Bots can only see templates they're linked to
*/
export const createBotLink = async (botUID, actionTemplateUID) => {
try {
// Create or update bot-to-action-template link
// ON DUPLICATE KEY UPDATE ensures idempotent operation
await query(`
INSERT IGNORE INTO Links (UID, Type, UIDTarget)
VALUES (?, 'actionT', ?)
`, [UUID2hex(botUID), UUID2hex(actionTemplateUID)]);
return { success: true };
} catch (e) {
errorLoggerUpdate(e);
return { success: false, error: e.message };
}
};
/**
* Delete an action template and all associated data
*
* This function performs a cascading delete of an action template, removing
* all related data to maintain database integrity. It's a comprehensive
* cleanup operation that affects multiple tables.
*
* @param {string} uid - Action template UUID to delete
* @param {string} orgUID - Organization UUID (for security validation)
* @returns {Promise<Object>} Response object with success status
*
* Cascading delete operations:
* 1. Delete the action template from ObjectBase
* 2. Remove bot links to this template from Links table
* 3. Delete all active actions created from this template
* 4. Notify UI clients of template changes
*
* Security measures:
* - Validates organization ownership (UIDBelongsTo=orgUID)
* - Prevents cross-organization template deletion
*
* Side effects:
* - All bots lose access to this template
* - All active actions from this template are removed
* - UI clients receive real-time update notifications
*/
export const deleteActionTemplate = async (uid, orgUID) => {
try {
const UID = UUID2hex(uid);
// Delete the action template from ObjectBase
// Security check: Only delete if template belongs to the specified organization
await query(`DELETE FROM ObjectBase WHERE UID=? AND Type='actionT'
AND UIDBelongsTo=?`, [UID, UUID2hex(orgUID)]);
// Remove bot links to this action template
// This prevents bots from trying to execute actions from deleted templates
await query(`DELETE FROM Links WHERE Type='actionT' AND UIDTarget=?`, [UID]);
// Delete all active actions that were created from this template
// Complex join operation to find and delete related action instances
query(`DELETE Links,ObjectBase
FROM Links
INNER JOIN ObjectBase ON (Links.UIDTarget=ObjectBase.UID AND Links.Type='action' AND ObjectBase.Type='action')
WHERE Links.UID=?`, [UID]);
// Notify UI clients that templates have been updated for this organization
updateTemplate(orgUID);
return { success: true };
} catch (e) {
errorLoggerUpdate(e);
return { success: false, error: e.message };
}
};
/**
* Restart an action template
*
* This function sends a restart signal to the bots that defined
* the specified action template. It's used to update call the onStartup function
* of the bot part without requiring a full bot restart.
*
* @param {string} uid - Action template UUID to restart
* @returns {Promise<Object>} Response object with success status
*
* Operation mechanism:
* - Publishes a restart event to the Redis event system
* - Event format: `/restart/actionT/{templateUID}`
* - All subscribed bots receive the restart signal
*
* Bot behavior upon receiving restart signal:
* - Bots executed the onStartup function for the specified template
*
* Use cases:
* - Template logic has been modified
* - Template parameters have changed
* - Template permissions have been updated
* - Forcing bot cleanup related to the template
*/
export const restartActionTemplate = async (uid) => {
try {
// Publish restart event to Redis for all subscribed bots
// Event path follows pattern: /restart/actionT/{templateUID}
// Note: organization is null for system-wide action template restarts
publishEvent(`/restart/actionT/${uid}`, { organization: null, data: { uid } });
return { success: true };
} catch (e) {
errorLoggerUpdate(e);
return { success: false, error: e.message };
}
};
/**
* Propagate template default changes to all actions of a template.
*
* Computes the full set of defaults (UI field defaults + template.defaults)
* and updates every action that belongs to this template. Only virgin fields
* (those NOT listed in action.modifiedFields) are overwritten with the new
* default value. User-modified fields are left untouched.
*
* @param {string} templateUID - The action template UID (UUID string)
* @param {Object} templateData - The template Data object (after admin/bot merge)
* @returns {Promise<{success: boolean, updatedCount?: number, error?: string}>}
*/
export const propagateDefaultsToActions = async (templateUID) => {
try {
const UIDtemplate = UUID2hex(templateUID);
// Fetch the template data to get the current defaults
const [tmpl] = await query(
`SELECT Data FROM ObjectBase WHERE UID=? AND Type='actionT'`,
[UIDtemplate], { cast: ['json'] }
);
if (!tmpl) return { success: true, updatedCount: 0 };
const templateData = tmpl.Data;
// Build full defaults: UI field defaults (lowest prio) → template.defaults (highest prio)
const uiDefaults = templateData.UIaction ? extractUIDefaults(templateData.UIaction) : {};
const allDefaults = { ...uiDefaults, ...templateData.defaults };
if (Object.keys(allDefaults).length === 0) {
return { success: true, updatedCount: 0 };
}
// Fetch all actions belonging to this template
const actions = await query(
`SELECT ObjectBase.UID, ObjectBase.Data
FROM ObjectBase
INNER JOIN Links ON (Links.UIDTarget=ObjectBase.UID AND Links.Type='action')
WHERE Links.UID=? AND ObjectBase.Type='action'`,
[UIDtemplate], { cast: ['json'] }
);
let updatedCount = 0;
for (const action of actions) {
const data = action.Data || {};
const modifiedFields = new Set(data.modifiedFields || []);
let changed = false;
// Update virgin fields with new defaults
for (const [key, defaultVal] of Object.entries(allDefaults)) {
if (!modifiedFields.has(key)) {
if (data[key] !== defaultVal) {
data[key] = defaultVal;
changed = true;
}
}
}
if (changed) {
await query(
`UPDATE ObjectBase SET Data=? WHERE UID=? AND Type='action'`,
[JSON.stringify({ ...data, UID: undefined }), action.UID]
);
updatedCount++;
}
}
return { success: true, updatedCount };
} catch (e) {
errorLoggerUpdate(e);
return { success: false, error: e.message };
}
};