Lesson 3.3: 3-Table CRM Schema

Most small businesses need a CRM, but they don't need Salesforce. They need something that tracks who contacted them, what was said, what was purchased, and where the relationship stands. Building that as a custom schema inside WordPress gives you data ownership, query flexibility, and zero per-seat licensing fees.

The situation

The Anchor build's customer data before Era 2 was in three places simultaneously: an email inbox, a spreadsheet maintained manually by the front desk, and WordPress's comment table (which was being misused as a notes system). No single place had the complete picture. Staff cross-referenced three systems to answer "what's the status on the Smith deal?"

Consolidating into a purpose-built schema meant: one place to look, queryable data, and a clear model for how relationships evolve over time.

What I did

Three tables, three questions

The schema answers three questions:

Who are our leads and where do they stand? ([client]_leads)
What has been communicated with each lead? ([client]_conversations)
What has each customer purchased or consigned? ([client]_owned_units)

leads table — one record per person

CREATE TABLE [client]_leads (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name      VARCHAR(100)     NOT NULL DEFAULT '',
    last_name       VARCHAR(100)     NOT NULL DEFAULT '',
    email           VARCHAR(200)     NOT NULL DEFAULT '',
    phone           VARCHAR(30)      NOT NULL DEFAULT '',
    status          ENUM('new','contacted','qualified',
                         'closed_won','closed_lost','nurture')
                                     NOT NULL DEFAULT 'new',
    source_form     VARCHAR(50)      NOT NULL DEFAULT '',
    source_page     VARCHAR(500)     NOT NULL DEFAULT '',
    source_unit_id  BIGINT UNSIGNED  NOT NULL DEFAULT 0,
    utm_source      VARCHAR(200)     NOT NULL DEFAULT '',
    utm_medium      VARCHAR(200)     NOT NULL DEFAULT '',
    utm_campaign    VARCHAR(200)     NOT NULL DEFAULT '',
    utm_term        VARCHAR(200)     NOT NULL DEFAULT '',
    utm_content     VARCHAR(200)     NOT NULL DEFAULT '',
    referrer        VARCHAR(500)     NOT NULL DEFAULT '',
    landing_page    VARCHAR(500)     NOT NULL DEFAULT '',
    notes           TEXT,
    activity        JSON,
    created_at      DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP
                                     ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status     (status),
    INDEX idx_email      (email),
    INDEX idx_created_at (created_at),
    INDEX idx_source     (utm_source, utm_medium)
);

conversations table — every touchpoint on record

CREATE TABLE [client]_conversations (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id     BIGINT UNSIGNED NOT NULL,
    type        ENUM('email_out','email_in','sms_out','sms_in',
                     'call','note','system')
                                NOT NULL DEFAULT 'note',
    subject     VARCHAR(300)    NOT NULL DEFAULT '',
    content     TEXT            NOT NULL,
    created_by  VARCHAR(100)    NOT NULL DEFAULT '',
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lead_id   (lead_id),
    INDEX idx_created_at(created_at),
    FOREIGN KEY (lead_id) REFERENCES [client]_leads(id) ON DELETE CASCADE
);

owned_units table — category-agnostic ownership records

CREATE TABLE [client]_owned_units (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id      BIGINT UNSIGNED NOT NULL,
    unit_id      BIGINT UNSIGNED NOT NULL,  -- wp_posts ID
    relationship ENUM('buyer','consignor','service')
                                 NOT NULL DEFAULT 'buyer',
    status       ENUM('active','completed','cancelled')
                                 NOT NULL DEFAULT 'active',
    acquired_at  DATETIME,
    completed_at DATETIME,
    notes        TEXT,
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lead_id (lead_id),
    INDEX idx_unit_id (unit_id),
    FOREIGN KEY (lead_id) REFERENCES [client]_leads(id) ON DELETE CASCADE
);

The status state machine

The status field is a state machine, not a free-text field. Free-text status fields produce garbage data: "closed," "Closed," "closed - won," "sold," "SOLD" all mean the same thing but can't be reported on consistently.

Valid transitions:

new → contacted → qualified → closed_won
new → contacted → qualified → closed_lost
new → contacted → nurture (not ready to buy, follow up later)
new → closed_lost (immediate disqualification)

State machine enforcement in PHP

function [client]_valid_status_transition( string $from, string $to ): bool {
    $allowed = [
        'new'       => [ 'contacted', 'closed_lost' ],
        'contacted' => [ 'qualified', 'nurture', 'closed_lost' ],
        'qualified' => [ 'closed_won', 'closed_lost', 'nurture' ],
        'nurture'   => [ 'contacted', 'closed_lost' ],
    ];

    return in_array( $to, $allowed[ $from ] ?? [], true );
}

JSON activity timeline

The activity column stores a JSON array of timestamped events — a denormalized append-only log. This is faster to read for the "show me everything about this lead" view than joining conversations + owned_units + a separate status_history table.

Activity append — called on every status change, conversation, and unit link

function [client]_append_activity( int $lead_id, string $type, string $description ) {
    global $wpdb;
    $lead     = [client]_get_lead_by_id( $lead_id );
    $timeline = json_decode( $lead->activity ?? '[]', true );

    $timeline[] = [
        'type'       => $type,
        'desc'       => $description,
        'timestamp'  => current_time( 'mysql' ),
        'created_by' => wp_get_current_user()->display_name ?: 'system',
    ];

    $wpdb->update(
        '[client]_leads',
        [ 'activity' => wp_json_encode( $timeline ) ],
        [ 'id' => $lead_id ]
    );
}

Why it matters

The three-table design gives you one canonical customer record (not split between an email inbox, a spreadsheet, and a comment thread), full conversation history in one place in order, clear ownership relationships (when a customer buys two units over three years, both link to the same lead record), and queryable data for attribution and reporting.

The schema is category-agnostic by design. The owned_units table references a WP post ID and a relationship type. The schema doesn't know whether that post is a boat, an insurance policy, a real estate listing, or a service contract. The post type determines that — the CRM schema doesn't need to.

The Anchor build

The leads table has logged every lead since Era 2. The conversations table has a complete record of every email sequence, every SMS thread, and every staff note. The owned_units table links every sale back to the original lead record.

The data enabled the AI lead summary feature (Module 7), which reads the full activity timeline and conversations history and generates a concise status brief for each lead. It enabled the customer portal (Module 5), which queries owned_units to populate the "my purchases" tab. Both features were built against the same schema without modification.

Do this, not that

Use custom tables, not WordPress post types, for CRM data. Post types are for content. Lead records are relational data. Custom tables with proper indexes, foreign keys, and ENUM status fields are the right tool.
Enforce the state machine in PHP, not just in the UI. A UI dropdown that prevents invalid transitions is easily bypassed via direct API calls. The PHP validation layer is what makes the data trustworthy for reporting.
Index every column you'll query against. Status, email, created_at, and UTM fields all need indexes. An unindexed WHERE clause on a 10,000-row lead table is slow. On 100,000 rows it's unusable.
Use the JSON activity column for the lead detail view. The full history of a lead rendered from a single JSON column is faster than three JOIN queries and simpler to cache.
Keep the schema category-agnostic. The owned_units table references a post ID, not a unit type. The schema should not need to change when you add a new inventory category.

← Previous Lesson 3.2 — Source Attribution Next → Lesson 3.4 — Lead Helpers Library

When you’re ready to build

The lessons are yours. When you want it built, we’re here.

Every lesson stays free — no account, no paywall, no email gate, ever. But if you’d rather have this system standing on your business than wire all 48 lessons yourself, leave your email. We’ll send you a direct line to a build — and you’ll be first to hear when we add new tools to the curriculum.

None of this gates a single lesson. The curriculum was free before you got here and it stays that way.

Done learning how it’s built? We’ll build it.

You came here to understand the system, and now you do. If you’d rather have it standing on your business than spend the next three months wiring it yourself, GAP Concierge is the same architecture from these lessons — a white-label AI agent that knows your catalog and captures your leads — set up for you, from $97/mo.

See GAP Concierge →