Ten things, worked through

Ten scenarios against one small domain, one capability at a time, each with the output it actually produced. This is the page for finding out what a single move does and what it gives you back.

Everything below was run against a live PostgreSQL 19 with the extension installed, and the tables are pasted from the transcript rather than typed out from what the query ought to return. You can reproduce all of it in about a minute, and the last section says how.

If you came looking for whole pipelines rather than single moves, this is the wrong page and workflow recipes is the right one. The two divide the work deliberately: this page answers what does GRAPH return, and that one answers how do I poll a source into a corpus an agent can be asked about.

The domain

One fixture, reused by all ten — and by the graph algorithms page, which asks a different class of question over the same nine edges. It is a port-operations company with two shipping lines in it: Meridian Line, which has a bulk subsidiary and three ships, and Kestrel Shipping, which has one. There are three ports belonging to neither, a chartering house both of them use, four inspection records, and five short inspection reports.

harbour fixture: 4 operators, 5 vessels, 3 ports, 4 inspections,
                 11 nodes, 9 edges, 5 chunks

Two tenants matter more than the ships do. Almost every example below is the same query asked twice with a different claim, and the answers differ because row-level security is doing the work rather than a where org_id = somebody remembered to write.

Why it works — five vessels but eleven nodes, and the missing one is deliberate

One of the ships is scrapped, and the registration that projects vessels into the graph carries include_where => n.status <> 'scrapped'. The row is still there in harbour.vessels; it is simply not an identity anybody can look up.

That is the inclusion policy doing its job. The node registry is a curated index over a subset of your data — things worth resolving by name, plus edge endpoints — rather than a copy of the data plane, which is what keeps it small enough to be a lookup rather than a second database.

1. Resolve a name somebody half-remembered

This is the question an agent asks first, and it almost never arrives with the right spelling.

What we are trying to do here is find a vessel from a misspelled name, and then make the right spelling cheap for next time.

select aiq.query('LOOKUP "meridien dawn"');       -- 0 rows
select aiq.query('FUZZY LOOKUP "meridien dawn" LIMIT 3');

aiq.query

      key      | entity_type | score | match_kind
---------------+-------------+-------+------------
 meridian dawn | vessel      | 0.647 | fuzzy
 meridian      | operator    | 0.353 | fuzzy

select aiq.add_node_key('Meridian Dawn', 'dawn', 'short');
select aiq.query('LOOKUP "dawn"');

aiq.query

 key  | entity_type | match_kind
------+-------------+------------
 dawn | vessel      | short

Why it works — `LOOKUP` searches keys, and a short key is an editorial act

The lookup answers from the node registry rather than from any of the tables the keys came from, which is why one query resolves a vessel and an operator in the same breath — they are different tables and the same kind of identity.

Exact spelling returns nothing and FUZZY returns the ship plus the operator behind it at a lower score. If you know the shape of the names in your store you can skip the fuzz entirely by registering a short key, and nothing projects those for you deliberately: deciding that "dawn" means that ship is a judgement, not a derivation, and a system that guessed would eventually guess wrong in a way nobody could see.

2. Walk the ownership chain, and find a neighbour on the way

What we are trying to do here is answer two questions from one walk: who ultimately operates this ship, and what else was in the same port.

select aiq.query('GRAPH "Bulk Harmony" DEPTH 2');

aiq.query

 depth |   type   |           summary           |               path               | weight
-------+----------+-----------------------------+----------------------------------+--------
 1     | port     | Rotterdam, Netherlands      | ["last_called"]                  |   1.00
 1     | operator | Meridian Bulk               | ["operated_by"]                  |   1.00
 2     | vessel   | Meridian Dawn (IMO 9331802) | ["last_called", "last_called"]   |   1.00
 2     | operator | Meridian Line               | ["operated_by", "subsidiary_of"] |   0.95

select aiq.query('GRAPH "MERB" DEPTH 2 TYPE subsidiary_of');

aiq.query

 depth |    summary
-------+---------------
 1     | Meridian Line

Why it works — the path column finds relationships nobody stored, and the weight says how much to believe them

Two rows in that table are worth slowing down for.

The depth-2 row with ["last_called", "last_called"] is a different ship, reached by going out to Rotterdam and back in again. Nobody stored a "sibling" relationship; the walk found it, and the path is what tells you it is derived rather than asserted.

The ownership row carries a weight of 0.95 rather than 1.00 because the subsidiary claim came from a registry rather than from a filing, and edges.weight is where that kind of confidence lands. It is a static number, which is a known limitation — a claim asserted two years ago weighs the same as one asserted this morning.

3. Three ways to find the same page

The corpus is written so the three retrieval modes disagree, because a corpus where they agree cannot show you why there are three. One report carries a rare inspection code verbatim; another says the same thing in different words and carries none of them.

What we are trying to do here is find the same page lexically, semantically, and by fusing both, and watch the ranking change.

select aiq.query('TEXT "PSC-441" FROM chunks LIMIT 3');

TEXT — lexical, no embedding anywhere

 score  |                         content
--------+----------------------------------------------------------
 0.0991 | # Port state control, Rotterdam Meridian Dawn (IMO 93318
 0.0991 | # Meridian fleet exposure Internal. PSC-441 exposure now

:query_vector is the embedding of "a boiler fault", and it is an argument because the database makes no model calls — which is the whole reason a vector query compiles to two tasks. The \set line asks the model the corpus was embedded with, from psql, using the LLM_API_KEY that loaded the corpus and curl and jq from your shell; in a workflow the compiler writes that embed step for you. Write it :'query_vector', with the quotes, so the array arrives as one literal. Do not substitute a short literal to see it run: the wrong width is caught, and the wrong provenance is not.

\set query_vector `curl -s https://api.openai.com/v1/embeddings -H "Authorization: Bearer $LLM_API_KEY" -H 'Content-Type: application/json' -d '{"model": "text-embedding-3-small", "input": "a boiler fault"}' | jq -c '.data[0].embedding'`

select round((r->>'distance')::numeric,3) as distance,
       left(regexp_replace(c.content, '\s+', ' ', 'g'), 56) as content
from jsonb_array_elements(
       aiq.query('SEMANTIC "a boiler fault" FROM chunks LIMIT 3',
                 :'query_vector')->'rows') r
join content.resource_chunks c on c.id = (r->>'chunk_id')::uuid
order by 1;

SEMANTIC — the vector is an argument

 distance |                         content
----------+----------------------------------------------------------
    0.599 | # Auxiliary machinery note The secondary steam generator
    0.626 | # Port state control, Rotterdam Meridian Dawn (IMO 93318
    0.699 | # Meridian fleet exposure Internal. PSC-441 exposure now

select aiq.query('SEARCH "PSC-441 boiler" FROM chunks LIMIT 3', :'query_vector');

SEARCH — both rankings, fused

 lex | sem |   rrf   |                       content
-----+-----+---------+------------------------------------------------------
 1   | 2   | 0.03252 | # Port state control, Rotterdam Meridian Dawn (IMO 9
 2   | 3   | 0.03200 | # Meridian fleet exposure Internal. PSC-441 exposure
     | 1   | 0.01639 | # Auxiliary machinery note The secondary steam gener

Why it works — the nearest page by meaning shares no word with the query, and fusion still places it

The nearest semantic hit shares not one word with "a boiler fault", which is exactly the case TEXT cannot reach. Meanwhile TEXT finds the rare token PSC-441 that a vector will happily rank alongside a dozen near-synonyms.

Fusion is what lets a page found by only one of the two still place. Rows with an empty lex were never matched lexically at all and placed on their semantic rank alone.

SEMANTIC returns refs rather than content, which is why the middle query joins back to read them — the vector table holds chunk_id and an embedding, and carrying the text through the ranking would mean copying the corpus into every result.

4. Two tenants, one table

What we are trying to do here is run the same query under two different claims and see the answers differ, with nothing in the query filtering by organisation.

begin;
set local role authenticated;
set local request.jwt.claims =
  '{"sub":"e0000000-0000-0000-0000-00000000000a",
    "orgs":["d0000000-0000-0000-0000-00000000000a"]}';   -- Meridian
select entity_type, summary from aiq.nodes
where entity_type in ('vessel','operator') order by entity_type, summary;
rollback;

The begin is load-bearing and its absence is silent. SET LOCAL outside a transaction warns — SET LOCAL can only be used in transaction blocks — and then does nothing, so the claims are never set and the query runs with whatever identity the connection already had. Paste these three lines into psql without it and you get a result rather than an error: the superuser's, which bypasses RLS entirely and shows both tenants' rows. That looks like the demonstration working. rollback rather than commit because nothing here writes; either ends the transaction and discards the settings with it, which is the whole point of LOCAL.

as Meridian

 entity_type |           summary
-------------+-----------------------------
 operator    | Meridian Bulk
 operator    | Meridian Line
 operator    | Nordvik Chartering
 vessel      | Bulk Harmony (IMO 9407711)
 vessel      | Meridian Dawn (IMO 9331802)
 vessel      | Meridian Star (IMO 9331803)

the same statement, as Kestrel

 entity_type |           summary
-------------+------------------------------
 operator    | Kestrel Shipping
 operator    | Nordvik Chartering
 vessel      | Aurora Kestrel (IMO 9214578)

Why it works — RLS reorders results, it does not only remove rows

Nordvik appears in both because it belongs to the shared tier, and the three ports are there for the same reason. That tier is a real organisation — a well-known row with the same id in every deployment — rather than a null standing in for one. The distinction is the point: a NULL can only mean unknown, so "shared" and "nobody filled this in" would be the same value, and a policy cannot tell a deliberate choice from an omission. Without a shared tier at all, every tenant would need a private copy of Rotterdam.

The part worth checking by behaviour rather than by reading the policy is that a name does not resolve at all. select count(*) from aiq.lookup('Meridian Dawn') returns 0 as Kestrel — because a key that matches and then returns nothing looks identical to a key that does not exist, and only one of those is safe.

Now go back to the SEARCH in scenario 3 and run it as Kestrel:

SEARCH, as Kestrel

 lex | sem |                       content
-----+-----+------------------------------------------------------
 1   | 2   | Port state control at Rotterdam raised deficiency PS
     | 1   | The secondary steam generator was found unserviceabl

The winner's semantic rank moved from 3 to 2, because the Meridian note that outranked it is not there for this caller. Row-level security is inside the ranking, not a filter applied after it.

5. A file becomes answerable

What we are trying to do here is register bytes that live in object storage, record what they say, and have them searchable with no reindex step.

select content.register_upload(
    p_channel      => 'harbour-reports',
    p_checksum     => 'sha256:6f1a9c',
    p_bucket       => 'harbour',
    p_object_key   => 'psc/2026/gothenburg-9214578.pdf',
    p_size_bytes   => 48213,
    p_content_type => 'application/pdf',
    p_title        => 'Gothenburg PSC report',
    p_org_id       => 'd0000000-0000-0000-0000-00000000000a',
    p_external_id  => 'psc-2026-018') as resource_id \gset

select content.record_chunks(:'resource_id', $j$[
  {"ordinal":0,"content":"Gothenburg PSC inspection found the auxiliary boiler within tolerance.","start_offset":0,"end_offset":69},
  {"ordinal":1,"content":"No deficiencies were recorded against Aurora Kestrel on this call.","start_offset":70,"end_offset":135}
]$j$::jsonb);

TEXT "auxiliary boiler" FROM chunks — immediately afterwards, beside the sample's two reports that say the same

 score  |                        content
--------+--------------------------------------------------------
 0.0991 | # Port state control, Rotterdam Meridian Dawn (IMO 933
 0.0991 | # Meridian fleet exposure Internal. PSC-441 exposure n
 0.0991 | Gothenburg PSC inspection found the auxiliary boiler w

Why it works — there are two dedup keys and they do different jobs

Neither call needs the bytes in Postgres. The file lives in object storage and the row carries the bucket and key.

The checksum dedups the bytes in content.files; external_id dedups the registration in content.resources. Register the same upload twice with the same external_id and you get the same resource back. Register it twice with no external_id and you get two resources over one file, which means the ingestion runs twice:

registering twice with no external_id

 same_resource | same_file
---------------+-----------
 f             | t

So pass an external_id whenever the upload might be retried, and treat it as the caller's idempotency key. That is the same property recipe 1 leans on for a poller.

Two tenants uploading the same public PDF get one object and two resources, which is correct: they each uploaded a thing, with their own title, org and lifecycle, that happens to share bytes.

6. A workflow with nothing running

What we are trying to do here is run a three-step pipeline that has already finished by the time start_workflow returns.

name: fleet_brief
steps:
  - id: operator
    p8ql: 'LOOKUP "MERI"'
  - id: fleet
    needs: [operator]
    p8ql: 'GRAPH "MERI" DEPTH 1'
  - id: reports
    needs: [operator]
    p8ql: 'TEXT "PSC-441" FROM chunks LIMIT 3'

worker and rows_out are not columns — claimed_by carries the worker and the rows are inside the task's output document, so the query that produced the table below is:

select step_key, kind, status, claimed_by as worker,
       jsonb_array_length(output->'result'->'rows') as rows_out
from workflow.tasks
where run_id = (select id from workflow.runs order by created_at desc limit 1)
order by step_key;

workflow.tasks

 step_key | kind |  status   |   worker    | rows_out
----------+------+-----------+-------------+----------
 fleet    | sql  | succeeded | in-database |        4
 operator | sql  | succeeded | in-database |        1
 reports  | sql  | succeeded | in-database |        2

Why it works — and the one trap that reports success

Both step kinds here are in-database, so each executes inside the transaction that makes it ready. There is no queue to poll and no process to deploy, and the rows land on the task, so the graph walk the middle step did is readable straight out of workflow.tasks.output.

Before you write these, know whose privileges a step runs with. Every p8ql: step runs as the engine owner, not as you. That is true of a dialect query and of plain SQL alike, and plain SQL shows it most directly:

workflow.tasks.output, for `p8ql: "select current_user"`

  status   | mode |                    rows
-----------+------+---------------------------------------------
 succeeded | SQL  | [{"u": "app_owner"}]

app_owner owns every table and is not subject to their row-level security, so a step reads across tenants. A SEARCH … FROM chunks returns every org's uploads, and a plain-SQL step reads tables you hold no grant on. Anyone who may define a workflow may therefore read anything in the database. That is the trade the beta makes, expressiveness over isolation. It is stated here because earlier versions of this page claimed two opposites: that a plain-SQL step did not execute, and that a dialect step was scoped to you. Both run as the owner.

It reaches further than the people who define. In 0.1.6 anyone signed in may start any workflow, and the steps read with the owner's reach whoever started the run. A member of one org who starts a retrieval workflow an admin wrote gets answers from every org's documents, and the raw rows sit on their own run's tasks. The next extension release refuses such a read when the person who started the run may not define workflows. Until you run it, do not define a workflow that reads tenant data on a deployment holding more than one org's data, unless everyone who can sign in may read all of it.

Two more things bound it. A step without write: true runs in a read-only transaction, which the database enforces rather than a keyword filter, so this is a read rather than a tamper. And a deployment can refuse the SQL an author writes:

alter database <yourdb> set percolate.sql_policy = 'registered';

Statements are then refused, and so is plain SQL in a p8ql: step: sql: at authoring, p8ql: at execution, both from 0.1.6 onward. Before that release the policy refused sql: only, because a p8ql: step is not a statement in the compiled spec. select * from percolate_build() says which you are running. The dialect modes are not refused: p8ql is itself a registered function, so a SEARCH or TEXT step still runs as the owner under either policy. Calling workflow.p8ql() directly is unaffected: outside a step you are the invoker, so it runs as you, under RLS.

A registered function (sql: {function: …}, scenario 7) narrows what a step can ask to something a person reviewed. It still runs as the owner, so a function meant to serve any caller has to scope itself to the run's owner.

7. Fan out over a query result

What we are trying to do here is turn one authored step into one task per vessel with an open deficiency, without deciding the width outside the database.

  - id: fan
    matrix:
      max_fanout: 50
      rows: {function: deficient_vessels}
      template:
        p8ql: 'LOOKUP "{{item.vessel}}"'

workflow.tasks — two vessels had open deficiencies

 step_key |  kind  |  status   |   worker
----------+--------+-----------+-------------
 fan      | matrix | succeeded | in-database
 fan[0]   | sql    | succeeded | in-database
 fan[1]   | sql    | succeeded | in-database
 report   | sql    | succeeded | in-database

each child carries its own row under {{item}}

    vessel     |  code   | resolved
---------------+---------+-----------
 Bulk Harmony  | PSC-441 | canonical
 Meridian Dawn | PSC-441 | canonical

Why it works — the expansion is a transaction, and the ceiling is mandatory

The children are inserted by the statement that completes the parent, so nothing outside the database decides the width and there is no window where the parent is succeeded and the children do not exist.

rows: takes the SELECT that decides the width, or a registered function where you want the operation blessed. Either way it runs read-only: the row source is re-run by every retry of the expansion, so a query that mutated while deciding how many children to make would have no good reading.

Leave out max_fanout and the compiler refuses the document with the reason:

define_yaml

ERROR: workflow YAML did not compile: matrix step 'fan' declares no
`max_fanout`. A matrix needs a ceiling: a cross join with a forgotten WHERE
expands to the cartesian product, and that should fail this step rather than
the database.

The rows live on the children rather than on the parent, so five thousand of them do not have to fit in one payload — which is also why a fan-in reads a handle rather than a value.

8. Wait for a person, or for a clock

A detention decision belongs to the harbourmaster, and the run should sit there until they make it.

What we are trying to do here is hold a run open through a cooling-off period and then a human decision, with no process doing the waiting.

  - id: cooling_off
    needs: [notice]
    timer: 3600
  - id: approve
    needs: [cooling_off]
    signal: true

immediately after start_workflow — the timer is holding it

  step_key   |  kind  |  status   | still_waiting
-------------+--------+-----------+---------------
 approve     | signal | pending   | f
 cooling_off | timer  | ready     | t
 notice      | sql    | succeeded | f

select workflow.signal_task(
    (select run_id from workflow.tasks
      where step_key = 'approve' and status = 'waiting_external'
      order by created_at desc limit 1),
    'approve', '{"decision":"released","by":"harbourmaster"}'::jsonb);

The subquery finds the newest run waiting on approve. From psql it needs the identity claim that your first workflow sets, because signal_task checks who is signing and refuses a session that carries nobody.

after the signal

  step_key   |  kind  |  status
-------------+--------+-----------
 approve     | signal | succeeded
 cooling_off | timer  | succeeded
 notice      | sql    | succeeded
 release     | sql    | succeeded

Why it works — `waiting_external` is the status that means a person is the dependency

workflow.promote_due_timers() is what a clock calls, and after it runs the signal step moves to waiting_external. The only thing that moves it from there is signal_task.

That function checks who you are, and a run with no owner is not owned by everyone. It takes an explicit permission, so the run has to have been started by somebody for the harbourmaster to be able to sign it off — which is also why a scheduled run carries the schedule's owner rather than being unowned.

9. Undo the steps that already worked

A berth is booked, a pilot is booked, and then the tide window is missed. Two things have to be given back in the reverse of the order they were taken.

What we are trying to do here is roll back the parts of a booking that succeeded, after a later step fails terminally.

  - id: reserve_berth
    p8ql: 'LOOKUP "Rotterdam"'
    compensate_with: release_berth
    saga_group: booking
  - id: book_pilot
    needs: [reserve_berth]
    p8ql: 'LOOKUP "MERI"'
    compensate_with: cancel_pilot
    saga_group: booking

before anything fails — the compensations are not tasks yet

   step_key    |   kind    |  status
---------------+-----------+-----------
 book_pilot    | sql       | succeeded
 confirm_tide  | http_call | ready
 reserve_berth | sql       | succeeded

after begin_compensation

                      step_key                      |   kind    |  status
----------------------------------------------------+-----------+-----------
 book_pilot                                         | sql       | succeeded
 cancel_pilot:1d05408d-98c4-4b96-9d08-cb63dd0b166d  | sql       | succeeded
 confirm_tide                                       | http_call | failed
 release_berth:e765c77e-87e1-4be6-ade7-096c551af57b | sql       | succeeded
 reserve_berth                                      | sql       | succeeded

 status | compensation_state
--------+--------------------
 failed | compensated

Why it works — two columns, because there are two questions

The run ends failed with compensation_state = compensated, and those are separate columns deliberately. A saga that rolled back cleanly still did not do what it was asked, and a run reporting succeeded because its cleanup worked is a run nobody investigates.

Compensations are ordinary tasks, so they inherit retries, backoff and the audit trail — and they stay out of the forward graph, so a compensation cannot accidentally satisfy a forward dependency.

10. An agent is a row

What we are trying to do here is register an agent and the tool server it calls, with no class, no decorator and nothing to deploy.

select agentic.upsert_tool_server($j${
  "name": "harbour-query", "kind": "mcp", "url": "http://query-mcp:8090",
  "emits_citations": true,
  "cached_tools": [{"name": "query"}, {"name": "schema"}]
}$j$::jsonb);

select agentic.upsert_agent($j${
  "name": "harbourmaster",
  "model": "anthropic:claude-sonnet-5",
  "system_prompt": "You answer questions about the fleet. Start with SCHEMA…",
  "tools": [{"server": "harbour-query", "tools": ["query"]}]
}$j$::jsonb);

the binding

     agent     |    server     | allowlist
---------------+---------------+-----------
 harbourmaster | harbour-query | ["query"]

Why it works — registration and re-sync are the same call, so correcting a URL cannot throw away discovery

A tool server is a row naming an endpoint, and an agent is a row naming that server plus an allowlist of tools on it. The allowlist is a list: "tool": "query" is stored here without complaint and then dropped by the runtime, leaving the agent with no allowlist at all — which means every tool the server exposes. Re-running the upsert with a corrected URL keeps what discovery found:

tools after re-sync

           tools_after_resync
-----------------------------------------
 [{"name": "query"}, {"name": "schema"}]

Sessions and runs are rows too, which is what makes a conversation something you can query rather than something in a log:

agentic.sessions

 status  | trigger_kind |           title
---------+--------------+----------------------------
 running | interactive  | Aurora Kestrel PSC history

Registering the agent and opening the session are two different actors, and the examples run them that way. Registering needs agents:update, which a migration has and a person usually does not; opening a session needs a user, because agentic.sessions.user_id is not nullable.

Running all of it

The fixture is samples/harbour in get-percolate, and the ten scripts are the blocks on this page — there is nothing else to fetch.

What we are trying to do here is get back to a known state and run any subset of the ten against it.

export P8_ADMIN_DSN=postgres://p8:p8@localhost:5432/percolate
percolate sample load samples/harbour --as-email you@example.com

Then paste any example above into psql. The ones that read tenanted rows — 2, 3, 4 and the graph algorithms page — need the claims wrapper from scenario 4 around them, or they answer as the superuser and quietly show you both tenants.

Why it works — the loader is idempotent, so re-running it is the reset

Re-running the loader is how you get back to a known state: it upserts, so a second run leaves the same 11 nodes, 9 edges and 14 keys rather than doubling them. Each example that changes anything runs in its own transaction and rolls back, which is what makes them safe to run in any order and as many times as you like.

If you want to go deeper on any one of these, the P8QL grammar and the workflow grammar are the references, querying covers the dialect as prose, and uploading files covers what happens between POST /files and a chunk that answers a question.

And if you now want these ten assembled into things worth deploying — a source polled on a clock, a backlog through a specialist extractor, a question answered by an agent over your own corpus — that is workflow recipes.