YAML config apply resolves change_retentions against a stale table cache, crash-looping self-hosted deployments when tables are new

[hognevevle]

Summary

When applying declarative YAML config (Sequin.YamlLoader.apply_from_stdin! via start_commands.sh), change_retentions source/destination tables are resolved against the cached table list stored on the postgres_databases record — not against the live database. The cache is only refreshed on create_db and by a 6-hourly cron (EnqueueDatabaseUpdateWorker). If a table was created after the last refresh, config apply fails even though the table exists and the connection role can see it:

** (RuntimeError) Failed to apply config: %Sequin.Error.BadRequestError{message: "Error setting up change retention '...': Not found: No `table` found matching params: `name=..., schema=public`", code: nil}

Because config apply runs at container start (before start_application), the failure crash-loops the container — which in turn largely prevents the 6-hourly refresh from ever running, so the deployment cannot self-heal.

Environment

• Sequin v0.14.1, self-hosted, headless (SERVER_CHECK disabled — no web console available)
• Config delivered via CONFIG_FILE_PATH, applied on every container start
• Source database on Cloud SQL for PostgreSQL 16

Steps to reproduce

1. Run self-hosted Sequin with a YAML config including a database and change_retentions.
2. After Sequin has been running for a while (so tables_refreshed_at is in the past), create a new table in the source database.
3. Add a change_retentions entry for the new table and restart the container so the config re-applies.
4. Config apply fails with No table found matching params, the container exits, and it keeps crash-looping until the cache happens to be refreshed.

Where this happens in the code

• lib/sequin/yaml_loader.ex — parse_wal_pipeline_attrs/2 calls fetch_table(source_database.tables, ...), which searches the cached list only.
• lib/sequin/databases/databases.ex — update_db/2 (the path taken for an existing database during config apply) never calls update_tables/1.
• config/config.exs — the only periodic refresh is {"0 */6 * * *", Sequin.Databases.EnqueueDatabaseUpdateWorker}.

Workarounds we tried

• Granting privileges / verifying the table is visible to the replication role: no effect (the lookup never touches the live DB).
• Hand-inserting an Oban job for Sequin.Databases.DatabaseUpdateWorker into oban_jobs: the job was never picked up inside the short crash-loop windows (manual inserts don't fire the insert notification, and flipping the row to scheduled for the stager didn't get it dispatched in time either).
• What finally works is either manually writing the table entry into the postgres_databases.tables jsonb, or waiting for a boot to happen to straddle the 6-hour cron tick — neither is great operationally.

Request

Any of the following would solve this class of problem:

1. Refresh on miss: when fetch_table misses during YAML config apply, call Databases.update_tables/1 once and retry the lookup before failing. This keeps the fast path cached but makes declarative config deterministic.
2. Refresh on apply: unconditionally refresh tables for each upserted database during apply_from_yml (config apply is rare and already talks to the database).
3. An operator escape hatch: a documented release task, e.g. bin/sequin eval "Sequin.Release.refresh_tables", and/or a CLI command (sequin databases refresh-tables) so headless deployments can force a refresh without touching internal tables.

Option 1 seems the most aligned with the existing design. Happy to contribute a PR if you'd accept one — thanks for the great tool!

[dosubot]

Hi @hognevevle! I'm Dosu and I'm helping the Sequin team.

Great write-up — I can confirm the analysis is accurate. Here's what's happening in the code:

In apply_config/3, the flow is: upsert databases → load databases from DB → resolve change_retentions [1]. When upserting an existing database, the update_database path does not call Databases.update_tables/1 [2], unlike the create_database path which does refresh tables [3]. So when databases are loaded at line 166, they carry the stale cached tables list.

Then parse_wal_pipeline_attrs/2 calls fetch_table/4, which is a pure in-memory filter over database.tables with no fallback [4]:

defp fetch_table(tables, schema, table_name, _role) do
  case Enum.find(tables, &(&1.schema == schema && &1.name == table_name)) do
    nil -> {:error, Error.not_found(entity: :table, params: %{schema: schema, name: table_name})}
    table -> {:ok, table}
  end
end

And update_db/2 in databases.ex only updates the record and enqueues health checks — no table refresh [5].

A straightforward fix would be to call Databases.update_tables/1 for each database during apply_config before resolving WAL pipelines — either unconditionally (your option 2), or as a retry on miss (option 1). Since config apply is already a rare, startup-time operation, the extra round-trip to the source database seems very reasonable. No existing PRs appear to address this yet.

To reply, just mention @dosu.

---

Docs are dead. Just use Dosu.

[hognevevle]

For future reference, in case anyone else runs into the same in the mean time, this workaround (executed exactly within the short time while Sequin was trying to boot), forced a refresh of the table cache -- which in turn allowed Sequin to recover in the next attempt.

# wait until `docker ps` shows the sequin container up, then:
docker exec sequin bash -c 'cd /home/app && ./prod/rel/sequin/bin/sequin eval "Application.ensure_all_started(:sequin); Sequin.Databases.list_dbs() |> Enum.each(&Sequin.Databases.update_tables!/1)"'