By the end of this chapter you can

• Say what the CRM module covers, what it leaves to other modules and which subscriptions it replaces.
• Explain why an account points at the shared party table instead of holding its own company record.
• Describe what lead conversion creates and what it must never change.
• Name the consent and do-not-contact fields and the written rule that merges duplicates.

What the module replaces

The module manages the revenue pipeline from a first enquiry to a won order: accounts and contacts, leads and how they are qualified, deals that move through stages, the calls, emails and meetings around them, and quotes priced from a price book or estimated from a routing and a bill of materials. It replaces the customer relationship management tools a business usually pays for: Salesforce Sales Cloud, HubSpot Sales Hub, Microsoft Dynamics 365 Sales, Pipedrive, Zoho CRM and Close, and the quoting tools Salesforce Revenue Cloud, DealHub and Paperless Parts. HubSpot describes its data as a small set of linked objects such as contacts, companies and deals ^[1]. This module keeps the same objects, but on the platform's own spine, so a customer is one record that sales, service and billing share.

The specification lists fifteen crm_ tables. This course builds thirteen of them: crm_account, crm_contact, crm_lead, crm_pipeline, crm_pipeline_stage, crm_opportunity, crm_stage_history, crm_contact_role, crm_activity, crm_price_book, crm_price_book_entry, crm_quote and crm_quote_line. Chapter 2 lists every column of the thirteen. The other two, crm_cost_estimate and crm_territory, are explained in chapters 2 and 3 so you can add them later.

A company is stored once

Every business has one list of companies and people. The Foundation spine already holds it as core_party (a company or organisation) and core_person (a human). A CRM account is not a second company record. It is a crm_account row whose party_id points at the one core_party row, one to one, and adds only what sales needs: the account_type (prospect, customer, partner, competitor or distributor), industry, segment, owner, employee and revenue bands, a parent account for company groups, and a lifecycle_stage (target, engaged, opportunity, customer or churned).

A contact is a person at an account: a crm_contact row with its own first and last name, title, email, phone, owner and a lifecycle_stage that runs from subscriber, lead, mql (a marketing qualified lead, someone marketing judges ready for sales) and sql (a sales qualified lead, someone sales has accepted as worth pursuing) through opportunity and customer to evangelist or other. It also points at a core_person of type customer_contact. The shapes (account, contact, lead, opportunity, quote) follow the reference entities in the Microsoft Common Data Model ^[2], and the public vocabularies schema.org Organization ^[3] and schema.org Person ^[4] are what you use when you hand records to another system.

Leads and conversion

A lead comes from a web form or an import with a name, a company, an email, a phone, a source, an owner and the campaign tags in the page address (utm_source, utm_medium and so on) stored as utm. The rep records what was learned while qualifying as a JSON column named qualification. The specification names BANT (budget, authority, need, timing) and MEDDICC (metrics, economic buyer, decision criteria, decision process, identify the pain, champion, competition) as examples; use the fields your business actually asks.

Conversion is one database function that runs as a single transaction (all of its changes happen, or none do). It reuses the core_party if the company already exists or makes it; it reuses the crm_account if that party already has one (an existing customer stays a customer, and a target or engaged account becomes an opportunity account), and otherwise makes it, because the database allows one account per party; it makes a core_person and the contact, makes the opportunity at the first stage with an owner and a next step, marks the lead converted with the three new ids and the time, and writes a crm.lead.converted event. The lead row itself is kept.

Duplicates and consent

Two records for one customer are the most common CRM fault. Decide the rule before loading anything, write it in plain sentences and apply it the same way every time. A reasonable rule: a company is the same if its website address matches, or if its name matches after lowercasing, removing punctuation and dropping endings such as Inc, LLC and Ltd; a contact is the same if its email matches after lowercasing; a contact with no email is never merged by a script and goes to a person to review. Merging moves the dropped record's roles, activities and deals to the kept one and archives the dropped row. Nothing is deleted.

Contact data is personal data. Each contact carries do_not_contact, which is required and must be respected by every screen and route that sends a message, and the lead or contact keeps the wording of the consent shown, the time and the lawful basis relied on. The rules differ by place and by how you contact people: the United States CAN-SPAM Act for commercial email ^[5], the EU General Data Protection Regulation ^[6], and the California Consumer Privacy Act as amended by CPRA ^[7]. Which basis applies to your business is a decision for you and your adviser. The module records the answer you give; it does not invent one.

Exercise · Sort three real records15 minutes

You need: Your current CRM or contact list, a text editor, and your AI coding agent if you want help with the table

Use real records from the tool you pay for today. Do not paste personal details into a chat; describe each record by its type and fields.

1. 1. Pick one company, one person who works at a company, and one enquiry that has not been qualified.
2. 2. For each, write which shape it is: account, contact, lead or opportunity, and the table that holds it.
3. 3. For the company, write the core_party it would be, and check that the same company does not already exist twice in your tool.
4. 4. For the person, write the account they belong to and the lifecycle_stage that fits today.
5. 5. For the enquiry, write its source, its status and the utm tags you hold for it, or note that you hold none.
6. 6. Write your duplicate rule for companies and for contacts in three plain sentences, and check your three records against it.
7. 7. Note, for each person, whether you hold a record of consent and where it is kept.

Outcome: A one-page note with three real records sorted into the module's shapes, a written duplicate rule and the consent you hold for each person.

References

1. HubSpot developers: understanding the CRM. https://developers.hubspot.com/docs/api/crm/understanding-the-crm
2. Microsoft Learn: Common Data Model. https://learn.microsoft.com/en-us/common-data-model/
3. schema.org: Organization. https://schema.org/Organization
4. schema.org: Person. https://schema.org/Person
5. Federal Trade Commission: CAN-SPAM Act, a compliance guide for business. https://www.ftc.gov/business-guidance/resources/can-spam-act-compliance-guide-business
6. EUR-Lex: Regulation (EU) 2016/679, the General Data Protection Regulation. https://eur-lex.europa.eu/eli/reg/2016/679/oj
7. California Attorney General: California Consumer Privacy Act. https://oag.ca.gov/privacy/ccpa

By the end of this chapter you can

• Define a pipeline and its stages with probability, won and lost flags and required fields.
• Explain why storing only the current stage makes velocity and win-rate analysis impossible.
• Rebuild the open pipeline as of a past date from crm_stage_history.
• Calculate win rate, cycle time and coverage from the module's own tables.
• Find every column, type, required flag and allowed value of the thirteen tables, and say who opens each screen.

Pipelines and stages

A crm_pipeline is a named sales process, for opportunities or for leads, and one of them is the default. A crm_pipeline_stage belongs to a pipeline and has a name, a position (seq), a probability, an is_won flag, an is_lost flag and required_fields: the columns that must be filled before a deal may enter the stage, such as amount, close_date or primary_contact_id. Setting probabilities from your own past results is better than copying a vendor's defaults, which describe someone else's business.

A crm_opportunity points at an account, a pipeline and a stage. It has a name, an amount and currency, an expected close_date, a probability that follows the stage, a forecast_category, an owner (required), a source and a next_step. When it is lost it also has a reason from the shared core_reason_code list and, if known, the competitor as a core_party. Two habits keep the pipeline honest: every open deal has an owner and a next step, and no deal is lost without a reason.

Who is on the deal

Real deals have several people. A crm_contact_role links a contact to an opportunity with one role. The seven roles are decision_maker, economic_buyer (holds the budget), champion (argues for you inside), influencer, technical_evaluator, user and blocker (opposes the deal). A deal with no economic buyer or decision maker on it is worth asking about before it is called likely.

The timeline

Calls, emails, meetings, tasks, notes, texts and site visits are crm_activity rows with a subject, a direction (inbound, outbound or internal), an owner, an outcome and a related record, so one list shows everything that happened on an account or a deal. The specification includes Gmail and Outlook sync and reminders; this course leaves sync for later, and keeps an external_id column so a later sync or an import never logs the same message twice.

Keep every stage change

The worst mistake in a home-made CRM is a stage column that is simply overwritten. It looks fine on the board and makes every serious question unanswerable: how long deals sit in each stage, where they are lost, what the pipeline looked like at the end of last quarter. The remedy is a crm_stage_history table that gets one row for every change, with the stage it came from, the stage it went to, when, who, and the amount at that moment.

Let the database write the history, not the screen. A trigger (a rule inside the database that runs by itself when a row changes) on crm_opportunity inserts the row on every insert and every change of stage, so a change made by hand in SQL leaves a row too. A move function adds the business rules: it refuses a stage from another pipeline, refuses the move if a required field is empty and names it, and refuses an open stage with no next step.

When a deal is won, the module sets its forecast_category to closed, makes the account a customer (a partner or distributor keeps its type) and publishes crm.opportunity.won. When it is lost, its forecast_category is also set to closed and a reason is required. Both outcomes are stages with is_won or is_lost set, so they appear in the history like any other move.

Forecast and the five measures

Every deal sits in one forecast category: pipeline, best_case, commit, closed or omitted. Roll-ups add the categories by owner, by territory (a crm_territory row has a name, JSON assignment rules and an owner) and by period. Coverage compares the open pipeline to the quota.

A worked example from the course seed: of six closed deals, four were won. By count the win rate is 4 ÷ 6, about 67%. By value, 13000 of 21000 was won, about 62%. The two differ because the lost deals were not the same size as the won ones. Always say which one you are quoting. The median days from first stage to won in the seed is 51.5.

Every table, column by column

Every table in the module starts with the standard columns every platform table has: id, tenant_id, created_at, created_by, updated_at, updated_by, row_version, archived_at and ext. The table below is the full definition of the thirteen tables this course builds: every column the module adds to the standard ones, with its type and whether it is required (marked req), and the allowed values where the specification lists them. Types are written as in the specification: text, int, num (a decimal number), bool, date, ts (a timestamp with time zone), uuid, json (jsonb in Postgres), money (an amount with fixed decimals, never floating point) and qty (a quantity that allows decimals); an arrow means a foreign key to that table. It is what your agent builds from in Session 2.

Two tables in the specification are not built in this course: crm_cost_estimate (behind a quote line, explained in chapter 3) and crm_territory (name text req, rules json, owner_id → core_person). Add them later the same way.

The screens

Each screen is a row in the Role and Exposure Matrix with the roles that may open it, so the route guard and the menus follow from it. Simple one-table changes, such as claiming a lead or setting the discount threshold, are written straight to the table from the screen, under row-level security.

References

1. HubSpot developers: understanding the CRM. https://developers.hubspot.com/docs/api/crm/understanding-the-crm
2. Metabase: shared sales CRM data model. https://www.metabase.com/integrations/sales-crm
3. Syncari: HubSpot database schema compared with Salesforce. https://syncari.com/blog/hubspot-database-schema/

By the end of this chapter you can

• Price a quote from a price book and check the stored totals against a hand calculation.
• Describe the quote statuses and the moves between them, including approval and versioning.
• Say what acceptance records and what event it publishes for the sales order.
• Build a unit price from cost and a target margin, and tell margin from markup.

Price books

A crm_price_book is a named price list with a currency, an optional segment (a customer group) and an active flag. Each crm_price_book_entry gives an item from the shared core_item list a list_price and, optionally, the dates it is valid from and to. Currencies are written as the three-letter codes in ISO 4217 ^[1]. Keeping prices in one book per currency or segment means a price change is one edit, and a customer-specific or distributor price is another book, not another spreadsheet.

Pricing is the server's job

A quote line holds an item, a description, a quantity, a unit of measure, a unit_price, an optional discount_pct, a line_total, any configuration as JSON and a lead_time_days. The browser may send only the item, the quantity and a discount percentage. The server reads the list price from the active entry valid today, copies it into the line as unit_price and works out every total. A price typed into a browser is never trusted, and because the quote keeps its own copy, a later price book change never alters a quote that was already sent.

Approval, versions and acceptance

A sales manager sets a discount threshold, a percentage that follows your own policy. If any line is above it, the quote cannot go straight to the customer: it moves from draft to in_review and creates an approval request. A manager approves or rejects with a comment, and the person who built the quote cannot approve it, which is the separation of duties auditors look for.

A changed quote is a new row, not an edit: version plus one, the number with -v2 on the end, the lines copied and the old row marked superseded. Sending stores a PDF of the quote and records its id. Acceptance is the customer saying yes, and the module records it: status accepted, the time from the server's clock and a reference to the customer's reply or signed PDF, and it publishes crm.quote.accepted. The event carries the quote number, the total, the currency and one entry for each line. When the order module is installed it turns the event into a sales order, and the module's definition of done requires the order's lines to match the quote.

Estimating a made-to-order price

A shop that makes parts to order does not have a list price for a part it has never made. It builds the price from cost. The crm_cost_estimate table sits behind a quote line and holds the material_cost from the bill of materials, labor_cost, machine_cost, overhead_cost, outside_cost for work sent out, the total_cost, the target_margin_pct and the assumptions as JSON. Its routing_ref is a soft link to the product definition from the manufacturing modules, so the estimate reuses the routing and the bill of materials instead of copying them.

Margin and markup are not the same. A margin is a share of the price; a markup is a share of the cost. For a cost of 80 and a target margin of 20%, the price is 80 ÷ (1 − 0.20) = 100, and the margin is 20 of 100. A markup of 20% on the same cost gives 96, which is a margin of only about 16.7%. These round numbers are for the arithmetic only; use your own rates and costs. Write the assumptions down with the estimate, so that whoever reads the quote later can tell what was assumed about scrap, setup and quantity.

Quote turnaround, the hours from request to quote sent, is the measure the specification calls key for job shops. An estimate built from the routing is how you shorten it without a spreadsheet per customer.

Exercise · Price one quote by hand, then make the system agree20 minutes

You need: One recent real quote from your business, a calculator, and your platform's quote screen or your AI coding agent

Use the numbers from a real quote. Do the arithmetic first, before the system does it.

1. 1. List the lines of the quote: item, quantity, list price and any discount percentage given.
2. 2. Work out each line total (qty x unit price less the discount percentage), then the subtotal (the sum of qty x unit price before discounts), the discount total (the sum of the line discounts) and the total (subtotal less discount total, plus tax if any, which equals the sum of the line totals), to the cent.
3. 3. Write the discount percentage above which a sales manager must approve a quote in your business.
4. 4. Write the allowed status moves for your quotes, using the eight statuses, and who may make each move.
5. 5. Build the quote on your platform sending only the item, the quantity and the discount percentage, and compare the stored subtotal, discount_total and total with your hand figures.
6. 6. Try to approve the quote as the person who built it, and check that the system refuses.
7. 7. Make a changed version and check that the old row is superseded and the new number ends in -v2.

Outcome: A hand calculation that equals the stored totals, a written approval threshold, a refused self-approval and a quote with two versions.

References

1. ISO: ISO 4217, currency codes. https://www.iso.org/iso-4217-currency-codes.html
2. Microsoft Learn: Common Data Model. https://learn.microsoft.com/en-us/common-data-model/
3. Metabase: shared sales CRM data model. https://www.metabase.com/integrations/sales-crm
4. HubSpot developers: understanding the CRM. https://developers.hubspot.com/docs/api/crm/understanding-the-crm

By the end of this chapter you can

• List the roles the module needs and what each may do, and why the rights must cover every later session.
• Name the events the module publishes and consumes.
• Load accounts, contacts, deals, activities and history from an old CRM so reports continue unbroken.
• Run old and new side by side, compare the numbers, and retire the subscription.

Who may do what

Row-level security (the database refusing rows a role should not see) is generated from the Role and Exposure Matrix, so a right that is missing from the matrix is refused later, in the middle of a session that needs it. Write every right the module needs, then walk each later step against the matrix before you build.

A customer has no login, so recording a customer's acceptance is done by a rep, or by a separate acceptance route with its own row in the matrix. Respecting do_not_contact is a rule in the database and on every screen and route that sends a message, not a courtesy.

Events in and out

Modules talk through events written to a table in the same transaction as the change (core_event_outbox), so an event cannot be lost if the change succeeds. The CRM publishes crm.lead.converted, crm.opportunity.stage_changed, crm.opportunity.won and crm.quote.accepted. It consumes cms.web_form.submitted, which becomes a lead, mkt.lead.mql from marketing, and svc.ticket.created, which feeds account health.

Meetings and contacts also travel in standard file formats: a contact as a vCard ^[1] and a meeting as an iCalendar event ^[2]. They matter when you sync with Gmail or Outlook, or send an invitation, because every mail and calendar program reads them.

Moving the data in

Salesforce and HubSpot both export accounts or companies, contacts, deals or opportunities, activities and stage history through an API or as CSV files. Find out which of the five your own plan gives you, and read the vendor's export terms on your own account page. Exports hold personal data: keep them in a private folder outside the repository, and never paste them into a chat. Try everything on a throwaway copy of the database first.

1. Write the mapping. Company becomes a core_party and a crm_account; person becomes a core_person and a crm_contact; deal becomes an opportunity; each activity keeps the old id in external_id. Map every old user to an existing person, and every old stage and lost reason to yours, and stop on any that has no mapping.
2. Carry opt-outs across. Anyone marked unsubscribed or do-not-contact in the old tool gets do_not_contact set to true. Copy consent only if the old tool holds a record of it.
3. Write the duplicate rule first, as in chapter 1, and write skipped duplicates to a report with both source ids. The deals, contacts and activities that point at a skipped duplicate are loaded against the kept record, and an opt-out on a skipped copy is carried to the kept contact, so a person opted out on either copy stays do_not_contact.
4. Keep the source of every record in ext.source_system and ext.source_id, with a unique index over them, so running the import twice skips what is already loaded. A stage change rarely has an id of its own, so a history row's source id is the old deal id, the stage and the time of the change joined together.
5. Load history in full. If the old tool kept only a deal's current stage, load one starting row dated when the deal was created, mark it as having no history, and write down that analytics before the import date cannot be rebuilt.
6. Reconcile. For each file write rows received, loaded, skipped as duplicates and refused, adding up to the file. Check that open pipeline at the export date equals the old tool's report.

Cut over and retire

Run the old tool and the new module side by side for at least a week. Compare open pipeline, win rate, cycle time and forecast, and explain every difference. When they agree, move the team over, cancel the subscription and record the saving: what the old tool cost a year, from its own bill. Costs are only what the specification and your own bills give; the module itself adds none beyond the platform you already run.

Deletion and anonymisation requests from the people in your data must also be handled without breaking the sales record. Under the GDPR ^[3] a person may ask for their personal data to be erased. The module supports this with a function the sales manager runs, crm_anonymise_contact, which replaces the name, email and phone of the contact, its core_person and any lead converted to it with the word Removed, sets do_not_contact and keeps the ids, the opportunities, the amounts and the stage history, so the pipeline and the win rate do not change. Free-text notes may still hold the person's details and are edited by hand. The audit log still holds them as well, because the audit trigger copied the old and new values of every change, names, emails and phones included, into the append-only core_audit_log, and so does any export or backup taken earlier; what to do about those is for the owner and their adviser to decide. What the law requires of your business in a given case is for you and your adviser to decide.

Exercise · Plan the move from your old CRM15 minutes

You need: Access to your old CRM's export page and its bill, and a text editor

Plan only; do not export anything yet. Look at what the tool offers and what you would map.

1. 1. List what your old tool can export: accounts or companies, contacts, deals, activities and stage history, and whether each is by CSV or API.
2. 2. For each, note the plan terms that limit exporting, from your own account page.
3. 3. Write the mapping of your old stage names to the stages in your new pipeline, and your old lost reasons to reasons you keep.
4. 4. Write the duplicate rule for companies and contacts in sentences.
5. 5. Draw the reconciliation table you will fill in: one row per file, with received, loaded, skipped and refused.
6. 6. Write the date you will run both tools side by side, the numbers you will compare, and the date and the saving of cancelling.

Outcome: A one-page migration plan with the export list, the stage and reason mapping, the duplicate rule, the reconciliation table and the cancellation date.

References

1. IETF RFC 6350: vCard Format Specification. https://www.rfc-editor.org/rfc/rfc6350
2. IETF RFC 5545: Internet Calendaring and Scheduling Core Object Specification (iCalendar). https://www.rfc-editor.org/rfc/rfc5545
3. EUR-Lex: Regulation (EU) 2016/679, the General Data Protection Regulation. https://eur-lex.europa.eu/eli/reg/2016/679/oj
4. Syncari: HubSpot database schema compared with Salesforce. https://syncari.com/blog/hubspot-database-schema/

Choose one answer for each question, then submit. You will see the right answer and why for every question.

1. What does crm_account.party_id do?

AIt stores the company's name inside the CRMBIt points at the one core_party row for the company, one to one, so there is no separate company tableCIt links an account to its parent accountDIt records which person owns the account

2. Which of these is a valid crm_lead status?

AwonBlostCopenDnurturing

3. Why keep a stage history row for every stage change?

ASo velocity, win rate and the pipeline as of any past date can be rebuiltBSo the board loads fasterCSo deals cannot be editedDSo the owner can be changed

4. How is the open pipeline as of a past date found?

ARead the opportunity table as it is todayBCount the deals created before that dateCTake each opportunity's last history row at or before that date, leave out won and lost, and group by stageDAsk each rep to remember

5. Which forecast category is set when a deal moves into a won stage?

AcommitBbest_caseCclosedDomitted

6. How is win rate defined?

AWon ÷ (won + lost), by count and by valueBWon ÷ all deals ever createdCLost ÷ wonDOpen pipeline ÷ quota

7. What does pipeline coverage compare?

AWon deals to lost dealsBQuotes sent to quotes acceptedCHours to quote to hours to closeDOpen pipeline in the period to the quota

8. Who decides the unit price on a quote line?

AThe browser, from what the customer typesBThe server, reading the active price book entryCThe rep, by typing it inDThe customer's purchasing system

9. A sent quote is changed. What does the module do?

AEdits the lines of the sent quoteBDeletes the old quoteCChanges the status to draftDMakes a new row with version plus one and marks the old one superseded

10. A job costs 60 and the target margin is 25%. What is the price?

A75, because price = cost × 1.25B80, because price = cost ÷ (1 − 0.25)C85, because price = cost + 25D45, because price = cost × 0.75

11. Why does every imported row keep ext.source_system and ext.source_id with a unique index?

ASo running the import twice skips rows already loaded, and each record can be traced to the old toolBSo the old tool keeps workingCSo personal data is hiddenDSo stages map to the new pipeline

12. What may the lead intake route do?

ARead every leadBConvert a lead to an accountCInsert one crm_lead and nothing elseDApprove a discount

Submit answers