Flexible Record Model
Every CRM that locks its schema dies the same way. The owner asks for one custom field, the vendor says "great, that'll ship next quarter," and a year later the spreadsheet beside the CRM is doing more work than the CRM itself. We took the model that Drupal popularized and that Twenty CRM modernized — a thin base record plus typed field tables — and built the entire data layer around it.
The base Record
Every entity in the system is a Record. A contact, a project, an invoice, a job site, a piece of equipment, a custom "Permit Application" object a tenant invents — all of them sit in the same base table.
records · the shared base table
ColumnTypePurpose
idTEXT PKUUIDv7 — sortable and globally unique across all tenants
record_typeTEXTContact, Project, Invoice, JobSite, or any user-defined type
display_nameTEXTCached human-readable label so lists don't need joins to render
created_atINTEGERUnix epoch ms — sortable, comparable, timezone-independent
updated_atINTEGERUnix epoch ms — used by sync to determine staleness
owner_idTEXT FKUser who owns the record — defaults to creator, transferable
parent_idTEXT FKOptional parent record — enables nesting (project → tasks)
archived_atINTEGERSoft delete marker — preserved for sync and audit
versionINTEGERMonotonically incremented on every field write — used in conflict resolution
That's it. Nine columns. Every record in the CRM, regardless of type, fits this shape. The interesting data lives elsewhere.
The field storage tables
Field values are stored in a small set of typed tables, one per primitive data type. This is what Twenty CRM does, what Drupal calls "field storage," and what database people sometimes call "table-per-type EAV." It's faster than a single value column because indices on value_int, value_text, etc. can actually be useful, and the type column tells the query layer which table to read from.
The catalog of every field that exists for every record type. Tenants edit this to add or remove fields.
id · field UUID
record_type · which entity this field belongs to
name · "phone_mobile", "warranty_years"
data_type · text · int · float · bool · datetime · ref · enum · json
required · whether NULL is allowed
display_order · default form-rendering position
config · JSON · options for enum/ref/validation
field_values_* (one per type)
Actual values, sharded by primitive type so each table has a tightly-typed indexed column.
record_id · FK → records.id
field_id · FK → field_definitions.id
value · the typed column (TEXT / INTEGER / REAL / etc.)
updated_at · for CRDT merge during sync
Tables: field_values_text, field_values_int, field_values_real, field_values_bool, field_values_datetime, field_values_ref, field_values_json
A concrete walkthrough
Picture three records — a Contact, a Project, and a custom "Permit Application" the tenant invented because they install solar arrays and care about permit status.
Records
r_01H...A · type=Contact
r_01H...B · type=Project
r_01H...C · type=PermitApp
→
Field Definitions
full_name · text · Contact
phone · text · Contact
status · enum · Project
permit_no · text · PermitApp
filed_on · datetime · PermitApp
fee_paid · real · PermitApp
→
Field Values
text: r_...A + full_name = "Sarah Garcia"
text: r_...A + phone = "208-555-0142"
enum: r_...B + status = "on_site"
text: r_...C + permit_no = "P-2026-114"
datetime: r_...C + filed_on = 1716163200000
real: r_...C + fee_paid = 340.00
To read the full Contact record, the API joins records with each field_values_* table where record_id = r_01H...A. The materialized read is cached server-side and indexed locally on mobile so a list view doesn't pay the join cost on every render.
Why this beats fixed columns
No migrations to add a field
A tenant clicks "Add field: Warranty Years" in the admin UI. We insert one row into field_definitions and the form, table view, kanban card, and search index all pick it up immediately. No ALTER TABLE, no deploy, no downtime.
Multi-tenant safe by default
Custom fields belong to a tenant. They never appear in any other tenant's schema. You can ship a new industry-specific feature without forking the schema or shipping a 200-column table that 80% of tenants ignore.
Field deprecation is trivial
Mark a field_definition as archived. Existing values stay readable in audit history; new writes are rejected. Compare to a fixed-column model where dropping a column is a quarter-long project.
Composable views
A view is just a saved combination of which fields to show, how to group, and how to filter. Same field definitions feed table, kanban, gantt, and card views — see the Views architecture page for details.
Schema-as-data audit
The field_definitions table is itself audited. You can see who added the "Lead Source" enum option "TikTok Ad" on what date — the same way you audit data changes. Schema and data live in the same governance system.
Industry-specific templates ship as field bundles
Onboarding a roofer? Stamp a bundle of pre-defined field definitions (Pitch, Material, SquareFootage, Warranty). Onboarding HVAC? Different bundle. Tenants extend from the template instead of starting from a blank table.
The tradeoffs we accept
Field-storage models aren't free. Two costs are worth naming up front so nobody is surprised later.
Joins on read
Reading a full record means joining records + every field_values_* table that holds data for it. We mitigate this with a per-record materialized JSON snapshot updated on write, so reads hit one table for hot data and only fall back to joins for rare queries.
Foreign-key constraints across tables
You can't declare "every Project must have a non-null status" with a NOT NULL constraint on a column that doesn't exist. We enforce required-ness in the field_definitions row and validate at the API layer. The cost is a slight loss of database-level rigor; the benefit is the flexibility everything else on this page describes.
This data model is what makes the multi-tenant D1 model viable, the offline sync tractable, and the multi-view rendering practical. The other architecture pages build on it directly.