[Feature Request] Update the Redash webhook payload to be a Snowplow-compatible JSON

[alexanderdean]

This is a proposal to take the existing Redash webhook and make some minor changes to make it compatible with Snowplow.

As well as allowing a Redash+Snowplow user to warehouse all of their Redash activity in Snowplow for audit and compliance, this has the additional benefit that it ties the Redash webhook to an agreed JSON Schema format.

JSON Schema

We have published a JSON Schema for the Redash webhook as part of Iglu Central, here:

https://github.com/snowplow/iglu-central/blob/master/schemas/io.redash.webhooks/event/jsonschema/1-0-0

The exact naming of the webhook schema (io.redash.webhooks/event) was checked with @arikfr and should allow Redash to add further more-specific webhooks in the future as needed.

The schema has been carefully specified so that the generated Redshift table maps very closely onto Redash's underlying event table:

https://github.com/snowplow/iglu-central/blob/master/sql/io.redash.webhooks/event_1.sql

Steps to make the Redash webhook compatible

Currently the Redash webhook is a POST request with the event data encoded in the body as form data.

We want to change this to JSON, but specifically Iglu-compatible self-describing JSON (Snowplow's schema system is called Iglu).

In place of the current POST payload, the Redash webhook should look like this:

{
  "schema": "iglu:io.redash.webhooks/event/jsonschema/1-0-0",
  "data": {
    "id": 23,
    "user_id": null,
    "action": "DELETE",
    ...
  }
}

Notes:

• The event's integer types should be kept as numerics in JSON
• The created_at field should be a valid ISO8601 date-time

We can easily check any test webhook JSON against the JSON Schema to verify that they match (if there is an error in the JSON Schema, we can patch it in a new Iglu Central release).

Outcome

When this has been implemented, it will be possible to warehouse all Redash webhooks in Snowplow with zero coding, using Snowplow's built-in Iglu webhook adapter.

There are some really interesting use cases here:

• Full audit log for Redash in Snowplow (allowing the events table in Redash to be regularly truncated?)
• Real-time detection (ML-driven?) of a bad actor based on their SQL queries (e.g. PII theft, corporate espionage)