SQL in one place. Business logic defined once. Every future feature that touches leads faster to build and harder to break.
A CRM schema is only as useful as the code that reads from it. The anti-pattern: SQL scattered across 15 different files, each slightly different, each maintained independently. One file uses $wpdb->prepare() correctly; another doesn't. One selects specific columns; another does SELECT *. When the schema changes, you find every instance by searching for partial SQL strings and hoping you got them all.
By the time the Anchor build's lead engine was operational, lead-related queries had spread to: the form submission handler, the admin leads list page, the lead notification email builder, a REST API endpoint for the customer portal, and a draft AI summary feature. Each was written independently. Three of them had slightly different interpretations of what "hot lead" meant. Two had no SQL injection protection.
The refactor: extract every query into a shared library, establish consistent patterns, and make all callers use the library.
One file — [client]_lead_helpers.php — included once via require_once in the plugin's main file. Eight core functions:
[client]_create_lead() — INSERT a new lead, return ID
[client]_get_lead_by_id() — SELECT single lead by ID
[client]_get_lead_by_email() — SELECT single lead by email (dedup check)
[client]_update_lead_status() — UPDATE status with state machine validation
[client]_get_hot_leads() — SELECT leads needing follow-up
[client]_get_leads_by_source() — SELECT leads filtered by utm_source
[client]_add_conversation() — INSERT conversation record + append activity
[client]_get_lead_timeline() — SELECT lead + conversations for detail view
function [client]_create_lead( array $data ): int {
global $wpdb;
$defaults = [
'first_name' => '',
'last_name' => '',
'email' => '',
'phone' => '',
'status' => 'new',
'source_form' => 'general',
'utm_source' => '',
'utm_medium' => '',
'utm_campaign' => '',
'referrer' => '',
'landing_page' => '',
'activity' => wp_json_encode( [] ),
];
$insert = wp_parse_args( $data, $defaults );
// Check for existing lead — update rather than duplicate
$existing = [client]_get_lead_by_email( $insert['email'] );
if ( $existing ) {
[client]_append_activity(
$existing->id,
'repeat_visit',
'Submitted ' . $insert['source_form'] . ' form again'
);
return $existing->id;
}
$wpdb->insert( '[client]_leads', $insert );
$lead_id = (int) $wpdb->insert_id;
[client]_append_activity( $lead_id, 'created', 'Lead created via ' . $insert['source_form'] );
return $lead_id;
}
function [client]_get_hot_leads( int $limit = 25 ): array {
global $wpdb;
return $wpdb->get_results( $wpdb->prepare(
"SELECT l.*
FROM [client]_leads l
WHERE l.status IN ('new','contacted')
AND l.created_at >= DATE_SUB(NOW(), INTERVAL 72 HOUR)
ORDER BY
CASE l.status
WHEN 'new' THEN 0
WHEN 'contacted' THEN 1
END ASC,
CASE l.source_form
WHEN 'credit_application' THEN 0
WHEN 'financing' THEN 1
WHEN 'unit_inquiry' THEN 2
ELSE 3
END ASC,
l.created_at ASC
LIMIT %d",
$limit
) );
}
The priority logic is encoded once: new before contacted, credit apps before financing before general. Every part of the system that shows a "needs attention" list uses this function. When the definition of "hot" changes — for example, extending the window from 72 to 96 hours — you edit one function.
function [client]_update_lead_status( int $lead_id, string $new_status, string $changed_by = 'system' ): bool {
global $wpdb;
$lead = [client]_get_lead_by_id( $lead_id );
if ( ! $lead ) return false;
if ( ! [client]_valid_status_transition( $lead->status, $new_status ) ) {
error_log( "[client] Invalid status transition: {$lead->status} → {$new_status} on lead #{$lead_id}" );
return false;
}
$wpdb->update(
'[client]_leads',
[ 'status' => $new_status ],
[ 'id' => $lead_id ]
);
[client]_append_activity( $lead_id, 'status_change', "Status: {$lead->status} → {$new_status} by {$changed_by}" );
return true;
}
function [client]_get_lead_timeline( int $lead_id ): array {
$lead = [client]_get_lead_by_id( $lead_id );
$conversations = [client]_get_conversations_for_lead( $lead_id );
$owned_units = [client]_get_owned_units_for_lead( $lead_id );
return [
'lead' => $lead,
'conversations' => $conversations,
'owned_units' => $owned_units,
'activity' => json_decode( $lead->activity ?? '[]', true ),
];
}
The admin lead detail page calls [client]_get_lead_timeline() once and renders the complete picture. Three queries total. The detail page renders fast enough without a cache layer because it's always a single-record view.
Centralizing queries means: SQL injection protection is applied consistently (every query uses $wpdb->prepare()), column selection is consistent, and business logic like "what makes a lead hot" is defined once. The library also makes the AI layer possible — the AI lead summary feature reads a complete lead timeline through [client]_get_lead_timeline(). If that function didn't exist, the AI feature would need its own SQL, with its own edge cases, maintained separately.
The deduplication logic in create_lead() caught approximately 15% of submissions in the first month — people who submitted both the unit inquiry form and the general contact form for the same unit. Without dedup, that's 15% inflated lead counts and 15% duplicate notification emails to the sales team.
The lead helpers library is at 8 core functions and approximately 250 lines of PHP. It has not needed a rewrite since Era 2. Every module built after it — email engine, customer portal, AI layer, SMS integration — uses it as the CRM interface without knowing the schema directly.
$wpdb->prepare(). Never interpolate PHP variables directly into SQL strings. $wpdb->prepare("WHERE id = %d", $id) is the right pattern for every single query.error_log(). Silent failures in the state machine produce inconsistent data that's hard to diagnose later. Log it even when you don't throw an exception.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.
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 →