Register a hook

[chasers]

Note: Hooks v1 should be scoped to web hooks only. Break out function hooks into another issue later. Including here for comms on the final end state.

Hooks

We want to let users run semi-arbitrary code when certain things happen in the system. Users may want to do things when pools are spawned or destroyed. When certain kinds of SQL statements are submitted or when certain records are updated in the database.

• A Hook can be a function hook (Postgres function call) or a web hook (http request)
• Hooks can by sync or async

Function hooks

• When you create a function hook it creates a hook record in the metadata database with the code required to create a Postgres function using a Postgres TLE language.
• When a connection pool starts up Supavisor will create or replace all registered function hooks in the tenant database ensuring they exist.

Web hooks

• Supavisor will manage making the http requests for web hooks.

Async hooks

• Async hooks can be implemented as Telemetry events
• A Telemetry event handler for a tenant database can attached to telemetry events based on hooks for a tenant in the metadata database
• Example: like Realtime's Telemetry.Logger but instead of logging it would dynamically do what was registered with the hook, and the Telemetry events could be registered dynamically

Sync hooks

• Sync hooks should be ran in between hook events and block until the hook response

Hook events

Hook evens will be generated from:

• sql queries
• wal records
• tenant db metrics polling (e.g. db size - see Support limiting for postgres-related statistics #88)

Unknown:

• How would we let users address these?

Examples

As a db admin I want to turn my database to read-only mode when it's used a certain amount of storage

• polling metric queries db size every minute
• polling process emits a Telemetry event with db size
• Telemetry hook handler matches on db size to trigger
• Function registered with hook is called and includes ALTER SYSTEM SET default_transaction_read_only TO on;
• Database is in read only mode now

As a dev I want my Stripe data updated in Stripe when I update a customer record in my database:

• Register a synchronous function hook with code which will use http to make a request to Stripe
• This hook will run on insert statements on a customers table for example
• The insert will error to the SQL client if Stripe responds with an error

Prior Art

• Auth0 Actions: https://www.youtube.com/watch?v=UesFSY1klrI

[J0]

Hey @chasers

Just wondering is this still planned by any chance?