Back to blog
Map Postgres Tables to an Ontology for AI Agents

This blog is written by AI for SEO

Map Postgres Tables to an Ontology for AI Agents

HelixDB8 min read

To map Postgres tables to an ontology, treat each table as an entity type, each foreign key as a typed link, and write down the rules that say when two records are the same thing and when a human has to decide. Do it by hand once on a small schema and the idea stops being abstract. Then you can let Helix Foundry draft the same mapping, review it and load it, instead of writing every line yourself.

This guide starts from real DDL for customers, orders and invoices, maps it manually, matches a CSV export to the same customer, and adds one constraint that routes a mismatch to a person. After that it shows the same job in Helix Foundry, and where hand-mapping still wins.

Start with the Postgres schema you already have

The schema you already run is the best input, because it holds decisions someone already made about what a customer, an order and an invoice are. Here is a small one:

CREATE TABLE customers (
  id          bigint PRIMARY KEY,
  email       text UNIQUE NOT NULL,
  name        text NOT NULL,
  credit_limit numeric(12,2)
);

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  total       numeric(12,2) NOT NULL,
  placed_at   timestamptz NOT NULL
);

CREATE TABLE invoices (
  id          bigint PRIMARY KEY,
  order_id    bigint NOT NULL REFERENCES orders(id),
  amount      numeric(12,2) NOT NULL,
  status      text NOT NULL
);

Three tables, two foreign keys. That is enough structure for an agent to answer "which invoices belong to this customer" without guessing, provided the model says so explicitly. Raw tables don't say so. They give an agent column names and leave the meaning to inference, which is exactly where AI projects get customers and orders subtly wrong.

Each table becomes an entity type, and each foreign key becomes a named link between two types. Do this on paper first.

Entities: Customer from customers, Order from orders, Invoice from invoices. Columns become properties on the entity. The primary key becomes the identity you map from, so customers.id is how a Customer is addressed in this source.

Links: orders.customer_id becomes Order -> placed_by -> Customer, and invoices.order_id becomes Invoice -> bills -> Order. Name them as verbs a person would say. A link called customer_id tells an agent nothing, while placed_by reads as a sentence.

Two judgment calls matter here. First, not every column is worth keeping: status on an invoice might deserve to be its own concept if rules hang off it later. Second, a many-to-many join table is usually a link, not an entity, unless the join row itself carries facts like a quantity or a date. Decide these by asking what question an agent will need to answer, then keep what serves it.

Written as a plain mapping, the result is short: three types, two links, and a column-to-property list for each. That small artifact is the whole ontology for this slice of the business.

Matching the same customer in a CSV export

To match the same customer across Postgres and a spreadsheet, pick a reliable key first, fall back to weaker evidence second, and never merge silently on weak evidence.

Say finance sends a CSV export from their billing sheet:

customer_email,customer_name,open_balance
ana@acme.example,Acme Corp,4200.00
ops@globex.example,Globex Ltd.,0.00

The stable key is email, and customers.email is already UNIQUE. So the rule is: if customer_email equals customers.email, the CSV row and the Postgres row describe one Customer. Both sources stay attached to that customer as evidence, so you can always see which system said what.

When there is no shared ID, fall back in order: exact email, then normalized name plus another attribute, such as a domain. Globex Ltd. and Globex Limited are probably the same company, but "probably" isn't good enough to merge on. Normalize the casing and punctuation, score the match, and set a threshold below which a person looks at it. The same idea is covered in more depth in entity resolution for knowledge graphs built by an LLM.

Keep the CSV as its own source rather than flattening it into Postgres. An ontology gives you one model of the customer with every source still traceable.

A constraint that flags a mismatch for human review

A constraint is a checkable rule that, when broken, produces a flag for a person instead of a quiet correction. Take the schema's credit_limit. A reasonable business rule: an order whose total exceeds the customer's credit limit needs a linked approval.

Written by hand, it is one sentence a finance lead would agree with: for every Order placed by a Customer, if Order.total > Customer.credit_limit, there must be an approval linked to that order. If there is none, flag it.

A second, cross-source rule uses the CSV. If the sheet's open_balance disagrees with the sum of unpaid Invoice.amount for the same Customer, that is a mismatch worth a person's eyes. Neither number is automatically right. The sheet could be stale, or an invoice could be mis-statused. Surface both values with their sources and let someone decide. Don't overwrite one with the other.

Carry this into any tool: code measures, the check is explicit, and anything uncertain goes to review. A rule that silently fixes data teaches people to stop trusting the model.

Doing it in Helix Foundry: connect, generate, review, load

Helix Foundry does the drafting you just did by hand, then gives you a version to review before anything is loaded. It is the app; HelixDB is the database it loads into. Foundry is open source under Apache 2.0, and the repo covers running it on your computer: you need Docker with Compose v2, ports 3000 and 8333 free, and a few GB for the first image pull. Clone it, run docker compose up -d --wait, and open http://localhost:3000. On Helix Cloud, use foundry.helix-db.com. The self-hosted install is for one person on their own computer, so don't expose it.

Connect. Foundry connects to PostgreSQL with a read-only database user. The docs say it does not write to your source database. Create a role with CONNECT, USAGE on the schema and SELECT on the tables, enter host, database, username, password and port, test the connection, then choose the tables to sync. Tables start selected, and views are not synced. A CSV like the billing export is another source for the same ontology.

Generate. Select Generate on an ontology, describe what you want to understand (for example, how customers, orders and invoices relate and where billing disagrees with the sheet), and pick the connectors and any document sets. Foundry drafts a new ontology version with types, links, table mappings, rules and questions. A generation can include up to 50 tables. Per the docs, a generation sends your synced tables, plus up to five of the newest documents of each set it picks, to Foundry's agent, which drafts the version.

Review. Open Review version and check the Types, Links, Mappings, Sources and Rules tabs, plus Questions and TOML as needed. This is where your hand-mapping pays off: you already know that orders.customer_id should read as placed_by, so you can check the draft against it. Use Edit to stage changes or add a rule like the credit-limit check, preview it, and create a new version. Versions are immutable, so nothing applies until you make a version current.

Load. Make the chosen version current and load it into HelixDB. Rule breaks are recorded for review rather than silently corrected, and the load has a Rule breaks page where violations show up. For the broader walkthrough, the quickstart goes from sign-in to a first answer, and how to build an ontology from scattered company data covers the wider job.

Foundry can also run locally or BYOC with your own models, which matters if the schema can't leave your environment.

Trade-offs and where hand-mapping still wins

Hand-mapping wins when the schema is tiny, when naming carries political weight, or when you are still learning what the business means. Writing three tables out by hand takes minutes and teaches you more than any generated draft, because you have to decide what a link should be called and which columns matter.

It loses on scale and drift. Past a dozen tables, with a CSV, a billing tool and a CRM all describing the same customer, the manual version becomes a document nobody updates. Generation gives you a first draft of types, links and mappings in one pass, and review lets a person correct it rather than author it from nothing.

Two honest limits. A draft is only a draft: the agent proposes, a person approves each rule, and you should budget review time. And very large tables deserve a look at what you actually need selected before you sync them. When the model changes later, the same review loop applies; see how to update an ontology when your data changes.

FAQ

See the questions below.

Map one schema by hand, then let Foundry draft the rest

Do the three-table mapping yourself first: entities from tables, links from foreign keys, email as the match key, one constraint that sends a mismatch to a person. Then clone helix-foundry, run docker compose up -d --wait, connect Postgres with a read-only user, generate a version and compare the draft against your hand-written mapping. Where they differ, you have found either a gap in your model or a link worth renaming.

Build with HelixDB

Give your coding agent the setup prompt, or sign up and deploy a database.

Sign up