Python

You author Particles as ordinary Python functions and mark each with the SQL concept it exposes. The decorators live in particledb:

from particledb import scalar, aggregate, window, cast

Your type hints are the SQL signature (see the type mapping below). Every example on this page is drawn from the in-repo example modules (examples/particles/) and the end-to-end regression tests, so the SQL results shown are exactly what the engine returns.

The synchronous kinds

Decorator	SQL concept	Shape
@scalar	A per-row function	one value in → one value out, per row
@aggregate	A group fold (GROUP BY)	the group’s values (a list) → one value per group
@window	A window function (OVER (...))	the frame’s values (a list) → one value per row
@cast	A user-defined cast (CAST / ::)	one value of the source type → one value of the target type

The decorators are intentionally transparent: they attach a small metadata attribute and return your function unchanged, so the module stays ordinary Python you can import and unit-test. Nothing in the decorator talks to the database.

Type mapping

Your Python type hints map directly to SQL types — this is the single table the collector uses to build each Particle’s SQL signature:

Python hint	SQL type
bool	boolean
int	bigint
float	double precision
str	text
bytes	bytea
Decimal	numeric
datetime	timestamp
date	date
list[T]	T[] (array of the element type)

Every parameter and the return value must have a type hint — the hint is the SQL type. A missing or unsupported hint is a clear, source-located error at discovery time, so you fix your module rather than the database.

@scalar — a per-row function

A @scalar runs once per row and returns one value per row.

text.py

from particledb import scalar

@scalar
def domain_of(email: str) -> str:
    """The domain part of an email address."""
    return email.split("@")[-1]

@scalar
def slugify(title: str) -> str:
    """A URL-safe slug: lowercase, non-alphanumerics collapsed to dashes."""
    import re
    s = re.sub(r"[^a-z0-9]+", "-", title.lower())
    return s.strip("-")

str -> str maps to text -> text. Once loaded (see Loading):

SELECT domain_of('kyle@jepsen.io');   -- 'jepsen.io'
SELECT slugify('Hello, World!');      -- 'hello-world'

-- Over a table column:
SELECT domain_of(email) FROM users;

Type hints drive marshaling in both directions — an int -> int Particle maps to bigint -> bigint:

@scalar
def add_sq(a: int, b: int) -> int:
    return a * a + b * b

SELECT add_sq(3, 4);   -- 25

@aggregate — a group fold

An @aggregate receives every non-NULL value in the group as a Python list and returns one value per group. Declare the parameter as list[float] (→ double precision[]):

stats.py

from particledb import aggregate

@aggregate
def pmedian(values: list[float]) -> float:
    """Exact median of the group's non-NULL values."""
    xs = sorted(values)
    n = len(xs)
    if n == 0:
        return None
    mid = n // 2
    if n % 2 == 1:
        return float(xs[mid])
    return (xs[mid - 1] + xs[mid]) / 2.0

Why pmedian, not median

MEDIAN is a built-in aggregate, and a Particle whose name collides with a built-in is rejected at deploy time (SQLSTATE 42723). pmedian is the same median under a non-colliding name — see Collisions & Error Handling.

Call it like any aggregate:

-- Given rows: (g=1: 1,3,2)  (g=2: 10,20,30,40)
SELECT g, pmedian(x) FROM t GROUP BY g;
-- g | pmedian
-- --+--------
-- 1 |   2       -- median(1,2,3)
-- 2 |  25       -- median(10,20,30,40) = (20+30)/2

-- Ungrouped folds the whole table:
SELECT pmedian(x) FROM t;   -- median(1,3,5) = 3

An all-NULL or empty group returns NULL without calling the body (STRICT — see null handling).

@window — a window function

A @window receives the frame’s values as a Python list and returns a list of the same length — one value per input row of the frame. Declare it list[float] -> list[float]:

stats.py

from particledb import window

@window
def zscore(values: list[float]) -> list[float]:
    """Per-row z-score within the window frame."""
    n = len(values)
    if n == 0:
        return []
    mean = sum(values) / n
    var = sum((x - mean) ** 2 for x in values) / n
    sd = var ** 0.5
    if sd == 0.0:
        return [0.0 for _ in values]
    return [(x - mean) / sd for x in values]

Call it with an OVER (...) clause:

-- Partition g=1: [10,20,30]  -> z-scores [-1.2247…, 0, 1.2247…]
-- Partition g=2: [5,5]       -> [0, 0]  (constant partition, sd = 0)
SELECT g, x, zscore(x) OVER (PARTITION BY g ORDER BY x) AS z
FROM t
ORDER BY g, x;

@cast — a user-defined conversion

A @cast is a one-argument function (source) -> target that the engine invokes whenever it converts a value of the source SQL type to the target — both an explicit CAST(x AS target) / x::target and an implicit coercion (for example, writing a value into a column of the target type). It’s how you teach the database to parse or convert a type pair your way.

casts.py

from particledb import cast

@cast
def human_to_int(s: str) -> int:
    """Parse a human-friendly number ('1.2k', '3m', ' 42 ') into an integer."""
    s = s.strip().lower()
    mult = 1
    if s.endswith("k"):
        mult, s = 1_000, s[:-1]
    elif s.endswith("m"):
        mult, s = 1_000_000, s[:-1]
    return int(round(float(s) * mult))

The single parameter’s type is the source and the return type is the target, so this is a text -> int cast. Load it (see Loading) and every text → integer conversion runs it:

SELECT '1.2k'::int;              -- 1200
SELECT '3m'::int;                -- 3000000
SELECT '42'::int;                -- 42
SELECT CAST(' 2.5k ' AS int);    -- 2500

-- Fires per row on a column read, too:
SELECT id, raw::int AS parsed FROM amounts;   -- '12k' → 12000, '1.5m' → 1500000

A @cast takes precedence for its type pair

A registered @cast overrides the built-in conversion for that exact (source, target) pair — here, every text → integer cast now runs human_to_int. STRICT-null still applies: CAST(NULL AS int) is NULL and the body is never called. Scope a @cast to a pair you genuinely want to customize.

Asynchronous: @agent & @workflow

Preview

The async tier runs behind a feature flag with a mock backend today — see Async Agents & Workflows for status. The decorators and model are stable, so modules you write now stay forward-compatible.

The kinds above are all synchronous — they run inside the query. When a Particle instead needs to call an LLM, hit an external API, or run a multi-step process, it must be asynchronous: that work takes seconds to minutes and has to be durable and retry-safe, so it can’t block a query. Author it as an async kind:

triage.py

from particledb import agent, workflow

@agent
def triage_ticket(subject: str, body: str) -> str:
    """One bounded AI / external step — classify a support ticket with your model."""
    ...                          # your LLM / API / tool call
    return category

@workflow
def resolve_ticket(ticket_id: str) -> str:
    """A durable multi-step process — each step is retried independently, and the
    whole run survives a restart."""
    # express the steps in Python (classify -> draft -> send); the deploy tool
    # lowers them to a durable multi-step definition.
    ...

An async Particle is submitted, not called inline — it returns a run_id immediately and runs durably in the background. You then track it by run_id. Both the invocation verbs and the status-tracking functions live in Async Agents & Workflows.

Declaring properties

Both the bare form (@scalar) and the called form work. The called form lets you override the SQL name and declare execution properties:

@scalar("email_domain")                 # explicit SQL name override
def domain_of(email: str) -> str:
    return email.split("@")[-1]

@scalar(volatility="stable")            # default is "immutable"
def now_bucket(ts: datetime) -> date:
    return ts.date()

@scalar(called_on_null=True)            # opt out of STRICT — body sees NULL
def coalesce_blank(s: str) -> str:
    return s or ""

Defaults (PostgreSQL-faithful): volatility="immutable", STRICT null handling, parallel_safe=True. Capabilities (network / secrets / filesystem) are deny-by-default — see Best Practices.

Testing your Particles

Because the decorators return your function unchanged, a Particle module is ordinary Python you can import and test directly — no database needed:

from text import domain_of, slugify

def test_domain_of():
    assert domain_of("kyle@jepsen.io") == "jepsen.io"

def test_slugify():
    assert slugify("Hello, World!") == "hello-world"

Next steps

• Best Practices — naming, determinism, and safety.
• Collisions & Error Handling — the built-in name rule and null/error semantics.
• Loading — get these functions into a running server.