foony-sync

The Foony Database Sync agent. It runs next to your Postgres, holds your database credentials and live-query definitions locally (neither ever reaches Foony), watches row changes over logical replication, re-runs the affected queries, and publishes only the results as live documents on db: channels.

Clients subscribe to those channels with any Foony Realtime SDK and get the current result immediately, then a fresh copy every time the underlying rows change.

Run it

Get an agent key from your app's Database Sync tab in the dashboard, write your queries in a foony-sync.json, then:

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, it reports the exact statement to run in its logs and on the source's dashboard card, and it uses a pre-created publication as long as the table list matches its config.

See the Database Sync docs for the full walkthrough: query definitions, watch rules, doc channels, and client subscriptions.

Configuration

Environment:

• FOONY_SYNC_KEY (required): the source credential from the dashboard (appSlug.keyId:secret).
• DATABASE_URL (required): the Postgres DSN. The agent adds replication=database itself for the WAL connection.
• FOONY_SYNC_CONFIG: path to the definitions file. Defaults to ./foony-sync.json.
• FOONY_URL: data-plane override. Defaults to https://realtime.foony.io.

The definitions file holds the live queries plus two protect-my-database knobs:

{
  "queries": [{
    "name": "orders",
    "sql": "SELECT coalesce(json_agg(o.* ORDER BY o.created_at DESC), '[]') FROM orders o WHERE o.tenant_id = $1 AND o.status = 'open'",
    "watches": [
      { "table": "orders", "columns": ["tenant_id"] }
    ]
  }],
  "statementTimeoutMs": 5000,
  "walRetentionCapBytes": 4294967296
}

The SQL's $1..$n placeholders are the query's params. Their values become the doc's channel 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 watch's columns array names the changed row's columns that carry those values, in the same order (element i feeds $i+1). A keysSql watch returns one column per param, also in that order.

• statementTimeoutMs pins every query the agent runs (default 5000).
• walRetentionCapBytes is the safety valve: past this much retained WAL the agent drops its replication slot rather than risk filling the database's disk, then attaches a fresh one and recomputes every live doc (default 4 GiB, -1 disables). Docs are computed from live SQL, so the dropped WAL costs freshness for the length of one recycle and never costs data. After 3 recycles in an hour the agent stops re-attaching and reports the source detached, because a source that keeps falling behind needs a fix, not another recompute. Postgres will not drop a slot whose replication connection is still open, and that connection is only reaped after the server's wal_sender_timeout (60s by default, never when set to 0), so the agent terminates its own replication backend instead of waiting. A slot it still cannot drop is re-attached and drained rather than left behind.

Definitions never leave the machine. The dashboard only ever sees name-and-table summaries from heartbeats.

What it talks to

Outbound only, to FOONY_URL:

• Doc publishes and the dbsync:warm subscription, through the realtime-go SDK.
• Heartbeats and live-doc polls over REST.

The only inbound traffic it acts on is a warm request naming a db: channel to compute. Your database is reached only from this process.

Build from source

go build .          # or: go install github.com/Foony-Limited/foony-sync@latest
go test ./...

Cutting a release

Releases are version tags. With main green:

git tag v0.2.0
git push origin v0.2.0

release-image.yml then builds the image for linux/amd64 and linux/arm64 and pushes ghcr.io/foony-limited/foony-sync:0.2.0 plus :latest. The tag's version (without the v) is stamped into the binary and shows up in dashboard heartbeats as the agent version.

Before tagging, check whether realtime-go has shipped a release the agent should pick up:

go get github.com/Foony-Limited/realtime-go@latest && go mod tidy

Verify a release with an anonymous pull once the workflow finishes:

docker pull ghcr.io/foony-limited/foony-sync:0.2.0

License

Apache-2.0 © Foony Limited