Product catalog, end to end

This tutorial follows one complete Indexlume workflow: connect a read-only PostgreSQL source, combine related product data, index it, validate the result in Search Studio, and call the public search API.

The names and records below are sanitized sample data. They demonstrate the workflow; Indexlume does not install an ecommerce schema or sample catalog for you.

What you will build

Each products row becomes one search document. Category and inventory values come from related tables discovered through foreign keys.

{
  "id": "101",
  "name": "Mechanical Keyboard",
  "description": "Compact wireless keyboard",
  "price_cents": 8999,
  "category_name": "Accessories",
  "stock_quantity": 24
}

The final collection supports text search on name and description, filtering and faceting by category_name, filtering by stock_quantity, and sorting by price_cents.

1. Prepare the sample schema

Use a development database. Run the downloadable product catalog SQL as a database administrator, not as the Indexlume connector.

The schema creates:

• categories as a lookup table;
• products as the projection root;
• inventory as a one-to-one product dependency;
• indexlume_reader as a login with CONNECT, schema USAGE, table SELECT, and default read-only transactions.

Replace the sample password and the catalog database name before running it. Limit the grants further when using an existing database.

2. Add the PostgreSQL source

1. Open Data Sources.

2. Choose Add data source → PostgreSQL.

3. Name the source Product catalog.

4. Enter the connector URL:

   postgresql://indexlume_reader:<password>@db.example.com:5432/catalog?sslmode=require

5. Keep strict TLS unless this is a deliberately trusted local-development network.

6. Test and save the source.

Expected result: the source reports a successful test, and dataset discovery lists products, categories, and inventory with their key relationships.

3. Create the target collection

Open Collections, create a collection with slug products, and map these fields:

Field	Purpose
id	Stable document identity
name	Searchable text
description	Searchable text
price_cents	Sortable numeric value
category_name	Filterable and facetable text
stock_quantity	Filterable numeric value

Use the field types presented by Indexlume that match the previewed PostgreSQL values. Do not change a field type after seeing a conflicting projection preview; correct the mapping first.

4. Build the relational projection

1. Return to Data Sources and open Product catalog.
2. Choose Create projection.
3. Select products as the root dataset and products.id as its stable identity.
4. Select the product scalar fields name, description, and price_cents.
5. Follow the discovered products.category_id → categories.id relationship and expose categories.name as category_name.
6. Follow products.id → inventory.product_id and expose inventory.quantity as stock_quantity.
7. Select the products target collection and save the draft.

Indexlume only follows catalog-backed relationships. This is not an arbitrary SQL editor.

5. Validate, preview, and publish

Run Validate and resolve every blocking catalog or mapping error. Then run Preview with a small limit.

Check that:

• every preview document has the expected root ID;
• category and inventory fields contain the expected related values;
• null behavior matches your source data;
• numeric values match the collection field types.

Publish the validated version and explicitly accept the documented eventual-consistency boundary. Publishing queues the initial snapshot job.

6. Monitor indexing

Open Indexing Jobs and select the new projection run. Wait for it to complete, then confirm its processed and failed counts.

If a run fails, inspect its recorded error before retrying. Indexlume records checkpoints and supports state-appropriate retry and cancellation actions; it does not silently discard failed records.

7. Test in Search Studio

Open Search Studio, select products, and try:

• query: keyboard;
• searchable fields: name, description;
• filter: stock_quantity greater than 0;
• sort: price_cents ascending;
• facet: category_name.

Expected result: the sample keyboard appears with its category and inventory values. Save the valid configuration as a preset if the project will reuse it.

8. Call the public search API

Create a key under Settings → API keys and copy it when shown. Then call the project-scoped endpoint:

curl --request POST \
  'https://search.example.com/api/v1/search/v1/<project-public-id>/products' \
  --header 'Authorization: Bearer <search-key>' \
  --header 'Content-Type: application/json' \
  --data '{
    "q": "keyboard",
    "filter": [{"field":"stock_quantity","operator":"gt","value":0}],
    "sort": [{"field":"price_cents","direction":"asc"}],
    "facets": ["category_name"],
    "page": 1,
    "perPage": 10
  }'

Continue with the TypeScript, React, and PHP examples when integrating an application.

9. Understand synchronization

PostgreSQL ingestion uses polling and checkpoint-safe replay; it is not complete CDC. Root and dependency changes can be processed by the projection workflow. Soft-delete behavior depends on configured source fields. For hard deletes without a supported soft-delete marker, use reconciliation preview, inspect the candidates, and submit the matching confirmation token.

Never automate reconciliation confirmation without reviewing the candidate count and deployment health.

File-import alternative

To explore the collection without PostgreSQL, download products.jsonl and follow Import JSONL files. This demonstrates direct document import and does not exercise multi-table projection behavior.