Application access and governance

NYXDB operators can publish typed read queries for direct application execution or subscription. The SQL in this page creates the operator-owned contract; an application client invokes it through the experimental auth and endpoint wire operations documented in Browser and platform clients.

Complete claims-bound example

The provider and endpoint use operator-owned tables. Every table still follows the normal storage-policy requirement.

CREATE TABLE sessions (
  token String,
  user_id UInt64,
  tenant_id UInt64,
  role String,
  expires_at UInt64,
  PRIMARY KEY (token)
) SETTINGS mode='keyed', storage_policy='memory_data';

CREATE TABLE fills (
  tenant_id UInt64,
  symbol String,
  px Float64,
  qty UInt64
) SETTINGS mode='append', storage_policy='memory_data';

CREATE AUTH PROVIDER app_sessions AS
STREAM SELECT token, user_id, tenant_id, role
FROM sessions
WHERE expires_at > to_unix_timestamp(now());

CREATE ENDPOINT live_fills(symbol String)
AUTH CLAIMS (tenant_id UInt64)
USING app_sessions
AS STREAM SELECT symbol, px, qty
FROM fills
WHERE tenant_id = $auth.tenant_id
  AND symbol = $symbol
SETTINGS max_subscribers_per_user=3,
         max_rows_per_s=500,
         max_bytes_per_s=65536,
         max_executions_per_min=60;

The client supplies only symbol. The engine takes tenant_id from the claims row returned for the presented token. Sending another wire parameter cannot replace $auth.tenant_id; it produces an arity error.

Auth providers

An auth provider maps one presented token to a claims row. Provider output names form the claim schema used by endpoints and invocable views.

Return a stable, non-secret user_id from every provider. The engine uses that claim for subscriber attribution and per-user governance. Without it, the presented bearer token becomes the fallback identity and can surface in subscriber or governance telemetry; never use a bearer token as a display identity.

Lookup provider

CREATE AUTH PROVIDER session_lookup AS
SELECT user_id, tenant_id, role
FROM sessions
WHERE token = $token
  AND expires_at > to_unix_timestamp(now())
SETTINGS cache_ttl_ms=5000;

Lookup-provider rules:

• the body is a one-shot SELECT and must reference the reserved $token;
• exactly one matching row authenticates the connection;
• every named output column is a claim; computed outputs require aliases;
• duplicate claim names are rejected;
• cache_ttl_ms defaults to 5,000 ms;
• the claims row is revalidated after its cache age when the connection invokes again; and
• zero rows, multiple rows, an unknown provider, and provider errors all become the same end-user wire error: auth failed.

Streaming auth gate

CREATE AUTH PROVIDER session_gate AS
STREAM SELECT token, user_id, tenant_id, role
FROM sessions
WHERE expires_at > to_unix_timestamp(now());

Streaming-gate rules:

• STREAM SELECT is mandatory for this form;
• one output column must be named token; it is the replica key and is not a claim;
• the remaining named columns are the claims row;
• the body cannot use $token or client parameters;
• cache_ttl_ms is rejected because the live replica is the truth;
• deleting a session, changing the predicate result, or passing a time-based expiry removes the token and revokes every connection it admitted; and
• replay restores the provider and its internal system-class subscription after a persistent restart.

The lookup form remains useful when materializing all valid sessions would be inappropriate. The streaming form provides event-driven revocation.

Changing claim values in a streaming-gate row affects future endpoint or view invocations; it does not rebind an already running subscription. A live subscription freezes its parameter bindings, user_id, tenant, and role for its lifetime. To change any identity or authorization claim safely, remove the old token so its connections are revoked, issue a new token, and require a fresh connection and subscription.

Provider selection and lifecycle

With no registered provider, no application end user can complete OP_AUTH. Creating a claimless endpoint does not create an end-user entry path.

An endpoint with claims must resolve one provider. If exactly one provider is registered, USING may be omitted; the canonical persisted DDL records the resolved provider. When several providers exist, USING provider_name is required.

USING is a definition-time binding and schema-validation reference, not an invocation-affinity check. At runtime, a session authenticated through any provider can invoke the endpoint when it supplies compatible declared claim names and types. Do not treat the provider name as an authorization boundary; encode every tenant and visibility restriction in server-bound claims and the stored query predicate.

SHOW AUTH PROVIDERS;
DROP AUTH PROVIDER session_lookup;

SHOW AUTH PROVIDERS returns name, claims, cache_ttl_ms, source, digest, uuid, version. A streaming provider shows stream in the cache column. CREATE OR REPLACE AUTH PROVIDER is not a current form. A provider referenced by an endpoint cannot be dropped. Dropping a live gate after its dependencies are removed revokes the sessions it admitted.

Endpoints

The general form is:

CREATE [OR REPLACE] ENDPOINT endpoint_name(
  parameter_name Type [, ...]
)
[AUTH CLAIMS (claim_name Type [, ...]) [USING auth_provider]]
AS [STREAM] SELECT ...
[SETTINGS key=value [, ...]];

USING is part of the AUTH CLAIMS clause. A claimless endpoint cannot name an auth provider.

Use $parameter_name for a declared client value and $auth.claim_name for a declared server claim. Raw ordinal placeholders such as $1, the provider-only $token, and undeclared named parameters are rejected in an endpoint body.

CREATE ENDPOINT fill_snapshot(symbol String)
AUTH CLAIMS (tenant_id UInt64)
USING app_sessions
AS SELECT symbol, px, qty
FROM fills
WHERE tenant_id = $auth.tenant_id
  AND symbol = $symbol
SETTINGS max_executions_per_min=30,
         timeout_ms=250,
         max_result_rows=1000;

AS SELECT is one-shot and is invoked with OP_ENDPOINT_EXEC. AS STREAM SELECT is live and is invoked with OP_ENDPOINT_SUB. Subscribing to a one-shot endpoint is an invalid invocation shape.

CREATE OR REPLACE ENDPOINT retains the endpoint UUID and increments its version. Plain CREATE rejects a duplicate name. Catalog DDL is journaled and replayed when a persistent data directory is configured.

Static endpoint limits

All six recognized settings use an unsigned integer. An omitted setting and an explicit 0 both mean unlimited for that dimension.

Neither timeout_ms nor max_result_rows applies to subscription setup, its initial snapshot, or subsequent deltas.

Operator and system-class work is exempt from end-user endpoint limits, though endpoint counters still include operator invocations and subscriptions.

Unknown endpoint settings are recorded with a warning for forward-compatible DDL replay; they are not enforcement controls. Use only the six keys above when the behavior is required.

Application grants and invocable views

An endpoint is the pre-authorized bundle. A logical view exposed through the same endpoint wire operations follows a different rule: default deny against the application grant catalog.

CREATE VIEW fills_for_symbol AS
SELECT symbol, px, qty
FROM fills
WHERE tenant_id = ${auth.tenant_id}
  AND symbol = ${symbol:String};

GRANT read, subscribe
ON VIEW fills_for_symbol
TO ROLE 'customer';

The auth provider conventionally returns a role claim. OP_ENDPOINT_EXEC requires read; OP_ENDPOINT_SUB requires subscribe. The view may be invoked by name or relation UUID. A supported CREATE OR REPLACE operation that keeps the relation UUID also keeps the UUID-backed policy. Drop-and-create establishes a new identity and therefore a new policy boundary.

Name resolution is endpoint-first. If an endpoint and logical view share a name, OP_ENDPOINT_EXEC or OP_ENDPOINT_SUB selects the endpoint and does not perform the expected view-grant check. Prevent collisions in published naming, and use the relation UUID when a client must invoke a specific logical view unambiguously.

SHOW GRANTS;

REVOKE subscribe
ON VIEW fills_for_symbol
FROM ROLE 'customer';

Application grants can target TABLE, VIEW, or MATERIALIZED VIEW and accept the closed verb vocabulary read, subscribe, insert, delete, and definition. This is catalog and future-facing vocabulary: the current OP_ENDPOINT_EXEC and OP_ENDPOINT_SUB paths can invoke only endpoints and logical VIEW relations. Consequently, only a logical-view read or subscribe grant is consumable on the end-user wire today. The wire does not expose application-user SQL, table or materialized-view invocation, insert, delete, or block-ingest operations.

Application grants protect authenticated end-user invocations. Named non-admin operator-class OP_ENDPOINT_EXEC and OP_ENDPOINT_SUB calls instead resolve the stored query's scanned base relations and enforce the separate operator read or subscribe grants. Admin and default-open implicit operators bypass that policy. Operator-class calls remain exempt from application endpoint quotas, so operator and end-user authorization and metering must still be reviewed as separate domains.

SHOW GRANTS and system.grants use relation, relation_uuid, role, verbs. Do not confuse them with SHOW OPERATOR GRANTS or system.operator_grants; the two catalogs share no authorization state.

Query-driven rate limits

A rate limit is a mandatory streaming query with exactly two outputs:

1. the application-user identity; and
2. an integer allowance named or interpreted as the allowed value.

CREATE TABLE user_limits (
  user_id UInt64,
  role String,
  trades_30d UInt64,
  PRIMARY KEY (user_id)
) SETTINGS mode='keyed', storage_policy='memory_data';

CREATE RATE LIMIT trader_subscriptions
ON concurrent_subscriptions AS
STREAM SELECT user_id,
  CASE
    WHEN role = 'admin' THEN 0
    WHEN trades_30d > 100 THEN 5
    ELSE 1
  END AS allowed
FROM user_limits;

The legal dynamic dimensions are:

The query body cannot use $token or client parameters. It must read a relation, return exactly two columns, and produce an allowance that parses as a non-negative UInt64. 0 means unlimited. A negative, malformed, or otherwise invalid allowance row is silently skipped; that user then falls back to the remaining dynamic coverage, the endpoint static setting, or unlimited when no bound exists. Monitor definitions and enforcement rather than assuming an invalid policy row fails closed. CREATE OR REPLACE RATE LIMIT is not supported.

Static and dynamic precedence

The effective bound is deterministic:

1. A user absent from every replica for a dimension uses the endpoint static setting unchanged.
2. A covered user takes the tightest non-zero allowance across covering rate limits.
3. If every covering limit returns 0, that explicit unlimited verdict overrides the endpoint static value.
4. A non-zero dynamic allowance and a non-zero static value compose tightest-wins. Ties are attributed to the rate limit.

Live updates do not require new DDL. Tightening concurrent_subscriptions terminates newest subscriptions first so the oldest admitted seats remain. Rows/bytes changes apply at the next sampling window; execution changes apply at the next admission. Dropping a rate limit reverts covered users to the remaining limits or the static endpoint setting.

SHOW RATE LIMITS;
DROP RATE LIMIT trader_subscriptions;

SHOW RATE LIMITS returns name, dimension, source, uuid, version. Definitions and drops are journaled; after restart, their internal system-class streams are restored. The enforcement log itself is intentionally volatile.

Introspection and operational queries

SHOW ENDPOINTS;
SHOW AUTH PROVIDERS;
SHOW GRANTS;
SHOW RATE LIMITS;

SELECT name, params, claims, provider, limits,
       subscribers, executions_total, bytes_out_total,
       uuid, version
FROM system.endpoints
ORDER BY name;

SELECT ts_ms, event, user, role, relation, dimension,
       allowed, observed, source, event_id
FROM system.governance_log
ORDER BY ts_ms DESC
LIMIT 200;

SHOW ENDPOINTS contains definition cells only: name, params, claims, provider, limits, digest, uuid, version. system.endpoints adds the live counters between the definition and identity cells.

system.governance_log records endpoint denials and kills, auth revocations, operator login outcomes, and user-management events. It is capacity-bounded, not journaled, and empty after restart. Export it if the incident record must be durable.

Safe dependency teardown

Use dependency order rather than forcing catalog removal:

1. stop or drain application clients;
2. verify system.endpoints.subscribers is zero and terminate any remaining transport sessions;
3. drop or replace endpoints that reference the provider only after the old subscriptions are gone, because catalog mutation does not revoke them and a drop removes their governor/metering state;
4. revoke application grants that should no longer exist;
5. drop rate limits no longer needed;
6. drop the auth provider; and
7. remove the underlying session or policy tables only after their internal subscriptions are gone.

For transport framing and reconnect behavior, continue with Browser and platform clients. For database users and the operator plane, see Operator governance.