Data Model

Understand the SOIT kernel data model for identity, agents, workflows, runtime traces, knowledge, plugins, and secrets.

SOIT data is organized around scoped resources, immutable versions, runtime evidence, and governed capability artifacts. The kernel data model is intentionally a stable skeleton: columns may be extended over time, but the core keys, constraints, and index patterns are designed to stay fixed so that migrations remain small.

Global conventions

IDs are sortable ULIDs stored as text or UUID.
Timestamps use created_at / updated_at as timestamptz.
Soft delete via deleted_at is recommended for high-value resources.
Scoping: every workspace-scoped table carries tenant_id + workspace_id.

Identity

Table	Purpose
`tenants`	Top-level account with plan information
`workspaces`	Named workspace under a tenant, unique per `(tenant_id, name)`
`users`	User accounts with unique email
`tenant_memberships`	Role per user per tenant (Owner / Admin / Dev / Viewer)
`workspace_memberships`	Role per user per workspace

Agents and workflows

Agents are the primary business object. Both agents and workflows follow the same versioning shape:

A mutable head record (agents, workflows) with name, status, visibility, and two pointers: current_version_id (latest draft head) and published_version_id (live release).
An immutable version table (agent_versions, workflow_versions) holding the full spec_json snapshot (agent.v1, workflow.v1 schemas) with creator and timestamp.

Version records are never mutated; releasing means moving the published pointer. See Versioning and Release.

Runtime trace ledger

The unified execution trace is the heart of the model — every execution mode (chat, agent, workflow, knowledge, memory) writes into the same ledger:

Table	Purpose
`threads`	Workspace-scoped session container: type, status, model defaults, context-window settings, knowledge/tool config, message counters
`thread_messages`	Append-only message ledger with `sequence_no` per thread, role, content, model usage, citations, tool calls, and links to `run_id` / `task_id` / `response_id`
`runs`	The trace root for one execution: `mode`, execution subject (`subject_kind` / `subject_id` / `subject_version_id`), status, bounded input/output summaries
`run_steps`	Atomic observable units inside a run: `step_type` (llm / retrieve / tool / node / plan), status, timings, bounded summaries, and `metrics_json` (tokens, latency, HTTP status, vector counts)
`run_artifacts`	Large payloads by reference: type, `storage_key`, and metadata (mime, size, hash)
`run_cost_entries`	Normalized billable usage per run or step: currency, amount, unit, quantity, provider, model/tool reference, token counts

Every run records the exact subject version it executed, which is what makes observe, audit, replay, and rollback meaningful.

Knowledge

The knowledge family covers ingestion and retrieval:

knowledge — workspace-scoped knowledge bases with chunking, retrieval, and embedding settings.
knowledge_documents — versioned documents with doc_key, source_kind (upload | crawler | api | manual), status, and retry tracking.
knowledge_chunks — chunked text with previews, artifact keys for full text, and vector references.
knowledge_indexes — named vector indexes with embedding model reference, dimension, metric type, and build status.

Public APIs expose knowledge_type and source_kind as stable fields; internal storage details do not leak into product APIs.

Tools, plugins, and secrets

tools — workspace-scoped tool specs (ToolSpec in spec_json).
plugins — tenant-scoped plugin records with the same head-plus-pointer versioning shape as agents.
plugin_versions — immutable manifests with digest and signature.
plugin_releases — an append-only release ledger recording every publish and rollback action.
plugin_installations — workspace-scoped installs with enablement state and environment config.
plugin_installed_artifacts — a projection ledger of what each installed plugin contributes: skill, mcp_server, tool, or workflow_node artifacts.
secrets — tenant-scoped secret references (vault / kms / local provider plus a reference path). Plaintext secret values are never stored in business rows.

Index rules

Three hard rules keep multi-tenant queries fast and isolation enforceable:

Any UNIQUE constraint on a workspace resource must include (tenant_id, workspace_id, ...).
Any listing query must be backed by an index starting with (tenant_id, workspace_id, ...).
Trace tables must support fast lookup by (tenant_id, workspace_id, run_id).

Cross-resource references are enforced in business logic and repositories rather than through database-level foreign keys across families.

Runtime Ledger — how executions write into this model.
Outbox Events — how state changes dispatch side effects.
Versioning and Release — the immutable version semantics.

Data Model

On this page