Backfill silently nulls columns of custom composite types (no Jason.Encoder), while streamed changes correctly serialize them as text

[roberto-palloni]

Summary

For a table column whose type is a custom Postgres composite type (e.g.

CREATE TYPE money_with_currency AS
  (currency_code varchar, amount numeric)

),
TableReader's backfill path always emits null for that column, while the same column streamed via a live insert/update/delete correctly emits Postgres's text representation of the value (e.g. "(EUR,1234.56)"). This means every row loaded via backfill silently loses data in that column — deterministically, on every backfill (initial, retried, or manually re-triggered), not just intermittently.

Environment

• Self-hosted Sequin, v0.14.6
• RabbitMQ sink, default (non-transform) message envelope
• Postgres source, logical replication

Reproduction

  CREATE TYPE public.money_with_currency AS (currency_code varchar, amount numeric);

  CREATE TABLE public.payments (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    amount public.money_with_currency NOT NULL
  );

  INSERT INTO public.payments (amount) VALUES (ROW('EUR', 42.00)::public.money_with_currency);

1. Create a sink with initial_backfill: true (or manually trigger a backfill) against a table containing a money_with_currency column with existing data.
2. Inspect the resulting action: "read" message for that row.
3. Separately, UPDATE the same row (or insert a new one) so it's captured by the live replication stream instead, and inspect that message.

Expected

Both messages represent amount the same way — ideally the same text form the live path already produces
("(EUR,42.00)"), or some structured equivalent, but consistently.

Actual

• Backfill (action: "read"): "amount": null
• Live (action: "insert"/"update"): "amount": "(EUR,42.00)"

Root cause

Sequin.Postgres.load_rows/2
(https://github.com/sequinstream/sequin/blob/v0.14.6/lib/sequin/postgres/postgres.ex#L890-L936), which every
backfilled row passes through in TableReader.fetch_batch/6, special-cases a fixed list of Postgrex value types
(Pgvector, bytea/bit/varbit, uuid, ranges, intervals) and falls through to this catch-all for everything else:

This is the catch-all when encode is not implemented

  Jason.Encoder.impl_for(value) == Jason.Encoder.Any ->
    Logger.debug("[Postgres] No Jason.Encoder for #{inspect(value)}", column: col.name, table: table.name)
    nil

Postgrex decodes a custom composite type into a struct with no registered Jason.Encoder, so it silently becomes nil here — logged only at debug, easy to miss in production. This code path is specific to the backfill's typed SELECT (Postgres.result_to_maps → Postgres.load_rows); the live replication path takes Postgres's own WAL text representation directly and never goes through Postgrex's typed decoding or this function at all, which is why it doesn't hit the same gap.

Impact

Any table backfilled (initial load, a table added to include_tables after already having data, or a manually
re-triggered catch-up backfill) loses every column of a custom composite type, silently, for every row the
backfill touches. This isn't a transient failure — it will reproduce identically on every future backfill of the
same table until a decoder is registered for that type. We found it affects a double-digit percentage of rows
across multiple tables in production before noticing, since nothing in the pipeline (or Sequin itself) surfaces
it above debug log level.

Suggested fix direction

• At minimum, log this at warning (or expose it via Sequin's health/metrics) rather than debug, so it's not silently discoverable only by downstream data audits.
• Ideally, fall back to Postgres's own text representation of the value (the same thing the WAL/live path already produces) for any type without a registered Jason.Encoder, instead of nil — consistent behavior between backfill and streaming, and no data loss for types Sequin doesn't have first-class support for.

[dosubot]

Hi @roberto-palloni! I'm Dosu and I'm helping the Sequin team.

Your read of load_rows/2 is accurate: it special-cases Pgvector, bytea/bit/varbit, uuid, ranges, and intervals, and anything else falls through to the Jason.Encoder.impl_for(value) == Jason.Encoder.Any branch, which logs at debug and returns nil [1] [2].

There's a related guard upstream that doesn't catch this case. Postgres.safe_select_columns/1 filters out columns whose type isn't in a known-has_encoder? list before building the backfill SELECT [3] [4], but for composite types, enums, and domains (pg_typtype in ["c", "d", "e"]), has_encoder? assumes Postgrex can encode the base type and includes the column anyway [5] — the comment even says "we don't yet store the underlying base type... assume/hope Postgrex has an encoder." So for money_with_currency, the column is selected, Postgrex decodes it into a struct with no Jason.Encoder, and it hits the catch-all and becomes nil.

That also explains the divergence you saw. The replication path never goes through Postgrex's typed decoding: it decodes tuple data via Sequin.Postgres.ValueCaster.cast/2, which works off Postgres's text representation and passes the raw string through unchanged for any type without a specific case [6] [7], which is why insert/update messages carry "(EUR,42.00)" [8].

So your suggested direction targets the real gap — the backfill path has no text-representation fallback, while the replication path gets one by design. I don't have visibility into whether a fix is already tracked internally; this is just what the code shows today. Want me to dig into how a text fallback (e.g., casting the column via ::text in the backfill SQL) could work without disturbing the other special-cased types?

To reply, just mention @dosu.

---

Share context across your team and agents. Try Dosu.

[roberto-palloni]

@dosu, yes go ahead

[dosubot]

A text-representation fallback would touch two places, and the cleaner of the two is the SQL side.

Postgres.safe_select_columns/1 filters table.columns down to those where has_encoder?/1 returns true and quotes each one for a plain select "col1", "col2", ... from table [1]. Composite, domain, and enum columns (pg_typtype in ["c", "d", "e"]) already pass has_encoder?/1, since it optimistically assumes Postgrex can handle them [2]. So they land in the query bare, Postgrex decodes them into a struct, and the value is lost downstream. TableReader.fetch_batch_query/3 builds its SELECT with this same helper (lib/sequin/runtime/table_reader.ex:283).

Postgres.load_rows/2 is where the value actually disappears. It pattern-matches on Elixir struct types (Pgvector, Postgrex.Range, Postgrex.Interval) rather than Postgres types, so no per-type SQL casting is involved — it operates purely on whatever Postgrex already decoded [3].

Following your "fall back to Postgres's own text representation" suggestion, the viable fix is to select those composite/domain/enum columns with an explicit ::text cast: for any column where pg_typtype in ["c","d","e"], emit "col"::text as "col" instead of "col". That bypasses Postgrex's typed decoding for exactly those columns — Postgrex hands back a string matching what the WAL/live path already produces — and load_rows's catch-all never fires for them, since the incoming value is already a plain string. It also leaves the Pgvector/range/interval/uuid special cases untouched; those have real structured Postgrex encoders and shouldn't be down-cast to text.

The alternative — keeping the current column list and changing load_rows's catch-all to to_string/1 or an inspect of the raw struct — is riskier. Postgrex's decoded struct for a composite type won't necessarily stringify to the same text Postgres emits over the wire, and there's no guarantee of a String.Chars implementation, so the SQL-level ::text cast is the more faithful match to the live-replication behavior you want to converge with.

Independent of either approach, bumping the "no Jason.Encoder" log from Logger.debug to Logger.warning [4] is a small, low-risk change that makes this failure mode visible right away.

I don't have visibility into whether the Sequin team plans to pick this up — this is just the shape a fix could take given the current code.

To reply, just mention @dosu.

---

Docs are dead. Just use Dosu.

[roberto-palloni]

@dosu can you alert someone in the team as this is a relatively quick fix but with high impact?

[dosubot]

I can't ping or notify maintainers directly — I'm limited to researching and posting here. That said, this issue itself is what surfaces the problem to the Sequin team, and your write-up already lays out the root cause, the reproduction path, and a suggested fix direction, so it's in good shape for a maintainer to pick up at triage.

To reply, just mention @dosu.

---

Share context across your team and agents. Try Dosu.