Domain event storage in SQL... use JSON serialization?

I'm looking at refactoring an existing code-base before we let it loose in the wild with our first customer, and I really don't like the current domain event storage structure and I was trying to come up with a good way to store the many & very different events in RDB tables.

Architecture:

• Web app
• Java
• Spring 4
• Spring JDBC
• MSSQL

Details:

• 40+- different events, each related to either the aggregate root or one of it's child elements.
• Details of the event are stored, but not the object state (so no CQRS event sourcing)
• Events are ONLY used for reports
• Reports are fed with java objects. IE. the reports do NOT run directly off of SQL.

Currently the events are stored in a single event table per bounded context. In order to hold all the different event types & data the table schema looks like this

event_id    long,
event_type  varchar,
event_time  datetime,
context_1_key varchar,
context_1_val varchar,
context_2_key varchar,
context_2_val varchar,
context_3_key varchar,

...repeat like 10x...

so, for example(order=aggregate root, item=child of order):

event_type=ITEM_OPEN
context_1_key=ORDER_ID
context_1_value=1000
context_2_key=ITEM_ID
context_2_value=2000

No I don't like it, and no I was not responsible for doing that.

Issues:

• context_1_xxx fields are fragile and difficult to maintain/troubleshoot/expand
• Everything stuffed into one table will be a performance problem(even though reporting is not performance sensitive)
• Events are linked to the domain object; they don't store the state of the object. eg. the recorded event is useless if the item is deleted

My gut tells me creating 40 different tables with schema unique to each event is not the answer. Instead I was thinking of serializing(JSON) a snapshot of the domain object(s) to be saved along with the event data.
It seems convenient solution:

• we already use a Spring/Jackson module to serialize objects for the browser-based clients.
• the team is pretty comfortable with the serialize/deserialize process so there is no major learning curve.
• the event data must go back through the application for generating reports which will be easy enough by de-serializing with Jackson

The only real downsides I can see are: - unable to use SQL based 3rd party reporting tools - unable to index the tables on the properties of the stored object(JSON)

I can somewhat mitigate issue#2 by further breaking down the event storage into a few different tables.

What else am I missing? Is there an established best-approach to accomplishing this? How do you do it?

Start with Building an Event Storage, by Greg Young.

Konrad Garus describes an event store using PostgresSQL.

My gut tells me creating 40 different tables with schema unique to each event is not the answer.

Probably not. First cut should be a single table for events of all types. You have a blob (json is fine) for the event data, and a similar blob for the event metadata, and then a bunch of columns that you use to extract correctly ordered histories of events.

Instead I was thinking of serializing(JSON) a snapshot of the domain object(s) to be saved along with the event data.

That's an odd phrase. JSON representation of the event data? That makes sense.

"Snapshot" is an eyebrow raiser, though -- you don't need a snapshot of the event data, because the event is immutable. You definitely don't want to be mixing state snapshots (ie, the result of rolling up the history of events) with the events themselves.

Followup: looking at how a GetEventStore .NET client writes and reads event histories might give you additional ideas for how to design your schema. Notice that the event data/metadata are being handled as blobs.