ParaQL Language Reference

This page is the definitive syntax reference for ParaQL. Every clause, operator, function, and special statement is documented here.

Basic SELECTCopy Anchor Link

SELECT <columns>
FROM <source>
[WHERE <conditions>]
[GROUP BY <columns>]
[HAVING <conditions>]
[ORDER BY <column> [ASC|DESC]]
[LIMIT <n>]
[OFFSET <n>]

SELECT ColumnsCopy Anchor Link

raw.* paths and the raw_data column read the CI's verbatim source payload, which can contain secrets, so they require the raw_data:read permission anywhere in a query, WHERE and ORDER BY included. Without it the query is rejected with raw_data access requires the raw_data:read permission. fqdn_sources (FQDN provenance) needs no extra permission: you can select, filter and order by it by name, although SELECT * does not include it. See Access Control for who holds raw_data:read.

DISTINCTCopy Anchor Link

SELECT DISTINCT namespace
FROM kubernetes.pod

Removes duplicate rows from the result set.

FROM ClauseCopy Anchor Link

CI TypesCopy Anchor Link

Query a specific CI type using the source.type format:

FROM kubernetes.pod
FROM openstack.instance
FROM ceph.osd

Use SHOW TABLES to list all available CI types.

Some registered types are subordinate records rather than CIs: a Kubernetes container belongs to its pod, a ReplicaSet to its Deployment, a Ceph placement group to its pool, a Tenable vulnerability to its asset. These records have no CI of their own, so SHOW TABLES leaves them out, and FROM kubernetes.replicaset or DESCRIBE kubernetes.container returns a validation error that names the parent type to query instead.

Wildcard (All Types)Copy Anchor Link

FROM *

Queries across all CI types. Useful for cross-system searches. Note that this carries a higher complexity cost.

Special TablesCopy Anchor Link

SELECT ci_name, change_type, changed_at
FROM changes
WHERE changed_at > NOW() - INTERVAL '24 hours'
ORDER BY changed_at DESC

Change history needs the changes:read permission, the same one the Changes page and the changes API need. FROM changes, DIFF, AS OF (which replays change history) and change_count() all read it, so without that permission the query is rejected with field 'changes' reads changes; requires the changes:read permission (the field names DIFF, AS OF or change_count for those). DESCRIBE changes lists the columns and needs no extra permission. Users who can read configuration items hold changes:read; an API key needs it granted. See Access Control.

A TAG filter on changes tests the tags of the CI each change is about. TAG is not supported on relationships: a relationship row has two CIs, and the filter used to test whether any CI in the estate carried the tag. Filter a traversal instead.

Data scopes apply to every query form. If your access is limited to some sources or scopes, a query sees only the CIs inside them. That covers each UNION ALL branch, every step of a traversal, the IMPACT walk, the LINEAGE and AS OF seeds, and the CI behind each change and relationship row. A relationship row is visible only when you can see the CIs at both ends. A traversal or IMPACT walk does not pass through a CI you cannot see, so a CI reached only through one is not returned. LINEAGE starts only from a CI you can see and does not walk past a CI you cannot see. It returns no row for such a CI. The result's metadata.hidden_neighbours gives only how many it reached in each direction, as ancestors and descendants. ORPHANS and the relationship counts consider only relationships whose CIs you can see. See Access Control.

Table AliasesCopy Anchor Link

SELECT p.name, p.config.phase
FROM kubernetes.pod p
WHERE p.config.phase = 'Running'

WHERE ClauseCopy Anchor Link

Comparison OperatorsCopy Anchor Link

Pattern MatchingCopy Anchor Link

Set OperatorsCopy Anchor Link

WHERE config.phase IN ('Pending', 'Failed', 'Unknown')
WHERE source NOT IN ('kubernetes', 'ceph')

RangeCopy Anchor Link

WHERE changed_at BETWEEN NOW() - INTERVAL '48 hours' AND NOW()
WHERE config.vcpus BETWEEN 2 AND 8

NULL ChecksCopy Anchor Link

WHERE namespace IS NULL
WHERE criticality IS NOT NULL

StalenessCopy Anchor Link

Check if a CI hasn't been seen recently:

WHERE last_seen IS STALE BY INTERVAL '2 hours'

Logical OperatorsCopy Anchor Link

Combine conditions with AND, OR, and NOT. Use parentheses for grouping:

WHERE (config.phase = 'Running' OR config.phase = 'Succeeded')
  AND namespace != 'kube-system'
  AND NOT name LIKE 'test-%'

UUID ValuesCopy Anchor Link

A column that holds UUIDs, such as ci_id, compares only with a UUID. WHERE ci_id = 'web-01' returns an error ('ci_id' holds UUIDs, and 'web-01' is not a UUID) instead of an empty result. A valid UUID matches whatever its letter case. LINEAGE, DIFF and AS OF apply the same rule with their own error message.

Automatic Active FilterCopy Anchor Link

ParaQL automatically adds status = 'active' to CI queries unless you explicitly reference status in your WHERE clause. This means:

-- Implicitly filtered to active CIs only
SELECT name FROM kubernetes.pod

-- No auto-filter because status is referenced
SELECT name, status FROM kubernetes.pod WHERE status = 'inactive'

Column NamespacesCopy Anchor Link

Every CI has three data namespaces:

Use DESCRIBE kubernetes.pod to see all available columns for a CI type, grouped by namespace.

AggregationCopy Anchor Link

GROUP BYCopy Anchor Link

SELECT source, COUNT(*) AS count
FROM *
WHERE status = 'active'
GROUP BY source
ORDER BY count DESC

Aggregate FunctionsCopy Anchor Link

HAVINGCopy Anchor Link

Filter aggregated results (applied after GROUP BY):

SELECT namespace, COUNT(*) AS pod_count
FROM kubernetes.pod
GROUP BY namespace
HAVING COUNT(*) > 5
ORDER BY pod_count DESC

Ordering and PaginationCopy Anchor Link

ORDER BY name ASC                    -- ascending (default)
ORDER BY criticality_score DESC      -- descending
ORDER BY namespace, name             -- multi-column sort
LIMIT 50                             -- max rows returned
OFFSET 20                            -- skip first N rows

When no LIMIT is specified, ParaQL applies a default limit of 1000 rows to prevent accidental full-table scans.

LIMIT and OFFSET accept values up to 9223372036854775807, the largest signed 64-bit integer. A larger value returns an error such as LIMIT is too large: the largest allowed value is 9223372036854775807.

When more rows matched than a result returns, the query console says so above the results. Without a LIMIT of your own, a warning names the row limit and suggests a fix: "More than 1,000 rows matched; showing 1,000. Add a LIMIT or narrow the query." With your own LIMIT, a quieter note says only "More rows matched; showing 50 rows."

Scalar FunctionsCopy Anchor Link

Computed Virtual ColumnsCopy Anchor Link

These are ParaQL-specific functions that compute derived values from CI metadata:

SELECT name, ci_type,
       age() AS ci_age,
       stale_for() AS staleness,
       change_count(INTERVAL '7 days') AS weekly_changes,
       relationship_count() AS rels
FROM *
WHERE status = 'active'
ORDER BY weekly_changes DESC
LIMIT 20

Tip: Always alias computed columns (e.g., age() AS ci_age) for readable column headers in results.

ArithmeticCopy Anchor Link

Standard arithmetic operators work on numeric columns:

SELECT name,
       config.vcpus,
       config.ram_mb / 1024 AS ram_gb,
       config.vcpus * 2 AS double_vcpus
FROM openstack.instance
WHERE status = 'active'
ORDER BY config.vcpus DESC

Supported operators: +, -, *, /, % (modulo).

IntervalsCopy Anchor Link

Time intervals are used with temporal functions and comparisons:

INTERVAL '7 days'
INTERVAL '24 hours'
INTERVAL '30 minutes'
INTERVAL '2 hours'
NOW() - INTERVAL '7 days'

Relationship TraversalCopy Anchor Link

Walk the infrastructure graph using arrow syntax:

SELECT dep.name, dep.ci_type,
       pod.name, pod.ci_type
FROM kubernetes.deployment dep
  -[manages]-> kubernetes.pod pod

Arrow SyntaxCopy Anchor Link

Multi-Hop TraversalCopy Anchor Link

Chain multiple hops to walk deep into the infrastructure stack:

SELECT node.name, node.ci_type,
       vm.name, vm.ci_type,
       os.name, os.ci_type
FROM proxmox.node node
  <-[runs_on]- proxmox.vm vm
  <-[runs_on]- os.linux os

Wildcard TargetsCopy Anchor Link

Match any CI type at the other end:

SELECT src.name, src.ci_type,
       tgt.name, tgt.ci_type
FROM kubernetes.deployment src
  -[*]-> * tgt
LIMIT 30

Variable-Depth TraversalCopy Anchor Link

Explore relationships up to N hops deep:

SELECT src.name, src.ci_type,
       tgt.name, tgt.ci_type
FROM proxmox.node src
  -[*1..3]-> * tgt
LIMIT 50

Maximum depth is 10 hops. Cycle detection prevents infinite loops.

Filtering Traversal ResultsCopy Anchor Link

Apply WHERE conditions to any aliased table in the traversal:

SELECT dep.name, dep.ci_type,
       pod.name, pod.ci_type
FROM kubernetes.deployment dep
  -[manages]-> kubernetes.pod pod
WHERE pod.config.phase = 'Running'

SELECT * on a traversal returns every column of every alias, labelled alias.column. Every type table has its own created_at and updated_at, and some have a name, status, path or depth. When a type-specific column shares its name with another column of the same alias, it is labelled alias.config.column. You can select it by that name.

A comparison between values of incompatible types, such as tgt.path_names = tgt.name (a list of names against one name), is rejected with an error that names the types. The same applies in IMPACT and every other query form.

LINEAGE (Stack Traversal)Copy Anchor Link

Discover the full infrastructure stack above and below a CI, from physical hardware up through virtualization to application containers:

SELECT name, ci_type, tier, depth, direction, ci_id, status
FROM LINEAGE(kubernetes.pod, name LIKE '%api%')

These are the only columns a LINEAGE query can use in SELECT, WHERE, and ORDER BY; any other column returns an error that lists them. There is no relationship_type column: the relationship behind each hop is in the edges of the lineage metadata described below.

A SELECT item can take an alias, and ORDER BY can sort by it; WHERE uses the column name. Using one alias twice, or an alias that is the name of a different LINEAGE column (SELECT name AS tier), returns an error. GROUP BY, HAVING, aggregate functions, and DISTINCT are not supported, and each SELECT item and ORDER BY term must be a column, not a function call or an expression.

WHERE can only compare columns with literals or with other columns. Parentheses around a column or a literal are ignored, and a negative number such as -1 is read as a literal. Arithmetic, a negated column (-depth), INTERVAL literals, function calls, CAST, IS STALE, CONTAINS, and TAG return an error. Literals are read as the compared column's type: depth and tier_index are numbers, ci_id takes a UUID and matches it in any letter case, and the other columns are text. A literal that does not fit its column returns an error. Comparing two columns of different types also returns an error, in a plain comparison such as depth = name as well as in IN and BETWEEN. ci_id is text in LINEAGE rows, so ci_id = name is allowed.

LINEAGE walks from one seed CI: the first active CI of the type, in ci_id order, that matches the seed filter. Naming status in the seed filter or in the outer WHERE lifts the active-only default, so LINEAGE(proxmox.vm, status = 'inactive') can start from an inactive CI.

LINEAGE traverses by each relationship's direction classification. Every relationship type is classified as upward, downward, lateral, or non-lineage (graph-only). The ancestors and descendants walks follow the upward and downward edges; lateral edges (equivalence links such as correlations) are followed in both directions. Tier ordering does not gate traversal; it only labels each returned node with its stack layer. (IMPACT has always worked this way; it never enforced tier ordering either.)

-- Lineage for a specific node
SELECT name, ci_type, tier, depth, direction, ci_id, status
FROM LINEAGE(proxmox.node)

Lineage MetadataCopy Anchor Link

When a LINEAGE query is returned whole, the response carries a lineage_metadata object alongside the result rows. The query console uses it to render the lineage as a hierarchy tree instead of a flat table; the rows themselves are unchanged.

lineage_metadata is attached only when the LINEAGE query is unmodified enough to reassemble into a tree. It is omitted (and the console falls back to the plain results table) when the query:

• adds an outer WHERE, LIMIT, or OFFSET (an ORDER BY only reorders the rows, so it is allowed)
• projects away any of the columns the tree needs to render (every lineage column except tier_index and source must survive the projection)
• returns more than 1000 flattened rows

When a walk is truncated, the tree shows a banner for that direction so the result is not mistaken for a complete stack.

IMPACT (Blast Radius)Copy Anchor Link

Determine what would be affected if a CI goes down:

SELECT name, ci_type, depth, path, criticality, criticality_score
FROM IMPACT(proxmox.node, name LIKE '%pve%')
ORDER BY depth, criticality_score DESC

Impact analysis follows all relationships outward from the seed CI, building a dependency tree of everything that would break.

IMPACT rows carry only ci_id, name, ci_type, depth, path, path_names, path_types, tier, criticality and criticality_score, which can also be written impact.name and so on. Any other column, and computed functions such as age(), return an error in the outer query. The seed filter inside IMPACT(...) can use any CI column.

IMPACT seeds from active CIs unless the seed filter names status, so IMPACT(proxmox.node, status = 'inactive') starts from inactive CIs. The walk from the seed follows active CIs and relationships only.

-- Storage failure: what depends on this pool?
SELECT name, ci_type, depth, path, criticality, criticality_score
FROM IMPACT(ceph.pool)
ORDER BY criticality_score DESC
LIMIT 50

ORPHANSCopy Anchor Link

Find CIs with no relationships (potential configuration drift or missing discovery):

SELECT name, ci_type, source, age() AS ci_age, last_seen
FROM ORPHANS()
WHERE status = 'active'
ORDER BY ci_age DESC
LIMIT 30

You can optionally filter orphans to a specific CI type:

SELECT COUNT(*) AS orphaned_pods
FROM ORPHANS(kubernetes.pod)

DIFF (Temporal Comparison)Copy Anchor Link

Compare infrastructure state between two points in time:

DIFF kubernetes.pod
BETWEEN NOW() - INTERVAL '7 days' AND NOW()

Results include three categories:

The two times cannot reference a column: use a timestamp literal or an expression over NOW(). The AS OF time follows the same rule.

DIFF supports WHERE, ORDER BY, LIMIT, and OFFSET over its result columns: ci_id, name, diff_type, field, before_value, after_value, and change_count. Any other column returns an error that lists them. The LINEAGE WHERE rules apply: WHERE can only compare columns with literals or with other columns, the same operands are rejected, and literals are read as the column's type. change_count is a number, ci_id takes a UUID and matches it in any letter case, and the other columns are text. Comparing two columns of different types returns an error, so change_count = name and ci_id = name are rejected. ORDER BY can only name columns:

DIFF openstack.instance
BETWEEN NOW() - INTERVAL '30 days' AND NOW()
ORDER BY name
LIMIT 100

AS OF (Point-in-Time Snapshot)Copy Anchor Link

View a CI type as it existed at a specific point in the past:

SELECT name, config.replicas
FROM kubernetes.deployment
AS OF NOW() - INTERVAL '7 days'
WHERE config.replicas = 3

AS OF loads each CI's current row and replays its change history backwards to the timestamp, so every value it returns is either recorded history or a value that has not changed since. The exceptions are listed under Known limits: keyed-list elements restored from their summary, JSONB columns that were empty before their first population, changes deeper than two nested levels, and source before its history begins. It needs a plain FROM <source>.<type> reference: FROM *, the changes and relationships tables, traversals, ORPHANS, LINEAGE and IMPACT are not supported, and neither are GROUP BY, HAVING, DISTINCT, any select item that is not a plain column reference, or any function or cast anywhere in WHERE.

Only simple predicates are evaluated. In WHERE, AS OF accepts plain comparisons, IN, BETWEEN, LIKE/ILIKE, IS [NOT] NULL, NOT, AND/OR and TRUE/FALSE as bare predicates. Parentheses around a column or a literal are ignored, and a negative number such as -1 is read as a literal. A comparison's literal must match the compared column's type, so name = TRUE is an error. IS STALE, CONTAINS and TAG filters are rejected, as are a bare literal, a bare column reference or bare arithmetic used as a predicate, and an arithmetic expression, an INTERVAL literal or a negated column (-config.replicas) used as an operand. An alias used in ORDER BY that shadows a column of the same name referenced elsewhere in the query is rejected, as are duplicate output column names.

Columns without history are rejected. Change detection records name, namespace, status and source on every CI plus the tracked columns of its type table. Those and the identity columns ci_id and ci_type (which never change) are the only references AS OF accepts in SELECT, WHERE and ORDER BY. source is recorded from September 2026 onward and is rewound like the others; changes made before then were not recorded (see Known limits). last_seen, scope_id, scope_label, timestamps, ip_addresses, hostnames, fqdns, metrics.*, raw.*, raw_data, the excluded content columns (data, binary_data, cost_last_updated), generated columns and runtime state that change detection does not record fail at query time instead of returning a present-day value labelled as history. Runtime state covers values a collector reports that change detection does not record, such as a deployment's ready_replicas, a container's restart_count, a NetBox prefix's utilization or a network device's interfaces. SELECT * expands to name, ci_type, source, namespace, status and every config.<column> that has history.

Only the columns you ask for need history. A size placeholder or a nested path the reader cannot place only matters for a column the query selects, filters or sorts on, and makes that column unknown for that CI. Change detection stores a value as a size placeholder when it is an object with more than 5 keys or a list with more than 3 items. A record with a key the reader cannot map to a column makes every column except ci_id and ci_type unknown for that CI. A record with an unknown change type leaves the CI out whatever the query references.

WHERE has three outcomes. Each CI's WHERE is evaluated on its rewound values:

• False on columns that could be rewound. The CI is not a match and is not reported, even when another condition reads an unknown column. For example, status = 'active' AND config.replicas = 3 is false for a CI that was inactive at the target, even when its config.replicas history cannot be rewound.
• Depends on an unknown column. The CI is left out and reported.
• True. The CI is returned, unless a column it selects or sorts on is unknown; then it is left out and reported.

Every CI left out is reported in the response's as_of field; AS OF never returns a present-day value in its place.

Data scopes apply before reconstruction. AS OF returns only CIs you can see now; access is decided on each CI's current source and scope, even though the source it returns is rewound.

Bounded work. One AS OF query examines at most the configured row limit of CIs (10,000 by default) in ci_id order, and reads at most a configured number of change records (100,000 by default); a CI whose history does not fit is left out and reported.

Naming a CI. A ci_id = '<uuid>' or ci_id IN (...) condition joined to the rest of the WHERE by AND narrows the candidates before that limit applies, so AS OF evaluates only the named CIs, and a type with more CIs than one query examines does not make the answer incomplete. The same condition under OR or NOT does not narrow the candidates.

The as_of response field. complete is true when every candidate CI was evaluated; otherwise seed_budget_exhausted, history_budget_excluded (which also counts history that changed while it was being read) and unreconstructable_excluded say why, and excluded_ci_ids lists up to 20 of the left-out CIs. truncated keeps its usual meaning: more matching rows existed than the LIMIT returned. When complete is false, the query console shows a notice above the results, in every view, saying that matching CIs may be missing and why: "Matching CIs may be missing from these results because this query does not evaluate every CI at the requested time", followed by the reasons.

Typed comparisons. Literals are read as the compared column's type: timestamps and dates as ISO 8601 (a timestamp without a zone is UTC), UUIDs (in any letter case), numbers and true/false. A literal that does not fit its column is an error. So is comparing two columns of different types, such as config.replicas = name, in a plain comparison, IN or BETWEEN; number columns of any kind compare with each other.

Known limits. A keyed-list element (Proxmox NICs, disks, cluster members) removed after the target is restored from its recorded summary, which omits sub-fields that were null or false and volatile flags such as online. A JSONB column that was empty before its first population rewinds to an empty object. A runtime value that a collector assembles in a way the platform's static check cannot see may still return its present-day value. Changes deeper than two nested levels, hard-deleted CIs (their history is deleted with them) and generated columns derived from a rewound column are also outside what the history records. source changes are recorded from September 2026 onward, with no backfill: for a time before a CI's first recorded source change, AS OF returns the value that change replaced, and for a CI with no recorded source change it returns the present value.

Schema IntrospectionCopy Anchor Link

EXPLAINCopy Anchor Link

Preview the execution plan without running the query:

EXPLAIN SELECT name, config.phase
FROM kubernetes.pod
WHERE config.phase = 'Running'

The explain view shows the query execution plan, complexity score, and complexity breakdown. Useful for understanding query cost and optimizing performance.

Complexity SystemCopy Anchor Link

Every query is scored for complexity before execution. This protects the database from accidentally expensive queries.

Factors that increase complexity:

• FROM * (wildcard across all types)
• Relationship traversal (per hop)
• Variable-depth traversal
• IMPACT, LINEAGE, ORPHANS
• DIFF, AS OF
• Missing LIMIT clause
• Leading-wildcard ILIKE (ILIKE '%term%')

Use EXPLAIN to inspect the complexity breakdown of any query.