# Perfex Database

> Use whenever the user writes SQL DDL for a Perfex CRM module, adds a foreign key referencing `tblcontacts`, `tblstaff`, `tblclients`, `tblinvoices`, or any `tbl*` core table, designs `tbl<module>_<entity>` schema, writes `install.php` / `uninstall.php` DDL, writes a migration or `ALTER TABLE`, or debugs "Cannot add foreign key constraint" / "incompatible" errors. Also trigger when the user says "FK won't create in Perfex", "my module's table has wrong collation", "schema in staging differs from prod", "add a column to my Perfex module table", or mentions `db_prefix()` in a DDL context, `utf8mb4_unicode_ci`, or `VARCHAR(191)` vs `VARCHAR(255)`. Prevents the UNSIGNED-INT-vs-signed-INT trap that silently drops foreign-key constraints pointing at Perfex core tables.

- Skill: `yasserstudio/perfex-database` (Agent Skill)
- Install (CLI): `npx skillmds@latest add yasserstudio/perfex-database`
- Raw SKILL.md: https://api.skillmd.com/api/skills/yasserstudio/perfex-database/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- License: MIT
- Author: yasserstudio (https://skillmd.com/u/yasserstudio)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/yasserstudio/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.

```sql
-- ❌ 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
<?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

Perfex has no built-in migration system. Roll your own:

```php
// 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. Admins will re-run `app_init` 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.

```php
// ❌ 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.

```php
// 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:

```php
// ✅ 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:
```php
$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:

```php
$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:

```php
// 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:

```php
$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:
```bash
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