Create Message Module

[KDwevedi]

Functionality

• Converts incoming gupshup message notifications to XMessage format
• XMessage type data is then forwarded to other services and common db

TODO

• [x] POST /gupshup/whatsapp endpoint
• [x] Interfaces of type GSWhatsAppMessage and XMessage
• [ ] Supabase Service

References

• Nest Module Structure
• XMessageDAO
• XMessageRepository for inbound reference

  Supabase:

• timescaledb
• Queries need to be optimized on BotID, UserID, Timestamp
• Use a Summary Table as temporary cache ~Redis - UseID based last 100 messages will be stored.~

[KDwevedi]

On creating hypertables and timescale db from Supabase AI

In Supabase, hypertables are virtual tables that are partitioned into chunks based on time intervals. They act as a proxy for the actual table and make it easy to query and manage time-series data.

To create a hypertable, you can use the create_hypertable function. Here's an example of creating a hypertable for a table named "temperatures":

create table temperatures (
  time timestamp not null,
  sensor_id int not null,
  temperature double precision not null
);

select create_hypertable('temperatures', 'time');

Once you have created a hypertable, you can populate it with data using INSERT statements. Here's an example of inserting some values into the "temperatures" table:

insert into temperatures (time, sensor_id, temperature)
values
    ('2023-02-14 09:00:00', 1, 23.5),
    ('2023-02-14 09:00:00', 2, 21.2),
    ('2023-02-14 09:05:00', 1, 24.5),
    ('2023-02-14 09:05:00', 2, 22.3),
    ('2023-02-14 09:10:00', 1, 25.1),
    ('2023-02-14 09:10:00', 2, 23.9),
    ('2023-02-14 09:15:00', 1, 24.9),
    ('2023-02-14 09:15:00', 2, 22.7),
    ('2023-02-14 09:20:00', 1, 24.7),
    ('2023-02-14 09:20:00', 2, 23.5);

Finally, you can query the hypertable using timescaledb's time_bucket function to divide the time-series into intervals of the specified size (in this case, 1 hour) averaging the temperature reading within each group. Here's an example of querying the "temperatures" table:

select
    time_bucket('1 hour', time) AS hour,
    avg(temperature) AS average_temperature
from
    temperatures
where
    sensor_id = 1
    and time > NOW() - interval '1 hour'
group by
    hour;

This will return the average temperature readings for sensor 1 in 1-hour intervals for the past hour.

Convert the query part to JS client To execute the SQL query mentioned in the previous answer using the Supabase JS client, you can use the supabase.from method to create a query builder instance and then use the select method to specify the columns to select and the where method to filter the results. Here's an example:

const { data, error } = await supabase
  .from('temperatures')
  .select(`
    time_bucket('1 hour', time) AS hour,
    avg(temperature) AS average_temperature
  `)
  .eq('sensor_id', 1)
  .gt('time', new Date(Date.now() - 60 * 60 * 1000)) // past hour
  .group('hour')

if (error) {
  console.error(error)
} else {
  console.log(data)
}

This will return an array of objects, where each object represents a row in the result set. The hour and average_temperature columns can be accessed using the dot notation, like row.hour and row.average_temperature.

[KDwevedi]

On indexing by Supabase AI

To optimize tables for lookup based on certain IDs, you can create an index on the column(s) used for the lookup.

For example, suppose you have a table named "users" with a column named "email" that you frequently use to look up users. You can create an index on the "email" column to speed up the lookup:

create index idx_users_email on users (email);

This will create an index named "idx_users_email" on the "email" column of the "users" table. The index will allow Postgres to quickly find the rows that match a given email address, instead of scanning the entire table.

You can also create a unique index on the column(s) used for the lookup to enforce uniqueness and further speed up the lookup:

create unique index idx_users_email_unique on users (email);

This will create a unique index named "idx_users_email_unique" on the "email" column of the "users" table. The unique index will ensure that each email address can only appear once in the table, and will further speed up the lookup.

Note that creating too many indexes can slow down write operations, so it's important to only create indexes that are necessary for your queries. You can use the explain command to see how Postgres is executing your queries and identify which indexes are being used.

[KDwevedi]

SQL Query to Create the Table:

Requires modification basis hypertables and indexing

CREATE TABLE XMessage (
    id UUID,
    userId TEXT NOT NULL,
    fromId TEXT NOT NULL,
    channel TEXT,
    provider TEXT,
    timestamp TIMESTAMPTZ NOT NULL,
    messageState TEXT,
    xMessage TEXT,
    app TEXT,
    auxData TEXT,
    messageId TEXT,
    replyId TEXT,
    causeId TEXT,
    sessionId UUID,
    ownerOrgId TEXT,
    ownerId TEXT,
    botUuid UUID,
    tags TEXT[],
    respMsgId TEXT,
    remarks TEXT,
    PRIMARY KEY (userId, fromId, timestamp)
);

[KDwevedi]

Prospective XMessage interface using DAO

interface XMessage {
    id: string; // UUID
    userId: string;
    fromId: string;
    channel: string;
    provider: string;
    timestamp: string; // ISO string for LocalDateTime
    messageState: string;
    xMessage: string;
    app: string;
    auxData: string;
    messageId: string;
    replyId: string;
    causeId: string;
    sessionId: string; // UUID
    ownerOrgId: string;
    ownerId: string;
    botUuid: string; // UUID
    tags: string[];
    respMsgId: string;
    remarks: string;
}

[KDwevedi]

Basic functions for Supabase Interaction

import { createClient } from '@supabase/supabase-js';

const supabaseUrl = 'your_supabase_url';
const supabaseKey = 'your_supabase_key';
const supabase = createClient(supabaseUrl, supabaseKey);

async function createXMessage(message: XMessage) {
    const { data, error } = await supabase
        .from('XMessage')
        .insert([message]);
    return { data, error };
}

async function getXMessages() {
    const { data, error } = await supabase
        .from('XMessage')
        .select('*');
    return { data, error };
}

async function updateXMessage(id: string, updatedFields: Partial<XMessage>) {
    const { data, error } = await supabase
        .from('XMessage')
        .update(updatedFields)
        .match({ id });
    return { data, error };
}