Database Sync

Database Sync turns a SQL query into a live document. You register a named, parameterized SELECT against your own Postgres, and every (query, parameters) pair becomes a doc on a db: channel. Clients subscribe to the channel and receive the current result immediately, then a fresh copy every time the underlying rows change. No row events to merge, no client-side sync engine: the server-defined query is the document your UI renders.

Your database credentials never leave your infrastructure. A small open-source agent you run next to the database (foony-sync) watches for changes with Postgres change data capture over logical replication, re-runs affected queries locally, and publishes only the results.

Quickstart

1. Connect a database. In the dashboard, open your app’s Database Sync tab and add a database. You get an agent key.

2. Write your live queries in a foony-sync.json next to the agent. Definitions are config in your repo: version them, review them, promote them through environments. They never leave your infrastructure. The dashboard only shows their names.

{
  "queries": [{
    "name": "orders",
    "sql": "SELECT json_agg(o) FROM orders o WHERE o.tenant_id = $1",
    "watches": [
      { "table": "orders", "columns": ["tenant_id"] }
    ]
  }]
}

The SQL’s $1..$n placeholders are the query’s parameters. Their values become the doc’s channel name segments in placeholder order, so this query’s doc for tenant 42 lives on db:orders:42. Values bind as text and Postgres casts them from the query’s context. Write an explicit cast like $1::bigint when it cannot.

A doc query returns exactly one column and at most one row, because the single value it returns is the doc. Wrap rows in json_agg (a list, as above), to_jsonb (a single row), or json_build_object (a custom shape). See Advanced queries for shaping beyond the basics.

3. Run the agent next to your Postgres:

docker run -d --name foony-sync \
  -e FOONY_SYNC_KEY="myapp.kid_abc:sk_..." \
  -e DATABASE_URL="postgres://foony_sync:...@localhost:5432/mydb" \
  -v $(pwd)/foony-sync.json:/foony-sync.json \
  -e FOONY_SYNC_CONFIG=/foony-sync.json \
  ghcr.io/foony-limited/foony-sync

The agent needs a role with REPLICATION and SELECT on the tables your queries read, and wal_level = logical on the server:

CREATE ROLE foony_sync REPLICATION LOGIN PASSWORD '...';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO foony_sync;

The blanket grant keeps getting started easy. For production, restrict it to only the tables your queries read and watch (GRANT SELECT ON orders TO foony_sync;) so the agent’s credential can’t read anything else.

The agent also manages a publication named after its key (foony_sync_<hash>, printed at startup). Creating or altering a publication needs CREATE on the database plus ownership of the watched tables, which the role above deliberately lacks. Either grant those, or create the publication yourself as a superuser. When the agent can’t manage it, the source card on the dashboard shows the exact statement to run, and the agent uses a pre-created publication as long as its table list matches the config.

4. Subscribe from a client. The doc for tenant 42 lives on db:orders:42:

const channel = client.channel('db:orders:42');
channel.subscribe((message) => {
  renderOrders(message.data); // the full, current query result
});

A fresh subscriber gets the current doc on attach (or triggers its first computation), then every recompute as a normal message. A doc that queries zero rows is null.

How a doc stays fresh

The agent watches your tables through a replication slot. When a row changes, the watches you declared map that row back to the affected docs:

• columns is the direct case: the changed row itself carries the parameter values. It lists the row’s columns in the query’s parameter order (element i feeds $i+1), so ["tenant_id"] says an orders row’s tenant_id names the doc. Both the old and new row are mapped, so an UPDATE that moves a row between tenants refreshes both docs.
• keysSql is the reverse index for join queries, where the changed row does not carry the doc key. It returns one column per query parameter, in parameter order, one row per affected doc. Example: a friends-list query joins user_friends with users for usernames. When a user renames themselves, the changed users row only has that user’s id, and keysSql answers “whose friend lists contain them?”:

{
  "table": "users",
  "keysSql": "SELECT user_id FROM user_friends WHERE friend_user_id = $1",
  "keysColumns": { "$1": "id" },
  "maxKeys": 1000
}

maxKeys caps the fan-out of one change. When a change would affect more docs than that, the agent recomputes every currently watched doc of the query instead, so nothing goes stale. Prefer attaching a keysSql watch to the table that changes rarest: watch a username_changes history table rather than users when only renames matter.

Only docs someone is actually subscribed to are recomputed on change. The agent learns who is watching from foony: a signal fires the moment a doc gets its first subscriber, and a poll every few minutes keeps the full watched set in sync. When the last subscriber leaves, recomputes for that doc stop within minutes, so an abandoned doc costs your database nothing. A doc that is watched but rarely changes stays fresh indefinitely.

Every query the agent runs on your database is pinned read-only with a 5 second statement timeout, on a pool of 2 connections. If the agent falls far enough behind that the replication slot retains more than 4 GB of WAL, it drops its own slot rather than fill your disk, and the source shows as detached in the dashboard until you restart the agent.

Advanced queries

The doc is whatever one JSON value your SQL builds, so ordering, filtering, and shaping all happen in the query. A production version of the quickstart’s doc might sort newest first, keep only open orders, and return [] instead of null when the tenant has none:

SELECT coalesce(json_agg(o.* ORDER BY o.created_at DESC), '[]')
FROM orders o
WHERE o.tenant_id = $1 AND o.status = 'open'

json_build_object composes several results into one doc, so the UI gets a single message instead of stitching channels:

SELECT json_build_object(
  'open',  (SELECT count(*) FROM orders WHERE tenant_id = $1 AND status = 'open'),
  'items', (SELECT coalesce(json_agg(o ORDER BY o.created_at DESC), '[]')
            FROM (SELECT * FROM orders WHERE tenant_id = $1 LIMIT 50) o)
)

Join queries work too. Pair them with a keysSql watch (below) so changes to the joined table refresh the right docs.

Docs keyed on non-primary-key columns need REPLICA IDENTITY FULL

Postgres decides which columns of the old row go into the replication stream using the table’s replica identity, and by default that is the primary key only. That is fine when your doc keys on the primary key. When it keys on another column (the quickstart’s orders doc keys on tenant_id), a DELETE’s old row does not carry that column, so the agent cannot tell which doc lost the row and the doc keeps showing it. An UPDATE that changes the key column has the same problem: the doc the row left is never refreshed.

Tell Postgres to include the whole old row for those tables:

ALTER TABLE orders REPLICA IDENTITY FULL;

The agent checks this at startup and logs a warning naming any table whose watched columns are not covered by its replica identity.

Auth: who sees which docs

Doc access uses the same capabilities as every other channel. Your backend mints tokens scoped to exactly the docs a user may read:

const token = await fetch('https://realtime.foony.io/keys/' + keyName + '/requestToken', {
  method: 'POST',
  headers: authHeaders,
  body: JSON.stringify({
    clientId: user.id,
    capability: { ['db:orders:' + user.tenantId]: ['subscribe'] },
  }),
});

Prefix grants work too (db:notifications:*). Client tokens can never publish on db: channels, no matter what capability they hold, so a carelessly broad token cannot corrupt a doc. Your own backend can: anything authenticated with an API key may write a doc directly, which is handy for pushing a fresh result the moment you commit a write instead of waiting for replication (the agent’s next recompute reconciles either way).

Best practice: if a service only needs to push or invalidate docs, give it its own key scoped to just that instead of your admin key:

{ "db:*": ["publish"] }

Narrow it further per query ({"db:orders:*": ["publish"]}) if the service only owns one doc family. The agent’s own credential is created for you when you add a database and needs no changes.

Reading a doc over HTTP

For server-side rendering or jobs that just want a snapshot:

GET /channels/db:orders:42/doc

Returns { "channel", "doc", "ts" }, or 404 with Retry-After: 1 when the doc is cold (the read itself asks the agent to compute it, so a retry finds it).

Forcing a recompute

Replication cannot see everything (time-based queries, changes in tables you chose not to watch). Your backend can force a recompute with an API key:

POST /sync/invalidate
{ "channel": "db:orders:42" }

Limits

Doc publishes are normal messages: they meter and bill like everything else, and show up under the db: bucket in the usage breakdown. Watched tables live in the public schema in this release.