PostgreSQL Type Handling#

Sequin handles PostgreSQL types at two distinct layers during backfill and keyset cursor pagination: row-level serialization (converting Postgrex structs to JSON-safe values) and cursor-level casting (converting stored cursor strings back to typed values for SQL WHERE clauses).

Keyset Cursor: min_for_type and cast_value#

lib/sequin/runtime/keyset_cursor.ex drives type handling for cursor-based pagination.

• min_for_type/1 returns the lexicographically smallest seed value for each supported type (e.g., "00000000-0000-0000-0000-000000000000" for UUID, ~N[0001-01-01 00:00:00] for timestamp). Unsupported types fall back to "" (empty string).

• cast_value/2 converts a string cursor value back to its native Elixir/Postgrex type before it is bound as a SQL parameter. Covered types: uuid, timestamp, timestamp without/with time zone, date, time (both variants), integer/bigint/smallint/serial. The catch-all clause passes values through unchanged — this is the safe fallback for types like numeric, text, or any custom domain type.

• casted_cursor_values/2 applies cast_value/2 over the full cursor map, producing an ordered list of typed values that match the ? placeholders in the WHERE SQL.

Row-Level Serialization: load_rows#

Postgres.load_rows/2 in lib/sequin/postgres/postgres.ex converts each row fetched during a backfill into a JSON-safe map. Key per-type behaviors:

The no-encoder fallback (line 925) is the safety net for any Postgrex struct not explicitly handled — it avoids a runtime crash but drops the value, emitting a debug log with the column and table name.

Custom Jason Encoders: Sequin.Postgrex.Encoders#

lib/sequin/postgrex/encoders.ex implements Jason.Encoder protocol for types that would otherwise fall through to Jason.Encoder.Any:

• Postgrex.Lexeme → encodes as %{type: "lexeme", word: ..., positions: [...]} 8
• Postgrex.Range → serialized as a bracket-notation string (e.g., [2023-01-01,2024-01-01)) via a String.Chars implementation that handles Date, NaiveDateTime, and DateTime bounds 9

Notable Fixes#

PR #2093 — Relax UUID validation 10: Sequin.String.string_to_binary!/1 (called by cast_value for UUID cursors) was relaxed to accept any UUID parseable by UUID.info(), not just RFC 4122 versions 4 and 7. This unblocked custom ULID-based primary keys used as sort columns.

PR #2095 — Serialize interval columns during backfill 11: Before this fix, Postgrex.Interval structs had no Jason.Encoder implementation, causing them to serialize as null in backfill output (hitting the no-encoder fallback). The fix added handling so intervals are converted during load_rows — currently via format_interval/1, which produces a %{"months" => ..., "days" => ..., "microseconds" => ...} map. This ensures consistency between backfill and live CDC output.

Key Source Files#