Validate telegram mini app init data in PostgreSQL, can be useful when you want to use supabase client in TMA, pass `initDataRaw` to headers, then in SQL queries get init data (username, user id...)

supabase-client.ts

const supabase = useMemo(() => {
    return createClient(
      process.env.NEXT_PUBLIC_SUPABASE_URL!,
      process.env.NEXT_PUBLIC_SUPABASE_KEY!,
      {
        global: {
          headers: {
            telegram_init_data: initDataRaw || "",
          },
        },
      },
    );
  }, [initDataRaw]);

validate.sql

-- Implement supabase decode_uri function, so we can decode the query parameters.
CREATE OR REPLACE FUNCTION hidden.decode_uri (text) returns text language plv8 strict immutable as $$
  return decodeURIComponent($1);
$$;

-- Here you can find tma validate example using postgresql, so we can check the request on the database.
-- The docs for validating the data can be found here: https://core.telegram.org/bots/webapps#validating-data-received-via-the-mini-app
CREATE
OR REPLACE FUNCTION hidden.validate_query_params (query_params text, token text default '') RETURNS JSON AS $$
DECLARE
    query_params_map jsonb;
    auth_date bigint := 0;
    hash text := '';
    pairs text[] := '{}';
    pair text;
    computed_hash text;
BEGIN
    IF token = '' THEN
        SELECT decrypted_secret INTO token FROM vault.decrypted_secrets WHERE name = 'TELEGRAM_BOT_TOKEN';
    END IF;
    -- Parse the query parameters
    BEGIN
        select
            json_object_agg(
                split_part(param, '=', 1),
                split_part(param, '=', 2)
            ) into query_params_map
            from
            (
                select
                unnest(
                    string_to_array(query_params, '&')
                ) as param
            ) as extracted_params limit 1;

        FOR pair IN SELECT jsonb_object_keys(query_params_map) LOOP
            IF pair = 'hash' THEN
                hash := query_params_map::json->>pair;
            ELSIF pair = 'auth_date' THEN
                BEGIN
                    auth_date := (query_params_map::json->>pair)::bigint;
                    pairs := array_append(pairs, pair || '=' || (query_params_map::json->>pair));
                    IF auth_date IS NULL THEN
                        RAISE EXCEPTION '"auth_date" should present integer';
                    END IF;
                EXCEPTION
                    WHEN others THEN
                        RAISE EXCEPTION '"auth_date" should present integer';
                END;
            ELSE
                pairs := array_append(pairs, pair || '=' || (query_params_map::json->>pair));
            END IF;
        END LOOP;
    END;

    -- Check for the presence and validity of required parameters
    IF length(hash) = 0 THEN
        RAISE EXCEPTION '"hash" is empty or not found';
    END IF;

    IF auth_date = 0 THEN
        RAISE EXCEPTION '"auth_date" is empty or not found';
    END IF;

    -- Sort pairs in alphabetical order
    pairs := (select array_agg(a) from (select unnest(pairs) as a order by a) as s);

    -- Compute the hash
    computed_hash := encode(
            extensions.hmac(
                convert_to(decode_uri(array_to_string(pairs, E'\n')::text), 'UTF8'),
                extensions.hmac(token, 'WebAppData', 'sha256'),
                'sha256'::text
            ),
        'hex'
    );

    -- Verify computed hash against the provided hash
    IF computed_hash <> hash THEN
        return '{}';
    END IF;

    RETURN decode_uri(query_params_map ->> 'user')::json;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION public.current_user_id() RETURNS TEXT as $$
    SELECT hidden.validate_query_params(
        current_setting('request.headers'::text, true)::json ->> 'telegram_init_data',
        current_setting('request.headers'::text, true)::json ->> 'telegram_bot_token'
    )->>'id';
$$ language sql security definer;

validate.ts

import { createHmac } from "crypto";

const data = "TELEGRAM_RAW_DATA";

const params = new URLSearchParams(data);
params.sort();

const hash = params.get("hash");
const withoutHash = new URLSearchParams(data.replace(`&hash=${hash}`, ""));
withoutHash.sort();

const token = "TELEGRAM_BOT_TOKEN";

validate(data, token);
type ValidateOptions = {
  expiresIn?: number;
};

export function validate(
  sp: string | URLSearchParams,
  token: string,
  { expiresIn = 86400 }: ValidateOptions = {}
): void {
  const searchParams = typeof sp === "string" ? new URLSearchParams(sp) : sp;

  let authDate = new Date(0);
  let hash = "";
  const pairs: string[] = [];

  searchParams.forEach((value, key) => {
    if (key === "hash") {
      hash = value;
      return;
    }

    if (key === "auth_date") {
      const authDateNum = parseInt(value, 10);
      if (Number.isNaN(authDateNum))
        throw new TypeError('"auth_date" should present integer');
      authDate = new Date(authDateNum * 1000);
    }

    pairs.push(`${key}=${value}`);
  });

  if (hash.length === 0) throw new Error('"hash" is empty or not found');
  if (authDate.getTime() === 0)
    throw new Error('"auth_date" is empty or not found');

  if (
    expiresIn > 0 &&
    authDate.getTime() + expiresIn * 1000 < new Date().getTime()
  )
    throw new Error("Init data expired");

  pairs.sort();

  const secretKey = createHmac("sha256", "WebAppData").update(token).digest();
  const computedHash = createHmac("sha256", secretKey)
    .update(pairs.join("\n"))
    .digest("hex");

  if (computedHash !== hash) throw new Error("Signature is invalid");
}