collect

[![Hex](https://img.shields.io/hexpm/v/collect?label=Hex)](https://hex.pm/packages/collect) [![HexDocs](https://img.shields.io/badge/hexdocs-HexDocs-blue?label=Documentation)](https://collect.hexdocs.pm/) [![CI status](https://codeberg.org/ArthurClemens/collect/badges/workflows/ci.yml/badge.svg?label=CI)](https://codeberg.org/ArthurClemens/collect/actions) ![Coverage: 100%](https://img.shields.io/badge/code_coverage-100%25-brightgreen?label=Coverage) [![License](https://img.shields.io/hexpm/l/collect.svg?label=Licence)](https://codeberg.org/ArthurClemens/collect/src/branch/main/LICENSE)

Collect builds and maintains a **document table**: a continuously updated table that stores precomputed,
searchable data derived from a source table and, optionally, its joined tables.
Instead of joining and searching across several tables at query time, an application queries a single flat, indexed table.

## Motivation

Reading data that is spread across joined tables - and searching its text - is
expensive to do on every query. The usual way to speed this up is to precompute
the result, but the common approaches have drawbacks:

- A **materialized view** precomputes the result, but a change is not reflected until the whole view is rebuilt. 
  Rebuilding is slow on a large table, so it is done infrequently, and the view is out of date in between.
- A **hand-maintained table** kept in sync with triggers avoids the rebuild, but writing those triggers correctly - across
  multi-hop joins, and for inserts, updates, deletes, and cascading deletes - is difficult and error-prone.

Collect keeps the document table up to date by updating only the rows that have changed. Database triggers record
which source rows are affected by a change, and a merge step rebuilds only those rows. Because it never rebuilds
the entire table, the document table can stay current regardless of how large the source tables grow.

## Features

- Generate a document table with **aggregated data for display and search**.
- The document table is **automatically kept up to date** as the source changes (merged on demand).
- Configure joins to pull in **data from related tables**.
- Store **structured data** as `jsonb`.
- Store textual data in a weighted `search_vector` for **full-text search**.
- Configure extra columns for **sorting, indexing, or easy-to-apply filters**.
- Add **scope predicates** to restrict which source rows become document rows.

## Requirements

- Elixir
- PostgreSQL 12 or later.
  Collect uses a `MATERIALIZED` CTE in its generated SQL to compute each document once per row; the keyword is available from PostgreSQL 12 onward.