A customer relationship management (CRM) application centralizes customer data, interactions, and revenue activities so that sales, support, and operations can execute consistent workflows. Designing a CRM from scratch requires explicit scoping, a data model that supports your business processes, and an architecture that remains secure and maintainable as features and users grow.
Start with requirements that define the CRM core. Identify the primary user roles, the primary objects, and the actions that must be fast and reliable. Common roles include sales representatives, sales managers, support agents, and administrators. Common objects include accounts, contacts, leads, opportunities, activities, and support tickets. Define non functional requirements such as uptime targets, auditability, data retention, export needs, compliance expectations, and acceptable latency for list and search views.
Define the minimum viable CRM scope before expanding. A defensible initial scope often includes authentication and authorization, account and contact management, activity logging, pipeline tracking, basic reporting, and CSV import and export. Defer advanced automation, complex forecasting, and large integration catalogs until the foundational model, permissions, and search capabilities are stable.
Design the domain model around real workflows. A typical CRM has both static entities and time ordered events. Static entities include accounts and contacts. Events include calls, emails, meetings, notes, and status changes. Modeling events explicitly improves auditability and reporting because it produces a consistent timeline. Decide early whether you will implement a single tenant model with tenant identifiers on every row or a separate database per tenant. Most small and mid sized systems adopt a shared database with strict tenant isolation enforced at the application and database layers.
A recommended baseline relational model includes tenants, users, teams, accounts, contacts, leads, opportunities, pipeline stages, activities, and attachments. Ensure that every business object includes tenant_id, created_at, updated_at, and created_by. Add soft deletion fields when legal or operational requirements demand recoverability. Include stable unique identifiers that are not guessable. Use separate tables for many to many relationships such as contact to account or user to team.
sql 
CREATE TABLE tenants (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE users (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  email TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (tenant_id, email)
);

CREATE TABLE accounts (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  name TEXT NOT NULL,
  website TEXT,
  industry TEXT,
  owner_user_id UUID REFERENCES users(id),
  created_by UUID NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE contacts (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  account_id UUID REFERENCES accounts(id),
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT,
  phone TEXT,
  title TEXT,
  owner_user_id UUID REFERENCES users(id),
  created_by UUID NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE opportunities (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  account_id UUID NOT NULL REFERENCES accounts(id),
  name TEXT NOT NULL,
  amount_cents BIGINT NOT NULL DEFAULT 0,
  currency TEXT NOT NULL DEFAULT 'USD',
  stage TEXT NOT NULL,
  close_date DATE,
  owner_user_id UUID REFERENCES users(id),
  created_by UUID NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE activities (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  subject_type TEXT NOT NULL,
  subject_id UUID NOT NULL,
  activity_type TEXT NOT NULL,
  body TEXT,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_by UUID NOT NULL REFERENCES users(id)
);

CREATE INDEX idx_accounts_tenant_name ON accounts (tenant_id, name);
CREATE INDEX idx_contacts_tenant_email ON contacts (tenant_id, email);
CREATE INDEX idx_opps_tenant_stage ON opportunities (tenant_id, stage);
CREATE INDEX idx_activities_subject ON activities (tenant_id, subject_type, subject_id, occurred_at);
Choose an architecture that fits your team and scaling needs. A modular monolith is a strong default for a new CRM because it reduces operational complexity while allowing clean separation of domains such as identity, CRM core, reporting, and integrations. Use a service boundary only when team structure and scalability requirements justify it. Regardless of approach, design clear module interfaces, isolate data access, and implement consistent domain events for audit and downstream processing.
Define an API contract that supports list views, detail views, and timeline views. A CRM frequently requires flexible filtering, sorting, pagination, and partial text search. Implement cursor based pagination for large lists. For filtering, define a consistent query format and validate it to prevent unbounded queries. Expose endpoints for bulk actions such as importing contacts, assigning owners, and updating opportunity stages.
typescript 
type CursorPage<T> = {
  items: T[];
  nextCursor: string | null;
};

type ListContactsQuery = {
  q?: string;
  accountId?: string;
  ownerUserId?: string;
  limit?: number;
  cursor?: string;
  sort?: 'created_at' | 'last_name';
  order?: 'asc' | 'desc';
};

// Example route contract
// GET /api/contacts?q=ana&accountId=...&limit=50&cursor=...
// Returns CursorPage<ContactSummary>
export type ContactSummary = {
  id: string;
  accountId: string | null;
  firstName: string;
  lastName: string;
  email: string | null;
  ownerUserId: string | null;
  updatedAt: string;
};
Security must be designed, not added later. Enforce tenant isolation at every query and at every mutation. Apply role based access control (RBAC) with object level permissions such as read, create, update, delete, and export. Add field level controls for sensitive attributes such as personal phone numbers or revenue amounts if your organization requires it. Store passwords using a modern hashing function such as Argon2id. Implement session management with short lived access tokens and rotating refresh tokens or server side sessions with secure cookies. Log security relevant events including logins, permission changes, exports, and bulk updates.
Data quality and auditability are defining CRM features. Maintain an immutable history of important changes such as stage transitions, ownership assignment, and contact email updates. You can implement this with an append only audit_log table or an event stream. Always record who made the change and when it occurred. Provide administrators with basic audit views and export capabilities.
sql 
CREATE TABLE audit_log (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  actor_user_id UUID NOT NULL REFERENCES users(id),
  entity_type TEXT NOT NULL,
  entity_id UUID NOT NULL,
  action TEXT NOT NULL,
  before_json JSONB,
  after_json JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_audit_entity ON audit_log (tenant_id, entity_type, entity_id, created_at);
Search and reporting frequently determine whether users adopt the CRM. Use a relational database for transactional integrity and add a search layer when needed. PostgreSQL full text search can cover many use cases early. For advanced relevance, fuzzy matching, and high volume indexing, consider OpenSearch or Elasticsearch. For reporting, start with optimized SQL queries and materialized views. As reporting needs grow, add an analytics store or a columnar warehouse. Always ensure that reports respect tenant isolation and permission rules.
User experience design should match CRM work patterns. Optimize for high frequency tasks such as searching, opening records, editing key fields, logging activities, and moving opportunities through pipeline stages. Provide fast keyboard friendly forms, inline edits, and clear validation messages. Use a timeline component that consolidates activities, notes, and state changes in chronological order. Avoid complex multi step modals for core flows because they slow down daily usage.
Design integrations as a first class subsystem. The most common CRM integrations include email syncing, calendar syncing, web forms, and accounting tools. Use an outbound webhook system for event notifications and an inbound webhook handler with signature verification. Implement an integrations table to store connection state, encrypted credentials, scopes, and last sync checkpoints. Use job queues for sync work because external APIs are slow and unreliable. Support retry with exponential backoff and dead letter handling.
python 
from dataclasses import dataclass
from datetime import datetime
from typing import Optional

@dataclass
class WebhookEvent:
  tenant_id: str
  event_type: str
  entity_type: str
  entity_id: str
  occurred_at: datetime
  payload: dict

# Pseudocode for enqueueing outbound webhooks

def publish_webhook(event: WebhookEvent, deliver_to_url: str) -> None:
  job = {
    'tenant_id': event.tenant_id,
    'deliver_to_url': deliver_to_url,
    'event': {
      'event_type': event.event_type,
      'entity_type': event.entity_type,
      'entity_id': event.entity_id,
      'occurred_at': event.occurred_at.isoformat(),
      'payload': event.payload
    }
  }
  enqueue('deliver_webhook', job)


def enqueue(queue_name: str, job: dict) -> None:
  # Integrate with your queue system such as Redis, RabbitMQ, or SQS.
  raise NotImplementedError
Plan for imports, exports, and deduplication. CSV import needs validation, mapping, and error reporting. Use a staging table to store raw rows, then validate and transform into canonical objects. For deduplication, implement matching rules such as normalized email, normalized phone, and fuzzy name matching within a tenant. Provide a merge workflow with an explicit survivor record and a merge audit record. Avoid automatic merges without administrator review.
Testing and quality assurance must cover domain rules and multi tenant isolation. Add unit tests for business logic such as stage transition rules and required fields. Add integration tests for API authorization, tenant scoping, and database constraints. Add end to end tests for the primary flows: creating accounts, adding contacts, logging activities, creating opportunities, and updating pipeline stages. Load testing should validate list view performance and search latency at realistic dataset sizes.
Deployment and operations require observability and safe migrations. Use structured logging with correlation identifiers, metrics for latency and error rates, and tracing for slow endpoints and external API calls. Use a migration tool and practice backward compatible schema changes. Add feature flags for risky changes and progressive rollout. Back up the database with tested restore procedures. Provide an incident runbook for common failure modes such as queue backlog, search index drift, and external API throttling.
The Twelve Factor App methodology for building maintainable web services →
OWASP Cheat Sheet Series for secure authentication, session management, and API design →

Database indexing concepts and strategies that apply directly to CRM list and search performance

A CRM built from scratch succeeds when the design prioritizes data integrity, tenant isolation, auditability, fast search, and workflows that match daily operations. Start with a modular monolith, a relational model with explicit activity timelines, and strict authorization. Then expand into integrations, automation, and analytics after the core experience is stable and measurable.