First I wanted to write this article on my own, just to explore Azure Durable Functions and build something that sat on my not-good-not-terrible chatbot ideas list for years.

And then I found out that #PlanetScaleHackathon launched the same month and I decided to take a part in it. I required a database and PlanetScale looks like a new and interesting solution to play with.

So what kind of chatbot we are building here? I believe that messenger could be the most convenient way to gather feedback in almost any scenario, but especially in the physical world. But sharing your messenger contacts with anyone will bring spam, trolls, and sometimes unwanted night calls.

Recently Telegram became one of the top-5 downloaded apps worldwide in 2022 and now has over 700 million monthly active users, so the timing is quite good.

It would be nice to have the ability for your customers, clients, or subscribers to contact you directly in Telegram without telegraphing your personal account to everyone.

In this bot scenario we would actually have two bots:

MasterBot will have master users who receive and answer the messages
ClientBot will be attached to the master user and receive messages from clients to resend them to the master user.

Clients will not know the master user's private account name, and masters could know client users' names if they have a username set in their telegram profile.

WhatsApp does this as part of its separate Business App. So let's build a similar solution for Telegram.

What is a Telegram Chatbot

(Chat)Bots are like small programs that run right inside Telegram. They are made by third-party developers. Bots are safe to use. They can see your public name, username, and profile pictures, and they can see messages you send to them, that's it. They can't access your last seen status and don't see your phone number (unless you decide to give it to them yourself).

What is a Durable Function?

The official definition

Durable Functions is an extension of Azure Functions that lets you write stateful functions in a serverless compute environment.

There are two types of durable functions: orchestrator functions and entity functions.

In easy words, the orchestrator function is a function that contains orchestrator, and it could be stopped and resumed at any moment. The main purpose of the orchestrator is to launch other functions (they are called activity functions) and the result could be stored in local variables, durable entities, and used in logic constructions.

This allows the implementation of a lot of cool patterns in a serverless environment, like function chaining for example. function chaining.png You could read more about it in docs.

The entity function is again, a function, that allows saving, reading, and manipulating data in a serverless environment. I think it could be treated as a repository: we have data and a set of operations to manipulate it. The data is stored serialized as JSON in Azure Table Storage.

Entity function provides convenient class-based syntax where entities and operations are represented as classes and methods.

Now when we are familiar with tech, let's design ourselves a chatbot.

Chatbot design

Chatbots always contain some kind of state machine under the hood.

example of state machine

There is an entire theory behind that, but in simple words applied to our case:

a user always is in some state (p0-p5)
any message a user sends to a chatbot will move him into another state (a,b,...)
each time the state changes, our application performs some action and returns a response message to be sent back to the user.

Each state implies some logic it could do and the next state where it could navigate. To implement it in a scalable (and a bit overengineering) way we would use State design pattern.

You might ask yourself: do I need that? Well, the answer is "it depends". If you building a simple chatbot with a couple of commands, or plan to connect your chatbot to a question-answering "AI" or plan to heavily depend on some chatbot framework or engine - you probably could make a several ifs and that would be enough. Because you should K.I.S.S.

State Machine design

Our state machine will be only for the MasterBot. Client bots are extremely simple: they forward messages to the master user, and forward responses back.

For the master bot we would need the next operations:

/start - this operation will move the master user to a MainState
/add - attach new client bot
/remove - detach client bot
/subscribe - update to pro-plan (we want to pay for hosting somehow)
/unsubscribe - mandatory
/qr - generate QR code with client bot invite
Reply - not a command, but an actual reply to a client message, it requires a state.
Ban - is not a command and does not require a state

Not everything here requires a state, a lot of operations have only one step and could be done by going from MainState to MainState.

Each arrow would have an associated Activity Function with it, so each time we change the state of the master user we will execute some action, then generate and send a response back.

The state machine will not be doing any hard work by itself. It will validate input, execute steps and return a name of Activity Function to be executed by the orchestrator.

State Machine Implementation

To implement our state machine in the serverless environment we need three main components:

We need a Context - it defines the interface to clients and maintains a reference to an instance of a State subclass, which represents the current state of the User.
We need our states - all of them will be inherited from the base State abstract class and implement a do-step method.
We need a Durable Entity to store the current user state.

But it's not that easy with Durable Entity. As I mentioned earlier it is stored serialized as JSON by the Newtonsoft.JSON library. And because we want to use inheritance and we want the State to have reference to a user Context - it really messes up serialization.

There is a way to set up Newtonsoft.JSON to store type names and deserialize them into concrete classes by applying TypeNameHandling = TypeNameHandling.All setting. For durable functions it's TypeNameHandling.None by default.

To change this setting we need to override IMessageSerializerSettingsFactory in the Startup class like that

public class Startup : FunctionsStartup
{       
    public override void Configure(IFunctionsHostBuilder builder)
    {
        ...
        builder.Services.AddSingleton<IMessageSerializerSettingsFactory, CustomMessageSerializerSettingsFactory>();
    }

    internal class CustomMessageSerializerSettingsFactory : IMessageSerializerSettingsFactory
    {
        public JsonSerializerSettings CreateJsonSerializerSettings()
        {
            return new JsonSerializerSettings
            {
                TypeNameHandling = TypeNameHandling.All,
                DateParseHandling = DateParseHandling.None,         
            };
        }
    }
}

Unfortunately, that didn't work for me for some reason. I decide to go another way and add two extra components

States enum to store
StateFactory to rebuild concrete state class from stored enum value.

1. Context class

First of all, we need a user context. Context holds the current user state, allows perform transition to other states, and has a single method GetAction.

public class Context
{
    // A reference to the current state of the Context.
    public State State { get; set; }

    public Context(State state)
    {
        TransitionTo(state);
    }

    // The Context allows changing the State object at runtime.
    public void TransitionTo(State state)
    {
        Console.WriteLine($"Context: Transition to {state.GetType().Name}.");
        State = state;
        State.SetContext(this);
    }

    // The Context delegates part of its behavior to the current State
    // object.
    public StateAction GetAction(string messageText) => State?.GetAction(messageText);
}

2. Base State

Base State classes require each inherited class to implement which States it represents. It is required for the factory to reconstruct user context based on Durable Entity which stores user state.

/// <summary>
/// The base State class declares methods that all Concrete State should implement
/// </summary>
public abstract class State
{
    /// <summary>
    /// Property should be implemented in concrete state for our factory to construct concrete type of state
    /// </summary>
    /// <remarks>
    /// It could be Type if we want to use reflection
    /// </remarks>
    public abstract States Type { get; }

    protected Context _context;

    /// <summary>
    /// a backreference to the Context object, associated with the State. 
    /// This backreference can be used by States to transition the Context to another State.
    /// </summary>
    public void SetContext(Context context)
    {
        _context = context;
    }

    /// <summary>
    /// Get Activity Function name
    /// </summary>
    public abstract StateAction GetAction(string messageText);
}

We also create a StateAction record as a result of the GetAction method. This record returns to the orchestrator what needs to be done next: either just send the response, or perform an activity, or maybe both.

public record StateAction(MasterBotResponse Response = default, string Activity = default);

I will not show an implementation of all states, just a couple of classes to understand the pattern.

Main state

This would be the starting point for every user. Based on the input of this state our chatbot will provide one or another answer.

If input a valid command MainState would return either message or activity to launch.

If an input is an unknown command the fallback message is sent back to a user.

class MainState : State
{
    public override States Type => States.Main;

    public override StateAction GetAction(string messageText)
    {
        switch (messageText)
        {
            case "/start":
                return new StateAction(Response: new MasterBotResponse(@"Welcome to a Feedbacks Master Bot"), Activity: nameof(MasterBotActivityFunctions.ActivityRegisterMasterUser));
            case "/add":
                _context.TransitionTo(new AddState());
                return new StateAction(Response: new MasterBotResponse("Create client-facing chatbot through @BotFather and send me generated UserToken"));
            case "/remove":
                return new StateAction(Activity: nameof(MasterBotActivityFunctions.ActivityDoRemove));
            case "/qr":
                return new StateAction(Activity: nameof(MasterBotActivityFunctions.ActivityCreateQrCode));
        }

        return new StateAction(Response: new MasterBotResponse("Unknown command, press **Menu** button for list of commands", Markdown: true));
    }

Workflows

Based on this design it's very easy to combine several steps (states) into a workflow. Imagine a user who wants to delete an item. The flow looks like that

Send /add command
Bot prompt to send bot token
Send token
Validate token, perform the action
Back to the main

Code of the state might look something like that

public class AddState : State
{
    public override States Type => States.Add;

    bool IsValidToken(string messageText)
    {
        return Regex.IsMatch(messageText, "[0-9]{9}:[a-zA-Z0-9_-]{35}");
    }

    public override StateAction GetAction(string messageText)
    {
        if (messageText.Equals("/cancel", StringComparison.OrdinalIgnoreCase))
        {
            _context.TransitionTo(new MainState());
            return new StateAction(new MasterBotResponse("Cancelled"));
        }

        if (IsValidToken(messageText))
        {
            _context.TransitionTo(new MainState());
            return new StateAction(Activity: nameof(MasterBotActivityFunctions.ActivityDoAdd));
        }

        return new StateAction(new MasterBotResponse("Invalid input, try again or /cancel"));
    }
}

Users could send the /cancel command to undo the adding.

Responding State

If other states are triggered by a sent message, this state is triggered by replying. When a user responds to any message it will be forced to RespondingState. We also save a MessageReferenceId from our storage of received messages to use later in the activity function.

Here is what we should add to MasterBotWebhook to handle replies:

// the message is a reply, so we process it into reply state
if (update.Message.ReplyToMessage != null)
{
    var reply = update.Message.ReplyToMessage;
    var messageReference = await _database.GetClientMessageReference(messageId: reply.MessageId);

    if (messageReference == null)
    {
        log.LogError("Message to answer not found");
        return new OkResult();
    }

    // change user to responding state
    var userEntity = new EntityId(nameof(DurableUserState), update.Message.From.Id.ToString());
    await durableClient.SignalEntityAsync<IDurableUserState>(userEntity, us => us.SetUserState(States.Responding));

    // store original message id                    
    await durableClient.SignalEntityAsync<IDurableUserState>(userEntity, us => us.SetReplyToMessageId(messageReference.Id));
}

After that, we start the orchestrator function as usual.

In RespondingState we handle /cancel, we handle sending other command and we launch ActivityResponse

public class RespondingState : State
{
    public override States Type => States.Responding;

    public override StateAction GetAction(string messageText)
    {
        if (messageText.Equals("/cancel", StringComparison.OrdinalIgnoreCase))
        {
            _context.TransitionTo(new MainState());
            return new StateAction(new MasterBotResponse("Cancelled"));
        }          

        var ms = new MainState();
        _context.TransitionTo(ms);

        if (messageText.StartsWith("/"))
        {
            return ms.GetAction(messageText);
        }

        return new StateAction(Activity: nameof(MasterBotActivityFunctions.ActivityResponse));
    }
}

The ActivityResponse function

[FunctionName(nameof(ActivityResponse))]
public async Task<MasterBotResponse> ActivityResponse([ActivityTrigger] Message message, [DurableClient] IDurableEntityClient client, ILogger log)
{
    // update message (icon)
    await _telegramService.SetAnsweredAsync(message); // todo not working

    // get client chat messageRef and botToken
    var entityId = new EntityId(nameof(DurableUserState), message.From.Id.ToString());
    var userState = await client.ReadEntityStateAsync<DurableUserState>(entityId);
    var messageRef = await _database.GetClientMessageReference(messageReferenceId: userState.EntityState.MessageReferenceId);
    var botToken = await _database.GetClientBotToken(messageRef.ClientBotChatId);

    // send responce to a client chat
    await _telegramService.ResponseAsync(new MasterBotResponse(message.Text, messageRef.OriginalFromId), botToken);

    // no answer needed
    return default;
}

3. Durable User State Implementation

This is the place where we use the full power of durable function. On the call, our function will execute the GetAction method of the current state and will navigate to the next state by sending a fire-and-forget signal to itself.

A durable user state is defined by Interface IDurableUserState.

public interface IDurableUserState
{
    Task<States> GetUserState();
    void SetUserState(States state);
    void SetReplyToMessageId(long messageReferenceId);
}

This allows to access Durable Entity in a safe way:

// get user context (user state) from durable entity
var userState = await userProxy.GetUserState();
...
// save new state            
userProxy.SetUserState(userContext.State.Type);

And this is how it's stored in Azure Storage

4. States enum

Our states enum currently is very small and required only for ContextFactory

public enum States
{
    Main,
    Add,
    Responding
}

5. Context Factory

Context Factory is required for restoring stored user State enum value to a State object. It simplifies serialization and deserialization but made code a bit bulkier.

Note that by default we put the user into a Main state.

public class ContextFactory
{
    public Context RestoreContext(States userState)
    {
        var state = RestoreState(userState) ?? RestoreState(States.Main);
        var context = new Context(state);
        return context;            
    }

    private State RestoreState(States state)
    {
        switch (state)
        {
            case States.Main:
                return new MainState();
            case States.Add:
                return new AddState();
            case States.Responding:
                return new RespondingState();
            default:
                return null;
        }
    }
}

Functions design and implementation

Let's design our functions

The flow goes like this

1) The HTTP-trigger function accepts the telegram webhook and launches the orchestrator function

[FunctionName("FeedbackBotsWeebhook")]
public static async Task<IActionResult> HttpStart(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post")] HttpRequest req,
    [DurableClient] IDurableOrchestrationClient starter,
    ILogger log)
{
    log.LogInformation("C# HTTP trigger function processed a webhook.");

    // Function input comes from the request content.
    Message message = await GetMessageFrom(req);

    // Start MessageProcessFunctions Orchestration function
    string instanceId = await starter.StartNewAsync(MessageProcessFunctions, message);
    log.LogInformation($"Started orchestration with ID = '{instanceId}'.");

    return new OkResult();
}

2) Orchestration function process incoming message in this way

Get the current User Context from the Durable Entity associated with it by ChatId
Feed a Message to the current state, get an Action to execute
Execute the Action Function, which will provide a response
Send the response

[FunctionName(MessageProcessFunctions)]
public async Task RunOrchestrator([OrchestrationTrigger] IDurableOrchestrationContext context)
{
    var message = context.GetInput<Message>();

    // create unique id of DurableUserState based on user id
    var userEntity = new EntityId(nameof(DurableUserState), message.From.Id.ToString());
    var userProxy = context.CreateEntityProxy<IDurableUserState>(userEntity);

    // get user context (user state) from durable entity
    var userState = await userProxy.GetUserState();

    // restore context
    var userContext = contextFactory.RestoreContext(userState);

    // do FSM step and get action to execute
    var action = userContext.GetAction(message.Text);

    // save new state            
    userProxy.SetUserState(userContext.State.Type);

    // execute action and get response
    var response = await context.CallActivityAsync<MasterBotResponse>(action, message);

    // send response
    await context.CallActivityAsync(SendResponse, response);
}

It's important to understand how the orchestrator will process this code. You can read all technical details here, but in easy words:

The function executes until it meets await and then will be stopped, and another function (activity or entity) will be launched. If you have a good understanding of the async-await pattern, this is very similar to it.
After the other function is finished the orchestrator wakes up and re-executes the entire function from the start to rebuild the local state.

During the replay, if the code tries to call a function (or do any other async work), the Durable Task Framework consults the execution history of the current orchestration. If it finds that the activity function has already executed and yielded a result, it replays that function's result and the orchestrator code continues to run.

With this in mind, here how the function calls look like:

[2022-07-22T00:05:23.459Z] Executing 'FeedbackBotsWeebhook' (Reason='This function was programmatically called via the host APIs.', Id=69435207-90fa-4da5-97b5-d15d2aa2c4fb)
[2022-07-22T00:05:23.472Z] C# HTTP trigger function processed a webhook.
[2022-07-22T00:05:23.559Z] Got message: User sent message 53 to chat ... at 22.07.2022 0:05:23. It is a reply to message  and has 1 message entities.
[2022-07-22T00:05:23.682Z] Started orchestration with ID = '7eb2fffba7a54e979c7cf05666812f28'.
[2022-07-22T00:05:23.698Z] Executed 'FeedbackBotsWeebhook' (Succeeded, Id=69435207-90fa-4da5-97b5-d15d2aa2c4fb, Duration=270ms)
[2022-07-22T00:05:23.813Z] Executing 'MessageProcessFunctions' (Reason='(null)', Id=f04ab79e-8cf3-42cf-a38b-99ac7ce0b594)
[2022-07-22T00:05:23.892Z] Executed 'MessageProcessFunctions' (Succeeded, Id=f04ab79e-8cf3-42cf-a38b-99ac7ce0b594, Duration=92ms)
[2022-07-22T00:05:24.000Z] Executing 'DurableUserState' (Reason='(null)', Id=46997e18-95a9-4523-af0c-3fc029a17b8c)
[2022-07-22T00:05:24.045Z] Executed 'DurableUserState' (Succeeded, Id=46997e18-95a9-4523-af0c-3fc029a17b8c, Duration=45ms)
[2022-07-22T00:05:24.089Z] Executing 'MessageProcessFunctions' (Reason='(null)', Id=6308c902-06eb-4c25-953e-2a95690d39a5)
Context: Transition to MainState.
[2022-07-22T00:05:28.187Z] Executed 'MessageProcessFunctions' (Succeeded, Id=6308c902-06eb-4c25-953e-2a95690d39a5, Duration=4098ms)
[2022-07-22T00:05:28.232Z] Executing 'DurableUserState' (Reason='(null)', Id=6d918bbe-f647-4c6a-bb22-b8c185512922)
[2022-07-22T00:05:28.241Z] Executed 'DurableUserState' (Succeeded, Id=6d918bbe-f647-4c6a-bb22-b8c185512922, Duration=9ms)
[2022-07-22T00:05:28.254Z] Executing 'ActivityWelcome' (Reason='(null)', Id=ca71be23-192c-4881-8483-84316492425f)
[2022-07-22T00:05:28.264Z] Executed 'ActivityWelcome' (Succeeded, Id=ca71be23-192c-4881-8483-84316492425f, Duration=14ms)
[2022-07-22T00:05:28.313Z] Executing 'MessageProcessFunctions' (Reason='(null)', Id=64f4b18d-746f-48ef-a039-e309be39b63e)
Context: Transition to MainState.
[2022-07-22T00:05:33.383Z] Executed 'MessageProcessFunctions' (Succeeded, Id=64f4b18d-746f-48ef-a039-e309be39b63e, Duration=5069ms)
[2022-07-22T00:05:33.417Z] Executing 'ActivitySendResponse' (Reason='(null)', Id=3da5cf30-be33-463f-804c-36791a791a1e)
Feedback Bots Master sent message 54 to chat ... at 22.07.2022 0:05:34. It is a reply to message  and has  message entities.
[2022-07-22T00:05:33.795Z] Executed 'ActivitySendResponse' (Succeeded, Id=3da5cf30-be33-463f-804c-36791a791a1e, Duration=380ms)
[2022-07-22T00:05:33.832Z] Executing 'MessageProcessFunctions' (Reason='(null)', Id=23177e66-1113-43c7-9a56-8a8f3391083c)
Context: Transition to MainState.
[2022-07-22T00:05:33.845Z] Executed 'MessageProcessFunctions' (Succeeded, Id=23177e66-1113-43c7-9a56-8a8f3391083c, Duration=13ms)

Note that message from restoring context Context: Transition to MainState. was repeated 3 times here. That's because MessageProcessFunctions was relaunched 3 times. This is why the orchestrator function code must be deterministic - always returns the same value given the same input, no matter when or how often it's called.

Activity Functions

For simplicity, I combined all state's activity functions under a single class, except ActivitySendResponse. Activity functions are a place where all hard work is done. They are designed to consume Telegram messages, process them in some way, and return responses.

For example DoAdd function read client input, extracts token from it, and attach a new chatbot.

[FunctionName(nameof(ActivityDoAdd))]
public async Task<MasterBotResponse> ActivityDoAdd([ActivityTrigger] Message message, ILogger log)
{
    log.LogInformation($"Adding client bot for {message.From.Id}");
    var botToken = Regex.Match(message.Text, "[0-9]+:[a-zA-Z0-9_-]{35}").Value;
    var bot = await _telegramService.GetBot(botToken);

    await _database.AddUserTokenAsync(message.From.Id, botToken, bot.Id, bot.Username);
    await _telegramService.Setup(message.From.Id, botToken);

    return new MasterBotResponse($"Chatbot {bot.Username} attached as a client bot");
}

And DoRemove do the opposite

[FunctionName(nameof(ActivityDoRemove))]
public async Task<MasterBotResponse> ActivityDoRemove([ActivityTrigger] Message message, ILogger log)
{
    log.LogInformation($"Removing client bot for {message.From.Id}");

    var botToken = await _database.GetMasterBotToken(message.From.Id);
    var bot = await _telegramService.GetBot(botToken);
    await _telegramService.UnSetup(botToken);

    return new MasterBotResponse($"Chatbot {bot.Username} detached as client bot");
}

I like how every activity function becomes an independent testable piece of functionality. Separation of concerns at its best.

Storage

Because I love to try new things I decided to try PlanetScale as a database for the project.

First of all, I love when a database is compatible with something common used. It's frustrating to have a custom API for each DB out there. But this usually comes with some trade-off, fortunately, non of the existing limitations of PlanetScale will prevent us from using it in the project.

We will need a database in several places

To store user profile
To store user-attached bots
To store attached bots message references (we will not store messages themselves)

For simplicity all database operations will be provided by a single service:

public interface IDatabase
{
    Task<MasterBotUser> NewMasterUser(long fromId, string userName);
    Task AddUserTokenAsync(long fromId, string botToken, long chatId, string botName);

    Task<string> GetClientBotToken(long clientBotChatId);
    Task<string> GetClientBotName(long fromId);
    Task<long> GetMasterBotChatId(string clientToken);        
    Task<long> GetMasterBotFromId(string clientToken);
    Task<string> GetMasterBotToken(long fromId);

    Task<long> SaveClientMessageReference(string clientToken, long clientId, int messageId, int resendMessagId);
    Task<ClientBotsMessageReference> GetClientMessageReference(long? messageReferenceId = default, long? messageId = default);
}

Maybe I overdid it with the atomization of getting a master bot user, it's a tiny anyway.

Database Structure

Right now our database schema contains only three tables

The banned user's table does not exist yet but will be added in the future.

To store user profile

This table will store the user profile, its limits and link paddle account for handling user subscription.

CREATE TABLE MasterBotUsers (
  FromId BIGINT NOT NULL PRIMARY KEY,
  Username varchar(255) NOT NULL,
  IsPro TINYINT NOT NULL default 0,
);

To store user-attached bots

CREATE TABLE ClientBots (
  ChatId BIGINT NOT NULL PRIMARY KEY,
  Name varchar(255) NOT NULL,
  Token varchar(255) NOT NULL,  
  MasterFromId BIGINT,
  KEY master_from_id_idx (MasterFromId)
);

To store attached bots message references

Here we store the original message id and original token to be able to send answers, the id of resending the message to handle replies, and IsAnswered flag to use in the future for statistics.

CREATE TABLE ClientBotsMessageReference (
  Id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,

  OriginalMessageId INT NOT NULL,
  OriginalFromId BIGINT NOT NULL,
  ResendMessageId INT NOT NULL,

  IsAnswered TINYINT NOT NULL default 0,
  ClientBotChatId BIGINT,
  KEY client_chat_id_idx (ClientBotChatId)
);

Database queries

First I think about using EF here because EF Code First is very good. But in fact, most of the data manipulation is quite simple here, so I used Dapper for my SQL mapping and that's it.

I will not show you all queries, you could check them in github if you wish, only couple to understand the pattern.

Here we have an upsert operation with INSERT IGNORE for adding a new master user.

public async Task<MasterBotUser> NewMasterUser(long fromId, string userName)
{
    var masterBot = new MasterBotUser
    {
        FromId = fromId,
        Username = userName,
    };

    string sqlQuery = "INSERT IGNORE INTO MasterBotUsers (FromId, Username) VALUES(@FromId, @Username)";
    await _connection.ExecuteAsync(sqlQuery, masterBot);

    return masterBot;
}

Here we store information about attached client chatbot

public Task AddUserTokenAsync(long fromId, string botToken, long chatId, string botName)
{
    var clientBot = new ClientBot
    {
        ChatId = chatId,
        Token = botToken,
        MasterFromId = fromId,
        Name = botName
    };

    string sqlQuery = "INSERT IGNORE INTO ClientBots (ChatId, Name, Token, MasterFromId) VALUES(@ChatId, @Name, @Token, @MasterFromId)";
    return _connection.ExecuteAsync(sqlQuery, clientBot);
}

Getting MessageReferencesId

Usually in MySQL to get an auto-generated primary key value after INSERT we could use select LAST_INSERT_ID();

But instead of results, Dapper returns exception The given key '0' was not present in the dictionary when trying to execute the select. I didn't figure out why this happens and wonder if it could be a limitation on the PlanetSide part.

Official Vitess project board mentions that LAST_INSERT_ID with attributes is not supported yet, but nothing about an attributeless.

So I was forced to manually get the latest inserted value in SaveClientMessageReference:

var messageRef = new ClientBotsMessageReference
{
    OriginalMessageId = messageId,
    OriginalFromId = clientId,
    ClientBotChatId = await GetMasterBotChatId(clientToken),

    IsAnswered = false,
    ResendMessageId = resendMessagId,
};

// insert new client message ref
string sqlQuery = @"INSERT INTO ClientBotsMessageReference (OriginalMessageId, OriginalFromId, ClientBotChatId, IsAnswered, ResendMessageId) 
    VALUES(@OriginalMessageId, @OriginalFromId, @ClientBotChatId, @IsAnswered, @ResendMessageId);";
await _connection.ExecuteAsync(sqlQuery, messageRef);

// get latest ID        
var result = await _connection.ExecuteScalarAsync<long>("SELECT Id FROM ClientBotsMessageReference WHERE " +
    "OriginalMessageId = @OriginalMessageId AND OriginalFromId = @OriginalFromId AND ResendMessageId = @ResendMessageId",
    new { messageRef.OriginalMessageId, messageRef.OriginalFromId, messageRef.ResendMessageId });

return result;

Deploying

Deploying and setup of Master Chatbot might be a bit complicated at the moment. Maybe in the future, I would be able to add an ARM Template and make a one-click setup. But for now, to deploy you should do the next:

Deploy

Deploy Feedback bots to Azure, open Azure Function App configuration
Create Database tables from Database.sql in PlanetScale and publish it to production

Setup Azure Function App

Set Uri to a FQDN of your Azure Function App (without https://)
Set ConnectionString:Default to a connection string of your MySQL-compatible database
Create a Host key for your functions in App Keys blade of your Azure Function App
Set ClientId to a created key - it will be used to auth webhooks

Setup Master Bot

Create a Master Bot using @BotFather
Set MasterToken setting to the chatbot token obtained from BotFather
Execute api/setup function with the admin key - this will setup the webhook for your master bot
Send /start to master bot, check logs of MasterBotWebhook for message like Got message: User sent message 123 to chat **123456789** at 30.07.2022 22:23:14.
Set MasterChatId to your chat number id

Setup Client Bots Now your Master Bot is operational and you are ready to add client bots

Create a Client Bot using @BotFather
Send /add command to Master Bot
Send the client bot's token to a master bot, the confirmation message should return

Setup is done, now any message to the Client Bot will be forwarded to a Master Bot. Reply to the Message in Master Bot to send back an answer.

Conclusion and SaaS version

Now you have a fully functional chatbot based on Azure Durable Functions. The source code is available on GitHub with an AGPL license.

Feel free to deploy and use it for yourself.

If you don't want to bother with deployment and just want to set up your feedback chatbot I also built a SaaS version of FeedbackBots available here. It's exactly the same, only multi-tenant with a subscription.

What is the future of the project?

I like how it's looking so far. Azure grant 1 million function executions per month for free, Azure Storage costs a penny, and PlanetScale has a free plan, so I will not spend anything until (with strong if) the product gets some traction.

I'm not fully happy with that though

My payment provider Paddle temporarily suspended my account yesterday - beautiful timing, until I provide them with some legal documents. So right now subscription does not work.
I need to implement the Block feature, it requires a new database table and another action function.
Add support of different types of answers: image, videos, locations (maybe even pay link?)
I don't really like the current texts on the landing page and in a chatbot. I need to invest in some better wording.
Maybe ProductHunt? Nah, probably it's not good enough.

Anyway, thanks for your attention, and if you have any questions feel free to contact me on the bot @FeedbackBotsSupportBot or on Twitter

Note for Hackathon Judges

The easiest way to test a FeedbackBots solution is

use @FeedbackBotsMasterBot
create your own client bot with @BotFather
attach your client bot to MasterBot with /add command
send some messages to the client bot and answer them in the master bot

How to build Telegram Chatbots with Azure Durable Functions

Comments

More from this blog

Building a Powerful MCP Server in .NET with Just 20 Lines of Code

WinUI 3: How to Set Minimum Window Size (Desktop)

Debugging .NET in Docker: Mount Points and Cross-platform Symbols

Harmonizing Line Endings Across Windows and WSL

Extending Visual Studio 2022

What is a Telegram Chatbot

What is a Durable Function?

Chatbot design

State Machine design

State Machine Implementation

1. Context class

2. Base State

Main state

Workflows

Responding State

3. Durable User State Implementation

4. States enum

5. Context Factory

Functions design and implementation

Activity Functions

Storage

Database Structure

To store user profile

To store user-attached bots

To store attached bots message references

Database queries

Getting MessageReferencesId

Deploying

Conclusion and SaaS version

What is the future of the project?

Command Palette

Comments

More from this blog

What is a Telegram Chatbot

What is a Durable Function?

Chatbot design

State Machine design

State Machine Implementation

1. Context class

2. Base State

Main state

Workflows

Responding State

3. Durable User State Implementation

4. States enum

5. Context Factory

Functions design and implementation

Activity Functions

Storage

Database Structure

To store user profile

To store user-attached bots

To store attached bots message references

Database queries

Getting MessageReferencesId

Deploying

Conclusion and SaaS version

What is the future of the project?