CRM architecture: the decisions that matter

Situation: Every workspace needs its own fields (renewal date, region, tier) without migrations, yet users must filter and report on them at 100k+ records.

Choice: Store values in a validated jsonb column per entity, indexed with GIN jsonb_path_ops for containment filters, with definitions in custom_field_defs and promotion of hot fields to typed columns by migration when usage proves it.

• Rejected: EAV (entity, field, value) table: flexible but every filter is a join per field and reports become slow.
• Rejected: ALTER TABLE per customer field: fast queries but migrations on a live tenant database for every product change.
• Rejected: Schemaless document store: outside the locked stack and loses relational integrity.
• Consequence: Containment filters are index-backed; arbitrary range sorts on custom fields are not until promoted.
• Consequence: Validation lives in app code driven by the definitions, with type changes blocked once data exists.
• Consequence: Per-entity field count is capped to keep rows small.

Situation: Back-offices hold salary, margin or health fields only some roles may see; enforcing this only in the UI leaks through the API, search, sort, filters and CSV.

Choice: One projectRecord serializer and one query builder consult field_permissions on every output and input path; fields with access none are unreadable, unfilterable, unsortable and unexportable, with RLS as the tenant and scope backstop.

• Rejected: Hide fields in the UI only: bypassed by the API.
• Rejected: Separate tables per sensitivity level: strong isolation but complicates every query and report.
• Rejected: Per-role database views: hard to keep in sync with custom fields.
• Consequence: Every new access path must go through the two gates (covered by tests).
• Consequence: Reports must respect field permissions or aggregate only permitted data.
• Consequence: Permission checks add a small cached lookup per request.

Situation: Customers need to answer who changed a deal or exported the customer list, and auditors expect evidence that logs cannot be silently edited.

Choice: Write an audit row in the same transaction as each change, with changed fields only, a prev_hash/row_hash chain, no UPDATE/DELETE grant, monthly partitions and a daily verification job.

• Rejected: Application logs only: lossy, not queryable per record, easy to tamper.
• Rejected: Database triggers capturing everything: complete but noisy and unable to record actor and request context.
• Rejected: Separate event-sourcing store: powerful but a different architecture than phase 1 needs.
• Consequence: Roughly one extra insert per write.
• Consequence: Restricted fields are redacted so the audit log is not a bypass.
• Consequence: Archival keeps the chain head so old partitions stay verifiable.

Situation: Drag-and-drop between and within columns with thousands of cards must not rewrite every row in the column or conflict between two users.

Choice: Each deal has a fractional-index text rank within its stage; a move writes one row (rank between neighbours) and rebalances a column lazily when ranks grow too long.

• Rejected: Integer positions with renumbering: O(n) writes per move and conflicts.
• Rejected: Linked list of next ids: fragile under concurrent edits.
• Consequence: Moves are O(1) and conflict-friendly.
• Consequence: Occasional background rebalance job per stage.
• Consequence: Ordering queries use the (stage_id, rank) index.

Indexes are chosen from the questions the app asks most often. Each line below names a table and the question its main index answers.

• workspace_members: unique (user_id) — one membership row per user per workspace database, role lookup on every request.
• companies: unique (domain) where deleted_at is null — one company per normalised domain, upsert target for import.
• contacts: unique (email_norm) where deleted_at is null — dedupe and idempotent import upsert target.
• pipelines: unique (name) — pipeline names are unique within a workspace.
• pipeline_stages: (pipeline_id, position) — render columns in order.
• deals: (stage_id, rank) — kanban column in order, one range read per column.
• deal_stage_history: (deal_id, entered_at DESC) — deal timeline and time-in-stage.
• activities: (subject_type, subject_id, occurred_at DESC) — record timeline keyset page.

Forge64 matches your description to one of 12 reference architectures, writes the plan and data model into the project, generates the app, and runs a quality gate over the code before you see it. Each app gets its own Postgres database and sign-in with no per-user fee. Hosting on Forge Cloud and Forge Payments are rolling out.

This is a reference design for the category, not a promise that every item is generated today. Built-ins such as email, background jobs, file uploads and search are coming soon, so anything here that needs them is something you add yourself for now.

What is the most important architecture decision for a CRM?

jsonb custom fields with GIN, not EAV or per-field columns. Store values in a validated jsonb column per entity, indexed with GIN jsonb_path_ops for containment filters, with definitions in custom_field_defs and promotion of hot fields to typed columns by migration when usage proves it.

Do I have to make these decisions myself?

No. Forge AI plans them from your description and writes the decisions into the project as architecture records you can read and change.

What do the alternatives cost me?

For example, for "Field-level permissions enforced in the query builder, serializer and export": Hide fields in the UI only: bypassed by the API. Separate tables per sensitivity level: strong isolation but complicates every query and report.

• How to build a CRM
• Example prompt for a CRM
• CRM security checklist
• CRM scaling checklist
• Often combined: SaaS dashboard
• Often combined: AI document chatbot
• All app types