Plan: Post-Migration Rounding Correction Transactions

Context

Cyclos stores transaction amounts as decimal hours (e.g. 1.5h). Laravel stores integer minutes (e.g. 90 min). During import, ROUND(amount * 60) per transaction accumulates rounding errors. An account with 29,222 transactions (Lekkernassuh) ends up with 1,158 minutes of drift.

The fix: after importing all Cyclos transactions, insert one correction transaction per affected account per calendar year present in the Cyclos data. This keeps individual correction amounts small — one year at a time rather than one large correction for the full history. No annual scheduled command is needed; the correction lives entirely inside MigrateCyclosCommand.

Approach

For each account with Cyclos transactions:

Group the account's Cyclos transactions by calendar year
For each year, compute drift: ROUND(SUM(signed_hours) * 60) - SUM(ROUND(signed_hours * 60)) where signed_hours = +amount if credit, -amount if debit for that account
Insert a correction transaction dated {year}-12-31 23:59:59, type 7
Counterpart account: Central Bank debit account (keeps system balanced)

Description Format

Yearly transaction to correct rounding discrepancy between decimal values (like H 1,5) and hourly values (time-based like H 1:30).
In {year} you had {n} transactions, the average correction per transaction was {avg} seconds.

Where {avg} = abs(diff_minutes) / tx_count * 60, rounded to 2 decimal places (e.g. 1.20 seconds).

Files to Modify

1. `database/seeders/TransactionTypesTableSeeder.php`

Add type 7. Note: type 7 does not currently exist in the codebase — it was previously added as "Migration rounding correction" (icon adjustments-horizontal, 22 chars) but was removed because it exceeded the icon varchar(20) column limit and the correction mechanism was dropped. It is re-added here with a new name and a shorter icon:

ID	Name	Icon
1	Work	`clock`
2	Gift	`gift`
3	Donation	`hand-thumb-up`
4	Currency creation	`bolt`
5	Currency removal	`bolt-slash`
6	Migration	`truck`
7	Rounding correction	`adjustments` (11 chars ✓)

6 => [
    'id'         => 7,
    'name'       => 'Rounding correction',
    'label'      => 'Rounding correction: corrects balance drift from decimal-to-time format conversion',
    'icon'       => 'wrench',
    'created_at' => NULL,
    'updated_at' => NULL,
],

2. New migration

database/migrations/YYYY_MM_DD_HHMMSS_add_rounding_correction_transaction_type.php

Insert type 7 if not exists (for databases that already ran the seeder without it):

if (!DB::table('transaction_types')->where('id', 7)->exists()) {
    DB::table('transaction_types')->insert([
        'id'    => 7,
        'name'  => 'Rounding correction',
        'label' => 'Rounding correction: corrects balance drift from decimal-to-time format conversion',
        'icon'  => 'adjustments',
    ]);
}

3. `app/Console/Commands/MigrateCyclosCommand.php`

After the MIGRATE TRANSACTIONS block, add a ROUNDING CORRECTIONS section:

// ROUNDING CORRECTIONS (post-migration, per account per year)
$debitAccountId = DB::table("{$destinationDb}.accounts")
    ->where('accountable_type', 'App\\Models\\Bank')
    ->where('accountable_id', 1)
    ->where('name', $debitAccountName)
    ->value('id');

if (!$debitAccountId) {
    $this->warn("Could not find Central Bank debit account — skipping rounding corrections.");
} else {
    $corrections = DB::select("
        SELECT
            la.id AS laravel_account_id,
            YEAR(t.date) AS tx_year,
            COUNT(t.id) AS tx_count,
            ROUND(SUM(IF(t.to_account_id = ca.id, t.amount, -t.amount)) * 60)
                - SUM(ROUND(IF(t.to_account_id = ca.id, t.amount, -t.amount) * 60)) AS diff_min
        FROM {$destinationDb}.accounts la
        INNER JOIN {$sourceDb}.accounts ca ON la.cyclos_id = ca.id
        INNER JOIN {$sourceDb}.transfers t ON t.from_account_id = ca.id OR t.to_account_id = ca.id
        WHERE la.cyclos_id IS NOT NULL
        GROUP BY la.id, ca.id, YEAR(t.date)
        HAVING ABS(diff_min) > 0
    ");

    $correctionCount = 0;
    foreach ($corrections as $row) {
        $diff    = (int) $row->diff_min;
        $txCount = (int) $row->tx_count;
        $year    = (int) $row->tx_year;
        $avg     = $txCount > 0 ? round(abs($diff) / $txCount / 60, 4) : 0;

        $description = "Transaction to correct rounding discrepancy between decimal values "
            . "(like H 1,5) and hourly values (time-based like H 1:30).\n\n"
            . "In {$year} you had {$txCount} transactions, the average correction per "
            . "transaction was {$avg}H.";

        DB::table("{$destinationDb}.transactions")->insert([
            'from_account_id'       => $diff > 0 ? $debitAccountId : $row->laravel_account_id,
            'to_account_id'         => $diff > 0 ? $row->laravel_account_id : $debitAccountId,
            'amount'                => abs($diff),
            'description'           => $description,
            'transaction_type_id'   => 7,
            'transaction_status_id' => 1,
            'created_at'            => "{$year}-12-31 23:59:59",
            'updated_at'            => "{$year}-12-31 23:59:59",
        ]);
        $correctionCount++;
    }
    $this->info("Rounding corrections applied: {$correctionCount} year/account combinations adjusted.");
}

Direction logic:

diff > 0: account should have more minutes → debit account pays the user account
diff < 0: account has more than it should → user account pays back to debit account

4. `app/Console/Commands/VerifyCyclosMigration.php`

Update checkTransactions() to exclude type 7 from the imported count and mention it in the info line:

$roundingCorrCount = DB::table("{$destDb}.transactions")->where('transaction_type_id', 7)->count();
$laravelImported   = $laravelTotal - $giftMigCount - $currRemovalCount - $roundingCorrCount;

$this->info("  (Total Laravel: {$laravelTotal} = {$laravelImported} imported"
    . " + {$giftMigCount} gift migrations"
    . " + {$currRemovalCount} currency removals"
    . " + {$roundingCorrCount} rounding corrections)");

Verification

Run ./seed.sh with Cyclos DB
Check corrections: SELECT transaction_type_id, COUNT(*), SUM(amount) FROM transactions WHERE transaction_type_id = 7 GROUP BY transaction_type_id;
Verify Lekkernassuh (account 8268) has multiple yearly corrections summing to ~1158 min total
Check one correction's description matches the format
Run php artisan verify:cyclos-migration — all checks should pass
Run php artisan test

6.7 KiB Raw Permalink Blame History