Skip to main content

Agent Patterns

Examples on this page use this demo table:

CREATE TABLE tasks (id BIGINT PRIMARY KEY, name TEXT, status TEXT, agent TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO tasks (id, name, status, agent) VALUES
    (1, 'summarize-report', 'done',    'agent-a'),
    (2, 'label-images',     'running', 'agent-b'),
    (3, 'draft-reply',      'queued',  'agent-a');
ALTER TABLE tasks ADD COLUMN owner TEXT;
CREATE TABLE documents (id BIGINT PRIMARY KEY, tenant_id TEXT, text TEXT, embedding TEXT);
INSERT INTO documents VALUES (1, 'acme', 'Welcome guide', '[0.12, -0.04, 0.91]');
CREATE ROLE agent_run_142_role;

By the end of this page you will have five concrete patterns for building agents on ScramDB: retrieving over live data, constraining agent-written SQL, choosing a tool surface, and keeping every agent role inside its own privileges, plus a working example that proves the guardrail is real.

Retrieval over live data​

Because ScramDB keeps one live copy of your data, retrieval for an agent is just a SELECT, no separate index to keep in sync. A similarity-ranked retrieval query looks like this:

SELECT id, text
FROM documents
WHERE tenant_id = 'acme'
ORDER BY embeddings_cosine_similarity(embedding, '[0.12, -0.04, 0.91]') DESC
LIMIT 5;

The WHERE tenant_id = 'acme' filter and the similarity ranking run against whatever the last committed transaction wrote. See Building Agent Features for the full walkthrough, including the exact bundled functions and how to install them.

Agent-written SQL: guardrails, not sandboxing by hope​

ScramDB has no query-approval or SQL-linting layer of its own that vets a generated statement before it runs today. The guardrails are the standard SQL access-control primitives, and they are real and enforced at execution time, not just parsed:

  • Least privilege. Grant the agent's role only the tables, columns, and actions the task needs, never ALL.

  • Branch, don't risk the source. Run destructive or exploratory agent work on a branch (see Branching for Agents), never the source database, so a bad generated statement costs nothing.

  • Row-level security when a database is shared. If an agent role must share a database with other traffic instead of getting its own branch, use row-level security to keep it to a subset of rows:

    ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

    CREATE POLICY agent_owns_its_rows ON tasks
        FOR SELECT
        TO agent_run_142_role
        USING (owner = 'agent_run_142');

    Add WITH CHECK on an INSERT or UPDATE policy to also stop the role from writing rows it does not own.

ScramDB does not statically analyze or veto an LLM-generated SQL statement before executing it. If you want a review step in front of agent-generated SQL, treat it the same way you would treat a review step in front of agent-generated code: a process you build around the agent, not a feature ScramDB provides.

Tool surfaces: named queries, not raw SQL access​

Rather than connecting a model directly to a role with broad SELECT/INSERT privileges, expose a small number of parameterized, application-defined queries as the agent's "tools." Each one is a fixed statement shape, with the model supplying only the parameter values, and your own review already applied to the statement shape itself before it ever runs. Ordinary parameterized queries and PREPAREd statements, available through any PostgreSQL driver, are the natural fit: the statement text is fixed and reviewed once, and only the bound parameters vary per call.

If a tool is really just calling a UDF, scram.invoke (and its batch and columnar variants) is a one-shot call form that skips composing a full SELECT fn(...) query. See Install and use a package for how those work.

Keeping an agent inside a role's privileges​

An operational checklist, built entirely from real, enforced SQL:

  1. One role per agent, or per agent class, never the bootstrap or superuser role.
  2. GRANT exactly the tables, columns, and actions the task needs: 
    GRANT SELECT (id, name, status) ON tasks TO agent_run_142_role;
    Column-level GRANT is real and enforced: a role granted SELECT on specific columns cannot read the rest of the row.
  3. Remember that ScramDB has no working SQL form for PostgreSQL's ALTER DEFAULT PRIVILEGES. A table created after the role already exists is not automatically covered by any grant you made earlier. Either re-run GRANT after the agent creates something another role needs to read, or grant generously on a branch, since the branch itself is already the isolation boundary.
  4. Use CREATE POLICY (row-level security) when the unit of isolation is rows within a shared table, not whole tables.
  5. Give the agent's role a password and require SCRAM authentication in any environment that is not pure local development. See Connecting an Agent and the quick start's warning about the default --pg-no-auth mode.

A minimal worked example​

One scoped role, one grant, one query that succeeds, one that is refused:

-- as an administrative role
CREATE ROLE demo_agent LOGIN PASSWORD 'a-real-password';
GRANT SELECT (id, name) ON tasks TO demo_agent;
GRANT INSERT ON tasks TO demo_agent;

Connect as demo_agent (see Connecting an Agent), then:

-- succeeds: id and name are granted
SELECT id, name FROM tasks LIMIT 5;
-- refused: status was never granted to this role
SELECT status FROM tasks LIMIT 5;

Expected result for the second query: an error whose message contains permission denied. The exact wording is not a fixed contract, but the refusal itself is real and execution-enforced, not a client-side convention the agent could bypass by generating different SQL: the column simply was not granted, and the engine rejects the read.