Custom Fields: Give Your Customers the Fields They Need
Tags: rails, development, programming, buildingproductive
Published: 2023-12-06 10:20:00

---
title: Custom Fields: Give Your Customers the Fields They Need
published: true
description: Each agency is different (think type, size, services they offer…), so they need customization options for their workflows. You can imagine it's pretty tough to model all those needs through a unified data model. If only there were a way...
tags: rails,development,programming,buildingproductive
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wykyzawnfphsbvf3o92p.png
published_at: 2023-12-06 10:20 +0000
canonical_url: https://productive.io/engineering/custom-fields-give-your-customers-the-fields-they-need/
---
Here at Productive, **we’re building an operating system for digital agencies**.

But, because each agency is different (think type, size, services they offer, the way they’re set up as an organization…), they need customization options for their workflows. So it’s pretty hard to model all those needs and use cases through a unified data model.

**If only there were a way to let them shape those models to their own needs.**

![Customer feature requests](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xvm0ccd18zdv5n5yq5fm.png)

Let’s say that one of our customers, *ACME digital agency* wants to keep track of their employees’ nicknames and to be able to search them by that field. Other than that, they would also like to keep track of their birthdays and be able to sort them and group them by that date.

To me, as a developer, this sounds as simple as it gets—add two new columns to the `people` table, open those attributes to be editable over the API and send them back in the response.
But should we do that? Should we add all kinds of fields to our models even if those fields are going to be used only by a handful of our customers?

Let me show you how we tackled this type of feature request and made a pretty generic system around it.

### What Did Our Customers Want?

It was pretty clear to us what our customers wanted, and that was:

* to be able to **add additional fields** to some of our models (People, Projects, Tasks, …)
* to have **various data types on those fields** (text, number, or date)
* to be able to **search, sort, or even group** by those fields

## Our Approach
### The Custom Field Model
As we’re building a RESTful [API](https://developer.productive.io/) that’s formatted by the [JSON:API specification](https://jsonapi.org/) and store our data in a [MySQL8](https://dev.mysql.com/doc/refman/8.0/en/) relational database, a few things were pretty straightforward – we need a new model and we’ll name it **Custom Field** (naming wasn’t an issue here 🥲).

The main attributes of that model should be:
```ruby
name - string
description - string
data_type_id - integer (1 - text, 2 - number, 3 - date)
  -> enumeration that described the data type of the field
customizable_type - string 
  -> tells us on what data model will that custom field be available, eg. Person, Project, Task, ...
  -> could've also been an enumeration 😅
```

### How To Store the Field Values?
OK, so now that we know how to define custom fields, how can we know which value someone assigned to a custom field for some object? And where to store that information?

Three possible solutions came to mind:

**1. Add a limited number of *custom_field* columns to our models**

We can add a few `custom_field` columns to our models and that will work for some of our customers but there will always be others that need few extra fields. Adding numerous columns to our models surely isn’t the best solution, we can do better than this 😅

**2. Add a join table**

As mentioned before, while relying on a relational database, a join table sounds like the go-to approach. That table would be a simple join table between the custom field and a polymorphic target (yay, Rails 🥳). Other than those foreign keys, we would have a column to store the value.


**3. Add a single JSON column to our models**

This sounded as flexible as it gets. It would be a simple map where the key would be the custom field ID and the value would be the assigned value for that custom field.
![Solutions](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u8izj6admv3lmmufdpt1.png)

### Why We Ended Up Choosing JSON
The first solution was just too limited so we discarded that one immediately and focused on the remaining two solutions.

On one hand, a better design would be to have the custom field values represented by a model but on the other hand, we won’t actually do much with that data. That would just be data that our users set on our objects, data that isn’t important for our business logic. So a simple JSON column didn’t sound bad either.

The searching and sorting aspect of this feature request was probably the most important one for us. That was supposed to work as fast as it gets, without being a burden to our performance.

That’s why we implemented both solutions, tested a lot of searching/sorting/grouping scenarios (we’ll go through that in more detail soon), and then checked the metrics.

The faster solution was the second one, the one with the JSON column, and that made sense to us. That solution doesn’t use `JOIN` clauses in SQL since the values are written directly in the searched table and can be queried in the `WHERE` clause. Luckily for us, MySQL8 supports a bunch of [great functions to work with JSON columns](https://dev.mysql.com/doc/refman/8.0/en/json-functions.html) (`JSON_EXTRACT`, `JSON_UNQUOTE`, `JSON_CONTAINS` and others).

### Great! Now that we know how to store the custom field values too, let’s dig into the coding.

From a development point of view, we did the following:

* Added a new model, **Custom Field**, and implemented CRUD operations that can be called over the API
* Wrote schema migrations that added a JSON column – `custom_fields` – to some of our models (people, projects, tasks, …)
* Opened the `custom_fields` attribute so it can be edited over the API
* Wrote a generic validation that checks if all the values in the `custom_fields` hash have the appropriate data type
* Added the `custom_fields` attribute to the API response of the appropriate models

That was most of the work we needed to do to be able to manage custom fields in our models.

But…what about the searching and sorting aspect of custom fields?

### Searching Through Custom Field Values
We already had a generic solution written for [searching over the API](https://developer.productive.io/index.html#header-filtering).

We have a format of sending query params for searching, like `filter[attribute][operation]=value`. For searching through custom fields, we wanted to keep the same format so we ended with a quite similar one – `filter[custom_fields][custom_field_id][operation]=value`.

We had to add an if-else statement that would handle the custom fields filtering in a different way than filtering through other attributes as the format contained one additional argument — `custom_field_id`.

What was different in the filtering logic was that we have to load the custom field that’s being filtered by and check what data type its values are. That’s needed to cast the values into numbers or dates—text values don’t make a difference.

So the query params and its SQL query counterparts, based on custom field type, would look like this:
```sql
/* for a text custom field and query filter[custom_fields][1][eq]=abc */
LOWER(custom_fields->>'$."1"') LIKE '%abc%'; 

/* for a number custom field and query filter[custom_fields][2][eq]=42 */
CAST(LOWER(custom_fields->>'$."2"') AS DECIMAL(20,10)) = 42; 

/* for a date custom field and query filter[custom_fields][3][eq]=2022-11-01 */
DATE(LOWER(custom_fields->>'$."3"')) = '2022-11-01';

/* The ->> operator is an alias for JSON_UNQUOTE(JSON_EXTRACT(...)) */
```

### Sorting by Custom Field Values
The concept of [sorting](https://developer.productive.io/index.html#header-sorting) by attributes is something we also already tackled by abstracting logic.

The only thing that changes when sorting by custom fields is that we first need to cast the values and then sort by them.

Once again, there’s a small change in the format for custom fields sorters (`sort=custom_fields[custom_field_id]`) compared to when sorting by a standard attribute (`sort=attribute`). We need to handle the custom_fields sorters separately because we have to load the desired custom_field and check its type.

Then the `ORDER BY` statement, based on custom field types, looks like this:
```sql
/* for a text custom field and query sort=custom_fields[1] */
ORDER BY CAST(custom_fields->>'$."1"' AS CHAR) ASC; 

/* for a number custom field and query sort=-custom_fields[2]
Noticed the - on the begining? That defines the order of sorting - DESC or ASC */
ORDER BY CAST(custom_fields->>'$."2"' AS unsigned) DESC;

/* for a date custom field and query sort=custom_fields[3] */
ORDER BY DATE(custom_fields->>'$."3"') ASC;
```

### Grouping by Custom Field Values
This was a fun one. The main point here was that you should include the custom fields as some kind of columns stated in the `SELECT` statement so that you could later use those columns in the `GROUP BY` statement.

To get the custom field in the `SELECT` statement, you have to create a virtual column for it. All we needed to do was to extract the values of the grouped custom field and give that virtual column an alias so that we could reference it in the `GROUP BY` statement. For the column alias we went with the format `custom_fields_{custom_field_id}`.

For a custom field with `id=x`, this is done as following:
```sql
SELECT ..., custom_fields->>'$."x"' AS custom_fields_x 
```

Once we have the virtual column defined, the grouping part gets done simply, by adding the `GROUP BY` statement with the earlier mentioned alias.

So in the end, you get a `SQL` query like:
```sql
/* query group=custom_fields[1] */
SELECT ..., custom_fields->>'$."1"' AS custom_fields_1
FROM ...
WHERE ...
GROUP BY custom_fields_1 
```

### What Our Customers Got
A simple way to define Custom Fields:
![Custom fields UI](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/guvx6aizi5m9xhrs3qwo.png)
And a place to assign values to their fields:
![Assigning custom fields](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2oyesva4yxmac6r9w50l.png)

## Summa Summarum
We made it possible for our customers to define custom fields in our data models. Also, we made it possible to search, sort and group by those fields.
It wasn’t long before we had even more requests that built upon our custom fields architecture. The fields we supported at first were okay, but now our customers wanted more field types. They wanted:
* to have dropdown custom fields
* to have relational custom fields
* a field where the values would be objects from one of our existing data models

But before we dig into that, let’s give some time for this basics to sink in. I’ll be back soon with another blog post in which I cover how we solved that new set of feature requests