# System relations

> The read-only system relations a NYXDB node registers, grouped by area, with the permission each read needs and the purpose where verified.

System relations are read-only `system.<name>` relations maintained by the
engine. The list below gives every relation with the permission its read needs
and its purpose.

## How to read them

- Use an ordinary `SELECT`, over HTTP or a WebSocket session.
- Each read needs the listed administrative permission. For several areas an
  older manage permission (for example `credential_manage` for
  `system.credentials`) still admits the read. Relations marked — have no
  administrative read action.
- Rows carry observation fields such as `observed_at_millis` and
  `freshness_millis`.
- A one-shot read may project columns, filter, order and limit.
- `STREAM SELECT … FROM system.<name>` streams a relation, with filters and
  projections.

```sql
SELECT *
FROM system.query_history;
```

```sql
SELECT
  row_id,
  source_relation_id,
  derived_relation_id,
  target_relation_id,
  transformation_revision
FROM system.derived_lineage;
```

```sql
SELECT
  name,
  source_position,
  rows_written,
  completed_rows,
  transformation_revision
FROM system.transformation_revisions;
```

`system.query_history` holds the SQL text as executed, so grant
`telemetry_read` only to principals that may see every statement. Credential
statements are the exception: history holds only their redacted form.

## Catalog and tables

| Relation | Read permission | Purpose |
| --- | --- | --- |
| `system.catalog_relations` | — |  |
| `system.tables` | — | One row per table, with `relation_id` and `name`. `SHOW TABLES` reads it. |
| `system.table_columns` | — | The columns of each table. `DESCRIBE` reads it. |
| `system.table_keys` | — |  |
| `system.table_attribute_groups` | — |  |
| `system.table_indexes` | — |  |
| `system.table_index_builds` | — |  |
| `system.table_partitions` | — |  |
| `system.table_projections` | — |  |
| `system.table_storage_plans` | — |  |
| `system.table_column_codecs` | — |  |
| `system.table_policy_bindings` | — |  |
| `system.table_identities` | — |  |
| `system.table_tablets` | — |  |

## Views, transformations and lineage

| Relation | Read permission | Purpose |
| --- | --- | --- |
| `system.views` | `derived_state_read` |  |
| `system.materialized_views` | `derived_state_read` |  |
| `system.transformations` | `derived_state_read` | One row per transformation, with `derived_relation_id` and its declared `name`. |
| `system.transformation_revisions` | `derived_state_read` | One row per transformation revision: `name`, `source_position`, `rows_written`, `completed_rows`, `condition`, `health`, `lag_source_sequences`. |
| `system.transformation_frontiers` | `derived_state_read` | Per source tablet of each transformation: the applied `source_sequence`, the retained floor and pending outputs. |
| `system.transformation_validation` | `derived_state_read` |  |
| `system.transformation_maintenance` | `telemetry_read` |  |
| `system.derived_lineage` | `derived_state_read` | One row per declared source of each transformation: source, derived and target relation, and the revision. |
| `system.derived_dependencies` | `derived_state_read` |  |
| `system.derived_authority` | `derived_state_read` |  |
| `system.derived_operations` | `derived_state_read` |  |

## PSI and continuous delivery

| Relation | Read permission | Purpose |
| --- | --- | --- |
| `system.psi_streams` | `arrangement_read` |  |
| `system.psi_arrangements` | `arrangement_read` |  |
| `system.continuous_sources` | `arrangement_read` |  |
| `system.continuous_edge_dead_letters` | `telemetry_read` |  |

## Queries and telemetry

| Relation | Read permission | Purpose |
| --- | --- | --- |
| `system.queries` | `telemetry_read` |  |
| `system.query_history` | `telemetry_read` | Recent queries: request id, principal, kind, state, status, the full SQL text (not redacted), timings and delivered rows and bytes. Held in process memory, 4,096 entries by default (`gateway.observability.query_history_entries`), rebuilt empty at restart. |
| `system.query_stats` | `telemetry_read` | One row per normalized statement digest: executions, failures, cancellations and timing totals. |
| `system.query_traces` | `telemetry_read` |  |
| `system.query_telemetry_rollups` | `telemetry_read` |  |
| `system.query_telemetry_status` | `telemetry_read` |  |
| `system.query_definition_associations` | `telemetry_read` |  |
| `system.telemetry_metrics` | `telemetry_read` |  |
| `system.telemetry_events` | `telemetry_read` |  |
| `system.telemetry_profiles` | `telemetry_read` |  |
| `system.health_signals` | `telemetry_read` |  |

## Settings

| Relation | Read permission | Purpose |
| --- | --- | --- |
| `system.settings` | `telemetry_read` | One row per registered setting: name, scope, value, whether it is the default, its source, type, unit, allowed values and documentation fields. |

## Operations and lifecycle

| Relation | Read permission | Purpose |
| --- | --- | --- |
| `system.operations` | `operation_read` | Administrative operations, kept in a durable journal under the data directory. |
| `system.load_operations` | `telemetry_read` |  |
| `system.relation_ledger` | `telemetry_read` |  |
| `system.relation_retirements` | `telemetry_read` |  |
| `system.bounded_gates` | `telemetry_read` |  |
| `system.bounded_gate_waits` | `telemetry_read` |  |
| `system.wal_reclamation_blockers` | `telemetry_read` |  |
| `system.typed_image_retention` | `telemetry_read` |  |

## Security and governance

| Relation | Read permission | Purpose |
| --- | --- | --- |
| `system.database_principals` | `governance_read` |  |
| `system.roles` | `governance_read` |  |
| `system.role_bindings` | `governance_read` |  |
| `system.grants` | `governance_read` |  |
| `system.administrative_grants` | `governance_read` |  |
| `system.credentials` | `governance_read` | Credentials per principal with their kind and status; no digest, salt or secret. |
| `system.audit_log` | `audit_read` |  |

## Federation and applications

| Relation | Read permission | Purpose |
| --- | --- | --- |
| `system.application_accesses` | `governance_read` | Application accesses on a federation host: status, secret generation and grants. |
| `system.sources` | `telemetry_read` | Federation sources on a consumer: endpoint, credential id, lifecycle (ready, paused, unavailable) and the last status. |
| `system.external_relations` | `telemetry_read` | External relations on a consumer: the host binding, mode and state. |
| `system.application_subscriptions` | `telemetry_read` |  |
| `system.application_subscription_history` | `telemetry_read` |  |
| `system.application_usage_direct` | `telemetry_read` |  |
| `system.application_usage_allocated` | `telemetry_read` |  |
| `system.application_usage_gross` | `telemetry_read` |  |
| `system.application_usage_physical` | `telemetry_read` |  |
| `system.application_direct_usage_history` | `telemetry_read` |  |
| `system.usage_rollups` | `telemetry_read` |  |
| `system.arrangement_usage_history` | `telemetry_read` |  |

## Node, storage and replication

| Relation | Read permission | Purpose |
| --- | --- | --- |
| `system.cluster_nodes` | `telemetry_read` |  |
| `system.node_services` | `telemetry_read` |  |
| `system.node_resources` | `telemetry_read` |  |
| `system.resource_allocations` | `telemetry_read` |  |
| `system.tablets` | `telemetry_read` | Each tablet with its relation, `owner_epoch` and `committed_frontier`. |
| `system.storage` | `storage_read` |  |
| `system.storage_policies` | `storage_read` |  |
| `system.object_pools` | `storage_read` |  |
| `system.index_residency` | `storage_read` |  |
| `system.tiering_moves` | `storage_read` |  |
| `system.replication` | `replication_read` |  |
| `system.replication_groups` | `replication_read` |  |
| `system.replicas` | `replication_read` |  |

The replication, object-pool and tiering relations describe replicas and data
placement across nodes; see [Deployment topologies](/docs/operations/supported-boundaries).

## Lineage and functions

`system.derived_lineage` holds the lineage of every derived relation, and
`SHOW FUNCTIONS` lists the function library; each function is documented in the
[function reference](/docs/functions).

---

Source: https://nyxdb.io/docs/operations/system-tables/