Files

Ronald Huynen 2547717edb Initial commit

2026-03-23 21:37:59 +01:00

18 KiB

Raw Permalink Blame History

Cyclos Database Import Reference

This document describes how the legacy Cyclos database is imported into the Laravel Timebank application during the php artisan db:seed process.

Overview

The Cyclos import is a production-grade data migration system that transfers members, accounts, transactions, profile data, and images from a Cyclos timebank database into the new Laravel-based system. The migration preserves historical data integrity while transforming it to fit the new polymorphic multi-profile architecture.

Migration Flow

The import process is orchestrated through database/seeders/DatabaseSeeder.php and consists of several stages, each triggered by user confirmation prompts:

db:seed
├── Database refresh & base seeders
├── [Optional] migrate:cyclos              ← Main migration
│   ├── [Optional] migrate:cyclos-profiles ← Profile data
│   │   ├── profiles:clean-about           ← Cleanup
│   │   └── profiles:clean-cyclos_skills   ← Cleanup
│   └── [Optional] migrate:cyclos-gift-accounts ← Account consolidation
└── [Optional] Elasticsearch re-indexing

Source Database Configuration

The source Cyclos database name is prompted during the migration process. The database must be imported into MySQL and accessible from the application.

Setup Steps:

Export the Cyclos database as a SQL dump
Place the dump file in the application root
Import into MySQL: mysql -u root -p < cyclos_dump.sql
Run the migration and enter the database name when prompted

Input Validation:

If you enter the database name with a .sql extension (e.g., timebank_2025_09_19.sql), it will be automatically stripped
The command verifies the database exists before proceeding; if not found, it displays available databases to help identify typos

During the db:seed process, the source database name entered in migrate:cyclos is cached and automatically reused by migrate:cyclos-profiles, so you only need to enter it once.

The destination database is read from the environment:

$destinationDb = env('DB_DATABASE');

Stage 1: Main Migration (`migrate:cyclos`)

File: app/Console/Commands/MigrateCyclosCommand.php

This is the primary migration command that handles members, images, accounts, and transactions.

Member Group Mapping

Cyclos organizes members into groups. The migration maps these groups to Laravel model types:

Cyclos Group ID	Group Name	Laravel Model	Status
5	Active Users	`User`	Active
6	Inactive Users	`User`	`inactive_at` set
8	Removed Users	`User`	`deleted_at` set
13	Local Banks (Level I)	`Bank`	`level = 1`
14	Organizations	`Organization`	Active
15	Projects (Level II)	`Bank`	`level = 2`
17	Local Admins	Not migrated	-
18	TEST: Projects	`Organization`	Active
22	TEST: Users	`User`	Active
27	Inactive Projects	`Organization`	`inactive_at` set

Member Field Mapping

Each member type maps Cyclos fields to Laravel columns:

Cyclos Field	Laravel Column	Notes
`members.id`	`cyclos_id`	Foreign key reference
`members.name`	`full_name`	Display name
`members.email`	`email`	-
`members.member_activation_date`	`email_verified_at`	Converted via `FROM_UNIXTIME()`
`members.creation_date`	`created_at`	Converted via `FROM_UNIXTIME()`
`users.username`	`name`	Profile URL slug
`users.password`	`password`	Preserved hash (may need reset)
`users.salt`	`cyclos_salt`	For potential password compatibility
`users.last_login`	`last_login_at`	Converted via `FROM_UNIXTIME()`
`group_history_logs.start_date`	`inactive_at` / `deleted_at`	For inactive/removed members

Member Migration SQL Pattern

Each member type uses a similar INSERT pattern with ON DUPLICATE KEY UPDATE for idempotency:

INSERT INTO {destination}.users (cyclos_id, full_name, email, ...)
SELECT
    m.id AS cyclos_id,
    m.name AS full_name,
    m.email AS email,
    FROM_UNIXTIME(UNIX_TIMESTAMP(m.creation_date)) AS created_at,
    ...
FROM {source}.members m
JOIN {source}.users u ON m.id = u.id
WHERE m.group_id = {group_id}
ON DUPLICATE KEY UPDATE ...

Removed User Handling

Removed users (group_id = 8) receive anonymized data:

CONCAT('Removed Cyclos user', m.id) AS full_name,
CONCAT(m.id, '@removed.mail') AS email,
CONCAT('Removed user ', m.id) AS name,
0 as limit_min,
0 as limit_max

Profile Images Migration

Images are stored as binary data in Cyclos. The migration:

Selects the image with the lowest order_number for each member (primary profile photo)
Extracts binary data from {source}.images.image
Saves to storage/app/public/profile-photos/{type}_{uniqid}.jpg
Updates the profile's profile_photo_path column
Sets default avatar path for profiles without images

Storage::disk('public')->put($filename, $image->image);

Account Migration

Cyclos uses account types identified by type_id. These map to the polymorphic accounts table:

Cyclos Type ID	Account Type	Owner Types	Notes
1	Debit	Bank (id=1)	System settlement account
2	Community	Bank (id=1)	Community pool
5	Work	User, Organization, Bank	Primary transaction account
6	Gift	User, Organization, Bank	Temporary, consolidated later
7	Project	User, Organization, Bank	Project-specific transactions

Account limits are pulled from white-label configuration:

$userLimitMin = timebank_config('accounts.user.limit_min');
$userLimitMax = timebank_config('accounts.user.limit_max');

Account Migration SQL Pattern

INSERT INTO {destination}.accounts (name, accountable_type, accountable_id, cyclos_id, ...)
SELECT
    '{accountName}' AS name,
    'App\\Models\\User' AS accountable_type,
    u.id AS accountable_id,
    a.id AS cyclos_id,
    ...
FROM {source}.accounts a
JOIN {destination}.users u ON a.member_id = u.cyclos_id
WHERE a.type_id = {type_id}

Transaction Migration

Critical Transformation: Cyclos stores amounts as decimal hours (e.g. 0.8667 = 52 minutes). Laravel stores amounts as integer minutes.

ROUND(t.amount * 60) AS amount

Known limitation: Rounding to integer minutes per transaction accumulates small errors across accounts with many transactions. For example, an account with 502 transactions may end up with a balance ±11 minutes off from Cyclos. The verify:cyclos-migration command reports these discrepancies. A per-account correction mechanism is planned to address this.

Transaction mapping:

Cyclos Field	Laravel Column	Notes
`transfers.from_account_id`	`from_account_id`	Via `accounts.cyclos_id` join
`transfers.to_account_id`	`to_account_id`	Via `accounts.cyclos_id` join
`transfers.amount`	`amount`	Multiplied by 60, rounded to integer minutes
`transfers.description`	`description`	-
`transfers.type_id`	`transaction_type_id`	Direct mapping
-	`transaction_status_id`	Always `1` (completed)
`transfers.date`	`created_at`	Converted via `FROM_UNIXTIME()`

Validation Checks

After migration, three inline integrity checks are performed:

Member Count: Total Cyclos members (excluding group 17) must equal Users + Organizations + Banks
Transaction Count: Total Cyclos transfers must equal Laravel transactions
Balance Integrity: Net balance across all accounts must equal zero

For a full verification after the complete seed process, run:

php artisan verify:cyclos-migration

This checks member counts, transaction counts, per-account-type balances, gift account cleanup, and deleted profile cleanup. See references/CYCLOS_MIGRATION_DISCREPANCIES.md for the latest results.

// Balance check using window functions
$totalNetBalance = DB::query()
    ->fromSub($accountBalances, 'account_balances')
    ->selectRaw('SUM(net_balance) as total_net_balance')
    ->first();

if ($totalNetBalance->total_net_balance != 0) {
    $this->error("OUT OF BALANCE AMOUNT: {$totalNetBalance->total_net_balance}");
}

Laravel-Love Registration

After data migration, the command registers all profiles with the Laravel-Love package for reaction tracking:

Artisan::call('love:register-reacters', ['--model' => 'App\Models\User']);
Artisan::call('love:register-reactants', ['--model' => 'App\Models\User']);
// ... for Organization and Bank as well
Artisan::call('love:add-reactions-to-transactions');

Stage 2: Profile Data Migration (`migrate:cyclos-profiles`)

File: app/Console/Commands/MigrateCyclosProfilesCommand.php

This command migrates custom profile fields and preferences from Cyclos.

Custom Field Mapping

Cyclos stores custom fields in custom_field_values with field_id identifiers:

Field ID	Cyclos Field	Laravel Column	Model Types	Notes
1	Birthday	`date_of_birth`	User	Date format: `DD/MM/YYYY` → `YYYY-MM-DD`
7	Phone	`phone`	All	Truncated to 20 characters
10	Website	`website`	All	-
13	Skills	`cyclos_skills`	User, Organization	Truncated to 495 chars + "..."
17	About	`about`	User	-
28	General Newsletter	`message_settings.general_newsletter`	All	790=No, 791=Yes
29	Local Newsletter	`message_settings.local_newsletter`	All	792=No, 793=Yes
35	Motivation	`motivation`	All	-
36	Country	`locations.country_id`	All	Via code mapping
38	City	`locations.city_id`	All	Via code mapping

Location Code Mapping

Cyclos uses possible_value_id for location selection. These map to Laravel location IDs:

Countries:

$countryCodeMap = [
    860 => 2,    // BE (Belgium)
    861 => 7,    // PT (Portugal)
    862 => 1,    // NL (Netherlands)
    863 => null, // Other/not set
];

Cities:

$cityCodeMap = [
    864 => 188, // Amsterdam
    865 => 200, // Haarlem
    866 => 316, // Leiden
    867 => 305, // The Hague
    868 => 300, // Delft
    869 => 331, // Rotterdam
    870 => 272, // Utrecht
    881 => 345, // Brussels
];

After creating location records, the migration calls $location->syncAllLocationData() to populate related fields (divisions, etc.) from the parent city/country.

Newsletter preferences migrate to the polymorphic message_settings table:

$model->message_settings()->updateOrCreate(
    ['message_settingable_id' => $model->id, 'message_settingable_type' => $modelClass],
    ['general_newsletter' => $value]
);

HTML Tag Stripping

After migration, HTML tags are stripped from text fields:

$cleaned = strip_tags($record->cyclos_skills);
$cleaned = strip_tags($record->about);

Stage 3: Cleanup Commands

`profiles:clean-about`

File: app/Console/Commands/CleanCyclosProfiles.php

Removes empty paragraph markup from about fields:


 
 
" (single double-quote character)

Sets these to null for clean display.

`profiles:clean-cyclos_skills`

File: app/Console/Commands/CleanCyclosSkills.php

Removes trailing pipe symbols from skills fields. Cyclos used pipe-delimited skill lists that sometimes had trailing pipes:

$cleaned = preg_replace('/(\s*\|\s*)+$/', '', $original);

Stage 4: Gift Account Consolidation (`migrate:cyclos-gift-accounts`)

File: app/Console/Commands/MigrateCyclosGiftAccounts.php

Cyclos had separate "gift" accounts (type_id = 6) that received welcome bonuses or gift transactions. This command consolidates gift account balances into primary work accounts and marks all gift accounts as inactive.

How Gift Accounts Are Created

During Stage 1 (migrate:cyclos), gift accounts are imported from Cyclos for all profile types:

All gift accounts are imported from Cyclos accounts table where type_id = 6
They are created with name = 'gift' and associated with Users, Organizations, or Banks
All related transactions are also imported, preserving the original balances
At this stage, gift accounts are active (no inactive_at set)

Consolidation Process

The migrate:cyclos-gift-accounts command then processes these accounts:

Find all gift accounts - Queries all accounts where name = 'gift'
For each gift account, check the balance:
- If balance is zero or negative: Skip (nothing to transfer)
- If balance is positive: Transfer the full balance to the owner's primary (non-gift) account

Transfer positive balances:

$transfer = new Transaction();
$transfer->from_account_id = $fromAccount->id;  // Gift account
$transfer->to_account_id = $toAccount->id;      // Primary work account
$transfer->amount = $balance;
$transfer->description = "Migration of balance from gift account (ID: {$fromAccount->id})";
$transfer->transaction_type_id = 6;             // Migration type
$transfer->save();

Mark ALL gift accounts as inactive - After the loop completes, all gift accounts are marked inactive, regardless of whether they had a positive balance or not:
```
$giftAccountIds = $giftAccounts->pluck('id');
Account::whereIn('id', $giftAccountIds)->update(['inactive_at' => now()]);
```

Important Notes

All gift accounts become inactive after this command runs - not just those with positive balances
Accounts with zero/negative balance are skipped for the transfer step but still marked inactive
The original transactions remain in the gift account history (for audit purposes)
The migration transaction creates a clear record of when and how balances were moved
If no destination account is found for an owner, the gift account is logged as a warning but still marked inactive

Database Safety Measures

Transaction Wrapping

Every migration step is wrapped in database transactions:

DB::beginTransaction();
try {
    // Migration logic
    DB::commit();
} catch (\Exception $e) {
    DB::rollBack();
    $this->error('Migration failed: ' . $e->getMessage());
}

Job Queue Suppression

During migration, job dispatching is disabled to prevent side effects:

\Queue::fake();

Idempotency

Member migrations use ON DUPLICATE KEY UPDATE to allow re-running without data duplication.

Password Handling

Cyclos password hashes are preserved in the password column with the salt stored in cyclos_salt. However, Laravel uses a different hashing mechanism (bcrypt by default), so:

Users may need to use "Forgot Password" to set a new Laravel-compatible password
Alternatively, a custom hash driver could be implemented to verify Cyclos passwords

Running the Migration

Prerequisites

Export the Cyclos database as a SQL dump
Import the dump into MySQL:
```
mysql -u root -p < cyclos_dump.sql
```
Note the database name you used during import

Full Fresh Migration

Use seed.sh for a complete fresh setup — it handles dropping tables, running migrations, maintenance mode, immutability restrictions, and prompts for the Cyclos DB:

./seed.sh

If running the seeder manually:

php artisan db:seed

You will be prompted through the following steps:

Seed base data? - Transaction types, permissions, countries, etc.
Seed example tags? - Optional tag data
Migrate cyclos database? - If yes:
- You will be prompted to enter the source Cyclos database name
- The database name is cached for use by subsequent commands
Migrate cyclos profile data? - Uses cached database name automatically
Migrate cyclos gift accounts? - Consolidates gift balances
Assign Dev-Admin role? - Optional admin assignment
Re-create Elasticsearch index? - Rebuilds search indices

Individual Commands

When running commands individually (outside of db:seed), each command will prompt for the source database name if not cached:

# Main migration - prompts for database name and caches it
php artisan migrate:cyclos

# Profile data - uses cached name or prompts if cache expired
php artisan migrate:cyclos-profiles

# Cleanup commands (no database prompt needed)
php artisan profiles:clean-about
php artisan profiles:clean-cyclos_skills

# Gift account consolidation (no database prompt needed)
php artisan migrate:cyclos-gift-accounts

Database Name Caching

The source database name is cached for 1 hour after being entered in migrate:cyclos. This allows migrate:cyclos-profiles to automatically use the same database without re-prompting during the db:seed process.

// Set by migrate:cyclos
cache()->put('cyclos_migration_source_db', $sourceDb, now()->addHours(1));

// Retrieved by migrate:cyclos-profiles
$sourceDb = cache()->get('cyclos_migration_source_db');

Key Files Reference

File	Purpose
`database/seeders/DatabaseSeeder.php`	Orchestrates the full seeding process
`app/Console/Commands/MigrateCyclosCommand.php`	Members, images, accounts, transactions
`app/Console/Commands/MigrateCyclosProfilesCommand.php`	Custom fields, locations, newsletters
`app/Console/Commands/MigrateCyclosGiftAccounts.php`	Gift account consolidation
`app/Console/Commands/CleanCyclosProfiles.php`	About field cleanup
`app/Console/Commands/CleanCyclosSkills.php`	Skills field cleanup

Post-Migration Considerations

Password Resets: Advise users to reset passwords via the forgot password flow
Profile Photos: Verify images are accessible at /storage/profile-photos/
Balance Verification: Run php artisan verify:cyclos-migration — all 20 checks should pass. See references/CYCLOS_MIGRATION_DISCREPANCIES.md for explanation of known discrepancies.
Search Indexing: Ensure Elasticsearch indices are rebuilt after migration
Gift Accounts: Verified automatically by verify:cyclos-migration (section 4)

18 KiB Raw Permalink Blame History