🌐 Building a REST API Suite for Meeting & Appointment Management in Joget

Overview

In enterprise applications built on Joget DX, external mobile apps, third-party portals, or integrated calendars often need to trigger workflow updates—such as confirming, rescheduling, or canceling appointments—without rendering a full Joget web form.

By leveraging Joget API Builder (or BeanShell REST Web Services), developers can expose secure, lightweight REST endpoints that interact directly with Joget database tables, return standardized JSON responses, and automatically capture transaction audit logs.

---

Architecture & How It Works

1. Parameter Parsing: The endpoint receives request parameters via Joget Hash Variables (e.g., #requestParam.id#).
2. Input Validation: Required parameters are validated. If missing or invalid, an HTTP 400 Bad Request JSON structure is returned immediately.
3. Database Operations: Using org.joget.apps.app.service.AppUtil.getApplicationContext().getBean("setupDataSource"), a JDBC Connection and PreparedStatement update the specific appointment record safely against SQL injection.
4. Transaction Audit Logging: Upon successful status updates, a transaction history record is inserted into an audit log table with timestamps, user details (#currentUser.username#), and status descriptions.
5. Standardized Response: A JSON object with response codes (200, 400, 404, 500), status messages, and payload details is returned to the client.

---

Where to Use in Joget

• API Builder: Attach the BeanShell script to a custom REST endpoint in the Joget API Builder plugin.
• Workflow Tools: Execute as a BeanShell Tool within workflow transitions to process automated backend approvals or meeting updates.

---

Full Implementation Code

import org.joget.commons.util.LogUtil;
import org.json.JSONObject;
import org.joget.apps.app.service.AppUtil;
import javax.sql.DataSource;
import java.sql.*;

// 1. Fetch request parameters via Hash Variable
String appointmentId = "#requestParam.id#";

LogUtil.info("Meeting API", "Processing confirmation for ID: " + appointmentId);

JSONObject jsonResponse = new JSONObject();
int updatedRows = 0;

// 2. Validate input parameter
if (appointmentId == null || appointmentId.trim().isEmpty()) {
    jsonResponse.put("code", 400);
    jsonResponse.put("message", "Invalid or missing appointment ID parameter");
    jsonResponse.put("data", new JSONObject().put("receivedId", appointmentId));
    return jsonResponse;
}

Connection con = null;
PreparedStatement psUpdate = null;

try {
    // 3. Obtain Joget DataSource connection
    DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
    con = ds.getConnection();

    // 4. Update main record status safely
    String updateSql = "UPDATE app_fd_meeting_appts SET c_status = 'Confirmed' WHERE id = ?";
    psUpdate = con.prepareStatement(updateSql);
    psUpdate.setString(1, appointmentId);

    updatedRows = psUpdate.executeUpdate();
    LogUtil.info("Meeting API", "Updated rows count: " + updatedRows);

} catch (Exception e) {
    LogUtil.error("Meeting API", e, "Database error during status update");
    jsonResponse.put("code", 500);
    jsonResponse.put("message", "Database error: " + e.getMessage());
    return jsonResponse;
} finally {

    // 5. Audit Transaction History Insertion
    if (updatedRows > 0 && con != null) {
        String parentId = null;
        PreparedStatement psParent = null;
        ResultSet rsParent = null;

        try {
            String parentSql = "SELECT c_fk FROM app_fd_meeting_appts WHERE id = ?";
            psParent = con.prepareStatement(parentSql);
            psParent.setString(1, appointmentId);
            rsParent = psParent.executeQuery();

            if (rsParent.next()) {
                parentId = rsParent.getString("c_fk");
            }
        } catch (Exception e) {
            LogUtil.error("Meeting API", e, "Error reading parent reference");
        } finally {
            try { if (rsParent != null) rsParent.close(); } catch (SQLException e) {}
            try { if (psParent != null) psParent.close(); } catch (SQLException e) {}
        }

        if (parentId != null && !parentId.isEmpty()) {
            PreparedStatement psHistory = null;
            try {
                String historySql = "INSERT INTO app_fd_meeting_history (" +
                    "id, c_transaction_status, dateCreated, dateModified, " +
                    "c_fk, createdBy, modifiedBy, createdByName, modifiedByName, c_message" +
                    ") VALUES (UUID(), ?, NOW(), NOW(), ?, ?, ?, ?, ?, ?)";

                psHistory = con.prepareStatement(historySql);
                psHistory.setString(1, "Confirmed");
                psHistory.setString(2, parentId);
                psHistory.setString(3, "#currentUser.username#");
                psHistory.setString(4, "#currentUser.username#");
                psHistory.setString(5, "#currentUser.fullName#");
                psHistory.setString(6, "#currentUser.fullName#");
                psHistory.setString(7, "Meeting confirmed via API endpoint");

                psHistory.executeUpdate();
            } catch (Exception e) {
                LogUtil.error("Meeting API History", e, "Error inserting history log");
            } finally {
                try { if (psHistory != null) psHistory.close(); } catch (SQLException e) {}
            }
        }
    }

    try {
        if (psUpdate != null) psUpdate.close();
        if (con != null) con.close();
    } catch (SQLException se) {
        LogUtil.error("Meeting API", se, "Error closing connection resources");
    }
}

// 6. Return standardized JSON response
if (updatedRows == 0) {
    jsonResponse.put("code", 404);
    jsonResponse.put("message", "No matching active appointment found");
} else {
    jsonResponse.put("code", 200);
    jsonResponse.put("message", "Appointment confirmed successfully");
}

jsonResponse.put("data", new JSONObject().put("id", appointmentId).put("rowsUpdated", updatedRows));

return jsonResponse;

---

Example Use Cases

• Mobile App Integration: Mobile applications allowing attendees to accept or reschedule meetings with a single tap.
• External Email Webhooks: Webhook callbacks from calendar integrations (e.g., Google Calendar, Outlook) updating appointment statuses in real time.
• Self-Service Portals: External customer portals confirming appointment times without logging directly into the main Joget administrative interface.

---

Key Benefits

• Decoupled Architecture: Enables external platforms to interact cleanly with Joget workflow data.
• Built-in Security: Uses Parameterized SQL PreparedStatements to eliminate SQL injection vulnerabilities.
• Automatic Audit Logging: Ensures complete traceability of status changes and user actions.