SupabaseDB : With Authentication and Database

[White_Tiger]

Latest version

com.xtiger.supabasedb.aix (38.9 KB)

[Mark_M_Scicluna]

Would it be possible to send a screenshot with blocks using the updated extension, just as you provided a screenshot for the first version?

Thanks in advance

[otniel]

thanks for updtae

[OregonM]

Good afternoon. Are there any examples of work RealTime Supabase?

[White_Tiger]

Here is another extension for supabase series ,

Supabase Notification : Send foreground and background notifications even the app is closed.

Mainly designed for chatting apps , so

Things to consider before using this extension .
There shoud be tables like ,
"messages" table with coloums (from_email,to_email,message,...) must have
"profiles" table with (email,name,profilepic,....) optional
Realtime must be on in messages table

How to use :-

useremail is current user email , which listened from_email and to_email coloums as mentioned above.

com.xtiger.supabasenotification.aix (60.0 KB)

Demo Apk if anyone wants to try with google sign in two device needed : Download RedisDB.apk | LimeWire

[Jonathan_Urbina1]

Just wow! and EXCELLENT!!!!

---

I have some stranding-type projects running on Supabase, so I can grab you the next ones.

I just want to help you, to get future updates:

• RPC (Remote Procedure Call).
  Need: ULTRA

---

Why RPCs instead QUERIS for CRUD???

Security related issues:

• No good obfuscation. (API keys & url as raw) (on AppInventor & APK can be decompiled).
• Newbies: Don’t really get how RLS works. (User Auth & Roles & Schemes & Tables & Views).
• Newbies: Don’t really get how START works. (HOT & COLD).
• Supabase has a 'limited' free plan for Users Connected at Month: 50k MAU.
• SQL Injection & Sanitization of data pre CRUD on DB; Messages can be intercepted, modified, and sended to DB (MiM Atack), and then... Just CRUD???.
• It is not good to expose tables to direct CRUD operations; it is better to have well-defined logic for each option (GET, INSERT, MODIFY, DELETE, ETC.).

Because

• When you create a QUERI from the client code (App), you are actually sending a string (sentence) in SQL or PL/PGSQL code, like; 'SELECT id, name FROM users WHERE active = true;'
• The client sends that string, the server parses it, validates (just the QUERI & not DATA) it, generates an execution plan, and only then runs it.
• You EXPOSE ID, TABLE_NAME, other PARAMETERS, SCHEMAS, etc. This facilitate RECON attacks by fingerprinting your DB
• Difficult to apply business rules or access restrictions if everything is done from the client side.
• Even with HTTPS, the payload is still a readable SQL || PL/PGSQL.
• Each QUERI sent as a string must be parsed and optimized by the engine on each execution. This consumes CPU and memory on the server.
• Raw QUERI do not reuse precompiled plans.
• QUERIs are usually longer, which affects mobile apps with unstable connections.
• When many clients send different QUERIs, the server cannot optimize globally. It becomes a bottleneck in high-concurrency scenarios.
• The client must know how to assemble correct queries. This increases the likelihood of errors.
• So... If QUERI needs to be modified for X reason:
  Instead of .jpg now it's .png || instead of ID now it's by GUID...

You must compile the app again, and if it's on the Play Store... Go through the update process.

• These are some of the most common and important problems of using QUERIs from the client. (App)

RPC to VIEWS vs QUERI on TABLES in Code/App/Client

• RPC: The client does not build SQL || PL/PGSQL; it only calls functions with parameters.
• RPC: The logic is encapsulated → tables or internal structures are not exposed.
• RPC: RLS and roles can be applied directly to the function.
• RPC: Smaller attack surface: the client cannot execute arbitrary queries.
• RPC: Function execution plans can be cached. (START HOT vs COLD issue)
• RPC: Less traffic: only the function name + parameters are sent.
• RPC: Scalability: all clients call the same function → the DBA can optimize it (indexs).
• RPC: Logic is centralized → changes are made in the Supabase, not in client.
• VIEWS: They allow hiding sensitive columns and exposing only what is necessary.
• VIEWS: They can be combined with RLS to automatically filter rows.
• VIEWS: They act as an abstraction layer: the client never touches and even KNOW the tables || schemes, etc.
• VIEWS: They simplify complex queries → the engine can optimize better.
• VIEWS: Materialized Views allow caching heavy results (e.g., reports).
• VIEWS: They reduce the load on the client → it only queries the view as if it were a table.

So:

• Raw queries: if the logic changes (e.g., from ID to GUID), the app needs to be recompiled and redistributed.
• RPC: it is enough to modify the function in the database; clients continue calling it the same way.
• VIEWS: allow versioning of data exposure without breaking compatibility with client code.

How can be implemented???

Secure RPC over Views in Supabase + AppInventor

Instead of sending raw SQL queries from the client (which exposes table names, schema, and business logic), you can encapsulate everything inside a PostgreSQL function (RPC) that operates on a pre‑built, cached VIEW. This gives you three layers of protection and optimization:

VIEW:

• The VIEW hides the underlying tables and columns.
• It can be materialized (cached) to avoid “cold start” penalties and improve performance.
• It ensures the client never touches raw tables directly.

RPC (Function)

• The RPC receives sanitized parameters (dates, IDs, filters, etc.).
• It executes logic only against the VIEW, not the raw tables.
• Execution plans can be cached by PostgreSQL, so repeated calls are fast (“hot start”).

Obfuscation / Darkness Layer

• The RPC name can be something meaningless like RTGTEG.
• Parameters can be named xyz, hgf, jkl, iuy.
• To anyone intercepting the request, it looks like gibberish. Only the server knows the mapping.
• The actual logic (filtering, sanitization, aggregation) lives inside the function, invisible to the client.

RPC for GET

Step 1: Create a cached VIEW

sql

CREATE MATERIALIZED VIEW sales_summary AS
SELECT DATE_TRUNC('month', sale_date) AS month,
       SUM(amount) AS total
FROM sales
GROUP BY 1;

Step 2: Create the RPC that operates on the VIEW

sql

CREATE OR REPLACE FUNCTION RTGTEG(
    xyz DATE,   -- start date
    hgf DATE,   -- end date
    jkl TEXT,   -- optional filter (e.g., region)
    iuy INT     -- optional limit
) RETURNS TABLE (month DATE, total NUMERIC) AS $
BEGIN
  RETURN QUERY
  SELECT month, total
  FROM sales_summary
  WHERE month BETWEEN xyz AND hgf
    AND (jkl IS NULL OR region = jkl)
  ORDER BY month
  LIMIT COALESCE(iuy, 100);
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

Step 3: Call from AppInventor

• The client only calls rpc("RTGTEG", { xyz: "...", hgf: "...", jkl: "...", iuy: ... }).
• No table names, no SQL, no schema details are exposed.
• Even if intercepted, the payload is meaningless without knowing the server logic.
• You can even Encrypt data before load into the payload. (for even more meaningless).

Benefits

• Security: No raw queries, no schema exposure, sanitized parameters.
• Performance: Cached VIEW + cached execution plan = fast hot starts.
• Maintainability: If business logic changes, only the function/view is updated. Clients keep calling RTGTEG.
• Obfuscation: RPC and parameter names are opaque, adding another layer of defense.

This pattern is especially powerful in environments like AppInventor, where the client code can be easily decompiled. By hiding all logic behind an RPC that only touches a VIEW, you ensure that the app is lightweight, secure, and future & feature‑proof.

RPC for INSERT

Let’s build the INSERT example following the same principles:

• The client (AppInventor) never sends raw SQL.
• The RPC operates only on a VIEW (or better, a controlled staging table/view).
• The RPC name and parameters are obfuscated (e.g., QWERTY with params abc, def, ghi).
• The function itself sanitizes inputs and applies business rules before inserting into the real table.

Step 1: Create a staging VIEW (optional, for abstraction)

sql

CREATE VIEW safe_orders AS
SELECT id, customer_id, product_id, quantity, created_at
FROM orders;

This view exposes only the columns you want to allow inserts into, hiding internal fields.

Step 2: Create the RPC (obfuscated name + params)

sql

CREATE OR REPLACE FUNCTION QWERTY(
    abc INT,     -- customer_id
    def INT,     -- product_id
    ghi INT      -- quantity
) RETURNS VOID AS $
BEGIN
  -- Sanitize inputs
  IF abc IS NULL OR def IS NULL OR ghi <= 0 THEN
    RAISE EXCEPTION 'Invalid input parameters';
  END IF;

  -- Insert into the real table (not exposed to client)
  INSERT INTO orders (customer_id, product_id, quantity, created_at)
  VALUES (abc, def, ghi, NOW());
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

Notes:

• SECURITY DEFINER ensures the function runs with the privileges of its owner, not the caller.
• Input validation prevents bad or malicious data.
• The client never sees the real table name (orders).

Step 3: Call from AppInventor

The client only calls:

js

supabase.rpc("QWERTY", { abc: 42, def: 7, ghi: 3 })

• No SQL, no table names, no schema details.
• Even if intercepted, the payload looks meaningless (abc=42, def=7, ghi=3).
• The server handles the actual insert logic.

Benefits Recap

• Security: No raw INSERTs from the client, no schema exposure, sanitized inputs.
• Performance: Execution plan cached, minimal payload.
• Maintainability: If the schema changes (e.g., new audit columns), only the function is updated.
• Obfuscation: RPC and parameter names are opaque, adding a “darkness” layer.

This way, your AppInventor app can safely perform INSERT operations into Supabase without ever exposing the underlying database structure.

Resume:

RPC vs Raw Queries (Code Perspective)

Raw Queries:

sql

-- Client sends this as a string
'SELECT id, name FROM users WHERE active = true;'

• Exposure: Table names, schema, and logic are visible in the client code.
• Security risk: Vulnerable to SQL injection if not sanitized.
• Performance hit: Every query string must be parsed and planned on each execution.
• Maintainability issue: If schema changes, every client app must be updated and redeployed.

RPC (Remote Procedure Call)

sql

-- Define once in PostgreSQL
CREATE OR REPLACE FUNCTION get_active_users()
RETURNS TABLE (id INT, name TEXT) AS $
BEGIN
  RETURN QUERY SELECT id, name FROM users WHERE active = true;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

-- Client only calls
supabase.rpc("get_active_users");

• Encapsulation: Logic lives in the database, not in the client.
• Security: No schema exposure, parameters are sanitized, RLS and roles apply directly.
• Performance: Execution plans can be cached → faster hot starts.
• Maintainability: Update the function once, all clients benefit immediately.

VIEWS (Complementary Layer)

sql

CREATE VIEW active_users AS
SELECT id, name FROM users WHERE active = true;

• Abstraction: Clients query a simplified surface, not raw tables.
• Security: Hide sensitive columns, combine with RLS.
• Performance: Materialized views cache heavy computations.

Why This Matters

• Raw queries are quick to prototype but fragile, insecure, and inefficient at scale.
• RPCs give you a hardened, centralized, and optimized entry point for all CRUD operations.
• VIEWS let you expose only what’s necessary, simplify client code, and leverage PostgreSQL’s optimizer.

Next Steps

If you want to build better, more secure, and high‑performance apps with AppInventor, Supabase, or any client platform:

• Dive into PostgreSQL functions, triggers, and views.

• Learn how Row Level Security (RLS) works in Supabase.

• Explore materialized views for caching and reporting.

• Practice designing RPCs that sanitize inputs and encapsulate business logic.

• The more you master PostgreSQL’s advanced features, the more your apps will stand out: lighter clients, stronger security, and better performance.

---

TIPS & TRICKS:

• Many times there is data that is of public interest, so it is not necessary to be logged in to view it (saving a lot of the 50k MAU).
• Supabase supports 'anonymous' navigation (ex: shopping carts) and you only need to log in to place the order (you save the cart in TinyDB > login > authenticated business logic > purchase).
• Only when you need to perform 'important' operations (INSERT, MODIFY, DELETE) is it good to log in the user.
• The session does not necessarily have to be active 24/7; you can use a single-use JWT for quick sessions.
• You don't necessarily have to/can use RealTime DB (it has a limit); with ITO you can (via RPC) check certain tables/views from time to time to see if there are any updates.
  -For example: You can send in the RPC parameter a value x=123 (as the last seen update; if the table now has 234 records, then you send the alert to the user; if they want it, 'they get it', otherwise 'they don't get it').

---

• Thnx! learn something new
• Looks great!
• I don't know... It's seems to much.
• I didn't understand anything at all!

[Krx_asy]

hello! how can i get all columns instead of making a list of all the columns?

[Krx_asy]

Why do i get thus "Length=2;Index=2" runtime error?

[ABG]

You requested index 4 of blank.

[Jonathan_Urbina1]

As ABG says:

@ABG

You requested index 4 of blank.

Also: your code it's hard to read.

[Krx_asy]

how can i check if a column has a text in it(case insensitive)? i tried using

but it just returns every data, also passing in % as a wildcard gives a cloudfare error

[Krx_asy]

or if possible can i get the data but in lowercase

[White_Tiger]

You can use ilike in filter ,
for example
name=eq.appinventor to name=ilike.appinventor

or if you want to seach MIT Appinventor
name=ilike.* appinventor * (without space) works well

[Krx_asy]

i see, thank you!

[Krx_asy]

btw is there a column data changed event or something similar to that? if not what way could i replicate something lik that?

[Krx_asy]

oh i seem to found SupabaseRT, but is there any documentation on how to use it?

[Unnecessary]

In firebase we have data change event. Will this event possible in supabase?

[White_Tiger]

Yes that can be done if you give some time to companion and SupabaseRT , currently i cant make sample .aia later when i get time then i will post.