# Full-text search

DuckDB's `fts` extension adds full-text indexes and BM25 ranking. QuackDB provides small SQL helpers for index management and Ecto helpers for search expressions.

## Load the extension

```elixir
alias QuackDB.FTS

QuackDB.query!(conn, FTS.install())
QuackDB.query!(conn, FTS.load())
```

DuckDB can autoload extensions in many environments, but explicit install/load keeps setup scripts and examples predictable.

## Create an index

```elixir
QuackDB.query!(
  conn,
  FTS.create_index("documents", :id, [:title, :body],
    stemmer: :porter,
    stopwords: :english,
    overwrite: true
  )
)
```

Use `:all` to index all `VARCHAR` columns:

```elixir
QuackDB.query!(conn, FTS.create_index("documents", :id, :all, overwrite: true))
```

Drop the generated index schema with:

```elixir
QuackDB.query!(conn, FTS.drop_index("documents"))
```

The index is a snapshot of the table when it was created; rows added later are not searchable until the index is recreated with `overwrite: true`. DuckDB 2.0 replays a recreated or dropped index from the write-ahead log, so a database closed without a checkpoint reopens with the new index.

DuckDB creates a schema for each index. For `main.documents`, the generated schema is `fts_main_documents`; an unqualified `documents` lives in `main` and gets the same schema. Use `FTS.schema_name/1` when building raw SQL fragments.

## Index a materialized source

DuckDB FTS indexes operate on tables, so source scans are often materialized first. This example uses a persistent table so subsequent pooled calls can query it. Temporary tables are session-local: their entire create/index/search/cleanup workflow must stay on one checked-out connection, for example inside a transaction.

```elixir
use QuackDB.Ecto

alias QuackDB.{DDL, FTS, Source}

source = Source.parquet("s3://analytics/documents/*.parquet")

query =
  from doc in source,
    select: %{id: doc.id, title: doc.title, body: doc.body}

QuackDB.query!(conn, DDL.create_table("docs", as: query))
QuackDB.query!(conn, FTS.create_index("docs", :id, [:title, :body], overwrite: true))
```

The same pattern works for CSV, JSON, Delta, Iceberg, Hugging Face datasets, or any source expression DuckDB can read. `DDL.create_table/2` accepts Ecto queries without pinned parameters because DDL helpers return SQL iodata rather than `{sql, params}` tuples. Use literal query expressions in the CTAS query, or materialize parameterized results before indexing. Use `QuackDB.Secret.create/2` and `QuackDB.Extension.load/1` when the source needs credentials or an extension such as `httpfs`.

## Search from direct SQL

```elixir
schema = FTS.schema_name("main.documents")
score = FTS.match_bm25(~s|"id"|, "duckdb analytics", schema: schema)

QuackDB.query!(conn, [
  "SELECT id, title, ", score, " AS score ",
  "FROM documents WHERE ", score, " > 0 ",
  "ORDER BY score DESC"
])
```

`match_bm25/3` accepts DuckDB's BM25 options. `bm25/3` and `search_score/3` are aliases when those read better in query-building code:

```elixir
FTS.match_bm25(~s|"id"|, "duckdb analytics",
  schema: "fts_main_documents",
  fields: [:title, :body],
  k: 1.2,
  b: 0.75,
  conjunctive: false
)
```

## Search from Ecto

Import `QuackDB.Ecto.FTS` or `use QuackDB.Ecto`:

```elixir
import Ecto.Query
import QuackDB.Ecto.FTS

scored =
  from doc in "documents",
    select: %{
      id: doc.id,
      title: doc.title,
      score: match_bm25("fts_main_documents", doc.id, ^"duckdb analytics")
    }

query =
  from doc in subquery(scored),
    where: doc.score > 0,
    order_by: [desc: doc.score],
    limit: 50,
    select: %{id: doc.id, title: doc.title, score: doc.score}

MyApp.AnalyticsRepo.all(query)
```

The subquery shape keeps the BM25 expression named once and lets the outer query filter, order, and limit by `score` without repeating the helper call. For large source-text tables, use the same Ecto pattern at a coarser grain first, then join the top matches to detailed rows:

```elixir
file_scores =
  from file in "files",
    select: %{
      file_id: file.file_id,
      score: match_bm25("fts_main_files", file.file_id, ^"duckdb analytics")
    }

top_files =
  from file in subquery(file_scores),
    where: file.score > 0,
    order_by: [desc: file.score],
    limit: 25

query =
  from fragment in "fragments",
    join: file in subquery(top_files),
    on: fragment.file_id == file.file_id,
    order_by: [desc: file.score, asc: fragment.path],
    select: %{
      file_id: fragment.file_id,
      path: fragment.path,
      content: fragment.content,
      file_score: file.score
    }

MyApp.AnalyticsRepo.all(query)
```

For static query modules, pass the generated FTS schema as a literal string. For runtime table names, pin the schema name and the helper will emit an Ecto `identifier(^schema)` fragment:

```elixir
schema = QuackDB.FTS.schema_name("main.documents")

from doc in "documents",
  where: match_bm25(^schema, doc.id, ^"duckdb analytics") > 0,
  select: %{score: search_score(^schema, doc.id, ^"duckdb analytics")}
```

## Text stemming

DuckDB's FTS extension also exposes `stem`:

```elixir
QuackDB.query!(conn, ["SELECT ", FTS.stem("'running'", :english)])
```

In Ecto:

```elixir
from doc in "documents", select: stem(doc.body, ^"english")
```