Custom Fields

Custom fields in CRMBuilder are stored as rows in a definitions table, not as schema columns. This is critical to understand before building any CF-prefixed template.

Storage model

Custom fields use a two-table EAV (Entity-Attribute-Value) pattern:

Table	Purpose
custom_field_definitions	Field metadata (name, type, target object)
custom_field_values	Per-record field values

This means adding a custom field requires two steps:

1. Insert a row into custom_field_definitions
2. Values are written to custom_field_values as records are updated

Never add columns to customers, deals, or companies for custom fields — always use this pattern.

custom_field_definitions schema

SELECT id, id_company, object_type, field_name, field_label,
       field_type, options, required, sort_order, created_at
FROM custom_field_definitions
WHERE id_company = :id_company
ORDER BY object_type, sort_order;

Column	Type	Notes
object_type	varchar	contact, deal, or company
field_name	varchar	Snake_case internal identifier
field_label	varchar	Human-readable label shown in UI
field_type	varchar	See type enum below
options	jsonb	For select and multiselect types only
required	boolean	Whether field is required on save
sort_order	int	Display order within object

field_type enum

Value	Input rendered
text	Single-line text input
textarea	Multi-line text
number	Numeric input
date	Date picker
select	Single-option dropdown
multiselect	Multi-option checkbox group
checkbox	Boolean toggle
url	URL input with validation

Creating a custom field

INSERT INTO custom_field_definitions
  (id_company, object_type, field_name, field_label,
   field_type, options, required, sort_order)
VALUES
  (:id_company, 'contact', 'lead_temperature', 'Lead Temperature',
   'select', '["Hot","Warm","Cold"]'::jsonb, false, 1)
RETURNING id;

For select and multiselect, options must be a JSON array of strings. For all other types, set options to null.

custom_field_values schema

SELECT id, id_definition, id_record, id_company, value
FROM custom_field_values
WHERE id_company = :id_company
  AND id_record = :contact_id
  AND id_definition = :definition_id;

Column	Type	Notes
id_definition	int	FK → custom_field_definitions.id
id_record	int	ID of the contact, deal, or company
id_company	int	Tenant scope
value	text	Always stored as text regardless of field_type

Writing a value (upsert pattern)

INSERT INTO custom_field_values
  (id_definition, id_record, id_company, value)
VALUES (:id_definition, :id_record, :id_company, :value)
ON CONFLICT (id_definition, id_record)
DO UPDATE SET value = EXCLUDED.value;

Always use upsert — never plain INSERT — to handle re-saves.

Reading all custom field values for a record

SELECT
  cfd.field_label,
  cfd.field_type,
  cfv.value
FROM custom_field_definitions cfd
LEFT JOIN custom_field_values cfv
  ON cfv.id_definition = cfd.id
  AND cfv.id_record = :record_id
WHERE cfd.id_company = :id_company
  AND cfd.object_type = 'contact'
ORDER BY cfd.sort_order;

Checking for field name conflicts

Before inserting a new field definition, check for duplicates:

SELECT id FROM custom_field_definitions
WHERE id_company = :id_company
  AND object_type = :object_type
  AND field_name = :field_name;
-- If rows returned, field already exists — update or skip