Modelling a financial services enterprise ontology in SurrealDB

AI

Tutorial

Jul 21, 20264 min read

Martin Schaer

Show all posts

Modelling a financial services enterprise ontology in SurrealDB

Our newsletter

Get tutorials, AI agent recipes, webinars, and early product updates in your inbox every two weeks

Who owns what, holds what, and moved what — as one graph, with beneficial-ownership chains in a single query.

Why it matters

Financial institutions spend enormous effort answering deceptively simple questions. Who is the ultimate beneficial owner of this fund? Which accounts does this entity control? What's our exposure to a single instrument across every book? In a traditional relational stack these turn into recursive CTEs, join tables, and brittle reporting jobs — and the ownership-chain question, the one regulators care most about, is the hardest of all.

SurrealDB lets you model the whole thing as a graph: parties, accounts, and instruments are nodes; ownership, custody, issuance, positions, and transactions are edges that carry their own data. Beneficial ownership becomes a recursive traversal rather than a stored procedure. This post walks through a compact but realistic enterprise ontology you can copy and adapt.

Every statement below was validated with surreal validate and run end-to-end against SurrealDB 3.1.5.

The shape of the model

Most of the domain is covered by three kinds of node:

Parties — legal_entity (companies, funds, SPVs, trusts) and natural_person.
Accounts and instruments — account (custody, trading, settlement, deposit) and instrument (equities, bonds, ETFs, derivatives).

...and five edge kinds: owns, holds (custody), issued, position, and transacted.

Two modelling choices do a lot of work here. First, the ownership and custody edges are union-typed — IN legal_entity | natural_person — so a single edge can originate from either a company or a person, which is exactly what beneficial-ownership and custody graphs need. Second, all monetary values use decimal, never float, so balances and quantities stay exact.

The schema

-- Parties -------------------------------------------------------------
DEFINE TABLE legal_entity SCHEMAFULL;
DEFINE FIELD legal_name      ON legal_entity TYPE string;
DEFINE FIELD lei             ON legal_entity TYPE option<string>
    ASSERT $value = NONE OR string::len($value) = 20;   -- Legal Entity Identifier
DEFINE FIELD jurisdiction    ON legal_entity TYPE string;
DEFINE FIELD entity_type     ON legal_entity TYPE string
    ASSERT $value IN ["bank", "broker_dealer", "asset_manager", "fund", "corporation", "spv", "trust"];
DEFINE FIELD status          ON legal_entity TYPE string
    ASSERT $value IN ["active", "dormant", "dissolved"] DEFAULT "active";
DEFINE FIELD incorporated_on ON legal_entity TYPE option<datetime>;
DEFINE FIELD headquarters    ON legal_entity TYPE option<point>;

DEFINE TABLE natural_person SCHEMAFULL;
DEFINE FIELD full_name       ON natural_person TYPE string;
DEFINE FIELD date_of_birth   ON natural_person TYPE option<datetime>;
DEFINE FIELD nationality     ON natural_person TYPE option<string>;
DEFINE FIELD pep             ON natural_person TYPE bool DEFAULT false;  -- politically exposed person

-- Accounts & instruments ----------------------------------------------
DEFINE TABLE account SCHEMAFULL;
DEFINE FIELD account_number  ON account TYPE string;
DEFINE FIELD account_type    ON account TYPE string
    ASSERT $value IN ["custody", "trading", "settlement", "deposit"];
DEFINE FIELD base_currency   ON account TYPE string ASSERT string::len($value) = 3;  -- ISO 4217
DEFINE FIELD opened_on       ON account TYPE datetime;
DEFINE FIELD status          ON account TYPE string
    ASSERT $value IN ["open", "frozen", "closed"] DEFAULT "open";
DEFINE FIELD balance         ON account TYPE decimal DEFAULT 0dec;

DEFINE TABLE instrument SCHEMAFULL;
DEFINE FIELD symbol          ON instrument TYPE string;
DEFINE FIELD name            ON instrument TYPE string;
DEFINE FIELD instrument_type ON instrument TYPE string
    ASSERT $value IN ["equity", "bond", "etf", "option", "future", "fx", "fund_unit"];
DEFINE FIELD isin            ON instrument TYPE option<string>
    ASSERT $value = NONE OR string::len($value) = 12;   -- ISO 6166
DEFINE FIELD currency        ON instrument TYPE string ASSERT string::len($value) = 3;
DEFINE FIELD issued_on       ON instrument TYPE option<datetime>;
DEFINE FIELD maturity_on     ON instrument TYPE option<datetime>;

-- Relationships (graph edges) ------------------------------------------
-- Ownership: either a company OR a person may own a company.
DEFINE TABLE owns SCHEMAFULL TYPE RELATION IN legal_entity | natural_person OUT legal_entity;
DEFINE FIELD percentage      ON owns TYPE float ASSERT $value >= 0 AND $value <= 100;
DEFINE FIELD ownership_type  ON owns TYPE string
    ASSERT $value IN ["direct", "beneficial", "voting"] DEFAULT "direct";
DEFINE FIELD since           ON owns TYPE option<datetime>;

-- Custody: who holds an account, and in what capacity.
DEFINE TABLE holds SCHEMAFULL TYPE RELATION IN legal_entity | natural_person OUT account;
DEFINE FIELD role            ON holds TYPE string
    ASSERT $value IN ["owner", "beneficiary", "authorized_signatory"];
DEFINE FIELD since           ON holds TYPE option<datetime>;

-- Issuance: an entity issues an instrument.
DEFINE TABLE issued SCHEMAFULL TYPE RELATION IN legal_entity OUT instrument;
DEFINE FIELD issued_on       ON issued TYPE datetime;
DEFINE FIELD notional        ON issued TYPE option<decimal>;

-- Positions: an account holds a quantity of an instrument.
DEFINE TABLE position SCHEMAFULL TYPE RELATION IN account OUT instrument;
DEFINE FIELD quantity        ON position TYPE decimal;
DEFINE FIELD cost_basis      ON position TYPE decimal;
DEFINE FIELD as_of           ON position TYPE datetime;

-- Transactions: value moving between accounts.
DEFINE TABLE transacted SCHEMAFULL TYPE RELATION IN account OUT account;
DEFINE FIELD amount          ON transacted TYPE decimal;
DEFINE FIELD currency        ON transacted TYPE string ASSERT string::len($value) = 3;
DEFINE FIELD txn_type        ON transacted TYPE string
    ASSERT $value IN ["transfer", "settlement", "fee", "dividend"];
DEFINE FIELD status          ON transacted TYPE string
    ASSERT $value IN ["pending", "settled", "failed"] DEFAULT "pending";
DEFINE FIELD executed_at     ON transacted TYPE datetime;
DEFINE FIELD reference       ON transacted TYPE option<string>;

A few details worth calling out:

option<…> + ASSERT— nullable fields guard with$value = NONE OR so the assertion is skipped when the field is unset. That's how theleiandisinlength checks coexist with the field being optional.
Enums via ASSERT $value IN [...]reject out-of-set values at write time — no separate lookup tables.
Geo pointonheadquartersopens the door to location queries later.

Some data to traverse

The seed below builds a four-level ownership chain — a real person at the top, three companies beneath — plus a custody account, an issued instrument, and a position.

CREATE legal_entity:acme SET
    legal_name = "Acme Holdings Ltd", lei = "5493001KJTIIGC8Y1R12",
    jurisdiction = "GB", entity_type = "corporation",
    incorporated_on = d"2009-04-01T00:00:00Z", headquarters = (-0.1276, 51.5072);
CREATE legal_entity:acme_capital SET
    legal_name = "Acme Capital LLC", jurisdiction = "US", entity_type = "asset_manager";
CREATE legal_entity:acme_fund SET
    legal_name = "Acme Growth Fund", jurisdiction = "LU", entity_type = "fund";

CREATE natural_person:jane SET full_name = "Jane Roe", nationality = "GB", pep = false;

CREATE account:cust_001 SET
    account_number = "CUST-0001", account_type = "custody", base_currency = "USD",
    opened_on = d"2020-01-15T00:00:00Z", balance = 1250000.00dec;

CREATE instrument:aapl SET
    symbol = "AAPL", name = "Apple Inc.", instrument_type = "equity",
    isin = "US0378331005", currency = "USD";

-- Ownership chain: Jane -> Acme Holdings -> Acme Capital -> Acme Growth Fund
RELATE natural_person:jane->owns->legal_entity:acme
    SET percentage = 100.0, ownership_type = "beneficial", since = d"2009-04-01T00:00:00Z";
RELATE legal_entity:acme->owns->legal_entity:acme_capital SET percentage = 80.0;
RELATE legal_entity:acme_capital->owns->legal_entity:acme_fund SET percentage = 51.0;

RELATE legal_entity:acme_fund->holds->account:cust_001 SET role = "owner";
RELATE legal_entity:acme->issued->instrument:aapl SET issued_on = d"2024-01-01T00:00:00Z";
RELATE account:cust_001->position->instrument:aapl
    SET quantity = 5000dec, cost_basis = 850000.00dec, as_of = d"2025-06-01T00:00:00Z";

Beneficial ownership in one query

This is the payoff. SurrealDB's recursive paths (.{min..max}) walk the owns graph to arbitrary depth, and the trailing .@ marks where each level recurses — so a single statement returns the entire ownership tree as a nested structure.

-- Full subsidiary tree under a parent (downstream)
legal_entity:acme.{1..5}.{
    legal_name,
    subsidiaries: ->owns->legal_entity.@
};
-- => Acme Holdings -> Acme Capital -> Acme Growth Fund (nested)

-- Ultimate beneficial owners upstream (inbound, across both party types)
legal_entity:acme_fund.{1..10}.{
    id,
    owners: <-owns<-(legal_entity, natural_person).@
};
-- => fund <- Acme Capital <- Acme Holdings <- Jane Roe (nested)

-- Flat set of every entity controlled by a person (collect algorithm)
natural_person:jane.{..+collect}->owns->legal_entity;
-- => [legal_entity:acme, legal_entity:acme_capital, legal_entity:acme_fund]

`.{..+collect}` returns the unique node set; `.{..+path}` returns every path; and `.{..+shortest=}` returns the shortest path to a target record — useful for "how is this person connected to that entity?" investigations.

One practical note from building this: the recursive form parses even without the .@ marker, but it fails at execution with "Expected a record ID during recursive graph traversal." The .@ is what tells SurrealDB where to continue recursing — worth validating against a live instance, not just the parser.

The everyday queries

Beyond ownership, the same graph answers the questions desks and compliance teams ask all day.

-- Immediate corporate owners of an entity
SELECT <-owns<-legal_entity.legal_name AS corporate_owners FROM legal_entity:acme_fund;

-- Accounts a party holds, and instruments it has issued
SELECT ->holds->account.* AS accounts FROM legal_entity:acme_fund;
SELECT ->issued->instrument.symbol AS issued_instruments FROM legal_entity:acme;

-- Aggregate exposure per instrument across all accounts
SELECT out AS instrument, math::sum(quantity) AS total_quantity, count() AS lots
FROM position
GROUP BY instrument;

Transaction monitoring is just a filter over the transacted edge — here, the classic AML pattern of large settled transfers in a recent window:

SELECT in AS from_account, out AS to_account, amount, currency, executed_at
FROM transacted
WHERE txn_type = "transfer"
  AND status = "settled"
  AND amount > 10000dec
  AND executed_at > time::now() - 30d;

And screening reads naturally too — for instance, politically exposed persons holding ownership stakes:

SELECT full_name, ->owns->legal_entity.legal_name AS holdings
FROM natural_person WHERE pep = true;

Why a graph, not a join table

None of these queries needed a reporting pipeline, a recursive CTE, or a separate graph database bolted onto your relational store. The ownership chain — the query that's painful everywhere else — is a one-liner. The edges carry their own attributes (percentage, role, cost basis), so the relationships are data rather than implicit foreign keys. And because it's all one database, exposure aggregation, transaction monitoring, and ownership traversal share the same source of truth.

Adapt the field names and enums to your own instruments, jurisdictions, and account types, and you have a foundation for KYC, AML, and risk reporting that grows with the graph.