Optionally send copy to JDBC

[kevinwallimann]

Background

This issue should implement sending a copy of the message from the EventGate Lambda to a database (e.g. Aurora / PostgreSQL)
Probably the target database can be generalized as just a configurable jdbc connection, no need to make it more specific.

The table schema should be similar like for the Kafka topic.

If possible, the script should handle the table creation. The jobs field can be mapped to a json field, so there will be only one table for the runs topic. But it could also be normalized to a child table, if that's preferred.

The database itself should be spun up probably as part of the deployment terraform scripts

Use cases

• As a support user for an internal application, I'd like to connect to the database and write (ad-hoc) SQL queries on my laptop to get the failed pipelines and their failure reasons of the last 24 hours, which I will then post on a teams channel once a day. I'm capable of using a DB client and writing SQL
• As the internal application, we'd like to query the runs status table to show the executions of the last 90 days per pipeline. We'd like to display job catalog id, start time, end time, and the job status, filtered by tenant id and job catalog id. To do this, we may want to connect directly to the DB, or use basic BI tool in between (like Metabase)
• There is no need to keep the data for longer than 90 days in the database, to avoid unbounded data growth. Archiving the data can be achieved with the internal application.

Feature

A description of the requested feature.

Example [Optional]

A simple example if applicable.

Proposed Solution [Optional]

Solution Ideas:
1.
2.
3.

[ABMC831]

@kevinwallimann not sure this is good idea,
Event Bus "IS" that kafka queue - that is, external one
internal one "IS", EventBridge, again, connected via some queuing mechanism

"EvenGate" - this repo, is just one of potential options how to write into the EventBus,
right now it writes to the external one.... and soon (PR) it would be writing to both, internal and external

i.e. means how to post message to the EventBus(-es),
but it does make little sense to have attached additional functionality there,
what you want might be better suited to be consumer of the queue (either internal or external one), and capture a copy of that into your DB, hence you would be able to capture all the EventBus messages, regardless of the means how they were posted

[ABMC831]

well apparently, there was a change in architecture,.... so this might be making sense now.....

[ABMC831]

DB would be better DynamoDB since our JSON is not entirely suited for relational database since schema is not as fixed as it should be

[oto-macenauer-absa]

Probably the target database can be generalized as just a configurable jdbc connection, no need to make it more specific.

It doesn't make sense to have it general since this is supposed to be part of the solution. The DB of choice is Postgres as it will perform better with analytical queries.

So the tasks will be:

• Create function writing message to the database
  • lambda is not responsible for table creation
  • write should fail if the message schema differs from the DB schema
  • connection parameters should be specified using secrets manager
• Create endpoint to cleanup database records
  • every row should have TTL column
  • add endpoint to api that would do the cleanup
  • external job would call the endpoint