Android Example [B4X] AS FeatureRequest - Supabase as backend

[B4X] [XUI] AS FeatureRequest [Payware]

AS FeatureRequest is a lightweight, easy-to-integrate library that allows you to present a list of potential features to your users, enabling them to vote on the ones they find most valuable. By collecting and prioritizing user feedback, you can accelerate the development of the most desired...

www.b4x.com

In this example I show you how to use supabase as a backend for the AS_FeatureRequest quickly and easily.

Supabase

[B4X] Supabase - The Open Source Firebase alternative

Supabase is an open source Firebase alternative. It provides all the backend services you need to build a product. Supabase uses Postgres database with real-time capabilities. Basically, supabase provides an interface to manage postgres database that you can use to create table and insert, edit...

www.b4x.com

1. Create tables

create table
  public."dt_FeatureRequest" (
    "Id" bigint generated by default as identity not null,
    "CreatedAt" timestamp with time zone not null default now(),
    "Title" text not null,
    "Description" text null,
    "isPremiumFeature" boolean null default false,
    "Status" integer null default 0,
    "ImplementedVersion" text null,
    "AppId" bigint null,
    constraint dt_FeatureRequest_pkey primary key ("Id"),
    constraint dt_FeatureRequest_AppId_fkey foreign key ("AppId") references "dt_FeatureRequestApp" ("Id") on update cascade on delete cascade,
    constraint dt_FeatureRequest_Status_fkey foreign key ("Status") references "dt_FeatureRequestStatus" ("Status") on update cascade on delete cascade
  ) tablespace pg_default;

create table
  public."dt_FeatureRequestUserUpvotes" (
    "Id" bigint generated by default as identity not null,
    "CreatedAt" timestamp with time zone not null default now(),
    "UserId" uuid not null default auth.uid (),
    "FeatureRequestId" bigint not null,
    "isUpvote" boolean not null,
    "AppId" bigint null default '1'::bigint,
    constraint dt_FeatureRequestUserUpvotes_pkey primary key ("UserId", "FeatureRequestId"),
    constraint dt_FeatureRequestUserUpvotes_AppId_fkey foreign key ("AppId") references "dt_FeatureRequestApp" ("Id") on update cascade on delete cascade,
    constraint dt_FeatureRequestUserUpvotes_FeatureRequestId_fkey foreign key ("FeatureRequestId") references "dt_FeatureRequest" ("Id") on delete cascade
  ) tablespace pg_default;

The constraint prevents the same user from voting for the same feature several times at database level, the AS_FeatureRequest already takes this into account, but still save it again at database level.
Defines the status of a feature e.g. 3= In development

create table
  public."dt_FeatureRequestStatus" (
    "Id" bigint generated by default as identity not null,
    "CreatedAt" timestamp with time zone not null default now(),
    "Status" bigint not null,
    "Name" character varying not null,
    constraint dt_FeatureRequestStatus_pkey primary key ("Id"),
    constraint dt_FeatureRequestStatus_Status_key unique ("Status")
  ) tablespace pg_default;

This table contains the apps from us, so we can easily select which app belongs to which feature request. This way we can use the same database for different apps and have less effort.

create table
  public."dt_FeatureRequestApp" (
    "Id" bigint generated by default as identity not null,
    "CreatedAt" timestamp with time zone not null default now(),
    "Name" character varying null,
    constraint dt_FeatureRequestProducts_pkey primary key ("Id")
  ) tablespace pg_default;

2. Activate anonymous login in the supabase settings:

read more:

[B4X] Supabase - Sign In Anonymously

https://www.b4x.com/android/forum/threads/b4x-supabase-the-open-source-firebase-alternative.149855/ Supabase now supports anonymous user login, the B4X library now too. https://supabase.com/blog/anonymous-sign-ins Create and use anonymous users to authenticate with Supabase Enable Anonymous...

www.b4x.com

3. Add RLS roles

Role for dt_FeatureRequest

Roles for dt_FeatureRequestUserUpvotes

4. Create RPC function to select the data we need

CREATE OR REPLACE FUNCTION GetFeatureRequests(AppId INTEGER DEFAULT 1)
RETURNS TABLE (
  Id BIGINT,
  CreatedAt TIMESTAMP WITH TIME ZONE,
  Title TEXT,
  Description TEXT,
  isPremiumFeature BOOLEAN,
  Status INTEGER,
  ImplementedVersion TEXT,
  VoteCount BIGINT,
  UserVoteStatus INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        fr."Id",
        fr."CreatedAt",
        fr."Title",
        fr."Description",
        fr."isPremiumFeature",
        fr."Status",
        fr."ImplementedVersion",
        COALESCE(SUM(CASE
                      WHEN u."isUpvote" = true THEN 1
                      WHEN u."isUpvote" = false THEN -1
                      ELSE 0
                    END), 0) AS VoteCount,
        COALESCE(MAX(CASE
                      WHEN u."UserId" = auth.uid() AND u."isUpvote" = true THEN 1
                      WHEN u."UserId" = auth.uid() AND u."isUpvote" = false THEN 0
                      ELSE -1
                    END), -1) AS UserVoteStatus
    FROM
        public."dt_FeatureRequest" fr
    LEFT JOIN
        public."dt_FeatureRequestUserUpvotes" u
    ON
        u."FeatureRequestId" = fr."Id"
    WHERE
        fr."AppId" = AppId
    GROUP BY
        fr."Id", fr."CreatedAt", fr."Title", fr."Description",
        fr."isPremiumFeature", fr."Status", fr."ImplementedVersion"
    ORDER BY
        VoteCount DESC,
        fr."CreatedAt" DESC;
END;
$$ LANGUAGE plpgsql;

5. Sample data

INSERT INTO "public"."dt_FeatureRequestApp" (
    "Id",
    "CreatedAt",
    "Name"
)
VALUES (
    '1',
    '2024-11-18 17:27:36.464055+00',
    'YourAppName1'
);

INSERT INTO "public"."dt_FeatureRequestStatus" (
    "Id",
    "CreatedAt",
    "Status",
    "Name"
)
VALUES
    ('1', '2024-11-18 17:54:41.55148+00', '0', 'Submitted'),
    ('2', '2024-11-18 17:54:50.234248+00', '1', 'Planned'),
    ('3', '2024-11-18 17:54:58.366798+00', '2', 'In Development'),
    ('4', '2024-11-18 17:55:08.479074+00', '3', 'Testing'),
    ('5', '2024-11-18 17:55:17.403454+00', '4', 'Done');

INSERT INTO "public"."dt_FeatureRequest" (
    "Id",
    "CreatedAt",
    "Title",
    "Description",
    "isPremiumFeature",
    "Status",
    "ImplementedVersion",
    "AppId"
)
VALUES (
    '1',
    '2024-10-08 13:12:53.772772+00',
    'Face ID Support',
    'The user should be able to lock all or specific groups. Only viewable after successful Face ID unlock.',
    'true',
    '2',
    null,
    '1'
);

Client

    xSupabase.Initialize("https://xxx.supabase.co","xxx")
    xSupabase.InitializeEvents(Me,"Supabase")
 
    'creates a new anon user if it has not yet been created for the current device
    Wait For (xSupabase.Auth.isUserLoggedIn) Complete (isLoggedIn As Boolean)
 
    If isLoggedIn = False Then
 
        Wait For (xSupabase.Auth.LogIn_Anonymously) Complete (AnonymousUser As SupabaseUser)
        If AnonymousUser.Error.Success Then
            Log("Successfully created an anonymous user")
        Else
            Log("Error: " & AnonymousUser.Error.ErrorMessage)
        End If
 
    End If
 
    LoadOpenRequests

Private Sub LoadOpenRequests
 
    Dim CallFunction As Supabase_DatabaseRpc = xSupabase.Database.CallFunction
    CallFunction.Parameters(CreateMap("appid":1)) 'Change this to the app id in the dt_FeatureRequestApp
    CallFunction.Rpc("GetFeatureRequests".ToLowerCase)
    Wait For (CallFunction.Execute) Complete (RpcResult As SupabaseRpcResult)
 
    AS_FeatureRequest1.Clear 'Clears the list
 
    If RpcResult.Error.Success Then
        'Log(RpcResult.Data)
 
        Dim parser As JSONParser
        parser.Initialize(RpcResult.Data)
        Dim jRoot As List = parser.NextArray
        For Each Row As Map In jRoot
 
            Dim StatusText As String = ""
            Select Row.Get("status")
                Case 0
                    StatusText = "Submitted"
                Case 1
                    StatusText = "Planned"
                Case 2
                    StatusText = "In Development"
                Case 3
                    StatusText = "Testing"
                Case 4
                    StatusText = "Done"
            End Select
 
            Dim lstChips As List : lstChips.Initialize
            lstChips.Add(AS_FeatureRequest1.CreateItemChip(StatusText,xui.Color_ARGB(255,45, 136, 121),xui.Color_White))
            If Row.Get("ispremiumfeature") Then lstChips.Add(AS_FeatureRequest1.CreateItemChip("Premium",xui.Color_ARGB(255,141, 68, 173),xui.Color_White))
 
            AS_FeatureRequest1.AddItem(Row.Get("title"),Row.Get("description"),lstChips,AS_FeatureRequest1.UserVoteStatus2Text(Row.Get("uservotestatus")),Row.Get("votecount"),Row.Get("id"))
 
        Next
 
    End If
 
End Sub

Private Sub AS_FeatureRequest1_Voted(isUpvote As Boolean,Value As Object)
    Dim Insert As Supabase_DatabaseInsert = xSupabase.Database.InsertData
    Insert.From("dt_FeatureRequestUserUpvotes")
    Dim InsertMap As Map = CreateMap("FeatureRequestId":Value,"isUpvote":isUpvote,"AppId":1)'Change this to the app id in the dt_FeatureRequestApp
    Wait For (Insert.Insert(InsertMap).Execute) Complete (Result As SupabaseDatabaseResult)
    Log(Result.Error.ErrorMessage)
End Sub

 

[Alexander Stolte]

Update

• Complete data structure changed.
• Add AppId to dt_FeatureRequest
• Add AppId to dt_FeatureRequestUserUpvotes
• Add table dt_FeatureRequestApp
• Add table dt_FeatureRequestStatus

Don't forget to set the contraints, or start again from the beginning.
The changes were necessary to manage several apps with only one data structure.

Example project updated