Configuration options

This page lists the field keys for the config map to be passed to Collect.create_document_table/3, Collect.merge_deltas/3 and Collect.drop_document_table_from_config/3

See also

• Collect.document_table_config/0 for type specifications
• Joins configuration for the shape of a joins chain
• Writing SQL in configuration ↓

source_table

Required

The name of the source table, with an optional database schema prefix.

Examples

• "articles"
• "catalog.articles"

identity_column

Required

The name of the column in the source table that uniquely identifies each row. It is used to reference source rows in the document table.

The identity column must be an integer type (smallint, integer, or bigint) and provide a stable, unique value per row. This is satisfied by any of:

• A serial or GENERATED AS IDENTITY column,
• The table's primary key (if integer),
• An integer column with a UNIQUE NOT NULL constraint.

If the source table does not yet have such a column, one can be added by passing config option add_identity_column_if_not_exists.

add_identity_column_if_not_exists

Adds an auto-increment identity column to the source table, using the column name provided via config option identity_column.

This step is skipped if a valid identity column already exists.

data_fields

Optional (defaults to [])

The fields stored in the document's data (a jsonb column). Each entry has a field_name (the key under which the value is stored) and describes how its value is derived.

The aggregate option selects the mode. Modes are: :array, :first, :object_array, and scalar (default).

Scalar

A single value read from a column. Automatically assigned when no aggregate is used.

Map keys:

• value_column - The column from the source table or a joined table.
• join_table and joins (optional) - If the value comes from a joined table.

Examples

Read a value from the source table:

%{
  ...
  data_fields: [
    %{field_name: "title", value_column: "title"}
  ]
}

Read a value from a joined table:

%{
  ...
  data_fields: [
    %{
      field_name: "author_name",
      value_column: "name",
      join_table: "authors",
      joins: [
        %{
          table: "authors",
          match: "authors.id",
          to: "articles.author_id"
        }
      ]
    }
  ]
}

:array

A list of values collected across a one-to-many join.

Requires:

• value_column
• join_table
• joins

Examples

%{
  ...
  data_fields: [
    %{
      field_name: "categories",
      value_column: "name",
      join_table: "categories",
      aggregate: :array,
      joins: [
        %{
          table: "article_categories",
          match: "article_categories.article_id",
          to: "articles.id"
        },
        %{
          table: "categories",
          match: "categories.id",
          to: "article_categories.category_id"
        }
      ]
    }
  ]
}

:object_array

A list of objects collected across a one-to-many join. Instead of value_column, it takes a non-empty columns list, each entry a %{key, column} mapping a key in the resulting object to a source column.

Requires:

• columns
• join_table
• joins

Examples

%{
  ...
  data_fields: [
    %{
      field_name: "categories",
      columns: [
        %{key: "name", column: "name"},
        %{key: "title", column: "title"}
      ],
      join_table: "categories",
      aggregate: :object_array,
      joins: [
        %{
          table: "article_categories",
          match: "article_categories.article_id",
          to: "articles.id"
        },
        %{
          table: "categories",
          match: "categories.id",
          to: "article_categories.category_id"
        }
      ]
    }
  ]
}

:first

A single value picked from a one-to-many join.

Requires:

• value_column
• join_table
• joins
• order_by (mandatory: without a defined order, which row is picked would be arbitrary and could differ between a full backfill and an incremental rebuild)

Examples

%{
  ...
  data_fields: [
    %{
      field_name: "primary_author",
      value_column: "name",
      join_table: "authors",
      aggregate: :first,
      order_by: "authors.rank",
      joins: [
        %{
          table: "article_authors",
          match: "article_authors.article_id",
          to: "articles.id"
        },
        %{
          table: "authors",
          match: "authors.id",
          to: "article_authors.author_id"
        }
      ]
    }
  ]
}

search_fields

Optional (defaults to [])

A list of fields to make searchable.

A search field entry contains:

• field_name - A field name that is defined in data_fields.
• weight (optional) - A value that defines the priority of the text, from 1 (highest) to 4 (lowest). Defaults to 4 when omitted.
• key (optional) - when the data field contains an object, key extracts a nested value from it. For example, key: "street" reads data.address.street. Use this when the data field is an object (or object array). Can be combined with transforms.
• transforms (optional) - to apply PostgreSQL transforms. It is a list of chained SQL expressions applied to the extracted value; the ? placeholder is replaced with the value. Note that a transform that calls an extension function (for example unaccent(?)) requires that extension to be installed.

Examples

Make the title and summary fields searchable, with the highest priority given to the title:

%{
  ...
  search_fields: [
    %{field_name: "title", weight: 1},
    %{field_name: "summary", weight: 2}
  ]
}

Make the title field searchable, removing diacritics from the text (requires installation of the unaccent extension):

%{
  ...
  search_fields: [
    %{field_name: "title", transforms: ["unaccent(?)"]}
  ]
}

Make data.address.street searchable:

%{
  ...
  search_fields: [
    %{field_name: "address", key: "street"},
  ]
}

language

Optional (defaults to "english")

The language option specifies the PostgreSQL text-search configuration used to build the search_vector (via to_tsvector). It controls stemming, stop-word removal, and token normalization.

To store literal, unprocessed tokens - no stemming, no stop-word removal - use language: "simple".

The value must be a text-search configuration PostgreSQL recognizes (e.g. english, simple, french, or a custom one). Queries against the table should use the same configuration, or matches will be inconsistent.

columns

Optional (defaults to [])

Creates additional columns, on top of the default source_identity, data and search_vector, for sorting, filtering, and indexing.

The column value is extracted from the data (jsonb) fields.
The value is extracted from data as text. A transform that needs another type (for date formatting, arithmetic, etc.) must cast the value first - for example (?)::timestamptz.

A column entry contains:

• name - A field name that is defined in data_fields.
• from - The data key to read. The data value should be a scalar (or, with key, an object containing a scalar at that key).
• type - The PostgreSQL type: "text", "integer", "boolean", "numeric(10,2)", "timestamptz", etc.
• key (optional) - When the from data field contains an object, key extracts a nested value from it. For example, from: "address", key: "street" reads data.address.street. Use this when the data field is an object (or object array). Can be combined with transforms.
• transforms (optional) - To apply PostgreSQL transforms. It is a list of chained SQL expressions applied to the extracted value; the ? placeholder is replaced with the value. Note that a transform that calls an extension function (for example unaccent(?)) requires that extension to be installed.

Examples

Create a column "active" from a boolean data.active:

%{
  ...
  columns: [
    %{
      name: "active",
      from: "active",
      type: "boolean"
    }
  ]
}

Extract data.address.street into a sortable column:

%{
  ...
  columns: [
    %{
      name: "street_sort",
      from: "address",
      key: "street",
      type: "text"
    }
  ]
}

Create a column "title_sort" from data.title, with lowercase values:

%{
  ...
  columns: [
    %{
      name: "title_sort",
      from: "title",
      type: "text",
      transforms: ["lower(?)"]
    }
  ]
}

Create a column "publish_iso_date" that formats a utc_datetime as an ISO 8601 string:

%{
  ...
  columns: [
    %{
      name: "publish_iso_date",
      from: "publish_date",
      type: "text",
      transforms: ["to_char((?)::timestamptz, 'YYYY-MM-DD')"]
    }
  ]
}

To create a sortable date column, simply pass a date type:

%{
  ...
  columns: [
    %{
      name: "publish_date_sort",
      from: "publish_date",
      type: "date"
    }
  ]
}

indexes

Optional (defaults to [])

Additional indexes on the document table's extracted columns.

An index entry contains:

• One or more column names.
• The index method via using (default "btree") (optional). Possible values are PostgreSQL index methods: "btree" (default), "gin", "gist", "brin", "hash".

Notes:

• The search_vector column is always indexed with a GIN index automatically; it does not need to be declared.
• A listed column must be a declared extracted column (in columns); indexing a column that isn't extracted is rejected.

Examples

Create 2 indexes, where the index on the published_at column uses the BRIN method:

%{
  ...
  indexes: [
    %{columns: ["title_sort"]},
    %{columns: ["published_at"], using: "brin"}
  ]
}

scopes

Optional (defaults to [])

Use scopes to tailor a document table to a subset of the source - for example, only active records, or the records belonging to one division.

A scope entry contains:

• where - An SQL boolean expression restricting which source rows become document rows. When there are multiple scope entries, their where expressions are combined with AND: a source row is included only if it satisfies all of them.
• join_table and joins - Supply these if the where predicate needs to reference a joined table.

Examples

Only include source rows where the active column is TRUE:

%{
  ...
  scopes: [
    %{where: "active = true"}
  ]
}

Only include articles whose author is active:

%{
  ...
  scopes: [
    %{
      where: "authors.active = true",
      join_table: "authors",
      joins: [
        %{table: "authors", match: "authors.id", to: "articles.author_id"}
      ]
    }
  ]
}

Writing SQL in configuration

Several configuration options take raw SQL fragments that Collect inserts into the generated queries verbatim:

• A scope's where predicate
• A data field's where predicate
• A join's where predicate
• An order_by (required for :first fields)
• A column's transforms

Because these are inserted as-is, you are writing SQL directly, and two rules apply.

1. Quote literal values

Any literal value in a fragment must be quoted the way PostgreSQL expects. Text, UUIDs, dates, and similar values need single quotes; only numeric literals and keywords (true, false, null) go unquoted.

# text, UUID, and date values need single quotes
%{where: "status = 'active'"}
%{where: "id = '0195edcd-d7dc-739b-b728-d63528a34d21'"}
%{where: "published_at > '2024-01-01'"}

# numbers and keywords do not
%{where: "priority > 5"}
%{where: "archived = false"}

A common mistake is interpolating a UUID without quotes:

scopes: [%{where: "id = #{user_id}"}]

This produces id = 0195edcd-..., which PostgreSQL reads as a numeric literal (0195) followed by junk:

"trailing junk after numeric literal at or near \"0195edcd\""

Instead, quote the value:

scopes: [%{where: "id = '#{user_id}'"}]

2. Treat interpolated values as untrusted

A fragment is inserted into SQL without escaping, so interpolating a value into one is the same as building a raw query by hand. When a value could come from outside your control, validate it first.

For a UUID, Ecto.UUID.cast!/1 both confirms it is a valid UUID and guarantees it is safe to interpolate:

uuid = Ecto.UUID.cast!(user_id)
scopes: [%{where: "id = '#{uuid}'"}]