README

Maatify Persistence

Package status:

Documentation:

Ecosystem and usage:

Standalone, framework-agnostic PDO utilities for Maatify projects, providing robust scoped and global ordering, composable transaction support, and pagination tools. Designed and verified for MySQL environments.

Note: PDO Pagination is available starting with v1.1.0.

🚀 Key Features

• Global and Scoped Ordering: Easily manage display order across an entire table or within a specific scope.
• Composable PDO Transactions: Owns a transaction when needed and participates in an existing transaction without changing its ownership.
• Operation-Local Savepoint Orchestration: Creates operation-local rollback boundaries within caller-owned transactions, allowing individual operations to roll back without fully terminating the outer transaction.
• SQL Identifier Validation: Ensures table and column configurations are safe and properly quoted.
• Soft-Delete Filtering: Optional support for ignoring soft-deleted rows in ordering calculations.
• Scope Isolation: Ensures only the affected range within the configured scope is updated.
• PDO Pagination: Deterministic offset pagination with strict normalization, bounds checking, and safe whitelist-based sorting.

⚙️ Requirements

Runtime requirements:

• PHP >= 8.2
• ext-pdo
• maatify/exceptions ^1.0

Database behavior:

• The package behavior is designed and verified against MySQL.

📦 Installation

composer require maatify/persistence

⚡ Quick Usage

use Maatify\Persistence\Pdo\Ordering\ScopedOrderingConfig;
use Maatify\Persistence\Pdo\Ordering\ScopedOrderingManager;

// 1. Configure the ordering behavior for a table
$config = new ScopedOrderingConfig(
    table: 'maa_shipping_rates',
    scopeColumn: 'method_id', // Use null for global ordering
    idColumn: 'id',
    orderColumn: 'display_order',
    deletedAtColumn: 'deleted_at', // Use null if soft-deletes are not used
    // Set nullableScope: true when NULL is a real scope (for example, roots).
    nullableScope: false,
    // Optional: update this column atomically with a successful move.
    updatedAtColumn: null,
);

$ordering = new ScopedOrderingManager();

// 2. Get the next position for a new insert
$nextPosition = $ordering->getNextPosition(
    pdo: $pdo,
    config: $config,
    scopeValue: 2, // Use null for global ordering
);

// 3. Move an existing row within its scope
$success = $ordering->moveWithinScope(
    pdo: $pdo,
    config: $config,
    scopeValue: 2, // Use null for global ordering
    id: 15,
    newOrder: 4,
    // Required when updatedAtColumn is configured.
    updatedAtValue: null,
);

Composing PDO Mutations in One Transaction

Use the transaction runner when several mutations must commit or roll back together. Every participant must use the same PDO connection:

use Maatify\Persistence\Pdo\Transaction\PdoTransactionRunner;

$transactions = new PdoTransactionRunner($pdo);

$transactions->run(function () use ($pdo, $ordering, $config): void {
    $pdo->prepare('UPDATE `consumer_table` SET `status` = :status WHERE `id` = :id')
        ->execute(['status' => 'ready', 'id' => 10]);

    $ordering->moveWithinScope($pdo, $config, 2, 15, 4);
});

TransactionRunnerInterface exposes only run(callable $callback), so a consumer service can depend on the shared transaction abstraction without knowing about PDO. The package provides two intentional, public, and supported PDO implementations. Neither is deprecated, and neither replaces the other:

PdoTransactionRunner

• owns begin/commit/rollback when no transaction is active
• participates in an existing caller-owned transaction without begin/commit/full rollback
• intended when operation-local savepoint isolation is not required

PdoSavepointTransactionRunner

• preserves normal owned-transaction behavior when no transaction is active
• when an outer transaction is active, creates an operation-local savepoint
• on callback failure, attempts best-effort rollback to the operation savepoint without fully rolling back the caller-owned outer transaction
• never commits or fully rolls back the caller-owned outer transaction
• outer transaction remains caller-owned
• same PDO connection is required
• intended when operation-local rollback isolation is required

For the detailed behavioral contract and runner selection guidance, see the PDO Transaction Architecture.

PDO Pagination

use Maatify\Persistence\Pdo\Pagination\PaginationConfig;
use Maatify\Persistence\Pdo\Pagination\PageRequest;
use Maatify\Persistence\Pdo\Pagination\PdoPaginationQueryDescriptor;
use Maatify\Persistence\Pdo\Pagination\PdoPaginator;
use Maatify\Persistence\Pdo\Pagination\SortWhitelist;
use Maatify\Persistence\Pdo\Pagination\SortDirectionEnum;

$config = new PaginationConfig(
    defaultPerPage: 10,
    maxPerPage: 100,
    minPerPage: 1,
    sortWhitelist: new SortWhitelist([
        'id' => 'id',
        'created' => 'created_at',
        'name' => 'user_name',
    ]),
    defaultSortBy: 'created',
    defaultSortDirection: SortDirectionEnum::DESC,
    tieBreakerSortBy: 'id',
    tieBreakerDirection: SortDirectionEnum::DESC
);

$query = new PdoPaginationQueryDescriptor(
    totalSql: 'SELECT COUNT(*) FROM users',
    totalParams: [],
    filteredCountSql: 'SELECT COUNT(*) FROM users WHERE status = :status',
    filteredCountParams: ['status' => 'active'],
    dataSql: 'SELECT id, user_name, created_at FROM users WHERE status = :status',
    dataParams: ['status' => 'active']
);

$request = new PageRequest(page: 2, perPage: 15, sortBy: 'name', sortDirection: 'ASC');

$paginator = new PdoPaginator();
$result = $paginator->paginate(
    pdo: $pdo,
    query: $query,
    request: $request,
    config: $config,
    mapper: fn(array $row) => (object) $row
);

🧩 Public Runtime API

The package currently provides the following public classes for PDO ordering, transactions, and pagination:

Maatify\Persistence\Pdo\Ordering\ScopedOrderingConfig;
Maatify\Persistence\Pdo\Ordering\ScopedOrderingManager;
Maatify\Persistence\Pdo\Transaction\TransactionRunnerInterface;
Maatify\Persistence\Pdo\Transaction\PdoTransactionRunner;
Maatify\Persistence\Pdo\Transaction\SavepointTransactionRunnerInterface;
Maatify\Persistence\Pdo\Transaction\PdoSavepointTransactionRunner;

Maatify\Persistence\Pdo\Pagination\PageRequest;
Maatify\Persistence\Pdo\Pagination\SortDirectionEnum;
Maatify\Persistence\Pdo\Pagination\SortWhitelist;
Maatify\Persistence\Pdo\Pagination\PaginationConfig;
Maatify\Persistence\Pdo\Pagination\PdoPaginationQueryDescriptor;
Maatify\Persistence\Pdo\Pagination\PageResult;
Maatify\Persistence\Pdo\Pagination\PdoPaginator;

// Exceptions
Maatify\Persistence\Exception\PersistenceException;
Maatify\Persistence\Exception\InvalidOrderingConfigurationException;
Maatify\Persistence\Exception\InvalidOrderingOperationException;
Maatify\Persistence\Exception\OrderingTransactionException;
Maatify\Persistence\Exception\InvalidPaginationConfigurationException;
Maatify\Persistence\Exception\InvalidPaginationQueryException;
Maatify\Persistence\Exception\PaginationExecutionException;
Maatify\Persistence\Exception\TransactionExecutionException;

OrderingTransactionException remains public and autoloadable for backward compatibility with 1.x consumers, but is deprecated. moveWithinScope() now participates in an active caller-owned PDO transaction and no longer throws it for that condition.

⚠️ Critical Runtime Behavior

getNextPosition():

• Does not start a transaction.
• Does not lock the applicable scope.
• For concurrent inserts, the host application must provide the transaction and locking mechanism required to serialize position allocation.

moveWithinScope():

• Rejects inconsistent scope usage.
• Rejects id <= 0.
• Rejects newOrder <= 0.
• Owns a transaction when called without an active PDO transaction.
• Participates in an active caller-owned PDO transaction without beginning, committing, or rolling it back.
• Locks the applicable active scope using SELECT ... FOR UPDATE.
• Supports NULL as a scope value when nullableScope is enabled; this is distinct from global ordering, which has no scopeColumn.
• Reads the current order from the database within the same transaction.
• Does not trust a current order provided by the caller.
• Returns false if the target row is missing.
• Clamps values higher than the maximum position to the maximum available position.
• Returns true if the movement is a no-op (already at the requested position).
• Moves only the affected range.
• When updatedAtColumn is configured, updates that column on the target row in the same SQL statement and transaction as the final order update.
• Does not globally normalize pre-existing gaps.
• Rolls back and returns false if the final target update fails.
• Rolls back on any Throwable after starting the transaction.
• Rethrows the original Throwable without arbitrary wrapping.

PdoTransactionRunner:

• Requires all composed participants to use the same PDO connection.
• Starts, commits, and rolls back the transaction when it owns it.
• Participates in an existing transaction without changing its ownership.
• Preserves the callback return value.
• Rethrows the original callback Throwable after attempting to roll back an owned transaction.

PdoSavepointTransactionRunner:

• no-active-transaction path uses normal owned transaction behavior
• active outer transaction uses an operation-local savepoint
• after a successful callback, the runner releases the operation savepoint; the callback result is returned only when completion succeeds
• after a callback failure, the runner attempts best-effort rollback to the operation savepoint while preserving caller ownership of the outer transaction
• cleanup failures never replace the original callback Throwable
• the runner never commits or fully rolls back the caller-owned outer transaction
• same PDO connection requirement

rowExistsInScope():

• Returns false for id <= 0.
• Returns false if the row is not found within the configured scope.
• Treats soft-deleted rows as non-existent if deletedAtColumn is configured.
• Throws InvalidOrderingOperationException on invalid scope usage.
• External PDO errors propagate unmodified.

PDO Pagination:

• Normalizes page and per-page limits strictly.
• Uses safe whitelist-based sorting.
• Host application owns SQL, scopes, mapping, and filters.
• Package handles count queries and offset calculation.
• Does not alter or require active PDO transactions.

🏛️ Architecture Guarantees

• Standalone Composer package.
• Framework-agnostic.
• Host-agnostic.
• PDO-based.
• No ORM.
• No framework bindings.
• No HTTP endpoints, UI, controllers, or routes.
• No host table ownership.
• No generic application repository abstraction.
• The host provides the PDO connection.
• The caller owns the outer transaction when composing ordinary PDO mutations with Ordering mutations.
• Trusted SQL identifiers.
• Runtime values use prepared statements.

🛡️ Exception and Error Propagation

All package-defined exceptions implement the marker interface Maatify\Persistence\Exception\PersistenceException. However, this interface is not a catch-all. PDOException or other external Throwables may propagate without wrapping and require a separate catch or an outer Throwable boundary if handling is needed.

🔐 Security and Trust Boundaries

The ScopedOrderingConfig validates and quotes all configured table and column identifiers. However, these identifiers must still be provided as trusted application configurations (e.g., constants), never as raw user input. All actual runtime values are safely passed using PDO prepared statements.

📚 Documentation

For a comprehensive guide, please refer to the main technical reference:

• Persistence Package Reference

Other important documentation:

• Changelog
• Security Policy
• Contributing Guide
• Code of Conduct
• Architecture Decision Records
• Standards Manifest
• Package Building Standard
• CI Workflow Standard
• Library Presentation Standard

✅ Quality Status

• PHP 8.2–8.5 verification in CI.
• PHPStan Level Max.
• Unit, Regression, and MySQL Integration tests.
• Lowest dependencies verification.
• Stable CI Gate.

Integration testing:

• Real MySQL is required for Integration tests.
• SQLite is not an Integration substitute.
• MySQL 8.4.10 is the currently verified CI baseline.

🛠️ Development and Testing

composer validate --strict
composer dump-autoload --optimize --strict-psr
composer check-platform-reqs
composer audit --no-interaction --abandoned=fail
composer analyse
composer test:unit
composer test:regression
composer test:integration
composer test:consumer
vendor/bin/php-cs-fixer fix --dry-run --diff
git diff --check

composer test:integration, composer test:consumer, and composer test require a real MySQL database. SQLite is explicitly not an integration substitute. The Consumer Verification Harness creates a separate Composer root, installs this package as a non-symlinked dependency, and performs two clean runs.

Set the following environment variables for Integration tests:

• PERSISTENCE_TEST_MYSQL_DSN
• PERSISTENCE_TEST_MYSQL_USER
• PERSISTENCE_TEST_MYSQL_PASSWORD

For workflow syntax validation, install actionlint v1.7.12 with the checksum pinned in .github/workflows/ci.yml, then run:

actionlint -color

The CI workflow-lint job and this local command cover every workflow under .github/workflows/.

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

👤 Author

Engineered by Mohamed Abdulalim (@megyptm)
Backend Lead & Technical Architect
https://www.maatify.dev

Built with ❤️ by Maatify.dev — Unified Ecosystem for Modern PHP Libraries