Skip to content

About

Persistence layer for LegionIO: SQLite, PostgreSQL, and MySQL via Sequel.

Topics

Resources

Code of conduct

Contributing

Security policy

Stars

1 star

Watchers

1 watching

Forks

 
 

legion-data

Persistent database storage for the LegionIO async job engine and AI coding assistant platform. Provides database connectivity via the Sequel ORM, numbered schema migrations, Sequel models for the full LegionIO control plane, and a parallel local SQLite database for on-node agentic cognitive state.

Version: 1.11.6 | Ruby: >= 3.4 | License: Apache-2.0


What It Owns

legion-data is the data contract for LegionIO. It owns database connectivity, migrations, model loading, and portable Sequel model definitions for shared platform state. HTTP routes, runtime orchestration, and extension behavior live in other LegionIO repos and call into these models.

Core responsibilities:

Area Tables and models
Control plane extensions, functions, runners, nodes, tasks, settings, workers, relationships, chains
Audit and governance audit_log, audit_records, governance_events, archive manifests
Identity and RBAC providers, principals, identities, groups, memberships, role grants, runner grants
LLM ledger conversations, model-visible messages, inference requests/responses, routing, metrics, tool calls, policy/security events
Apollo knowledge PostgreSQL pgvector and SQLite JSON-text embeddings, relations, expertise, access logs
Local state on-node SQLite cognitive state, independent of the shared database

The schema is portable by default across SQLite, MySQL, and PostgreSQL. PostgreSQL-only behavior is isolated to features that need PostgreSQL, such as Apollo vector columns.

Legion::Data.setup raises on fatal connection configuration, migration, or model-loading failures rather than announcing successful startup. Failed setup closes its database connections and clears readiness; fix the underlying error before retrying. Local migrations registered after startup also propagate failures and leave the local database disconnected until setup succeeds.

Framework Conventions

Startup and Table Ownership

The shared database starts in this order: connect → run migrations → load models → mark Data connected. Cache setup and local storage setup follow. Application code uses the models registered by that lifecycle; model declarations must not be loaded before their tables are migrated.

A table belongs to the component that creates and maintains its schema. A reference in an existing helper does not establish ownership. Before adding a model, identify the owning migration and its supported adapters. A PostgreSQL-only migration does not create its table on SQLite or MySQL.

Model-backed Table Access

  • Read and write application tables through named Sequel models, such as Legion::Data::Model::Task, rather than connection[:tasks] or db[:tasks].
  • If a Data-owned table is used but lacks a model, add a normal named class under lib/legion/data/models/ and register it in Legion::Data::Models.models.
  • Use the existing model namespace and association conventions. Do not replace named models with Class.new, anonymous models, or a facade around raw table access.
  • Shared operations can accept an explicit source model rather than infer a class from a table name. Keep transactional source and destination models on the same database.
  • Keep model instances within database operations. Return plain Hashes across module boundaries, using .values or an explicit result Hash.

Migrations and database infrastructure still need direct Sequel database operations for schema changes, connection setup, and adapter/session management. Those operations are distinct from application-table reads and writes.

Settings

Keep method-returned settings defaults in the existing methods, including Legion::Data::Settings.default. Do not extract that settings tree into a constant such as CONNECTION_DEFAULTS, a class variable, or a memoized snapshot.

Register defaults through Legion Settings at library startup. Runtime code reads the current Legion::Settings[:data] values so configured overrides and settings reloads remain authoritative. Follow the framework's existing loading and access patterns rather than adding a second settings mechanism.

Supported Databases

Database Adapter Gem Default
SQLite sqlite sqlite3 (bundled) Yes
MySQL mysql2 mysql2 (optional) No
PostgreSQL postgres pg (optional) No

SQLite is the default and requires no additional gems. For MySQL or PostgreSQL, install the corresponding gem and configure the adapter.


Installation

gem install legion-data

Or add to your Gemfile:

gem 'legion-data'

# For production databases, add one of these:
# gem 'mysql2', '>= 0.5.5'
# gem 'pg', '>= 1.5'

Architecture Overview

Legion::Data (singleton module)
├── .setup              # Connect, migrate, load models, set up local DB
├── .connection         # Sequel::Database handle (shared/central)
├── .local              # Legion::Data::Local (local SQLite accessor)
├── .stats              # Combined { shared: ..., local: ... } metrics
├── .reload_static_cache  # Refresh in-memory StaticCache after extension hot-load
├── .shutdown           # Close both shared and local connections
│
├── Connection          # Sequel database connection management
│   ├── .adapter        # Reads adapter from settings (:sqlite, :mysql2, :postgres)
│   ├── .setup          # Establish connection (dev_mode fallback to SQLite if unreachable)
│   ├── .sequel         # Raw Sequel::Database accessor
│   ├── .connection_info  # Adapter, liveness, and fallback diagnostics
│   ├── .fallback_active? # True when dev fallback moved a network DB to SQLite
│   ├── .stats          # Pool metrics, tuning snapshot, adapter-specific DB stats
│   └── .shutdown       # Disconnect shared connection; close query log
│
├── Migration           # Numbered Sequel DSL migrations
│
├── Model               # Sequel model autoloader
│   └── Models: Extension, Function, Runner, Node, Task, TaskLog, Setting,
│               DigitalWorker, Relationship, AuditLog, AuditRecord, Chain,
│               RbacRoleAssignment, RbacRunnerGrant, RbacCrossTeamGrant,
│               IdentityProvider, Principal, Identity, IdentityGroup,
│               IdentityGroupMembership, IdentityAuditLog, ExtractStepTiming,
│               ApolloEntry, ApolloRelation, ApolloExpertise, ApolloAccessLog (PG only),
│               LLM::Conversation, LLM::Message, LLM::MessageInferenceRequest,
│               LLM::MessageInferenceResponse, LLM::RouteAttempt,
│               LLM::MessageInferenceMetric, LLM::ToolCall, LLM::ToolCallAttempt,
│               LLM::ConversationCompaction, LLM::PolicyEvaluation,
│               LLM::SecurityEvent, LLM::RegistryEvent
│
├── Local               # Parallel local SQLite for agentic cognitive state
│   ├── .setup          # Lazy init — creates legionio_local.db on first access
│   ├── .connection     # Sequel::SQLite::Database handle
│   ├── .model(:table)  # Create Sequel::Model bound to local connection
│   ├── .register_migrations(name:, path:)  # Extensions add their own migration dirs
│   ├── .stats          # Local SQLite metrics (PRAGMAs, file size, registered migrations)
│   └── .shutdown       # Close local connection
│
├── Extract             # 10-handler text extraction registry (txt/md/csv/json/jsonl/html/xlsx/docx/pdf/pptx/vtt)
├── Spool               # Filesystem write buffer for DB-unavailable scenarios
├── Rls                 # PostgreSQL row-level security helpers (tenant isolation)
├── StorageTiers        # Hot/warm/cold archival lifecycle
├── EventStore          # Append-only governance event store with hash chain integrity
├── Vector              # Reusable pgvector helpers (cosine_search, l2_search, ensure_extension!)
└── Settings            # Default configuration with per-adapter credential presets

Two-Database Architecture

legion-data maintains two independent databases:

  1. Shared DB (SQLite / MySQL / PostgreSQL) — control plane data: extensions, tasks, runners, nodes, settings, audit logs, relationships. Shared across the cluster.
  2. Local DB (always SQLite) — agentic cognitive state: memory traces, trust scores, dream journals. On-node only; no cross-database joins.

Deleting legionio_local.db provides cryptographic erasure — no residual data.


Usage

require 'legion/data'

# Set up shared DB + local SQLite, run migrations, load models
Legion::Data.setup

# Access the Sequel database handle
Legion::Data.connection              # => Sequel::Database

# Access models
Legion::Data::Model::Extension.all  # => Sequel::Dataset
Legion::Data::Model::Task.first(id: 42)
Legion::Data::Model::Setting.where(key: 'my_setting').first

# Access local cognitive state DB
Legion::Data.local.connection        # => Sequel::SQLite::Database
Legion::Data.local.connected?        # => true
Legion::Data.local.db_path           # => "legionio_local.db"

# Check connection health
Legion::Data.connected?              # => true
Legion::Data.stats                   # => { shared: {...}, local: {...} }

# Inspect shared DB diagnostics, including dev fallback state
Legion::Data::Connection.connection_info
# => { adapter: :sqlite, connected: true, fallback_active: false, ... }

# Shut down both connections
Legion::Data.shutdown

Database Shutdown

Legion::Data.shutdown disconnects the shared connection, closes the query log, and marks Data as disconnected. It does not cancel in-flight SQL — configure database statement timeouts separately to bound query execution. To resume work, call Legion::Data.setup; it creates a new connection.

Model Associations

Models use Sequel associations as the public object graph. Prefer association methods and association datasets over hand-written foreign-key lookups when the relationship is part of the schema contract.

task = Legion::Data::Model::Task.first(id: 42)
task.function             # many_to_one :function
task.relationship         # many_to_one :relationship
task.task_logs_dataset    # further filter/order without losing the relationship

conversation = Legion::Data::Models::LLM::Conversation.first(uuid: conversation_uuid)
conversation.messages_dataset.order(:seq).all
conversation.security_incident_lineage

Official LLM lifecycle data lives under Legion::Data::Models::LLM. legion-llm and lex-llm-ledger should use these models and the llm_* migration tables for conversations, model-visible messages, inference requests, responses, route attempts, metrics, tool calls, policy decisions, security events, and registry events. Legacy ledger-only tables are not the canonical schema.

Association rules used in this repo follow Sequel's own association model:

Relationship Use this Sequel association
Current table has the foreign key many_to_one
Associated table has the foreign key one_to_many or one_to_one
Join table connects both sides many_to_many
One associated record through a join table one_through_one

When Sequel cannot infer names from the schema, models must be explicit with :class, :key, :primary_key, :join_table, :left_key, and :right_key. Association names must not collide with real column names because Sequel creates methods with the association name.

Local Database (Agentic Cognitive State)

Extensions register their own migration directories and create models bound to the local connection:

# Register extension migrations (called during extension setup)
Legion::Data::Local.register_migrations(name: :memory, path: '/path/to/migrations')

# Create a model class bound to the local DB
MyMemoryTrace = Legion::Data::Local.model(:memory_traces)
MyMemoryTrace.all  # queries legionio_local.db, never the shared DB

Text Extraction

Legion::Data::Extract provides a handler registry for extracting text from documents, used by lex-knowledge for corpus ingestion:

result = Legion::Data::Extract.extract('/path/to/document.pdf')
text = result[:text]
result[:step_timings] # per-step name, start_time, end_time, status, error, duration_ms

Supported formats: .txt, .md, .csv, .json, .jsonl, .html, .xlsx, .docx, .pdf, .pptx, .vtt

When migration 076 is present, Extract also persists the same per-step timing rows to extract_step_timings under the returned extract_id.

Task Persistence

Legion::Data::Model::Task is the normal Sequel model for task persistence. The message-processing contract assumes RabbitMQ prefetch of one: each persistence message performs one model insert or update, and the runtime acknowledges the message only after that operation succeeds. A failed write must not be acknowledged as successful. Data does not add message batching, attempt ownership, leases, or outbox coordination.

Task statuses retain their existing persisted identities in Legion::Data::Task::Status. Task rows follow the ordinary archival age, status, and dependency rules.

The existing model idempotency API is unchanged:

  • Legion::Data::Model::Task.idempotency_key_for(payload) computes a stable SHA-256 key from canonical JSON.
  • create_idempotent(values, payload: nil, idempotency_key: nil, ttl: nil) accepts a positional values Hash and returns a Sequel model. Without ttl, it does not add an expiry.
  • find_active_by_idempotency_key(key, now: Time.now) reads the original idempotency_key column and returns a Sequel model or nil. Terminal tasks and expired keys allow a new occurrence.

These helpers do not promise concurrent deduplication or permanent execution identity. Frozen timestamp payloads are canonicalized without mutation.

Migration 139 supplies the previously unprovisioned task_logs table and the normal model loader registers Legion::Data::Model::TaskLog. Both TaskLog.create and TaskLog.insert generate its public UUID and retain supplied event timestamps. insert supports the existing positional Hash and keyword-Hash call shapes and returns the inserted integer ID; create returns a model. Entries are text and may reference a task, node, or function. Removing those referenced records nulls the reference rather than deleting the log evidence. An unsupported manually created task_logs table requires operator reconciliation before upgrading: migration 139 fails safely if it already exists and never overwrites, truncates, or automatically adopts existing logs.

Serialized Audit and Governance Appends

After migration 138, AuditRecord.append serializes each chain and EventStore.append serializes each stream with database-backed claims, including empty chains. Different audit/event namespaces remain independent. Claim rows do not accumulate after transactions finish; failed appends roll back both claim and record.

Audit tails, verification, and walks use append IDs rather than timestamps, preserving chain order across writer clock skew. Governance events retain their sequential stream numbering. PostgreSQL lock/statement timeouts and native adapter lock limits bound contention; timeout and unrelated database errors propagate rather than being retried indefinitely. MySQL claims use exclusive duplicate-key locking to avoid InnoDB shared-lock upgrade deadlocks.

Model-backed Storage Tiers

Legion::Data::StorageTiers.archive_to_warm(
  model: Legion::Data::Model::Task, age_days: 90, batch_size: 1000
)

Pass the source Sequel model using model:, not a table name. The source must have id and created_at columns and use the same database as Legion::Data::Model::DataArchive. Selected rows are copied as JSON and deleted in the existing transaction. export_to_cold returns plain archived-row hashes; stats reports warm/cold counts.

Bounded Retention

Legion::Data::Retention.archive_old_records(
  table: :audit_log, archive_after_days: 90, batch_size: 1000
) # => { archived: total_moved, table: :audit_log }

Source tables need a declared primary key, including composite keys. batch_size must be a positive integer and bounds each materialized batch. Rows are selected in primary-key order; PostgreSQL/MySQL lock them and SQLite uses an immediate write transaction. Each batch copies and deletes exactly the same identities atomically, including when other rows become eligible during the copy.

Outside a caller-owned transaction, each batch commits independently. A later failure is raised without undoing earlier batches; rerun to archive the remaining eligible rows. A successful result sums that invocation's moved rows. If the caller wraps retention in its own transaction, that outer transaction still controls commit and lock lifetime.

Audit Verification After Retention

Legion::Data::Model::AuditLog.verify_chain verifies hot audit_log rows together with retained audit_log_archive rows in one ordered SQL statement when the archive exists. Moving records between these tables does not discard their predecessors or create a mixed-snapshot boundary. Archived content is rehashed; missing predecessors, modified content, and duplicate copies remain verification failures.

Passing an explicit record collection still verifies exactly that collection from genesis. Cold storage and purged history are not silently trusted: missing archived predecessors remain invalid until the complete history is supplied. This does not change LegionIO's separate tier-verification API.

Before the first retention run, verification reads hot-only history without creating archive schema. It checks again afterward for concurrent archive creation and, if the archive appeared, verifies both tables together. Only an actual missing-table error means the archive is absent; permission, connection, and other database failures propagate.

Filesystem Spool (Write Buffer)

When the database is unavailable, Legion::Data::Spool buffers writes to ~/.legionio/data/spool/ and replays once the connection is restored:

spool = Legion::Data::Spool.for(Legion::Extensions::MyLex)
spool.write(:events, { task_id: SecureRandom.uuid, data: payload })
spool.flush(:events) { |entry| process(entry) }

Replay claims each event with a nonblocking advisory file lock held through the callback and successful deletion. Concurrent processes cannot replay the same live claim. read and clear skip in-flight entries; count includes them until deletion. Corrupt files are quarantined under the same ownership protocol.

Callback failure retains the event and releases its lock. Process exit also releases the lock automatically, so the next drainer can recover it without stale claim markers. Delivery is at least once: a crash after an external side effect but before deletion can replay that effect. Callbacks must provide their own idempotency. Single-drainer filename order is preserved, but concurrent completion order is not guaranteed. The spool filesystem must support cooperative advisory locking.

Row-Level Security (PostgreSQL)

Legion::Data::Rls provides tenant isolation via PostgreSQL session variables (migration 043):

Legion::Data::Rls.with_tenant(tenant_id) do
  Legion::Data::Model::Task.all  # scoped to tenant_id via RLS policy
end

The scope holds one pooled connection until the block finishes and its prior tenant state has been restored, including when the block raises. Nested scopes restore the outer tenant. Run scoped database work in the calling thread; newly spawned threads do not inherit its tenant scope.

Permission Checks

Legion::Data.can_write?(:tasks)  # => true (SQLite always true)
Legion::Data.can_read?(:tasks)   # => true
Legion::Data.reset_privileges!   # clear cached privilege checks

Checks use the active database adapter, including SQLite fallback, and return false while disconnected. PostgreSQL checks table-level SELECT/INSERT grants. MySQL checks base tables with a zero-row SELECT and a nonexecuting EXPLAIN INSERT; checking a MySQL view also requires the privileges needed to explain that view, including SHOW VIEW for writes. Neither probe inserts data.

Successful lookups are cached per physical connection. Connection replacement and credential rotation invalidate caches; query or connection failures return false without caching a denial. Call reset_privileges! after changing grants or roles on existing sessions.


Configuration

All settings live under the data key. The adapter controls which options apply.

SQLite (default)

{
  "data": {
    "adapter": "sqlite",
    "creds": {
      "database": "legionio.db"
    }
  }
}

SQLite lock waits use a Ruby busy handler that releases the GVL between retries, so other threads can finish transactions while a writer waits. data.timeout sets the wait limit in milliseconds for both databases; when unset, shared SQLite uses 5,000 ms and local SQLite uses 30,000 ms. Local connection-specific tuning is applied to every pooled connection. PRAGMA busy_timeout does not report the Ruby handler's deadline.

MySQL

{
  "data": {
    "adapter": "mysql2",
    "creds": {
      "username": "legion",
      "password": "legion",
      "database": "legionio",
      "host": "127.0.0.1",
      "port": 3306
    }
  }
}

PostgreSQL

{
  "data": {
    "adapter": "postgres",
    "creds": {
      "user": "legion",
      "password": "legion",
      "database": "legionio",
      "host": "127.0.0.1",
      "port": 5432
    }
  }
}

data.statement_timeout (default 5,000 ms) and data.lock_timeout (default 2,000 ms) are applied before every PostgreSQL connection enters the pool, including startup and replacement connections. Set a value to null to leave that server setting unchanged, or 0 to explicitly disable the corresponding PostgreSQL timeout.

PostgreSQL Apollo vector storage requires the pgvector extension:

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Full Configuration Reference

Defaults are registered when the library loads; runtime consumers read the current Legion::Settings[:data] tree. User configuration and subsequent settings reloads take precedence, including explicit false migration/TLS flags and empty archival table lists. Connection options apply on the next connection setup, while local migration registration and Archival.archive! (without an explicit policy) read the current configuration on each call. A full Legion::Settings.reset! requires explicit library-default registration again before using Data.

{
  "data": {
    "adapter": "sqlite",
    "connected": false,
    "dev_mode": false,
    "dev_fallback": true,
    "connect_on_start": true,

    "max_connections": 25,
    "pool_timeout": 5,
    "preconnect": "concurrently",
    "single_threaded": false,
    "test": true,

    "log": false,
    "query_log": false,
    "log_warn_duration": 1,
    "sql_log_level": "debug",

    "connection_validation": true,
    "connection_validation_timeout": 600,
    "connection_expiration": true,
    "connection_expiration_timeout": 14400,

    "read_replica_url": null,
    "replicas": [],

    "creds": { "database": "legionio.db" },
    "tls": { "enabled": false },
    "archival": {
      "retention_days": 90,
      "warm_after_days": 7,
      "cold_after_days": 90,
      "batch_size": 1000,
      "tables": ["tasks", "metering_records"],
      "storage_backend": null
    },

    "migrations": {
      "continue_on_fail": false,
      "auto_migrate": true
    },
    "models": {
      "continue_on_load_fail": false,
      "autoload": true
    },
    "local": {
      "enabled": true,
      "database": "legionio_local.db",
      "migrations": { "auto_migrate": true }
    },
    "cache": {
      "connected": false,
      "auto_enable": false,
      "static_cache": false,
      "ttl": 60
    }
  }
}

Dev Mode Fallback

When dev_mode: true and a network database is unreachable, the shared connection automatically falls back to SQLite:

{ "data": { "dev_mode": true, "dev_fallback": true } }

Fallback is intentionally loud. Connection.setup logs the degraded mode at error level, Connection.fallback_active? returns true, and Connection.connection_info reports the configured adapter, actual adapter, connection state, and Sequel liveness. Data written during fallback is local-only SQLite data and will not appear in the configured network database after reconnect.

HashiCorp Vault Integration

When Vault is connected, credentials are fetched dynamically from database/creds/legion, overriding any static creds block.

Caching

Two independent caching tiers, both disabled by default:

Tier Setting Models Backend
StaticCache data.cache.static_cache: true Extension, Runner, Function In-process frozen Ruby hash
External Cache data.cache.auto_enable: true + Legion::Cache Relationship, Node, Setting Redis/Memcached/Memory
# After hot-loading extensions, refresh the static cache:
Legion::Data.reload_static_cache

Query File Logging

Enable data.query_log (or data.local.query_log) to write SQL to a dedicated file. Write and close I/O failures, including a full disk, are reported through Legion::Logging without changing the database operation's result or replacing its exception. Opening the configured log file must still succeed during setup.

Read Replicas (PostgreSQL)

{
  "data": {
    "read_replica_url": "postgres://user:pass@replica1/db",
    "replicas": ["postgres://user:pass@replica2/db"]
  }
}

Replica URLs initialize a sharded pool at startup. URL-specific options override primary connection options; omitted credentials and session timeouts are inherited. Duplicate URLs are registered once as read_0, read_1, and so on. Reads remain on the primary unless explicitly routed, for example dataset.server(Legion::Data::Connection.read_server). With no replicas configured, read_server returns :default.

Task Database Archival

Archival.archive_completed_tasks and task-policy archival retain every task field after migration 137. Copy/delete and restore are atomic; only copied task IDs are deleted. A task referenced as a parent or master by another live task remains hot, so linked task trees archive from leaves upward across batches.

Restore preserves original IDs and restores parent/master links after inserting the requested rows. Include archived ancestors in the same restore request or restore them first. Occupied IDs or missing referenced functions/relationships cause rollback and leave the archive intact. Fields already discarded by older archival versions cannot be reconstructed by this migration.

Archive Objects

Each archive batch receives a UUID before upload. S3, Azure, and local object paths use legion-archive/<table>/<year>/<month>/batch_<uuid>.jsonl.gz; the same UUID is stored in archive_manifest.batch_id. Repeated runs do not reuse numbered object names. Existing manifest paths remain valid; no stored objects are renamed. Direct Archiver.upload_batch callers must supply batch_id: instead of batch_n:.

Encryption Keys and Cache

When Crypt and Crypt::PartitionKeys are loaded, the default scope (tenant_id: nil) uses the supported Legion::Crypt.cs cluster-key API. Keep the cluster secret persistent across restarts to retain access to encrypted records. Crypt initialization failures propagate; they do not select the development fallback key. Encrypted columns require binary storage. The no-Crypt development fallback is unchanged.

This default-scope fix does not repair tenant-scoped Crypt integration or implement the separate per-tenant Vault master-key provisioning and erasure lifecycle.

The default scope (tenant_id: nil) and tenant IDs have separate cache entries, including a tenant literally named __default__. Restart processes or clear existing key-provider caches when upgrading. This fix does not rewrite existing ciphertext: if that tenant name was used with the old cache, assess records encrypted with the wrong scope's key and re-encrypt them with the intended key. The reader does not fall back to another scope's key.


Common Fields Standard

All new tables follow a column convention. Required fields are present on every table. Optional fields are added when the domain warrants them.

Required

Column Type Notes
id INTEGER PRIMARY KEY (auto-increment) Internal join key. Never expose externally — use a uuid column for API/log references.
identity_principal_id INTEGER FK → identity_principals.id The principal who caused this row to exist.
identity_id INTEGER FK → identities.id The specific provider-bound identity credential.
identity_canonical_name VARCHAR(255) Denormalized snapshot of the principal's canonical name. Point-in-time copy — may become stale if the principal is renamed. Use the FK join for authoritative lookups. Exists for fast filtering without joins.
created_at TIMESTAMPTZ Row creation time.
updated_at TIMESTAMPTZ Last modification time.

Optional (add when applicable)

Column Type Notes
expires_at TIMESTAMPTZ TTL / archival eligibility.
content_type VARCHAR(...) Classifier for the row's payload kind.
conversation_id INTEGER FK → llm_conversations.id Links to the LLM conversation that produced this row.
contains_phi BOOLEAN Row contains Protected Health Information.
contains_pii BOOLEAN Row contains Personally Identifiable Information.

Naming rules

  • Identity FKs are always identity_principal_id and identity_id — not principal_id, agent_id, user_id, or other loose variants on new tables.
  • The denormalized string column is always identity_canonical_name — not canonical_name, actor, agent_id, or identity_name.
  • Existing columns on pre-existing tables are never renamed or removed. Columns like agent_id, source_agent, submitted_by, and actor are historical record. The new identity columns are purely additive.

Data Models

Model Table Description
Extension extensions Installed LEX extensions
Function functions Available functions per extension (with embeddings)
Runner runners Runner definitions (AMQP routing keys)
Node nodes Cluster node registry
Task tasks Task instances
TaskLog task_logs Task execution logs
Setting settings Persistent settings store
DigitalWorker digital_workers Digital worker registry
Relationship relationships Task trigger/action chains between functions
Chain chains Task execution chains
AuditLog audit_log Tamper-evident audit trail with hash chain
AuditRecord audit_records Structured audit records
ExtractStepTiming extract_step_timings Per-step Extract pipeline timing metadata
RbacRoleAssignment rbac_role_assignments RBAC principal -> role mappings
RbacRunnerGrant rbac_runner_grants Per-runner permission grants
RbacCrossTeamGrant rbac_cross_team_grants Cross-team access grants
IdentityProvider identity_providers Identity provider registrations
Principal principals Authentication principals
Identity identities Identity records tied to principals
IdentityGroup identity_groups Identity groups
IdentityGroupMembership identity_group_memberships Group membership records
ApolloEntry apollo_entries Knowledge entries — PostgreSQL only (pgvector)
ApolloRelation apollo_relations Relations between Apollo entries — PostgreSQL only
ApolloExpertise apollo_expertise Per-agent domain expertise — PostgreSQL only
ApolloAccessLog apollo_access_log Apollo access audit log — PostgreSQL only

The legacy flat Apollo model classes require PostgreSQL with pgvector. The namespaced Legion::Data::Model::Apollo::* models use the available Apollo tables on PostgreSQL and SQLite.

The Legion::Data::Model::Identity::*, Apollo::*, and RBAC::* namespaces provide cleaner Sequel model names for API-facing code while preserving the legacy flat model classes. Official LLM lifecycle models live under Legion::Data::Models::LLM.

Apollo Embedding Storage

Migrations 140–150 add the storage fields needed by the Apollo consumer without changing the established PostgreSQL vector(1024) columns or removing any ANN indexes. SQLite gains JSON-text embedding columns. Active and archived entries gain nullable embedding_space_id, embedding_dimensions, and embedding_provenance; provenance uses PostgreSQL JSONB or SQLite text. Model identity and dimensions are separate facts: two 1024-dimensional vectors are not necessarily from a compatible embedding space.

Existing rows, hashes, and PostgreSQL vectors are preserved. Legacy metadata remains null; reembedding must supply genuine model provenance rather than inferring it from vector length. These migrations do not change embedding routing or add support for arbitrary vector sizes.

Active content-hash uniqueness is scoped by access_scope and owner. Global entries share one boundary regardless of owner; restricted owners and legacy null owners remain distinct. Archived copies remain permitted. The new unique index is created before the old one is removed, so a failed upgrade does not leave deduplication unprotected.

Each migration changes one table. Rollback restores the original global unique index when the records satisfy it; conflicting ownership-scoped duplicates cause a database constraint failure without deleting records. Reversing the additions removes their metadata and SQLite embedding columns. PostgreSQL vectors and existing ANN indexes are unchanged in both directions.

Identity Namespace Models

Model Table Description
Identity::Provider identity_providers Provider records with integer primary keys and public UUIDs
Identity::ProviderCapability identity_provider_capabilities Normalized provider capability declarations
Identity::Principal identity_principals Human, service, worker, or system principals
Identity::Identity identities Provider-bound identities for principals
Identity::Group identity_groups Identity groups
Identity::GroupMembership identity_group_memberships Principal and identity group membership rows
Identity::AuditLog identity_audit_log Identity lifecycle and lookup audit events

LLM Lifecycle Models

Model Table Description
LLM::Conversation llm_conversations Conversation container tied to the base user identity
LLM::Message llm_messages Model-visible conversation transcript messages
LLM::MessageInferenceRequest llm_message_inference_requests Provider request assembled from message, context, tools, policy, and routing inputs
LLM::MessageInferenceResponse llm_message_inference_responses Provider/runtime response for one inference request
LLM::RouteAttempt llm_route_attempts Provider/model/runner routing attempts, including failures and escalations
LLM::MessageInferenceMetric llm_message_inference_metrics Token, latency, cost, and finance usage metrics for an inference pair
LLM::ToolCall llm_tool_calls Tool calls requested by an LLM provider response
LLM::ToolCallAttempt llm_tool_call_attempts Execution attempts, retries, failures, and results for provider-requested tool calls
LLM::ConversationCompaction llm_conversation_compactions Conversation-scoped compaction events
LLM::PolicyEvaluation llm_policy_evaluations Policy, classification, RBAC, and enforcement decisions for inference requests
LLM::SecurityEvent llm_security_events Security-relevant events tied to conversation, inference, response, or tool attempts
LLM::RegistryEvent llm_registry_events Provider/model registry availability and health events

Dependencies

Gem Purpose
sequel (>= 5.70) ORM and migration framework
sqlite3 (>= 2.0) SQLite adapter (default, bundled)
csv (>= 3.2) CSV extraction handler
legion-json JSON serialization via Legion::JSON
legion-logging (>= 1.5.0) Structured logging
legion-settings (>= 1.4.5) Configuration management
mysql2 (>= 0.5.5) MySQL adapter (optional)
pg (>= 1.5) PostgreSQL adapter (optional)

Migrations

Numbered Sequel DSL migrations run automatically on startup (auto_migrate: true). The files in lib/legion/data/migrations/ are the authoritative inventory. Key milestones:

Range What was added
001–011 Core schema: nodes, settings, extensions, runners, functions, tasks, digital workers, value metrics
012 Apollo tables (PG only: pgvector, uuid-ossp, 4 tables)
013–014 Relationships table with trigger/action FK chains
015 RBAC tables
017–019 Audit log with tamper-evident hash chain
020–025 Webhooks, archive tables, memory traces, tenant partitions
026 Function embeddings (description + vector on functions)
028–030 Agent clusters and approval queue
047–048 Apollo knowledge capture + financial logging (UAIS cost recovery, 7 tables)
050 Critical indexes across 13 tables
058–067 Audit records, chains, knowledge tiers, tool embedding cache, identity system (providers, principals, identities, groups)
068–071 Entity type on audit records, principal on nodes, approval queue resume, engine on relationships
072–073 Identity audit log and multi-instance identity columns
074–076 Apollo field width fixes, task idempotency columns, and Extract step timing rows
077–090 Portable LLM lifecycle schema: conversations, messages, inference requests/responses, route attempts, inference metrics, provider-requested tool calls, compactions, policy/security, and registry events
091–096 Portable identity companion schema with integer primary keys, public UUIDs, provider capabilities, principals, identities, groups, memberships, and audit log
097 LLM dispatch identifiers for fleet operation, correlation, idempotency, provider instance, and dispatch path
140–141 JSON-text embedding columns for non-PostgreSQL active and archived Apollo entries
142–147 Nullable embedding-space identity, dimensions, and provenance, one column per migration
148 Active embedding-profile lookup index
149–150 Ownership-scoped hash uniqueness, installed before removing the old global unique index

Run migrations standalone:

bundle exec legionio_migrate

Migration rules:

  • Do not edit published migrations.
  • Do not guard migrations with create_table?, table_exists?, if_not_exists, or similar conditional schema logic.
  • Add new migrations in the next available number and keep domains split by dependency and rollback risk.
  • Use portable Sequel DSL unless a feature truly requires adapter-specific behavior.
  • Prefer integer id primary keys for joins plus public uuid columns for APIs, logs, and external references.
  • Avoid JSON columns unless the shape is genuinely provider-specific or dynamic evidence.

CLI Executable

exe/legionio_migrate runs database migrations standalone, outside the full LegionIO service:

bundle exec legionio_migrate

Role in LegionIO

legion-data is optional but provides core platform persistence. It initializes during Legion::Service startup (after transport). Key responsibilities:

  1. Extension and function registry
  2. Task scheduling, logging, and relationship chains
  3. Node cluster membership tracking
  4. Persistent settings storage
  5. Digital worker registry (AI-as-labor platform)
  6. RBAC assignment tables
  7. Audit log with tamper-evident hash chain
  8. Governance event store with append-only integrity
  9. Apollo shared knowledge store (PostgreSQL + pgvector, used by lex-apollo)
  10. Local SQLite for agentic cognitive state — always on-node, independent of shared DB
  11. Financial logging for UAIS cost recovery
  12. Global tool embedding cache (L4 tier for Legion::Tools::EmbeddingCache)
  13. Unified identity system (providers, principals, identities, groups)
  14. LLM lifecycle ledger for audit, finance metrics, routing reconstruction, tool calls, and security incident lineage

Contributing

git clone https://github.com/LegionIO/legion-data
cd legion-data
bundle install
bundle exec rspec --format json --out tmp/rspec_results.json --format progress --out tmp/rspec_progress.txt
bundle exec rubocop -A   # zero offenses expected

The full suite defaults to SQLite. Set LEGION_DATA_ADAPTER=postgres plus LEGION_DATA_HOST, LEGION_DATA_PORT, LEGION_DATA_USER, LEGION_DATA_PASSWORD, and LEGION_DATA_DATABASE to exercise shared models and migrations against a disposable PostgreSQL database. PostgreSQL 16+ needs vector and uuid-ossp available, and the test role needs extension-installation and CREATEDB privileges. Apollo examples create and drop uniquely named databases and apply the real chain through 139 before exercising 140–150. PostgreSQL setup prohibits SQLite fallback and verifies the actual adapter. Intentionally SQLite-specific unit fixtures and local cognitive storage remain SQLite.

Always use a fresh temporary HOME, LEGION_DNS_BOOTSTRAP=false, and the real RBENV_ROOT when using rbenv. SQLite test setup deletes its database under HOME.

The existing ci.yml workflow retains its jobs, including PostgreSQL 16 with pgvector. The additional database-migrations.yml covers SQLite and PostgreSQL 17/pgvector on Ruby 3.4 and 4.0, verifies the backend/schema, runs the full suite, and uploads results.

This repo also includes a pre-commit configuration:

pre-commit install
pre-commit run --all-files

The local RuboCop hook auto-corrects staged Ruby files when RuboCop is available and fails the commit when RuboCop reports real offenses. The Ruby syntax hook checks every staged Ruby file.

Follow the LegionIO contribution guide. Open a PR against main.


Maintained by: Matthew Iverson (@Esity)

About

Persistence layer for LegionIO: SQLite, PostgreSQL, and MySQL via Sequel.

Topics

Resources

Code of conduct

Contributing

Security policy

Stars

1 star

Watchers

1 watching

Forks

Releases

Packages

Used by

Contributors

Languages