SponsorWare Model

My SuiteCRM SponsorWare Add-Ons

Power SuiteCRM Add-ons I created, available to my Sponsors on GitHub

Basic Tier

  • PowerWorkflows - adds actions buttons on views, saves your user’s time by improving processes - your way.

Pro Tier

  • PowerReplacer - supercharges Email Templates, PDF Templates and Campaigns. Make them dynamic and preview them before they go.
  • PowerFields - advanced formulas (calculated fields) everywhere. Can be typed directly or set as defaults from Studio.

Why? How? What for?

Read my explanation of this SponsorWare Model I’m trying, how it can work for me, for SuiteCRM, and for you!

Latest Blog Articles:

SuiteCRM Workflows Explained

SuiteCRM Workflows are powerful, but some of the available options can be confusing for new users. I’ll try and breakdown some of the elements that determine when and how a workflow runs.

Read More

How are Contact Photos Stored?

When uploading a ton of images to match each Contact you have, it’s useful to avoid doing it from the user interface, and go directly into the file system and database.

The Contacts Photo field

Some people don’t even notice this exists - but it is actually quite useful for many purposes. You can go into Studio and make a Photo field visible for the Contacts module. Enable it on the Detail View, the Edit View, and any List views and Contacts Subpanel (in other modules’ views) you find relevant.

If your business has photos of its people, your users will simply love this. Scrolling through a list view with little thumbnails of people’s photos makes everything more human and intuitive.

In the database

The database stores a very simple bit of information in the contacts table when you upload a Contact’s photo: the photo field containing the name of the file you originally provided, like johndoe.jpg.

Note that this is not the name of how the file is stored within SuiteCRM - it is just what you provided, in case it’s useful to you. It shows in the edit view, and you can click the Browse button near it, to select a new photo from your local operating system.

In the file system

Essentially, the photos go in the upload folder, with a name formed by the Contact’s database id and the suffix _photo. No file extension is kept.

So, suppose a contact has an id of 72c555b3-0ead-d66c-4c2a-57549a2e130b, as seen on the contacts table.

With that id, any photo in the upload folder named 72c555b3-0ead-d66c-4c2a-57549a2e130b_photo will get picked up and displayed as the contact’s photo.

Tip: you can also easily grab the id from the browser, when in the detail view of that contact; it’s the final code in the URL, after record=, or after record%3D (example:

Read More

Size of Tables and Indexes in Database

SuiteCRM’s Database can grow to a considerable size, depending on your use of the system. Having lots of data can be a good thing, but sometimes while investigating some error or performance problem, you will find an overgrown table. This post helps you track down those issues.

Be careful when handling data directly in the database. A single badly written DELETE query can ruin your database. Be sure to Backup first and do things at your own risk.

A view of table and index sizes

Here’s a fancy query I picked up from somewhere on the web to get a view of your database sizes:

SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 3), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 3), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 3), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 3), 'G') total_size,
       ROUND(index_length / data_length, 3)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

You can run this query from phpMyAdmin or MySQL, or the equivalent for your database.

Don’t jump into conclusions when seeing the results. You need to think whether the numbers make sense for you, knowing your data and your company’s activity. If something stands out as unjustified, then you might have found a problem.

Deleted records

SuiteCRM doesn’t really delete records when you delete them from the user interface. It simply marks them by setting a field called deleted to 1. Then all the other queries in the app ignore these records, so they don’t appear anywhere in the app. But if you really need to recover them, you can do so by accessing the database directly and setting deleted back to 0.

If you have too many of those, normally you can safely purge them from the database. There is a Scheduler Job that does that maintenance, it’s called Prune Database on 1st of Month. Note that it is not enabled by default, you have to enable it deliberately, after considering the pros and cons of keeping deleted records in your case. It’s your call after considering what kind of usage you have, and what kind (and frequency) of backups you have.

Job queue left-overs

If you see a very large job_queue table, you might want to purge it. I’ve used this query to look at old job_queue entries:

SELECT * FROM `job_queue` where status= 'done' and date_modified > date_sub(now(), interval 1 month)

Workflow left-overs

Other tables that frequently need manual pruning are aow_processed and aow_processed_aow_actions. These are related to Workflows and depend on your use of repeated runs. See this discussion for some interesting comments. Be careful and backup before touching this. But if you find 14 million rows here, like a user once reported in the forums, you know what to work on.

Slow queries

To check if an app performance problem is a database performance problem, you can go into Admin/ System Settings and turn on the Log slow queries option. Set the Slow query time threshold (msec) parameter to configure how long a query has to take to be logged.

Then use the app for a while and check the logs to see which queries are taking longest. A really unusual delay could be a sign of database corruption, index corruption, the need for a new or better index, even disk problems. But this logging is a good start to hunt down your problem.

Orphan records in relationships

Finally, another kind of orphan data is records of relationships where one side of the relationship no longer exists. Often this is not wrong at all (when you delete a Contact there is no reason to delete an associated Account), but other times there is nothing more to store after the record is deleted.

These orphan records can be many in tables like securitygroups_records (you don’t need security descriptors of records you no longer keep). I once had a SuiteCRM installation with tons of orphan records caused by my own import process. Successive cycles of import/delete/import left many loose ends in the database.

You can hunt down these rows with clever SQL.

Read More

Reindexing AOD Search

SuiteCRM relies on an indexing service called Lucene to handle the Advanced Search (accessible from every screen, on the top right corner). Sometimes this can stop working and a full-reindexing might be needed to get it working again.

Lock files

Under directory modules/AOD_Index/Index/Index, lock files (with extension .lock are safe to delete if their timestamps are old. They are left-overs from a process that might be stalled for days or weeks. Sometimes this can be enough.

Provoking a full reindexing process

Please do a full backup of the affected directories and tables before running these procedures.

Try to make steps 1 and 2 in quick succession so that no indexing cron jobs run betweem them (or if you prefer, disable cron jobs temporarily).

  1. From your SuiteCRM root, rename the Index folder (later, you can delete it):

mv modules/AOD_Index/Index/Index modules/AOD_Index/Index/Index.BACKUP

If you’re using a different command (like rm), note that you must delete also that final Index directory, not just the files within it.

  1. In your database, backup (by exporting) and then delete all rows from SQL tables aod_index and aod_indexevent. Don’t delete the tables themselves, just the rows in them.

  2. In SuiteCRM Schedulers, make sure that “Optimise AOD Index” and “Perform Lucene Index” are running at their appointed times.

  3. If you want to track the reindexing (may take a LONG time) you can use this neat command:

watch du -h modules/AOD_Index/Index/Index

This shows the directory size. As the indexer progresses, it seems to create and delete many temporary files, so the size will both increase and decrease, that’s normal. Of course eventually the final size will be bigger than the initial.

Read More

Bringing a Git branch up-to-date with its origin - an atomic hack

So, you want to contribute some code from your repo to the original project? But Github is showing other commits you don’t want to include, and you don’t know how to remove them…

Method #1 - do things properly

Learn Git, understand the project’s workflow, set up your local repositories correctly, set up the remotes, issue the correct git commands. This method is not the subject of this post… :-)

Method #2 - the ugly, but practical, hack

“But I just want it to let me do a simple PR! I don’t have time to learn all that…“

Ok, so here’s a nice little atomic procedure for you. Atomic doesn’t mean it explodes (hopefully), it means it’s self-contained, does its job without any previous set up, and leaves no local traces behind it. It’s fire and forget.

What this does is create a new directory, start a local clone, set everything up, bring a branch on your forked repo up to date with the origin repo, and delete everything local in the end. After that, you can easily and safely do your PR.

Git Origin and Upstream

Image taken from here, then edited by me.

See, when you’re on the online GitHub site, you only care about the top part of this scheme. You don’t want any work setting up the lower block, your local install, and learning about Push and Pull, when all you need to do is bring a branch up to date with SalesAgility (origin with upstream). So what the script does is create the lower part automatically, do it for you, and remove it afterwards.

So, here’s the script, complete with reassuring comments:


# These point to your github forked repo of SuiteCRM:


echo -------
echo "This script will reset a branch on a SuiteCRM fork to look exactly like the official repo."
echo "It will delete any work you have on that branch in your fork, but you will want to use this on branches like"
echo "hotfix and develop, where you shouldn't be doing your work anyway, so it should be safe."
echo "It won't delete anything on SalesAgility's repo, because you don't have the rights to push there..."
echo -------
echo 'What is the branch you want to reset (usually hotfix)? '
read theBranch

echo "Press any key to continue reset of branch $theBranch on $user/$fork"
read -rsp $'...\n' -n 1 key

mkdir TempRepo
cd TempRepo
git init
git remote add origin https://github.com/$user/$fork.git
git remote add upstream https://github.com/salesagility/SuiteCRM.git
git fetch upstream
git checkout $theBranch
git push origin $theBranch --force
cd ..
rm -R TempRepo

Make sure you edit those two variables at the beginning (user and fork), to fill them with appropriate values referencing your Github repo.

Of course, with a proper git set up you would avoid the long delay of fetching the entire project every time.

This is for Linux but it works great in Windows 10 if you install the Linux Bash Shell from Microsoft.

Read More

Audit file accesses to track your customizations

So, you need to know which file SuiteCRM is trying to load from where? Or why your customization file isn’t getting picked up? Or what files Studio has changed? Here’s what I would call an advanced debugging/troubleshooting technique.

Apart from anything you can find from the logs, sometimes you just wish you could see exactly which disk accesses were being made. Well, you can.

In Windows, this is done with a nice little app called Process Monitor (part of the brilliant SysInternals Suite you can download from Microsoft). But that is not the object of this post, here I will be discussing only the Linux solution: auditctl.

auditctl will let you monitor specific folders and see exactly what SuiteCRM is trying to read/write, and whether it’s succeding or not, and whether it’s failing due to permissions problems.

To install it on Ubuntu, use this (or something similar for your flavour of Linux):

sudo apt-get install auditd audispd-plugins

If you need to find your config file, to edit the log location, try this

find / -name auditd.conf 2>/dev/null

In my case, I edit it at this location:

nano /etc/audit/auditd.conf

Now you need to add rules saying what you want to monitor. Try to make them as restrictive as possible, there can be a lot of things going on in a file system… I suggest something like this (adapt to your paths, of course):

auditctl -a exit,always -F dir=/var/www/html/custom -F perm=rwa

auditctl -a exit,always -F dir=/var/www/html/modules -F perm=rwa

To list currently active rules use this command:

auditctl -l

Now you can cat or tail your log at the moment when you’re accessing the screens, or when you’re changing something in studio, or when you’re doing a Rebuild, to understand what’s going on. Here are a few examples of commands you can use to focus on a file, or on a folder:

cat /var/log/audit/audit.log | grep Line_Items.php

tail -f /var/log/audit/audit.log | grep -i 'custom/modules/AOS_Products_Quotes'

tail -f /var/log/audit/audit.log | grep -i 'Invoices\|Products\|Quotes'

That third example is an OR, so grep will match any of those three words (very useful!).

Note that there are performance issues associated with this technique. It should not be used on Production servers, or at least you should turn it off when you’re not needing it anymore.

To delete ALL rules, when you’re finished, so that performance isn’t hurt in the future:

auditctl -D

With this I believe you can see “under the hood” what SuiteCRM is trying to load, including directories it is searching, before deciding to load a file. You can also detect if the problem is caused by insufficient permissions. This is a great way to get a grip on what’s wrong with some file you’re trying to customize.

I even use it to learn how Studio works: I “watch” it doing the changes when I create a relationship, for example, and then check the files it touched to further customize or understand. It also lets me learn the various steps involved (Save, Deploy, Quick Repair and Rebuild).

Read More

How are Email addresses stored?

A look at how SuiteCRM stores Email addresses in the Database. This applies to all entities that have email addresses.

Currently this post covers only the simple topic of storing Email addresses. One day I plan to expand it to include how actual Email content is stored.

The several entities that can have Email addresses

In SuiteCRM, all of these entities can have Email addresses: Contacts, Leads, Targets, Users, Accounts, and perhaps more that I’m not remembering now.

Developers call entities beans because of the way they are internally represented and handled.

Database structure

There is a table handling the relationship between people and their email addresses called email_addr_bean_rel.

In there, the columns bean_id and bean_module specify the entity that has an email address. For example, if bean_module is Users, you can use bean_id to look the user up in the users table.

Then there is a column called email_address_id which is a reference to the id column in table email_addresses.

Example Query

Here is a sample query to extract a list of Users with their Email addresses:

SELECT users.user_name,
 FROM users
      LEFT JOIN email_addr_bean_rel
             ON email_addr_bean_rel.bean_id=users.id
            AND email_addr_bean_rel.bean_module = 'Users'
            AND email_addr_bean_rel.primary_address = 1
            AND email_addr_bean_rel.deleted = 0
      LEFT JOIN email_addresses
             ON email_addresses.id = email_addr_bean_rel.email_address_id
            AND email_addresses.deleted = 0

This can easily be adapted to the other entities like Contacts, Leads, etc.

Read More

SuiteCRM Concepts Explained: Accounts, Contacts and Sub-Accounts

Accounts and Contacts are very central concepts in SuiteCRM. Normally your data-modelling decisions start here - and there are a few subtleties you need to learn.

An Account is basicaly a representation of an impersonal entity, like a company, an institution, etc.

Then it articulates with the several representations of persons (Contacts, Leads, Targets). Of these, the main one is clearly Contacts, which is a person in full standing in your CRM: it is the personal entity that keeps more information, and that links to all other modules in the CRM, so that you can keep a view of the History of your relationship with that person. Customers will almost always be modelled as Contacts.

So, almost every system you design around SuiteCRM is going to make use of both of these concepts. Your main institutions will be modelled as Accounts, your main persons will be modelled as Contacts.

I don’t recommend starting with new entities to model companies or people. For example, you might think, my business deals with Artists, so I will go into Studio and create a module called Artists. Sure, you can do that, and you can even base it on the Contacts module so that most fields will already be there. But the default Contacts is so linked to everything in the CRM, that you will be better off using it directly. You can rename it to Artists if that’s really the only thing a contact will ever be in your company - but this is a superficial name change that keeps everything the same underneath. The new module would be more difficult to integrate. The same goes for the Accounts module.

An Account can have many Contacts

The most common relationship between these two central concepts is this: each Account can contain zero, one or many Contacts. These could be the many people you know inside an organization.

But you can also use an Account as an alternative way to group people. For example, you might create an Account called “Impressionists” and put all your impressionistic style Artists in it. You could - but I’m not recommending you do. A custom field called “Painting style” in the Contacts record might serve you better for these purposes.

But I gave the example just to show that sometimes the relationship doesn’t strictly have to be “people who work for a company”. It could be a system keeping data about students of a group of schools, and each student would be in an account for her own school.

So, how should you make a wise decision about whether to segment your people by a field, or by Accounts? Only you can say that, but as hints I would say: only use Accounts if it is a primary relationship in your data, an important aspect of your data; and if you don’t need to use Accounts for anything else; and if you need to use that information to differentiate security accesses (some users can only see or edit records from one Account, buy not the other).


There is a concept in SuiteCRM that allows making some Accounts part of others. This could be used for any case of entity-inside-entity, like for a central company that contains several other companies, or several branches, or locations, or departments.

To use this, go into the detail record of an Account and edit it. You will find a field there called Member of, with a selector for Accounts. What you choose there will make your current Account a member of the other Account, its Parent Account.

To see things from the other side of the relationship, if you go into a parent Account, you’ll see a subpanel called Member Organizations with a list of its sub-accounts. If you remove rows from this list, you will break the relationship (but the sub-account will only be unlinked, it won’t be deleted).

There can be several levels in this composition, so that an Account can be the parent of another Account, which in turn has other Accounts underneath it as Member organizations. So in theory you could represent a full organization chart of a large corporation with this.

And how does this affect the relationship with Contacts? Well, Contacts are still only linked to a single specific Account, and it can be at any level in the hierarchy. But with clever work under the hood, with some SQL you could have fancier operations like loading every contact of every sub-account into a Target List you can email, for example.

Where this is most useful is for security accesses. If you limit access to a certain Account, SuiteCRM will be smart enough to consider everything under that Account, including Sub-Accounts. This makes it easy to segment your data in high-level blocks, as well as with greater detail if you need to.

For example, in the system modelling several schools, you could have top-level Accounts for each school, then sub-accounts for each Class, in order to group students (modelled as Contacts) under these Accounts. In terms of security, a teacher from one of the schools (modelled as a User of your system) can be given access only to records from that school.

Exceptionally, a Contact can belong to several accounts

SuiteCRM is built to have only one Account attributed to a Contact. The user interface follows that assumption. However, there is a known gap in the system. You can add a second account to a user, making him effectively belong in both. You can also extend this to a third account, and more…

If you do this, the user interface will behave strangely. It will still only show one Account in a Contact’s record (I’m not sure if it shows the first one inserted, the last one inserted, or just a random pick). So you see a single Account linked, but if you delete it, instead of seeing an unlinked record, the second Account will magically appear in its place.

There might be good reasons to use this system, imperfect as it is. If you need to, you can work around it’s interface limitations in two ways:

  • handle the relationships directly from the database (you will find multiple rows in table accounts_contacts for a single contact)
  • if you don’t see the second Account from the Contacts detail record, you can still see the Contact in the Accounts record, as an item in a subpanel. From that line you can select “delete”. Remember deleting in subpanels doesn’t delete the full records, it only deletes the relationship between the record you are on, and that linked record.

Setting up Security

Although it’s a different topic, if you’re interested in modelling groups, subgroups and people, you will probably want to consider the different accesses you will be letting each group of users have. So a recommended reading is this: A Typical Hierarchy Setup.

If you have any further questions or comments to improve this article, please leave a comment below!

Read More

My SuiteCRM hardware and system architecture

I’m happy with my architecture and I think it’s a bit original, so I’d like to share it here. It’s basically an extremely unexpensive set up for a non-profit with a tight budget. But I would use it on a commercial company any day.

I run Microsoft Hyper-V Server as a virtualisation layer. This is the FREE server, without any GUI. It takes a bit getting used to but hey, it’s free. It has excellent hardware compatibility for cheaper hardware, unlike VMWare.

Inside that I run SuiteCRM over Ubuntu, latest version. Microsoft has been contributing directly to the kernel to improve Hyper-V compatibility, and it is now excellent (dynamic memory and all). Using SuiteCRM over Linux, it seems to me, gives less problems than over Windows.

I use Intel I7’s and SSD disks, but nothing too fancy. It’s not a large organization.

The main advantage of this set up is that I can get a virtualization feature that normally is reserved only for payed version, but on a free setup: that feature is replication. I can run a couple of Hyper-V servers and have them replicate the VM between them. Fail-over from one to the other is a breeze, really easy.

I also plan to do this for performance reasons: one server runs SuiteCRM database, the other runs SuiteCRM web server and app. Each replicates to the other. So in case of failure, I simply run both on the same server, with some performance penalty, of course, but the good thing is that I can make use of my replication hardware for performance on the good days, and for disaster recovery on the bad days.

Having replication (plus backups) is what enables me to drop the requirement for expensive hardware. I got this idea from Google, they realized that since they need to have several layers of disaster recovery they might as well use cheap hardware and just replace it as needed.

I have several layers of Backups. Some full VM backups (occasional, heavy, but most useful), some file copying from inside the VM (frequent, more lightweight). You should know that Replication is for fault-tolerance, Backups are for Data security and recovery. Different things, and you need both. If you have just replication, when you delete something by accident on one server, it gets replicated to the other, and you’re left with no option to get your data back unless you have proper Backups.

A replicated, virtualized, fault-tolerant, full-featured, performant CRM for around $ 1,500.

And then there’s my smaller Test system: a replicated, virtualized, copy of that running on even cheaper hardware, a couple of refurbished laptops with SSD’s and 16 GB RAM, totaling $ 600.

Read More

Why you should be running SuiteCRM in a VM

Here are some arguments in defense of the option to install SuiteCRM inside a Virtual Machine (VM), instead of on bare metal. That’s what I always do and I’m never turning back. These are basically the generic arguments in favor of virtualization, with some specific considerations regarding SuiteCRM.

  1. You get the perfect backups: entire machines, completely configured, ready to run. if you’ve ever tried rebuilding a server from simple data backups, only to start stumbling on all the configurations and system options you did throughout the years but can’t remember anymore… you know how much this is worth. Most (not very competent) sysadmins never even test their backups, and often discover they can’t really restore from them. None of this is a problem with full system backups.

  2. Snapshots: going to update your Linux packages? Or your SuiteCRM version? Or even some code you’re working on, or a crucial change in Studio that’s worrying you? Or some clean up operation in the database? All these things involve risk: but if could only take a snapshot of the full state of your system, and easily go back to that state in case anything goes wrong… well, you can with VM’s!

  3. Fault-tolerance: one simple disk fatal failure, or power-supply failure, and your company stops working - not a pleasant situation to be in, and not very professional either. If you get two servers, and replicate one from the other, if one goes down you just have to fail-over to the replica.

  4. Clones for prototyping and testing: similar to the snapshot option, but for larger scale tests. You copy the entire VM to another location, and test there. You can have a development machine that is an exact clone of your real server, with your real data on it. You can try Beta versions, or install Pull Requests that haven’t been merged into the main product yet. You can play with SuiteCRM without fear. You can bring your server with you on your laptop.

  5. Easy migrations and hardware changes. Need to change to a newer, better server? Just move the VM and start it. Have multiple VM’s in one server? You can move CPU’s and memory from one to the other at the click of a button.

  6. Migrate to the cloud, or at least be prepared for it: if you’re running a VM, you can also host that VM in a cloud service (Like AWS or Azure). If you’re not ready to do it today, then at least be ready to easily do it whenever you want. And be able to tell your management when they come to you with “cloud” hype buzzwords: “sure, it’s cloud-ready, I can move it up whenever we decide, should be easy”. This really solves that uneasiness they get when you try to convince them you don’t have to be on the Cloud, despite everything they read in business magazines. : - )

  7. Easy Scale-up: if your server is running slow with too much activity, despite having generous hardware, get a second server, clone your SuiteCRM system, stop MySQL on one server, and stop Apache on the other. Adjust the configuration file so that the one running the app uses the other one as a database. There, you’ve just split your server into two and it wasn’t a big complicated project…


SuiteCRM is not very demanding in terms of hardware, to start with. A simple system will run quite well on very modest hardware. Most of the people complaining about SuiteCRM’s performance eventually find out some other issue like the need to do some database maintenance or fix some bug. Of course if you start having many users and a lot of activity things might get more demanding.

It’s 2017, don’t use old spinning disks, use SSD’s, it really pays. Unless you already have a big, fancy, expensive, RAID or NAS system that’s really great to use instead.

For test and development machines, 3 Gb RAM will suffice, and about 10Gb for the disk, which is quite impressive.

Anything against?

Sure, some opposite arguments might also apply. These would be the generic reasons you can find online for why you prefer not to virtualize some server. For SuiteCRM I would say the only one I can find relevant is hardware performance, in case you have a very I/O intensive server and after careful consideration decide you can’t really virtualize without suffering some performance hit. But this is unlikely, and as a rule of thumb: if you have that kind of installation, you know what you’re doing, and you’re not here reading my introductory advice about virtualisation : - )

See My SuiteCRM hardware and system architecture for how I put all this together in real-life, with extra tips.

Read More

SuiteCRM Concepts Explained: Users, Employees, Portal Users

This is an area of SuiteCRM concepts which I find particularly confusing and clarification is required.

The terms Users and Employees overlap a lot in SuiteCRM, but they are not completely equivalent.

Both modules are a bit “special” modules. You access them from unusual places. You’ll find Employees in the menu on the top-right corner, and Users in the Admin menu, under User Management. Then you can also find them on the top menus, under Support, under All, but this also has a weird behaviour. You never see both options side-by-side at once, they seem to shift into one another, and you see Users when you are in the Users module, and Employees when you are in the Employees module. One wonders what “they” were thinking…


Basically the idea of Employees is to represent a person that works for your company. This lets you add a bunch of data for that person, but it’s simple “address book” stuff. Don’t expect any handling of work hours or of compensation payments (you could try and model that in other areas of the app if you really need to).

If you create an Employee in SuiteCRM he/she will also appear in the Users list.


The idea of User is to represent a person who can log in to your SuiteCRM system. Apart from the same kind of “Address book” info, you crucially include a user name and an email as a required field.

Then you have a few tabs with more information you can add/tweak: full email account settings, theme options, which modules the user can see, external apps to connect, etc.

And yes, if you create a User in SuiteCRM he/she will also appear in the Employees list!

Administrators are special users

There is a field for every user specifying the User type, which can be Regular user or Administrator. The system will not let you delete the last user who is an Administrator, to guarantee there is always at least one user with full access to configure the system.

The overlaps and differences

So, how can we make sense of all of this? The essential point is that normally the same people that work for you are the ones that you want logging in to your system…

  • all users are employees
  • not all employees are users

So how does SuiteCRM provide a way for you to express a person who is not your employee but that you want accessing your system? Well, there is a way, but very limited and different, which is the Portal User (see below).

On the other hand, an Employee who does not use the CRM is simple to achieve. It doesn’t do much, though, except save his name and address and other fields like these.

If you create an Employee you can later turn him into a User by going into the Users module and adding a username and password.

We could say Employee is a minor concept in SuiteCRM, while User is a big concept, with a lot of data and implications for security, record assignments, etc.

Portal Users

I won’t go into this so deeply, but it’s worth mentioning that you can activate the AOP Portal on Admin area, and then when viewing (not editing!) a Contact, you’ll get a button on the top to Create Portal User. This will let you give simple Contacts (not Employees, and not Users) an access to your system, but at a different URL, and for a very different experience. The functionality is like that of a support area, limited to opening Cases and checking their evolution. It can be programmed to handle more aspects of the app, of course.

Read More

How are Attachments stored?

At some point you might need to go under the hood and see how SuiteCRM stores Attachments in the database, and in the file system. So here’s a guide to help you out.

If haven’t read this already, start with a few non-obvious points about what Attachments are in SuiteCRM, by reading SuiteCRM Concepts Explained: Documents, Notes, Attachments

In the database

So Email, Campaigns and Case attachments all get stored as Notes in SuiteCRM.

The relevant table in the database is aptly called notes. It contains an id field, which is going to be important, and some additional fields specifying the file name and the file MIME type.

Each note record, unless it was created directly as a loose Note, will have a parent_type (for example Emails). This relates the attachment to the record to which it is connected. There is also a parent_id so you can put these two together and go into some other table (say Cases) and match that parent_id with the id on the other table to see the related record. Notice that there is no reverse link, you can’t find out from looking at a cases table which records have associated attachments or not.

In the file system

The actual file on your system will be in the upload directory, with a name equal to the id of the note record.

So, to see all the Email attachment files on your system, run this in MySql:

SELECT notes.id, emails.id, CONCAT("upload/", filename), file_mime_type FROM notes INNER JOIN emails ON notes.parent_type = 'Emails' AND notes.parent_id = emails.id INNER JOIN emails_text ON emails.id = emails_text.email_id

That filename shown by this query is the filename as seen by the user. You can pull other fields from the “Notes” and “Emails” tables, if you need (recipients, sender, date, etc.). You can also adapt that query to the other record types (cases, email templates, etc.)

The fact that every single file goes into the same directory is not very ordered and can actually lead into problems with the file system if you start having huge amounts of files. It would be nice for SuiteCRM to separate these into folders (by record type) in the future.

Read More

SuiteCRM Concepts Explained: Documents, Notes, Attachments

These SuiteCRM concepts seem to mix and overlap in unexpected manners, causing some initial confusion. This guide will help you sort everything out.

Documents are files that you upload into the system for later reference. The distinguishing feature of this concept is that it allows for Revisions, meaning SuiteCRM will keep all previous versions as you upload new ones, instead of replacing them. You can add some comments to each revision.

Attachments are also uplodaded files, but they can be, well, attached to either Emails or Cases or Notes. Even so, you shouldn’t conclude that Notes and Attachments are the same thing, because not all Notes contain (or are) Attachments. Some might just have a text (that shows directly on the Note), but not an attached file (that you can click to download and open locally on your computer). Attachments do not have the option to keep multiple revisions.

When attaching something to a Case or an Email, you get a chance of attaching both a Document and a File (Attachment). Not very intuitively, if it’s an Attached file it will get created as a Note in SuiteCRM. For example, the creation of an Attachment to a campaign, for example, will result in a Note being created with that Attachment.

When viewing a Contact or Account, you won’t see a subpanel for Notes, nor for Attachments. What you do get is a subpanel called History which includes Notes along with other completed items in recent related history. Documents do have a subpanel all for themselves.

The AOP Portal allows for customers to send you documents by attaching them to Cases. They can, of course, also send you documents with Emails.

So, to untangle all that from the point of view of each concept:


  • can be just text without file
  • can also have an attached file
  • are created automatically when you attach something to an Email, Case or Campaign
  • show in History subpanel, when viewing Contacts, Accounts, etc.


  • support multiple revisions
  • support more fields like category, sub-category, expiration date, etc.
  • always must include a file
  • show in their own Documents subpanel, when viewing Contacts, Accounts, etc.

Concept overlaps:

  • all files uploaded to the system become either a Document Revision or a Note
  • not all Notes are Attachments, they can be just text
  • but all Attachments created will be saved as Notes unless they are pre-existing Documents

In other posts, I explain how Documents and Attachments are stored in the database and in the file system.

Read More

How are Documents stored?

At some point you might need to go under the hood and see how SuiteCRM stores Documents in the database, and in the file system. So here’s a guide to help you out.

How are Documents connected to other records?

For the sake of an example, I tried creating a document attached to a contact, and went into the database (using phpMyAdmin) to see what had been created, it seems to work like this:

There’s a table in the database with the relationship between documents and contacts, called documents_contacts.

Here you find the id of the connected contact and of the document (to be used as keys into tables contacts and documents).

Quite a few other kinds of records can have connected documents, in analogous fashion (namely, AOS Contracts, Accounts, Bugs, Cases, and Opportunities).

A peculiar case is when attaching a Document to an Email or Email Template. The latest revision will be selected automatically, and the Document will be turned into a Note, and it is this Note that will be attached. This seems to be meant to preserve the typical attachment scheme for emails.

Documents contain one or more Revisions

Then, in the documents table, you need an extra level of document revisions, that’s what gets stored as a file: it’s a specific revision. That’s what’s specific of the Documents concept in SuiteCRM, it allows for several versions of the same document to be stored, unlike most other data where newer values simply replace older values. In the database, this is reflected in table document_revisions.

There will be a file in your upload directory whose name is exactly the id field of that document revision (e.g. something like 85c2ee00-a1ce-3c59-55bb-572397afb36e)

The file name as visible to the user (like invoice.doc) will be stored both in the documents table and in the document_revisions table, but not on the file system. Only ids of revisions are used on the file system.

So to get a full list translating document names to files on the filesystem, use this from SQL:

SELECT `filename`, CONCAT("upload/", `id`) FROM `document_revisions`

watch out for multiple files with the same name, these would be different revisions of the same document.

Read More

The two logs you need to know

SuiteCRM uses a couple of logs that give you helpful clues as to what is going on in your system. In fact, if you need to troubleshoot a problem, this is always worth checking.


You’ll find suitecrm.log in the base directory of your SuiteCRM installation. It will get rolled into several other files called suitecrm_1.log, suitecrm_2.log, etc., as the log fills up.

You can control several logging options in Admin / System Settings. These will get saved into config.php, so if you prefer you can edit them from there also (just make sure you get it right!).

An important setting is the log level, which can be Debug, Info, Warn, Deprecated, Error, Fatal, Security. These level start from the most verbose to the least verbose. Normally you can keep it at Error or even Fatal. But if you need to troubleshoot something then raising the verbosity might get you precious information.

An obvious thing that tends to be forgotten by newbies is to concentrate on messages at the time when your problem occurred. You know, press the button that crashes at 8:00 pm, check what the log shows at 8:00 pm!

You can safely ignore messages like “Information” and “Notice”, or even “Warnings”, if you don’t have any noticeable symptoms of that issue. There’s a lot of stuff in the logs, and you have to gauge how significant it is; sometimes it’s simply not.


This one is not strictly a SuiteCRM log, it’s a web server log. But it’s quite useful to troubleshoot PHP errors. Some errors only show on this log, because SuiteCRM might crash with a fatal PHP error without having an error-handler to log a prettier message in suitecrm.log.

You might need to turn on this log in your relevant php.ini file, by uncommenting or typing the following line:

error_log = php_errors.log

Again, for this log, pay attention to Fatal and Error messages, and ignore the rest, at least on your first approach.

Unlike the other log, this one includes stack traces which help you see the names of the functions that were being called when the error occurred.


I know I promised only two logs, but here’s a third one to surprise you. Also found in the root of your SuiteCRM installation, upgradeWizard.log contains the results of the original installation and SuiteCRM upgrades. So if you’re troubleshooting an upgrade that didn’t complete, or one that completed but left you with a broken system, this is the first place to check.

Read More

Customizations in the custom folder

SuiteCRM’s code can be customized in many places. The basic rule is pretty simple: whenever it needs a php file, it will look first inside the custom folder, and use any customized file that you placed there. Only if it’s not there it will keep looking in the original place of the file.

So, for example, if you want to customize


then create any necessary directories and copy the file over to


and make your changes in this second file.

Use a minimalistic approach, and copy only the files you really need to change.

Are these customizations upgrade-safe?

If you edited SuiteCRM’s files directly, the changes wouldn’t be upgrade-safe, meaning an upgrade could replace those files with new ones, and your changes would be overriden.

But if you use the custom folder like instructed above, then upgrades won’t replace them (upgrades never write files in custom directory), and so your changes will be kept - that’s why they’re called upgrade-safe (well, almost - see below).

Are the customizations picked up immediately?

If it’s a regular php file with code in it, normally you don’t have to do anything, just reload the page and it’s changed. There could be caching issues on the server side causing you to not see the change immediately, but my experience (on Linux, with Apache) is that it recognizes the change immediately.

But some php files are special in SuiteCRM. The ones like vardefs, layoutdefs, etc. For these files you often need to go into Admin, Repair menu and run a repair called Quick repair and rebuild to force the files to be read and processed. This is because SuiteCRM doesn’t directly use these files on normal operation; it just uses them to create another set of php files (that compile elements from several sources of SuiteCRM customizations), and this second set is what gets served to your browser.

Are upgrade-safe changes completely upgrade-safe?

No! There’s a caveat that I’ve never seen expressed, and that I’ve never seen happen in real-life, but feels very obvious indeed.

If an upgrade changes a SuiteCRM file, then there must be a reason for that change. Frequently, it’s tied to changes in other files. So the problem you could get is that if an upgrade changes a SuiteCRM file, and you have a copy of it custom folder, your version will be used, but you can still get all these problems:

  • miss out on a new feature
  • miss out on a security fix
  • break your system because the new changed file that came with the upgrade needs to work coherently with other new files, or with changes to the database.

So, once you start customizing, especially files with code (not just layouts and strings) keep an eye on what changes come with each upgrade.

Read More

Scheduler Jobs in SuiteCRM in Linux - the Definitive Guide

SuiteCRM uses a number of Scheduler jobs that are supposed to run at scheduled times, supporting functionalities like search indexing, workflows, email notifications, database maintenance, etc.

When completing installation, the Scheduler jobs need to be manually enabled. This is done by asking your operating system to run a script called cron.php every minute. This, in turn, manages all SuiteCRM jobs according to their proper schedules. This can all be managed at the Admin / Schedulers screen.

In Windows this is setup on the system’s “Task Scheduler”; in Linux and iOS, which is the focus of this post, it is setup in crontab. During the installation you will receive detailed instructions with the specific commands you need to run to setup these jobs. After installation, you can still access these instructions at the bottom of the Admin / Schedulers screen.

To make sure your Scheduler jobs don’t create permissions problems, the standard practice is to run them under the same user that your Web server runs under. For different configurations, the allowed_cron_users array in config.php must be adjusted.

What are the crontab basics I need to understand?

In Linux there can be one crontab per user. This applies even to users who can’t login to a shell, like the standard web-server users found in many default installations (like www-data and nobody). Throughout these examples I will be using www-data, but you can change that to whatever user you want.

To list the contents of a specific user’s crontab, use something like this command:

sudo crontab -l -u www-data

And to edit those contents, use the -e switch instead:

sudo crontab -e -u www-data

Some flavors of Linux (notably Ubuntu) have yet another crontab which is the system-wide crontab. This is edited by editing a file:

sudo nano /etc/crontab

Notice that the comments inside this crontab explain that it is the system-wide crontab. Here you can specifiy commands to be run as any user, so it has an extra column where you say which user the command runs under, for example:

* * * * * www-data cd /var/www/html; php -f cron.php > /dev/null 2>&1

See that www-data in there? That would not be there in a specific user’s crontab. That username column is just for the system-wide crontab.

Which user should these jobs run under?

The answer will vary from system to system. However, a standard acceptable solution is to find out which user your web-server runs under and use this user for the cron jobs also. This simplifies the design of your permissions scheme and basically ensures that the two processes (the web app and the cron jobs) accessing the same database and files don’t create permissions problems for one another.

Getting this wrong can severely impact your installation. Make sure that your permissions scheme is such that what one process writes and makes writable, the other can also read and write.

If you were running your cron jobs as root for some time, due to a wrong crontab configuration, after you fix it, remember to reset correct ownership and permissions on the entire SuiteCRM directory tree, and then do a Quick Repair and Rebuild.

And how can I find out which user my web server runs under?

There are many ways to do this that you can read about online. I will list a few that are specific to SuiteCRM:

  1. In versions 7.8.3 and later, go into Admin / Schedulers and see that crontab command on the bottom of the screen.
  2. In versions 7.8.3 and later, check the cron_allowed_users section of your config.php file, it should be there.
  3. On any version, go into Admin / Diagnostics and select only phpinfo. Once that file is produced, check the APACHE_RUN_USER value it provides. (Note: don’t run php -i from the command-line, it’s not the same thing)

What is the cron_allowed_users section in config.php?

The information in this section applies only to SuiteCRM version 7.8.3 and newer.

Starting with SuiteCRM 7.8.3 in April 2017, a mechanism was introduced to limit the users that are allowed to run cron jobs. Earlier versions don’t check the user and let you run cron as any user (so YOU need to make sure it is the correct user, and not root, for example)

Only users listed in an array called cron_allowed_users in config.php (on the root of your SuiteCRM installation) will be allowed to run cron.php. Any other users will cause that script to terminate itself every time it starts.

Normally, you don’t have to do anything to add a valid user to this array, since SuiteCRM installer will do that for you automatically, adding the current web server user, if it is not already there. The Upgrade Wizard will do the same (the downside of these automatic additions is that if you want to keep your web server user out of that array, it’ll take some work on every upgrade. But this should be very rare).

You can add more than one allowed user. root is particularly NOT recommended, and the Installer does not add root even if it the web server user running the installer, you have to do so manually if you really want to.

The relevant section of config.php might look like this:

'cron' =>
   array (
	 'max_cron_jobs' => 10,
	 'max_cron_runtime' => 30,
	 'min_cron_interval' => 30,
	 'allowed_cron_users' =>
	 array (
		0 => 'www-data',

Which command should I use in crontab?

The basic idea is to frequently run cron.php, and that takes care of everything.

Using the knowledge given above on how to edit the right crontab, and which user’s crontab it should be, add this command to the bottom of the crontab (given in general form):

* * * * * cd /your/suitecrm/folder; php -f cron.php > /dev/null 2>&1

And now some specific, typical examples. First, Ubuntu Server, system-wide crontab:

* * * * * www-data cd /var/www/html; php -f cron.php > /dev/null 2>&1

If you’re editing the specific www-data user crontab, omit the username in that command.

A very common install is the one provided by Bitnami. They advise using this command:

* * * * * su daemon -s /bin/sh -c "cd /opt/bitnami/apps/suitecrm/htdocs; /opt/bitnami/php/bin/php -f cron.php > /dev/null 2>&1"

Of course you can also use crontab’s syntax to make it run less frequently, which could prove relevant in a heavily used system. To run every two minutes, instead of every minute, change the first column, like this:

*/2 * * * * cd /your/suitecrm/folder; php -f cron.php > /dev/null 2>&1

What if I can’t edit crontab?

If you don’t have access as sudo to edit some crontab, which might occur on shared hosting scenarios, contact your web hosting technical support to request the changes. You can direct them to these instructions here.

What php.ini settings apply to the cron jobs?

First, recall that there are usually at least two independent PHP configurations in a system: the one that runs inside the web server and the one that runs from the command-line (CLI).

Cron jobs usually run from the CLI subsystem, which uses a different php.ini file. This means you have to configure things like timezone and other details specifically for the cron jobs.

To locate the several php.ini files in your system, you can use

sudo find / -name php.ini 2>/dev/null

To see which php.ini file your CLI is using, type

php -i | grep php.ini

How can I make sure the jobs are actually running?

  1. The Admin / Schedulers will show the latest run times of each job, and whether it had errors.

  2. Linux’s syslog normally logs which processes it launches in cron.

  3. SuiteCRM’s suitecrm.log will also log these events, as long as your log level covers it (you can check it and change it from Admin / System Settings).

  4. In case some specific Scheduler Job is failing with an error, the suitecrm.log is also the place to start troubleshooting, along with the Web Server’s log (often called php_errors.log).

Read More


The world badly needed yet another blogger, so here I am.

As I learn more about SuiteCRM, and as I get bored of repeating myself too often in the forums, I decided to put some stuff here for general reference.

I hope it will help someone. I’m sure the revenue from the ads will be very, very modest, but anyway a few cents here and there might contribute to keep me motivated to write more articles :-)

I’ll try to live up to the title “In-Depth”. That’s what I think is most missing from the world of SuiteCRM: content that gives real insight.

  • We don’t need more “there, I solved your case” forum posts, we need explanations of basic Concepts and Overviews.
  • We need stuff to help people design their data models, not just instructions on what some field in some screen does.
  • We don’t need any more generic sweeping fixes (“reset permissions on 30,000 files, it sometimes solves stuff”), we need real diagnoses and understanding of what’s really happening.

In short: I’m an engineer and a developer, not a support person. With all due respect to support persons, they would be the first to tell you they will never do their job properly unless we do ours properly first. :-)

Read More