Every account in our CRM can add its own custom fields, so any of them might end up holding health data. Here's why we encrypt every value, why that ruled out JSONB, and how we still filter and search them in Rails 8.1 and PostgreSQL.
An admin opens the contact settings, clicks New Field and types "Allergies". A week later, three hundred contacts have an answer. That's health data now, sitting in your database, and nobody told you.
That's the deal with custom fields. In our multi-tenant CRM, every account can add up to 30 fields of its own to a contact: text, a date, or a list of options. And they have to work like the built-in ones: on the profile, in web forms, in CSV import and export, in filters and in the public API.
Since the account decides what a field holds, we treat every custom value as sensitive and encrypt it. That one rule changed the whole design.
In this article, we'll cover why JSONB didn't fit, the two-table schema we use instead, how deterministic encryption keeps exact matches in SQL, and how we search the rest in Ruby. All examples run on Rails 8.1 and PostgreSQL.
Why Not JSONB?
The first idea is always a
custom_fields JSONB column on contacts. One column, no joins, and Postgres can index it with GIN. For data you can read, it's a great choice.
Encryption breaks it. Active Record Encryption encrypts the whole attribute. Your JSONB column turns into one encrypted string, and
->>, @> and GIN indexes stop working. So you're left with two bad options: encrypt it and never query it, or query it and keep health data in plain text.JSONB also makes the life of a field expensive:
- Deleting a field means rewriting every contact in the account.
- "Did anyone fill this field?" means scanning every contact.
- There's no foreign key. A key in the JSON can point to a field that no longer exists.
So the real question wasn't JSONB vs a table. It was: once the value is encrypted, which queries can we keep?
Two Tables: Definitions and Values
We split the field from its answers.
custom_fields holds one row per field per account. custom_field_values holds one row per contact and field.Think of it like a paper form. The form itself (the questions, the options) is the same for everyone and nothing on it is private. The filled-in copies are private, one per person.
# db/migrate/20260801120000_create_custom_fields.rb
class CreateCustomFields < ActiveRecord::Migration[8.1]
def change
enable_extension "citext"
create_table :custom_fields do |t|
t.references :account, null: false, foreign_key: { on_delete: :cascade }
t.citext :name, null: false
t.string :handle, null: false
t.string :field_type, null: false
t.jsonb :settings, null: false, default: {}
t.float :position, null: false
t.datetime :discarded_at
t.timestamps
end
add_index :custom_fields, [:account_id, :handle], unique: true
create_table :custom_field_values do |t|
t.references :contact, null: false, foreign_key: { on_delete: :cascade }
t.references :custom_field, null: false, foreign_key: { on_delete: :cascade }
t.text :value
t.timestamps
end
add_index :custom_field_values, [:contact_id, :custom_field_id], unique: true
end
end
A few things worth knowing:
-
JSONB is still here.
settingsholds the options and the multi-select flag. That's configuration, not personal data, so it stays readable. -
Cascades live in the database.
dependent: :delete_allskips callbacks, so a model-only cascade can leave orphan values behind.on_delete: :cascadecan't be skipped. -
Built-in fields can share the table. Add a nullable
keycolumn for them, and admins can sort built-in and custom fields together in one list.
The model wraps
settings with StoreModel, so options and the multi-select flag are typed attributes instead of hash keys.# app/models/custom_field.rb
class CustomField < ApplicationRecord
include Discard::Model
FIELD_TYPES = %w[text date select].freeze
class Settings
include StoreModel::Model
attribute :multi_select, :boolean, default: false
attribute :options, default: -> { [] }
end
attribute :settings, Settings.to_type
delegate :options, to: :settings
belongs_to :account
has_many :values, class_name: "CustomFieldValue", dependent: :delete_all
validates :field_type, inclusion: { in: FIELD_TYPES }
validates :handle, presence: true, uniqueness: { scope: :account_id }
validates :options, presence: true, if: :select?
def select? = field_type == "select"
def date? = field_type == "date"
def multi_select? = select? && settings.multi_select
end
Exact Matches on Encrypted Data (Deterministic Encryption)
By default, Active Record Encryption is non-deterministic: the same text gives a different ciphertext every time. That's the safest mode, but you can't search it.
With
deterministic: true, the same text always gives the same ciphertext. Think of it like a fingerprint: you can't read a person from it, but two matching prints mean the same person. So where(value: "Gold") works, and Rails encrypts the argument for you.# app/models/custom_field_value.rb
class CustomFieldValue < ApplicationRecord
belongs_to :contact
belongs_to :custom_field
encrypts :value, deterministic: true
validates :custom_field_id, uniqueness: { scope: :contact_id }
def value=(new_value)
super(new_value.is_a?(Array) ? serialize_list(new_value) : new_value)
end
def typed_value
return JSON.parse(value) if custom_field.multi_select?
return Date.iso8601(value) if custom_field.date? && value.present?
value
end
private
def serialize_list(values)
values.map(&:to_s).compact_blank.sort.to_json
end
end
Every value is stored as text and cast by type: dates as ISO 8601, multi-select as a JSON array.
Note the
.sort in serialize_list. ["Wine", "Golf"] and ["Golf", "Wine"] are the same answer but two different ciphertexts, which breaks the equality we just paid for. We caught it before real data existed. Adding it later would mean re-encrypting every multi-select row.Equality gives us more than filters:
- Single-select filters stay pure SQL.
-
"Is this option in use?" before an admin removes it is one
where(value: removed_options). -
"Has anyone answered?" is
field.values.exists?.
Rules That Keep the Data Clean
A row per value only works if every row means something. These rules came from review rounds and from bugs we'd rather not ship twice:
-
The handle never changes. We generate it from the name once: "Wine club" becomes
wine_club. The API, form mappings and CSV import all point at the handle, so renaming the field breaks nothing. - An empty value means no row. A blank row would count as "answered" forever and lock the field type for no reason.
- The type locks after the first answer. Switching a field from text to date would leave values that don't parse.
- Options in use can't be removed. Otherwise a saved answer points at an option the field no longer offers.
- Delete is soft. Discarding a field deletes its values. Creating a field with the same name brings the old record back, handle included, so forms mapped to it keep working.
Rule 3 is a short validation on
CustomField:validate :field_type_locked_once_answered private def field_type_locked_once_answered return unless persisted? && field_type_changed? return unless values.exists? errors.add(:field_type, "can't change once contacts have values") end
Searching What SQL Can't See (Match in Ruby)
Deterministic encryption gives you equality and nothing else: no
LIKE, no ranges, no ORDER BY. But users still want "notes contain vegan", "renewal date between March and May" and "any of these options".Here's what we do:
- Load the distinct values of one field.
distinctworks on ciphertext because the same text always encrypts the same way. - Decrypt and match them in Ruby.
- Go back to SQL with the exact values that matched.
# app/models/custom_field/value_filter.rb
class CustomField::ValueFilter
def initialize(field, term)
@field = field
@term = term.to_s.strip.downcase
end
def relation
@field.values.where(value: matching_values)
end
private
def matching_values
return [] if @term.empty?
stored_values.select { |value| value.downcase.include?(@term) }
end
def stored_values
@field.values.distinct.pluck(:value).compact
end
end
Date ranges work the same way: parse each distinct value and keep the ones in range. Multi-select decodes each stored list and keeps the ones that share an option with the filter.
We also looked at one row per selected option for multi-select, and said no. It fixes only multi-select, text and dates still need Ruby, and it costs a migration plus a backfill.
The catch: the cost grows with the number of distinct values in a field, not with the number of contacts. A select field has a handful. A free-text field in an account with 500,000 contacts could have 500,000, all loaded and decrypted on every search.
Putting It All Together
Here's the full loop: write an answer, read it back, filter by it. Writing goes through one method, so rule 2 (no empty rows) lives in one place:
# app/models/contact.rb
class Contact < ApplicationRecord
has_many :custom_field_values, dependent: :delete_all
def write_custom_field(field, answer)
record = custom_field_values.find_or_initialize_by(custom_field: field)
if Array(answer).compact_blank.empty?
record.destroy if record.persisted?
else
record.update!(value: answer)
end
end
def custom_field_value(field)
custom_field_values.find_by(custom_field: field)&.typed_value
end
end
The same method is called from the profile, from web form submissions, from the CSV import and from the API. The search plugs the filter in as a subquery:
contact.write_custom_field(wine_club, ["Reds", "Bubbles"]) contact.custom_field_value(wine_club) filter = CustomField::ValueFilter.new(notes_field, "vegan") account.contacts.where(id: filter.relation.select(:contact_id))
Advantages
- Sensitive data stays encrypted. Every custom value is encrypted at rest, no matter what the admin names the field.
-
Exact matches stay in SQL. Single-select filters, "option in use" checks and
exists?never leave the database. - The database guards integrity. Foreign keys, a unique index per contact and field, and cascades that callbacks can't skip.
-
A field's life is cheap. Deleting a field is one
delete_all. Checking if anyone answered is oneexists?. - Built-in and custom fields live together. One table of definitions, one order, one place to render the profile.
When to Use (and When Not to Use) This
This setup fits when:
- You have many tenants with few fields each (we cap at 30).
- The values may be sensitive, and you can't know which ones ahead of time.
- Most filters are exact matches on options.
Think twice when:
-
You need sorting or ranges on very large accounts. Encrypted text has no order, so there's no
ORDER BYand no index for ranges. - One field gets huge. Free-text search loads every distinct value of that field into memory.
- Frequency is a secret. Deterministic encryption shows patterns: in a Yes/No field, the split of ciphertexts shows the split of answers.
If you hit those limits, keep the same tables and add a "sensitive" flag per field. Only flagged fields get encrypted. The rest can go in typed columns (
value_text, value_date) with normal indexes. For exact lookups on fields that must stay encrypted, look at blind_index.Best Practices
Sort lists before you encrypt them. Deterministic encryption only helps if the same answer always produces the same text.
Delete empty answers instead of storing them. A blank row lies about who answered.
Put cascades in foreign keys.
delete_all and bulk deletes skip callbacks. The database doesn't.
Reference fields by a fixed handle, never by name. Names change. Integrations shouldn't break when they do.
Lock what existing data depends on. Type and in-use options freeze after the first answer.
Treat custom values as sensitive everywhere. We mask them in notification emails and PDFs too, not just in the database.
Conclusion
JSONB is a great default for flexible data you can read. Once the values have to be encrypted, JSONB vs a table is the wrong question. The real question is which queries you keep.
A row per value with deterministic encryption keeps exact matches, foreign keys and a cheap field lifecycle. Partial matches and ranges go to Ruby, which is fine until a single field gets very large. And when that day comes, a per-field "sensitive" flag is a small change, because the tables stay the same.
Additional Resources
Share: