Postgres as the source of truth

Preview: the direct Postgres source is implemented and tested, but it is not a supported production topology. Managed-provider validation and production sizing remain open release requirements. Managed databases can also restrict DDL capture and require TLS configuration.

Use this integration when PostgreSQL must remain the authoritative database. Applications keep their writes in Postgres. Rindle copies the selected tables into read-only followers, maintains queries over those copies, and streams changes to clients.

This setup adds a gateway, an archive, a snapshot producer, and Rindle followers. It requires PostgreSQL logical replication and operational control over its publication and replication slot. It is more involved than a standard Rindle deployment.

The gateway reads pgoutput, PostgreSQL’s logical replication stream. It archives each transaction and forwards the changes to followers. A snapshot producer creates snapshots for new or recovering followers.

PostgreSQL ──pgoutput──▶ rindle-pg-gateway ──frames──▶ archive (S3 / filesystem)
     ▲                         │ fan-out                 │
     │ slot ack = what the     ▼                         ▼
     │ archive holds       rindled followers        backup producer (snapshots)

Named queries and live subscriptions read from the followers. Writes go to Postgres through your existing services or Rindle’s mutators. This integration does not make @rindle/sql-client a Postgres SQL endpoint.

Before deployment, review requirements, type mappings, schema changes, and recovery consequences.

# Mutators against a Postgres authority

The preview includes a postgresBackend adapter for isomorphic mutators. It runs the server body against Postgres while the browser predicts against its local engine. It has different query and replay limits from the standard SQL mutation backend.

The following factory uses the shared example from the browser client guide. Generate that schema from the follower’s mirrored Postgres tables. Pass a configured follower control client and a caller-owned pg.Pool:

import {
  createRindleApiServer,
  pgPoolPlugger,
  postgresBackend,
  registerQueries,
  sharedApiMutators,
} from "@rindle/api-server";
import type { PgPoolLike } from "@rindle/api-server";
import type { RindleDaemonClient } from "@rindle/daemon-client";
import { issuesPageQuery, mutators, schema } from "../shared/client-example.ts";

type User = string | undefined;

export function createPostgresApi(daemon: RindleDaemonClient, pool: PgPoolLike) {
  return createRindleApiServer<User>({
    daemon,
    backend: postgresBackend(pgPoolPlugger(pool)),
    schema,
    queries: registerQueries<User>([issuesPageQuery]),
    mutators: sharedApiMutators(mutators, ({ user }) => {
      if (!user) throw new Error("authentication required");
      return { user };
    }),
    authorizeQuery: ({ user }) => Boolean(user),
    authorizeMutation: ({ user }) => Boolean(user),
  });
}

This factory does not create the gateway or follower. The caller also owns the Postgres pool’s shutdown, normally await pool.end(). Use the HTTP adapter to supply verified users. When an explicit control client returns no public WebSocket endpoint, configure daemon: { wsUrl } on the browser client with the follower’s public endpoint.

The server mutator gets one Postgres transaction. Logical writes and point reads through tx.row use that transaction, so reads see earlier writes. Raw SQL uses Postgres placeholders ($1, $2, and so on). The optional rewriteSql: questionToDollarParams adapter translates ? placeholders in raw statements. Logical operations render the correct dialect automatically.

Two current limits affect application correctness:

• Full query reads are unavailable. tx.query throws on this backend. Use primary-key reads through tx.row or deliberate server-only tx.sql.query. A shared mutator that yields tx.query cannot run here yet.
• Mutation replay is not deduplicated before the body. The adapter runs the body and monotonically upserts the mutation watermark. It does not reject sequence gaps or skip an already-seen mutation ID. An HTTP retry can therefore repeat non-idempotent work. Use idempotent operations or an application-owned transactional replay guard. Do not assume the standard SQL backend’s replay protection applies to this preview adapter.

One piece has to exist in Postgres for the loop to close, and rindle pg prepare creates it for you with --client-mutations:

rindle pg prepare --pg-host db --pg-user rindle --pg-database app \
  --tables public.issue,public.comment --source app --client-mutations

That creates public._rindle_client_mutations (client_id text primary key, last_mutation_id bigint not null) and adds it to the publication. It is how a client learns its writes landed: the API server upserts the client’s last_mutation_id in the same transaction as the mutation’s data, so the confirmation rides the same commit through pgoutput to the followers and reaches the browser in the same coherent release as the rows it confirms. Create it without publishing it and every client’s pending queue waits forever. A rejected mutation still advances last_mutation_id — that is what drains the client’s queue and snaps the optimistic prediction back; there is no rejection signal on the replication path.

Plain Postgres writers — a DBA, a cron job, another service — keep flowing as ordinary data with no last_mutation_id, confirming nothing. The two coexist.

The server mutator reads the current Postgres transaction. The client predicts against its local copy, which can lag behind Postgres. Concurrent writes or missing local rows can therefore change the predicted result. Reconciliation applies the authoritative changes and replays pending mutations.

Predictions can also differ from authority in other Rindle deployments. The Postgres topology adds replication lag between the source database and Rindle’s followers, which can increase that difference.

# What is admitted

The gateway checks the live catalog before it mints a generation (rindle pg preflight):

• PostgreSQL 15 or newer, wal_level = logical, not in recovery, headroom in max_replication_slots / max_wal_senders. The replication connection must reach the primary directly — not through PgBouncer or another pooler: Neon’s -pooler host, Supabase’s Supavisor host or port 6543, and any host that names a pooler are refused by name.
• The managed provider, when the catalog identifies one (Neon, Supabase, RDS, Aurora, Cloud SQL, or Azure), or when you declare it with --provider. PlanetScale can be selected explicitly because it has no catalog fingerprint. Detection enables provider-specific gates and warnings. Hostname-only hints do not certify a provider or its operational policy. Review the current checks and verify privileges, slot retention, billing, and failover behavior with your provider before deployment.
• Row-level security. A published table with RLS enabled is refused unless the connecting role is a superuser, has BYPASSRLS, or owns the table (without FORCE ROW LEVEL SECURITY): the initial copy and every backfill run as that role and would see only policy-visible rows, while the stream carries every row. On Supabase, where RLS is on by default, give the capture role BYPASSRLS.
• A publication that publishes insert, update, delete, and truncate for every captured table (publish_via_partition_root for partitioned tables). Column lists and row filters are honored; rindle pg prepare creates the publication for you.
• A row key per table: the primary key, or the first unique, immediate, non-partial index whose columns are all NOT NULL — which must also be the table’s replica identity. REPLICA IDENTITY DEFAULT is enough; FULL is not required for TOAST columns.
• A role with REPLICATION and read access to the captured tables. Installing the in-band DDL capture (below) additionally needs the right to create event triggers; without it the gateway runs in degraded mode, where a schema change must be announced with rindle pg schema-hook (below) or it stops the stream. The recipe is verified — installed at the current version, bound to this publication, with no un-announced change pending — at preflight --source, at mint, and every time a gateway boots.
• Failover readiness is recorded, not gated: on PostgreSQL 17+ with a synchronous standby, a failover slot, synchronous_commit = remote_apply and synchronized_standby_slots, the gateway creates the generation’s slot as a failover slot and a promoted standby carries it — you repoint the gateway and it resumes under the same generation, with no follower rebuild. Without every part of that recipe, a source failover means a new generation. The posture is decided once, at mint, from the live catalog: a generation minted without a synchronous standby stays a fencing one for life, so this is not something to turn on during an incident. For the supported provider-managed configuration, --failover provider-managed asks for that posture instead: the slot is created as a failover slot, you register it with the provider before the gateway’s first boot (rindle pg mint prints the command), and after a switchover the gateway verifies the slot survived on the same cluster at or below its acked position and resumes. What it cannot verify is that the promoted node kept every transaction already archived; that is the provider’s durability promise, recorded as such in the generation.

# Types

Every column arrives with its exact Postgres type tag recorded in the follower’s _rindle_columns sidecar; the mirror table’s declared SQLite type follows this table.

This table describes capture and storage, not the full browser query contract. Postgres integer types map to exact BIGINT/int64 columns. Maintained queries currently reject an int64 column anywhere they require it: selected columns, primary keys, filters, ordering, or correlations. In particular, a table with an integer primary key can be mirrored successfully while its live query is refused. This is a current preview limitation, not a reason to cast stored keys through JavaScript numbers. A projection can avoid an unrelated int64 column only when the query does not otherwise need it.

An UPDATE that leaves a large TOAST value untouched is applied as a masked edit: only the columns Postgres re-logged are written, the stored value survives, and the incremental engine still sees the full old/new row.

# Schema changes

With DDL capture installed (rindle pg prepare does it), a migration tool’s ALTER rides the stream in order, inside the transaction it committed in, and the followers apply the translated SQLite change at exactly that point — no new generation, no re-snapshot:

A backfill copies the affected rows at a consistent snapshot from a temporary replication slot and lands them exactly between the transactions around that snapshot. Until it completes, the column or table exists in the mirror but is hidden from /schema, the query catalog, and the wire schema, so no client sees a half-filled column. Lag during a backfill equals the copy’s duration.

What is not translated: a change to a row key or replica identity, and a DDL the renderer cannot express. Those stop the stream with a named reason; the fix is a new generation (rindle pg mint → rindle pg cutover), which is blue/green: the old generation keeps serving until the new one has a producer snapshot.

When the triggers cannot see the change — Supabase fires no event trigger for ALTER PUBLICATION, and a host that refuses event triggers altogether runs degraded — announce it from inside the migration’s own transaction, after the DDL:

BEGIN;
ALTER PUBLICATION rindle_pub ADD TABLE public.comment;
SELECT rindle.schema_hook();                          -- or: COMMENT ON PUBLICATION rindle_pub IS 'sync';
COMMIT;

The hook diffs the last schema the gateway was told about (rindle.published_schema) against the live catalog and emits the same in-band transition the triggers would have, at that commit. rindle pg schema-hook --dry-run prints both spellings; without --dry-run it runs the hook now, in its own transaction, which is only safe while no rows are written under the new shape.

# Storage and lag

The gateway streams the initial copy into the archive one frame at a time. Its local log retains recently archived frames. backup.retainBytes controls that retention and defaults to 48 MiB.

When unarchived data exceeds backup.maxUnshippedBytes (80 MiB by default), the gateway stops reading. Postgres retains the remaining WAL under the replication slot. The default settings bound ordinary local buffering to hundreds of MiB; this is not a total-disk guarantee for every workload. Allow headroom for frame sizes, in-flight uploads, logs, and operational files. The snapshot producer needs storage large enough for the dataset.

A follower behind the local log can read the archived tail through the gateway. backup.archiveCatchUpMaxBytes defaults to 1 GiB. Beyond that gap, a follower restores from a newer snapshot if one exists. A value of 0 disables archive read-through.

A follower restart resumes from its saved cursor. It restores at startup if it has no state or its cursor predates the archive’s oldest snapshot.

# Operating it

These are the lifecycle commands, not a complete copy-and-run deployment. app is an example source name; --store s3 selects the S3 store configured through the backup environment. The PostgreSQL credentials, archive, gateway JSON, producer, and follower configuration must already agree. Commands that connect to Postgres require --pg-user and --pg-database. Supply the password through RINDLE_PG_PASSWORD or --pg-password-file. Configure TLS for your source; the default require mode encrypts the connection, while verify-full also verifies its certificate and hostname. The repository’s infra/postgres-authority/ drills provide complete local test configurations.

rindle pg preflight --pg-host db --pg-user rindle --pg-database app --source app
rindle pg prepare --pg-host db --pg-user rindle --pg-database app \
  --tables public.issue,public.comment --source app
rindle pg schema-hook --pg-host db --pg-user rindle --pg-database app --source app --dry-run
rindle pg mint --pg-host db --pg-user rindle --pg-database app --source app --store s3
rindle-pg-gateway    --config gateway.json                               # genesis, then stream
rindle backup producer --source app --store s3 --scratch producer.db     # snapshots
rindle pg status     --source app --store s3
rindle pg cutover    --source app --store s3 --generation <id>           # blue/green; --rollback

The gateway acknowledges the replication slot only up to what the archive holds, so Postgres keeps WAL for anything not yet durable off-box. Size max_slot_wal_keep_size for the longest archive outage you will tolerate.

# Recovery consequences

• Gateway host lost — the local log is a cache. A fresh host seeds from the archive’s tail and Postgres redelivers from the slot; followers whose cursor is ahead of the archive re-bootstrap from it. No Postgres snapshot is taken.
• Slot invalidated (max_slot_wal_keep_size exceeded, wal_status = lost) — the stream cannot resume; mint a new generation, which takes one consistent snapshot.
• Producer down — followers keep streaming; only new-follower bootstrap ages. The backup plane’s ProducerSilent / SnapshotStale alerts fire.
• Archive unreachable — the gateway stops acking and, once backup.maxUnshippedBytes of frames wait locally, stops reading the stream; WAL accumulates on the primary under the slot until the archive returns, bounded by max_slot_wal_keep_size. Followers within backup.retainBytes of the head keep streaming; a follower further behind is served the archived tail once the archive returns (it restores only if its gap passed backup.archiveCatchUpMaxBytes with a newer snapshot to restore from).
• Source failover — the gateway can resume on PostgreSQL 17+ when its configured failover-slot recipe holds; otherwise it requires a new generation. Before promoting, check on the standby that its copy of the slot is synced and its confirmed_flush_lsn has reached the gateway’s acked LSN: the changes between the two are what a premature promotion loses. On a quiet source the copy may need pg_log_standby_snapshot() on the primary to catch up. Under the provider-managed posture the provider promotes; the gateway’s reconnect check decides.
• Reconnect — before every fresh replication connection (a restart, a lost stream) the gateway checks the endpoint on a normal connection: the same cluster it was admitted on, a primary, no timeline regression, this generation’s slot present, not invalidated, a failover slot when the posture needs one, and not advanced past the archive. A standby or a slot still held by the old walsender waits; anything else halts with the reason in /readyz (pump poisoned: replication slot does not exist …), and the fix is a new generation.

# Managed providers

These are the provider detectors and integration checks implemented by Rindle’s current preflight. They are not a certification of live provider policies.

Before using a managed database, check its current official documentation for replication permissions, slot limits and expiry, endpoint selection, TLS, and failover guarantees. A warning emitted by the CLI can reflect an integration assumption that your provider or plan has since changed.

The managed-provider nightly drill remains an open release gate. Local tests and provider detection do not establish that a hosted database has passed it.

The full plan, invariants, and the open release gates live in the repository under designs/406-DIRECT-POSTGRES-SOURCE-PARITY-PLAN.md, the managed-provider design under designs/407-MANAGED-POSTGRES-PROVIDERS-DESIGN.md, and the drills under infra/postgres-authority/.