perfex-database

Perfex Database Patterns

You are a Perfex CRM database engineer. Your job is to design module-owned tables and migrations that integrate cleanly with Perfex core — matching signed-INT foreign-key conventions, utf8mb4 collation, idempotent DDL — and to handle real-world schema drift between committed install.php and the production database.

Perfex uses MySQL/MariaDB with InnoDB, utf8mb4_unicode_ci, and a configurable table prefix (default tbl). All custom tables live in the same database as core — namespace them by module name to avoid collisions.

Table naming

tbl<module>_<entity>

Examples: tblmymodule_sessions, tblmymodule_logs. Always use db_prefix() in code — the prefix is user-configurable.

Foreign keys to core tables — the #1 trap

Perfex core uses signed INT, not UNSIGNED. If you create a FK on UNSIGNED INT pointing at tblcontacts.id, MySQL will reject the constraint with "incompatible" error or silently skip it on older MariaDB versions.

-- ❌ WRONG — will fail or silently drop the constraint
CREATE TABLE `tblmymodule_items` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `contact_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_mymodule_contact` FOREIGN KEY (`contact_id`)
    REFERENCES `tblcontacts`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ✅ RIGHT — match core's signed INT
CREATE TABLE `tblmymodule_items` (
  `id`         INT NOT NULL AUTO_INCREMENT,
  `contact_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_mymodule_contact` FOREIGN KEY (`contact_id`)
    REFERENCES `tblcontacts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Core tables that are common FK targets:

Table	PK column	Type
tblcontacts	id	INT
tblstaff	staffid	INT
tblclients	userid	INT
tblinvoices	id	INT
tblcontracts	id	INT
tblleads	id	INT

Charset/collation

Always utf8mb4 / utf8mb4_unicode_ci to match Perfex core. Mismatched collation on a FK column also fails constraint creation.

install.php DDL

<?php
defined('BASEPATH') or exit('No direct script access allowed');

$CI =& get_instance();

if (!$CI->db->table_exists(db_prefix() . 'mymodule_items')) {
    $CI->db->query('
        CREATE TABLE `' . db_prefix() . 'mymodule_items` (
            `id`         INT NOT NULL AUTO_INCREMENT,
            `contact_id` INT NOT NULL,
            `name`       VARCHAR(191) NOT NULL,
            `created_at` DATETIME NOT NULL,
            PRIMARY KEY (`id`),
            KEY `idx_contact` (`contact_id`),
            CONSTRAINT `fk_mymodule_contact` FOREIGN KEY (`contact_id`)
                REFERENCES `' . db_prefix() . 'contacts`(`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ');
}

Always if (!table_exists(...)) — activation hooks can run twice if the admin clicks twice or a module is re-activated.

VARCHAR length: use 191, not 255

MySQL's default utf8mb4 index-key limit is 767 bytes. VARCHAR(255) on a utf8mb4 indexed column overflows. Use VARCHAR(191) on any column that will be indexed (unique keys, FKs, lookups). Non-indexed columns can be longer.

Production drift is real

The install.php committed to the repo is the schema at the moment the module was first activated. Over a multi-year lifespan:

• Columns get added manually via phpMyAdmin
• Columns get renamed on staging and never reconciled
• Indexes disappear after a mysqldump/restore

Before assuming a column exists in production, verify. Use SHOW CREATE TABLE against the live DB. Don't trust install.php. Don't trust even a schema migration log.

Migration pattern — use Perfex's module migrations

Perfex has a module migration system (App_module_migration, since 2.3). Don't roll your own on app_init unless you're repairing drift on an install you can't re-version.

modules/my_module/
├── my_module.php        # header: Version: 1.1.2
└── migrations/
    ├── 110_version_110.php
    ├── 111_version_111.php
    └── 112_version_112.php

// modules/my_module/migrations/110_version_110.php
defined('BASEPATH') or exit('No direct script access allowed');

class Migration_Version_110 extends App_module_migration
{
    public function up()
    {
        $CI = &get_instance();
        if (!$CI->db->field_exists('new_column', db_prefix() . 'mymodule_items')) {
            $CI->db->query('ALTER TABLE `' . db_prefix() . 'mymodule_items` ADD `new_column` VARCHAR(191) NULL');
        }
    }
}

How it works — every one of these is a real trap:

• The target version is the module header Version: with dots stripped: 1.1.0 → 110, 1.1.2 → 112. Filenames must match /^\d{3}_(\w+)$/ and the class is Migration_Version_<n>. Keep the header at three single-digit components or the numbering breaks.
• Pending migration numbers must be consecutive. version() aborts with migration_sequence_gap if two files in the range differ by more than 1 — so 110 → 120 fails outright when an install skips a release. Bump the last version component by one per migration (1.1.0 → 1.1.1 → 1.1.2); core's own bundled modules number 110, 111, 112… for this reason.
• Every Version: bump needs a migration file with exactly that number, even with no schema change. to_latest() calls version(<header number>), which returns migration_not_found and aborts if migrations/<n>_*.php doesn't exist — the Upgrade Database link then errors forever and installed_version never advances. Ship an empty up() for code-only releases:

  class Migration_Version_113 extends App_module_migration { public function up() { /* no schema change in 1.1.3 */ } }
  

• The installed version lives in tblmodules.installed_version (set on first activation from the header), not in tbloptions. Migrations between the installed and target numbers run in filename order, each updating installed_version as it completes — so a failure mid-sequence leaves the module at the last successful step.
• Upgrades are not automatic. When the header version exceeds installed_version, Setup → Modules shows an Upgrade Database link and the admin must click it (admin/modules/upgrade_database/<module>). A deploy alone changes nothing — tell the admin, or your new columns won't exist.
• up() still needs field_exists() / table_exists() guards: a migration that throws halfway through up() leaves installed_version at the previous step, so that same file re-runs in full on the next click — and the DDL that did succeed is now applied twice.
• $this->ci->load->dbforge() is pre-loaded for you; remember dbforge auto-prefixes (see below).

Fallback: app_init self-migration (drift repair only)

If you can't bump the header (e.g. a table was hand-edited on prod and you need to reconcile without a release), an option-gated app_init check works. It runs on every request, so keep it to one cheap get_option() read:

// In module_name.php
hooks()->add_action('app_init', 'my_module_maybe_migrate');

function my_module_maybe_migrate() {
    $installed = get_option('my_module_schema_version') ?: '0';
    if (version_compare($installed, '1.1.0', '<')) {
        my_module_migrate_to_110();
        update_option('my_module_schema_version', '1.1.0');
    }
}

function my_module_migrate_to_110() {
    $CI =& get_instance();
    if (!$CI->db->field_exists('new_column', db_prefix() . 'mymodule_items')) {
        $CI->db->query('ALTER TABLE `' . db_prefix() . 'mymodule_items` ADD `new_column` VARCHAR(191) NULL');
    }
}

Always check field_exists() / table_exists() before DDL — migrations MUST be idempotent. app_init fires on every page load.

The dbforge->add_column prefix trap

CI3's dbforge->add_column() auto-prepends $this->db->dbprefix to the table name. If you also pass db_prefix(), you get a double prefix and the query fails silently or errors on a non-existent table.

// ❌ WRONG — produces ALTER TABLE `tbltblitems`
$this->dbforge->add_column(db_prefix() . 'items', [
    'new_col' => ['type' => 'VARCHAR(191)', 'null' => true],
]);

// ✅ RIGHT — dbforge adds the prefix itself
$this->dbforge->add_column('items', [
    'new_col' => ['type' => 'VARCHAR(191)', 'null' => true],
]);

Note: $this->db->list_fields() does NOT auto-prefix — you must pass db_prefix() . 'tablename' there. The inconsistency is a CI3 quirk.

// list_fields needs the prefix, add_column does not
$columns = $this->db->list_fields(db_prefix() . 'items');     // ✅
$this->dbforge->add_column('items', $field);                   // ✅

Query builder vs raw SQL

Prefer CI's query builder — it parameterizes automatically:

// ✅ safe
$CI->db->where('contact_id', $id);
$CI->db->insert(db_prefix() . 'mymodule_items', $data);

// ❌ SQL injection risk
$CI->db->query("SELECT * FROM " . db_prefix() . "mymodule_items WHERE id = $id");

If you must use raw SQL (complex JOINs, DDL), use $CI->db->escape() or bind parameters:

$CI->db->query('SELECT * FROM `' . db_prefix() . 'mymodule_items` WHERE id = ?', [$id]);

Atomic updates for race safety

Whenever you're consuming a one-time token or claiming a lock, update-then-check:

$CI->db->where('token', $token);
$CI->db->where('used', 0);
$CI->db->update(db_prefix() . 'mymodule_tokens', ['used' => 1, 'used_at' => date('Y-m-d H:i:s')]);

if ($CI->db->affected_rows() !== 1) {
    // token was already consumed in a concurrent request
    return false;
}

See perfex-security for the full token lifecycle pattern.

list_fields() vs field_exists() — choosing the right check

Both verify column existence, but they serve different purposes:

// field_exists — checks a single column, cheap, returns bool
if (!$CI->db->field_exists('new_col', db_prefix() . 'mymodule_items')) {
    // add the column
}

// list_fields — returns ALL column names as array, one SHOW COLUMNS query
$columns = $CI->db->list_fields(db_prefix() . 'mymodule_items');
if (!in_array('new_col', $columns)) {
    // add the column
}

Use field_exists() when checking one or two specific columns (migrations, guards). Use list_fields() when you need to check multiple columns in a loop — one query beats N field_exists() calls. Both require db_prefix() in the table name (unlike dbforge).

Dynamic column pattern (multi-currency pricing)

Perfex core uses a dynamic column pattern for per-currency item pricing. Instead of a separate table, it adds rate_currency_{currency_id} columns to tblitems on demand:

$columns = $this->db->list_fields(db_prefix() . 'items');
$this->load->dbforge();

foreach ($currencies as $currency) {
    $col = 'rate_currency_' . $currency['id'];
    if ($currency['isdefault'] == 0 && !in_array($col, $columns)) {
        $this->dbforge->add_column('items', [
            $col => [
                'type' => 'decimal(15,' . get_decimal_places() . ')',
                'null' => true,
            ],
        ]);
    }
}

Key behaviors:

• Base currency price lives in the rate column; non-base currencies get rate_currency_X
• Columns are created on first save via dbforge (see Invoice_items_model::add())
• When a currency is deleted, Currencies_model::delete() drops the matching column
• Import/export discovers columns via list_fields() — columns must exist before import can populate them
• The model detects these columns by prefix: strpos($column, 'rate_currency_') !== false

This pattern works for any feature where you need per-entity pricing across a small, admin-managed set of variants. Don't use it for high-cardinality dimensions — a junction table is better past ~10 columns.

Backup before destructive ops

Before any ALTER TABLE, DROP COLUMN, or UPDATE without WHERE, dump the target table:

mysqldump -u USER -p DB tblmymodule_items > /tmp/pre_migration_$(date +%s).sql

Related skills

• perfex-module-dev — install.php is where module schema lives; this skill covers the DDL inside it.
• perfex-customfields — tblcustomfields schema quirks (only_admin, the disalow_client_to_edit typo) that affect DDL generation.
• perfex-security — the atomic-UPDATE-with-affected_rows() pattern for race-safe token consumption.

Upstream docs

• CI3 database: https://codeigniter.com/userguide3/database/
• MySQL utf8mb4 index limit: https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html

---

Verified against Perfex CRM 3.4.0 core source on 2026-09-15 with scripts/verify-against-core.sh. Version-specific notes in the text are from official changelogs.