Sync Postgres records to Algolia index

Keep an Algolia search index continuously in sync with a PostgreSQL table. This blueprint watches the public.articles table for newly inserted or updated rows, upserts each one into an Algolia index, and stamps the source record as indexed so it is never reprocessed. It closes the classic gap between an operational database and a search backend: instead of running nightly full reindex jobs or sprinkling brittle webhook calls through your application code, you get an incremental, idempotent, near real time pipeline that only moves the records that actually changed.

How it works

1. A io.kestra.plugin.jdbc.postgresql.Trigger polls Postgres every five minutes (interval: PT5M, fetchType: FETCH). Its SQL selects id, title, content, and updated_at from public.articles where updated_at is newer than CURRENT_TIMESTAMP - INTERVAL '{{ inputs.lookback_interval }}' and the row has not yet been indexed (indexed_at IS NULL OR indexed_at < updated_at).
2. The each task (io.kestra.plugin.core.flow.ForEach) iterates over the rows, drawing from inputs.test_rows ?? trigger.rows ?? [] so the same flow can be driven by the trigger or by manual test data.
3. For every row, the upsert task (io.kestra.plugin.algolia.Index) writes the record to Algolia, merging in an objectID derived from the row id (falling back to a timestamp) so repeated runs update rather than duplicate the document.
4. The mark_indexed task (io.kestra.plugin.jdbc.postgresql.Query) runs UPDATE public.articles SET indexed_at = now() for that id, guaranteeing the row is skipped on the next poll.

What you get

• Near real time search freshness without a full nightly reindex.
• Idempotent upserts: the objectID mapping prevents duplicate Algolia documents.
• A self healing watermark via indexed_at so interrupted runs simply resume.
• One declarative flow instead of search logic scattered across application code.

Who it's for

• Backend and platform engineers wiring Postgres content into Algolia search.
• Data engineers who need a reliable change capture pattern for a search index.
• Teams running content, catalog, or knowledge base sites that must stay searchable.

Why orchestrate this with Kestra

Algolia's own dashboard can ingest data but cannot watch your database, retry a failed batch, or coordinate the write back to Postgres. Kestra fills that gap. The event style Trigger polls on a schedule you control, retries and replays are built in, every execution is captured for lineage and auditing, and the whole pipeline (database read, search upsert, watermark update) lives in one declarative YAML file you can version, review, and reuse across environments.

Prerequisites

• A running Postgres instance with a public.articles table containing id (PRIMARY KEY), title, content, updated_at, and an indexed_at (TIMESTAMP) column.
• An Algolia account with an existing index and a write enabled Admin API key.
• A running Kestra instance.

Secrets

• POSTGRES_JDBC_URL: JDBC URL for Postgres (for example, jdbc:postgresql://host:5432/db).
• POSTGRES_USERNAME: database username.
• POSTGRES_PASSWORD: database password.
• ALGOLIA_APP_ID: Algolia Application ID.
• ALGOLIA_ADMIN_API_KEY: Algolia Admin API key with write permissions.

Quick start

1. Add the five secrets above to your Kestra instance.
2. Confirm public.articles has an indexed_at column, or adjust the trigger SQL.
3. Set algolia_index to your target index name (default articles).
4. To test without the trigger, run the flow manually and pass a few JSON objects in test_rows.
5. Otherwise, save the flow and let the five minute trigger pick up changed rows.

How to extend

• Tune lookback_interval to widen or narrow the change window per run.
• Add a transformation task before upsert to reshape or enrich records for search relevance.
• Point at additional tables by parameterizing the trigger SQL and index name.
• Layer in a notification task on failure, or batch rows to respect Algolia rate limits.

Links

• Algolia plugin docs: https://kestra.io/plugins/plugin-algolia
• PostgreSQL plugin docs: https://kestra.io/plugins/plugin-jdbc-postgresql