ext_tables.sql

#
# Table structure for table 'tx_nrllm_provider'
# API provider connections (endpoint, credentials, adapter type)
#
CREATE TABLE tx_nrllm_provider (
    uid int(11) NOT NULL auto_increment,
    pid int(11) DEFAULT '0' NOT NULL,

    -- Identity
    identifier varchar(100) DEFAULT '' NOT NULL,
    name varchar(255) DEFAULT '' NOT NULL,
    description text,

    -- Connection settings
    adapter_type varchar(50) DEFAULT '' NOT NULL,

    -- Where this provider's inference happens, as declared by the operator
    -- (ADR-094). Governs the most sensitive tool output a run reaching it may
    -- collect. Empty resolves to the strictest zone, so an un-migrated row can
    -- never widen the gate.
    trust_zone varchar(20) DEFAULT '' NOT NULL,
    endpoint_url varchar(500) DEFAULT '' NOT NULL,
    api_key varchar(500) DEFAULT '' NOT NULL,
    organization_id varchar(100) DEFAULT '' NOT NULL,

    -- Request settings (for API operations like list models, test connection)
    api_timeout int(11) DEFAULT '120' NOT NULL,
    max_retries int(11) DEFAULT '3' NOT NULL,

    -- Additional options (JSON)
    options text,

    -- Status and Priority
    is_active tinyint(1) DEFAULT '1' NOT NULL,
    priority int(11) DEFAULT '50' NOT NULL,
    sorting int(11) unsigned DEFAULT '0' NOT NULL,

    -- Standard TYPO3 fields
    tstamp int(11) unsigned DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    deleted tinyint(4) unsigned DEFAULT '0' NOT NULL,
    hidden tinyint(4) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    KEY identifier (identifier),
    KEY priority_active (priority, is_active)
);

#
# Table structure for table 'tx_nrllm_model'
# Available LLM models with capabilities and pricing
#
CREATE TABLE tx_nrllm_model (
    uid int(11) NOT NULL auto_increment,
    pid int(11) DEFAULT '0' NOT NULL,

    -- Identity
    identifier varchar(100) DEFAULT '' NOT NULL,
    name varchar(255) DEFAULT '' NOT NULL,
    description text,

    -- Provider relation
    provider_uid int(11) unsigned DEFAULT '0' NOT NULL,

    -- Model settings
    model_id varchar(150) DEFAULT '' NOT NULL,
    context_length int(11) unsigned DEFAULT '0' NOT NULL,
    max_output_tokens int(11) unsigned DEFAULT '0' NOT NULL,

    -- Embedding vector dimensionality (0 = unknown)
    dimensions int(11) unsigned DEFAULT '0' NOT NULL,

    -- Capabilities (comma-separated: chat,completion,embeddings,vision,streaming,tools)
    capabilities varchar(255) DEFAULT '' NOT NULL,

    -- Capability provenance (ADR-160): what the last provider discovery
    -- reported, when, and whether that answer was live or the bundled static
    -- catalog. A capability declared above but absent here is the operator's
    -- own claim; capabilities_confirmed_at = 0 means nothing ever confirmed
    -- any of them.
    capabilities_discovered varchar(255) DEFAULT '' NOT NULL,
    capabilities_confirmed_at int(11) unsigned DEFAULT '0' NOT NULL,
    capabilities_source varchar(20) DEFAULT '' NOT NULL,

    -- Default timeout for LLM inference (seconds, 0 = provider default)
    default_timeout int(11) DEFAULT '120' NOT NULL,

    -- Pricing (cents per 1M tokens)
    cost_input int(11) unsigned DEFAULT '0' NOT NULL,
    cost_output int(11) unsigned DEFAULT '0' NOT NULL,

    -- Status
    is_active tinyint(1) DEFAULT '1' NOT NULL,
    is_default tinyint(1) DEFAULT '0' NOT NULL,
    sorting int(11) unsigned DEFAULT '0' NOT NULL,

    -- Standard TYPO3 fields
    tstamp int(11) unsigned DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    deleted tinyint(4) unsigned DEFAULT '0' NOT NULL,
    hidden tinyint(4) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    KEY provider_uid (provider_uid),
    KEY identifier (identifier)
);

#
# Table structure for table 'tx_nrllm_configuration'
# Named LLM configuration presets with provider, model, parameters, and access control
#
CREATE TABLE tx_nrllm_configuration (
    uid int(11) NOT NULL auto_increment,
    pid int(11) DEFAULT '0' NOT NULL,

    -- Identity
    identifier varchar(100) DEFAULT '' NOT NULL,
    name varchar(255) DEFAULT '' NOT NULL,
    description text,

    -- Model relation (new multi-tier architecture)
    model_uid int(11) unsigned DEFAULT '0' NOT NULL,

    -- Dynamic model selection (criteria-based runtime selection)
    model_selection_mode varchar(20) DEFAULT 'fixed' NOT NULL,
    model_selection_criteria text,

    -- Translation service
    translator varchar(50) DEFAULT '' NOT NULL,
    system_prompt mediumtext,

    -- Operator-declared sensitivity ceiling for system_prompt (ADR-155), a
    -- ToolDataClass value on the same scale snippets and skills use. Declared
    -- here rather than refused per record: what a criteria-mode configuration
    -- may resolve to depends on it. EMPTY means undeclared and constrains
    -- nothing, so every existing row keeps reaching the providers it reached.
    system_prompt_data_class varchar(32) DEFAULT '' NOT NULL,

    -- Model parameters
    temperature decimal(3,2) DEFAULT '0.70' NOT NULL,
    max_tokens int(11) DEFAULT '1000' NOT NULL,
    top_p decimal(3,2) DEFAULT '1.00' NOT NULL,
    frequency_penalty decimal(3,2) DEFAULT '0.00' NOT NULL,
    presence_penalty decimal(3,2) DEFAULT '0.00' NOT NULL,
    timeout int(11) DEFAULT '0' NOT NULL,
    options text,

    -- Usage limits
    max_requests_per_day int(11) DEFAULT '0' NOT NULL,
    max_tokens_per_day int(11) DEFAULT '0' NOT NULL,
    max_cost_per_day decimal(10,2) DEFAULT '0.00' NOT NULL,

    -- Fallback chain (JSON list of configuration identifiers to try on retryable failures)
    fallback_chain text,

    -- SHA-256 checksum of the configuration preset this record was imported from ('' = not preset-imported)
    preset_checksum varchar(64) DEFAULT '' NOT NULL,

    -- Status
    is_active tinyint(1) DEFAULT '1' NOT NULL,
    is_default tinyint(1) DEFAULT '0' NOT NULL,

    -- Access control (MM relation to be_groups)
    allowed_groups int(11) DEFAULT '0' NOT NULL,

    -- Four-eyes approval (ADR-172): 1 refuses an approval from the backend user
    -- who started the run, so releasing a write needs a second person. 0 keeps
    -- the single-operator behaviour every existing row has.
    require_second_approver tinyint(1) DEFAULT '0' NOT NULL,

    -- Comma list of permitted tool groups (empty = all groups allowed)
    allowed_tool_groups varchar(255) DEFAULT '' NOT NULL,
    allowed_guardrails varchar(255) DEFAULT '' NOT NULL,

    -- Comma list of prompt-snippet tags composed into the system prompt (ADR-031)
    snippet_tags varchar(255) DEFAULT '' NOT NULL,

    -- Attached skills (MM relation to tx_nrllm_skill)
    skills int(11) DEFAULT '0' NOT NULL,

    -- Standard TYPO3 fields
    tstamp int(11) unsigned DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    deleted tinyint(4) unsigned DEFAULT '0' NOT NULL,
    hidden tinyint(4) unsigned DEFAULT '0' NOT NULL,
    sorting int(11) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    KEY model_uid (model_uid),
    KEY identifier (identifier)
);

#
# Table structure for table 'tx_nrllm_user_budget'
# Per-backend-user spending and request limits.
#
CREATE TABLE tx_nrllm_user_budget (
    uid int(11) NOT NULL auto_increment,
    pid int(11) DEFAULT '0' NOT NULL,

    -- Backend user reference
    be_user int(11) unsigned DEFAULT '0' NOT NULL,

    -- Daily limits (0 = unlimited)
    max_requests_per_day int(11) unsigned DEFAULT '0' NOT NULL,
    max_tokens_per_day int(11) unsigned DEFAULT '0' NOT NULL,
    max_cost_per_day decimal(10,4) DEFAULT '0.0000' NOT NULL,

    -- Monthly limits (0 = unlimited)
    max_requests_per_month int(11) unsigned DEFAULT '0' NOT NULL,
    max_tokens_per_month int(11) unsigned DEFAULT '0' NOT NULL,
    max_cost_per_month decimal(10,4) DEFAULT '0.0000' NOT NULL,

    -- Status
    is_active tinyint(1) DEFAULT '1' NOT NULL,

    -- Standard TYPO3 fields
    tstamp int(11) unsigned DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,
    deleted tinyint(4) unsigned DEFAULT '0' NOT NULL,
    hidden tinyint(4) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    -- No DB UNIQUE on be_user: a soft-deleted row keeps its be_user value in
    -- the index and would block re-creating a budget for that user. One budget
    -- per user is enforced in application logic (repository getFirst()).
    KEY be_user (be_user)
);

#
# MM table for backend user group access control
#
CREATE TABLE tx_nrllm_configuration_begroups_mm (
    uid_local int(11) unsigned DEFAULT '0' NOT NULL,
    uid_foreign int(11) unsigned DEFAULT '0' NOT NULL,
    sorting int(11) unsigned DEFAULT '0' NOT NULL,
    sorting_foreign int(11) unsigned DEFAULT '0' NOT NULL,

    KEY uid_local (uid_local),
    KEY uid_foreign (uid_foreign)
);

#
# Table structure for table 'tx_nrllm_task'
# One-shot prompt tasks for common operations (demonstration/utility)
#
CREATE TABLE tx_nrllm_task (
    uid int(11) NOT NULL auto_increment,
    pid int(11) DEFAULT '0' NOT NULL,

    -- Identity
    identifier varchar(100) DEFAULT '' NOT NULL,
    name varchar(255) DEFAULT '' NOT NULL,
    description text,

    -- Category for grouping
    category varchar(50) DEFAULT 'general' NOT NULL,

    -- Configuration relation
    configuration_uid int(11) unsigned DEFAULT '0' NOT NULL,

    -- Prompt template (with {{input}} placeholder)
    prompt_template mediumtext,

    -- Input configuration
    input_type varchar(50) DEFAULT 'manual' NOT NULL,
    input_source text,

    -- Output configuration
    output_format varchar(20) DEFAULT 'markdown' NOT NULL,

    -- Attached skills (MM relation to tx_nrllm_skill)
    skills int(11) DEFAULT '0' NOT NULL,

    -- Status
    is_active tinyint(1) DEFAULT '1' NOT NULL,
    is_system tinyint(1) DEFAULT '0' NOT NULL,
    sorting int(11) unsigned DEFAULT '0' NOT NULL,

    -- Standard TYPO3 fields
    tstamp int(11) unsigned DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    deleted tinyint(4) unsigned DEFAULT '0' NOT NULL,
    hidden tinyint(4) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    KEY configuration_uid (configuration_uid),
    -- No DB UNIQUE on identifier: a soft-deleted row keeps its identifier in
    -- the index and would block recreating/re-seeding a task with the same
    -- identifier. Uniqueness is enforced (delete-aware) via TCA eval=unique.
    KEY identifier (identifier),
    KEY category (category)
);

#
# Table structure for table 'tx_nrllm_promptsnippet'
# Tagged prompt fragments (personas, tones of voice, audiences, styles, layouts)
#
CREATE TABLE tx_nrllm_promptsnippet (
    uid int(11) NOT NULL auto_increment,
    pid int(11) DEFAULT '0' NOT NULL,

    -- Identity
    identifier varchar(100) DEFAULT '' NOT NULL,
    name varchar(255) DEFAULT '' NOT NULL,
    description text,

    -- Tagging (comma-separated free-form tags)
    tags varchar(255) DEFAULT '' NOT NULL,

    -- Operator-declared sensitivity ceiling (ADR-144). A ToolDataClass value,
    -- the same scale tool OUTPUT is classified on, so one trust zone answers
    -- both questions. EMPTY means undeclared, which is the migration-safe
    -- default: existing rows keep reaching every provider they reached before,
    -- and an operator opts in per record rather than being blocked
    -- retroactively by a value nobody set.
    data_class varchar(32) DEFAULT '' NOT NULL,

    -- Fragment content
    snippet text,

    -- Additional metadata (JSON object, e.g. {"voice":"nova"})
    metadata text,

    -- Status
    is_active tinyint(1) DEFAULT '1' NOT NULL,
    sorting int(11) unsigned DEFAULT '0' NOT NULL,

    -- Standard TYPO3 fields
    tstamp int(11) unsigned DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    deleted tinyint(4) unsigned DEFAULT '0' NOT NULL,
    hidden tinyint(4) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    KEY identifier (identifier),
    KEY active_sorted (is_active, sorting, name)
);

#
# Table for tracking specialized service usage (translation, speech, image)
#
CREATE TABLE tx_nrllm_service_usage (
    uid int(11) unsigned NOT NULL auto_increment,
    pid int(11) unsigned DEFAULT '0' NOT NULL,

    -- Service identification
    service_type varchar(50) DEFAULT '' NOT NULL,
    service_provider varchar(50) DEFAULT '' NOT NULL,
    configuration_uid int(11) unsigned DEFAULT '0' NOT NULL,

    -- Model dimension (usage analytics dashboard)
    model_uid int(11) unsigned DEFAULT '0' NOT NULL,
    model_id varchar(150) DEFAULT '' NOT NULL,

    -- Task dimension (per-task usage tracking)
    task_uid int(11) unsigned DEFAULT '0' NOT NULL,

    -- Caller dimension (ADR-178): the extension key a consumer named via
    -- withCallerSource(). Part of the daily aggregation key, so two
    -- extensions on the same model produce two rows. '' = unattributed.
    source_extension varchar(64) DEFAULT '' NOT NULL,

    -- User context
    be_user int(11) unsigned DEFAULT '0' NOT NULL,

    -- Usage metrics
    request_count int(11) unsigned DEFAULT '0' NOT NULL,
    tokens_used int(11) unsigned DEFAULT '0' NOT NULL,
    prompt_tokens int(11) unsigned DEFAULT '0' NOT NULL,
    completion_tokens int(11) unsigned DEFAULT '0' NOT NULL,
    characters_used int(11) unsigned DEFAULT '0' NOT NULL,
    audio_seconds_used int(11) unsigned DEFAULT '0' NOT NULL,
    images_generated int(11) unsigned DEFAULT '0' NOT NULL,

    -- Cost tracking. Width aligned with the budget-ceiling columns
    -- (max_cost_per_day/month) so an aggregated daily cost cannot overflow the
    -- column before it can be compared against a configured budget ceiling.
    estimated_cost decimal(14,6) DEFAULT '0.000000' NOT NULL,

    -- Time tracking
    request_date int(11) unsigned DEFAULT '0' NOT NULL,

    -- Standard TYPO3 fields
    tstamp int(11) unsigned DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    KEY lookup (service_type, service_provider, request_date, model_uid, model_id),
    KEY user_lookup (be_user, service_type, request_date),
    KEY config_lookup (configuration_uid, request_date),
    KEY model_lookup (model_uid, request_date),
    KEY task_lookup (task_uid, request_date),
    -- Leading request_date index for the analytics/dashboard read paths that
    -- filter by a date-range window only (no leading dimension) — the other
    -- composite indexes cannot serve those range scans.
    KEY request_date (request_date)
);

#
# Table structure for table 'tx_nrllm_skill_source'
# GitHub-hosted skill sources (single SKILL.md, repo, or marketplace index)
#
CREATE TABLE tx_nrllm_skill_source (
    uid int(11) NOT NULL auto_increment,
    pid int(11) DEFAULT '0' NOT NULL,

    -- Identity
    title varchar(255) DEFAULT '' NOT NULL,
    type varchar(20) DEFAULT 'single_file' NOT NULL,

    -- Source location
    url varchar(2048) DEFAULT '' NOT NULL,
    ref varchar(255) DEFAULT '' NOT NULL,
    pinned_sha varchar(64) DEFAULT '' NOT NULL,

    -- Credentials (nr-vault UUID, never plaintext)
    github_token varchar(64) DEFAULT '' NOT NULL,

    -- Publisher trust + manifest fingerprint (ADR-061)
    trust_level varchar(20) DEFAULT 'untrusted' NOT NULL,
    expected_fingerprint varchar(64) DEFAULT '' NOT NULL,

    -- Sync state
    sync_status varchar(20) DEFAULT 'never_synced' NOT NULL,
    sync_error text,
    last_synced int(11) unsigned DEFAULT '0' NOT NULL,

    -- Status
    enabled tinyint(1) DEFAULT '1' NOT NULL,

    -- Standard TYPO3 fields
    tstamp int(11) unsigned DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    deleted tinyint(4) unsigned DEFAULT '0' NOT NULL,
    hidden tinyint(4) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    KEY type (type)
);

#
# Table structure for table 'tx_nrllm_skill'
# Parsed SKILL.md records materialized from a skill source
#
CREATE TABLE tx_nrllm_skill (
    uid int(11) NOT NULL auto_increment,
    pid int(11) DEFAULT '0' NOT NULL,

    -- Source relation
    source int(11) unsigned DEFAULT '0' NOT NULL,

    -- Identity
    identifier varchar(512) DEFAULT '' NOT NULL,
    name varchar(255) DEFAULT '' NOT NULL,
    description text,

    -- Content
    body mediumtext,
    body_checksum varchar(64) DEFAULT '' NOT NULL,
    source_sha varchar(64) DEFAULT '' NOT NULL,
    raw_frontmatter text,

    -- Support assessment
    support_status varchar(20) DEFAULT 'full' NOT NULL,
    unsupported_notes text,
    allowed_tools text,

    -- Isolation metadata (ADR-061): trust denormalized from the source,
    -- prompt-injection scan findings recorded at ingest.
    trust_level varchar(20) DEFAULT 'untrusted' NOT NULL,
    injection_scan text,

    -- Operator-declared sensitivity ceiling (ADR-144), a ToolDataClass value.
    -- A SECOND axis from trust_level, not a replacement: trust_level says who
    -- wrote this skill, data_class says how sensitive what it carries is. A
    -- first-party skill can still hold confidential material. EMPTY means
    -- undeclared and cannot block, so ingested skills keep working untouched.
    data_class varchar(32) DEFAULT '' NOT NULL,

    -- Lifecycle
    orphaned tinyint(1) DEFAULT '0' NOT NULL,
    enabled tinyint(1) DEFAULT '0' NOT NULL,

    -- Standard TYPO3 fields
    tstamp int(11) unsigned DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    deleted tinyint(4) unsigned DEFAULT '0' NOT NULL,
    hidden tinyint(4) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    KEY source (source),
    KEY identifier (identifier)
);

#
# MM table for task ↔ skill relations
#
CREATE TABLE tx_nrllm_task_skill_mm (
    uid_local int(11) unsigned DEFAULT '0' NOT NULL,
    uid_foreign int(11) unsigned DEFAULT '0' NOT NULL,
    sorting int(11) unsigned DEFAULT '0' NOT NULL,
    sorting_foreign int(11) unsigned DEFAULT '0' NOT NULL,

    KEY uid_local (uid_local),
    KEY uid_foreign (uid_foreign)
);

#
# MM table for configuration ↔ skill relations
#
CREATE TABLE tx_nrllm_configuration_skill_mm (
    uid_local int(11) unsigned DEFAULT '0' NOT NULL,
    uid_foreign int(11) unsigned DEFAULT '0' NOT NULL,
    sorting int(11) unsigned DEFAULT '0' NOT NULL,
    sorting_foreign int(11) unsigned DEFAULT '0' NOT NULL,

    KEY uid_local (uid_local),
    KEY uid_foreign (uid_foreign)
);

#
# Table structure for table 'tx_nrllm_tool_state'
# Global per-tool enable/disable overrides for the agent tool runtime.
# Managed via the Tool Playground module toggles (no FormEngine UI / no TCA);
# a missing row means "use the tool's isEnabledByDefault()".
#
CREATE TABLE tx_nrllm_tool_state (
    uid int(11) NOT NULL auto_increment,
    pid int(11) DEFAULT '0' NOT NULL,

    tool_name varchar(190) DEFAULT '' NOT NULL,
    enabled smallint(5) unsigned DEFAULT '1' NOT NULL,

    PRIMARY KEY (uid),
    UNIQUE KEY tool_name (tool_name)
);

#
# Table structure for table 'tx_nrllm_tool_group_state'
# Global per-GROUP enable/disable overrides for the agent tool runtime.
# Managed via the Tools module group toggles (no FormEngine UI / no TCA);
# a missing row means "group enabled". A disabled group also covers tools
# of that group installed later — the runtime gate is fail-closed.
#
CREATE TABLE tx_nrllm_tool_group_state (
    uid int(11) NOT NULL auto_increment,
    pid int(11) DEFAULT '0' NOT NULL,

    group_name varchar(190) DEFAULT '' NOT NULL,
    enabled smallint(5) unsigned DEFAULT '1' NOT NULL,

    PRIMARY KEY (uid),
    UNIQUE KEY group_name (group_name)
);

#
# Table structure for table 'tx_nrllm_telemetry'
# One row per provider middleware pipeline run (ADR-058). Unlike
# tx_nrllm_service_usage (a daily cost AGGREGATE) this is a per-request log:
# every run appends exactly one immutable row, success or failure. It stores
# only cross-cutting observability fields — NO prompts, NO responses, and the
# exception FQCN (error_class) rather than the message, because messages can
# carry payload fragments. Growth is bounded by the nrllm:telemetry:purge
# command. No TCA / backend UI (reads happen via SQL / analytics), matching
# the other UI-less log tables in this extension.
#
CREATE TABLE tx_nrllm_telemetry (
    uid int(11) unsigned NOT NULL auto_increment,
    pid int(11) unsigned DEFAULT '0' NOT NULL,

    -- Trace correlation (UUID v4, RFC 4122 = 36 chars)
    correlation_id varchar(36) DEFAULT '' NOT NULL,

    -- What ran: operation kind + the requested primary configuration
    operation varchar(32) DEFAULT '' NOT NULL,
    provider varchar(64) DEFAULT '' NOT NULL,
    model varchar(128) DEFAULT '' NOT NULL,
    configuration_identifier varchar(150) DEFAULT '' NOT NULL,

    -- What ANSWERED: the configuration that actually served the run. Equal to
    -- the requested triple above whenever no fallback swap happened, so a row
    -- always names its serving configuration without a COALESCE. A failed run
    -- that nothing served keeps the requested values here — "served" is only
    -- ever a configuration that produced a response. Rows written before these
    -- columns existed carry '' and are therefore not mistakable for a rescue.
    served_configuration_identifier varchar(150) DEFAULT '' NOT NULL,
    served_provider varchar(64) DEFAULT '' NOT NULL,
    served_model varchar(128) DEFAULT '' NOT NULL,

    -- Attribution (backend user; 0 for CLI / scheduler / unauthenticated)
    be_user int(11) unsigned DEFAULT '0' NOT NULL,

    -- Caller identity (ADR-177): which piece of software made the call and
    -- which operation inside it (e.g. 'ai_seo_helper' / 'requestAi'), set via
    -- AbstractOptions::withCallerSource(). '' = unannotated call — identical
    -- to a pre-feature row on purpose.
    source_extension varchar(64) DEFAULT '' NOT NULL,
    source_operation varchar(64) DEFAULT '' NOT NULL,

    -- Outcome. error_class is the exception FQCN on failure ('' on success);
    -- the message is deliberately NOT stored (privacy).
    success smallint(5) unsigned DEFAULT '0' NOT NULL,
    error_class varchar(255) DEFAULT '' NOT NULL,

    -- Wall-clock latency around the whole pipeline (includes cache lookup)
    latency_ms int(11) unsigned DEFAULT '0' NOT NULL,

    -- Pipeline signals collected on the way out
    cache_hit smallint(5) unsigned DEFAULT '0' NOT NULL,
    fallback_attempts smallint(5) unsigned DEFAULT '0' NOT NULL,

    -- Time-to-first-token for streamed runs (ADR-062). NULL for every
    -- non-streaming run — there is no partial-response milestone to measure —
    -- which is deliberately distinct from a real 0 ms first token.
    time_to_first_token_ms int(11) unsigned DEFAULT NULL,

    -- Why THIS model, for a call that already happened (ADR-156). Written only
    -- for a criteria-mode configuration: routing_policy_mode is '' whenever no
    -- automatic selection took place (fixed mode names its own model, and the
    -- service paths resolve no configuration at all), which is what separates
    -- "no decision" from "a decision with nothing in it".
    --
    -- The selected model and whether a fallback ran are NOT repeated here —
    -- served_model and fallback_attempts above already carry them.
    --
    -- Prompt-free like the rest of the row: a mode name, a count, enum names
    -- and three booleans. The candidate MODELS are deliberately not stored;
    -- the Governance tab answers "which models exist and which lost" against
    -- the live catalogue, and a per-request copy would go stale on a rename.
    routing_policy_mode varchar(32) DEFAULT '' NOT NULL,
    routing_candidates smallint(5) unsigned DEFAULT '0' NOT NULL,
    -- Comma-separated DISTINCT RoutingRejectionReason names, sorted, so the
    -- same outcome always reads as the same string.
    routing_rejections varchar(255) DEFAULT '' NOT NULL,
    -- Whether the signal both carried weight in the mode AND had data for an
    -- eligible candidate — i.e. whether it moved this decision at all.
    routing_signal_quality smallint(5) unsigned DEFAULT '0' NOT NULL,
    routing_signal_health smallint(5) unsigned DEFAULT '0' NOT NULL,
    routing_signal_cost smallint(5) unsigned DEFAULT '0' NOT NULL,

    -- How involved the request was (ADR-156). OBSERVATION ONLY: nothing routes
    -- on these columns, and ADR-156 names the three things that must hold
    -- before anything is allowed to. Sizes and counts, never content.
    --
    -- complexity_shape is '' on the paths that measure nothing: the provider-
    -- pinned entry points (chatWithTools(), vision(), and chat()/complete()
    -- with no default configuration), embeddings by identifier, and every
    -- specialized service — image, speech and translation alike, all of which
    -- run through this pipeline and so reach this row. Only a
    -- configuration-driven chat, completion, tool or stream send runs the
    -- context fit the measurement
    -- hangs off. The empty shape is the flag for "this row carries no
    -- complexity", the way routing_policy_mode is for the decision, and the
    -- Governance readout reads it before showing the other five figures.
    complexity_score smallint(5) unsigned DEFAULT '0' NOT NULL,
    complexity_payload_bytes int(11) unsigned DEFAULT '0' NOT NULL,
    -- NULL where no context fit ran, which is not the same as an empty send.
    complexity_tokens int(11) unsigned DEFAULT NULL,
    complexity_tools smallint(5) unsigned DEFAULT '0' NOT NULL,
    -- Estimated tokens as a percentage of the model's budget; NULL like the
    -- token count above. May exceed 100 — that is the ADR-143 overflow case,
    -- and clamping it here would hide it.
    complexity_context_percent int(11) unsigned DEFAULT NULL,
    complexity_shape varchar(32) DEFAULT '' NOT NULL,

    -- What the request WAS, measured BEFORE any model was chosen (ADR-174).
    -- The complexity group above is measured after the model is chosen and
    -- partly FROM it (its size term is estimated tokens against the budget of
    -- the model already selected), so it can describe a decision but never
    -- inform one. This group is model-independent by construction: no window,
    -- no price, no utilisation, no chosen model. The operation is not repeated
    -- either — `operation` above already names it.
    --
    -- OBSERVATION ONLY, exactly like the complexity group: nothing routes on
    -- these columns, and ADR-156 still names what must hold before anything may.
    --
    -- The counts describe the CALLER's transcript, not the bytes on the wire:
    -- the configuration's system prompt is prepended later, from options built
    -- out of the resolved model, so counting it would mean measuring after the
    -- decision. The complexity group draws the same line, which is what keeps
    -- the two comparable.
    --
    -- All five figures are measured in one pass or not at all, so they are NULL
    -- together on every path that forms no fact set — only the four
    -- configuration-driven sends (chat, completion, tools, stream) form one, so
    -- the provider-pinned entry points, embeddings and every specialized
    -- service (image, speech and translation alike) write NULL here — and
    -- facts_shape is '' there, the same "nothing was measured" flag
    -- complexity_shape is. NULL rather than 0 throughout: a request nobody
    -- measured is not a request with no messages.
    facts_messages smallint(5) unsigned DEFAULT NULL,
    facts_turns smallint(5) unsigned DEFAULT NULL,
    facts_tools smallint(5) unsigned DEFAULT NULL,
    facts_payload_bytes int(11) unsigned DEFAULT NULL,
    -- Provider- and model-independent, from the same estimator the context fit
    -- uses but uncalibrated — so it can be compared with complexity_tokens
    -- without being derived from the model that answered.
    facts_token_estimate int(11) unsigned DEFAULT NULL,
    facts_shape varchar(32) DEFAULT '' NOT NULL,

    -- What the call actually consumed, and what it cost (ADR-174).
    -- tx_nrllm_service_usage carries the same figures as a DAILY AGGREGATE with
    -- no correlation_id, so nothing there can be joined to a call, a request
    -- shape or a complexity bucket. These columns can, because they sit on the
    -- row the correlation id already identifies.
    --
    -- NULL is a measurement that did not happen and is never a zero. Both token
    -- columns are NULL where the provider reported no usage block at all
    -- (adapters build UsageStatistics with plain ints, so all three counts zero
    -- at once is the only signal an absent block leaves). actual_cost is NULL
    -- where the serving model carries no pricing — which is the defect these
    -- columns exist to fix: a free local model and an unpriced one both used to
    -- report a cost of 0, and the free arm is precisely what a cheap-model
    -- experiment is about.
    --
    -- All four are NULL/'' wherever no provider call produced a token-shaped
    -- response: a cache hit (Cache short-circuits above Usage), a failed run, a
    -- streamed run (no usage block, only a char estimate — which is not what
    -- "actual" means), and the specialized image/speech/translation operations
    -- that record through their own extractors.
    actual_input_tokens int(11) unsigned DEFAULT NULL,
    actual_output_tokens int(11) unsigned DEFAULT NULL,
    -- Dollars. decimal, not float: a per-call cost of a cheap model is small
    -- enough that binary rounding shows up once these are summed.
    actual_cost decimal(14,8) DEFAULT NULL,
    -- The model the PROVIDER named on the response, which need not be the one
    -- the configuration asked for: an alias can resolve to a dated snapshot,
    -- and which one answered is the joinable fact. '' where none was named.
    response_model varchar(128) DEFAULT '' NOT NULL,
    -- HTTP attempts an adapter had to repeat during this run, fallback re-sends
    -- included. Unlike the columns above, this one is always written: both write
    -- sites hold the counter, so a recorded 0 is a measured "no retry" and no
    -- row from this version reports NULL here. Nullable because the rows that
    -- existed before the column did have no value for it — which is the only
    -- population `provider_retries IS NULL` selects.
    provider_retries smallint(5) unsigned DEFAULT NULL,

    -- Standard TYPO3 field (append-only; no tstamp — rows are never updated)
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    -- Fetch every hop of one traced call.
    KEY correlation (correlation_id),
    -- Purge and time-range analytics scan by crdate only.
    KEY crdate (crdate),
    -- Failure-rate / latency breakdowns filter by operation over a window.
    KEY operation_lookup (operation, crdate),
    -- "which provider fails most" breakdowns.
    KEY provider_lookup (provider, success, crdate),
    -- The Governance tab's routed-call reader: newest rows that carry a
    -- decision, within a window. Without the mode in the key an installation
    -- whose configurations are all fixed-mode would scan the whole window to
    -- find nothing.
    KEY routed_lookup (routing_policy_mode, crdate)
);

#
# Table structure for table 'tx_nrllm_skill_audit'
# Append-only provenance trail for skill ingest/enable/disable (ADR-061).
# Written only by SkillAuditRepository::record() (INSERT); the application has
# no update/delete path. No TCA (UI-less log, like tx_nrllm_tool_state), no
# soft-delete column — crdate is the immutable event time.
#
CREATE TABLE tx_nrllm_skill_audit (
    uid int(11) NOT NULL auto_increment,
    pid int(11) DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    event varchar(40) DEFAULT '' NOT NULL,
    source_uid int(11) unsigned DEFAULT '0' NOT NULL,
    skill_identifier varchar(512) DEFAULT '' NOT NULL,
    source_sha varchar(64) DEFAULT '' NOT NULL,
    body_checksum varchar(64) DEFAULT '' NOT NULL,
    trust_level varchar(20) DEFAULT 'untrusted' NOT NULL,
    scan_result text,
    actor_uid int(11) unsigned DEFAULT '0' NOT NULL,
    detail text,

    PRIMARY KEY (uid),
    KEY parent (pid),
    KEY source_uid (source_uid),
    KEY event (event)
);

#
# Table structure for table 'tx_nrllm_eval_result'
# Quality-evaluation run results (ADR-060). One row per golden-set run
# against a model: aggregate pass rate / mean score plus a JSON snapshot of
# the per-prompt outcomes. UI-less result log (no TCA), like
# tx_nrllm_service_usage; written only by the nrllm:eval:run command and read
# for regression comparison and quality-aware routing. Grows per run;
# retention is a documented follow-up (ADR-060).
#
CREATE TABLE tx_nrllm_eval_result (
    uid int(11) unsigned NOT NULL auto_increment,
    pid int(11) unsigned DEFAULT '0' NOT NULL,

    -- Run identity
    set_identifier varchar(190) DEFAULT '' NOT NULL,
    model_id varchar(150) DEFAULT '' NOT NULL,
    grader varchar(50) DEFAULT '' NOT NULL,

    -- Aggregate metrics (pass_rate / mean_score normalised 0.0000-1.0000)
    prompt_count int(11) unsigned DEFAULT '0' NOT NULL,
    passed_count int(11) unsigned DEFAULT '0' NOT NULL,
    pass_rate decimal(5,4) DEFAULT '0.0000' NOT NULL,
    mean_score decimal(5,4) DEFAULT '0.0000' NOT NULL,

    -- Per-prompt outcomes snapshot (JSON) for later inspection
    details mediumtext,

    -- Time tracking
    run_date int(11) unsigned DEFAULT '0' NOT NULL,

    -- Standard TYPO3 fields
    tstamp int(11) unsigned DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    -- Regression read path: latest run(s) for a (set, model), newest first.
    KEY set_model (set_identifier, model_id, run_date),
    -- Quality-routing read path: latest runs per set for a model.
    KEY model_lookup (model_id, run_date)
);

#
# Persisted agent runs (ADR-081): one row per ToolLoopService run, promoted from
# the in-memory RunTrace so runs survive the request. UI-less append-and-update
# log, mirroring tx_nrllm_telemetry (no TCA, no Extbase).
#
CREATE TABLE tx_nrllm_agentrun (
    uid int(11) unsigned NOT NULL auto_increment,
    pid int(11) unsigned DEFAULT '0' NOT NULL,

    uuid varchar(36) DEFAULT '' NOT NULL,
    status varchar(32) DEFAULT 'queued' NOT NULL,
    configuration_uid int(11) unsigned DEFAULT '0' NOT NULL,
    configuration_identifier varchar(150) DEFAULT '' NOT NULL,
    be_user int(11) unsigned DEFAULT '0' NOT NULL,

    -- Outcome
    iterations int(11) unsigned DEFAULT '0' NOT NULL,
    truncated smallint(5) unsigned DEFAULT '0' NOT NULL,
    total_prompt_tokens int(11) unsigned DEFAULT '0' NOT NULL,
    total_completion_tokens int(11) unsigned DEFAULT '0' NOT NULL,
    total_tokens int(11) unsigned DEFAULT '0' NOT NULL,
    estimated_cost decimal(10,6) DEFAULT '0.000000' NOT NULL,
    error_class varchar(255) DEFAULT '' NOT NULL,

    -- Why the run ended (ADR-092). The status says what state the run is in,
    -- this says how it got there: an iteration cap and an exhausted budget both
    -- surface as completed-but-truncated and are otherwise indistinguishable.
    termination_reason varchar(32) DEFAULT '' NOT NULL,

    -- Resumable state (ADR-084): serialised transcript + pending tool calls while
    -- status = waiting_for_approval; empty otherwise.
    suspended_state mediumtext,

    -- Queued execution (ADR-102): the serialised AgentRunRequest while
    -- status = queued, so a worker in another process can rehydrate and run it.
    -- Cleared by the guarded terminal settle, like suspended_state.
    queued_request mediumtext,

    -- Worker lease (ADR-102): who claimed the queued run and until when the
    -- claim is presumed live. ''/0 = not claimed — the fail-safe default, so an
    -- un-migrated row can never look leased. Written by the atomic
    -- QUEUED -> RUNNING claim; the stale-run reaper epic reads lease_expires.
    claimed_by varchar(64) DEFAULT '' NOT NULL,
    lease_expires int(11) unsigned DEFAULT '0' NOT NULL,

    -- Requeue budget (ADR-104): how many times this run was requeued for a
    -- retryable failure or a stale-lease reclaim. Capped so a deterministically
    -- failing run cannot loop forever; 0 = never requeued.
    requeue_count int(11) unsigned DEFAULT '0' NOT NULL,

    -- Side effect of the tool currently executing (ADR-111), stamped just BEFORE
    -- a tool runs on the queued path and cleared once it completes. '' = no tool
    -- in flight. The reaper reads it: a run reaped mid non-idempotent-write is
    -- dead-lettered instead of requeued, so an at-least-once retry never repeats
    -- a write that already ran once. Empty is the fail-safe default.
    pending_effect varchar(32) DEFAULT '' NOT NULL,

    -- Time tracking
    started_at int(11) unsigned DEFAULT '0' NOT NULL,
    finished_at int(11) unsigned DEFAULT '0' NOT NULL,

    -- Standard TYPO3 fields
    tstamp int(11) unsigned DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    KEY run_uuid (uuid),
    KEY be_user (be_user, crdate),
    KEY status_lookup (status, crdate),
    KEY crdate (crdate)
);

#
# Agent-run event stream (ADR-081): the durable, replayable form of each
# RunStep, one row per step, ordered by sequence within a run.
#
CREATE TABLE tx_nrllm_agentrun_event (
    uid int(11) unsigned NOT NULL auto_increment,
    pid int(11) unsigned DEFAULT '0' NOT NULL,

    run int(11) unsigned DEFAULT '0' NOT NULL,
    sequence int(11) unsigned DEFAULT '0' NOT NULL,
    kind varchar(32) DEFAULT '' NOT NULL,
    round int(11) unsigned DEFAULT '0' NOT NULL,
    duration_ms decimal(10,2) DEFAULT '0.00' NOT NULL,
    payload mediumtext,

    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    -- Replay read path: a run's events in order.
    KEY run_sequence (run, sequence),
    KEY crdate (crdate)
);

#
# Conversation sessions (ADR-083): one row per multi-turn conversation, so a
# stateful assistant can resume. UI-less append-and-update log (no TCA, no
# Extbase), mirroring tx_nrllm_telemetry.
#
CREATE TABLE tx_nrllm_ai_session (
    uid int(11) unsigned NOT NULL auto_increment,
    pid int(11) unsigned DEFAULT '0' NOT NULL,

    uuid varchar(36) DEFAULT '' NOT NULL,
    be_user int(11) unsigned DEFAULT '0' NOT NULL,
    configuration_identifier varchar(150) DEFAULT '' NOT NULL,
    title varchar(255) DEFAULT '' NOT NULL,
    message_count int(11) unsigned DEFAULT '0' NOT NULL,

    -- Retention is by inactivity: the purge deletes sessions whose last_activity
    -- predates the window.
    last_activity int(11) unsigned DEFAULT '0' NOT NULL,

    -- Standard TYPO3 fields
    tstamp int(11) unsigned DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    UNIQUE KEY session_uuid (uuid),
    KEY be_user (be_user, last_activity),
    KEY inactivity (last_activity)
);

#
# Conversation session messages (ADR-083): one row per turn, ordered by sequence
# within a session. `content` carries the prompt/reply text.
#
CREATE TABLE tx_nrllm_ai_session_message (
    uid int(11) unsigned NOT NULL auto_increment,
    pid int(11) unsigned DEFAULT '0' NOT NULL,

    session int(11) unsigned DEFAULT '0' NOT NULL,
    sequence int(11) unsigned DEFAULT '0' NOT NULL,
    role varchar(32) DEFAULT '' NOT NULL,
    content mediumtext,
    model varchar(128) DEFAULT '' NOT NULL,
    prompt_tokens int(11) unsigned DEFAULT '0' NOT NULL,
    completion_tokens int(11) unsigned DEFAULT '0' NOT NULL,
    total_tokens int(11) unsigned DEFAULT '0' NOT NULL,
    -- How many older turns were dropped from the transcript SENT for this turn
    -- so it would fit the model's context window (ADR-121). NULL means no fit
    -- was evaluated (a pre-feature row, or a turn without a bound
    -- configuration); 0 means it was evaluated and nothing had to go. The
    -- persisted history itself is never trimmed -- this records what the model
    -- saw, not what was kept.
    dropped_turns int(11) unsigned DEFAULT NULL,

    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    -- Replay read path: a session's turns in order.
    -- Unique, not merely indexed: it is the authority that decides which of two
    -- concurrent turns owns a sequence (ADR-083).
    UNIQUE KEY session_sequence (session, sequence)
);

#
# Table structure for table 'tx_nrllm_governance_event'
# Append-only governance/tool-decision audit trail. One row per denied or
# gated decision: a guardrail DENY/REQUIRE_APPROVAL, a provider content-filter
# block, or a tool-gate denial. Closes the gap that these outcomes were only
# reflected on a run (termination_reason) or logged, never queryable. Written
# only by GovernanceEventRepository::record() (INSERT); no update/delete path.
# No TCA, no soft-delete, no tstamp -- crdate is the immutable event time.
# Growth is bounded by a nrllm:governance:purge command (mirrors telemetry).
#
CREATE TABLE tx_nrllm_governance_event (
    uid int(11) unsigned NOT NULL auto_increment,
    pid int(11) unsigned DEFAULT '0' NOT NULL,

    -- Immutable event time (append-only; no tstamp).
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    -- Trace correlation (UUID v4, 36 chars) -- links a middleware-origin row to
    -- the telemetry rows of the same call. Inside an agent run that id IS the
    -- run's uuid (ADR-153), so the row joins to tx_nrllm_agentrun.uuid as well;
    -- outside a run it is a per-call id that joins to telemetry only. '' when
    -- the write point runs before the call context exists (the input-context
    -- gate) -- agentrun_uid below is that row's join key.
    correlation_id varchar(36) DEFAULT '' NOT NULL,

    -- What kind of decision: tool_denied | response_blocked | approval_required | content_filter
    decision varchar(32) DEFAULT '' NOT NULL,

    -- Why, as a raw enum value: ToolDenialReason (notRegistered/toolDisabled/
    -- requiresAdmin/configurationGroup/trustZone), GuardrailVerdict (deny/
    -- require_approval), or the 'content_filter' literal -- see the
    -- decision->reason mapping in the write points.
    reason varchar(64) DEFAULT '' NOT NULL,

    -- Provider context (strings, like telemetry -- resolved from the response /
    -- context, '' when unknown at the write point).
    provider varchar(64) DEFAULT '' NOT NULL,
    model varchar(128) DEFAULT '' NOT NULL,
    configuration_identifier varchar(150) DEFAULT '' NOT NULL,

    -- Attribution (backend user; 0 for CLI/scheduler/unauthenticated).
    be_user int(11) unsigned DEFAULT '0' NOT NULL,

    -- The tool this decision was about; '' for guardrail/content_filter rows.
    tool_name varchar(190) DEFAULT '' NOT NULL,

    -- The agent run this decision belongs to (tx_nrllm_agentrun.uid); 0 when the
    -- decision did not happen inside a run -- a plain provider call, or a bare
    -- tool-loop consumer driving the loop without a persisted run. All three
    -- write points stamp it since ADR-153: the tool gate from the run's
    -- execution context, the guardrail middleware and the input-context gate
    -- from the run identity the runtime threads down the pipeline.
    agentrun_uid int(11) unsigned DEFAULT '0' NOT NULL,

    -- The deciding guardrail FQCN for guardrail/content_filter rows ('' for
    -- tool_denied). Class name only -- never the reason string's payload
    -- fragments, matching telemetry's error_class privacy stance.
    guardrail varchar(255) DEFAULT '' NOT NULL,

    -- Optional short policy detail (trust zone, ceiling, ...). NEVER tool
    -- arguments/output or response content (ADR-064 privacy).
    detail text,

    PRIMARY KEY (uid),
    KEY parent (pid),
    -- Purge + time-range analytics scan by crdate only.
    KEY crdate (crdate),
    -- "governance blocks over time", broken down by kind.
    KEY decision_lookup (decision, crdate),
    -- "tool denials by reason" / "guardrail denials by reason".
    KEY reason_lookup (decision, reason, crdate),
    -- "tool usage/denials by tool_name".
    KEY tool_lookup (tool_name, crdate),
    -- Join a middleware-origin row to its trace.
    KEY correlation (correlation_id),
    -- The run-detail timeline (ADR-153) lists one run's decisions in time order.
    KEY agentrun (agentrun_uid, crdate)
);

#
# Table structure for table 'tx_nrllm_mcp_server'
# Operator-configured external MCP servers (ADR-116). nr_llm is the only MCP
# client in the stack; consumers reach these servers through ToolRegistry, never
# directly. HTTP is the sole transport, so there is no transport column.
#
CREATE TABLE tx_nrllm_mcp_server (
    uid int(11) NOT NULL auto_increment,
    pid int(11) DEFAULT '0' NOT NULL,

    -- Identity. The identifier prefixes every imported tool name and the group
    -- mcp_<identifier>, so it is part of the tool namespace: ^[a-z0-9_]{1,32}$.
    identifier varchar(32) DEFAULT '' NOT NULL,
    name varchar(255) DEFAULT '' NOT NULL,
    description text,

    -- Absolute http(s) MCP endpoint.
    url varchar(2048) DEFAULT '' NOT NULL,

    -- Credential (nr-vault UUID, never plaintext). Empty = unauthenticated server.
    auth_credential varchar(64) DEFAULT '' NOT NULL,
    auth_placement varchar(32) DEFAULT 'bearer' NOT NULL,
    auth_header_name varchar(190) DEFAULT '' NOT NULL,

    -- ToolDataClass every tool of this server is classified as (ADR-094). No
    -- default: a server whose class the operator has not declared is inert.
    data_class varchar(32) DEFAULT '' NOT NULL,

    -- Whether every tool of this server pauses for a human before the agent
    -- loop executes it (ADR-134). Operator-declared, like data_class above: the
    -- server's own `readOnlyHint` annotation is stored for display and read by
    -- nobody, because a remote server must not decide its own authorisation.
    -- Defaults to 1 -- a server nobody has judged asks first. Only a literal 0
    -- means "no approval"; McpServerRecord::approvalRequired() says why.
    requires_approval tinyint(1) DEFAULT '1' NOT NULL,

    -- Off until an operator switches it on.
    enabled tinyint(1) DEFAULT '0' NOT NULL,

    -- Import state
    import_status varchar(20) DEFAULT 'never_imported' NOT NULL,
    import_error text,
    last_imported int(11) unsigned DEFAULT '0' NOT NULL,
    tool_count int(11) unsigned DEFAULT '0' NOT NULL,

    -- Liveness (ADR-154). Written on every successful client round trip, not
    -- only on an import: a server answering tools/call for weeks used to read
    -- as untouched since its last catalogue import. 0 = never reached.
    last_contact int(11) unsigned DEFAULT '0' NOT NULL,
    -- Duration of the round trip that set last_contact, in milliseconds. Only
    -- meaningful together with it; 0 with last_contact = 0 means "no
    -- measurement", 0 with a contact means "under half a millisecond".
    last_latency_ms int(11) unsigned DEFAULT '0' NOT NULL,

    -- Standard TYPO3 fields
    tstamp int(11) unsigned DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    deleted tinyint(4) unsigned DEFAULT '0' NOT NULL,
    hidden tinyint(4) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    KEY identifier (identifier)
);

#
# Table structure for table 'tx_nrllm_mcp_tool'
# Tool catalogue imported from an MCP server, written only by the import action
# (no FormEngine UI / no TCA). Queries drop the default restrictions, like
# tx_nrllm_tool_state, so there is no deleted/hidden column.
#
# Per-tool enablement is NOT stored here: an imported tool is toggled through the
# existing tx_nrllm_tool_state override keyed by tool_name, the same switch a
# builtin tool uses.
#
CREATE TABLE tx_nrllm_mcp_tool (
    uid int(11) NOT NULL auto_increment,
    pid int(11) DEFAULT '0' NOT NULL,

    server int(11) unsigned DEFAULT '0' NOT NULL,

    -- Local name mcp_<identifier>_<remote>, <= 64 chars, ^[a-zA-Z0-9_-]+$. This
    -- is the ToolSpec name the model sees.
    tool_name varchar(190) DEFAULT '' NOT NULL,
    -- The name on the wire, sent verbatim in tools/call.
    remote_name varchar(190) DEFAULT '' NOT NULL,

    description text,
    -- Normalised JSON-Schema parameter object.
    input_schema mediumtext,
    -- Stored verbatim for display only; no resolver reads it.
    remote_annotations text,

    -- Present in an earlier import, absent from the latest. Kept so an operator
    -- sees what disappeared.
    orphaned tinyint(1) DEFAULT '0' NOT NULL,

    tstamp int(11) unsigned DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    KEY server (server),
    UNIQUE KEY tool_name (tool_name)
);

--
-- Table structure for table 'tx_nrllm_call_outcome'
--
-- How one call turned out (ADR-176). Joined to tx_nrllm_telemetry by
-- correlation_id, which is why neither the model nor the routing arm is
-- repeated here: they sit on the telemetry row already and a second copy could
-- only drift from it.
--
-- There is no be_user column, and none is coming. Who ran the call is on the
-- run record, because budgets and the resume authorisation need it there;
-- nr_llm ships no per-editor readout and builds no anonymisation of its own,
-- because erasure belongs to the installation's be_users lifecycle. The join
-- through the run still exists — that is stated rather than dressed up as
-- "we store no names".
--
-- The source (explicit or observed) is NOT a column. It is derived from the
-- outcome value, so a row carrying an explicit rating under an observed value
-- is not a state this table can hold.
--
CREATE TABLE tx_nrllm_call_outcome (
    uid int(11) NOT NULL auto_increment,
    pid int(11) unsigned DEFAULT '0' NOT NULL,

    -- Trace correlation (UUID v4, RFC 4122 = 36 chars), as on tx_nrllm_telemetry
    correlation_id varchar(36) DEFAULT '' NOT NULL,

    -- A CallOutcome case. Its enum decides which source it belongs to.
    outcome varchar(24) DEFAULT '' NOT NULL,

    crdate int(11) unsigned DEFAULT '0' NOT NULL,
    tstamp int(11) unsigned DEFAULT '0' NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    -- One row per call per source, so a rater who changes their mind replaces
    -- their answer instead of adding a second one and leaving the readout to
    -- guess which counts. The source is part of the key through the outcome
    -- value's own grouping, enforced by the repository rather than by SQL.
    KEY correlation (correlation_id),
    KEY outcome_lookup (outcome, crdate)
);