There is neither an 'on chain' or a 'native in validator' way to do this.
Due to the underlying nature of how data is stored on Solana - as key-value pairs - anything that requires high performance query based on the value will need a reverse index of some sort.
As you've mentioned, the validator lets you build a few specific reverse indexes (program id -> accounts, token mint -> accounts, token account owner -> accounts), but these are a fixed list and are held in memory (much slower node startup).
The accepted methods for building custom indices for your own use cases are:
Build a Geyser plugin that can be run 'embedded' on validators or RPCs, which can subscribe inline to new transactions / accounts / blocks, and write to an external store in any manner you like. See https://docs.solana.com/developing/plugins/geyser-plugins
Use the RPC to download and process finalized blocks to achieve the same.
In both cases, high availability and idempotency will need to be architected at the application level (make sure you run on enough nodes, use backend stores that are resilient, deal with duplicate processing of same block etc).
There are two 'external provider' options I'm aware of, and probably others too: