Building a CRM from scratch, kinda

How I basically ended up making a CRM from scratch.

The company I work for used to have all of their student information recorded on paper and random spreadsheets (Office 365 and Google Sheets sometimes). It was a huge mess and I was hired to fix it.

At the time I was already a little experienced in web dev, mostly making marketing websites for clients of my freelance business. I used the typical React and Nextjs slop that every new web dev is taught to use. So, nothing too backend-heavy. Using an already-made CRM was the right choice at the time.

Creating my own database made me shudder at the time so I searched around for a free and open source CRM. I found one called EspoCRM and it was a great fit. It was light on resources so I spun up a container on a crappy $5/mo VPS and here’s how it went.

Initial requirements

Right, so I was hired to transfer all physical and digital info on our students to one place, and to make that info easy to retrieve and modify. Seemed like a pretty simple job at first, just setup the right tables in EspoCRM and start entering data.

This is what I was working with, but with a million more rows and about half the data was missing, spread across Slack messages, paper notes, and other spreadsheets.

With a lot of communication I eventually got all the requirements to create all the record types in the CRM. I then got to work importing all the data we had.

Requirements change

This project was initially a big success. We got to thinking, what could we automate?

1. At the end of the day, trainers will write down everything a student did, on paper. Afterwards they’ll send a picture of it in a Slack channel for someone to transcribe it into our CRM. This is very painful and makes my soul ache.
2. We send out weekly progress report PDFs to our clients about how their students are doing, and what they did. Currently this is done manually through Microsoft W*rd.

At the time, I thought recording activity for a student would be pretty simple. Little did I know.

First, every student has a record in the database. This includes things like their DOB, age, status, etc.

Second, when using EspoCRMs built-in PDF generation, you would select one record and generate a PDF based on fields only stored in that selected record. So, you couldn’t just print out a report for John Smith and have the report access stuff about our truck records. Fine, not a problem, we’ll just store each student’s daily journal in their own record so that all the info is in one place, and then we can run off PDF generation.

Here comes the jank.

It was simple, I have a field called journal on every student record that would store the activity for each day. journal was an array of strings, with a YYYY-MM-DD date in the front:

student.journal = [
  "2023-01-01: John did great today",
  "2023-01-02: John did backing maneuvers and did well today",
  "2023-01-03: John did self study today",
]

This worked okay until it didn’t.

Requirements changed, we needed to generate a mass PDF that included all students in one document (a weekly progress report, if you will). But, I can only generate a PDF of one student at a time in EspoCRM and I need to make one with every student combined.

After some futzing with the PDF generation, I decided there was only one course of action. Time to go hog-wild and create a whole custom frontend for this CRM, yeehaw.

The custom frontend

I had only ever done serious web work with React, Nextjs, etc. So the idea of doing something like this for each form was nauseating.

import React from 'react';
import { Formik, Field, Form } from 'formik';

const Basic = () => (
  <Formik
    initialValues={{
      firstName: '',
      lastName: '',
      email: '',
    }}
    onSubmit={async (values) => {
      // fetch here
    }}
  >
    <Form>
      <Field id="firstName" name="firstName" placeholder="Jane" />
      <Field id="lastName" name="lastName" placeholder="Doe" />
      <Field
        id="email"
        name="email"
        placeholder="jane@acme.com"
        type="email"
      />
      <button type="submit">Submit</button>
    </Form>
  </Formik>
);

This might have been okay for maybe a while, but if I had gone this path I would have gone insane. There ended up being a bazillion forms for my custom frontend, so thankfully I had the foresight to pivot.

Enter HTMX. HTMX is a very cool library. The rest of this article is shilling for HTMX, fair warning. HTMX can turn that chunk of crap from above into something like this:

<form hx-post="/email-submission">
  <input name="firstName" />
  <input name="lastName" />
  <input name="email" type="email" placeholder="jane@acme.com" />
  <button type="submit">Submit</button>
</form>

Look at that. Elegant. Simple. No JSX in site. No import this or require that. Just me, an hx-post attribute, and my server sending HTML over the wire just as God intended.

How embarassing… You’ve reached the end of the rough draft of this article so far. Enjoy this while it lasts because this article will be finished soon :)