Dynamic Tool Registry: Managing Tools Without Hardcoding

tools (
  id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  technical_name              TEXT UNIQUE NOT NULL,
  -- e.g. "mcp-readonly-sql"
  visible_name                TEXT NOT NULL,
  -- e.g. "Read-only SQL Queries"
  type                        TEXT NOT NULL,
  -- mcp_http | mcp_stdio | cli | http_api | webhook_in | webhook_out
  transport                   TEXT NOT NULL,
  -- oauth | command | hmac | webhook | api
  endpoint                    TEXT,
  -- URL when type IN (mcp_http, http_api, webhook_in, webhook_out)
  command                     TEXT,
  -- binary name when type = cli
  config                      JSONB NOT NULL DEFAULT '{}',
  -- flags, SQL restrictions, headers, env key names, etc.
  state                       TEXT NOT NULL
    CHECK (state IN ('active', 'disabled', 'deprecated')),
  environment                 TEXT NOT NULL,
  -- dev | staging | prod
  operations                  JSONB NOT NULL DEFAULT '[]',
  -- [{name, description, input_schema, output_schema, scope}]
  required_scopes             TEXT[] NOT NULL DEFAULT '{}',
  -- scopes the tool requires globally
  risk_level                  TEXT NOT NULL,
  -- READ_ONLY | READ_SENSITIVE | WRITE | DESTRUCTIVE
  -- | ADMINISTRATIVE | EXTERNAL_SIDE_EFFECT
  timeout_ms                  INTEGER NOT NULL,
  retry_policy                JSONB NOT NULL DEFAULT '{}',
  -- {max_attempts, backoff, jitter}
  audit_policy                JSONB NOT NULL DEFAULT '{}',
  -- {level, include_args, include_result, retention_days}
  requires_human_confirmation BOOLEAN NOT NULL DEFAULT false,
  -- applied to WRITE / DESTRUCTIVE / EXTERNAL by default
  required_env_vars           TEXT[] NOT NULL DEFAULT '{}',
  -- env var names the tool needs (not the values)
  network_restrictions        JSONB NOT NULL DEFAULT '{}',
  -- {egress_allowlist, ingress_allowlist}
  filesystem_restrictions     JSONB NOT NULL DEFAULT '{}',
  -- {read_allowlist, write_allowlist}  (applies to type=cli)
  execution_restrictions      JSONB NOT NULL DEFAULT '{}',
  -- {cpu, memory, no_network, sandbox}  (applies to type=cli)
  version                     TEXT NOT NULL,
  created_at                  TIMESTAMP NOT NULL DEFAULT now(),
  updated_at                  TIMESTAMP NOT NULL DEFAULT now(),
  created_by                  TEXT NOT NULL
);

-- Named index aligned with the design contract (used in EXPLAIN plans):
CREATE INDEX ix_tools_technical_name ON tools (technical_name);

{
  "allowed_connection_profiles": [
    {
      "profile": "harness_readonly",
      "required_env_var": "MCP_READONLY_SQL_DATABASE_URL",
      "allowed_schemas": ["public_reporting"],
      "allowed_tables": [],
      "allowed_views": [
        "vw_expedientes_resumen",
        "vw_tramites_estado"
      ],
      "max_rows": 500,
      "query_timeout_ms": 5000,
      "response_size_limit_bytes": 1048576
    }
  ],
  "reject_multi_statement": true,
  "statement_allowlist": ["SELECT"],
  "statement_blocklist": [
    "INSERT", "UPDATE", "DELETE", "DROP", "ALTER",
    "TRUNCATE", "CREATE", "REPLACE", "MERGE", "CALL", "EXEC"
  ]
}

active  ──────────────►  deprecated  ──────────────►  disabled
  │                           │
  │  (incompatible change)     │  (migration window ends)
  │  creates new row            │  invocations respond:
  │  with new version           │  code=TOOL_NOT_ALLOWED
  ▼
active (new version)