🌈 0.1.4 Custom fields

[FelixMalfait]

Why we need this

Every business has unique data collection requirements, which standard fields may not be able to accommodate. Custom fields offer the flexibility to tailor the CRM to specific business needs.

DB design

Recommended option

Fields Table

Column	Type	Description
id	SERIAL PRIMARY KEY	A unique, auto-incrementing identifier for each field.
workspace_id	INT NOT NULL	Foreign key reference to the id of the corresponding field in the Workspaces table
object_name	VARCHAR(255) NOT NULL	The name of the object to which the field belongs.
field_name	VARCHAR(255) NOT NULL	The name of the field.
field_type	VARCHAR(255) NOT NULL	The data type of the field.

FieldsValues Table

Column	Type	Description
id	SERIAL PRIMARY KEY	A unique, auto-incrementing identifier for each field value.
field_id	INT NOT NULL	Foreign key reference to the id of the corresponding field in the Fields table.
object_id	INT NOT NULL	An identifier for the specific object instance to which the field value belongs.
value	TEXT	This column stores the actual field value.

Alternative option

Same ObjectFields table. But Values table by type, e.g.

FieldsValuesInt Table

Column	Type	Description
id	SERIAL PRIMARY KEY	A unique, auto-incrementing identifier for each field value.
field_id	INT NOT NULL	Foreign key reference to the id of the corresponding field in the Fields table.
object_id	INT NOT NULL	An identifier for the specific object instance to which the field value belongs.
value	INT	This column stores the actual field value.

FieldsValuesStringTable

Column	Type	Description
id	SERIAL PRIMARY KEY	A unique, auto-incrementing identifier for each field value.
field_id	INT NOT NULL	Foreign key reference to the id of the corresponding field in the Fields table.
object_id	INT NOT NULL	An identifier for the specific object instance to which the field value belongs.
value	VARCHAR(255)	This column stores the actual field value.

Backend design

Let's modify the API of standard objects.
For example, People record would be returned like this

{
    "firstName": "Tim",
    "lastName": "Apple",
    "customFileds": {
         "VIP": true
    }
}

Note: let's be careful to eager-load data properly (i.e. avoid doing a lot of sql queries)

Frontend design

Tbd

[BenjaminBeguin]

Some random thought on it:

I think attaching the fields to the workspace is a good idea, especially if you are planning to reuse them across multiple tables.

Regarding the Schema:

{
    "firstName": "Tim",
    "lastName": "Apple",
    "customFileds": {
         "VIP": true
    }
}

If you are using GraphQL, having the custom field names (or IDs) as keys might not be easy to handle. How about having it like this:

Proposal

{
    "firstName": "Tim",
    "lastName": "Apple",
    "custom-fields-values": [
       {
           id: "xxx", // For update mutation, the id might be needed 
           name: "My Field Name" // Probably not needed on the Front-end, but can be present un the node schema,
           value: "Hello!"
       }
    ] // If there is multiple types, having a union of nodes could work here
}

DB Alternative option

A values table by type (it's the approach we used at my company) might lead to a lot of tables, and it's easy to have more database queries. Another alternative that we were thinking about (we haven't migrated to this solution yet) is to have one single table with as many value fields as different types of values you want to store.

Proposal

Column	Type	Description
id	SERIAL PRIMARY KEY	A unique, auto-incrementing identifier for each field value.
field_id	INT NOT NULL	Foreign key reference to the id of the corresponding field in the Fields table.
object_id	INT NOT NULL	An identifier for the specific object instance to which the field value belongs.
text_value	VARCHAR(255)	This column stores the actual field value.
number_value	FLOAT(255)	This column stores the actual field value.
_	_	_
date_value	DATE(255)	This column stores the actual field value.
user_value	FK to user	This column stores the actual field value. => This column stores the actual field value. => Probably user_values, to be able to save multiple users in a cell
company_value	FK to user	This column stores the actual field value. => Probably user_values, to be able to save multiple companies in a cell

This approach has pros and cons too. Let me know your thoughts.

Other

Maybe there are some rules to define, such as:

Field uniqueness in a workspace => Probably name + type is good

FE Design

I can show you a quick demo of what we built at my company if you want to take some inspiration (or not!).

[skamensky]

I highly reccomend not creating a dedicated field value table, but instead use idiomatic SQL style. One table per "object", and one column per "field". This grants access to database tools, standard backups, views, index tuning, full text search, procedures, triggers, etc.

Corteza started with a field value table and was the source of many performance issues. They later pivoted to standard the DB model.

If you need the field value table, make that a calculated table and take responsibility for synchronizing its data instead of putting the onus on developers to normalize it into a standard data format.

[FelixMalfait]

Thanks @skamensky good point, the discussion continued here: #1142 and @Weiko started working on a proof of concept here: #1374
I think you'll like the direction we're going! Let us know

One thing we wanted to do is introduce a concept of "remote objects", connecting external data sources under our GraphQL layer, like Hasura is doing it (instead of having to build a pipeline to transfer data to your CRM you can directly connect it and fetch it live). This makes things more complex for us to build and wouldn't allow us to leverage as much from Postgres long-term (e.g. permissions need to be managed in the application layer not Postgres), but it's still relevant to leverage the DB features as much as we can for now

[skamensky]

@FelixMalfait

If remote objects get first class support, then it seems all we would need to do would be to add a postgres database and do all of the customzations on that end.

I would say that something most implementations I've seen miss (except for supabase) is forgetting that the DB (at least postgres) has its own permissioning system that can be useful on top of the application permissioning system.

The way supabase accomplishes this is by wrapping all queries (even select's) in transactions and setting transaction level variables to a values that identifies the application user. Once you have that you can use postgres's RLS on the application user level.

I understand that you would probably not put twenty's permission management there since you want to allow for multiple systems (not just postgres) to plugin, but it would be a shame to not to enable dev's to choose postgres RLS when the solution is so accessible!

[FelixMalfait]

Thanks a lot @skamensky. Good point.

Agree it’d be cool to consider first-class support for Postgres things like RLS, materialized views or triggers before we start re-implementing things at the application layer.
Cc @Weiko