Building Agent Features
Examples on this page use this demo table:
CREATE TABLE documents (id BIGINT PRIMARY KEY, tenant_id TEXT, account_id BIGINT,
text TEXT, category TEXT, embedding VECTOR(3));
INSERT INTO documents (id, tenant_id, account_id, text, category, embedding) VALUES
(1, 'acme', 1, 'How to install and run.', 'guide', '[0.10, 0.20, 0.30]'),
(2, 'acme', 2, 'Making queries fast.', 'tuning', '[0.90, 0.10, 0.05]');
CREATE INDEX documents_embedding_idx ON documents USING anode (embedding vector_cosine_ops);
CREATE TABLE accounts (id BIGINT PRIMARY KEY, status TEXT);
INSERT INTO accounts VALUES (1, 'active'), (2, 'suspended');
CREATE TABLE document_term_stats (id BIGINT, term TEXT, tf INT, doc_len INT,
avg_doc_len DOUBLE PRECISION, doc_freq INT, corpus_size INT);
INSERT INTO document_term_stats VALUES (1, 'invoice', 3, 120, 100.0, 4, 20);
CREATE TABLE places (id BIGINT PRIMARY KEY, name TEXT,
lat DOUBLE PRECISION, lon DOUBLE PRECISION);
INSERT INTO places VALUES (1, 'Ferry Building', 37.7955, -122.3937);
By the end of this page you will have embedding similarity, full-text relevance, and geospatial context wired into an agent's retrieval queries, using ScramDB's bundled packages, no custom code required.
These are the bundled-package building blocks most agent features need: retrieval, ranking, and location-aware context. Writing your own function instead of using a bundled one is a different, separate path, covered in full by Programmability, Writing a Package, and Install and use a package. This page only shows the "use what's bundled" path, for an agent use case, end to end.
1. Install once
Every bundled function follows the same pattern: install the package, and the install registers the function under its own name.
SELECT scram.install('scramdb/bm25_term_score');
-- the install registers bm25_term_score(...) - call it directly
To use a bundled package under another name, bind it by hand with CREATE OR REPLACE FUNCTION ... AS 'namespace/pkg': LANGUAGE js is the binding keyword for JS, TypeScript, Rust, Go, C, and C++ packages, and a package of Python or Ruby source is bound with its own keyword (LANGUAGE python, LANGUAGE ruby). A function declared in another language than its package is refused.
Run this once per function you use. Every example below assumes the relevant function has already been installed this way. The image installs the bundled packages for you at startup, so this step is already done on a stock image.
2. Embedding similarity for retrieval-augmented generation
Embeddings live in a native VECTOR column beside the rows they describe, and <=> ranks by cosine distance:
SELECT id, text
FROM documents
WHERE tenant_id = 'acme'
ORDER BY embedding <=> '[0.12, -0.04, 0.91]'
LIMIT 5;
The ordering is ascending because <=> is a distance: nearest first. That is the shape the anode index on documents serves, so the search stays sub-linear as the corpus grows instead of scoring every row that passes the filter. cosine_distance(a, b) is the same operator spelled as a function, and <-> and <#> rank by Euclidean distance and inner product for models trained on those metrics.
Retrieval for an agent is almost always similarity plus a business filter, and both belong in one statement, as above. See Vector Search for the full cookbook page and Vector Search in the SQL reference for thresholds, nearest-neighbour joins and the tuning knobs.
3. Full-text relevance for keyword-style agent queries
SELECT scram.install('scramdb/bm25_term_score');
-- the install registers bm25_term_score(...) - call it directly
SELECT id, bm25_term_score(tf, doc_len, avg_doc_len, doc_freq, corpus_size, 1.2, 0.75) AS score
FROM document_term_stats
WHERE term = 'invoice'
ORDER BY score DESC
LIMIT 5;
This scores one term against one table of precomputed per-document term statistics; it is enough for an agent to combine a keyword filter with a relevance score. See Full-Text Search for the multi-word, whole-corpus version.
4. Geospatial context
An agent answering "what's near the user" can score distance directly in SQL:
SELECT scram.install('scramdb/geo_haversine_distance');
-- the install registers geo_haversine_distance(...) - call it directly
SELECT id, name,
geo_haversine_distance(37.7749, -122.4194, lat, lon) AS distance_meters
FROM places
WHERE geo_haversine_distance(37.7749, -122.4194, lat, lon) < 5000
ORDER BY distance_meters
LIMIT 10;
geo_haversine_distance takes latitude first and returns meters. See Geospatial for the full cookbook page, including geo_geohash_encode for grouping points into map cells.
5. Combining structural and semantic scoring in one query
An agent's retrieval is rarely just similarity or just a filter; usually it's both, in the same query:
SELECT d.id, d.text, d.category
FROM documents d
JOIN accounts a ON a.id = d.account_id
WHERE a.status = 'active'
ORDER BY d.embedding <=> '[0.12, -0.04, 0.91]'
LIMIT 5;
An ordinary WHERE/JOIN filter narrows the candidate set to what your business logic allows, and the distance or bm25_term_score ordering ranks what's left. This is the same combined pattern the vector-search cookbook shows, and it is the bridge into Using Graphs, where the same query adds a graph hop on top of a similarity score.