Your plan seems like a good approach to me. However, the thought has just struck me that what you’re describing is actually the job that RDB shredder already does (GitHub and docs).
The shredder flattens out custom events and contexts into their own federated tables for a relational DB like Redshift, which can’t deal very well with JSON.
There’s also the more recent Postgres Loader (docs), which unlike the above works on stream rather than batch - I’m not very familiar with it so I’m not sure if it shreds the data in the same way but there’s a shred module so I’m guessing that’s a good start.
So actually, forking one of those projects, or lending from their approach might actually be worthwhile here.
Also will the foreign keys be the same irrespective of context? Example: for performancetiming will the keys still be true timestamp and event id (which as per my understanding will be used to join with main context to generate the web model)?
Actually it’s event_id and collector_tstamp. The shredder will rename these to root_id and root_tstamp for the child tables (atomic.events will keep event_id and collector_tstamp). true_tstamp isn’t always set, whereas collector_tstamp is (also it’s a reliable source of truth for actual time in the world since it’s set by the collector and unaffected by any client clock).
A longer term question, how to go about generalizing the above to dynamic contexts (as of now i have preset the contexts I want, but every time my js tracker code changes I have to make code changes with the above approach). Is making use of Event inventory a part of solution? (Thanks for that awesome link)
The low-tech approach, which we used to follow, is to enforce that for any new schema, the table must be created in advance of the tracking going live. This approach also required JSONpath files - not sure if that’d be relevant for you.
The shiny new approach is to implement logic in the loaders, which calls Iglu when it finds a new event or context, and creates the relevant table or column. If you do some digging into the two loaders I’ve linked you to already, I’m sure the logic is in there also!