loom.core.sql

Backend-agnostic SQL query subsystem.

Provides an injectable SqlQueryService that executes SQL through named connections with a fail-closed per-request role policy, readonly enforcement and pagination. Concrete backends implement the SqlExecutor port; the first supported backend is ClickHouse (loom.core.sql.clickhouse, optional extra loom-kernel[clickhouse]).

Work done on behalf of a caller uses CallerBoundSql instead, which derives the roles from the verified identity rather than from an argument.

class loom.core.sql.CallerBoundSql(service, config, observability=None)[source]

Bases: object

Executes SQL with the roles of the verified caller, and only those.

Every query is bound to an Identity: the effective roles are the intersection of the roles that identity holds with the connection allowlist. There is deliberately no roles parameter — accepting one would reintroduce the very gap this collaborator closes, since the caller’s entitlements would stop being what decides.

The identity must come from Caller(). That marker is the only thing that makes the executor inject the identity the transport verified. An identity parameter declared without it is an ordinary primitive parameter, bound from the params the calling code supplies: the caller would then choose its own identity, and with it the roles this class derives. On the agent path it is worse still, because those params are tool arguments the model writes. A query bound to a forged identity is unbound.

A connection with an empty allowed_roles cannot be queried this way: its only role is the shared default_role, which is not derived from anyone, so RolesNotBoundError is raised. Use SqlQueryService for that unbound work.

Each accepted query opens one span labelled with the effective roles, the caller subject and the authentication mechanism. Those labels match what the REST endpoint records, but the span is its own: a narrower scope and a different name, so the two are auditable on the same terms rather than being one record. Without an observability runtime there is no span and no other difference.

Parameters:

  • service (SqlQueryService) – Underlying query service applying the connection policy.

  • config (SqlConfig) – Parsed sql: section holding the per-connection allowlists.

  • observability (ObservabilityRuntime | None) – Runtime the audit span is opened on. None disables the span; the auto-bootstrapped app injects the registered runtime.

Example:

from loom.core.identity import Identity
from loom.core.sql import CallerBoundSql, SqlQueryResult
from loom.core.use_case.markers import Caller
from loom.core.use_case.use_case import UseCase


class ListSales(UseCase[object, SqlQueryResult]):
    def __init__(self, sql: CallerBoundSql) -> None:
        self._sql = sql

    async def execute(self, identity: Identity = Caller()) -> SqlQueryResult:
        return await self._sql.execute(
            "SELECT * FROM sales", connection="analytics", identity=identity
        )
async execute(sql, *, connection, identity, parameters=None, limit=None, offset=0)[source]

Execute sql on connection with the roles identity holds.

Parameters:

  • sql (str) – SQL statement with native parameter placeholders.

  • connection (str) – Name of the configured connection to use.

  • identity (Identity) – Verified caller injected by the Caller() marker; the sole source of the effective roles.

  • parameters (Mapping[str, Any] | None) – Values bound server-side by the backend.

  • limit (int | None) – Requested row limit; clamped to the connection max_limit and defaulted to default_limit when absent.

  • offset (int) – Number of rows to skip.

Returns:

The standard tabular result envelope from the executor.

Raises:

  • UnknownConnectionError – When connection is not configured.

  • RolesNotBoundError – When the identity is anonymous, holds no role, or holds none that the connection allowlists. A connection configured without a registered executor is reported this way too when the caller holds no allowlisted role: the roles are checked here, before the service reports the connection as unknown. Both are refusals; only the wording differs.

  • ConfigError – When the application has no sql: section.

Return type:

SqlQueryResult

class loom.core.sql.NullSqlQueryService[source]

Bases: SqlQueryService

Null implementation registered when no sql: section is configured.

Keeps SqlQueryService always resolvable from the container so a use case never hits an opaque resolution error: the first execute call raises an actionable ConfigError instead.

async execute(sql, *, connection, roles=None, parameters=None, limit=None, offset=0)[source]

Always fail: SQL execution requires the sql config section.

Raises:

ConfigError – Always, with instructions to configure the section.

Parameters:
Return type:

SqlQueryResult

class loom.core.sql.SqlQueryService(executors, config)[source]

Bases: object

Executes SQL through named connections under the fail-closed role policy.

Role resolution, readonly enforcement and limit clamping happen here, before the executor is ever touched. A rejected role never reaches the backend.

This is the unbound path: the roles are an argument, so the code writing the call chooses them, bounded only by the connection allowlist. That is correct for system work with no caller — a scheduled job, a migration, a health probe. Work done on behalf of a caller uses CallerBoundSql, which derives the roles from the verified identity and accepts no roles argument.

Parameters:

  • executors (Mapping[str, SqlExecutor]) – Backend executor per connection name.

  • config (SqlConfig) – Parsed sql: section with the named connections.

Example — system work with no caller, running as the connection’s own default_role:

service = SqlQueryService(executors=executors, config=sql_config)
result = await service.execute(
    "SELECT count() FROM sales", connection="analytics"
)
async execute(sql, *, connection, roles=None, parameters=None, limit=None, offset=0)[source]

Execute sql on connection applying the connection policy.

Parameters:

  • sql (str) – SQL statement with native parameter placeholders.

  • roles (Sequence[str] | None) – Caller roles, each validated against the connection allowlist; the query runs with the union of their privileges. Empty or None falls back to the connection default_role. They are not checked against any caller: when the query is run on behalf of one, use CallerBoundSql.

  • connection (str) – Name of the configured connection to use.

  • parameters (Mapping[str, Any] | None) – Values bound server-side by the backend.

  • limit (int | None) – Requested row limit; clamped to the connection max_limit and defaulted to default_limit when absent.

  • offset (int) – Number of rows to skip.

Returns:

The standard tabular result envelope from the executor.

Raises:
Return type:

SqlQueryResult

loom.core.sql.resolve_query_roles(identity, *, connection, roles_bound, allowed_roles, requested_roles)[source]

Resolve the roles one query may use.

Parameters:

  • identity (Identity) – Verified caller published by the authentication middleware.

  • connection (str) – Name of the SQL connection being queried.

  • roles_bound (bool) – Whether the configured authentication mechanism binds roles to the identity. False means the connection declares no binding and is single-role by config.

  • allowed_roles (frozenset[str]) – Connection allowlist, the ceiling of the intersection.

  • requested_roles (Sequence[str] | None) – Roles asked for in the body; they may only narrow.

Returns:

The effective roles for this single request.

Raises:
Return type:

tuple[str, …]