Skip to main content

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 TEXT);
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 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 two-step pattern: install the package, then bind it to a callable SQL function name.

SELECT scram.install('scramdb/embeddings_cosine_similarity');

-- the install registers embeddings_cosine_similarity(a, b) - call it directly

LANGUAGE js is the binding keyword for this package-reference form regardless of the package's actual implementation language; it works the same way for JS, TypeScript, Rust, Go, C, and C++ packages. It does not work for LANGUAGE python or LANGUAGE ruby packages, which only accept inline source (LANGUAGE python AS $$...$$), never a package-reference binding.

Run this once per function you use. Every example below assumes the relevant function has already been installed and bound this way.

2. Embedding similarity for retrieval-augmented generation​

A documents table with an embedding stored as a JSON array in a text column:

SELECT scram.install('scramdb/embeddings_cosine_similarity');

-- the install registers embeddings_cosine_similarity(a, b) - call it directly
SELECT id, text
FROM documents
WHERE tenant_id = 'acme'
ORDER BY embeddings_cosine_similarity(embedding, '[0.12, -0.04, 0.91]') DESC
LIMIT 5;

There is no native vector type and no ANN or vector index. embeddings_cosine_similarity and embeddings_dot both take a JSON array embedding as text and score it in plain SQL. Every similarity example, including this one, is a scored, ordered, and LIMITed scan over the rows that pass your business filter (tenant_id = 'acme' here), not an index lookup. See Vector Search for the full cookbook page.

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 distance_meters < 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 embeddings_cosine_similarity(d.embedding, '[0.12, -0.04, 0.91]') DESC
LIMIT 5;

An ordinary WHERE/JOIN filter narrows the candidate set to what your business logic allows, and the similarity 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.