Designing a flexible crm schema

Today I went down a rabbit-hole thinking about how to build a flexible CRM on top of Postgres. Most CRMs lock you into rigid tables (contacts, deals, etc.If you want users to define their own record types, custom fields, and relations, you need a different schema design. 💽👇

To build a flexible CRM like @Attio or @NotionHQ, the core approach is similar:
• records → one table, one row per object
• data JSONB → entire payload of custom attributes
• attribute_definitions → metadata describing fields (name, type, validations)
• attr_* mirrors → typed tables for indexed filters & sorts

Why JSONB? Easy whole-object storage, fast patching, and flexible schemas per tenant. Paired with mirrors (attr_text, attr_number, …) you still get performant queries.

Relations are first-class:
• record_edges stores links between records
• relation_types define semantics + cardinality (1-1, 1-n, n-n)
• constraints enforced with partial unique indexes

Primitives to support:
• text (with validations like email/url/phone)
• number (int/decimal)
• boolean
• date, datetime (zoned), datetime_local (wall time)
• select + multiselect (with option catalogs + junctions)
• arrays of primitives (attr_text_many, attr_number_many, …)
• references (edges)
• file/media pointers (attachments)
• currency (with FX normalization)
• formula / rollup

First-class currency handling (if you’d build @Stripe in 2025):
• store amount_minor (integer minor units, exact)
• generate amount (NUMERIC) for math
• compute amount_base (converted to tenant’s base currency) for sorting & rollups
• fetch FX rates opportunistically for only the (base, quote) pairs you actually need

Dates & time zones:
• date for calendar days (filters like “last week”)
• timestamptz for absolute instants (UTC)
• timestamp for local wall times (“every day at 9am”)
• optionally persist original IANA tzid for display

Soft deletes:
• deleted_at everywhere
• partial indexes where deleted_at is null
• triggers cascade edges on delete
• background job purges after N days

Formulas & rollups:
• single formula engine
• rollups = formulas that aggregate via a relation
• dependency graph stored in formula_dependencies
• recompute enqueued to formula_jobs and processed by workers

Unique fields:
• implemented with partial unique indexes on the mirrors
• e.g. create unique index on attr_text(tenant_id, attribute_id, value) where attribute_id = …;

Files/media:
• metadata in attachments (filename, mime, size, storage key)
• blob in S3/GCS
• queries like “has proposal PDF” = exists(select …)

Arrays:
• per-element tables for queryable sets (attr_text_many, …)
• add position if ordered
• no mixed-type arrays

Indexing patterns:
• (tenant_id, record_type_id, updated_at desc) for list views
• (tenant_id, attribute_id, value) on mirrors
• GIN on records>data for ad-hoc JSON search

TL;DR:
• records + JSONB payload for flexibility
• typed mirrors for performance
• edges for relations
• currency normalized to base for cross-currency ops
• formulas/rollups with a dependency graph
• soft deletes + jobs for hygiene