Connecting SQL server to IFTTT

I know I promised a follow up on 'Aspects of Data warehousing' but I got side tracked. I have a fond interest in IoT and all those new technologies. Especially technologies like IFTTT and MQTT. The challenge is to bring these in relation to my work field: data warehousing and business intelligence.
I just came up with the idea of linking an event in a database to blink a hue-lamp in my house. Crazy or not! But think of it, something happens during the nightly processing of the data warehouse and my hue-lamp at home turns red. Funny and useful.

How to crack this challenge ...

1) we need a kind of FIFO in the database to register our events.
2) we want to use IFTTT, because IFTTT can control my hue-lights.
3) we need to create a bridge to link the dequeuing of the FIFO to IFTTT (built in Java)
4) we will use Microsoft SQL server as database (just as example)

Lets define a table for our FIFO:

CREATE TABLE [ttm].[ADM_TELEMETRY_LOG](
    [ID] [numeric](8, 0) IDENTITY(1,1) NOT NULL,
    [TOPIC] [varchar](255) NOT NULL,
    [SUBJECT] [varchar](255) NOT NULL,
    [VALUE] [varchar](255) NOT NULL,
    [TIMESTAMP] [datetime] NOT NULL,
    [STATUS] [char](3) NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [cdxFifoQueue] ON [ttm].[ADM_TELEMETRY_LOG]
(
    [ID] ASC
)
GO

And a routine to enqueue an event:

create procedure [ttm].[pll_enqueue_ttm_log]
  @vTopic varchar(255),
  @vSubject varchar(255),
  @vValue varchar(255)
as
  set nocount on;

  INSERT INTO [ttm].[ADM_TELEMETRY_LOG]
           ([TOPIC]
           ,[SUBJECT]
           ,[VALUE]
           ,[TIMESTAMP]
           ,[STATUS])
     VALUES
           (@vTopic
           ,@vSubject
           ,@vValue
           ,getdate()
           ,'N');
GO

And another routine to dequeue an event:

CREATE procedure [ttm].[pll_dequeue_ttm_log]
as
declare
  @Deleted table (ID bigint, TOPIC varchar(255), SUBJECT varchar(255), VALUE varchar(255));
begin
  set nocount on;

  with cte as (
    select top(1) ID, TOPIC, SUBJECT, VALUE
      from [ttm].[ADM_TELEMETRY_LOG] with (rowlock)
    order by ID)

  delete from cte
    output deleted.ID, deleted.TOPIC, deleted.SUBJECT, deleted.VALUE into @Deleted
  
  select TOPIC, SUBJECT, VALUE FROM @deleted;
end;
GO

BTW these routines are inspired on an article by Rosanu Consulting.

Lets setup our interface to IFTTT.

IFTTT knows something called 'Webhooks'. By using GET/POST URL's you can 'generate' an event within IFTTT. A nifty method.

 You first have to signup with IFTTT and then register with Webhooks. They provide you with a 'key' which you must use to identify your application with IFTTT webhooks.

The only thing left is to build a bridge between a dequeue of our FIFO and sending a 'POST' to IFTTT.
I used Java to build such a bridge. I will publish the source on request.

The last thing we have to do is create a applet in IFTTT to relate our Webhook event to blinking our Hue-light:

To test is to enqueue an event into our FIFO. The Java bridge will dequeue it and post a URL with IFTTT.
IFTTT will execute the applet and my Hue-Go lamp will blink.