Credits Table Improvement for Adjustment Transactions

TL;DR

To improve query performance and support future adjustment transaction types, we will introduce a new indexed boolean flag (is_adjustment) in the credits table.

This replaces the current implementation that identifies adjustment records by searching the comment column using LIKE queries.

Current Implementation	New Implementation
`credits.comment LIKE "%CAFS%"`	`credits.is_adjustment = true`
Full string search	Indexed boolean lookup
CAFS only	Supports all adjustment types

Important

A new column is_adjustment (TINYINT(1)) will be added to the credits table.

The column defaults to false.

The column will be indexed.

Existing adjustment transactions (such as CAFS) should have is_adjustment = true.

Future adjustment types (for example, Cancellation Penalty) will also use this flag.

Background

The current implementation identifies CAFS transactions by filtering the comment column.

Example:

WHERE credits.comment LIKE "%CAFS%"

This approach has several limitations:

comment is not indexed.
LIKE queries become increasingly expensive as the credits table grows.
Every new adjustment type requires another string comparison.
Business logic becomes coupled to free-text comments instead of structured data.

As new adjustment transaction types are introduced (such as Cancellation Penalty), relying on comment matching becomes more difficult to maintain and less performant.

Proposed Solution

Introduce a new column in the credits table.

Column	Type	Default	Indexed
`is_adjustment`	`TINYINT(1)`	`0 (false)`	✅ Yes

This column explicitly identifies whether a credit transaction represents an adjustment.

Examples include:

CAFS
Cancellation Penalty
Future adjustment transaction types

Instead of relying on comment text, the application will filter adjustment records using the indexed flag.

Current Implementation

Current query:

$credits = $this->model->selectRaw('
    id,
    company_id,
    location_id,
    credits.comment,
    credits.assigned_credits,
    DATE_FORMAT(credits.created_at, "%d %M %Y - %h:%i") as date
')
->where('company_id', '=', $dataFilter['company_id'])
->where('created_at', '>=', $dataFilter['report_start_date'])
->where('created_at', '<=', $dataFilter['report_end_date'])
->where('status', '=', Constants::CREDIT_STATUS_ENABLE)
->where(function ($query) use ($dataFilter) {
    $query->whereNull('credits.location_id')
        ->orWhere(function ($query) use ($dataFilter) {
            $query
                ->where('credits.company_id', '=', $dataFilter['company_id'])
                ->where('credits.comment', 'like', '%CAFS%');
        });
});

return $credits;

Current filtering logic:

credits.comment LIKE "%CAFS%"

New Implementation

The query will instead filter using the indexed flag.

Example:

->where('credits.is_adjustment', true)

This removes the dependency on parsing the comment field while allowing the query to use an index.

Why This Change?

Better Performance

Searching using an indexed boolean column is significantly more efficient than performing wildcard string searches on an unindexed text column.

Instead of:

comment LIKE "%CAFS%"

we can simply use:

is_adjustment = 1

which allows the database to utilize the index effectively.

Better Scalability

Today we only identify CAFS transactions.

However, upcoming features introduce additional adjustment transaction types, including:

CAFS
Cancellation Penalty
Future adjustment categories

Without this change, the query would gradually evolve into something like:

->where(function ($query) {
    $query
        ->where('comment', 'like', '%CAFS%')
        ->orWhere('comment', 'like', '%Cancellation Penalty%');
});

As more adjustment types are added, this approach becomes increasingly difficult to maintain.

Using is_adjustment eliminates the need to update queries whenever a new adjustment type is introduced.

Seeder / Data Migration Plan

To ensure existing reports continue to function correctly after introducing the is_adjustment column, a one-time data migration (seeder) will be executed.

The seeder will update historical adjustment transactions by identifying existing CAFS records using the current matching logic.

Pseudo logic:

UPDATE credits
SET is_adjustment = 1
WHERE comment LIKE '%CAFS%';

Migration rules:

Existing Record	is_adjustment
`comment` contains `CAFS`	`1`
All other records	`0` (default)

After the seeder has been executed:

Historical CAFS transactions will be correctly identified as adjustment transactions.
Existing reports will continue to return the same data after switching to the new query.
Future adjustment transactions (such as Cancellation Penalty) should explicitly set is_adjustment = 1 during creation instead of relying on the comment field.

Impact

The following components should be updated:

Credits table migration
Seeder to backfill existing CAFS transactions
Credit creation logic (to populate is_adjustment)
Existing adjustment transaction creators (CAFS)
Cancellation Penalty implementation
PORTAL_V2_REPORT adjustment queries
Any future reports that retrieve adjustment transactions

Data Migration

Existing adjustment records should be backfilled by setting:

is_adjustment = true

for all historical CAFS transactions.

This ensures existing reports continue to function correctly after the migration.

Future Adjustment Types

With this implementation, adding new adjustment categories becomes straightforward.

Adjustment Type	Comment	is_adjustment
CAFS	CAFS	✅ true
Cancellation Penalty	Cancellation Penalty	✅ true
Future Adjustment	Any value	✅ true

Report queries no longer need to know the specific adjustment type.

Benefits

Implementing is_adjustment provides several advantages:

Improved query performance through index usage.
Eliminates expensive wildcard searches on the comment column.
Decouples business logic from free-text comments.
Simplifies report queries.
Makes the system easier to extend with new adjustment transaction types.
Improves long-term maintainability and scalability of the credits reporting system.

TL;DR​