Models & schema

A Tiger model is a table-data gateway — one class per table, extending Tiger_Model_Table. The base carries the enterprise boilerplate every domain table needs (UUID keys, timestamps, actor stamps, soft-delete) so your subclass is just a table name plus the finders your feature actually needs.

Extend Tiger_Model_Table, never Zend_Db_Table_Abstract

A model declares its table ($_name) and primary key ($_primary) and builds domain finders on top of the base:

class Billing_Model_Invoice extends Tiger_Model_Table
{
    protected $_name    = 'invoice';
    protected $_primary = 'invoice_id';

    /** Open invoices for an org, newest first — built on activeSelect() so deleted rows stay hidden. */
    public function openForOrg(string $orgId): array
    {
        $select = $this->activeSelect()
            ->where('org_id = ?', $orgId)
            ->where('status = ?', 'open')
            ->order('created_at DESC');
        return $this->fetchAll($select)->toArray();
    }
}

Metadata loads lazily, so the class is cheap to construct without a DB.

UUID primary keys — minted in PHP on insert

insert() mints the primary key itself (you never pass it) and returns the UUID string — not ZF1's lastInsertId(), which is meaningless for a client-generated string PK:

$id = (new Billing_Model_Invoice())->insert(['org_id' => $orgId, 'amount' => 4200]);
// $id === 'x' — a 36-char lowercase UUID

The default is UUID v7 (time-ordered — its leading 48 bits are a millisecond timestamp, so inserts append near the right edge of the index like an auto-increment, and rows sort chronologically by PK). For a table whose id must not leak its creation time — tokens, secrets, reset/invite ids — set the version to 4 (opaque, fully random):

class Auth_Model_Token extends Tiger_Model_Table
{
    protected $_name        = 'auth_token';
    protected $_primary     = 'auth_token_id';
    protected $_uuidVersion = 4;   // opaque — no embedded timestamp
}

Rule of thumb: entities → v7, secrets/tokens → v4. (See Tiger_Uuid — v7(), v4(), timeOf(), isValid().)

The standard columns (maintained for you)

Every domain table carries these, and the base maintains each only if the column exists:

Anything you pass explicitly wins over the automatic value.

Actor & org stamping

created_by/updated_by come from the current actor, set request-wide by the auth layer on login; a CLI/system insert leaves it unset, so those rows get created_by = NULL (system/genesis). The tenant org_id is stamped the same way from the active membership.

Tiger_Model_Table::setActor($userId);   // auth calls this on login
Tiger_Model_Table::setOrg($orgId);      // auth calls this per request
// ... any insert()/update() now stamps this actor + org automatically
$who = Tiger_Model_Table::actor();      // the current user_id, or null

created_by/updated_by are deliberately not foreign-keyed — a stamp must never block deleting the user it points at. Audit trails (who-changed-what history) are an app concern; core ships the stamp columns, not a history table.

Soft-delete and activeSelect()

softDelete() flips deleted to 1 instead of removing the row (falling back to a hard delete only when the table has no deleted column); restore() reverses it. Reads exclude deleted rows by default:

$invoice = $model->findById($id);            // null if missing OR soft-deleted
$invoice = $model->findById($id, true);      // include a soft-deleted row
$model->softDelete("invoice_id = '$id'");    // deleted = 1
$model->restore("invoice_id = '$id'");       // deleted = 0

Build every finder on activeSelect(), not select() — it comes pre-scoped to deleted = 0, so soft-deleted rows stay hidden without you remembering the where each time. Use delete() only for a deliberate hard delete.

The query-builder rule: never a raw SQL string

Build every query with the ZF1 query builder — activeSelect() / $db->select()->from(...) with bound parameters, and Zend_Db_Expr for aggregates. It's parameterized, portable, and injection-safe.

$select = $this->activeSelect()
    ->from($this->_name, ['status', new Zend_Db_Expr('COUNT(*) AS n')])
    ->where('org_id = ?', $orgId)      // bound, never concatenated
    ->group('status');

No $db->query("SELECT …"), no string-concatenated SQL in a model or a service — ever.

Schema conventions

When you write the migration for a new table, follow the substrate's conventions:

• UUID PKs are CHAR(36) (canonical lowercase text — portable and readable in logs), InnoDB, utf8mb4 / utf8mb4_unicode_ci.
• Unique-indexed text is VARCHAR(191) (191 × 4 bytes < the 767-byte index limit — portable to older MySQL).
• DATETIME, not TIMESTAMP (no 2038 problem, no implicit-timezone surprises).
• JSON-shaped data → LONGTEXT, never the JSON column type. You already json_encode/json_decode it, so validating JSON is the app's job — and MariaDB's JSON type is LONGTEXT plus an implicit CHECK(json_valid()) that rejects JSON nested ≥ 32 levels, which PHP encodes fine. That mismatch really broke the CMS builder's deep project blob; store JSON as LONGTEXT.

Migrations

Schema changes are additive-only PHP files in a migrations/ dir, named NNNN_snake_name.php, returning ['up' => [...], 'down' => [...]] — arrays of statements run in order:

<?php
return [
    'up' => [
        "CREATE TABLE `invoice` (
            `invoice_id` CHAR(36)     NOT NULL,
            `org_id`     CHAR(36)     NOT NULL DEFAULT '',
            `amount`     INT          NOT NULL DEFAULT 0,
            `status`     VARCHAR(32)  NOT NULL DEFAULT 'open',
            `deleted`    TINYINT(1)   NOT NULL DEFAULT 0,
            `created_by` CHAR(36)         NULL,
            `updated_by` CHAR(36)         NULL,
            `created_at` DATETIME     NOT NULL,
            `updated_at` DATETIME         NULL,
            PRIMARY KEY (`invoice_id`),
            KEY `ix_invoice_org` (`org_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    ],
    'down' => [
        "DROP TABLE IF EXISTS `invoice`",
    ],
];

• One logical DDL change per migration. MySQL/MariaDB auto-commit DDL, so a CREATE/ALTER can't roll back inside a transaction — a migration is recorded as applied only after all its statements succeed, and if one half-applies you fix forward.
• The runner (Tiger_Db_Migrator) discovers migrations across core, app (application/migrations), and every module's migrations/ dir, merges them into one ascending-by-version sequence, and records applied versions in tiger_migration so nothing runs twice.
• A step may also be a callable function ($db) { … } for a data migration SQL can't express cleanly (e.g. transforming a column across rows) — the string-vs-callable split is by type, so a SQL string is never mistaken for a function.

Run them from the console:

vendor/bin/tiger migrate            # apply all pending (core + app + modules)
vendor/bin/tiger migrate:status     # applied [x] / pending [ ]
vendor/bin/tiger migrate:rollback 1 # reverse the last n (default 1)

New module migrations go in application/modules/<name>/migrations/ and are picked up by a bare migrate. See the console for the full command list, and configuration for how the DB adapter is wired.