use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    public function up(): void
    {
        Schema::create('platform_messages', function (Blueprint $table) {
            $table->id();
            $table->string('routing_code')->unique()->index();
            $table->enum('message_type', ['broadcast', 'system']);
            $table->enum('lifecycle_status', ['draft', 'active', 'expired']);

            // System alerts: translation keys + dynamic parameters
            $table->string('title_translation_key')->nullable();
            $table->string('body_translation_key')->nullable();
            $table->json('dynamic_params')->nullable();

            // Broadcasts: pre-rendered localized content
            $table->json('localized_titles')->nullable();
            $table->json('localized_bodies')->nullable();

            // Targeting & scheduling
            $table->json('audience_criteria')->nullable();
            $table->json('ui_metadata')->nullable();
            $table->timestamp('activation_at')->nullable();
            $table->timestamp('deactivation_at')->nullable();
            $table->timestamps();
        });

        Schema::create('message_user_reads', function (Blueprint $table) {
            $table->id();
            $table->foreignId('platform_message_id')
                ->constrained()
                ->cascadeOnDelete();
            $table->foreignId('user_id')
                ->constrained()
                ->cascadeOnDelete();
            $table->timestamp('read_timestamp')->nullable()->index();
            $table->timestamps();

            $table->unique(['platform_message_id', 'user_id']);
        });
    }
};

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use Illuminate\Database\Eloquent\Factories\HasFactory;

class PlatformMessage extends Model
{
    use HasFactory;

    protected $fillable = [
        'routing_code',
        'message_type',
        'lifecycle_status',
        'title_translation_key',
        'body_translation_key',
        'dynamic_params',
        'localized_titles',
        'localized_bodies',
        'audience_criteria',
        'ui_metadata',
        'activation_at',
        'deactivation_at',
    ];

    protected function casts(): array
    {
        return [
            'dynamic_params' => 'array',
            'audience_criteria' => 'array',
            'localized_titles' => 'array',
            'localized_bodies' => 'array',
            'ui_metadata' => 'array',
            'activation_at' => 'datetime',
            'deactivat

ion_at' => 'datetime', ]; }

public function recipients(): BelongsToMany
{
    return $this->belongsToMany(User::class, 'message_user_reads')
        ->withPivot('read_timestamp')
        ->withTimestamps();
}

public function scopeActive($query)
{
    return $query->where('lifecycle_status', 'active')
        ->where(function ($q) {
            $q->whereNull('activation_at')
              ->orWhere('activation_at', '<=', now());
        })
        ->where(function ($q) {
            $q->whereNull('deactivation_at')
              ->orWhere('deactivation_at', '>=', now());
        });
}

public function resolveLocalizedTitle(string $locale = 'en'): string
{
    if ($this->message_type === 'system') {
        return __($this->title_translation_key, $this->dynamic_params ?? [], $locale);
    }

    return $this->localized_titles[$locale]
        ?? $this->localized_titles['en']
        ?? '';
}

public function resolveLocalizedBody(string $locale = 'en'): string
{
    if ($this->message_type === 'system') {
        return __($this->body_translation_key, $this->dynamic_params ?? [], $locale);
    }

    return $this->localized_bodies[$locale]
        ?? $this->localized_bodies['en']
        ?? '';
}

}

**Architecture Rationale:**
- `resolveLocalizedTitle/Body` methods abstract the dual-content strategy. The frontend never needs to know whether content came from a translation key or JSON storage.
- `scopeActive` enforces visibility windows at the query level, preventing expired or future-dated messages from leaking into user inboxes.
- Pivot relationship uses `withPivot('read_timestamp')` to enable precise read analytics without additional queries.

### Step 3: Broadcast Dispatch Service

Admin broadcasts require audience filtering, transactional safety, and chunked attachment to prevent memory exhaustion during large-scale deployments.

```php
namespace App\Services;

use App\Models\PlatformMessage;
use App\Models\User;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class BroadcastDispatcher
{
    public function activate(PlatformMessage $message, array $criteria): void
    {
        DB::transaction(function () use ($message, $criteria) {
            $targetIds = User::query()
                ->when($criteria['country'] ?? null, fn($q, $v) => $q->where('country_code', $v))
                ->when($criteria['age_range'] ?? null, fn($q, $v) => $q->whereBetween('age', $v))
                ->when($criteria['verification_status'] ?? null, fn($q, $v) => $q->where('email_verified_at', $v === 'verified' ? '!=' : '=', null))
                ->pluck('id');

            $chunkSize = 500;
            $targetIds->chunk($chunkSize)->each(function ($chunk) use ($message) {
                $attachData = $chunk->mapWithKeys(fn($id) => [
                    $id => ['created_at' => now(), 'updated_at' => now()]
                ])->toArray();

                DB::table('message_user_reads')->insertOrIgnore([
                    'platform_message_id' => $message->id,
                    ...$attachData
                ]);
            });

            $message->update([
                'lifecycle_status' => 'active',
                'audience_criteria' => $criteria,
            ]);
        });
    }
}

Architecture Rationale:

• insertOrIgnore prevents duplicate pivot rows when resending or re-evaluating criteria.
• Chunking attachment operations prevents memory spikes and database lock contention during broadcasts to 50k+ users.
• Transaction wrapping ensures atomicity: either the message activates with all recipients attached, or the entire operation rolls back.

Step 4: System Alert Dispatcher

Automated alerts are triggered by domain events and dispatched via queued jobs. They inherit auto-expiration rules and dynamic parameter injection.

namespace App\Jobs;

use App\Models\PlatformMessage;
use App\Models\User;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;

class DispatchSystemAlert implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    public function __construct(
        public string $routingCode,
        public User $targetUser,
        public string $titleKey,
        public string $bodyKey,
        public array $params = [],
        public int $ttlDays = 30
    ) {}

    public function handle(): void
    {
        $message = PlatformMessage::firstOrCreate(
            ['routing_code' => $this->routingCode],
            [
                'message_type' => 'system',
                'lifecycle_status' => 'active',
                'title_translation_key' => $this->titleKey,
                'body_translation_key' => $this->bodyKey,
                'dynamic_params' => $this->params,
                'deactivation_at' => now()->addDays($this->ttlDays),
            ]
        );

        $message->recipients()->syncWithoutDetaching([
            $this->targetUser->id => ['created_at' => now(), 'updated_at' => now()]
        ]);
    }
}

Architecture Rationale:

• firstOrCreate with routing_code prevents duplicate system alerts for the same event.
• syncWithoutDetaching safely attaches the target user without removing existing recipients.
• Auto-expiration via deactivation_at eliminates the need for periodic cleanup cron jobs. The scopeActive query naturally filters expired records.

Pitfall Guide

1. N+1 Read Tracking Queries

Explanation: Fetching messages and iterating over recipients to calculate read rates triggers a query per message. This collapses under moderate traffic. Fix: Use withCount(['recipients as read_count' => fn($q) => $q->whereNotNull('read_timestamp')]) or pre-aggregate metrics in a materialized view/cache layer.

2. Timezone-Blind Scheduling

Explanation: Storing activation_at and deactivation_at in local time causes visibility windows to shift when users travel or servers change regions. Fix: Always store timestamps in UTC. Convert to user timezone at render time using Carbon's setTimezone() or frontend localization libraries.

3. Over-Engineering with Polymorphic Relations

Explanation: Attempting to separate broadcasts and system alerts into different tables with polymorphic associations adds join complexity, breaks composite indexing, and complicates analytics. Fix: Stick to a single table with a type enum. Nullable columns are cheaper than polymorphic joins and simplify query scopes.

4. Unbounded Broadcast Attachments

Explanation: Calling attach() with 10,000+ user IDs exceeds database packet limits and triggers memory exhaustion in PHP. Fix: Chunk the user ID array and use insertOrIgnore or upsert in batches of 500-1000. Monitor database connection pool limits during peak dispatch windows.

5. Stale Audience Filter Caching

Explanation: Caching filter results indefinitely causes broadcasts to target users who no longer match criteria (e.g., newly verified accounts). Fix: Cache filter results with a short TTL (5-15 minutes). Invalidate the cache when user profile fields referenced in audience_criteria are updated.

6. Missing Transaction Boundaries on Send

Explanation: Updating message status to active before attaching recipients creates a race condition where the message appears live but has no targets. Fix: Wrap status updates and pivot attachments in DB::transaction(). Roll back on any failure to maintain data consistency.

7. Ignoring Expiration in Default Queries

Explanation: Forgetting to apply visibility windows in default model queries causes expired messages to leak into user inboxes. Fix: Register a global scope in the model's booted() method that applies scopeActive() by default. Override explicitly only when admin analytics require historical data.

Production Bundle

Action Checklist

• Run migration to create platform_messages and message_user_reads tables with proper indexes
• Implement PlatformMessage model with type casts, scopes, and localization resolvers
• Build BroadcastDispatcher service with chunked attachment and transaction safety
• Create DispatchSystemAlert job with auto-expiration and deduplication logic
• Add global scope to filter expired/inactive messages by default
• Integrate frontend inbox component using resolveLocalizedTitle/Body methods
• Configure queue worker to process system alerts with appropriate retry/backoff policies
• Set up monitoring for broadcast attachment failures and pivot table growth

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Targeting < 5,000 users	Direct attach() or syncWithoutDetaching()	Simpler code, negligible memory overhead	Low
Targeting > 50,000 users	Chunked insertOrIgnore with 500 batch size	Prevents packet limits, reduces lock contention	Medium (slightly more code)
Dynamic parameterized alerts	System type with translation keys + dynamic_params	Leverages Laravel's translation pipeline, reduces storage	Low
Static marketing campaigns	Broadcast type with localized_titles/localized_bodies	Pre-renders content, avoids runtime translation overhead	Low
Short-lived alerts (< 7 days)	Auto-expire via deactivation_at	Eliminates cleanup jobs, natural lifecycle	None
Compliance/audit retention	Keep expired messages, query via admin-only scope	Preserves historical data without polluting user UI	Low (storage)

Configuration Template

// config/platform_notifications.php
return [
    'system_ttl_days' => 30,
    'broadcast_chunk_size' => 500,
    'supported_locales' => ['en', 'es', 'fr', 'de', 'ja'],
    'default_visibility_window' => [
        'activation_delay_minutes' => 0,
        'auto_expire_days' => 90,
    ],
    'queue_connection' => env('NOTIFICATION_QUEUE_DRIVER', 'database'),
    'max_recipients_per_broadcast' => 100000,
];

// app/Models/PlatformMessage.php (global scope registration)
protected static function booted(): void
{
    static::addGlobalScope('active', function ($builder) {
        $builder->active();
    });
}

Quick Start Guide

1. Run the migration: Execute php artisan migrate to create the platform_messages and message_user_reads tables. Verify indexes on routing_code, read_timestamp, and lifecycle_status.
2. Seed translation keys: Add system alert strings to resources/lang/en/messages.php using the title_translation_key and body_translation_key format.
3. Dispatch a test alert: Call DispatchSystemAlert::dispatch('test_payment_failed', $user, 'payment.failed.title', 'payment.failed.body', ['amount' => 50]) and verify queue processing.
4. Create a broadcast draft: Use the admin form to save a broadcast type message with lifecycle_status = 'draft'. Populate localized_titles and audience_criteria.
5. Activate and monitor: Call BroadcastDispatcher::activate($message, $criteria). Check the user inbox UI to confirm visibility, read tracking, and expiration behavior. Monitor queue workers for system alert delivery.