Search feature covering multiple database tables of data

Hi.

I am looking for some pointers on how others have created a search function that covers multiple database tables of data.

For example: Search word ‘Smit’.

Results from:
Table Name: Cases - cases containing the partial word Smit i.e. Smith.
Table Name: Case - Notes (case notes containing the partial word Smit i.e. Smith.
Table Name: Assets - assets containing the name ‘Smit’ or Assigned to ‘Smith’.
Table Name: Users - users with the name or email ‘Smit’ i.e. Smith.

Search needs to be able to display results in a repeating table for each regardless of whether the search word is a partial match or not and contained within a string or is the string.

I thought I had a working solution using Advanced filter on each table, however, that appears to be limited to matching words and not partial matches within the string, therefore isn’t suitable.

I have now used some Javascript to filter provided data, however, this is proving slow as more records are added to the test database so in production this is not going to be suitable either.

Ideally I am looking for a solution that does not require sending data to a 3rd party service to process and would like to keep it within the bubble systems.

How have others handled this kind of requirement in Bubble?

Appreciate any feedback on this.

Nigel