Dual-Write Database System: Implementation Guide

Overview

The dual-write system enables simultaneous data synchronization between your Core database and Portal database. This setup ensures data consistency across both systems while maintaining separation of concerns.

System Architecture

Core Components:

Core Database: Primary application database
Portal Database: Secondary database for portal-specific functionality
Synchronization Service: Handles real-time data replication
Queue System: Manages synchronization jobs

Prerequisites

Before implementation, ensure:

All migration files are executed (php artisan migrate)
Configuration files are properly set up:
- config/sync.php
- config/sync_mapping.php
- config/queue.php
Composer dependencies are installed (composer install)

Implementation Steps

1. Model Setup

For models that require dual-write:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use App\Traits\SyncsToPortal;

class YourModel extends Model
{
    use SyncsToPortal;

    protected $portalTable = 'portal_table_name';
    protected $syncAttributes = ['column1', 'column2', 'column3'];
    protected $portalKey = 'portal_id';

    // Optional: Define transformation logic
    public function toPortalArray()
    {
        return [
            'portal_column' => $this->core_attribute,
            'transformed_data' => $this->transformMethod(),
        ];
    }
}

2. Configuration Mapping

Update config/sync_mapping.php:

return [
    'core_table_name' => [
        'portal_table' => 'portal_table_name',
        'columns' => [
            'core_column' => 'portal_column',
            'core_foreign_key' => 'portal_foreign_key',
        ],
        'conditions' => [
            'should_sync' => true,
            'where' => ['status' => 'active'],
        ],
        'transformations' => [
            'core_field' => 'transformation_method',
        ],
    ],
    // Add more table mappings as needed
];

3. Observer Registration

Register observers in AppServiceProvider.php:

public function boot()
{
    YourModel::observe(YourModelObserver::class);
    // Register other model observers
}

Frequently Used Commands

Initial Setup Commands

# Setup portal schema structure
php artisan portal:setup

# Perform initial data synchronization
php artisan sync:initial

# Verify schema compatibility
php artisan schema:verify

Regular Operation Commands

# Monitor synchronization status
php artisan sync:monitor

# Manual schema synchronization
php artisan schema:sync

# Debug portal schema issues
php artisan portal:debug

# Test mapping configuration
php artisan sync:test-mapping

Maintenance Commands

# Compare schemas between core and portal
php artisan schema:compare

# Retry failed synchronization jobs
php artisan queue:retry sync

# View synchronization queue status
php artisan queue:listen --queue=sync

Key Configuration Files

config/sync.php

return [
    'enabled' => env('SYNC_ENABLED', true),
    'connection' => 'portal',
    'queue' => 'sync',
    'chunk_size' => 1000,
    'timeout' => 3600,
    'retry_attempts' => 3,
    'models' => [
        App\Models\User::class,
        App\Models\JobApplication::class,
        // Add other sync-enabled models
    ],
];

config/queue.php (Relevant Section)

'connections' => [
    'sync' => [
        'driver' => 'database',
        'table' => 'jobs',
        'queue' => 'sync',
        'retry_after' => 3600,
    ],
],

Implementation Best Practices

1. Data Transformation

protected function transformForPortal(array $coreData): array
{
    return [
        'portal_ready_data' => $this->transformData($coreData),
        'sync_timestamp' => now(),
        'metadata' => $this->extractMetadata(),
    ];
}

2. Error Handling

try {
    $this->synchronizer->sync($model);
} catch (SyncException $e) {
    Log::error('Sync failed: ' . $e->getMessage());
    $this->retrySync($model, $e);
}

3. Conflict Resolution

protected function resolveConflict($coreData, $portalData)
{
    // Implement your conflict resolution strategy
    return $this->useLatestTimestamp($coreData, $portalData);
}

Monitoring and Logging

Enable Detailed Logging

// In config/logging.php
'channels' => [
    'sync' => [
        'driver' => 'daily',
        'path' => storage_path('logs/sync.log'),
        'level' => 'debug',
    ],
],

Custom Monitoring

// Add to your application's monitoring system
SyncMonitor::trackPerformance();
SyncMonitor::alertOnFailureRate(0.1); // Alert if >10% failure rate

Troubleshooting Common Issues

1. Schema Mismatch

php artisan schema:verify
php artisan schema:sync --fix

2. Queue Backlog

php artisan queue:work --queue=sync --timeout=3600
php artisan queue:retry all

3. Data Inconsistency

php artisan sync:validate
php artisan sync:repair {table_name}

Performance Considerations

Batch Processing

// For large datasets
DatabaseSynchronizer::chunk(1000, function ($records) {
    $this->syncBatch($records);
});

Index Optimization

-- Portal database indexes
CREATE INDEX idx_sync_status ON portal_table (sync_status);
CREATE INDEX idx_last_sync ON portal_table (last_synced_at);

Testing Your Implementation

Unit Tests

public function test_dual_write_operation()
{
    $model = YourModel::create($testData);
    $this->assertPortalRecordExists($model->portal_id);
    $this->assertDataConsistent($model);
}

Integration Tests

public function test_synchronization_flow()
{
    $this->artisan('sync:initial')
         ->expectsOutput('Synchronization completed successfully');

    $this->assertDatabaseHas('portal_table', ['synced' => true]);
}

Rollback Procedure

Emergency Disable

// In config/sync.php
'enabled' => false,

Data Recovery

php artisan sync:rollback {timestamp}
php artisan sync:restore {backup_id}

Important Notes

Always test synchronization in staging before production
Monitor queue health regularly using php artisan sync:monitor
Keep schema mappings updated when modifying database structure
Implement proper error handling for network issues between databases
Regularly validate data consistency between core and portal

This dual-write system provides robust data synchronization while maintaining system separation. Follow this guide carefully and refer to specific command help (php artisan help {command}) for detailed usage information.

Command Palette