@supalive/core

Core package for supalive — a reactive database framework.

Reactive queries for any Postgres/MySQL.

Write a typed query once. The client re-renders the moment a mutation touches a row that would change the result — no triggers, no LISTEN/NOTIFY wiring, no manual cache invalidation.

Live demo · How it works · Architecture

Installation

npm install @supalive/core

Exports

• @supalive/core - Procedure definitions + the patched Zod (z)
• @supalive/core/db/mysql - MySQL adapter
• @supalive/core/db/pg - PostgreSQL adapter
• @supalive/core/types - Type definitions
• @supalive/core/server - Server implementation
• @supalive/core/client - Client implementation
• @supalive/core/schema-sql - Generate schema.sql from your defineSchema definitions

What it is

Supalive sits between your app and your database and turns any query into a live query.

You define procedures the same way you'd define a tRPC router. Mark one as a query and any client that calls useLiveQuery on it gets a stream of updates. When a mutation commits and its writes overlap that query's read set, Supalive recomputes and pushes the new result. Everything is typed end-to-end from the database column to the React hook.

It does not replace your database. It runs on top of plain Postgres or MySQL — including Supabase Postgres, Neon, RDS, PlanetScale — without schema changes.

The 30-second version

1. Define a query and a mutation.

// procedures.ts
import { z, createQueryBuilder, createMutationBuilder, defineSchema, router } from "@supalive/core";

const query = createQueryBuilder<ServerContext>();
const mutation = createMutationBuilder<ServerContext>();

const ItemSchema = defineSchema({
  name: "items",
  schema: {
    id: z.string(),
    title: z.string(),
    status: z.enum(["todo", "in_progress", "done"]),
    ownerId: z.string(),
  },
  columns: { ownerId: "owner_id" },
});

export const listItems = query({
  args: z.object({ status: z.enum(["todo", "in_progress", "done"]).optional() }),
  handler: async (ctx, { status }) => {
    let q = ctx.db.query(ItemSchema).select()
      .where((f) => f.eq("ownerId", ctx.serverCtx.user.userId));
    if (status) q = q.where((f) => f.eq("status", status));
    return q.orderBy("createdAt", "desc").limit(50).get();
  },
});

export const createItem = mutation({
  args: z.object({ id: z.string(), title: z.string(), status: z.enum(["todo", "in_progress", "done"]) }),
  handler: async (ctx, args) => {
    await ctx.db.insert(ItemSchema, args.id, {
      ...args,
      ownerId: ctx.serverCtx.user.userId,
      createdAt: new Date().toISOString(),
      updatedAt: new Date().toISOString(),
    });
  },
});

export const appRouter = router({ procedures: { listItems, createItem } });

2. Start the server.

// server.ts
import { SupaliveWebSocketServer } from "@supalive/core/server";
import { appRouter } from "./procedures";

const { cacheLayer, redisSubClient } = initCacheLayer({
  upstash: {
    url: `https://${process.env.UPSTASH_REDIS_HOST}`,
    token: process.env.UPSTASH_REDIS_TOKEN ?? "",
    redisUrl: process.env.UPSTASH_REDIS_URL ?? ""
  },
  cacheTtlSeconds: 24 * 3600,
});
const db = await initDatabase({
  DbConfig: { type: "postgres", connectionString: process.env.DATABASE_URL || '', },
  CacheLayer: cacheLayer,
  forceBootstrapReload: false,
});

const server = new SupaliveWebSocketServer({
  port: 3000,
  cacheLayer: cacheLayer,
  redisSubClient: redisSubClient,
  database: db,
  subManagerUrl: process.env.SUB_MANAGER_URL!,
  verifyAuth: async (data) => verifyJwt(data.token),
});
server.registerRouter(appRouter);
await server.start();

3. Use it on the client.

// App.tsx
import { useLiveQuery, useMutation } from "./supalive";

function Todos() {
  const { data, status } = useLiveQuery((c) => c.listItems, { status: "todo" });
  const [createItem] = useMutation((c) => c.createItem);

  return (
    <>
      {data?.map((item) => <li key={item.id}>{item.title}</li>)}
      <button onClick={() => createItem({ id: crypto.randomUUID(), title: "new", status: "todo" })}>
        add
      </button>
    </>
  );
}

Open this page in two tabs. Click add in one. Watch the list grow in both, automatically.

Database setup

Supalive is code-first: your defineSchema calls are the source of truth for the SQL. Generate a schema.sql from them and apply it to your database before starting the server.

// schema-gen.ts
import { generateSchemaSql } from "@supalive/core/schema-sql";
import { writeFileSync } from "node:fs";

await import("./procedures.js");   // side-effect: runs your defineSchema calls

writeFileSync("schema.sql", generateSchemaSql(undefined, { dialect: "postgres" }));

The generated file contains:

• Your tables — one CREATE TABLE per defineSchema, plus an automatic index on each table's commit_ts column (<table>_commit_ts_idx) that the OCC / commit-log paths rely on.
• Framework-owned core objects — commit_logs, metadata, and the global_commit_ts commit-timestamp source (a sequence on Postgres, a BIGINT counter table on MySQL), plus their indexes — prepended automatically. Opt out with generateSchemaSql(..., { includeCoreTables: false }).

Apply schema.sql, then boot the server. initDatabase runs initCore on startup, which seeds the core runtime state — the metadata rows (and, on MySQL, the counter's initial value) — up to the current CORE_VERSION. It is:

• Versioned — the applied version lives in metadata.core_version; each core release that needs a new database operation bumps CORE_VERSION and applies only the missing steps.
• Concurrency-safe — the migration runs under a cross-process lock (Postgres pg_advisory_xact_lock, MySQL GET_LOCK) behind a lock-free fast path, so many instances can boot at once and only one applies it.

Order matters: apply the generated schema (which creates the metadata table) before the first initDatabase call — initCore reads its version from metadata.

Why this exists

Postgres LISTEN/NOTIFY is row-level — you get a notification, you still have to figure out which queries it affects. Supabase Realtime is great for broadcast, presence, and a row-level changefeed, but it doesn't answer the question every reactive app actually has: "re-run this exact query when its result would change."

The frameworks that do answer that question — Convex, Zero, Replicache, PowerSync — each ship their own database, sync engine, or client-side store. That's a lot to adopt when all you wanted was reactivity on top of the Postgres you already have.

Supalive is the smallest possible "reactive query" layer that works on top of your existing database. It's the Convex DX without the Convex lock-in.

How it works

   client                supalive server                sub-manager (1 process)              postgres
   ──────                ────────────────               ─────────────────────────             ────────
                                                        ┌── worker 0 ──┐
                                                        │ subs slice 0 │
                                                        ├── worker 1 ──┤  ← FNV-1a(subId) % N
                                                        │ subs slice 1 │     routes ops here
                                                        ├──   …    ────┤
                                                        │ worker N-1   │
                                                        └──────────────┘

   subscribe ────────▶  register subId  ───────────▶   slot in worker; if metadata
                                                       is still valid vs commit log,
                                                       skip recompute
                        liveQuery ────────────────────────────────────────────▶  SELECT
                        cache { data, metadata }
   sub:update ◀────────  push result

   call(mutation) ────▶ run handler (DbWriter)
                        capture readSet + writeSet (+ prevData)
                        OCC commit:
                          beginTs = latest snapshot
                          getNextTimestamp() ────────────────────────────────▶  commit_ts
                          CAS apply + readSet validate ─────────────────────▶  BEGIN…COMMIT
                          retries on conflict (cockatiel, max 15)
                        invalidateWriteset ────────▶  every worker scans its
                                                      slice; per-sub same-table
                                                      filter + exact predicate
                                                      eval on prev+post row
                                                    ◀── affected subs
                        recompute locally (if subs are here)
                          OR publish recompute-race
                             on sub:instance:<id>     ─┐
                             SETNX lock:recompute:    │  (cross-instance fanout
                             <subId>:<commitTs>       │   via Redis pub/sub)
                          winner runs query, losers   │
                          wait for update broadcast  ─┘
   sub:update ◀───────── { data, dataHash }

Four things make this fast and correct:

• ReadSet + WriteSet capture. Every query records exactly which point reads and range predicates (with column lists, order, and limit) it touched. Every mutation records the rows it wrote plus their previous values — both sides are needed so the invalidator can detect rows entering, leaving, or changing within a result set.
• Sharded scan, not row-by-row recheck. A single sub-manager process owns all subscription state, partitioned across N logical workers by FNV-1a(subId) % N (configurable via SUPALIVE_SUB_MANAGER_WORKERS). On commit, each worker scans only its slice; per-sub the check short-circuits on table-id mismatch, then runs an exact 3-valued predicate evaluation on the row's pre- and post-images. New subscribers also benefit: if the worker still holds metadata for a cacheKey, it replays commit_logs between the cached snapshotTs and the caller's snapshotTs to decide whether a fresh liveQuery is needed at all.
• OCC with retries. Mutations execute against a snapshot, allocate commitTs from a DB sequence at apply time, and commit via compare-and-swap with a readSet validator (per-row fast path; commit-log scan when range reads are present). Conflicts retry with exponential backoff (cockatiel, up to 15 attempts). The Σ-balance invariant in the money-transfer demo holds under burst contention because of this.
• Cross-instance fanout with a deterministic recompute winner. Multiple Supalive servers behind a load balancer share one sub-manager and one Redis. When a write on instance A affects a subscription whose session lives on instance B, A publishes a recompute-race on sub:instance:<B>; B and any other holders try to SETNX lock:recompute:<subId>:<commitTs> — the winner re-runs the query and broadcasts sub:update on sub:instance:<…>; losers no-op. A periodic prune trims commit_logs below the oldest active subscription's snapshot.

Full architecture write-up — including the cache layout, schema-reload broadcast, and sub-manager recovery protocol — lives in examples/basic/README.md.

Try the demo locally

The examples/basic workspace ships a 5-tab React demo that shows everything Supalive can do. Each tab is a different stress test of the system.

| Tab | What it shows | |---|---| | Todos · list | Live query + create. Toggle the subscription to prove updates only flow when subscribed. | | Todos · cards | Filter + inline edit. Changing status moves a row in or out of the visible set — predicate index handles it. | | Mouse tracking | High-frequency writes (cursor positions @ ~60ms). Stress test for fanout. | | Money transfer | OCC under contention. Σ-balance on screen must never drift, no matter how hard you spam transfers. | | Parallel lanes | Disjoint concurrent transfers. Non-overlapping writers don't block each other. |

git clone https://github.com/rebaz94/supalive
cd supalive
npm install

cp examples/basic/.env.example examples/basic/.env
# fill in DATABASE_URL, REDIS_URL, UPSTASH_REDIS_TOKEN, JWT_SECRET

# terminal 1 — sub-manager
cd examples/basic && npm run dev:submanager

# terminal 2 — websocket server
cd examples/basic && npm run dev

# terminal 3 — web demo
cd examples/basic && npm run dev:demo

Open the demo URL in two tabs and click around. The whole point is that nothing in your app code wires the updates — Supalive does.

API surface

Server

import { SupaliveWebSocketServer, SubscriptionManager } from "@supalive/core/server";
import {
  createQueryBuilder, createMutationBuilder, createActionBuilder,
  defineSchema, router,
} from "@supalive/core";

• query — read-only, subscribable. ReadSet captured automatically.
• mutation — read + write, transactional, OCC-committed.
• action — arbitrary async work (HTTP, queue calls). Can compose queries and mutations via ctx.db.

Client (React)

import {
  createSupaliveContext, createSupaliveProvider, createUseSupalive,
  createUseConnectionState, createLiveQuery, createQuery,
  createMutation, createAction,
} from "@supalive/react";

• useLiveQuery(c => c.listItems, args) — subscribe; re-renders on push.
• useQuery(c => c.getItem, args) — one-shot.
• useMutation(c => c.createItem) — returns [mutate, { status }].
• useAction(c => c.bulkUpdate) — returns [run, { status }].
• useSupaliveConnectionState() — connecting | connected | reconnecting | closed.

The hooks are factory-built so you get fully-typed autocomplete on c.<procedure> from your own router type. See examples/basic/src/web/supalive.tsx for the wiring pattern.

Where it sits

| | Supalive | Supabase Realtime | ElectricSQL | PowerSync | Zero | Replicache | Convex | |---|:-:|:-:|:-:|:-:|:-:|:-:|:-:| | Live queries (re-runs on relevant writes) | ✓ | — | ✓ | ✓ | ✓ | ✓ | ✓ | | Works on your own Postgres / MySQL | ✓ | ✓ | ✓ | ✓ | — | — | — | | Server-authoritative auth + validation | ✓ | ✓ | partial | partial | ✓ | partial | ✓ | | OCC mutations w/ automatic retry | ✓ | — | — | — | partial | — | ✓ | | Typed end-to-end (TS, no codegen) | ✓ | — | — | — | ✓ | ✓ | ✓ | | No client-side SQLite required | ✓ | ✓ | — | — | ✓ | ✓ | ✓ | | Horizontally scalable out of the box | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | MIT, self-hostable | ✓ | ✓ | ✓ | partial | ✓ | — | — |

(Categories vary in nuance; this is the executive-summary view. PRs welcome to refine.)

Production

The repo already includes Fly configs for a three-process deployment:

• examples/basic/config/fly.server.toml — SupaliveWebSocketServer (handles client websockets)
• examples/basic/config/fly.submanager.toml — SubscriptionManager (predicate index, overlap checks)
• examples/basic/config/fly.web.toml — the demo frontend

Multiple SupaliveWebSocketServer instances behind a load balancer share state via:

• The sub-manager (single source of truth for active subscriptions)
• Redis pub/sub (cross-instance recompute + cache-invalidation messages)
• An Upstash Redis cache layer for query results

Environment variables

| Variable | Required | Description | |---|---|---| | DATABASE_URL | yes | Postgres or MySQL connection string | | REDIS_URL / UPSTASH_REDIS_URL | yes | Cache + pub/sub | | UPSTASH_REDIS_TOKEN | yes | If using Upstash HTTP transport | | SUB_MANAGER_URL | yes | ws://…:3003 — where the sub-manager listens | | JWT_SECRET | yes (example) | The example verifies JWTs with jose | | SUPALIVE_DB_QUERY_TIMEOUT_MS | no | Per-statement timeout, default 30s. 0 to disable | | SUPALIVE_HEARTBEAT_INTERVAL_MS | no | WebSocket heartbeat, default 15s | | SUPALIVE_RECOMPUTE_LOCK_TTL_SECONDS | no | Redis recompute lock TTL, default 10s | | SUPALIVE_SUB_MANAGER_WORKERS | no | Logical workers inside the sub-manager process, default 1. Subscriptions are hash-routed across them | | SUPALIVE_SCHEMA_VERSION | no | Cache-busting key for column-type bootstrap |

Status

Alpha. APIs may change. The implementation is real — there's a working test suite (packages/test, packages/core/src/server/*.test.ts), the OCC and overlap-detection layers carry production-shaped concerns (statement timeouts, prepared statement cache, schema-reload broadcast, sub-manager recovery), and the demo is end-to-end. But: no semver guarantees yet, no published npm packages, no formal docs site.

If you're evaluating Supalive for production, open an issue first — I'd love to know what you're building.

License

MIT © Rebaz Raouf