# Postgres as the source of truth

Preview: keep PostgreSQL authoritative while Rindle captures its changes and serves live queries. Review setup, limits, and recovery.

> **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](https://rindle.sh/docs/deploy).

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](#what-is-admitted),
[type mappings](#types), [schema changes](#schema-changes), and
[recovery consequences](#recovery-consequences).

## Mutators against a Postgres authority

The preview includes a `postgresBackend` adapter for
[isomorphic mutators](https://rindle.sh/docs/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](https://rindle.sh/docs/client#define-the-shared-query-and-writes). Generate
that schema from the follower's mirrored Postgres tables. Pass a configured
follower control client and a caller-owned `pg.Pool`:

```ts
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](https://rindle.sh/docs/api-server#bring-your-own-http) 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`:

```sh
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](#managed-providers) 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.

| PostgreSQL | Rindle type | Notes |
| --- | --- | --- |
| `bool` | boolean | |
| `int2`, `int4`, `int8`, `oid` | integer (exact 64-bit) | never rounded through a double |
| `float4`, `float8` | number | |
| `numeric` | string | exact decimal text |
| `text`, `varchar`, `char`, `citext`, `uuid`, `name` | string | |
| `json`, `jsonb` | json | raw text |
| `date`, `time`, `timetz`, `timestamp`, `timestamptz`, `interval` | string | Postgres text form |
| `bytea` | string | hex |
| arrays | json | JSON array text |
| enums, domains, anything else | string | tagged, never rejected |

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:

| Postgres DDL | Follower effect |
| --- | --- |
| `CREATE TABLE` then `ALTER PUBLICATION … ADD TABLE` | new mirror table; existing rows are **backfilled** |
| `ADD COLUMN` with a literal default (`'free'`, `0`, `true`, `NULL`) | `ALTER TABLE … ADD COLUMN … DEFAULT` |
| `ADD COLUMN` with an expression default (`now()`, `gen_random_uuid()`, `nextval(…)`) | column added, then **backfilled** |
| `RENAME COLUMN`, `RENAME TABLE` | rename |
| `DROP COLUMN`, `DROP TABLE` | drop (a destructive change bounces live queries) |
| `ALTER COLUMN … TYPE` | table rebuild with a cast |
| `ALTER PUBLICATION … DROP TABLE` | mirror dropped |

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:

```sql
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.

```sh
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.

| Provider | Detection or declaration | Integration checks |
| --- | --- | --- |
| Neon | `neon_superuser` role or `neon` extension | Reject a `-pooler` endpoint; report provider-specific slot and compute warnings |
| Supabase | `supabase_admin` role or `supautils` | Reject detected pooled endpoints; check RLS capture privileges; verify DDL-capture capability |
| PlanetScale for Postgres | `--provider planetscale`; hostname can supply a hint | Gate slot creation on the expected role; support the explicit provider-managed failover posture |
| RDS / Aurora | Provider roles, logical-replication setting, or `aurora_version()` | Report replication privilege and source-failover requirements |
| Cloud SQL / Azure Flexible Server | Provider roles or logical-decoding settings | Report source configuration and failover requirements |

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/`.

---

[View this page on Rindle](https://rindle.sh/docs/postgres-source)