Database Schema

The Pro version of the ID Payment Link Generator creates three custom database tables to store customers, email logs, and email templates. This page documents the full schema, including column types, indexes, and the CRUD classes you can use to interact with the data programmatically.

Pro Feature: Custom database tables are only created when the Pro version is activated. The free version stores only its settings in the idplg_settings option and does not create any custom tables.

Table Creation & Versioning

Tables are created (or updated) during plugin activation via the IDPLG_Database::install() method, which uses WordPress’s dbDelta() function. This ensures safe, incremental schema changes — new columns can be added without losing existing data.

The current schema version is tracked in the idplg_db_version option. On each activation, the plugin compares the stored version against the code’s version and runs dbDelta() if an update is needed.

---

Table: {prefix}idplg_customers

Stores customer contact information for quick selection when generating payment links.

Column	Type	Attributes	Description
id	bigint(20) unsigned	PRIMARY KEY, AUTO_INCREMENT	Unique customer identifier
first_name	varchar(100)	NOT NULL, DEFAULT ”	Customer’s first name
last_name	varchar(100)	NOT NULL, DEFAULT ”	Customer’s last name
email	varchar(200)	NOT NULL, DEFAULT ”, INDEXED	Customer’s email address
company	varchar(200)	NOT NULL, DEFAULT ”	Company or organization name
phone	varchar(50)	NOT NULL, DEFAULT ”	Phone number
notes	text		Internal notes about the customer
created_at	datetime	NOT NULL	Record creation timestamp
updated_at	datetime	NOT NULL	Last update timestamp

Indexes:

• PRIMARY KEY (id)
• KEY email (email) — Speeds up customer search and duplicate detection.

---

Table: {prefix}idplg_log

Records every email send attempt, including both successful and failed deliveries. Stores a snapshot of the data at the time of sending rather than referencing the customer table, so log entries remain accurate even if customer details are later changed.

Column	Type	Attributes	Description
id	bigint(20) unsigned	PRIMARY KEY, AUTO_INCREMENT	Unique log entry identifier
customer_id	bigint(20) unsigned	NULLABLE, INDEXED	Associated customer ID (NULL if not linked)
first_name	varchar(100)	NOT NULL, DEFAULT ”	Recipient’s first name (snapshot)
last_name	varchar(100)	NOT NULL, DEFAULT ”	Recipient’s last name (snapshot)
email	varchar(200)	NOT NULL, DEFAULT ”	Recipient’s email address (snapshot)
amount	varchar(50)	NOT NULL, DEFAULT ”	Payment amount (as entered by admin)
payment_link	text		The full generated payment link URL
email_subject	varchar(500)	NOT NULL, DEFAULT ”	The email subject line used
status	varchar(20)	NOT NULL, DEFAULT ‘sent’, INDEXED	Delivery status: 'sent' or 'failed'
created_at	datetime	NOT NULL, INDEXED	Timestamp of the send attempt

Indexes:

• PRIMARY KEY (id)
• KEY customer_id (customer_id) — Enables efficient lookup of all emails sent to a specific customer.
• KEY status (status) — Supports filtering by sent/failed status.
• KEY created_at (created_at) — Supports date range queries and chronological sorting.

---

Table: {prefix}idplg_email_templates

Stores reusable email templates with placeholder support. One template can be marked as the default, which is automatically selected on the generator page.

Column	Type	Attributes	Description
id	bigint(20) unsigned	PRIMARY KEY, AUTO_INCREMENT	Unique template identifier
name	varchar(200)	NOT NULL, DEFAULT ”	Template display name
subject	varchar(500)	NOT NULL, DEFAULT ”	Email subject line (supports placeholders)
body	longtext		Email body HTML (supports placeholders)
is_default	tinyint(1)	NOT NULL, DEFAULT 0, INDEXED	Whether this is the default template (1 = yes)
created_at	datetime	NOT NULL	Record creation timestamp
updated_at	datetime	NOT NULL	Last update timestamp

Indexes:

• PRIMARY KEY (id)
• KEY is_default (is_default) — Quick lookup of the default template.

---

CRUD Classes

The plugin provides three static CRUD classes for interacting with the database tables. All classes use $wpdb with prepared statements to prevent SQL injection.

IDPLG_Customers

Handles all customer data operations.

• IDPLG_Customers::get( $id ) — Returns a single customer object or null.
• IDPLG_Customers::search( $term ) — Searches customers by name, email, or company. Returns array of objects.
• IDPLG_Customers::save( $data ) — Inserts (if $data['id'] is 0) or updates a customer. Returns the customer ID.
• IDPLG_Customers::delete( $id ) — Deletes a customer by ID. Returns true on success.
• IDPLG_Customers::get_all( $args ) — Retrieves customers with pagination, sorting, and search. Returns array.
• IDPLG_Customers::count( $search ) — Returns total customer count, optionally filtered by search term.

IDPLG_Log

Handles email log operations.

• IDPLG_Log::add( $data ) — Inserts a new log entry. Returns the log ID.
• IDPLG_Log::get( $id ) — Returns a single log entry object or null.
• IDPLG_Log::get_all( $args ) — Retrieves log entries with filtering, pagination, and sorting.
• IDPLG_Log::count( $args ) — Returns total log count with optional filters.
• IDPLG_Log::get_for_customer( $customer_id ) — Returns all log entries for a specific customer.

IDPLG_Email_Templates

Handles email template operations.

• IDPLG_Email_Templates::get( $id ) — Returns a single template object or null.
• IDPLG_Email_Templates::get_default() — Returns the default template object.
• IDPLG_Email_Templates::get_all() — Returns all templates, ordered by name.
• IDPLG_Email_Templates::save( $data ) — Inserts or updates a template. Returns the template ID.
• IDPLG_Email_Templates::delete( $id ) — Deletes a template by ID. Returns true on success.

---

Direct Database Query Example

While using the CRUD classes is recommended, you can also query the tables directly with $wpdb for advanced use cases:

<?php
/**
 * Example: Query customers directly using $wpdb.
 *
 * This demonstrates how to perform a custom query against
 * the idplg_customers table with proper prepared statements.
 */
global $wpdb;

$table_name = $wpdb->prefix . 'idplg_customers';

// Get all customers with a specific email domain.
$domain = 'example.com';
$customers = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT id, first_name, last_name, email, company
         FROM {$table_name}
         WHERE email LIKE %s
         ORDER BY last_name ASC, first_name ASC",
        '%@' . $wpdb->esc_like( $domain )
    )
);

if ( $customers ) {
    foreach ( $customers as $customer ) {
        printf(
            "Customer #%d: %s %s (%s) - %s\n",
            $customer->id,
            $customer->first_name,
            $customer->last_name,
            $customer->email,
            $customer->company ?: 'No company'
        );
    }
} else {
    echo "No customers found for domain: {$domain}\n";
}

/**
 * Example: Get email send statistics for the last 30 days.
 */
$log_table = $wpdb->prefix . 'idplg_log';

$stats = $wpdb->get_row(
    $wpdb->prepare(
        "SELECT
            COUNT(*) as total,
            SUM( CASE WHEN status = 'sent' THEN 1 ELSE 0 END ) as sent,
            SUM( CASE WHEN status = 'failed' THEN 1 ELSE 0 END ) as failed
         FROM {$log_table}
         WHERE created_at >= %s",
        gmdate( 'Y-m-d H:i:s', strtotime( '-30 days' ) )
    )
);

printf(
    "Last 30 days: %d total emails, %d sent, %d failed (%.1f%% success rate)\n",
    $stats->total,
    $stats->sent,
    $stats->failed,
    $stats->total > 0 ? ( $stats->sent / $stats->total * 100 ) : 0
);

/**
 * Example: Find customers who haven't been emailed in 90 days.
 */
$inactive_customers = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT c.id, c.first_name, c.last_name, c.email,
                MAX(l.created_at) as last_email_date
         FROM {$table_name} c
         LEFT JOIN {$log_table} l ON c.id = l.customer_id
         GROUP BY c.id
         HAVING last_email_date IS NULL
            OR last_email_date < %s
         ORDER BY last_email_date ASC",
        gmdate( 'Y-m-d H:i:s', strtotime( '-90 days' ) )
    )
);

foreach ( $inactive_customers as $customer ) {
    printf(
        "%s %s (%s) - Last emailed: %s\n",
        $customer->first_name,
        $customer->last_name,
        $customer->email,
        $customer->last_email_date ?: 'Never'
    );
}

Important: Always use $wpdb->prepare() for any queries that include user input. Never concatenate variables directly into SQL strings. The CRUD classes handle this automatically, but direct queries require manual preparation.

Table Prefix Note

Always use $wpdb->prefix to get the correct table prefix. This is especially important on WordPress multisite installations where each site has a unique prefix (e.g., wp_2_idplg_customers). Never hardcode wp_ as the prefix.