Data Models

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

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 (including capability and price-band tables), 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 sqlx::migrate!. 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. Rate cards preserve provider, canonical model, currency, default service tier, effective interval, source URL/hash, and parser version. Rate rows preserve service tier, exact context threshold, meter, billing unit, modality, qualifiers, and decimal unit price. model_registry_rate_card_activations records the half-open interval in which each immutable card is selected and whether that activation was superseded. 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_sync_plans, revisions, approvals, and findings store the collect-plan-approve-apply state. Approvals bind the provider, baseline revision/hash, candidate hash, parser version, actor, and expiry. Application rechecks that identity under a provider advisory lock before changing active endpoints or rates. model_registry_endpoint_quarantines and provider billing-failure rows preserve unknown post-response usage until an approved provider apply restores complete pricing coverage.

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.

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), 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).
• 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). Retried batch inserts do not inflate the rollup because every ingest insert carries a deterministic deduplication token, the insert-target tables keep a non_replicated_deduplication_window, and block deduplication extends into the dependent materialized views.
• 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.

The analytics ingest consumer and the audit consumer share one durability contract: a JetStream message is acked only after its write is confirmed durable (the ClickHouse insert for analytics; the Postgres event insert for audit). On a write failure the message is left unacked and JetStream redelivers it; nothing is dropped. Redelivery is idempotent for analytics because requests, request_cost_lines, and usage_monthly_rollup are ReplacingMergeTree deduplicated by their sorting key (which includes request_id, derived deterministically from the event) and the spend-cap/billing path reads usage_monthly_rollup FINAL. Messages that cannot be decoded, or that exhaust the redelivery budget, are routed to a dead-letter stream rather than discarded. 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 rollup is a SummingMergeTree fed by materialized views; redelivered batches do not double-count because ingest inserts carry deterministic deduplication tokens that the insert-target tables’ deduplication window honors and that extend to the dependent materialized views.

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.

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 is the contract row shape for agentic trace spans. It is keyed for tenant/session/trace reads and stores promoted columns for the UI and HQL 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 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.

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.

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 the wire contracts documentation aligned with what is actually served.