How to persist data into a table using only WSS ?

[baldhead69]

Hi everyone,

I am building an application where I want to handle data persistence exclusively through Secure WebSockets (WSS).Instead of using traditional HTTP REST endpoints (like POST or PUT) to save data, the client will send a JSON payload or bin payload over an established WSS connection, and the backend should insert this data directly into a database table.

My questions are:
What is the best architecture or pattern to handle database writing operations through a continuous WSS message stream ?

I just need a response from the server:
either "inserted in the table OK" or "not inserted" with the error reason.

Thank's

[barry166]

Supabase Realtime is not the write path I would use for this. Treat the WebSocket as your app transport to trusted server code, then write to Postgres from there: client -> WSS Edge Function/custom server -> authenticate + validate payload -> insert/RPC -> socket.send({ ok, id or error }).

Keep the service-role key only in that server code. If clients are allowed to write directly, the normal Supabase path is PostgREST/RPC with RLS; Realtime is better used after the insert for fan-out/subscriptions, not as the mechanism that inserts rows into your table.

[baldhead69]

But i want to save logs and possibly coredumps from my esp32s3 project, so i can test then in field.

Using https for insert each log gets very heavy.
Open and close https connection for every single log doesn't seem like the right way to me.

Core dumps are sporadic if they happen at all, but syslogs are not.

Note:
Preferably using Supabase Cloud if there is some way for this.

[yakubka]

one clarification on the architecture: if you control the server-side WebSocket handler, the simplest path is to validate and write inside the message handler using the service role client, not the anon key.

supabase Edge Functions support WebSocket connections directly:

// supabase/functions/ws-ingest/index.ts
import { createClient } from "@supabase/supabase-js";

const sb = createClient(
  Deno.env.get("SUPABASE_URL"),
  Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")
);

Deno.serve((req) => {
  if (req.headers.get("upgrade") !== "websocket") {
    return new Response("expected websocket", { status: 400 });
  }

  const { socket, response } = Deno.upgradeWebSocket(req);

  socket.onmessage = async (event) => {
    let payload;
    try {
      payload = JSON.parse(event.data);
    } catch {
      socket.send(JSON.stringify({ ok: false, error: "invalid json" }));
      return;
    }

    const { error } = await sb.from("your_table").insert(payload);
    if (error) {
      socket.send(JSON.stringify({ ok: false, error: error.message }));
    } else {
      socket.send(JSON.stringify({ ok: true }));
    }
  };

  return response;
});

the client connects to wss://your-project.supabase.co/functions/v1/ws-ingest. you do authentication in the onmessage handler before the insert, either by parsing a token from the first message or from the request headers on upgrade.

[baldhead69]

Hi @yakubka,

Now that I've almost finished everything and it's working, i discovered that Edge Functions don't keep WebSocket connections open for more than 150 seconds on the free plan and 400 seconds on the paid plan.
Which makes absolutely no sense for a WebSocket connection.
Is that true ?

Does an HTTPS POST request with the Connection: keep-alive header keep the connection open indefinitely ?

[GaryAustin1]

Edge function are limited to 150s in Free and 400s in Pro for background tasks. No way to extend that the hosted platform.

[GaryAustin1]

https://supabase.com/docs/guides/functions/limits

[baldhead69]

@GaryAustin1 ,

That’s very strange, because Supabase Realtime is supposed to allow persistent connections, and the server processing for this, is supposed to be the same.

Furthermore, if the connection drops, I will reconnect immediately afterward, which will consume more server resources than simply keeping the connection open.

[GaryAustin1]

Realtime uses websockets and does maintain the connection. But Edge Functions cannot stay connected after the worker's time expires.

[baldhead69]

Realtime uses websockets and does maintain the connection. But Edge Functions cannot stay connected after the worker's time expires.

I know, but that seems counterproductive to me, and regarding server resource consumption, using the built-in Realtime functions versus my Edge Function shouldn't make much of a difference, i think.

And I implemented a WebSocket server in the edge function, and then I perform a database insertion.

[hosanxiv]

The hosted Edge Function limits do apply to a WebSocket handled by that function. Supabase Realtime and Edge Functions are separate runtimes: Realtime is a persistent Elixir/Phoenix WebSocket cluster, while an Edge Function worker has a maximum wall-clock lifetime of 150 seconds on Free and 400 seconds on paid plans.

Connection: keep-alive does not make an HTTP request stay open indefinitely. It only permits reuse of the underlying connection for later requests, and the client, proxy, or server may close that connection. It can reduce TCP/TLS setup cost, but it is not a replacement for a persistent application stream.

For ESP32 telemetry on hosted Supabase, I would use:

1. Buffer multiple log records on the device and send one HTTPS request per batch, with a device ID, sequence number, and an idempotency key.
2. Insert the batch through PostgREST or a short-lived Edge Function and acknowledge the highest stored sequence number.
3. Retry unacknowledged batches after reconnecting.
4. Upload coredumps to Supabase Storage instead of a database row. Storage's TUS endpoint supports chunked, resumable uploads for unreliable networks.

Realtime Broadcast can carry client messages over a persistent WebSocket, but it is a messaging service and does not turn arbitrary client messages into durable rows in your application table. Its persistent connections also do not run inside your Edge Function.

If a permanently connected custom WSS ingestion protocol is a hard requirement, the WebSocket server needs to run outside hosted Edge Functions and write to Supabase from trusted server-side code.

References: Edge Function limits, Realtime architecture, and Storage resumable uploads.