# Database Schema Reference This document describes the database tables used by the CleverAgents tool and validation registry, introduced in migration `c1_001_tool_registry`. ## Tables ### `tools` Stores registered tool and validation definitions. | Column | Type | Constraints | Description | |-----------------|---------|-------------------------------------|------------------------------------------| | `tool_id` | TEXT | PRIMARY KEY | ULID identifier | | `name` | TEXT | NOT NULL, UNIQUE | Namespaced name (`namespace/short_name`) | | `description` | TEXT | | Human-readable description | | `tool_type` | TEXT | NOT NULL, CHECK (`tool`/`validation`) | Discriminator for registry queries | | `source_type` | TEXT | NOT NULL, CHECK (see below) | Implementation source | | `input_schema` | TEXT | | JSON Schema for tool inputs | | `output_schema` | TEXT | | JSON Schema for tool outputs | | `read_only` | BOOLEAN | NOT NULL, DEFAULT FALSE | Tool only reads, never writes | | `writes` | BOOLEAN | NOT NULL, DEFAULT FALSE | Tool can write to resources | | `checkpointable`| BOOLEAN | NOT NULL, DEFAULT FALSE | Tool supports checkpoint/rollback | | `side_effects` | BOOLEAN | NOT NULL, DEFAULT FALSE | Tool has known side effects | | `config_yaml` | TEXT | | Raw YAML config for reconstruction | | `created_at` | TEXT | NOT NULL | ISO-8601 timestamp | | `updated_at` | TEXT | NOT NULL | ISO-8601 timestamp | **Check constraints:** - `ck_tools_tool_type`: `tool_type IN ('tool', 'validation')` - `ck_tools_source_type`: `source_type IN ('mcp', 'agent_skill', 'builtin', 'custom', 'wrapped')` **Indexes:** - `ix_tools_name` on `(name)` - `ix_tools_tool_type` on `(tool_type)` --- ### `tool_bindings` Stores resource slot bindings for tools. | Column | Type | Constraints | Description | |----------------|---------|--------------------------------------|--------------------------------------| | `binding_id` | TEXT | PRIMARY KEY | ULID identifier | | `tool_id` | TEXT | NOT NULL, FK → `tools.tool_id` CASCADE | Parent tool reference | | `slot_name` | TEXT | NOT NULL | Named resource slot | | `resource_type`| TEXT | NOT NULL | Expected resource type | | `binding_mode` | TEXT | NOT NULL, CHECK (see below) | How the slot is resolved | | `access_level` | TEXT | NOT NULL, DEFAULT `read_only` | Access level on bound resource | | `created_at` | TEXT | NOT NULL | ISO-8601 timestamp | **Check constraints:** - `ck_tool_bindings_mode`: `binding_mode IN ('context', 'static', 'parameter')` - `ck_tool_bindings_access`: `access_level IN ('read_only', 'read_write')` **Unique constraints:** - `uq_tool_bindings_tool_slot`: `UNIQUE(tool_id, slot_name)` --- ### `validation_attachments` Links validations to resources with mode semantics. | Column | Type | Constraints | Description | |-------------------|---------|-----------------------------------------------|------------------------------------| | `attachment_id` | TEXT | PRIMARY KEY | ULID identifier | | `resource_id` | TEXT | NOT NULL, FK → `resources.resource_id` CASCADE | Target resource | | `validation_name` | TEXT | NOT NULL | References `tools.name` (validation)| | `mode` | TEXT | NOT NULL, DEFAULT `required` | `required` or `informational` | | `created_at` | TEXT | NOT NULL | ISO-8601 timestamp | **Check constraints:** - `ck_validation_attachments_mode`: `mode IN ('required', 'informational')` **Unique constraints:** - `uq_validation_attachments_resource_validation`: `UNIQUE(resource_id, validation_name)` **Indexes:** - `ix_validation_attachments_resource_id` on `(resource_id)` --- ## Relationships ``` tools 1 ──< tool_bindings (tool_id FK, CASCADE delete) resources 1 ──< validation_attachments (resource_id FK, CASCADE delete) validation_attachments.validation_name → tools.name (logical, not enforced by FK) ``` --- ### `actors` Stores actor configurations with YAML text retention, schema version tracking, and optional compiled metadata. | Column | Type | Constraints | Description | |---------------------|----------|-----------------------------|------------------------------------------------| | `id` | INTEGER | PRIMARY KEY, AUTOINCREMENT | Auto-generated row ID | | `name` | TEXT(255)| NOT NULL, UNIQUE | Namespaced name (`namespace/identifier`) | | `provider` | TEXT(255)| NOT NULL | Provider identifier (e.g. `openai`) | | `model` | TEXT(255)| NOT NULL | Model identifier (e.g. `gpt-4`) | | `config_blob` | JSON | NOT NULL, DEFAULT `{}` | Canonical actor configuration blob | | `config_hash` | TEXT(128)| NOT NULL | SHA-256 hash of `config_blob` | | `graph_descriptor` | JSON | | Adapter-produced graph descriptor | | `yaml_text` | TEXT | | Original YAML source text for the actor config | | `schema_version` | TEXT(20) | NOT NULL, DEFAULT `'1.0'` | Actor config schema version | | `compiled_metadata` | JSON | | Compiler-produced metadata (graph topology etc)| | `unsafe` | BOOLEAN | NOT NULL, DEFAULT FALSE | True when actor is marked unsafe | | `is_built_in` | BOOLEAN | NOT NULL, DEFAULT FALSE | Generated from provider registry | | `is_default` | BOOLEAN | NOT NULL, DEFAULT FALSE | Designated default actor | | `created_at` | DATETIME | NOT NULL | Row creation timestamp | | `updated_at` | DATETIME | NOT NULL | Last update timestamp | **Notes:** - `yaml_text` preserves the original YAML source so that actors can be reconstructed or exported without loss. - `schema_version` tracks which version of the actor YAML schema was used to create the configuration. Defaults to `1.0`. - `compiled_metadata` stores JSON output from the actor compiler (graph topology, resolved tools, etc.). - `name` follows the `namespace/identifier` convention enforced by the domain model validator (e.g. `local/my-actor`, `openai/gpt-4`). --- ## Migration - **Revision**: `c1_001_tool_registry` - **Depends on**: `b0_001_projects` - **File**: `alembic/versions/c1_001_tool_registry.py`