The External Queries feature enables Avvoka to connect directly to external databases and APIs, allowing templates to dynamically populate dropdown fields with live data from customer systems. This eliminates the need for a middleware and simplifies integrations with client databases like Aderant Expert, CRM systems, public registry databases (e.g: UK Companies House) and other data sources.
With External Queries, users can:
Create direct connections to external APIs from within Avvoka
Query external systems such as internal databases
Use real-time responses to automatically populate document fields
The External Queries feature allows Organisation Support users to set up connections to external data through either API integrations or SQL integrations. Once configured, onboard users can link them to the new “External Source” questionnaire input type.
Accessing External Queries
Go to Support > Organisations > Open the relevant organisation.
Scroll to External Queries and click to open.
Note: From this section, users can view existing connections or create new ones.
This article covers:
Functionality overview
The system uses a two-level structure to connect to and retrieve data from external sources:
1. Data Source (Connection to an external system)
A data source represents a connection to an external system and stores all the information needed to establish that connection:
Name: A descriptive identifier (e.g., "Client CRM Database")
Type: Either SQL Database or REST API
Connection details:
For SQL databases: host, port, database name, credentials (read-only database user recommended), and connection pool size
For APIs: base URL and authentication type (Bearer token, API key, OAuth2, or Basic Auth)
Organisation: Each source belongs to one organisation, ensuring data isolation
Ensure that your firewall and network settings allow Avvoka to access your database or API. You may need to whitelist Avvoka's IP addresses.
Alternatively, if you don’t wish to expose your database directly to Avvoka, you can consider using our new Middleware. This is a separately maintained miniature application that can be deployed separately from the Avvoka main application.
2. Query
A Query defines what data to retrieve from a source and how to use it:
Name: A descriptive identifier (e.g., "Active Matters by Client")
Source: Which Data Source to use
Query: The actual SQL query or API endpoint path
Parameters: Dynamic values from the questionnaire (e.g., client_id)
Outputs: Which fields to display in the questionnaire
Cache TTL: How long to cache results in seconds
All database connections and queries are managed by an Organisation Support user. Once configured, onboard users (i.e. template automators) can link them to the new “External Query” questionnaire input type.
Connection Options
Avvoka supports two primary connection types. Choose the option that aligns with the user’s system and security requirements.
1. SQL Integration
SQL Sources let users connect directly to a database and run SQL queries that return data to users templates. The setup process is similar to creating API Sources, with fields specific to database connections.
To create a SQL source, Open the SQL Sources > Create SQL Source.
Complete the following fields:
Name – Any name.
Description – Optional.
Host – Database host.
Port – Connection port.
Username – Database user.
Password – Database password.
Adapter – Choose the database type:
MySQL
SQL Server
PostgreSQL
Click Save.
Activating a SQL Source
Right-click the SQL Source name and select Activate.
Active sources display a green circle; inactive sources display a red circle.
Creating a SQL Query
Once your SQL Source is active, users can create a Query to retrieve and format data.
To create a SQL Query, Go to Queries → Create Query → fill in the fields.
Name – Any name.
Description – Optional.
Data Source – Select your SQL Source.
Query – Enter the SQL query.
Example: SELECT name FROM clients WHERE client_id = :client_id
Parameters are added using the colon syntax (:parameter).
Output Format:
In the Outputs field, choose the specific fields to display. Each field has:
Label – What the users see.
Value – The internal stored value.
Cache TTL:
Optionally define a number of minutes for caching. Leave empty to disable caching.
Activating the Query:
Right-click the Query and select Activate. Only active queries can be used inside templates.
2. Connect via an External API
API connections allow Avvoka to retrieve data from external systems, such as Companies House, Dutch Registry, or Finland Companies House, without the need for middleware or third-party tools.
To set up an API source, Open the API Sources tab > Create API Source.
Fill in these fields:
Name – Any name for this connection.
Description – Optional description.
Base URL – The root URL of the API (e.g.,
https://avvoka.com).Headers – Add as many headers as required by the API.
For authentication, add a header such as:Header name: Authorization
Value: Bearer
<token>
Click Save.
Activating an API Source
Right-click the API Source name and select Activate.
Active sources are indicated by a green circle, while inactive sources show a red circle.
Creating an API Query
If the users are using the Avvoka middleware, this step can be skipped. Instead, users can use the import method, where users paste the export from the middleware. After import, the query needs to be activated (right-click → Activate).
Once your API Source is active, users can create a Query that retrieves and formats the data from the API.
To create a Query, Open the Queries tab. > Click Create Query
Complete the fields:
Name – Any name.
Description – Optional.
Data Source – Select your API Source.
Query – The endpoint path (not the full URL).
Example: Endpoint → https://avvoka.com/path
Base URL → https://avvoka.com
Query → /path
Inputs – Add parameters required by the endpoint.
Variable example:
Endpoint: https://avvoka.com/path?input=xyz
Variable name: input
Parameters:
When defining a path, parameters are added with a colon.
Example: /example/:id/path?parameter=test
id → added in the path and as an input
parameter → added only as an input parameter
Output Format:
In the Outputs field, choose the specific fields shown to the users.
Each field has:
Label – What the users see.
Value – The internal stored value.
Example (nested structure): use fields like items.name
Cache TTL:
Optionally define a number of minutes for caching. Leave empty to disable caching.
Activating the Query:
Right-click the Query → Activate.
Only active queries can be used inside templates.
Use Queries in Templates
Once a database or API connection is set up and queries are created, users can use them in templates.
To use an External Query, Open your Template → Questionnaire tab
Select the input field for the query results.
Set Type to External Query.
Select the query user created (from your API or SQL source). Avvoka will indicate the expected input type.
Map query inputs to template fields and select outputs to display (e.g., company name, address, country, directors). Repeat for any additional outputs.
Note: Only active queries can be used in templates. Make sure your query is activated before mapping it.
Using Middleware
If direct database or API access is restricted, middleware can be used. Middleware retrieves data from a system and passes it securely to Avvoka.
Considerations:
Ensure middleware is hosted on a separate server.
Avvoka can assist with installation and configuration.
Maintain the middleware to ensure ongoing data access.
Note: Use middleware only if direct access is not available or additional validation is needed.
Security and data storage
Avvoka implements multiple security measures to protect your data:
No sensitive information in templates: Templates are designed to be secure and portable between environments, with no sensitive information stored within them. Templates only store the query ID and dependency mappings. All credentials remain server-side in encrypted database tables.
Encryption at rest: All connection details are encrypted using Lockbox encryption
Credential filtering: Credentials never appear in system logs or error messages
Organisation-scoped access: Data sources are isolated by organisation, preventing cross-organisation access
SQL injection prevention: Parameterised queries protect against malicious database commands
Read-only access recommended: Configure database connections with read-only permissions for additional protection
Sample flow
Example query:
Name | Client Matters
|
Source | Aderant Expert Production |
SQL | SELECT matter_id, matter_name FROM matters WHERE client_id = :client_id |
Parameters | client_id: (from questionnaire field "Client") |
Outputs | Value: matter_name - Label: matter_name
|
Cache | 300 seconds (5 minutes)
|
When a user completes a questionnaire with external data sources, the following process occurs:
The user fills out the questionnaire and selects a value (e.g., "Acme Corp" in the Client field)
A dependent dropdown (e.g., "Matter") detects the change in the Client field
Avvoka requests execution of the relevant query (e.g., "Execute 'Client Matters' query with client_id=123")
Avvoka checks if cached data is still valid (within the 5-minute cache window)
If the cache is stale, the system connects to the external database and runs the SQL query
The system returns the results (e.g., [{value: "Matter A", label: "Matter A", id: "456"}, ...])
The user sees the dropdown populated with only the relevant matters for Acme Corp