How to Query and Centralize Scattered Notes in Perfex CRM

If you are running a fast-growing digital agency, consulting firm, or service-based enterprise, client documentation is the lifeblood of your day-to-day work. As your customer base grows, keeping track of internal communications, client feedback, call logs, and project updates becomes increasingly difficult.

In our development agency, we frequently build custom integrations where client-facing marketing sites are hosted on highly optimized WordPress environments, while the heavy lifting of lead scoring, billing, and project tracking is handled by a self-hosted instance of Perfex CRM. This hybrid setup works exceptionally well, but it introduces interesting workflow challenges—particularly when it comes to internal data visibility.

Perfex CRM handles internal documentation via its native "Notes" feature. You can add notes to leads, customers, projects, tasks, estimates, and invoices.

However, a major operational friction point is that these notes are completely scattered across the system.

There is no central feed. If an agency manager wants to see all call logs or client updates entered by their support staff today, they must manually click through dozens of separate leads, projects, and tasks.

This is where a specialized utility like the Notes List Viewer Module for Perfex CRM becomes a valuable addition to an operational stack. This tool aggregates all polymorphic note records from across your database into a single, interactive, searchable dashboard.

In this developer-grade guide, we will analyze the database architecture of Perfex’s note-tracking tables, write highly optimized SQL queries to build a centralized feed, implement permissions-based filtering, and walk through a complete code security audit before deploying third-party extensions on your production servers.

Section 1: The Underlying Database Architecture of Perfex Notes

To build a centralized notes viewer, we must first understand how Perfex CRM models notes in its database. Unlike modern document management platforms that use structured, complex JSON document stores, Perfex stores notes inside a single, normalized relational table called tblnotes.

This design relies on a Polymorphic Relation pattern. Instead of having separate tables for lead notes, project notes, and customer notes, all notes are funneled into a single table. The application uses a combination of two columns (rel_id and rel_type) to dynamically link a note record to its parent entity.

Let us inspect the structure of the tblnotes table in MySQL:

CREATE TABLE tblnotes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  rel_id INT NOT NULL,
  rel_type VARCHAR(20) NOT NULL,
  description TEXT NOT NULL,
  dateadded DATETIME NOT NULL,
  addedfrom INT NOT NULL,
  KEY idx_rel_relation (rel_id, rel_type),
  KEY idx_dateadded (dateadded),
  KEY idx_addedfrom (addedfrom)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Understanding the Polymorphic Target Keys

The rel_type column acts as a routing string that tells Perfex's core framework which parent table to query for the parent entity's metadata:

The Performance Bottleneck of Centralized Note Queries

If you want to build a unified feed showing the Note ID, Note Content, Creation Date, Author Name, and the Name of the Parent Entity, your database has to perform multiple dynamic joins.

A naive query pattern using nested dependent subqueries looks like this:

SELECT 
    n.id AS note_id,
    n.description AS note_content,
    n.dateadded,
    CONCAT(s.firstname, ' ', s.lastname) AS author_name,
    n.rel_type,
    n.rel_id,
    CASE 
        WHEN n.rel_type = 'lead' THEN (SELECT name FROM tblleads WHERE id = n.rel_id)
        WHEN n.rel_type = 'project' THEN (SELECT name FROM tblprojects WHERE id = n.rel_id)
        WHEN n.rel_type = 'customer' THEN (SELECT company FROM tblclients WHERE userid = n.rel_id)
        WHEN n.rel_type = 'task' THEN (SELECT name FROM tbltasks WHERE id = n.rel_id)
        ELSE 'System Entity'
    END AS parent_entity_name
FROM tblnotes n
LEFT JOIN tblstaff s ON s.staffid = n.addedfrom
ORDER BY n.dateadded DESC
LIMIT 50;

While this query works, it is computationally expensive. Because it uses a CASE statement containing subqueries, MySQL is forced to execute an additional sub-select query for every single row returned in the main dataset. If your agency database contains 100,000 notes, running this search during peak operational hours will trigger significant disk I/O bottlenecks and drive up database CPU utilization.

Section 2: Implementing a High-Performance SQL Query and PHP Model

To optimize this query, we should replace the dependent subqueries inside our CASE block with explicit LEFT JOIN operations. This allows the MySQL query optimizer to resolve all entity names inside a single index-optimized scan.

Let us write a professional, optimized PHP Model class using CodeIgniter’s Query Builder pattern to fetch a centralized notes list safely:

db->select("
            n.id AS note_id,
            n.description AS note_content,
            n.dateadded,
            n.rel_id,
            n.rel_type,
            CONCAT(s.firstname, ' ', s.lastname) AS author_name,
            s.profile_image AS author_avatar,
            COALESCE(l.name, p.name, c.company, t.name, i.number, '') AS parent_title
        ", false);

    $this->db->from('tblnotes n');
    $this->db->join('tblstaff s', 's.staffid = n.addedfrom', 'left');

    // Explicitly join all possible parent tables
    $this->db->join('tblleads l', "l.id = n.rel_id AND n.rel_type = 'lead'", 'left');
    $this->db->join('tblprojects p', "p.id = n.rel_id AND n.rel_type = 'project'", 'left');
    $this->db->join('tblclients c', "c.userid = n.rel_id AND n.rel_type = 'customer'", 'left');
    $this->db->join('tbltasks t', "t.id = n.rel_id AND n.rel_type = 'task'", 'left');
    $this->db->join('tblinvoices i', "i.id = n.rel_id AND n.rel_type = 'invoice'", 'left');

    // Apply textual searching if provided
    if (!empty($search_query)) {
        // Protect query using built-in input escaping
        $this->db->group_start();
        $this->db->like('n.description', $search_query);
        $this->db->or_like('s.firstname', $search_query);
        $this->db->or_like('s.lastname', $search_query);
        $this->db->group_end();
    }

    $this->db->order_by('n.dateadded', 'DESC');
    $this->db->limit($limit, $offset);

    $query = $this->db->get();
    return $query->result_array();
}


}

Why This Query Approach Scales Much Better

• Hash-Join and Index Merging: By specifying join conditions directly in the ON clause (e.g. l.id = n.rel_id AND n.rel_type = 'lead'), MySQL can match rows using our composite index (rel_id, rel_type) without performing full scans of the parent tables.
• COALESCE Coalescing: The COALESCE() function returns the first non-null value it encounters across the joined tables. This lets us merge lead names, project titles, company titles, and invoice numbers into a single display column (parent_title) at the database layer, keeping our PHP arrays clean and light.
• Safe Pagination: The limit and offset parameters ensure that the application only retrieves the exact number of rows displayed on the screen, keeping memory usage low even on databases with millions of notes.

Section 3: Implementing Permissions-Based Row Filtering

Data visibility is a critical compliance issue for any agency. Sales agents should not have access to invoice notes, and customer support representatives should not see confidential project notes.

If your centralized Notes List Viewer Module for Perfex CRM simply dumps all notes into a single list, it bypasses your CRM's core security boundaries. To prevent unauthorized data access, we must filter our database queries based on the active user’s staff roles and permissions.

Let us write a robust PHP method inside our module controller to dynamically apply these permission filters:

load->model('notes_viewer_model');
    }

/**
 * Determines which entity types the active staff member is allowed to view.
 *
 * @return array Authorized rel_type strings
 */
private function get_authorized_entity_types() {
    $authorized_types = ['task']; // Tasks are generally open to all staff members

    // Check for lead viewing privileges
    if (has_permission('leads', '', 'view')) {
        $authorized_types[] = 'lead';
    }

    // Check for customer record viewing privileges
    if (has_permission('customers', '', 'view')) {
        $authorized_types[] = 'customer';
    }

    // Check for project viewing privileges
    if (has_permission('projects', '', 'view')) {
        $authorized_types[] = 'project';
    }

    // Check for financial viewing privileges
    if (has_permission('invoices', '', 'view')) {
        $authorized_types[] = 'invoice';
    }

    return $authorized_types;
}

/**
 * Secure controller endpoint that serves the filtered notes feed.
 */
public function get_feed() {
    if ($this->input->is_ajax_request()) {
        $limit  = (int)$this->input->get('limit', true);
        $offset = (int)$this->input->get('offset', true);
        $search = $this->input->get('search', true);

        // Default safe boundaries
        $limit  = ($limit > 0 && $limit <= 100) ? $limit : 30;
        $offset = ($offset >= 0) ? $offset : 0;
        $search = trim(strip_tags($search));

        $authorized_types = $this->get_authorized_entity_types();

        if (empty($authorized_types)) {
            echo json_encode([
                'success' => true,
                'notes'   => [],
                'message' => 'No authorized entities found.'
            ]);
            die();
        }

        // Fetch records using explicit permissions
        $this->db->where_in('n.rel_type', $authorized_types);
        $notes = $this->notes_viewer_model->get_centralized_notes($limit, $offset, $search);

        // Dynamically format dates and return results
        foreach ($notes as &$note) {
            $note['dateadded_formatted'] = time_ago($note['dateadded']);
            $note['entity_url']          = $this->generate_entity_url($note['rel_type'], $note['rel_id']);
        }

        echo json_encode([
            'success' => true,
            'notes'   => $notes
        ]);
        die();
    }
}

/**
 * Generates the appropriate admin dashboard link for a given entity type.
 */
private function generate_entity_url($type, $id) {
    switch ($type) {
        case 'lead':
            return admin_url('leads/index/' . $id);
        case 'project':
            return admin_url('projects/view/' . $id);
        case 'customer':
            return admin_url('clients/client/' . $id);
        case 'task':
            return admin_url('tasks/view/' . $id);
        case 'invoice':
            return admin_url('invoices/list_invoices/' . $id);
        default:
            return '#';
    }
}


}

Why This Controller Design is Secure

• Explicit where_in Enforcements: By querying the active staff member’s permissions using has_permission() and packing allowed strings into an array, our query completely blocks unauthorized table row visibility at the database layer. Even if a user attempts to intercept and manipulate API queries, they cannot view records outside of their assigned permissions.
• Input Sanitization: We explicitly cast pagination variables using (int) and sanitize our string inputs using strip_tags() and trim(). This completely removes the risk of cross-site scripting (XSS) injections through the AJAX query engine.
• Polymorphic URL Routing: The controller automatically generates the appropriate internal link for each note based on its rel_type, allowing managers to immediately click through and view the full context of any parent record.

Section 4: Security Auditing and Safe Customization Practices

As a white-hat security specialist, I must advise you to never upload any newly obtained module directly onto your production CRM database without first running it through a comprehensive local security check.

In our local staging workspace, we set up test sites and run initial module code reviews with GPL-licensed tools or plugins sourced from GPLPAL to analyze performance footprints and framework overrides [1]. This ensures that our primary databases remain protected against security risks, database locking, or unauthorized administrative escalation vulnerabilities.

                      MODULE SECURITY AUDITING STEPS

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│ Code Scanning   │────►│ Check for Raw   │────►│ Sandbox         │
│ (YARA / Grep)   │     │ SQL Queries     │     │ Performance Test│
└─────────────────┘     └─────────────────┘     └─────────────────┘
                                                         │
                                                         ▼
┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│ Push Clean Code │◄────│ Verify Database │◄────│ Apply Granular  │
│ to Production   │     │ Index Matching  │     │ Staff Filters   │
└─────────────────┘     └─────────────────┘     └─────────────────┘

1. Checking for Backdoors and Obfuscated Code blocks

Some custom integrations or modified modules downloaded from untrusted community hubs contain backdoors designed to let malicious users upload shells or execute server commands.

Run a quick audit of your module files inside your local terminal to scan for typical security risks:

# Scan for base64 dynamic evaluation wrappers
grep -rnw './notes_viewer' -e 'base64_decode'

# Scan for dynamic evaluation strings
grep -rnw './notes_viewer' -e 'eval('

# Scan for system call functions
grep -rnw './notes_viewer' -e 'shell_exec('
grep -rnw './notes_viewer' -e 'system('

If these scans return any results, examine the file paths carefully. While some external vendor dependencies (such as PDF generation engines or chart rendering systems) may legitimately use base64 strings to display static icons or process binary vectors, any instance of nested functions like eval(base64_decode(...)) is a major security risk and should never be deployed to a live server.

2. Auditing for Database Query Safeties

Ensure your module uses parameterized inputs instead of raw string concatenations. This is particularly important for search parameters and filter options passed from client-side UI dashboards.

• Unsafe Database Construction: $this->db->query("SELECT * FROM tblnotes WHERE description LIKE '%" . $_GET['search'] . "%'");
• Safe Database Construction: $this->db->like('description', $search_string);

3. Comparing Modular Architectures with Industry Standards

To maintain high codebase health across all your systems, look to standard practices found in other popular open-source platforms. For example, when developers write activity tracking plugins or custom logging queries for multisite setups on WordPress.org, they must carefully map parent and child relationships while escaping outputs to prevent database injection attacks. Applying these same best practices to your Perfex CRM modules ensures your entire application stack remains highly secure and consistent.

Section 5: Step-by-Step Installation & Integration Guide

If you have completed your code reviews and are ready to deploy your central notes manager, follow this step-by-step deployment guide:

Step 1: Upload the Module Archive

Log in to your server using SFTP, or use your hosting control panel file manager. Upload the extracted module directory directly to your Perfex CRM installations directory:

/modules/

The resulting folder path must look like:

/modules/notes_viewer/

Step 2: Configure Safe File Permissions

To prevent unauthorized scripts on the server from modifying your module's code, apply strict file ownership and execution permissions:

# Set folder owner to the active webserver user (usually www-data)
sudo chown -R www-data:www-data /var/www/perfex/modules/notes_viewer

# Secure all PHP file execution rights
find /var/www/perfex/modules/notes_viewer -type f -exec chmod 644 {} \;
find /var/www/perfex/modules/notes_viewer -type d -exec chmod 755 {} \;

Step 3: Verify Composite Database Indexing

To ensure your centralized database queries complete quickly as your notes table grows, check that your MySQL database contains appropriate composite indexes.

Log in to your MySQL terminal and run this query to view active indexes on your tblnotes table:

SHOW INDEX FROM tblnotes;

If your output does not show a composite key containing both rel_id and rel_type, execute this command to create the missing index:

ALTER TABLE tblnotes ADD INDEX idx_rel_relation (rel_id, rel_type);

Applying this composite index ensures your database can quickly filter note records by their target entity and type, avoiding slow table scans on production environments.

Step 4: Activate via Perfex Dashboard

1. Log in to your Perfex CRM Admin Panel as an Administrator.
2. Navigate to Setup -> Modules.
3. Locate Notes List Viewer in the modules table.
4. Click the Activate button.
5. Once activated, the module will dynamically inject a "Notes Feed" menu item into your left-hand administrative navigation sidebar.

Section 6: Operational Implementation Matrix (Build vs. Buy)

Before dedicating your development team to coding a custom centralized notes interface, use this operational matrix to compare building a custom tool in-house versus integrating a pre-built commercial solution:

For highly specialized development agencies, writing a custom query engine or model is a fantastic coding exercise. However, for active agency operations managers, time is your most valuable asset. Spending valuable development hours coding a common interface widget from scratch—when you can easily install a pre-built, highly secure module in under 30 minutes—is an unnecessary distraction. Your team should focus on what drives growth: acquiring leads, optimizing client onboarding, and delivering exceptional client results.

Technical Takeaways and Final Recommendations

Centralizing scattered administrative details is one of the easiest ways to improve your team's operational efficiency and keep everyone aligned. By consolidating scattered database tables into a single, secure feed, you save your staff from wasting hours clicking through separate screens and tabs.

If you choose the custom development path: Always use database indexing on composite parameters like (rel_id, rel_type) to keep your system fast. Leverage CodeIgniter's query builder to protect your inputs from SQL injection vulnerabilities. Filter your query results based on staff permissions to keep sensitive client data secure.

If you choose to buy a pre-built module: Deploy a staging environment to run static code audits on all new module files. Audit any files downloaded from developer forums or GPLPAL using local static scanning tools [1]. Apply composite database indexes to keep your system running smoothly as your database grows.

Investing a few hours into setting up clean, secure database queries and establishing reliable code verification practices will ensure your CRM platform continues running at peak efficiency, giving your team the exact business intelligence they need to confidently scale operations.