Adding Indexable Columns to Stored Events

[anabeto93]

For the purposes of security when dealing with payments and transactions, would like to ensure that any transaction that is going to be settled was creating using the right means, ie through the account aggregate. The way we check this is ensuring there exists a TransactionCreatedEvent event in stored_events where the event_properties contains the system_trace (system trace audit number` . Noticed a serious performance lag when searching for Transactions that have stored_events.

The recordedEvent relationship as used on eloquent below

    public function recordedEvent(): \Illuminate\Database\Eloquent\Relations\HasOne
    {
        return $this->hasOne(config('event-sourcing.stored_event_model'), 'event_properties->system_trace', 'system_trace');
    }

    public function hasRecordedEvent(): bool
    {
        return $this->recordedEvent()->exists();
    }

Tried creating an indexed computed column to improve the performance but currently unable to do so. Creating a new migration to add the column to stored_events table.

Schema::table('stored_events', function (Blueprint $table) {
            $table->string('transaction_system_trace')
                ->nullable()
                ->virtualAs('`event_properties`->>"$.system_trace"')
                ->after('event_properties')
                ->index();
        });

Don't know what I'm doing wrong but this does not work. Testing with sqlite by the way so I don't know if it affects this somehow. Would like to replace 'event_properties->system_trace' with 'transaction_system_trace' in the recordedEvent() relationship.