Guide to Install an Use Data Builder for Perfex CRM

Restful API & GraphQL for Perfex CRM

RESTful API & GraphQL — Developer Documentation

TASKS IN PROGRESS

Features processed and integrated in version 2.3.1

- (No tasks in progress)

TASKS COMPLETED (PENDING RELEASE)

- (No completed tasks)

P/S: These features will be integrated to meet the needs of customers using the module and requiring integration within the system.

VERSION HISTORY

Version 2.3.0 (May 28, 2026)

NEW

- Dashboard View: Create dashboards from built-in Presets (CEO Overview, Finance Summary, Marketing, Project Manager, Support Center) or build Custom dashboards from scratch (May 24, 2026)
- Dashboard Switcher: switch between dashboards instantly via dropdown, last-used dashboard remembered per user (May 24, 2026)
- Widget Library: 4 widget types — Chart, DataTable, KPI Card, Text/Note (May 24, 2026)
- Chart Widget supports 7 chart types: Bar, Line, Area, Pie, Doughnut, Radar, Polar Area — switchable on-the-fly (May 24, 2026)
- Drag-and-drop widget layout with configurable column widths (May 24, 2026)
- Auto-refresh with configurable interval (1/5/10/30 minutes) (May 24, 2026)
- Share dashboards with team members (May 24, 2026)
- Export All CSV: export the entire filtered report as CSV, bypassing page-size limits (May 24, 2026)
- Server-Side Query Caching with configurable Cache TTL from Admin Builder UI (May 24, 2026)
- Admin Force Refresh: "Clear Cache" button for real-time data on demand (May 24, 2026)
- Index Advisor: analyze view configurations and suggest optimal SQL indexes (May 24, 2026)
- Interactive Embed Report Generator with chart/table toggle and Dark Theme (May 24, 2026)
- Centralized Cache Manager: bulk enable, disable, and clear cache across all reports (May 24, 2026)

CHANGED

- Confirmation Modals: changed native confirm dialogs in "Report Groups" and "Webhooks" to styled SweetAlert2 modals for consistency with "Delete View" (May 24, 2026)
- Webhooks Action Buttons: styled actions inside the webhooks management view with clean, modern flat icons to match the /admin/data_builder view style (May 24, 2026)

FIXED

- Expression Join Totals: fixed Total row calculation in "Monthly Expense vs Payment" and "Net Result by Month" templates to correctly compute SUM(Payment) - SUM(Expense); negative values now display properly (May 24, 2026)
- Embed Report Styling: fixed Total row dark/light mode colors in public embed table (May 24, 2026)
- Chart Zoom UX: hide zoom controls for chart types that do not support axis zooming (Pie, Doughnut, Radar, PolarArea) (May 24, 2026)

Version 2.2.4 (May 18, 2026)

NEW

- Monthly Expense vs Payment (Expression Join): An essential cash flow comparison template and business financial health dashboard that empowers CFOs to track monthly expenses vs payments, identifying liquidity gaps within their Perfex CRM finance reports. (May 17, 2026)
- Net Result by Month (Payment - Expense): An automated business profitability tracking tool and monthly profit and loss template that calculates payment minus expense, helping owners perform accurate P&L reporting and net result analysis. (May 17, 2026)
- Revenue by Year & Revenue by Quarter: A comprehensive quarterly revenue report and annual sales revenue template for sales directors to track invoice volume and measure Perfex CRM year-over-year growth for quarterly business performance reporting. (May 17, 2026)
- Payments (Income) by Quarter & Year: An advanced cash collection report and payments received dashboard designed for accounts receivable analytics, enabling teams to perform accurate quarterly income tracking and cash flow generation. (May 17, 2026)
- Expenses by Quarter & Year: A detailed quarterly expense report and annual corporate spending analysis that provides operations teams with an operational cost dashboard and expense transaction tracking to streamline budget management. (May 17, 2026)

Version 2.2.3 (May 5, 2026)

CHANGED

- Time Duration Formatter: Introduced a new "Time Duration (HH:MM)" formatter to the Builder interface, allowing decimal hours to be properly displayed as `HH:MM` while maintaining backend aggregation integrity. (May 5, 2026)
- Thousands Separator in Time Formats: Enhanced the time duration formatting logic to include thousands separators for extreme hour counts (e.g., `57,614:01`). (May 5, 2026)
- Time Breakdown Logic: Added dynamic "sub-hours" rendering (Days, Weeks, Months, Years) into the new `time_duration` formatter for durations exceeding 24 hours. (May 5, 2026)
- Preset Updates: Updated the "Timesheet Summary by Project & Staff" template preset to feature side-by-side time columns: "Time (h)" utilizing the new `time_duration` formatter and "Time (decimal)" retaining the standard `number` formatter for better reporting flexibility. (May 5, 2026)

Version 2.2.2 (May 2, 2026)

NEW

- Date Macros: Officially implemented {{CURRENT_WEEK_START}} and {{CURRENT_MONTH_START}} dynamic date macros into the SQL parser (Runtime_model) and added quick-access UI buttons to the Builder interface. Improved CURDATE date generation to ensure exact bounds matching using DATE_FORMAT formatting. (May 1, 2026)

CHANGED

- Frontend Consistency: Extended the time-breakdown formatter logic across all Data Builder datatables, including runtime view, the Data Preview modal in the builder interface, and the public embedded report viewer. (May 1, 2026)
- Smart Time Breakdown: Implemented dynamic "sub-hours" rendering (Days, Weeks, Months, Years) with thousand separators for long-duration task hour columns exceeding 24 hours. (May 1, 2026)
- Template Preset Modal: Unified status badge and button sizing with consistent gap spacing. Shortened "Already in Views/Reports" label to "Saved View" for cleaner layout. (May 1, 2026)
- Builder Tips & Guide: Added a contextual help modal (?) to the View Builder toolbar describing Templates, Save as Template, JSON Import/Export, Preview Data, and Preview SQL features. (May 1, 2026)

FIXED

- Timesheet Calculation: Standardized hour calculations across all Data Builder templates (e.g., timesheet-by-project-staff, billable-hours) using robust SQL Unix timestamp arithmetic ((COALESCE(end_time, UNIX_TIMESTAMP()) - start_time) / 3600) instead of the unreliable TIMESTAMPDIFF, ensuring accurate decimals for both active and completed timers. (May 1, 2026)
- Timestamp Filtering Bug: Fixed a critical issue where filtering by date on UNIX timestamp columns (like tbltaskstimers.start_time) failed due to MySQL string vs integer comparison. The query builder now automatically wraps known UNIX fields in FROM_UNIXTIME(). (May 1, 2026)
- Dynamic Filter Duplication: Resolved a logical flaw in Runtime_model where dynamic UI filters were redundantly appended without FROM_UNIXTIME formatting via legacy apply_filters methods, preventing macros like {{CURDATE}} from accurately returning data. (May 1, 2026)
- UI Inconsistency: Removed custom bootstrap-select checkbox styling in runtime.css that was overriding and conflicting with standard Perfex CRM core UI components. (May 1, 2026)
- Footer Totals Ignoring Filters: Fixed a bug where the footer Total row in report views always showed aggregated values from the full dataset instead of recalculating based on the user's active dynamic filters. The totals rebuild query now passes dynamic_filters to build_query. (May 1, 2026)
- JSON Import Hardening: Added file size limit (2 MB), version compatibility check (2.x), strict Array.isArray validation for tables/fields, and XSS-safe HTML escaping for user-controlled name/description before rendering in the confirmation dialog. (May 1, 2026)

Version 2.2.0 (April 27, 2026)

NEW

- Added free companion module "API Data Builder Sample" to help developers quickly reference and integrate REST API & GraphQL with Data Builder. Source: https://github.com/PolyXGO/api_data_builder_sample (April 26, 2026)
- OWASP Security Headers: All API responses now include X-Content-Type-Options (nosniff), X-Frame-Options (DENY), Referrer-Policy, and X-XSS-Protection (0) headers. (April 26, 2026)
- HTTP Cache Control: All API responses include Cache-Control: no-store and Pragma: no-cache to prevent proxy/browser caching of dynamic CRM data. (April 26, 2026)
- Auth Test Endpoint: GET /api/v1/auth/test — Validates API token and returns connection metadata (token name, scopes, tables count, server time) without exposing CRM data. Designed for Zapier, Make.com, and n8n connection setup. (April 26, 2026)
- Polling Support (?since=): All list endpoints (GET /api/v1/{resource}) now accept a ?since=ISO8601 parameter to filter records by creation/modification timestamp. Auto-detects Perfex CRM timestamp columns (dateadded, datecreated, etc.) and returns results sorted newest-first. Enables polling triggers for automation platforms. (April 26, 2026)
- Endpoint Registry: Auth Test and ?since= parameter now appear in API Explorer sidebar and Public API Docs. (April 26, 2026)
- Template Catalog — Client Reports: New category with 5 ready-to-use report templates designed for automated weekly/monthly client reporting via API or PDF export. Includes Weekly Project Maintenance Summary, Tickets This Week by Client, Timesheet Summary by Project & Staff, Monthly Tasks Completed by Project, and Ticket Resolution Summary by Client. (April 27, 2026)
- Formatter — Task Priority: New task_priority formatter converts numeric priority values (1–4) to human-readable labels (Low, Medium, High, Urgent) using Perfex's native task_priority() helper. Available in Builder dropdown for any priority field on task tables. (April 27, 2026)
- Formatter — Payment Mode: New payment_mode formatter resolves payment mode IDs to their display names via static-cached DB lookup (single query per request). Auto-detected for paymentmode fields. Applied to Daily Sales by Item template. (April 27, 2026)

CHANGED

- Db_api_response: Added _set_standard_headers() method called from success(), error(), and from_context() — single point of control for all response headers. (April 26, 2026)
- QueryBuilderTrait: Added _apply_since_filter() method for shared polling timestamp logic across Db_resources and Db_tables controllers. (April 26, 2026)
- Db_api_base: _parse_query_params() now includes 'since' parameter in the returned array. (April 26, 2026)
- EndpointRegistryBuilder: GET list endpoints now document 'since' as an available query parameter. (April 26, 2026)
- my_routes.php: Added api/v1/auth/test route before wildcard patterns. (April 26, 2026)
- EndpointRegistryBuilder: GET list endpoints now document 'since' as an available query parameter. (April 27, 2026)
- Template_catalog_model: sync_catalog_templates() now updates existing system templates (tables_required, config_json, description) instead of skipping them, ensuring preset file changes propagate to the database on next page load. (April 27, 2026)

FIXED

- API Token Form: Fixed JavaScript SyntaxError ("Unexpected end of input") on /admin/data_builder/api_tokens/form caused by missing IIFE closure `})();`. Cascaded failures: toggleAllPerms undefined (Check All checkboxes), #tablePermFilter unresponsive, HMAC toggle non-functional. (April 26, 2026)
- ResourceNameResolver: Fixed payments resource mapping from non-existent 'tblpaymentsrecords' to the actual 'tblinvoicepaymentrecords', which caused 403/404 on GET /api/v1/payments. (April 26, 2026)
- AuthGateMiddleware: Moved raw_body caching (php://input) before AdminSessionBypass early-return. GraphQL endpoint received null raw_body under session bypass, causing "Empty Body" (400) on POST /api/v1/graphql. (April 26, 2026)
- Template Catalog: Fixed non-existent table reference tblstaff_tasks → tbltask_assigned in Monthly Tasks Completed by Project template. Fixed JOIN order (tbltask_assigned before tblstaff) to prevent "Unknown column" SQL error. (April 27, 2026)

Version 2.1.0 (April 26, 2026)

NEW

- Outbound Webhook Dispatcher with Pluggable Channel Architecture. (April 4, 2026)
- WebhookEventBus: fires events on REST and GraphQL Create/Update/Delete operations. (April 4, 2026)
- ChannelRegistry: extensible adapter registry (built-in: HTTP/cURL with HMAC-SHA256). (April 4, 2026)
- FieldMapper: per-subscription field include/exclude/rename transformations. (April 4, 2026)
- Admin UI for managing webhook subscriptions, testing, and viewing delivery logs. (April 4, 2026)
- Webhook delivery audit log with response codes and timing. (April 4, 2026)
- Automatic sensitive column redaction on outbound payloads. (April 4, 2026)
- Hook `data_builder_register_webhook_channels` for third-party channel registration. (April 4, 2026)
- Support dynamic relative date macros in WHERE conditions: {{CURDATE}}, {{CURDATE-7}}, {{CURDATE-30}}, or custom {{CURDATE-n}}, {{CURMONTH-n}}, {{CURYEAR-n}}. (April 20, 2026)
- Add "Save as New" filter button in Runtime Data View for easier filter duplication. (April 20, 2026)
- Customizable Header/Footer branding ("Powered by...") on Public Chart pages via Settings. Defaults to Company Name and Main Domain. (April 20, 2026)
- Add "Set as Default Filter" checkbox when saving filters. The selected filter will be automatically applied when loading the report in both Admin and Public views. (April 20, 2026)
- Add Toggle Chart visibility button to both Admin and Public views for better focus when viewing large data tables. (April 20, 2026)
- Add "Set as Default Filter" checkbox when saving filters. The selected filter will be automatically applied when loading the report in both Admin and Public views. (April 21, 2026)
- Add Toggle Chart visibility button to both Admin and Public views for better focus when viewing large data tables. (April 21, 2026)
- New report template: "Daily Sales by Invoice Item" — one-click report setup to track daily sales breakdown by item description, with client name, sales agent, payment mode, and line-item amounts. (April 21, 2026)
- New report template: "Daily Invoice Payment Records" — one-click report setup to view today's payment collections with client name, invoice number, sales agent, payment mode, and amount. Auto-filtered by current date with grand total. (April 21, 2026)
- Add "Minute" and "Hourly" options to the Chart Period selector, allowing detailed intraday data visualization for reports like Invoice Payment Records. (April 21, 2026)
- Add new seasonal report templates for comprehensive sales breakdown: "Month Sales by Invoice Item", "Q1/Q2/Q3/Q4 Sales by Invoice Item", and "H1/H2 Sales by Invoice Item". (April 21, 2026)
- Extends dynamic date macros to support strict range matches for: {{CURYEAR_Q1}} through {{CURYEAR_Q4}}, {{CURYEAR_H1}}, and {{CURYEAR_H2}}. For other timeframes, custom WHERE conditions can be used to filter desired periods. (April 21, 2026)

CHANGED

- Improve Template Groups layout to intuitively display templates in a nested format. (April 20, 2026)
- Support deleting custom templates. (April 20, 2026)
- Support drag-to-zoom and drag-to-pan interactions for charts. (April 20, 2026)
- Optimize Public Chart page layout for better chart visibility. (April 20, 2026)
- Remove hardcoded ORDER BY / Sorting constraints when creating reports, allowing dynamic datatable sorting. (April 20, 2026)
- Secured embedded public filters by restricting frontend payloads to pre-saved Server-side Filter IDs, preventing arbitrary query modifications. (April 20, 2026)
- Secured embedded public filters by restricting frontend payloads to pre-saved Server-side Filter IDs, preventing arbitrary query modifications. (April 21, 2026)
- Whitelist error dialogs (Preview Data / Preview SQL) now render translated instruction text as clickable HTML with direct link to Settings page, instead of stripping HTML and showing plain text. (April 21, 2026)
- Enhanced join engine to support Invoice Items and Expense-linked Invoices for flexible custom report building via the Visual Builder. (April 21, 2026)
- Template whitelist validation: when loading a preset template, the builder now checks if all required tables are available. If any are missing, a warning dialog lists them with a direct link to Settings. (April 21, 2026)

FIXED

- Fix data table synchronization issues between Admin reports and Public Charts (e.g. missing column totals). (April 20, 2026)
- Fix missing currency symbols in the Public Chart data table. (April 20, 2026)
- Fix whitelist instruction not displaying properly in error dialogs when using non-whitelisted tables. (April 21, 2026)

Version 2.0.1 (April 1, 2026)

NEW

- Comprehensive "Testing with Postman" guide in the Public API Docs. (April 1, 2026)
- Intelligent sample data formatting for Postman Collection exports, automatically recognizing column types for realistic Request Bodies. (April 1, 2026)
- Dynamic GraphQL Template generator built into the API Explorer and Public Docs, supporting automatic query and mutation creation to simplify integration development. (April 1, 2026)
- "Testing with OpenAPI" documentation section with download link, Swagger UI / Insomnia import guides, and step-by-step ChatGPT Custom GPT Actions integration. (April 1, 2026)
- Real-world use case gallery for AI-powered CRM analysis (data analysis, project status, client insights, unpaid invoices, revenue reports, AI reports). (April 1, 2026)
- Multi-language HMAC code samples (PHP, Node.js, Python, cURL) with copy-to-clipboard in the Authentication section. (April 1, 2026)
- Automated HMAC pre-request script embedded in both Postman Collection exports (Public Docs + API Explorer) with `hmac_secret` collection variable. (April 1, 2026)
- Integrated "Toggle All" functionality for managing Allowed Tables. (April 1, 2026)
- Implemented per-token HMAC signature toggles for granular security control. (April 1, 2026)

FIXED

- Resolved blank URL issues in Postman Collection v2.1 export by structuring URLs with `{{baseUrl}}` integration. (April 1, 2026)
- Query parameters (page, per_page, sort, fields, filter) now correctly display in the Postman Params tab as optional inputs. (April 1, 2026)
- Fixed OpenAPI YAML export producing invalid `responses` format (sequences instead of status-code mappings) caused by PHP `array_merge()` re-indexing numeric-string keys; replaced with `+` operator. (April 1, 2026)

Version 2.0.0 (March 30, 2026)

NEW

- Public blank chart view for embedding via iframe. (March 20, 2026)
- Report groups: organize independent reports into tabbed navigation. (March 20, 2026)
- Custom filter presets per report view for conditional data display. (March 20, 2026)
- Column-level aggregation row (sum, avg, count) at report table footer. (March 20, 2026)
- Visual charts with date comparison (daily/weekly/monthly/quarterly/yearly). Supported types: Bar, Line, Area, Doughnut, Radar, Bubble, Combo (Bar+Line), Pie, Scatter, Stepped Line, Point-styling, Interpolation. (March 20, 2026)
- REST API: versioned read-only endpoints (/v1/) for querying Perfex CRM tables and Data Builder views; supports filtering, sorting, field selection, and cursor pagination with HATEOAS links. (March 20, 2026)
- GraphQL API: single-endpoint querying with query depth and complexity controls; auto-generated schema per table and view; introspection restricted to admin explorer context. (March 22, 2026)
- GraphQL Mutations: full CRUD via GraphQL (create, update, delete) with auto-generated InputObjectTypes, primary key detection, write scope enforcement, and security column exclusions. Compliant with GraphQL Spec §6.3.1. (March 22, 2026)
- API Tokens: scoped access keys (tables/views/GraphQL); IP allowlist (CIDR), domain allowlist, per-token rate limiting, optional HMAC request signing, and configurable expiry. (March 20, 2026)
- 7-stage security middleware pipeline (DDoS shield, CORS, Admin Session Bypass, auth, rate limiter, scope verifier, request logger); all errors returned as RFC 9457 Problem Details. (March 20, 2026)
- Admin Session Bypass (middleware): allows authenticated Perfex admins to call the API from within the same site (API Explorer sandbox) without a Bearer token. Governed by a configurable setting (data_builder_api_admin_session_bypass); verifies admin session + same-site Origin/Referer/XHR dynamically via base_url() — no hardcoded values. (March 22, 2026)
- API Explorer: endpoint browser, live sandbox, code generator (cURL/JS/PHP/Python), Postman v2.1 and OpenAPI 3.0 export. (March 20, 2026)
- Public blank chart view for embedding via iframe. (March 14, 2026)
- Report Export / Import JSON: portable report definition workflow for backup, sharing, and migration between environments. (March 27, 2026)
- Report Templates for no-SQL users: pre-built templates plus "Save as Template" to speed up report creation without SQL. (March 27, 2026)
- Template Group management: organize templates by business domains; useful when systems include extra tables from third-party modules. (March 27, 2026)

CHANGED

- Chart.js upgraded to 4.5.0, locally integrated. (March 15, 2026)
- Data Builder settings moved to Perfex core (Setup -> Settings -> Data Builder). (March 18, 2026)

Search Knowledge Base Articles

Enim exercitationem quia cupiditate.