Data Models

Authoritative description of persisted shapes nexus owns: SQL migrations under services/*/migrations/ and ClickHouse schemas.

Postgres

Three Postgres databases run side by side in dev:

• nexus_auth — users, sessions, identities, OIDC login states, and the auth-service audit-staging table. Schema lives under services/auth/migrations/.
• nexus_control — workspaces, orgs, projects, memberships, invitations, registration codes, API keys, provider keys, trace ingest keys, model registry (models, endpoints, aliases, rate cards/rates/activations, support classifications, import change sets), file/vector-store metadata plus vector_store_chunks (pgvector), workspace wallet/settings/transaction/escrow/payment tables, and the control-plane audit-staging table. Schema lives under services/control-plane/migrations/.
• nexus_audit — the sealed audit store owned by the audit service: audit_events, audit_tenant_counters, audit_signing_keys, audit_batch_roots, and audit_meta_roots. Schema lives under services/audit/migrations/.

The hierarchy is tenant → workspace → organization → project. Auth users and sessions are scoped to a tenant. Workspaces are the funded and membership-managed container. Organizations belong to a workspace, projects belong to an organization, and API keys/provider keys are scoped to a project. Console sign-up provisions a personal workspace, default organization, default project, and workspace/org/project manager memberships for the new user.

Gateway-owned file and vector-store metadata belongs with the control data model because every row is scoped by tenant, org, and project. File bytes live in the configured artifact store; request analytics keep metadata and references only.

Each service applies its own migrations at startup via a runtime sqlx migrator pointed at its migrations/ directory. The control-plane database is shared between the gateway, control-plane, auth, and nexus CLI via the NEXUS_DATABASE_URL environment variable (auth additionally owns AUTH_DATABASE_URL for nexus_auth).

When adding a new persisted shape, add a numbered migration under the owning service’s migrations/ directory and update this file with the table summary.

registration_codes stores reusable invite-only sign-up credentials in the control-plane database. Rows are tenant-scoped and keep the public prefix, BLAKE3 digest, maximum uses, current uses, creator, revocation timestamp, expiration timestamp, and creation timestamp. The raw nxr_... code is returned only when created and is not stored.

Provider pricing is normalized under model_registry_rate_cards and model_registry_rates — the single stored source of pricing truth. model_registry_endpoints carries no price columns; every read surface (runtime registry, management listing, console, CLI) projects flat rates and context bands in memory from the endpoint’s active rate card, and billing selects meter-level rates from the card directly. Embedding-capable endpoint rows persist the artifact-published embedding_dimensions and embedding_max_inputs facts. Artifact import is full replacement for these nullable values, so removing a published fact also removes it from the runtime registry rather than retaining stale metadata. Rate cards preserve provider, canonical model, currency, default service tier, activation interval, source URL/hash, parser version, source import change set, and the normalized source-effective interval. Rate rows preserve service tier, exact context threshold, meter, billing unit, modality, qualifiers, decimal unit price, source import change set, and normalized source-effective interval. source_interval_recorded distinguishes an explicitly unbounded source interval from rows created before artifact-backed provenance. model_registry_rate_card_activations records the half-open interval in which each append-only card is selected, whether that activation was superseded, and the import change set that created it. An artifact apply whose rate set, activation interval, source interval, and default service tier match an existing active card reuses that card and activation, so only material pricing changes add pricing rows. Its composite foreign key requires the activation provider and canonical model to match the referenced rate card. The model pricing-history read joins activations, cards, and all qualified rates without adding a separate history store; actual superseded_at timestamps are kept separately from planned activation and card-effective intervals. Effective intervals are lower-bound inclusive and upper-bound exclusive; active rows must not overlap for the same qualified identity.

model_registry_import_change_sets stores each validated artifact together with its SHA-256 digest, signature metadata, provider baseline and revision, deterministic changes and findings, generator and contract identity, review state, and apply result. model_registry_import_change_set_provenance retains source URI, source SHA-256, generator version, and effective timestamp evidence for that change set. Imported endpoints retain their opaque artifact endpoint ID under a provider-scoped uniqueness constraint, plus endpoint-level request capability flags, streaming support, and input/output modalities. The endpoint provider is the open provider-lane identity used by model strings, credentials, policy, billing, and telemetry. The required endpoint backend is a supported typed provider wire name used only for adapter dispatch, protocol behavior, host selection, and partition rules. Built-in lanes use the same value for both fields; custom lanes can bind a different provider name to an existing backend. Disabled unpriced endpoints may omit an active rate card while retaining their artifact identity. model_registry_aliases stores provider-scoped aliases, retaining artifact provenance and the import change set that last wrote them. Approvals bind the artifact digest, baseline hash, provider revision, findings digest, artifact schema, import contract, generator name and version, actor, and expiry. Application rechecks those bindings under provider advisory and change-set row locks before committing all registry and lifecycle writes atomically. An artifact is the complete catalog for its provider, so apply is a full replace: endpoints, aliases, and support classifications the artifact no longer carries are retired, and live activations whose card the artifact no longer carries are superseded, all planned as explicit reviewable retirement changes at prepare time. Model rows are shared across providers and retire implicitly with their last endpoint rather than being deleted. All pricing and endpoint retirement preserves pinned endpoints. Database triggers make imported rate-card and qualified-rate rows immutable after insertion.

Endpoint quarantine is represented directly on model_registry_endpoints by pricing_status, pricing_status_reason, pricing_status_detail, quarantined_at, and quarantine_rate_card_id. model_registry_billing_failures preserves the request, endpoint, rate dimensions, raw provider usage, and typed billing failure as forensic detail. Endpoint billing health is evaluated from the ClickHouse request analytics window and stored on model_registry_endpoints; the configured action either blocks degraded endpoints or leaves them routable. There is no model_registry_endpoint_quarantines table.

files stores OpenAI-compatible artifact file metadata in the control-plane database. Rows are scoped by tenant, organization, and project and keep filename, purpose, byte count, BLAKE3 checksum, content type, object-store key, creation timestamp, and nullable soft-delete timestamp. The active file index covers (tenant_id, org_id, project_id, created_at) for non-deleted rows; file bytes are stored only in the configured artifact object store.

vector_stores stores OpenAI-compatible vector-store metadata. Rows are scoped by tenant, organization, and project and keep optional name, JSONB metadata, optional expires_after policy, creation timestamp, nullable soft-delete timestamp, and last_active_at. The active vector-store index covers (tenant_id, org_id, project_id, created_at) for non-deleted rows. expires_after follows the OpenAI policy shape with anchor = "last_active_at" and days; read paths compute expires_at from that policy without a background sweeper.

vector_store_files stores file attachments for vector stores. Rows are scoped by tenant, organization, and project, reference vector_stores(id) and files(id), keep attachment status plus creation and soft-delete timestamps, and retain soft-deleted history. A partial unique index on (vector_store_id, file_id) where deleted_at IS NULL allows only one active attachment at a time while permitting detach/re-attach history. Additional indexes cover active scope/created ordering, active store/created ordering, and file-scoped soft-delete sweeps.

ClickHouse

The ingest service applies the schemas in services/ingest/migrations/clickhouse/, creating:

• requests — tenant-visible request metadata for list/detail analytics. Core columns include request identity (request_id, org_id, project_id), routing (model, provider, endpoint_key), result (status_code, latency_ms), usage/cost (prompt_tokens, completion_tokens, *_cost_usd), frozen reporting-dimension values (reporting_dimensions) and source assignment ids (reporting_dimension_sources), and body references (request_body_ref, response_body_ref, hashes and byte sizes). Engine: ReplacingMergeTree(version) keyed by (org_id, project_id, created_at, request_id). Stored request and response bodies live in organization-isolated pack objects. Every non-empty reference has the form pack-v1:<bucket>/<key>#<offset>:<length>:zstd, where the range is one complete independently compressed frame.
• request_usage_lines — normalized provider usage keyed by (request_id, line_key), preserving meter, billing unit, modality, service tier, context, qualifiers, provider-reporting state, and exact decimal quantity.
• request_cost_lines — per-request cost breakdown lines keyed by (request_id, line_key), preserving the matching usage dimensions, rate-card/rate identifiers, source hash, billing status, exact unit price, and exact amount.
• request_feedback — thumbs-style feedback rows recorded through the query service.
• metrics_by_minute — per-minute SummingMergeTree rollup fed by two materialized views: one over requests (request count, tokens, errors, latency) and one over request_cost_lines (cost-line count, quantity, cost). It is a best-effort dashboard rollup, not a billing source; a redelivered batch whose row content changed can double-count.
• tenant_exporter_configs / exporter_delivery_state — tenant analytics exporter configuration and delivery bookkeeping.
• trace_spans, trace_events, trace_artifact_refs — native agentic trace rows (see the trace contract shapes below).
• usage_monthly_rollup — monthly usage/cost rollup read FINAL by the spend-cap and billing paths.

Applied ClickHouse schema versions are recorded in nexus_schema_migrations. The ledger makes table-rebuild migrations run once while leaving interrupted, unrecorded migrations safe to retry. Before applying an unrecorded migration a replica writes a claim row with a random token and proceeds only if its claim survives, so concurrently starting replicas do not run the same stateful rebuild twice.

The request row is always written for tenant analytics and accountability, but full payload trace storage is opt-in. When tracing is disabled, body refs stay empty, byte counts and blake3 hashes can still be recorded, request_body_omitted / response_body_omitted are true, and tool_summary_json contains only safe summary data. When tracing is enabled by org/project/API-key configuration or nexus-enable-tracing: 1, request/response bodies and full tool trace artifacts can be stored and referenced from the row.

Ack-after-durable-write contract

The analytics ingest consumer and the audit consumer share one durability contract: a JetStream message is acked only after its write is confirmed durable. Analytics acknowledgment follows body-pack upload, ClickHouse insertion, and the JetStream persistence acknowledgment for its audit event (whose deterministic event id makes republication idempotent); audit acknowledgment follows the Postgres event insert. On a write failure the message is left unacked and JetStream redelivers it; nothing is dropped. Redelivery is idempotent for analytics because requests, request_usage_lines, request_cost_lines, and usage_monthly_rollup are ReplacingMergeTree deduplicated by their sorting key (which includes request_id, derived deterministically from the event) and every read that has to be exact collapses versions with FINAL: the spend-cap/billing path over usage_monthly_rollup, the workspace report, the usage summary and its cost-band and cached-input reads, the latency and error-rate query, and the session list. Line rows carry an insert-deduplication identity of (org_id, request_id, line_key), so two requests emitting the same request-local line keys are distinct blocks and each keeps its complete evidence set. Messages that cannot be decoded, or that exhaust the redelivery budget, are routed to a dead-letter stream rather than discarded (see Observability (Local Stack)). Dead-lettered payloads are the raw inbound envelope (pre-redaction) and are retained on nexus_REQUESTS_DLQ for 30 days — longer than the 24h main stream and bypassing per-classification body redaction; per-classification handling of dead-lettered payloads is deferred. The per-minute metrics_by_minute rollup is best-effort and may double-count on redelivery; it is not a billing source.

The Postgres wallet/escrow ledger (workspace_wallets, workspace_wallet_escrows) is independent of this path: escrow reserve/commit/cancel are Postgres transactions driven by gateway-computed cost and are unaffected by a ClickHouse outage.

Trace contract shapes

The ingest service persists native trace metadata into trace_spans, trace_events, and trace_artifact_refs. Request/session metadata remains in the requests table, while full request/response bodies and trace artifacts use object storage only when capture policy allows it.

trace_spans

trace_spans is the contract row shape for agentic trace spans. It is keyed for tenant/session/trace reads and stores promoted columns for the UI while preserving the filtered attribute JSON.

Core identity and scope columns:

• trace_id
• span_id
• parent_span_id
• org_id
• project_id
• session_id
• request_id

Timing and status columns:

• started_at
• ended_at
• duration_ms
• status
• status_message

Pending spans keep ended_at equal to started_at, duration_ms = 0, and status = "pending" so ClickHouse stores a valid DateTime64 value while Query can still expose the span as in-flight. Completed spans use their OTLP end timestamp and measured duration.

Span classification columns:

• span_name
• span_kind
• openinference_span_kind
• service_name
• source

Promoted OpenInference columns:

• llm_model_name
• llm_provider
• llm_system
• llm_prompt_tokens
• llm_completion_tokens
• llm_total_cost_usd
• tool_name
• agent_name
• user_id

Nexus columns:

• classification
• trace_source
• payload_state
• full_trace_available
• attributes_json
• version

attributes_json keeps the normalized OpenTelemetry/OpenInference attributes after privacy filtering. Nexus tenancy and authorization never come only from attributes_json; they come from authenticated Nexus context and promoted scope columns.

trace_events

trace_events is the contract point-in-time event row shape for span lifecycle events, logs, and framework events. It is optimized for live replay and span detail panels.

Core columns:

• event_id
• trace_id
• span_id
• org_id
• project_id
• session_id
• event_name
• occurred_at
• level
• message
• artifact_id
• attributes_json
• version

Examples of event names include trace.span.started, trace.span.updated, trace.span.ended, and trace.event. Trace ingest writes a trace.span.started event for each accepted span and writes trace.span.ended only when the submitted span includes an end timestamp.

Trace artifacts

Large or sensitive span payloads are represented by TraceArtifactRef. The artifact reference shape includes:

• artifact_id
• trace_id
• span_id
• kind
• object_key
• mime_type
• compression
• byte_count
• blake3_hash
• payload_state
• redaction_metadata_json

Artifact kinds include input, output, tool_arguments, tool_result, retrieved_document, image, audio, and raw_provider_payload. When full tracing is disabled or policy rejects capture, object_key remains empty and the row retains only safe summary metadata, byte counts, and hashes where available.

Wire-only and in-memory shapes

Protobuf definitions under proto and generated code under crates/nexus-proto describe RPC messages. A .proto message is authoritative only where both ends of the RPC are implemented; keep Wire Contracts aligned with what is actually served.