# Collections

In pgContext, a collection is a catalog entry that groups table-backed
retrieval configuration. The V1 milestone covers collection
create/inspect/drop, dense vector-column registration, stable point ID
mappings, exact table-backed search, deterministic point scrolling, and
experimental sparse vector-column metadata.

Future consumption of vector configuration by quantized serving and named
sparse ANN is tracked in the [post-V1 roadmap](roadmap.md).

Collection names must be ASCII identifiers:

- start with an ASCII letter or underscore;
- contain only ASCII letters, digits, and underscores;
- fit within PostgreSQL's 63-byte identifier limit.

Invalid names use SQLSTATE `22023` (`invalid_parameter_value`). Duplicate collection
creation uses SQLSTATE `42710` (`duplicate_object`). Missing collection metadata
uses SQLSTATE `42704` (`undefined_object`).

## Create a Collection

```sql
SELECT collection_id, collection_name, owner_name
FROM pgcontext.create_collection('docs');
```

The collection owner is captured from `SESSION_USER` when the collection is
created.

To associate a collection with an existing table, pass a schema-qualified table
name:

```sql
CREATE TABLE public.docs (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  embedding vector
);

SELECT collection_id, collection_name, owner_name, table_schema, table_name
FROM pgcontext.create_collection('docs', 'public.docs');
```

The table must already exist and must be an ordinary or partitioned table.
pgContext records catalog metadata only; it does not copy or own table data.

## Inspect a Collection

```sql
SELECT collection_id, collection_name, owner_name, table_schema, table_name
FROM pgcontext.collection_info('docs');
```

`table_schema` and `table_name` are `NULL` for collections created without a
source table.

## Collection Aliases

Use collection aliases to route read traffic through a stable application-facing
name while embedding model migrations prepare a replacement collection:

```sql
SELECT alias_name, collection_name
FROM pgcontext.create_collection_alias('docs_live', 'docs_v2');
```

Aliases are visible through the same collection ACL view used by search, query,
scroll, count, facet, optimization status, telemetry, and diagnostics. Retarget
an alias by calling `create_collection_alias` again with the same alias and a
new target collection. List visible aliases with `pgcontext.collection_aliases()`
and remove one with `pgcontext.drop_collection_alias(alias_name)`.

Alias names use the same identifier rules as collections and cannot collide with
real collection names. Creating a collection whose name is already an alias also
uses SQLSTATE `42710` (`duplicate_object`). Point and catalog mutation APIs
should use the real collection name so migrations remain explicit.

## Configure Strict Limits

Collections can opt into strict-mode limits that reject operations before they
would exceed collection-level budgets:

```sql
SELECT strict_mode,
       max_dimensions,
       max_vectors,
       max_points,
       max_filter_nodes,
       max_search_limit,
       max_candidate_budget,
       query_timeout_ms,
       max_index_memory_bytes
FROM pgcontext.configure_collection_limits(
  'docs',
  true,
  1536,
  1,
  1000000,
  128,
  100,
  1000,
  250,
  1073741824
);
```

Pass `NULL` for a limit that should keep the global pgContext policy. Inspect
the current settings with `pgcontext.collection_limits(collection_name)`.

Strict mode currently enforces vector dimensions, vector registration count,
active point count before point upserts, search/scroll/facet result limits, and
candidate-point batch budgets for candidate recheck search. Timeout,
filter-node, and index-memory fields are stored as stable policy metadata for
planners and diagnostics that consume those budgets. Invalid limit values use
SQLSTATE `22023` (`invalid_parameter_value`); operations that exceed an enabled
strict limit use SQLSTATE `54000` (`program_limit_exceeded`) before partial work
is committed.

## Register a Vector

Register a dense vector column before table-backed search can use it:

```sql
SELECT collection_name,
       vector_name,
       table_schema,
       table_name,
       vector_column,
       dimensions,
       metric
FROM pgcontext.register_vector('docs', 'embedding', 'embedding', 1536, 'cosine');
```

`register_vector` validates that the collection exists, has a source table, and
that the named column currently exists on that table with type `vector`.
Supported metric names are `l2`, `inner_product`, `cosine`, and `l1`.

The caller must have `SELECT` privilege on the source table when creating a
table-backed collection and when registering vector columns. Collection
mutation APIs run with pgContext catalog privileges but check the SQL session
user against the stored collection owner. Non-owners cannot register vectors,
map points, delete points, or drop the collection.

Missing tables use SQLSTATE `42P01` (`undefined_table`). Missing vector columns
use SQLSTATE `42703` (`undefined_column`). Non-vector columns use SQLSTATE
`42804` (`datatype_mismatch`). Invalid dimensions use SQLSTATE `22023`
(`invalid_parameter_value`). Unsupported metrics use SQLSTATE `0A000`
(`feature_not_supported`). Duplicate vector names or duplicate vector-column
registrations use SQLSTATE `42710` (`duplicate_object`).

List registered dense vectors and their per-vector planner metadata with:

```sql
SELECT vector_name,
       vector_column,
       dimensions,
       metric,
       hnsw_options,
       quantization_options,
       status
FROM pgcontext.collection_vectors('docs');
```

The `collection_vectors` and `configure_vector` function contracts are
experimental. Their HNSW and quantization option fields are validated metadata,
but the serving semantics are outside the stable SQL compatibility promise until
the HNSW and quantized-index parity
rows graduate. PostgreSQL source tables and indexes remain authoritative. Newly
registered vectors default to empty option objects and status `ready`.

Update the per-vector metadata with:

```sql
SELECT vector_name, hnsw_options, quantization_options, status
FROM pgcontext.configure_vector(
  'docs',
  'embedding',
  '{"m":16,"ef_search":64}'::jsonb,
  '{"mode":"scalar","levels":256}'::jsonb,
  'building'
);
```

Allowed status values are `ready`, `building`, `disabled`, and `failed`.
Option values must be JSON objects. Missing vector registrations use SQLSTATE
`42704` (`undefined_object`); invalid option shapes or status values use
SQLSTATE `22023` (`invalid_parameter_value`). `quantization_options` may name
`metadata_version` or `version`; future versions, unsupported modes, invalid
scalar levels, and incompatible product-quantization dimensions are rejected
with SQLSTATE `22023` instead of being stored silently.

## Register a Sparse Vector

Register sparse-vector source columns when applications need collection-level
metadata for lexical or sparse embedding branches:

```sql
SELECT collection_name,
       vector_name,
       table_schema,
       table_name,
       vector_column,
       dimensions,
       metric,
       storage_options,
       index_options,
       status
FROM pgcontext.register_sparse_vector('docs', 'lexical', 'sparse_terms', 4096, 'inner_product');
```

`register_sparse_vector` validates that the collection exists, has a source
table, and that the named column currently exists on that table with type
`sparsevec`. Supported sparse metric names are `l2`, `inner_product`,
`cosine`, and `l1`. Sparse cosine rejects zero vectors because the distance is
undefined.

List registered sparse vectors with:

```sql
SELECT vector_name,
       vector_column,
       dimensions,
       metric,
       storage_options,
       index_options,
       status
FROM pgcontext.collection_sparse_vectors('docs');
```

Update per-sparse-vector metadata with:

```sql
SELECT vector_name, storage_options, index_options, status
FROM pgcontext.configure_sparse_vector(
  'docs',
  'lexical',
  '{"format":"posting_lists"}'::jsonb,
  '{"strategy":"exact"}'::jsonb,
  'building'
);
```

Sparse metadata is experimental. It records first-class storage and index
intent. Exact sparse top-k is available over explicit arrays and registered
sparse source columns through `pgcontext.search_sparse`, and exact dense+sparse
RRF fusion is available through `pgcontext.query`. Sparse ANN/index serving
remains planned. Missing sparse columns use SQLSTATE `42703`
(`undefined_column`). Non-`sparsevec` columns use SQLSTATE `42804`
(`datatype_mismatch`). Unsupported sparse metrics use SQLSTATE `0A000`
(`feature_not_supported`). Duplicate sparse vector names or duplicate sparse
vector-column registrations use SQLSTATE `42710` (`duplicate_object`). Option
values must be JSON objects, and status accepts `ready`, `building`,
`disabled`, or `failed`.

## Model Versions

Register embedding model versions used by a collection before planning
migrations or shadow reads:

```sql
SELECT collection_name,
       model_name,
       model_version,
       dimensions,
       metric,
       is_active
FROM pgcontext.register_model_version(
  'docs',
  'text-embedding-3-small',
  '2026-07-02',
  1536,
  'cosine'
);
```

List registered model versions with:

```sql
SELECT collection_name,
       model_name,
       model_version,
       dimensions,
       metric,
       is_active
FROM pgcontext.model_versions();
```

Model names and versions are operator-defined labels. Supported metric names
match vector registration. Duplicate model/version pairs use SQLSTATE `42710`
(`duplicate_object`).

## Embedding Migrations

Create a migration between two registered model versions before starting a
backfill:

```sql
SELECT migration_id,
       collection_name,
       source_model,
       source_version,
       target_model,
       target_version,
       status,
       total_points,
       processed_points
FROM pgcontext.create_embedding_migration(
  'docs',
  'text-embedding-3-small',
  '2026-07-02',
  'text-embedding-3-large',
  '2026-07-02',
  100000
);
```

Update backfill progress with:

```sql
SELECT migration_id, status, total_points, processed_points
FROM pgcontext.update_embedding_migration(1, 25000, 'running');
```

List migrations with `pgcontext.embedding_migrations()`. `status` is the typed
enum `pgcontext."EmbeddingMigrationStatus"` with `Planned`, `Running`, `Completed`, or
`Failed`; update input uses lowercase `planned`, `running`, `completed`, or
`failed`.

## Register Filter Columns

Register ordinary source-table columns before using them in filters or facets:

```sql
SELECT collection_name, filter_key, table_schema, table_name, column_name
FROM pgcontext.register_filter_column('docs', 'tenant_id', 'tenant_id');
```

The `filter_key` is the user-facing JSON filter key. The column must exist on
the collection source table, and duplicate keys or duplicate source columns use
SQLSTATE `42710` (`duplicate_object`).

Register a JSONB path when the filter or facet value lives inside a JSONB
source-table column:

```sql
SELECT collection_name, filter_key, table_schema, table_name, column_name, jsonb_path
FROM pgcontext.register_jsonb_path('docs', 'topic', 'metadata', ARRAY['topic']);
```

The JSONB column must exist and have type `jsonb`. Missing JSONB path values do
not appear in facet counts.

## Mutate Registered Payload Fields

Payload mutation helpers update user source tables directly, so pgContext keeps
the writable surface explicit:

- only collection owners can mutate payloads;
- the session user must have `UPDATE` privilege on the source table;
- source keys must already be active pgContext points and must still resolve to
  source table rows;
- payload keys must be registered with `register_filter_column` or
  `register_jsonb_path`;
- ordinary-column deletes and clears are allowed only for nullable columns.

Set registered payload keys with a JSON object:

```sql
SELECT source_key, updated
FROM pgcontext.set_payload(
  'docs',
  ARRAY['42'],
  '{"status":"published","topic":"postgres"}'::jsonb
);
```

For ordinary registered columns, pgContext converts JSON scalar values through
the target SQL type for text, boolean, integer, floating, numeric, and JSONB
columns. For registered JSONB paths, `set_payload` writes the value with
`jsonb_set(..., create_missing := true)`.

Delete selected registered keys:

```sql
SELECT source_key, updated
FROM pgcontext.delete_payload('docs', ARRAY['42'], ARRAY['topic']);
```

Clear all registered payload fields for selected points:

```sql
SELECT source_key, updated
FROM pgcontext.clear_payload('docs', ARRAY['42']);
```

Unknown payload keys use SQLSTATE `22023` (`invalid_parameter_value`). Missing
collections or source keys use SQLSTATE `42704` (`undefined_object`). Attempts
to clear non-null ordinary columns use SQLSTATE `23502` (`not_null_violation`).

## Map Points

Point mappings connect application source-row keys to stable pgContext point
IDs. pgContext stores only this mapping; source rows and vectors remain in the
registered PostgreSQL table.

```sql
SELECT point_id, source_key, inserted
FROM pgcontext.upsert_points('docs', ARRAY['doc-1', 'doc-2']);
```

`inserted` is `true` for a new mapping and `false` when an existing mapping was
reactivated or refreshed. Source keys must be non-empty text values no larger
than the current catalog policy limit.

Delete point mappings with:

```sql
SELECT point_id, source_key
FROM pgcontext.delete_points('docs', ARRAY['doc-2']);
```

Deletes are logical. The source table is not modified, and a later
`upsert_points` call for the same source key reuses the same point ID.
Missing source keys are ignored by `delete_points`.

For large point sets, use the bulk progress APIs with an explicit batch size:

```sql
SELECT batch_number, processed_count, inserted_count, reactivated_count
FROM pgcontext.bulk_upsert_points(
  'docs',
  ARRAY['doc-1', 'doc-2', 'doc-3'],
  1000
);

SELECT batch_number, processed_count, deleted_count, missing_count
FROM pgcontext.bulk_delete_points('docs', ARRAY['doc-2', 'missing'], 1000);
```

`bulk_upsert_points` validates the whole supplied source-key array before any
point rows are written, so malformed input fails without a partial insert.
`bulk_delete_points` keeps the same logical-delete and missing-key behavior as
`delete_points`, but returns per-batch counts for operational progress.

Backfill point mappings directly from the registered source table's `id` column:

```sql
SELECT batch_number, processed_count, inserted_count, reactivated_count
FROM pgcontext.backfill_points('docs', 1000);
```

Backfill reads source rows as the invoking role, so source-table `SELECT`
privileges and row-level security policies apply. Each batch is bounded by the
requested batch size, reports progress as rows, and uses normal transaction
semantics for cancellation and rollback.

## Search a Table-Backed Collection

The current table-backed exact search path uses the source table's `id` column
as the stable source key. Point `source_key` values must match `id::text`.

```sql
SELECT point_id, source_key, score
FROM pgcontext.search('docs', '[0,0]'::vector, 10);
```

Search uses the collection's registered vector column and metric, skips
logically deleted points, orders by score and then point ID, and rechecks the
registered table, source-key column, vector column, ownership, and table
privileges at execution time.

Pass Qdrant-style filter JSON to restrict candidates before exact scoring:

```sql
SELECT point_id, source_key, score
FROM pgcontext.search(
  'docs',
  '[0,0]'::vector,
  '{"must":[{"key":"tenant_id","match":"acme"}]}',
  10
);
```

Filter-first search uses registered filter columns and JSONB paths, materializes
matching active point mappings as the candidate set, and then scores only those
source rows. Unregistered filter fields use SQLSTATE `22023`
(`invalid_parameter_value`). Partitioned source-table parents are supported;
filter predicates remain ordinary SQL predicates, so PostgreSQL can apply its
normal partition pruning rules. Search, scroll, count, facet, and hybrid query
functions read source tables as the invoking role, so PostgreSQL row-level
security policies on the source table are enforced during SQL recheck.

Candidate recheck search accepts a batch of point IDs, such as point IDs
returned by a future ANN scan, and performs the same table-backed exact scoring
only for active mappings in that batch:

```sql
SELECT point_id, source_key, score
FROM pgcontext.search('docs', '[0,0]'::vector, ARRAY[1, 2, 3]::bigint[], 10);
```

Pass a filter before the candidate array to recheck only candidates that still
match the same registered-field predicate plan used by filtered exact search,
count, and facets:

```sql
SELECT point_id, source_key, score
FROM pgcontext.search(
  'docs',
  '[0,0]'::vector,
  '{"must":[{"key":"tenant_id","match":"acme"}]}',
  ARRAY[1, 2, 3]::bigint[],
  10
);
```

## Query with Dense and Full-Text Fusion

Hybrid query combines the collection's registered dense vector branch with a
full-text branch over a source-table text column:

```sql
SELECT point_id, source_key, score
FROM pgcontext.query('docs', '[0,0]'::vector, 'database internals', 'body', 10);
```

The text column is passed by name and validated against the registered source
table at execution time. Results use reciprocal rank fusion and skip logically
deleted point mappings in both branches.

Inspect the retrieval stages with:

```sql
SELECT stage, detail
FROM pgcontext.explain('docs', 'body');
```

## Scroll Point Mappings

Scroll active point mappings in stable point-ID order:

```sql
SELECT point_id, source_key, next_cursor
FROM pgcontext.scroll('docs', NULL, 100);
```

Pass the last returned `next_cursor` to continue from the next point:

```sql
SELECT point_id, source_key, next_cursor
FROM pgcontext.scroll('docs', 'v1:...', 100);
```

Cursor tokens are opaque. They are bound to the collection catalog id and last
returned point id, and malformed, tampered, or stale collection cursors use
SQLSTATE `22023` (`invalid_parameter_value`).

## Count Point Mappings

Count active point mappings, optionally with the same registered-field filter
JSON used by filtered search and facets:

```sql
SELECT pgcontext.count('docs');

SELECT pgcontext.count(
  'docs',
  '{"must":[{"key":"tenant_id","match":"acme"}]}'
);
```

Counts skip logically deleted point mappings, apply source-table `SELECT`
privileges and row-level security, and reject unregistered filter fields with
SQLSTATE `22023` (`invalid_parameter_value`).

## Facet Point Mappings

Facet active point mappings by a registered ordinary column, with an optional
Qdrant-style filter JSON string:

```sql
SELECT value, count
FROM pgcontext.facet(
  'docs',
  'status',
  '{"must":[{"key":"tenant_id","match":"acme"}]}',
  10
);
```

This slice supports ordinary source-table columns. Facets skip logically
deleted points and `NULL` facet values, order by descending count then value,
and reject unregistered filter or facet fields with SQLSTATE `22023`
(`invalid_parameter_value`).

## Group Search Results

Group exact vector search results by a registered ordinary column or JSONB path:

```sql
SELECT group_value, point_id, source_key, score
FROM pgcontext.grouped_search('docs', '[0,0]'::vector, 'tenant_id', 2, 10);
```

Grouped search applies the same collection ownership, source-table `SELECT`,
row-level security, active-point, deleted-point, and registered payload-field
rules as exact search and facets. `group_limit` caps rows per group; `limit`
caps the final result set. Rows with `NULL` or missing group values are skipped,
and ties are deterministic by score then point id.

## Recommend Similar Points

Recommend points from positive and negative point examples:

```sql
SELECT point_id, source_key, score
FROM pgcontext.recommend('docs', ARRAY[101, 205], ARRAY[309], 10);
```

Positive and negative point IDs must be active and visible through the
registered source table. Example point IDs are excluded from the result set,
deleted points are rejected, and results use the collection's registered vector
metric with deterministic score then point-id ordering.

Raw vectors can be supplied instead when the application has example embeddings
that are not already mapped to point IDs:

```sql
SELECT point_id, source_key, score
FROM pgcontext.recommend(
  'docs',
  ARRAY['[0.1,0.2,0.3]'::vector],
  ARRAY[]::vector[],
  10
);
```

## Discover Diverse Points

Discover or explore points that are farthest from active context point IDs:

```sql
SELECT point_id, source_key, score
FROM pgcontext.discover('docs', ARRAY[101, 205], 10);
```

`pgcontext.explore` is an alias for the same exact diversity search. Context
point IDs must be active and visible through the registered source table, are
excluded from results, and deleted context points are rejected.

## Drop a Collection

```sql
SELECT pgcontext.drop_collection('docs');
```

`drop_collection` returns `true` when a catalog entry was removed and `false`
when the collection did not exist. Dropping a collection also removes its
pgContext vector registrations and point mappings. It does not drop or alter
the source table.