Ontologies
Map the relationships between your tables and the business entities they hold, so agents and AI queries join your data the way your business does.
Why Use an Ontology
An AI model that sees only table and column names has to guess how your data fits together. It cannot know that crm.accounts.external_ref and billing.customers.id are the same customer, that an order belongs to the customer in placed_by, or that a "Customer" lives in three tables across two databases.
An ontology writes that knowledge down once:
Customer = sales_db.customers (key: id), crm_db.accounts (key: external_ref)
orders.customer_id --[placed by]--> customers.id
Agents and AI queries then use those relationships when they join tables, instead of inferring joins from column names.
Overview
An ontology is a named model over one or more databases (and, optionally, knowledge bases with a graph). It holds three kinds of rows:
- Relationships between two tables: the join columns, the cardinality of each side, and a short business label such as "placed by".
- Business entities, such as Customer or Order: the tables that hold their master records (members) and the tables that hold more rows about them (supporting tables).
- Properties of an entity, such as
date_of_birth: a named, typed business field with the column that holds it in each table.
You can create every row by hand, or let Squid generate them from the database schemas, sampled rows and your glossaries. Generation only proposes: you check the proposal, change what you want and save it. Every saved row reaches agents and AI queries.
When to use it
| Situation | Recommendation |
|---|---|
| Joins are not declared as foreign keys, or column names are cryptic | ✅ Ontology |
| One business concept spans several tables or databases | ✅ Ontology, with business entities |
| Users ask about business terms ("active customer", "churn") | A glossary |
| The schema is small and its foreign keys already describe every join | Not needed: AI queries already read the declared associations |
Quick Start
Prerequisites
- A database connector with a discovered schema.
- The Squid backend SDK. Ontology management requires your app's API key, so run it from backend code and expose it to your frontend through an executable.
The ontology API is available in the TypeScript SDK.
Step 1: Create an ontology and generate it
import { SquidService, executable } from '@squidcloud/backend';
import { AiOntologyGenerationRun } from '@squidcloud/client';
export class OntologyService extends SquidService {
@executable()
async createSalesOntology(): Promise<string> {
const ontologies = this.squid.ai().ontology();
// Creating the ontology stores no rows yet.
const { ontology } = await ontologies.createOntology({
name: 'Sales',
description: 'Customers, the orders they place and the products in them.',
integrationIds: ['sales_db'],
});
// Starts a background run that proposes the content of the ontology. It saves nothing.
await ontologies.generate(ontology.id);
return ontology.id;
}
@executable()
async getGenerationRun(ontologyId: string): Promise<AiOntologyGenerationRun | undefined> {
const ontology = await this.squid.ai().ontology().getOntology(ontologyId);
// status is 'running', 'completed' or 'failed'.
return ontology?.lastGenerationRun;
}
}
Step 2: Save what generation proposes
When lastGenerationRun.status is completed, read the proposal, change what you want and save it:
import { SquidService, executable } from '@squidcloud/backend';
export class OntologySaveService extends SquidService {
@executable()
async saveGeneration(ontologyId: string): Promise<number> {
const ontologies = this.squid.ai().ontology();
// The whole content the run proposes: the stored rows with its changes.
const result = await ontologies.getGenerationResult(ontologyId);
if (!result) return 0;
// Drop what you do not want before saving, such as weakly inferred relationships. A stored row you leave out
// is deleted, and every row you leave out is remembered, so generation does not propose it again.
const relationships = result.content.relationships.filter(({ confidence }) => confidence === undefined || confidence >= 0.8);
const { content } = await ontologies.saveContent({
ontologyId,
content: { ...result.content, relationships },
revision: result.revision,
startedOver: result.startedOver,
});
return content.relationships.length;
}
}
To drop the proposal instead, call discardGenerationResult(ontologyId).
Step 3: Connect it to an agent
Attach the ontology to an agent that is connected to the database. See Connected Ontologies and Glossaries.
Core Concepts
Relationships
A relationship joins a from side (the referencing table) to a to side (the referenced table). Each side names its integrationId, table, join columns (aligned by position with the other side) and cardinality (ONE or MANY).
Every relationship has a source:
| Source | Meaning |
|---|---|
declared | Imported from an association declared in the database schema |
inferred | Imported from an association Squid inferred for the schema |
ai | Proposed by a generation run |
human | Created or edited by a person through the API or the Console |
Optional fields refine a relationship:
conditionlimits it to rows where a column holds one value, such as{ field: 'type', value: 'billing' }.fromEntityIdandtoEntityIdname the business entity of each side. When omitted, Squid resolves each side to the one entity that has the table as a member, else as a supporting table, and returns it asresolvedFromEntityIdandresolvedToEntityId.confirmationColumnsare column pairs whose values should agree once the join columns match, such as a name on both sides. Agents are told to compare them, so a record whose values differ is reported as a possible mismatch.
import { SquidService, executable } from '@squidcloud/backend';
import { AiOntologyRelationship } from '@squidcloud/client';
export class OntologyRelationshipService extends SquidService {
@executable()
async addOrderCustomerRelationship(ontologyId: string): Promise<AiOntologyRelationship> {
return this.squid
.ai()
.ontology()
.createRelationship({
ontologyId,
// Each order points at one customer; a customer places many orders.
from: { integrationId: 'sales_db', table: 'orders', columns: ['customer_id'], cardinality: 'MANY' },
to: { integrationId: 'sales_db', table: 'customers', columns: ['id'], cardinality: 'ONE' },
label: 'placed by',
description: 'The customer who placed the order.',
});
}
}
A relationship you create is saved at once. Both tables must be in databases attached to the ontology, and the columns must exist and not be hidden.
Business entities
A business entity is a concept such as Customer, with:
members: the tables that hold its master records, one row per real-world thing, most authoritative first. Each member lists thekeyColumnsthat identify the thing.supportingTables(optional): tables that hold more rows about it. Each has akind:detail(child rows such as order lines),link(maps it to another entity),measurement(metrics over time) orlookup(a small code or status table).
import { SquidService, executable } from '@squidcloud/backend';
import { AiOntologyEntity } from '@squidcloud/client';
export class OntologyEntityService extends SquidService {
@executable()
async addCustomerEntity(ontologyId: string): Promise<AiOntologyEntity> {
return this.squid
.ai()
.ontology()
.createEntity({
ontologyId,
name: 'Customer',
description: 'A company that buys from us.',
// The most authoritative table comes first.
members: [
{ integrationId: 'sales_db', table: 'customers', keyColumns: ['id'] },
{ integrationId: 'crm_db', table: 'accounts', keyColumns: ['external_ref'] },
],
supportingTables: [{ integrationId: 'sales_db', table: 'customer_addresses', kind: 'detail' }],
});
}
}
Properties
A property is a named, typed field of an entity, in snake_case, with one source column per member table that has it. A property can instead come from a supporting table, but then that column must be its only source. Its type is string, number, date, boolean or id. Set sensitivity (pii, phi or confidential) to classify it, and agentVisible: false to keep it from agents and AI queries. Squid stores sensitivity and shows it; it does not enforce anything on it.
import { SquidService, executable } from '@squidcloud/backend';
import { AiOntologyProperty } from '@squidcloud/client';
export class OntologyPropertyService extends SquidService {
@executable()
async addSignupDateProperty(customerEntityId: string): Promise<AiOntologyProperty> {
return this.squid
.ai()
.ontology()
.createProperty({
// The id returned by createEntity(). The entity determines the ontology.
entityId: customerEntityId,
name: 'signup_date',
description: 'When the customer first signed up.',
type: 'date',
// One column per member table that holds the property.
sources: [
{ integrationId: 'sales_db', table: 'customers', column: 'created_at' },
{ integrationId: 'crm_db', table: 'accounts', column: 'signup_date' },
],
});
}
}
Saving and editing
Every saved row reaches agents and AI queries; rows have no review status. There are two ways to save:
- One row at a time.
createRelationship(),updateRelationship(),deleteRelationship()and the matching entity and property calls save at once. - The whole content.
getContent(ontologyId)returns every row with arevision.saveContent({ ontologyId, content, revision })stores the content you send in one write: a row sent back unchanged is kept, a new or changed row is checked like a create or an update, and a stored row you leave out is deleted. When someone saved the ontology since you read therevision, the save fails withONTOLOGY_CONTENT_CHANGED.
A person's edit makes a row's source human, and later imports and generation runs leave it alone. A generated or imported row you delete, or leave out of a save, is remembered, so later runs do not propose it again. resetOntology(), or saving a run that started over, forgets them.
Generating an Ontology
generate(ontologyId, options) starts a background run and returns at once. It reads the schemas, samples rows, reads the glossaries of the attached databases and the global glossaries in the ontology's glossaryIds, and proposes relationships, business entities and properties. It saves nothing. Read the outcome from lastGenerationRun: its status, the counts of what it proposes, and its warnings.
Once the run is completed, getGenerationResult(ontologyId) returns its proposal: the whole content, with the revision it builds on. Save it, as it is or edited, with saveContent(). The result stays for a day, until a save, discardGenerationResult() or the next run removes it. When the ontology changed after the run started, saving its result fails with ONTOLOGY_CONTENT_CHANGED; generate again.
| Option | Default | Description |
|---|---|---|
mode | update | update adds what is missing. regenerate reads the sources again without the AI-generated rows and proposes removing those it no longer finds. See Regenerating. |
usePreviousOntology | true | false starts from nothing and proposes everything again. Saving that proposal replaces every row, hand-made ones included. |
Every run also proposes the associations of the attached database schemas as relationships. Attaching a database stores nothing until you save a run's result. A run never proposes removing a row a person created or edited.
Regenerating
A regenerate run reads the sources without the AI-generated rows. Its proposal leaves out the AI-generated rows it no longer finds and adds what it finds now. Rows people created or edited stay. Saving the result applies the changes; discarding it keeps the ontology as it is:
import { SquidService, executable } from '@squidcloud/backend';
export class OntologyRegenerateService extends SquidService {
@executable()
async regenerate(ontologyId: string): Promise<void> {
await this.squid.ai().ontology().generate(ontologyId, { mode: 'regenerate' });
}
@executable()
async applyRegenerate(ontologyId: string, apply: boolean): Promise<void> {
const ontologies = this.squid.ai().ontology();
const result = await ontologies.getGenerationResult(ontologyId);
if (!result) return;
if (!apply) {
// Removes only this run's result, never a newer one.
await ontologies.discardGenerationResult(ontologyId, result.startedAt);
return;
}
const { content, revision, startedOver } = result;
await ontologies.saveContent({ ontologyId, content, revision, startedOver });
}
}
Guiding generation with glossaries
Generation reads the glossary of each attached database on every run. To add global glossaries, pass their ids in glossaryIds when you create or update the ontology. Glossaries are context only: generation proposes nothing just because a term exists.
import { SquidService, executable } from '@squidcloud/backend';
export class OntologyGlossaryService extends SquidService {
@executable()
async addCompanyGlossary(ontologyId: string): Promise<void> {
await this.squid
.ai()
.ontology()
.updateOntology({
id: ontologyId,
// updateOntology() replaces these three fields, so send their current values too.
name: 'Sales',
description: 'Customers, the orders they place and the products in them.',
integrationIds: ['sales_db', 'crm_db'],
// The ids of global glossaries.
glossaryIds: ['company-vocabulary'],
});
}
}
updateOntology() replaces the name, the description and the attached databases. An omitted knowledgeBaseIds or glossaryIds keeps the stored list. Adding a database stores nothing; the next generation run proposes its relationships.
Using an Ontology
- Agents. Attach the ontology to an agent and connect its databases. See Connected Ontologies and Glossaries.
- AI queries. Pass
ontologyIdsin the AI query options. The relationships of every listed ontology that attaches the database replace the relationships the query would otherwise read from the schema.
import { SquidService, executable } from '@squidcloud/backend';
import { AiQueryResponse } from '@squidcloud/client';
export class OntologyQueryService extends SquidService {
@executable()
async findFrequentBuyers(ontologyId: string): Promise<AiQueryResponse> {
return this.squid.ai().executeAiQuery('sales_db', 'Which customers placed more than ten orders last month?', {
// The relationships of this ontology guide the joins.
ontologyIds: [ontologyId],
});
}
}
Error Handling
| Error | Cause | Solution |
|---|---|---|
ONTOLOGY_NOT_FOUND | The ontology id does not exist | Use listOntologies() to find the id |
ONTOLOGY_GENERATION_RUN_IN_PROGRESS | A run is already going, or you changed the sources while one runs | Wait until lastGenerationRun.status is no longer running |
ONTOLOGY_CONTENT_CHANGED | The ontology was saved after you read its revision, or after the run started | Read the content again, or generate again, and reapply your changes |
ONTOLOGY_SCHEMA_NOT_FOUND | An attached database has no discovered schema | Discover the schema of the connector in the Console |
ONTOLOGY_DATABASE_NOT_ATTACHED | A relationship, entity or property names a database the ontology does not attach | Add the database to integrationIds with updateOntology() |
ONTOLOGY_TABLE_NOT_FOUND, ONTOLOGY_COLUMN_NOT_FOUND | A table or column does not exist in the schema, or is hidden | Check the names against the schema |
ONTOLOGY_RELATIONSHIP_EXISTS | The ontology already has a relationship between those columns | Update the existing relationship instead |
ONTOLOGY_PROPERTY_SUPPORTING_SOURCE_ALONE | A property mixes a supporting-table source with other sources | Give the property either member-table sources or a single supporting-table source |
ONTOLOGY_REGENERATE_STARTS_OVER | mode: 'regenerate' was combined with usePreviousOntology: false | Use one or the other |
ONTOLOGY_RELATIONSHIP_LIMIT warning | A run reached the limit of 2,000 relationships per ontology | Attach fewer databases, or limit the tables the connector discovers |
A run that fails sets lastGenerationRun.status to failed and its error, and leaves no result. Warnings name what a run left out.
Best Practices
- Check before you save. Read the result, drop what does not fit and save the rest. Nothing a run proposes reaches your agents until you save it.
- Keep the schema focused. Every run proposes all the associations of every attached database. For a database with hundreds of tables, such as a CRM, limit the tables its connector discovers to the ones the ontology should cover.
- Write a clear description. Generation reads the ontology's
descriptionas the scope of what to model. - Add a glossary first. Glossary terms help generation name entities and relationships the way your business does.
- Label hidden data. Mark sensitive properties with
sensitivity, and setagentVisible: falseon those agents must not see.