Skip to content

Knowledge graphs

When your data has entities and relationships, model it as a graph: entity concepts are the vertices, relationship concepts are the connections, and rules traverse the graph.

These are modeling conventions, not language rules. The compiler attaches no special meaning to a concept's name. They are the discipline that keeps a growing rule base composable, and the structure Synalog-based agent runtimes expect.

Nodes and edges are how the agent builds the knowledge-graph layer of its dynamic semantic layer: once entities and relationships are named, every later rule traverses them instead of re-joining raw tables, and a filter on one node propagates through the whole graph.

The graph is virtual. Nothing is copied into a graph database: nodes and edges are ordinary predicates that compile to SQL over the source tables, so the graph is always as fresh as the data underneath it.

Why model a graph at all

The questions a business actually asks are rarely about a table. They are about how things connect:

  • Which customers are exposed to this supplier, directly or through a subcontractor?
  • Who can approve this request, and who approves them if they are away?
  • Which accounts share a phone number with an account we just flagged?
  • Which teams touched this client, and were they on the team at the time?
  • If we discontinue this component, which finished products break?

Each of those is a traversal. Written directly in SQL, each becomes a bespoke chain of joins: correct only if whoever wrote it remembered every filter, and rewritten from scratch for the next question. The join logic is the knowledge, and it ends up scattered across hundreds of queries where it can neither be reviewed nor reused.

Modeling entities and relationships as concepts moves that knowledge into one place. Person, Team, Client, MemberOf, EngagedWith are written once. After that, "which teams touched this client" is two lines, and so is every variant of it nobody has thought of yet.

What it buys

  • Questions get short. The expensive part (deciding how things connect) is done once, not per query. New questions compose existing edges instead of re-deriving joins.
  • Filters propagate. Because edges join through nodes, narrowing Person to active employees narrows every traversal, metric and report built on it, without editing a single downstream rule. This is the property that makes a graph model worth the effort.
  • Relationships become reviewable. "How do we decide a person works in a department?" is a named rule someone can read, not a JOIN buried in query 47.
  • Recursive questions become expressible. Org charts, bills of materials, referral chains and dependency closures are a base case plus a recursive case rather than something you give up on in SQL.
  • No new system. The graph is a way of reading the tables you already have. Nothing is exported, synchronized or operated.

What it costs

  • It is modeling work. Someone has to decide what the entities are and what identifies them. It is hours, not months, and an agent can propose a first draft, but it is not free.
  • Discipline is required to keep the payoff. The moment a rule goes back to the raw table instead of the node, referential integrity and filter propagation are lost for that rule and everything above it.
  • Identity is the hard part. When the same customer exists in three systems with three keys, the graph forces you to decide how they reconcile. That decision was always required; the graph just refuses to let it stay implicit.
  • Deep traversal is still database work. Each recursive hop is another join. Bounded closures over a mid-sized graph are fine; interactive pathfinding over billions of edges is not what this is for.
  • Hubs get recomputed. A node concept used by twenty rules is inlined into each of them unless you materialize it with @Ground.

Compared to a dedicated graph database

Virtual graph in Synalog Graph database
Where the data lives Your existing tables, untouched A separate store, loaded and kept in sync
Freshness Always current by construction As fresh as the last sync
Operational cost None beyond the warehouse A second system to run, secure and back up
Deep or unbounded traversal Bounded hops, each one a join What the engine is built for
Graph algorithms (centrality, communities) Out of scope First-class
Aggregation and analytics across the graph Warehouse-grade Usually a weak point
Combining graph and non-graph data Same query, same engine Requires federation or export

Choose a graph database when traversal depth is unpredictable, latency budgets are interactive, or you need real graph algorithms. Choose the virtual graph when the data already lives in a warehouse, freshness matters, traversals are shallow and bounded, and you would rather not run another database. Many teams end up with both: the warehouse graph for analysis and reasoning, a specialized store for the one workload that genuinely needs it.

When not to model a graph

If the data is one wide table with no meaningful relationships, or the questions are pure aggregations over a single fact table, the graph conventions add ceremony and buy nothing. Model nodes when something is referenced from more than one place, or when the answer to a question depends on following a connection.

Conventions

  • Nodes are entities, edges are relationships, rules are traversals.
  • Primary key first. The first column of every concept is its primary key; sort by it with @OrderBy.
  • Preserve URIs and URLs in nodes (url, href, link, website, profile_url, image_url, permalink, homepage, and so on). Dropping them makes the concept useless for downstream action.
  • Edges join through nodes, not raw tables. This guarantees referential integrity: a filter on a node automatically applies to every edge that references it.
  • Name plainly. Person, Team, WorksIn, ReportsTo. No Node, Edge or Rel suffixes.
@OrderBy(Person, "person_id");
Person(person_id:, name:, role:) distinct :- Employees(person_id:, name:, role:);

@OrderBy(WorksIn, "person_id");
WorksIn(person_id:, department_id:) distinct :-
  Person(person_id:),
  Department(department_id:),
  Employees(person_id:, department_id:);

WorksIn mentions Person and Department even though Employees already carries both columns. That join is the point: it is what makes the edge follow the nodes. Restrict Person to active employees and every edge, traversal and metric built on it narrows with it, without touching a single rule downstream.

Node patterns

Entities from tables

A node concept is a distinct projection of the identifying and descriptive columns of a table:

@OrderBy(Product, "product_id");
Product(product_id:, name:, category:, permalink:) distinct :-
  Products(product_id:, name:, category:, permalink:);

Categorical values as nodes

Columns such as status, tier, category or country are entities in disguise. Extract the distinct values as a node before writing rules over them, so the vocabulary is discoverable and every rule agrees on it:

@OrderBy(Category, "category");
Category(category:) distinct :- Products(category:);

@OrderBy(BelongsTo, "product_id");
BelongsTo(product_id:, category:) distinct :-
  Product(product_id:), Category(category:), Products(product_id:, category:);

Subtypes and states

When an entity has distinct categorical states, model one concept per state, each joined through the base node. The subtype is then a drop-in replacement for the base node in any rule:

@OrderBy(ActiveCustomer, "customer_id");
ActiveCustomer(customer_id:, name:) distinct :-
  Customer(customer_id:, name:, status: "active");

@OrderBy(ChurnedCustomer, "customer_id");
ChurnedCustomer(customer_id:, name:) distinct :-
  Customer(customer_id:, name:, status: "churned");

Combined with functors, a subtype becomes a parameter: the same traversal runs over ActiveCustomer or ChurnedCustomer without being rewritten.

One node type, several sources

Entities often arrive from more than one table. Union the sources into a single node concept and make the identifier globally unique, so edges from either side land on the same vertex:

@OrderBy(Party, "party_id");
Party(party_id:, name:, kind:) distinct :-
  Employees(employee_id:, name:),
    party_id == "employee:" ++ ToString(employee_id), kind == "employee" |
  Contractors(contractor_id:, name:),
    party_id == "contractor:" ++ ToString(contractor_id), kind == "contractor";

Prefixing the source keeps two systems that both number their rows from 1 from colliding on the same node.

Edge patterns

N-ary relationships

When more than two entities participate, include all of them as columns:

WorksOn(person_id:, project_id:, role:) distinct :-
  Person(person_id:), Project(project_id:),
  ProjectAssignments(person_id:, project_id:, role:);

Weighted edges

Attach a numeric attribute to the relationship, often an aggregate:

Purchased(customer_id:, product_id:, total_amount? += amount) distinct :-
  Customer(customer_id:), Product(product_id:),
  Orders(customer_id:, product_id:, amount:);

Typed edges

Two options, and the choice matters. One concept per relationship type (Manages, Mentors) keeps rules precise and lets the verifier catch mistakes. A single concept with a type column is useful when a rule has to walk any connection, for example to compute a neighborhood or a degree:

@OrderBy(Related, "source_id", "target_id");
Related(source_id:, target_id:, type:) distinct :-
  Manages(manager_id: source_id, employee_id: target_id), type == "manages" |
  Mentors(mentor_id: source_id, mentee_id: target_id), type == "mentors";

Define the typed relations first and derive Related from them, never the other way around.

Symmetric edges

Define the raw direction once, for example with a < b, then close it with a union:

CoAuthored(author_a:, author_b:, paper_id:) distinct :-
  CoAuthoredRaw(author_a:, author_b:, paper_id:) |
  CoAuthoredRaw(author_a: author_b, author_b: author_a, paper_id:);

Inverse edges

Derive the opposite direction from an existing edge:

ReportsTo(employee_id:, manager_id:) distinct :- Manages(manager_id:, employee_id:);

Reified edges

When a relationship has attributes of its own, or when other things point at the relationship, promote it to a node and connect it with two edges. An assignment with a role, an allocation and its own history is an entity, not a label on a line:

## The relationship as a node.
@OrderBy(Assignment, "assignment_id");
Assignment(assignment_id:, role:, allocation:) distinct :-
  ProjectAssignments(assignment_id:, role:, allocation:);

@OrderBy(AssignmentPerson, "assignment_id");
AssignmentPerson(assignment_id:, person_id:) distinct :-
  Assignment(assignment_id:), Person(person_id:),
  ProjectAssignments(assignment_id:, person_id:);

@OrderBy(AssignmentProject, "assignment_id");
AssignmentProject(assignment_id:, project_id:) distinct :-
  Assignment(assignment_id:), Project(project_id:),
  ProjectAssignments(assignment_id:, project_id:);

The plain person -> project edge is then one composition away, and stays available for callers that do not care about the details.

Edge composition

Chain different edge types: A -> B via one relation and B -> C via another gives A -> C:

WorksWithClient(employee_id:, client_id:) distinct :-
  MemberOf(employee_id:, team_id:),
  EngagedWith(team_id:, client_id:);

Chains and paths

Recursion over a single edge type (parent to child, manager to employee) computes chains. See Recursion. To track the route rather than just the endpoints, accumulate it in the recursive rule:

@Recursive(PathTo, 10);
@OrderBy(PathTo, "source", "target");
PathTo(source:, target:, path:) distinct :-
  Edge(source:, target:),
  path == source ++ " > " ++ target;
PathTo(source:, target:, path:) distinct :-
  PathTo(source:, target: mid, path: prefix),
  Edge(source: mid, target:),
  path == prefix ++ " > " ++ target;

List= collects the visited nodes as an array instead, when the route is consumed by a program rather than read by a human.

Cycle and cardinality checks

A recursive closure detects hierarchy cycles (example). For cardinality constraints, count children per parent and filter for violations:

ChildCount(parent_id:, n? += 1) distinct :- ParentOf(parent_id:, child_id:);
TooManyChildren(parent_id:, n:) :- ChildCount(parent_id:, n:), n > 2;

Dangling references are the mirror image, and negation finds them:

OrphanEdge(child_id:) :- ParentOf(child_id:), ~Person(person_id: child_id);

Traversals

Once nodes and edges exist, questions become short rules over them.

Neighborhood. Everything one hop away from a node, in either direction:

@OrderBy(Neighbor, "node_id", "neighbor_id");
Neighbor(node_id:, neighbor_id:, type:) distinct :-
  Related(source_id: node_id, target_id: neighbor_id, type:) |
  Related(source_id: neighbor_id, target_id: node_id, type:);

Degree. How connected a node is, straight from an aggregation:

@OrderBy(Degree, "n", "DESC");
Degree(node_id:, n? += 1) distinct :- Neighbor(node_id:);

Multi-hop. Bounded transitive closure with @Recursive, and shortest paths with a Min= aggregation over enumerated route costs. Both are covered in Recursion.

Restriction. Because edges join through nodes, narrowing the graph is a node-level change. Swap Person for ActiveCustomer with a functor and the entire traversal runs on the sub-graph.

Materialize the hubs

A node or edge concept that many rules build on is recomputed inline in each of them. Put @Ground on it to materialize it once before its dependents run.

Complete example

A small employee, team and client graph: nodes with primary keys and preserved URLs, edges joined through nodes, an inverse edge, and an edge composition:

# run: Person, Team, Client, MemberOf, EngagedWith, WorksWithClient, ReportsTo
@Engine("duckdb");

# Tables
Employees(person_id: 1, name: "Ada", role: "engineer", manager_id: 2);
Employees(person_id: 2, name: "Grace", role: "lead", manager_id: null);
Employees(person_id: 3, name: "Alan", role: "engineer", manager_id: 2);

TeamAssignments(person_id: 1, team_id: "core");
TeamAssignments(person_id: 2, team_id: "core");
TeamAssignments(person_id: 3, team_id: "platform");

Engagements(team_id: "core", client_id: "acme", website: "https://acme.example.com");
Engagements(team_id: "platform", client_id: "globex", website: "https://globex.example.com");

# Concepts

## Nodes: primary key first, URLs preserved.
@OrderBy(Person, "person_id");
Person(person_id:, name:, role:) distinct :- Employees(person_id:, name:, role:);

@OrderBy(Team, "team_id");
Team(team_id:) distinct :- TeamAssignments(team_id:);

@OrderBy(Client, "client_id");
Client(client_id:, website:) distinct :- Engagements(client_id:, website:);

## Edges join through nodes, not raw tables.
@OrderBy(MemberOf, "person_id");
MemberOf(person_id:, team_id:) distinct :-
  Person(person_id:),
  Team(team_id:),
  TeamAssignments(person_id:, team_id:);

@OrderBy(EngagedWith, "team_id");
EngagedWith(team_id:, client_id:) distinct :-
  Team(team_id:),
  Client(client_id:),
  Engagements(team_id:, client_id:);

## Inverse edge derived from the management relation.
@OrderBy(ReportsTo, "employee_id");
ReportsTo(employee_id:, manager_id:) distinct :-
  Person(person_id: employee_id),
  Person(person_id: manager_id),
  Employees(person_id: employee_id, manager_id:), manager_id is not null;

# Rules

## Edge composition: person -> team -> client gives person -> client.
@OrderBy(WorksWithClient, "person_id");
WorksWithClient(person_id:, client_id:) distinct :-
  MemberOf(person_id:, team_id:),
  EngagedWith(team_id:, client_id:);
Generated SQL and execution results 
$ synalog.check('knowledge_graphs.l')
No errors found.

$ synalog.compile('knowledge_graphs.l', 'Person')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_0_Employees AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'Ada' AS name,
      'engineer' AS role,
      2 AS manager_id
   UNION ALL

    SELECT
      2 AS person_id,
      'Grace' AS name,
      'lead' AS role,
      null AS manager_id
   UNION ALL

    SELECT
      3 AS person_id,
      'Alan' AS name,
      'engineer' AS role,
      2 AS manager_id

) AS UNUSED_TABLE_NAME  )
SELECT
  Employees.person_id AS person_id,
  Employees.name AS name,
  Employees.role AS role
FROM
  t_0_Employees AS Employees
GROUP BY Employees.person_id, Employees.name, Employees.role ORDER BY person_id;

-- Executed on DuckDB:
| person_id | name  | role     |
|-----------|-------|----------|
| 1         | Ada   | engineer |
| 2         | Grace | lead     |
| 3         | Alan  | engineer |
(3 rows)

$ synalog.compile('knowledge_graphs.l', 'Team')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_0_TeamAssignments AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'core' AS team_id
   UNION ALL

    SELECT
      2 AS person_id,
      'core' AS team_id
   UNION ALL

    SELECT
      3 AS person_id,
      'platform' AS team_id

) AS UNUSED_TABLE_NAME  )
SELECT
  TeamAssignments.team_id AS team_id
FROM
  t_0_TeamAssignments AS TeamAssignments
GROUP BY TeamAssignments.team_id ORDER BY team_id;

-- Executed on DuckDB:
| team_id  |
|----------|
| core     |
| platform |
(2 rows)

$ synalog.compile('knowledge_graphs.l', 'Client')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_0_Engagements AS (SELECT * FROM (

    SELECT
      'core' AS team_id,
      'acme' AS client_id,
      'https://acme.example.com' AS website
   UNION ALL

    SELECT
      'platform' AS team_id,
      'globex' AS client_id,
      'https://globex.example.com' AS website

) AS UNUSED_TABLE_NAME  )
SELECT
  Engagements.client_id AS client_id,
  Engagements.website AS website
FROM
  t_0_Engagements AS Engagements
GROUP BY Engagements.client_id, Engagements.website ORDER BY client_id;

-- Executed on DuckDB:
| client_id | website                    |
|-----------|----------------------------|
| acme      | https://acme.example.com   |
| globex    | https://globex.example.com |
(2 rows)

$ synalog.compile('knowledge_graphs.l', 'MemberOf')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_1_Employees AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'Ada' AS name,
      'engineer' AS role,
      2 AS manager_id
   UNION ALL

    SELECT
      2 AS person_id,
      'Grace' AS name,
      'lead' AS role,
      null AS manager_id
   UNION ALL

    SELECT
      3 AS person_id,
      'Alan' AS name,
      'engineer' AS role,
      2 AS manager_id

) AS UNUSED_TABLE_NAME  ),
t_0_Person AS (SELECT
  Employees.person_id AS person_id,
  Employees.name AS name,
  Employees.role AS role
FROM
  t_1_Employees AS Employees
GROUP BY Employees.person_id, Employees.name, Employees.role ORDER BY person_id),
t_4_TeamAssignments AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'core' AS team_id
   UNION ALL

    SELECT
      2 AS person_id,
      'core' AS team_id
   UNION ALL

    SELECT
      3 AS person_id,
      'platform' AS team_id

) AS UNUSED_TABLE_NAME  ),
t_2_Team AS (SELECT
  t_3_TeamAssignments.team_id AS team_id
FROM
  t_4_TeamAssignments AS t_3_TeamAssignments
GROUP BY t_3_TeamAssignments.team_id ORDER BY team_id)
SELECT
  Person.person_id AS person_id,
  Team.team_id AS team_id
FROM
  t_0_Person AS Person, t_2_Team AS Team, t_4_TeamAssignments AS TeamAssignments
WHERE
  (TeamAssignments.person_id = Person.person_id) AND
  (TeamAssignments.team_id = Team.team_id)
GROUP BY Person.person_id, Team.team_id ORDER BY person_id;

-- Executed on DuckDB:
| person_id | team_id  |
|-----------|----------|
| 1         | core     |
| 2         | core     |
| 3         | platform |
(3 rows)

$ synalog.compile('knowledge_graphs.l', 'EngagedWith')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_1_TeamAssignments AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'core' AS team_id
   UNION ALL

    SELECT
      2 AS person_id,
      'core' AS team_id
   UNION ALL

    SELECT
      3 AS person_id,
      'platform' AS team_id

) AS UNUSED_TABLE_NAME  ),
t_0_Team AS (SELECT
  TeamAssignments.team_id AS team_id
FROM
  t_1_TeamAssignments AS TeamAssignments
GROUP BY TeamAssignments.team_id ORDER BY team_id),
t_4_Engagements AS (SELECT * FROM (

    SELECT
      'core' AS team_id,
      'acme' AS client_id,
      'https://acme.example.com' AS website
   UNION ALL

    SELECT
      'platform' AS team_id,
      'globex' AS client_id,
      'https://globex.example.com' AS website

) AS UNUSED_TABLE_NAME  ),
t_2_Client AS (SELECT
  t_3_Engagements.client_id AS client_id,
  t_3_Engagements.website AS website
FROM
  t_4_Engagements AS t_3_Engagements
GROUP BY t_3_Engagements.client_id, t_3_Engagements.website ORDER BY client_id)
SELECT
  Team.team_id AS team_id,
  Client.client_id AS client_id
FROM
  t_0_Team AS Team, t_2_Client AS Client, t_4_Engagements AS Engagements
WHERE
  (Engagements.team_id = Team.team_id) AND
  (Engagements.client_id = Client.client_id)
GROUP BY Team.team_id, Client.client_id ORDER BY team_id;

-- Executed on DuckDB:
| team_id  | client_id |
|----------|-----------|
| core     | acme      |
| platform | globex    |
(2 rows)

$ synalog.compile('knowledge_graphs.l', 'WorksWithClient')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_2_Employees AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'Ada' AS name,
      'engineer' AS role,
      2 AS manager_id
   UNION ALL

    SELECT
      2 AS person_id,
      'Grace' AS name,
      'lead' AS role,
      null AS manager_id
   UNION ALL

    SELECT
      3 AS person_id,
      'Alan' AS name,
      'engineer' AS role,
      2 AS manager_id

) AS UNUSED_TABLE_NAME  ),
t_1_Person AS (SELECT
  Employees.person_id AS person_id,
  Employees.name AS name,
  Employees.role AS role
FROM
  t_2_Employees AS Employees
GROUP BY Employees.person_id, Employees.name, Employees.role ORDER BY person_id),
t_5_TeamAssignments AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'core' AS team_id
   UNION ALL

    SELECT
      2 AS person_id,
      'core' AS team_id
   UNION ALL

    SELECT
      3 AS person_id,
      'platform' AS team_id

) AS UNUSED_TABLE_NAME  ),
t_3_Team AS (SELECT
  t_4_TeamAssignments.team_id AS team_id
FROM
  t_5_TeamAssignments AS t_4_TeamAssignments
GROUP BY t_4_TeamAssignments.team_id ORDER BY team_id),
t_0_MemberOf AS (SELECT
  Person.person_id AS person_id,
  Team.team_id AS team_id
FROM
  t_1_Person AS Person, t_3_Team AS Team, t_5_TeamAssignments AS TeamAssignments
WHERE
  (TeamAssignments.person_id = Person.person_id) AND
  (TeamAssignments.team_id = Team.team_id)
GROUP BY Person.person_id, Team.team_id ORDER BY person_id),
t_11_Engagements AS (SELECT * FROM (

    SELECT
      'core' AS team_id,
      'acme' AS client_id,
      'https://acme.example.com' AS website
   UNION ALL

    SELECT
      'platform' AS team_id,
      'globex' AS client_id,
      'https://globex.example.com' AS website

) AS UNUSED_TABLE_NAME  ),
t_9_Client AS (SELECT
  t_10_Engagements.client_id AS client_id,
  t_10_Engagements.website AS website
FROM
  t_11_Engagements AS t_10_Engagements
GROUP BY t_10_Engagements.client_id, t_10_Engagements.website ORDER BY client_id),
t_6_EngagedWith AS (SELECT
  t_7_Team.team_id AS team_id,
  Client.client_id AS client_id
FROM
  t_3_Team AS t_7_Team, t_9_Client AS Client, t_11_Engagements AS Engagements
WHERE
  (Engagements.team_id = t_7_Team.team_id) AND
  (Engagements.client_id = Client.client_id)
GROUP BY t_7_Team.team_id, Client.client_id ORDER BY team_id)
SELECT
  MemberOf.person_id AS person_id,
  EngagedWith.client_id AS client_id
FROM
  t_0_MemberOf AS MemberOf, t_6_EngagedWith AS EngagedWith
WHERE
  (EngagedWith.team_id = MemberOf.team_id)
GROUP BY MemberOf.person_id, EngagedWith.client_id ORDER BY person_id;

-- Executed on DuckDB:
| person_id | client_id |
|-----------|-----------|
| 1         | acme      |
| 2         | acme      |
| 3         | globex    |
(3 rows)

$ synalog.compile('knowledge_graphs.l', 'ReportsTo')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_3_Employees AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'Ada' AS name,
      'engineer' AS role,
      2 AS manager_id
   UNION ALL

    SELECT
      2 AS person_id,
      'Grace' AS name,
      'lead' AS role,
      null AS manager_id
   UNION ALL

    SELECT
      3 AS person_id,
      'Alan' AS name,
      'engineer' AS role,
      2 AS manager_id

) AS UNUSED_TABLE_NAME  ),
t_1_Person AS (SELECT
  t_2_Employees.person_id AS person_id,
  t_2_Employees.name AS name,
  t_2_Employees.role AS role
FROM
  t_3_Employees AS t_2_Employees
GROUP BY t_2_Employees.person_id, t_2_Employees.name, t_2_Employees.role ORDER BY person_id)
SELECT
  Person.person_id AS employee_id,
  t_0_Person.person_id AS manager_id
FROM
  t_1_Person AS Person, t_1_Person AS t_0_Person, t_3_Employees AS Employees
WHERE
  (t_0_Person.person_id IS NOT null) AND
  (Employees.person_id = Person.person_id) AND
  (Employees.manager_id = t_0_Person.person_id)
GROUP BY Person.person_id, t_0_Person.person_id ORDER BY employee_id;

-- Executed on DuckDB:
| employee_id | manager_id |
|-------------|------------|
| 1           | 2          |
| 3           | 2          |
(2 rows)

Choosing a time model

Before adding date columns to anything, decide what kind of time question the relation actually has to answer. Three questions settle it:

  1. Does anyone ever ask what this looked like at an earlier date?
  2. Does this data ever get corrected after the fact, backdated, or restated?
  3. Does anyone ever have to reproduce an answer as it was given, not as it is now understood?
Model Extra columns Answers Cost Typical use
Snapshot (no time) none What is true now None Reference data, categories, anything that only ever gains rows
Valid time valid_from, valid_to What was true on a given date Interval maintenance, overlap logic in joins Employments, contracts, assignments, prices, subscriptions
Transaction time recorded_from, recorded_to What the database held on a given date Append-only writes, versions accumulate Audit trails, agent memory, anything a regulator may inspect
Bitemporal all four Both, independently Both of the above, plus care in every query Late-arriving or corrected data with reporting obligations

Answer "no" to all three and use a snapshot; the cheapest correct model is a real design win, not a shortcut. Answer "yes" only to the first and valid time is enough. Reach for bitemporality when the second and third are also yes.

This is a decision per relation, not per graph. A graph where employments are bitemporal, team memberships carry valid time and job titles are a plain snapshot is normal and correct. Mixed models compose: a uni-temporal edge joins with a bitemporal one as long as the missing axis is treated as always valid.

Start smaller than you think

Time columns are easy to add to a relation later and hard to remove once rules depend on them. Model the handful of relations where history is genuinely consequential, and leave the rest as snapshots until a real question forces the change.

Temporal graphs

Most real relationships have a lifetime. An employment starts and ends, a contract is signed and expires, a device is assigned to a site for a while. A temporal edge carries that lifetime as columns, so a traversal can ask what the graph looked like at a given moment instead of only what it looks like today.

Two conventions make the arithmetic disappear:

  • Half-open intervals [valid_from, valid_to). The end of one period is the start of the next, with no gaps, no overlaps and no need to subtract a day anywhere.
  • A sentinel for the open end, "9999-12-31". ISO date strings compare correctly as strings, so "still true" needs no null handling and no special case in a filter.

Dates come out of the temporal pipeline, never out of raw timestamp arithmetic:

@OrderBy(MemberOf, "person_id", "valid_from");
MemberOf(person_id:, team_id:, valid_from:, valid_to:) distinct :-
  Person(person_id:), Team(team_id:),
  TeamAssignments(person_id:, team_id:, started_at:, ended_at:),
  valid_from == Substr(ToString(started_at), 1, 10),
  valid_to   == Substr(ToString(ended_at), 1, 10);

Closing intervals from an event log

Source systems often record only changes: one row per assignment, with no end date. The end of a period is the start of the next one for the same entity. Compute it with a self-join and a Min= aggregation, then handle the still-open period with negation:

## The next change for this person, when there is one.
@OrderBy(NextChange, "person_id", "changed_at");
NextChange(person_id:, changed_at:, next? Min= later) distinct :-
  Assignments(person_id:, changed_at:),
  Assignments(person_id:, changed_at: later),
  later > changed_at;

@OrderBy(MemberOf, "person_id", "valid_from");
MemberOf(person_id:, team_id:, valid_from:, valid_to:) distinct :-
  Person(person_id:), Team(team_id:),
  Assignments(person_id:, team_id:, changed_at: valid_from),
  NextChange(person_id:, changed_at: valid_from, next: valid_to);
MemberOf(person_id:, team_id:, valid_from:, valid_to:) distinct :-
  Person(person_id:), Team(team_id:),
  Assignments(person_id:, team_id:, changed_at: valid_from),
  ~NextChange(person_id:, changed_at: valid_from),
  valid_to == "9999-12-31";

Edges valid now

Today supplies the clock, and the half-open test reads exactly like the interval:

@OrderBy(ActiveMember, "person_id");
ActiveMember(person_id:, name:, team_id:) distinct :-
  MemberOf(person_id:, team_id:, valid_from:, valid_to:),
  Person(person_id:, name:),
  Today(date:),
  valid_from <= date, date < valid_to;

Overlap between edges

Two periods [s1, e1) and [s2, e2) overlap when s1 < e2 && s2 < e1. A derived edge should carry the intersection of the periods it was built from, and exist only when that intersection is non-empty:

@OrderBy(Colleague, "person_a", "person_b");
Colleague(person_a:, person_b:, team_id:, valid_from:, valid_to:) distinct :-
  MemberOf(person_id: person_a, team_id:, valid_from: a_from, valid_to: a_to),
  MemberOf(person_id: person_b, team_id:, valid_from: b_from, valid_to: b_to),
  person_a < person_b,
  valid_from == (if a_from > b_from then a_from else b_from),
  valid_to   == (if a_to < b_to then a_to else b_to),
  valid_from < valid_to;

Two people on the same team five years apart are not colleagues, and the rule says so without a single date function.

Time-respecting traversal

The same intersection carried through a recursive rule gives paths whose hops are simultaneously valid. A path that would need a hop to travel back in time is never derived:

@Recursive(ReachedBy, 10);
@OrderBy(ReachedBy, "source", "target");
ReachedBy(source:, target:, valid_from:, valid_to:) distinct :-
  HandedOver(source:, target:, valid_from:, valid_to:);
ReachedBy(source:, target:, valid_from:, valid_to:) distinct :-
  ReachedBy(source:, target: mid, valid_from: p_from, valid_to: p_to),
  HandedOver(source: mid, target:, valid_from: h_from, valid_to: h_to),
  valid_from == (if p_from > h_from then p_from else h_from),
  valid_to   == (if p_to < h_to then p_to else h_to),
  valid_from < valid_to;

Complete example

Interval closing from an event log, "active today", the overlap join and the time-respecting closure, in one runnable program:

# run: MemberOf, ActiveMember, Colleague, ReachedBy
@Engine("duckdb");

# Tables

## An event log: one row per role change, no end date.
Assignments(person_id: 1, team_id: "core", changed_at: "2024-01-01");
Assignments(person_id: 1, team_id: "platform", changed_at: "2026-02-01");
Assignments(person_id: 2, team_id: "core", changed_at: "2023-05-01");
Assignments(person_id: 3, team_id: "platform", changed_at: "2024-09-01");

People(person_id: 1, name: "Ada");
People(person_id: 2, name: "Grace");
People(person_id: 3, name: "Alan");

Teams(team_id: "core");
Teams(team_id: "platform");
Teams(team_id: "support");

## Directed handovers between teams, each valid over a period.
Handovers(source: "core", target: "platform", valid_from: "2024-01-01", valid_to: "2027-01-01");
Handovers(source: "platform", target: "support", valid_from: "2025-06-01", valid_to: "2027-01-01");
Handovers(source: "support", target: "core", valid_from: "2020-01-01", valid_to: "2021-01-01");

# Concepts

@OrderBy(Person, "person_id");
Person(person_id:, name:) distinct :- People(person_id:, name:);

@OrderBy(Team, "team_id");
Team(team_id:) distinct :- Teams(team_id:);

## The next change for a person, if there is one.
@OrderBy(NextChange, "person_id", "changed_at");
NextChange(person_id:, changed_at:, next? Min= later) distinct :-
  Assignments(person_id:, changed_at:),
  Assignments(person_id:, changed_at: later),
  later > changed_at;

## A valid-time edge, half-open: [valid_from, valid_to).
## An assignment ends where the next one starts, or never.
@OrderBy(MemberOf, "person_id", "valid_from");
MemberOf(person_id:, team_id:, valid_from:, valid_to:) distinct :-
  Person(person_id:),
  Team(team_id:),
  Assignments(person_id:, team_id:, changed_at: valid_from),
  NextChange(person_id:, changed_at: valid_from, next: valid_to);
MemberOf(person_id:, team_id:, valid_from:, valid_to:) distinct :-
  Person(person_id:),
  Team(team_id:),
  Assignments(person_id:, team_id:, changed_at: valid_from),
  ~NextChange(person_id:, changed_at: valid_from),
  valid_to == "9999-12-31";

@OrderBy(HandedOver, "source", "valid_from");
HandedOver(source:, target:, valid_from:, valid_to:) distinct :-
  Team(team_id: source),
  Team(team_id: target),
  Handovers(source:, target:, valid_from:, valid_to:);

# Rules

## Edges valid right now.
@OrderBy(ActiveMember, "person_id");
ActiveMember(person_id:, name:, team_id:) distinct :-
  MemberOf(person_id:, team_id:, valid_from:, valid_to:),
  Person(person_id:, name:),
  Today(date:),
  valid_from <= date, date < valid_to;

## Two memberships that overlap in time: the derived edge carries the
## intersection [max(starts), min(ends)) and exists only if it is non-empty.
@OrderBy(Colleague, "person_a", "person_b");
Colleague(person_a:, person_b:, team_id:, valid_from:, valid_to:) distinct :-
  MemberOf(person_id: person_a, team_id:, valid_from: a_from, valid_to: a_to),
  MemberOf(person_id: person_b, team_id:, valid_from: b_from, valid_to: b_to),
  person_a < person_b,
  valid_from == (if a_from > b_from then a_from else b_from),
  valid_to == (if a_to < b_to then a_to else b_to),
  valid_from < valid_to;

## Time-respecting reachability: a path exists only if every hop shares a
## common validity window, so the intersection is carried along the recursion.
@Recursive(ReachedBy, 10);
@OrderBy(ReachedBy, "source", "target");
ReachedBy(source:, target:, valid_from:, valid_to:) distinct :-
  HandedOver(source:, target:, valid_from:, valid_to:);
ReachedBy(source:, target:, valid_from:, valid_to:) distinct :-
  ReachedBy(source:, target: mid, valid_from: p_from, valid_to: p_to),
  HandedOver(source: mid, target:, valid_from: h_from, valid_to: h_to),
  valid_from == (if p_from > h_from then p_from else h_from),
  valid_to == (if p_to < h_to then p_to else h_to),
  valid_from < valid_to;
Generated SQL and execution results 
$ synalog.check('temporal_graph.l')
No errors found.

$ synalog.compile('temporal_graph.l', 'MemberOf')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_2_People AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'Ada' AS name
   UNION ALL

    SELECT
      2 AS person_id,
      'Grace' AS name
   UNION ALL

    SELECT
      3 AS person_id,
      'Alan' AS name

) AS UNUSED_TABLE_NAME  ),
t_1_Person AS (SELECT
  People.person_id AS person_id,
  People.name AS name
FROM
  t_2_People AS People
GROUP BY People.person_id, People.name ORDER BY person_id),
t_4_Teams AS (SELECT * FROM (

    SELECT
      'core' AS team_id
   UNION ALL

    SELECT
      'platform' AS team_id
   UNION ALL

    SELECT
      'support' AS team_id

) AS UNUSED_TABLE_NAME  ),
t_3_Team AS (SELECT
  Teams.team_id AS team_id
FROM
  t_4_Teams AS Teams
GROUP BY Teams.team_id ORDER BY team_id),
t_5_Assignments AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'core' AS team_id,
      '2024-01-01' AS changed_at
   UNION ALL

    SELECT
      1 AS person_id,
      'platform' AS team_id,
      '2026-02-01' AS changed_at
   UNION ALL

    SELECT
      2 AS person_id,
      'core' AS team_id,
      '2023-05-01' AS changed_at
   UNION ALL

    SELECT
      3 AS person_id,
      'platform' AS team_id,
      '2024-09-01' AS changed_at

) AS UNUSED_TABLE_NAME  ),
t_6_NextChange AS (SELECT
  t_7_Assignments.person_id AS person_id,
  t_7_Assignments.changed_at AS changed_at,
  MIN(t_8_Assignments.changed_at) AS next
FROM
  t_5_Assignments AS t_7_Assignments, t_5_Assignments AS t_8_Assignments
WHERE
  (t_8_Assignments.changed_at > t_7_Assignments.changed_at) AND
  (t_8_Assignments.person_id = t_7_Assignments.person_id)
GROUP BY t_7_Assignments.person_id, t_7_Assignments.changed_at ORDER BY person_id, changed_at),
t_0_MemberOf_MultBodyAggAux AS (SELECT * FROM (

    SELECT
      Person.person_id AS person_id,
      Team.team_id AS team_id,
      Assignments.changed_at AS valid_from,
      NextChange.next AS valid_to
    FROM
      t_1_Person AS Person, t_3_Team AS Team, t_5_Assignments AS Assignments, t_6_NextChange AS NextChange
    WHERE
      (Assignments.person_id = Person.person_id) AND
      (Assignments.team_id = Team.team_id) AND
      (NextChange.person_id = Person.person_id) AND
      (NextChange.changed_at = Assignments.changed_at)
   UNION ALL

    SELECT
      t_9_Person.person_id AS person_id,
      t_10_Team.team_id AS team_id,
      t_11_Assignments.changed_at AS valid_from,
      '9999-12-31' AS valid_to
    FROM
      t_1_Person AS t_9_Person, t_3_Team AS t_10_Team, t_5_Assignments AS t_11_Assignments
    WHERE
      ((SELECT
        MIN((CASE WHEN x_50.unnested_pod = 0 THEN 1 ELSE NULL END)) AS logica_value
      FROM
        t_6_NextChange AS t_14_NextChange, (select unnest([0]) as unnested_pod) as x_50
      WHERE
        (t_14_NextChange.person_id = t_9_Person.person_id) AND
        (t_14_NextChange.changed_at = t_11_Assignments.changed_at)) IS NULL) AND
      (t_11_Assignments.person_id = t_9_Person.person_id) AND
      (t_11_Assignments.team_id = t_10_Team.team_id)

) AS UNUSED_TABLE_NAME  )
SELECT
  MemberOf_MultBodyAggAux.person_id AS person_id,
  MemberOf_MultBodyAggAux.team_id AS team_id,
  MemberOf_MultBodyAggAux.valid_from AS valid_from,
  MemberOf_MultBodyAggAux.valid_to AS valid_to
FROM
  t_0_MemberOf_MultBodyAggAux AS MemberOf_MultBodyAggAux
GROUP BY MemberOf_MultBodyAggAux.person_id, MemberOf_MultBodyAggAux.team_id, MemberOf_MultBodyAggAux.valid_from, MemberOf_MultBodyAggAux.valid_to ORDER BY person_id, valid_from;

-- Executed on DuckDB:
| person_id | team_id  | valid_from | valid_to   |
|-----------|----------|------------|------------|
| 1         | core     | 2024-01-01 | 2026-02-01 |
| 1         | platform | 2026-02-01 | 9999-12-31 |
| 2         | core     | 2023-05-01 | 9999-12-31 |
| 3         | platform | 2024-09-01 | 9999-12-31 |
(4 rows)

$ synalog.compile('temporal_graph.l', 'ActiveMember')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_4_People AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'Ada' AS name
   UNION ALL

    SELECT
      2 AS person_id,
      'Grace' AS name
   UNION ALL

    SELECT
      3 AS person_id,
      'Alan' AS name

) AS UNUSED_TABLE_NAME  ),
t_3_Person AS (SELECT
  People.person_id AS person_id,
  People.name AS name
FROM
  t_4_People AS People
GROUP BY People.person_id, People.name ORDER BY person_id),
t_6_Teams AS (SELECT * FROM (

    SELECT
      'core' AS team_id
   UNION ALL

    SELECT
      'platform' AS team_id
   UNION ALL

    SELECT
      'support' AS team_id

) AS UNUSED_TABLE_NAME  ),
t_5_Team AS (SELECT
  Teams.team_id AS team_id
FROM
  t_6_Teams AS Teams
GROUP BY Teams.team_id ORDER BY team_id),
t_7_Assignments AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'core' AS team_id,
      '2024-01-01' AS changed_at
   UNION ALL

    SELECT
      1 AS person_id,
      'platform' AS team_id,
      '2026-02-01' AS changed_at
   UNION ALL

    SELECT
      2 AS person_id,
      'core' AS team_id,
      '2023-05-01' AS changed_at
   UNION ALL

    SELECT
      3 AS person_id,
      'platform' AS team_id,
      '2024-09-01' AS changed_at

) AS UNUSED_TABLE_NAME  ),
t_8_NextChange AS (SELECT
  t_9_Assignments.person_id AS person_id,
  t_9_Assignments.changed_at AS changed_at,
  MIN(t_10_Assignments.changed_at) AS next
FROM
  t_7_Assignments AS t_9_Assignments, t_7_Assignments AS t_10_Assignments
WHERE
  (t_10_Assignments.changed_at > t_9_Assignments.changed_at) AND
  (t_10_Assignments.person_id = t_9_Assignments.person_id)
GROUP BY t_9_Assignments.person_id, t_9_Assignments.changed_at ORDER BY person_id, changed_at),
t_1_MemberOf_MultBodyAggAux AS (SELECT * FROM (

    SELECT
      t_2_Person.person_id AS person_id,
      Team.team_id AS team_id,
      Assignments.changed_at AS valid_from,
      NextChange.next AS valid_to
    FROM
      t_3_Person AS t_2_Person, t_5_Team AS Team, t_7_Assignments AS Assignments, t_8_NextChange AS NextChange
    WHERE
      (Assignments.person_id = t_2_Person.person_id) AND
      (Assignments.team_id = Team.team_id) AND
      (NextChange.person_id = t_2_Person.person_id) AND
      (NextChange.changed_at = Assignments.changed_at)
   UNION ALL

    SELECT
      t_11_Person.person_id AS person_id,
      t_12_Team.team_id AS team_id,
      t_13_Assignments.changed_at AS valid_from,
      '9999-12-31' AS valid_to
    FROM
      t_3_Person AS t_11_Person, t_5_Team AS t_12_Team, t_7_Assignments AS t_13_Assignments
    WHERE
      ((SELECT
        MIN((CASE WHEN x_60.unnested_pod = 0 THEN 1 ELSE NULL END)) AS logica_value
      FROM
        t_8_NextChange AS t_16_NextChange, (select unnest([0]) as unnested_pod) as x_60
      WHERE
        (t_16_NextChange.person_id = t_11_Person.person_id) AND
        (t_16_NextChange.changed_at = t_13_Assignments.changed_at)) IS NULL) AND
      (t_13_Assignments.person_id = t_11_Person.person_id) AND
      (t_13_Assignments.team_id = t_12_Team.team_id)

) AS UNUSED_TABLE_NAME  ),
t_0_MemberOf AS (SELECT
  MemberOf_MultBodyAggAux.person_id AS person_id,
  MemberOf_MultBodyAggAux.team_id AS team_id,
  MemberOf_MultBodyAggAux.valid_from AS valid_from,
  MemberOf_MultBodyAggAux.valid_to AS valid_to
FROM
  t_1_MemberOf_MultBodyAggAux AS MemberOf_MultBodyAggAux
GROUP BY MemberOf_MultBodyAggAux.person_id, MemberOf_MultBodyAggAux.team_id, MemberOf_MultBodyAggAux.valid_from, MemberOf_MultBodyAggAux.valid_to ORDER BY person_id, valid_from)
SELECT
  MemberOf.person_id AS person_id,
  Person.name AS name,
  MemberOf.team_id AS team_id
FROM
  t_0_MemberOf AS MemberOf, t_3_Person AS Person, (SELECT strftime(current_date, '%Y-%m-%d') AS date) AS Today
WHERE
  (MemberOf.valid_from <= Today.date) AND
  (Today.date < MemberOf.valid_to) AND
  (Person.person_id = MemberOf.person_id)
GROUP BY MemberOf.person_id, Person.name, MemberOf.team_id ORDER BY person_id;

-- Executed on DuckDB:
| person_id | name  | team_id  |
|-----------|-------|----------|
| 1         | Ada   | platform |
| 2         | Grace | core     |
| 3         | Alan  | platform |
(3 rows)

$ synalog.compile('temporal_graph.l', 'Colleague')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_4_People AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'Ada' AS name
   UNION ALL

    SELECT
      2 AS person_id,
      'Grace' AS name
   UNION ALL

    SELECT
      3 AS person_id,
      'Alan' AS name

) AS UNUSED_TABLE_NAME  ),
t_3_Person AS (SELECT
  People.person_id AS person_id,
  People.name AS name
FROM
  t_4_People AS People
GROUP BY People.person_id, People.name ORDER BY person_id),
t_6_Teams AS (SELECT * FROM (

    SELECT
      'core' AS team_id
   UNION ALL

    SELECT
      'platform' AS team_id
   UNION ALL

    SELECT
      'support' AS team_id

) AS UNUSED_TABLE_NAME  ),
t_5_Team AS (SELECT
  Teams.team_id AS team_id
FROM
  t_6_Teams AS Teams
GROUP BY Teams.team_id ORDER BY team_id),
t_7_Assignments AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'core' AS team_id,
      '2024-01-01' AS changed_at
   UNION ALL

    SELECT
      1 AS person_id,
      'platform' AS team_id,
      '2026-02-01' AS changed_at
   UNION ALL

    SELECT
      2 AS person_id,
      'core' AS team_id,
      '2023-05-01' AS changed_at
   UNION ALL

    SELECT
      3 AS person_id,
      'platform' AS team_id,
      '2024-09-01' AS changed_at

) AS UNUSED_TABLE_NAME  ),
t_8_NextChange AS (SELECT
  t_9_Assignments.person_id AS person_id,
  t_9_Assignments.changed_at AS changed_at,
  MIN(t_10_Assignments.changed_at) AS next
FROM
  t_7_Assignments AS t_9_Assignments, t_7_Assignments AS t_10_Assignments
WHERE
  (t_10_Assignments.changed_at > t_9_Assignments.changed_at) AND
  (t_10_Assignments.person_id = t_9_Assignments.person_id)
GROUP BY t_9_Assignments.person_id, t_9_Assignments.changed_at ORDER BY person_id, changed_at),
t_2_MemberOf_MultBodyAggAux AS (SELECT * FROM (

    SELECT
      Person.person_id AS person_id,
      Team.team_id AS team_id,
      Assignments.changed_at AS valid_from,
      NextChange.next AS valid_to
    FROM
      t_3_Person AS Person, t_5_Team AS Team, t_7_Assignments AS Assignments, t_8_NextChange AS NextChange
    WHERE
      (Assignments.person_id = Person.person_id) AND
      (Assignments.team_id = Team.team_id) AND
      (NextChange.person_id = Person.person_id) AND
      (NextChange.changed_at = Assignments.changed_at)
   UNION ALL

    SELECT
      t_11_Person.person_id AS person_id,
      t_12_Team.team_id AS team_id,
      t_13_Assignments.changed_at AS valid_from,
      '9999-12-31' AS valid_to
    FROM
      t_3_Person AS t_11_Person, t_5_Team AS t_12_Team, t_7_Assignments AS t_13_Assignments
    WHERE
      ((SELECT
        MIN((CASE WHEN x_63.unnested_pod = 0 THEN 1 ELSE NULL END)) AS logica_value
      FROM
        t_8_NextChange AS t_16_NextChange, (select unnest([0]) as unnested_pod) as x_63
      WHERE
        (t_16_NextChange.person_id = t_11_Person.person_id) AND
        (t_16_NextChange.changed_at = t_13_Assignments.changed_at)) IS NULL) AND
      (t_13_Assignments.person_id = t_11_Person.person_id) AND
      (t_13_Assignments.team_id = t_12_Team.team_id)

) AS UNUSED_TABLE_NAME  ),
t_1_MemberOf AS (SELECT
  MemberOf_MultBodyAggAux.person_id AS person_id,
  MemberOf_MultBodyAggAux.team_id AS team_id,
  MemberOf_MultBodyAggAux.valid_from AS valid_from,
  MemberOf_MultBodyAggAux.valid_to AS valid_to
FROM
  t_2_MemberOf_MultBodyAggAux AS MemberOf_MultBodyAggAux
GROUP BY MemberOf_MultBodyAggAux.person_id, MemberOf_MultBodyAggAux.team_id, MemberOf_MultBodyAggAux.valid_from, MemberOf_MultBodyAggAux.valid_to ORDER BY person_id, valid_from)
SELECT
  MemberOf.person_id AS person_a,
  t_0_MemberOf.person_id AS person_b,
  MemberOf.team_id AS team_id,
  CASE WHEN (MemberOf.valid_from > t_0_MemberOf.valid_from) THEN MemberOf.valid_from ELSE t_0_MemberOf.valid_from END AS valid_from,
  CASE WHEN (MemberOf.valid_to < t_0_MemberOf.valid_to) THEN MemberOf.valid_to ELSE t_0_MemberOf.valid_to END AS valid_to
FROM
  t_1_MemberOf AS MemberOf, t_1_MemberOf AS t_0_MemberOf
WHERE
  (MemberOf.person_id < t_0_MemberOf.person_id) AND
  (CASE WHEN (MemberOf.valid_from > t_0_MemberOf.valid_from) THEN MemberOf.valid_from ELSE t_0_MemberOf.valid_from END < CASE WHEN (MemberOf.valid_to < t_0_MemberOf.valid_to) THEN MemberOf.valid_to ELSE t_0_MemberOf.valid_to END) AND
  (t_0_MemberOf.team_id = MemberOf.team_id)
GROUP BY MemberOf.person_id, t_0_MemberOf.person_id, MemberOf.team_id, CASE WHEN (MemberOf.valid_from > t_0_MemberOf.valid_from) THEN MemberOf.valid_from ELSE t_0_MemberOf.valid_from END, CASE WHEN (MemberOf.valid_to < t_0_MemberOf.valid_to) THEN MemberOf.valid_to ELSE t_0_MemberOf.valid_to END ORDER BY person_a, person_b;

-- Executed on DuckDB:
| person_a | person_b | team_id  | valid_from | valid_to   |
|----------|----------|----------|------------|------------|
| 1        | 2        | core     | 2024-01-01 | 2026-02-01 |
| 1        | 3        | platform | 2026-02-01 | 9999-12-31 |
(2 rows)

$ synalog.compile('temporal_graph.l', 'ReachedBy')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_4_Teams AS (SELECT * FROM (

    SELECT
      'core' AS team_id
   UNION ALL

    SELECT
      'platform' AS team_id
   UNION ALL

    SELECT
      'support' AS team_id

) AS UNUSED_TABLE_NAME  ),
t_3_Team AS (SELECT
  Teams.team_id AS team_id
FROM
  t_4_Teams AS Teams
GROUP BY Teams.team_id ORDER BY team_id),
t_6_Handovers AS (SELECT * FROM (

    SELECT
      'core' AS source,
      'platform' AS target,
      '2024-01-01' AS valid_from,
      '2027-01-01' AS valid_to
   UNION ALL

    SELECT
      'platform' AS source,
      'support' AS target,
      '2025-06-01' AS valid_from,
      '2027-01-01' AS valid_to
   UNION ALL

    SELECT
      'support' AS source,
      'core' AS target,
      '2020-01-01' AS valid_from,
      '2021-01-01' AS valid_to

) AS UNUSED_TABLE_NAME  ),
t_1_HandedOver AS (SELECT
  Team.team_id AS source,
  t_2_Team.team_id AS target,
  Handovers.valid_from AS valid_from,
  Handovers.valid_to AS valid_to
FROM
  t_3_Team AS Team, t_3_Team AS t_2_Team, t_6_Handovers AS Handovers
WHERE
  (Handovers.source = Team.team_id) AND
  (Handovers.target = t_2_Team.team_id)
GROUP BY Team.team_id, t_2_Team.team_id, Handovers.valid_from, Handovers.valid_to ORDER BY source, valid_from),
t_48_ReachedBy_MultBodyAggAux_recursive_head_f1 AS (SELECT * FROM (

    SELECT
      t_49_HandedOver.source AS source,
      t_49_HandedOver.target AS target,
      t_49_HandedOver.valid_from AS valid_from,
      t_49_HandedOver.valid_to AS valid_to
    FROM
      t_1_HandedOver AS t_49_HandedOver

) AS UNUSED_TABLE_NAME  ),
t_47_ReachedBy_r0 AS (SELECT
  ReachedBy_MultBodyAggAux_recursive_head_f1.source AS source,
  ReachedBy_MultBodyAggAux_recursive_head_f1.target AS target,
  ReachedBy_MultBodyAggAux_recursive_head_f1.valid_from AS valid_from,
  ReachedBy_MultBodyAggAux_recursive_head_f1.valid_to AS valid_to
FROM
  t_48_ReachedBy_MultBodyAggAux_recursive_head_f1 AS ReachedBy_MultBodyAggAux_recursive_head_f1
GROUP BY ReachedBy_MultBodyAggAux_recursive_head_f1.source, ReachedBy_MultBodyAggAux_recursive_head_f1.target, ReachedBy_MultBodyAggAux_recursive_head_f1.valid_from, ReachedBy_MultBodyAggAux_recursive_head_f1.valid_to ORDER BY source, target),
t_44_ReachedBy_MultBodyAggAux_recursive_head_f2 AS (SELECT * FROM (

    SELECT
      t_45_HandedOver.source AS source,
      t_45_HandedOver.target AS target,
      t_45_HandedOver.valid_from AS valid_from,
      t_45_HandedOver.valid_to AS valid_to
    FROM
      t_1_HandedOver AS t_45_HandedOver
   UNION ALL

    SELECT
      ReachedBy_r0.source AS source,
      t_46_HandedOver.target AS target,
      CASE WHEN (ReachedBy_r0.valid_from > t_46_HandedOver.valid_from) THEN ReachedBy_r0.valid_from ELSE t_46_HandedOver.valid_from END AS valid_from,
      CASE WHEN (ReachedBy_r0.valid_to < t_46_HandedOver.valid_to) THEN ReachedBy_r0.valid_to ELSE t_46_HandedOver.valid_to END AS valid_to
    FROM
      t_47_ReachedBy_r0 AS ReachedBy_r0, t_1_HandedOver AS t_46_HandedOver
    WHERE
      (CASE WHEN (ReachedBy_r0.valid_from > t_46_HandedOver.valid_from) THEN ReachedBy_r0.valid_from ELSE t_46_HandedOver.valid_from END < CASE WHEN (ReachedBy_r0.valid_to < t_46_HandedOver.valid_to) THEN ReachedBy_r0.valid_to ELSE t_46_HandedOver.valid_to END) AND
      (t_46_HandedOver.source = ReachedBy_r0.target)

) AS UNUSED_TABLE_NAME  ),
t_43_ReachedBy_r1 AS (SELECT
  ReachedBy_MultBodyAggAux_recursive_head_f2.source AS source,
  ReachedBy_MultBodyAggAux_recursive_head_f2.target AS target,
  ReachedBy_MultBodyAggAux_recursive_head_f2.valid_from AS valid_from,
  ReachedBy_MultBodyAggAux_recursive_head_f2.valid_to AS valid_to
FROM
  t_44_ReachedBy_MultBodyAggAux_recursive_head_f2 AS ReachedBy_MultBodyAggAux_recursive_head_f2
GROUP BY ReachedBy_MultBodyAggAux_recursive_head_f2.source, ReachedBy_MultBodyAggAux_recursive_head_f2.target, ReachedBy_MultBodyAggAux_recursive_head_f2.valid_from, ReachedBy_MultBodyAggAux_recursive_head_f2.valid_to ORDER BY source, target),
t_40_ReachedBy_MultBodyAggAux_recursive_head_f3 AS (SELECT * FROM (

    SELECT
      t_41_HandedOver.source AS source,
      t_41_HandedOver.target AS target,
      t_41_HandedOver.valid_from AS valid_from,
      t_41_HandedOver.valid_to AS valid_to
    FROM
      t_1_HandedOver AS t_41_HandedOver
   UNION ALL

    SELECT
      ReachedBy_r1.source AS source,
      t_42_HandedOver.target AS target,
      CASE WHEN (ReachedBy_r1.valid_from > t_42_HandedOver.valid_from) THEN ReachedBy_r1.valid_from ELSE t_42_HandedOver.valid_from END AS valid_from,
      CASE WHEN (ReachedBy_r1.valid_to < t_42_HandedOver.valid_to) THEN ReachedBy_r1.valid_to ELSE t_42_HandedOver.valid_to END AS valid_to
    FROM
      t_43_ReachedBy_r1 AS ReachedBy_r1, t_1_HandedOver AS t_42_HandedOver
    WHERE
      (CASE WHEN (ReachedBy_r1.valid_from > t_42_HandedOver.valid_from) THEN ReachedBy_r1.valid_from ELSE t_42_HandedOver.valid_from END < CASE WHEN (ReachedBy_r1.valid_to < t_42_HandedOver.valid_to) THEN ReachedBy_r1.valid_to ELSE t_42_HandedOver.valid_to END) AND
      (t_42_HandedOver.source = ReachedBy_r1.target)

) AS UNUSED_TABLE_NAME  ),
t_39_ReachedBy_r2 AS (SELECT
  ReachedBy_MultBodyAggAux_recursive_head_f3.source AS source,
  ReachedBy_MultBodyAggAux_recursive_head_f3.target AS target,
  ReachedBy_MultBodyAggAux_recursive_head_f3.valid_from AS valid_from,
  ReachedBy_MultBodyAggAux_recursive_head_f3.valid_to AS valid_to
FROM
  t_40_ReachedBy_MultBodyAggAux_recursive_head_f3 AS ReachedBy_MultBodyAggAux_recursive_head_f3
GROUP BY ReachedBy_MultBodyAggAux_recursive_head_f3.source, ReachedBy_MultBodyAggAux_recursive_head_f3.target, ReachedBy_MultBodyAggAux_recursive_head_f3.valid_from, ReachedBy_MultBodyAggAux_recursive_head_f3.valid_to ORDER BY source, target),
t_36_ReachedBy_MultBodyAggAux_recursive_head_f4 AS (SELECT * FROM (

    SELECT
      t_37_HandedOver.source AS source,
      t_37_HandedOver.target AS target,
      t_37_HandedOver.valid_from AS valid_from,
      t_37_HandedOver.valid_to AS valid_to
    FROM
      t_1_HandedOver AS t_37_HandedOver
   UNION ALL

    SELECT
      ReachedBy_r2.source AS source,
      t_38_HandedOver.target AS target,
      CASE WHEN (ReachedBy_r2.valid_from > t_38_HandedOver.valid_from) THEN ReachedBy_r2.valid_from ELSE t_38_HandedOver.valid_from END AS valid_from,
      CASE WHEN (ReachedBy_r2.valid_to < t_38_HandedOver.valid_to) THEN ReachedBy_r2.valid_to ELSE t_38_HandedOver.valid_to END AS valid_to
    FROM
      t_39_ReachedBy_r2 AS ReachedBy_r2, t_1_HandedOver AS t_38_HandedOver
    WHERE
      (CASE WHEN (ReachedBy_r2.valid_from > t_38_HandedOver.valid_from) THEN ReachedBy_r2.valid_from ELSE t_38_HandedOver.valid_from END < CASE WHEN (ReachedBy_r2.valid_to < t_38_HandedOver.valid_to) THEN ReachedBy_r2.valid_to ELSE t_38_HandedOver.valid_to END) AND
      (t_38_HandedOver.source = ReachedBy_r2.target)

) AS UNUSED_TABLE_NAME  ),
t_35_ReachedBy_r3 AS (SELECT
  ReachedBy_MultBodyAggAux_recursive_head_f4.source AS source,
  ReachedBy_MultBodyAggAux_recursive_head_f4.target AS target,
  ReachedBy_MultBodyAggAux_recursive_head_f4.valid_from AS valid_from,
  ReachedBy_MultBodyAggAux_recursive_head_f4.valid_to AS valid_to
FROM
  t_36_ReachedBy_MultBodyAggAux_recursive_head_f4 AS ReachedBy_MultBodyAggAux_recursive_head_f4
GROUP BY ReachedBy_MultBodyAggAux_recursive_head_f4.source, ReachedBy_MultBodyAggAux_recursive_head_f4.target, ReachedBy_MultBodyAggAux_recursive_head_f4.valid_from, ReachedBy_MultBodyAggAux_recursive_head_f4.valid_to ORDER BY source, target),
t_32_ReachedBy_MultBodyAggAux_recursive_head_f5 AS (SELECT * FROM (

    SELECT
      t_33_HandedOver.source AS source,
      t_33_HandedOver.target AS target,
      t_33_HandedOver.valid_from AS valid_from,
      t_33_HandedOver.valid_to AS valid_to
    FROM
      t_1_HandedOver AS t_33_HandedOver
   UNION ALL

    SELECT
      ReachedBy_r3.source AS source,
      t_34_HandedOver.target AS target,
      CASE WHEN (ReachedBy_r3.valid_from > t_34_HandedOver.valid_from) THEN ReachedBy_r3.valid_from ELSE t_34_HandedOver.valid_from END AS valid_from,
      CASE WHEN (ReachedBy_r3.valid_to < t_34_HandedOver.valid_to) THEN ReachedBy_r3.valid_to ELSE t_34_HandedOver.valid_to END AS valid_to
    FROM
      t_35_ReachedBy_r3 AS ReachedBy_r3, t_1_HandedOver AS t_34_HandedOver
    WHERE
      (CASE WHEN (ReachedBy_r3.valid_from > t_34_HandedOver.valid_from) THEN ReachedBy_r3.valid_from ELSE t_34_HandedOver.valid_from END < CASE WHEN (ReachedBy_r3.valid_to < t_34_HandedOver.valid_to) THEN ReachedBy_r3.valid_to ELSE t_34_HandedOver.valid_to END) AND
      (t_34_HandedOver.source = ReachedBy_r3.target)

) AS UNUSED_TABLE_NAME  ),
t_31_ReachedBy_r4 AS (SELECT
  ReachedBy_MultBodyAggAux_recursive_head_f5.source AS source,
  ReachedBy_MultBodyAggAux_recursive_head_f5.target AS target,
  ReachedBy_MultBodyAggAux_recursive_head_f5.valid_from AS valid_from,
  ReachedBy_MultBodyAggAux_recursive_head_f5.valid_to AS valid_to
FROM
  t_32_ReachedBy_MultBodyAggAux_recursive_head_f5 AS ReachedBy_MultBodyAggAux_recursive_head_f5
GROUP BY ReachedBy_MultBodyAggAux_recursive_head_f5.source, ReachedBy_MultBodyAggAux_recursive_head_f5.target, ReachedBy_MultBodyAggAux_recursive_head_f5.valid_from, ReachedBy_MultBodyAggAux_recursive_head_f5.valid_to ORDER BY source, target),
t_28_ReachedBy_MultBodyAggAux_recursive_head_f6 AS (SELECT * FROM (

    SELECT
      t_29_HandedOver.source AS source,
      t_29_HandedOver.target AS target,
      t_29_HandedOver.valid_from AS valid_from,
      t_29_HandedOver.valid_to AS valid_to
    FROM
      t_1_HandedOver AS t_29_HandedOver
   UNION ALL

    SELECT
      ReachedBy_r4.source AS source,
      t_30_HandedOver.target AS target,
      CASE WHEN (ReachedBy_r4.valid_from > t_30_HandedOver.valid_from) THEN ReachedBy_r4.valid_from ELSE t_30_HandedOver.valid_from END AS valid_from,
      CASE WHEN (ReachedBy_r4.valid_to < t_30_HandedOver.valid_to) THEN ReachedBy_r4.valid_to ELSE t_30_HandedOver.valid_to END AS valid_to
    FROM
      t_31_ReachedBy_r4 AS ReachedBy_r4, t_1_HandedOver AS t_30_HandedOver
    WHERE
      (CASE WHEN (ReachedBy_r4.valid_from > t_30_HandedOver.valid_from) THEN ReachedBy_r4.valid_from ELSE t_30_HandedOver.valid_from END < CASE WHEN (ReachedBy_r4.valid_to < t_30_HandedOver.valid_to) THEN ReachedBy_r4.valid_to ELSE t_30_HandedOver.valid_to END) AND
      (t_30_HandedOver.source = ReachedBy_r4.target)

) AS UNUSED_TABLE_NAME  ),
t_27_ReachedBy_r5 AS (SELECT
  ReachedBy_MultBodyAggAux_recursive_head_f6.source AS source,
  ReachedBy_MultBodyAggAux_recursive_head_f6.target AS target,
  ReachedBy_MultBodyAggAux_recursive_head_f6.valid_from AS valid_from,
  ReachedBy_MultBodyAggAux_recursive_head_f6.valid_to AS valid_to
FROM
  t_28_ReachedBy_MultBodyAggAux_recursive_head_f6 AS ReachedBy_MultBodyAggAux_recursive_head_f6
GROUP BY ReachedBy_MultBodyAggAux_recursive_head_f6.source, ReachedBy_MultBodyAggAux_recursive_head_f6.target, ReachedBy_MultBodyAggAux_recursive_head_f6.valid_from, ReachedBy_MultBodyAggAux_recursive_head_f6.valid_to ORDER BY source, target),
t_24_ReachedBy_MultBodyAggAux_recursive_head_f7 AS (SELECT * FROM (

    SELECT
      t_25_HandedOver.source AS source,
      t_25_HandedOver.target AS target,
      t_25_HandedOver.valid_from AS valid_from,
      t_25_HandedOver.valid_to AS valid_to
    FROM
      t_1_HandedOver AS t_25_HandedOver
   UNION ALL

    SELECT
      ReachedBy_r5.source AS source,
      t_26_HandedOver.target AS target,
      CASE WHEN (ReachedBy_r5.valid_from > t_26_HandedOver.valid_from) THEN ReachedBy_r5.valid_from ELSE t_26_HandedOver.valid_from END AS valid_from,
      CASE WHEN (ReachedBy_r5.valid_to < t_26_HandedOver.valid_to) THEN ReachedBy_r5.valid_to ELSE t_26_HandedOver.valid_to END AS valid_to
    FROM
      t_27_ReachedBy_r5 AS ReachedBy_r5, t_1_HandedOver AS t_26_HandedOver
    WHERE
      (CASE WHEN (ReachedBy_r5.valid_from > t_26_HandedOver.valid_from) THEN ReachedBy_r5.valid_from ELSE t_26_HandedOver.valid_from END < CASE WHEN (ReachedBy_r5.valid_to < t_26_HandedOver.valid_to) THEN ReachedBy_r5.valid_to ELSE t_26_HandedOver.valid_to END) AND
      (t_26_HandedOver.source = ReachedBy_r5.target)

) AS UNUSED_TABLE_NAME  ),
t_23_ReachedBy_r6 AS (SELECT
  ReachedBy_MultBodyAggAux_recursive_head_f7.source AS source,
  ReachedBy_MultBodyAggAux_recursive_head_f7.target AS target,
  ReachedBy_MultBodyAggAux_recursive_head_f7.valid_from AS valid_from,
  ReachedBy_MultBodyAggAux_recursive_head_f7.valid_to AS valid_to
FROM
  t_24_ReachedBy_MultBodyAggAux_recursive_head_f7 AS ReachedBy_MultBodyAggAux_recursive_head_f7
GROUP BY ReachedBy_MultBodyAggAux_recursive_head_f7.source, ReachedBy_MultBodyAggAux_recursive_head_f7.target, ReachedBy_MultBodyAggAux_recursive_head_f7.valid_from, ReachedBy_MultBodyAggAux_recursive_head_f7.valid_to ORDER BY source, target),
t_20_ReachedBy_MultBodyAggAux_recursive_head_f8 AS (SELECT * FROM (

    SELECT
      t_21_HandedOver.source AS source,
      t_21_HandedOver.target AS target,
      t_21_HandedOver.valid_from AS valid_from,
      t_21_HandedOver.valid_to AS valid_to
    FROM
      t_1_HandedOver AS t_21_HandedOver
   UNION ALL

    SELECT
      ReachedBy_r6.source AS source,
      t_22_HandedOver.target AS target,
      CASE WHEN (ReachedBy_r6.valid_from > t_22_HandedOver.valid_from) THEN ReachedBy_r6.valid_from ELSE t_22_HandedOver.valid_from END AS valid_from,
      CASE WHEN (ReachedBy_r6.valid_to < t_22_HandedOver.valid_to) THEN ReachedBy_r6.valid_to ELSE t_22_HandedOver.valid_to END AS valid_to
    FROM
      t_23_ReachedBy_r6 AS ReachedBy_r6, t_1_HandedOver AS t_22_HandedOver
    WHERE
      (CASE WHEN (ReachedBy_r6.valid_from > t_22_HandedOver.valid_from) THEN ReachedBy_r6.valid_from ELSE t_22_HandedOver.valid_from END < CASE WHEN (ReachedBy_r6.valid_to < t_22_HandedOver.valid_to) THEN ReachedBy_r6.valid_to ELSE t_22_HandedOver.valid_to END) AND
      (t_22_HandedOver.source = ReachedBy_r6.target)

) AS UNUSED_TABLE_NAME  ),
t_19_ReachedBy_r7 AS (SELECT
  ReachedBy_MultBodyAggAux_recursive_head_f8.source AS source,
  ReachedBy_MultBodyAggAux_recursive_head_f8.target AS target,
  ReachedBy_MultBodyAggAux_recursive_head_f8.valid_from AS valid_from,
  ReachedBy_MultBodyAggAux_recursive_head_f8.valid_to AS valid_to
FROM
  t_20_ReachedBy_MultBodyAggAux_recursive_head_f8 AS ReachedBy_MultBodyAggAux_recursive_head_f8
GROUP BY ReachedBy_MultBodyAggAux_recursive_head_f8.source, ReachedBy_MultBodyAggAux_recursive_head_f8.target, ReachedBy_MultBodyAggAux_recursive_head_f8.valid_from, ReachedBy_MultBodyAggAux_recursive_head_f8.valid_to ORDER BY source, target),
t_16_ReachedBy_MultBodyAggAux_recursive_head_f9 AS (SELECT * FROM (

    SELECT
      t_17_HandedOver.source AS source,
      t_17_HandedOver.target AS target,
      t_17_HandedOver.valid_from AS valid_from,
      t_17_HandedOver.valid_to AS valid_to
    FROM
      t_1_HandedOver AS t_17_HandedOver
   UNION ALL

    SELECT
      ReachedBy_r7.source AS source,
      t_18_HandedOver.target AS target,
      CASE WHEN (ReachedBy_r7.valid_from > t_18_HandedOver.valid_from) THEN ReachedBy_r7.valid_from ELSE t_18_HandedOver.valid_from END AS valid_from,
      CASE WHEN (ReachedBy_r7.valid_to < t_18_HandedOver.valid_to) THEN ReachedBy_r7.valid_to ELSE t_18_HandedOver.valid_to END AS valid_to
    FROM
      t_19_ReachedBy_r7 AS ReachedBy_r7, t_1_HandedOver AS t_18_HandedOver
    WHERE
      (CASE WHEN (ReachedBy_r7.valid_from > t_18_HandedOver.valid_from) THEN ReachedBy_r7.valid_from ELSE t_18_HandedOver.valid_from END < CASE WHEN (ReachedBy_r7.valid_to < t_18_HandedOver.valid_to) THEN ReachedBy_r7.valid_to ELSE t_18_HandedOver.valid_to END) AND
      (t_18_HandedOver.source = ReachedBy_r7.target)

) AS UNUSED_TABLE_NAME  ),
t_15_ReachedBy_r8 AS (SELECT
  ReachedBy_MultBodyAggAux_recursive_head_f9.source AS source,
  ReachedBy_MultBodyAggAux_recursive_head_f9.target AS target,
  ReachedBy_MultBodyAggAux_recursive_head_f9.valid_from AS valid_from,
  ReachedBy_MultBodyAggAux_recursive_head_f9.valid_to AS valid_to
FROM
  t_16_ReachedBy_MultBodyAggAux_recursive_head_f9 AS ReachedBy_MultBodyAggAux_recursive_head_f9
GROUP BY ReachedBy_MultBodyAggAux_recursive_head_f9.source, ReachedBy_MultBodyAggAux_recursive_head_f9.target, ReachedBy_MultBodyAggAux_recursive_head_f9.valid_from, ReachedBy_MultBodyAggAux_recursive_head_f9.valid_to ORDER BY source, target),
t_9_ReachedBy_MultBodyAggAux_recursive_head_f10 AS (SELECT * FROM (

    SELECT
      t_10_HandedOver.source AS source,
      t_10_HandedOver.target AS target,
      t_10_HandedOver.valid_from AS valid_from,
      t_10_HandedOver.valid_to AS valid_to
    FROM
      t_1_HandedOver AS t_10_HandedOver
   UNION ALL

    SELECT
      ReachedBy_r8.source AS source,
      t_14_HandedOver.target AS target,
      CASE WHEN (ReachedBy_r8.valid_from > t_14_HandedOver.valid_from) THEN ReachedBy_r8.valid_from ELSE t_14_HandedOver.valid_from END AS valid_from,
      CASE WHEN (ReachedBy_r8.valid_to < t_14_HandedOver.valid_to) THEN ReachedBy_r8.valid_to ELSE t_14_HandedOver.valid_to END AS valid_to
    FROM
      t_15_ReachedBy_r8 AS ReachedBy_r8, t_1_HandedOver AS t_14_HandedOver
    WHERE
      (CASE WHEN (ReachedBy_r8.valid_from > t_14_HandedOver.valid_from) THEN ReachedBy_r8.valid_from ELSE t_14_HandedOver.valid_from END < CASE WHEN (ReachedBy_r8.valid_to < t_14_HandedOver.valid_to) THEN ReachedBy_r8.valid_to ELSE t_14_HandedOver.valid_to END) AND
      (t_14_HandedOver.source = ReachedBy_r8.target)

) AS UNUSED_TABLE_NAME  ),
t_8_ReachedBy_r9 AS (SELECT
  ReachedBy_MultBodyAggAux_recursive_head_f10.source AS source,
  ReachedBy_MultBodyAggAux_recursive_head_f10.target AS target,
  ReachedBy_MultBodyAggAux_recursive_head_f10.valid_from AS valid_from,
  ReachedBy_MultBodyAggAux_recursive_head_f10.valid_to AS valid_to
FROM
  t_9_ReachedBy_MultBodyAggAux_recursive_head_f10 AS ReachedBy_MultBodyAggAux_recursive_head_f10
GROUP BY ReachedBy_MultBodyAggAux_recursive_head_f10.source, ReachedBy_MultBodyAggAux_recursive_head_f10.target, ReachedBy_MultBodyAggAux_recursive_head_f10.valid_from, ReachedBy_MultBodyAggAux_recursive_head_f10.valid_to ORDER BY source, target),
t_0_ReachedBy_MultBodyAggAux_recursive_head_f11 AS (SELECT * FROM (

    SELECT
      HandedOver.source AS source,
      HandedOver.target AS target,
      HandedOver.valid_from AS valid_from,
      HandedOver.valid_to AS valid_to
    FROM
      t_1_HandedOver AS HandedOver
   UNION ALL

    SELECT
      ReachedBy_r9.source AS source,
      t_7_HandedOver.target AS target,
      CASE WHEN (ReachedBy_r9.valid_from > t_7_HandedOver.valid_from) THEN ReachedBy_r9.valid_from ELSE t_7_HandedOver.valid_from END AS valid_from,
      CASE WHEN (ReachedBy_r9.valid_to < t_7_HandedOver.valid_to) THEN ReachedBy_r9.valid_to ELSE t_7_HandedOver.valid_to END AS valid_to
    FROM
      t_8_ReachedBy_r9 AS ReachedBy_r9, t_1_HandedOver AS t_7_HandedOver
    WHERE
      (CASE WHEN (ReachedBy_r9.valid_from > t_7_HandedOver.valid_from) THEN ReachedBy_r9.valid_from ELSE t_7_HandedOver.valid_from END < CASE WHEN (ReachedBy_r9.valid_to < t_7_HandedOver.valid_to) THEN ReachedBy_r9.valid_to ELSE t_7_HandedOver.valid_to END) AND
      (t_7_HandedOver.source = ReachedBy_r9.target)

) AS UNUSED_TABLE_NAME  )
SELECT
  ReachedBy_MultBodyAggAux_recursive_head_f11.source AS source,
  ReachedBy_MultBodyAggAux_recursive_head_f11.target AS target,
  ReachedBy_MultBodyAggAux_recursive_head_f11.valid_from AS valid_from,
  ReachedBy_MultBodyAggAux_recursive_head_f11.valid_to AS valid_to
FROM
  t_0_ReachedBy_MultBodyAggAux_recursive_head_f11 AS ReachedBy_MultBodyAggAux_recursive_head_f11
GROUP BY ReachedBy_MultBodyAggAux_recursive_head_f11.source, ReachedBy_MultBodyAggAux_recursive_head_f11.target, ReachedBy_MultBodyAggAux_recursive_head_f11.valid_from, ReachedBy_MultBodyAggAux_recursive_head_f11.valid_to ORDER BY source, target;

-- Executed on DuckDB:
| source   | target   | valid_from | valid_to   |
|----------|----------|------------|------------|
| core     | platform | 2024-01-01 | 2027-01-01 |
| core     | support  | 2025-06-01 | 2027-01-01 |
| platform | support  | 2025-06-01 | 2027-01-01 |
| support  | core     | 2020-01-01 | 2021-01-01 |
(4 rows)

Bitemporal graphs

A temporal edge answers when was this true. It cannot answer when did we believe it, and those are different questions. A salary correction backdated to January, a contract entered a week late, a source system that restates yesterday's export: in all three cases the world did not change, our knowledge of it did.

A bitemporal graph tracks both axes.

Axis Columns Question it answers
Valid time (world time) valid_from, valid_to When was the fact true in the world?
Transaction time (system time) recorded_from, recorded_to When did the database hold it to be true?

Valid time is decided by the business and can be edited freely, including into the past and the future. Transaction time is decided by the clock and is append-only: a version is never modified, only superseded. That is what makes the graph auditable, and what lets an agent reproduce an answer it gave last month instead of quietly overwriting it.

Why an agent wants both

An agent that writes to its own semantic layer is a source of restatements. Transaction time keeps every belief it ever held, so a wrong conclusion can be traced, explained and reversed rather than lost. Valid time keeps the corrected history clean, so today's answer is right even when the data arrived late.

Where the two clocks pay for themselves

The distinction sounds academic until it is someone's job. In each of these cases, a single time axis loses information the business is required to keep:

  • Restated reporting. A quarter is published, then a correction lands. Finance now needs two numbers that are both right: what the corrected books say, and what was published at the time. With valid time alone, publishing the correction destroys the ability to reproduce the original filing.
  • Backdated changes. A raise effective 1 January, approved in March. Payroll owes back pay (a valid-time fact) and the March payroll run was still correct given what was known then (a transaction-time fact). Overwriting the row makes the earlier run look like an error.
  • Late-arriving data. A policy is bound on the 3rd and reaches the warehouse on the 11th. Every report between those dates was right on the evidence available. Without transaction time there is no way to demonstrate that, and the gap looks like a data quality failure.
  • Disputes and approvals. "Was this within limits when it was approved?" is a question about what was known at approval time, not about the corrected record. Credit decisions, underwriting and access reviews all live here.
  • Regulatory reproducibility. Several regimes require that a figure be reproducible as reported. That is a transaction-time requirement, and it cannot be bolted on after the fact: the versions have to have been kept.
  • Agent trust. When an agent gives an answer that later turns out to be wrong, the useful question is whether it reasoned badly or was working from data that has since been corrected. Only transaction time can tell the two apart, and the difference decides whether you fix the rule or the source.

The common thread: a correction is not an edit. Treating it as one destroys evidence that someone eventually asks for, usually under time pressure and usually in front of an auditor.

Modeling

One row per version of a fact, four interval columns, half-open on both axes, with "9999-12-31" as the open end:

@OrderBy(EmployedAt, "person_id", "recorded_from");
EmployedAt(person_id:, company_id:, role:,
           valid_from:, valid_to:, recorded_from:, recorded_to:) distinct :-
  Person(person_id:),
  Company(company_id:),
  EmploymentVersions(person_id:, company_id:, role:,
                     valid_from:, valid_to:, recorded_from:, recorded_to:);

The edge still joins through Person and Company. Versioning is a property of the relationship, not a reason to abandon the graph conventions.

A correction to Ada's role is two rows: the old version keeps its valid time but has its recorded_to closed, and a new version opens with the corrected value.

role valid_from valid_to recorded_from recorded_to
engineer 2024-01-01 9999-12-31 2024-01-05 2026-04-01
lead 2024-01-01 9999-12-31 2026-04-01 9999-12-31

Both rows say the fact was true from January 2024. They disagree about what the fact is, and the transaction interval says which answer was in force when.

The current view

Believed now, true now. This is the view most rules should build on:

@OrderBy(CurrentEmployment, "person_id");
CurrentEmployment(person_id:, company_id:, role:) distinct :-
  EmployedAt(person_id:, company_id:, role:,
             valid_from:, valid_to:, recorded_to: "9999-12-31"),
  Today(date:),
  valid_from <= date, date < valid_to;

Matching recorded_to: "9999-12-31" directly in the argument list is the whole "latest version" filter. No window function, no ranking, no correlated subquery.

As-of queries

Make the vantage point a predicate instead of a constant, and every point on the bitemporal plane becomes reachable by functor application. The default is "now, as we know it now":

AsOf(valid_date:, known_date:) :-
  Today(date:), valid_date == date, known_date == date;

@OrderBy(EmploymentSnapshot, "person_id");
EmploymentSnapshot(person_id:, company_id:, role:) distinct :-
  AsOf(valid_date:, known_date:),
  EmployedAt(person_id:, company_id:, role:,
             valid_from:, valid_to:, recorded_from:, recorded_to:),
  valid_from <= valid_date, valid_date < valid_to,
  recorded_from <= known_date, known_date < recorded_to;

## What the database said in March 2026 about March 2026.
March2026(valid_date: "2026-03-01", known_date: "2026-03-01");
EmploymentAsKnownInMarch := EmploymentSnapshot(AsOf: March2026);

Three vantage points, one rule:

  • valid_date moves, known_date stays at today: the corrected history, as we understand it now.
  • known_date moves, valid_date stays at today: what we would have answered back then.
  • Both move: a faithful replay of a past answer about a past moment, which is what an audit asks for.

Every rule layered on EmploymentSnapshot inherits the vantage point, so a whole analysis can be rewound by swapping one predicate.

Corrections and retractions

The audit trail falls out of the versions themselves. A closed transaction interval with a successor is a correction:

@OrderBy(Correction, "person_id", "corrected_at");
Correction(person_id:, old_role:, new_role:, corrected_at:) distinct :-
  EmployedAt(person_id:, role: old_role, recorded_to: corrected_at),
  corrected_at != "9999-12-31",
  EmployedAt(person_id:, role: new_role, recorded_from: corrected_at);

A closed transaction interval with no successor is a retraction, an edge we no longer believe ever existed:

@OrderBy(Retracted, "person_id");
Retracted(person_id:, role:, retracted_at:) distinct :-
  EmployedAt(person_id:, role:, recorded_to: retracted_at),
  retracted_at != "9999-12-31",
  ~EmployedAt(person_id:, recorded_from: retracted_at);

Note what a retraction is not: it is not valid_to moving to today. Ending an employment is a fact about the world and belongs to valid time. Deciding the employment never happened is a fact about our knowledge and belongs to transaction time. Keeping the two apart is the entire benefit of the model.

Joining bitemporal edges

A composition of two bitemporal edges is valid only where both are valid and both were believed. Intersect on both axes and keep the result only if both intervals are non-empty:

@OrderBy(WorkedWithClient, "person_id", "valid_from");
WorkedWithClient(person_id:, client_id:, valid_from:, valid_to:,
                 recorded_from:, recorded_to:) distinct :-
  MemberOf(person_id:, team_id:, valid_from: m_vf, valid_to: m_vt,
           recorded_from: m_rf, recorded_to: m_rt),
  EngagedWith(team_id:, client_id:, valid_from: e_vf, valid_to: e_vt,
              recorded_from: e_rf, recorded_to: e_rt),
  valid_from    == (if m_vf > e_vf then m_vf else e_vf),
  valid_to      == (if m_vt < e_vt then m_vt else e_vt),
  recorded_from == (if m_rf > e_rf then m_rf else e_rf),
  recorded_to   == (if m_rt < e_rt then m_rt else e_rt),
  valid_from < valid_to,
  recorded_from < recorded_to;

The derived edge is itself bitemporal, so it composes further and can be queried through the same AsOf vantage point.

Filling the axes from real sources

Few tables arrive with four interval columns. The usual shapes:

  • Slowly changing dimension, type 2. effective_from / effective_to are valid time. If the warehouse also keeps a load timestamp, that is transaction time; close its intervals with the event-log technique over recorded_from.
  • Change data capture. One row per change with a commit timestamp and no end: that timestamp is recorded_from, and the next change for the same key closes it. Valid time comes from the business columns, or equals transaction time when the source has no notion of it.
  • Append-only event log. Events carry only transaction time. Derive valid time from the event's own fields (effective_date, signed_on) when they exist, and be explicit when they do not: a fact that is only known, never dated, has valid time equal to transaction time.

When only one axis exists in the source, model that one honestly rather than inventing the other. A uni-temporal edge composes with bitemporal ones as long as the missing axis is treated as always valid.

What bitemporality costs

Bitemporality is the most expensive modeling choice in this document, and it should be made deliberately:

  • Writes become append-only. Nothing is ever updated in place: a change is a closed version plus a new one. Any process that writes to the relation has to be taught this, and a single UPDATE that slips through silently destroys the audit trail the model exists to provide.
  • Rows multiply. A relation with frequent corrections grows with every restatement. Usually cheap relative to fact tables, occasionally not.
  • Every query must state a vantage point. Forgetting the recorded_to filter returns every version of every fact and inflates counts, quietly. The mitigation is structural: build a current view and an AsOf predicate early, and have ordinary rules go through them rather than touching the versioned edge directly.
  • The sources often do not cooperate. Many systems overwrite in place and simply do not record when they learned something. You cannot reconstruct transaction time retroactively; you can only start capturing it from today. That argues for deciding early on the few relations that will need it.
  • It is harder to explain. Two people looking at the same relation on different vantage points get different, both-correct answers. That confuses stakeholders until the distinction is explained once, properly.

The proportionate answer is rarely "make the graph bitemporal". It is to identify the two or three relations where corrections carry consequences, version those, and leave the rest as valid-time or snapshot concepts.

Complete example

The bitemporal edge with a real correction, the current view, the AsOf vantage point replaying the pre-correction answer, and the audit trail:

# run: EmployedAt, CurrentEmployment, EmploymentSnapshot, EmploymentAsKnownInMarch, Correction
@Engine("duckdb");

# Tables

## One row per version of a fact. Two half-open intervals:
##   [valid_from, valid_to)       when the fact holds in the world
##   [recorded_from, recorded_to) when the database believed it
## "9999-12-31" is the open end: still true / still believed.
EmploymentVersions(person_id: 1, company_id: "acme", role: "engineer",
  valid_from: "2024-01-01", valid_to: "9999-12-31",
  recorded_from: "2024-01-05", recorded_to: "2026-04-01");
EmploymentVersions(person_id: 1, company_id: "acme", role: "lead",
  valid_from: "2024-01-01", valid_to: "9999-12-31",
  recorded_from: "2026-04-01", recorded_to: "9999-12-31");
EmploymentVersions(person_id: 2, company_id: "acme", role: "engineer",
  valid_from: "2023-03-01", valid_to: "2025-01-01",
  recorded_from: "2023-03-02", recorded_to: "9999-12-31");

People(person_id: 1, name: "Ada", profile_url: "https://example.com/ada");
People(person_id: 2, name: "Grace", profile_url: "https://example.com/grace");
Companies(company_id: "acme", website: "https://acme.example.com");

# Concepts

@OrderBy(Person, "person_id");
Person(person_id:, name:, profile_url:) distinct :- People(person_id:, name:, profile_url:);

@OrderBy(Company, "company_id");
Company(company_id:, website:) distinct :- Companies(company_id:, website:);

## The bitemporal edge: every version, joined through the nodes.
@OrderBy(EmployedAt, "person_id", "recorded_from");
EmployedAt(person_id:, company_id:, role:,
           valid_from:, valid_to:, recorded_from:, recorded_to:) distinct :-
  Person(person_id:),
  Company(company_id:),
  EmploymentVersions(person_id:, company_id:, role:,
                     valid_from:, valid_to:, recorded_from:, recorded_to:);

# Rules

## The current view: valid now, and believed now.
@OrderBy(CurrentEmployment, "person_id");
CurrentEmployment(person_id:, name:, company_id:, role:) distinct :-
  EmployedAt(person_id:, company_id:, role:,
             valid_from:, valid_to:, recorded_to: "9999-12-31"),
  Person(person_id:, name:),
  Today(date:),
  valid_from <= date, date < valid_to;

## The vantage point, as a swappable predicate. The default is "now, as we
## know it now"; a functor application moves it anywhere on either axis.
AsOf(valid_date:, known_date:) :-
  Today(date:), valid_date == date, known_date == date;

@OrderBy(EmploymentSnapshot, "person_id");
EmploymentSnapshot(person_id:, company_id:, role:) distinct :-
  AsOf(valid_date:, known_date:),
  EmployedAt(person_id:, company_id:, role:,
             valid_from:, valid_to:, recorded_from:, recorded_to:),
  valid_from <= valid_date, valid_date < valid_to,
  recorded_from <= known_date, known_date < recorded_to;

## What the database said on 2026-03-01 about 2026-03-01: the pre-correction
## answer, reproduced exactly.
March2026(valid_date: "2026-03-01", known_date: "2026-03-01");
EmploymentAsKnownInMarch := EmploymentSnapshot(AsOf: March2026);

## The audit trail: a version that stopped being believed, and the version
## that replaced it.
@OrderBy(Correction, "person_id", "corrected_at");
Correction(person_id:, old_role:, new_role:, corrected_at:) distinct :-
  EmployedAt(person_id:, role: old_role, recorded_to: corrected_at),
  corrected_at != "9999-12-31",
  EmployedAt(person_id:, role: new_role, recorded_from: corrected_at);
Generated SQL and execution results 
$ synalog.check('bitemporal.l')
No errors found.

$ synalog.compile('bitemporal.l', 'EmployedAt')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_1_People AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'Ada' AS name,
      'https://example.com/ada' AS profile_url
   UNION ALL

    SELECT
      2 AS person_id,
      'Grace' AS name,
      'https://example.com/grace' AS profile_url

) AS UNUSED_TABLE_NAME  ),
t_0_Person AS (SELECT
  People.person_id AS person_id,
  People.name AS name,
  People.profile_url AS profile_url
FROM
  t_1_People AS People
GROUP BY People.person_id, People.name, People.profile_url ORDER BY person_id),
t_2_Company AS (SELECT
  'acme' AS company_id,
  'https://acme.example.com' AS website
FROM
  (SELECT 'singleton' as s) as unused_singleton
GROUP BY ('acme' || ''), ('https://acme.example.com' || '') ORDER BY company_id),
t_3_EmploymentVersions AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'acme' AS company_id,
      'engineer' AS role,
      '2024-01-01' AS valid_from,
      '9999-12-31' AS valid_to,
      '2024-01-05' AS recorded_from,
      '2026-04-01' AS recorded_to
   UNION ALL

    SELECT
      1 AS person_id,
      'acme' AS company_id,
      'lead' AS role,
      '2024-01-01' AS valid_from,
      '9999-12-31' AS valid_to,
      '2026-04-01' AS recorded_from,
      '9999-12-31' AS recorded_to
   UNION ALL

    SELECT
      2 AS person_id,
      'acme' AS company_id,
      'engineer' AS role,
      '2023-03-01' AS valid_from,
      '2025-01-01' AS valid_to,
      '2023-03-02' AS recorded_from,
      '9999-12-31' AS recorded_to

) AS UNUSED_TABLE_NAME  )
SELECT
  Person.person_id AS person_id,
  Company.company_id AS company_id,
  EmploymentVersions.role AS role,
  EmploymentVersions.valid_from AS valid_from,
  EmploymentVersions.valid_to AS valid_to,
  EmploymentVersions.recorded_from AS recorded_from,
  EmploymentVersions.recorded_to AS recorded_to
FROM
  t_0_Person AS Person, t_2_Company AS Company, t_3_EmploymentVersions AS EmploymentVersions
WHERE
  (EmploymentVersions.person_id = Person.person_id) AND
  (EmploymentVersions.company_id = Company.company_id)
GROUP BY Person.person_id, Company.company_id, EmploymentVersions.role, EmploymentVersions.valid_from, EmploymentVersions.valid_to, EmploymentVersions.recorded_from, EmploymentVersions.recorded_to ORDER BY person_id, recorded_from;

-- Executed on DuckDB:
| person_id | company_id | role     | valid_from | valid_to   | recorded_from | recorded_to |
|-----------|------------|----------|------------|------------|---------------|-------------|
| 1         | acme       | engineer | 2024-01-01 | 9999-12-31 | 2024-01-05    | 2026-04-01  |
| 1         | acme       | lead     | 2024-01-01 | 9999-12-31 | 2026-04-01    | 9999-12-31  |
| 2         | acme       | engineer | 2023-03-01 | 2025-01-01 | 2023-03-02    | 9999-12-31  |
(3 rows)

$ synalog.compile('bitemporal.l', 'CurrentEmployment')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_3_People AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'Ada' AS name,
      'https://example.com/ada' AS profile_url
   UNION ALL

    SELECT
      2 AS person_id,
      'Grace' AS name,
      'https://example.com/grace' AS profile_url

) AS UNUSED_TABLE_NAME  ),
t_2_Person AS (SELECT
  People.person_id AS person_id,
  People.name AS name,
  People.profile_url AS profile_url
FROM
  t_3_People AS People
GROUP BY People.person_id, People.name, People.profile_url ORDER BY person_id),
t_4_Company AS (SELECT
  'acme' AS company_id,
  'https://acme.example.com' AS website
FROM
  (SELECT 'singleton' as s) as unused_singleton
GROUP BY ('acme' || ''), ('https://acme.example.com' || '') ORDER BY company_id),
t_5_EmploymentVersions AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'acme' AS company_id,
      'engineer' AS role,
      '2024-01-01' AS valid_from,
      '9999-12-31' AS valid_to,
      '2024-01-05' AS recorded_from,
      '2026-04-01' AS recorded_to
   UNION ALL

    SELECT
      1 AS person_id,
      'acme' AS company_id,
      'lead' AS role,
      '2024-01-01' AS valid_from,
      '9999-12-31' AS valid_to,
      '2026-04-01' AS recorded_from,
      '9999-12-31' AS recorded_to
   UNION ALL

    SELECT
      2 AS person_id,
      'acme' AS company_id,
      'engineer' AS role,
      '2023-03-01' AS valid_from,
      '2025-01-01' AS valid_to,
      '2023-03-02' AS recorded_from,
      '9999-12-31' AS recorded_to

) AS UNUSED_TABLE_NAME  ),
t_0_EmployedAt AS (SELECT
  t_1_Person.person_id AS person_id,
  Company.company_id AS company_id,
  EmploymentVersions.role AS role,
  EmploymentVersions.valid_from AS valid_from,
  EmploymentVersions.valid_to AS valid_to,
  EmploymentVersions.recorded_from AS recorded_from,
  EmploymentVersions.recorded_to AS recorded_to
FROM
  t_2_Person AS t_1_Person, t_4_Company AS Company, t_5_EmploymentVersions AS EmploymentVersions
WHERE
  (EmploymentVersions.person_id = t_1_Person.person_id) AND
  (EmploymentVersions.company_id = Company.company_id)
GROUP BY t_1_Person.person_id, Company.company_id, EmploymentVersions.role, EmploymentVersions.valid_from, EmploymentVersions.valid_to, EmploymentVersions.recorded_from, EmploymentVersions.recorded_to ORDER BY person_id, recorded_from)
SELECT
  EmployedAt.person_id AS person_id,
  Person.name AS name,
  EmployedAt.company_id AS company_id,
  EmployedAt.role AS role
FROM
  t_0_EmployedAt AS EmployedAt, t_2_Person AS Person, (SELECT strftime(current_date, '%Y-%m-%d') AS date) AS Today
WHERE
  (EmployedAt.valid_from <= Today.date) AND
  (Today.date < EmployedAt.valid_to) AND
  (EmployedAt.recorded_to = '9999-12-31') AND
  (Person.person_id = EmployedAt.person_id)
GROUP BY EmployedAt.person_id, Person.name, EmployedAt.company_id, EmployedAt.role ORDER BY person_id;

-- Executed on DuckDB:
| person_id | name | company_id | role |
|-----------|------|------------|------|
| 1         | Ada  | acme       | lead |
(1 row)

$ synalog.compile('bitemporal.l', 'EmploymentSnapshot')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_2_People AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'Ada' AS name,
      'https://example.com/ada' AS profile_url
   UNION ALL

    SELECT
      2 AS person_id,
      'Grace' AS name,
      'https://example.com/grace' AS profile_url

) AS UNUSED_TABLE_NAME  ),
t_1_Person AS (SELECT
  People.person_id AS person_id,
  People.name AS name,
  People.profile_url AS profile_url
FROM
  t_2_People AS People
GROUP BY People.person_id, People.name, People.profile_url ORDER BY person_id),
t_3_Company AS (SELECT
  'acme' AS company_id,
  'https://acme.example.com' AS website
FROM
  (SELECT 'singleton' as s) as unused_singleton
GROUP BY ('acme' || ''), ('https://acme.example.com' || '') ORDER BY company_id),
t_4_EmploymentVersions AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'acme' AS company_id,
      'engineer' AS role,
      '2024-01-01' AS valid_from,
      '9999-12-31' AS valid_to,
      '2024-01-05' AS recorded_from,
      '2026-04-01' AS recorded_to
   UNION ALL

    SELECT
      1 AS person_id,
      'acme' AS company_id,
      'lead' AS role,
      '2024-01-01' AS valid_from,
      '9999-12-31' AS valid_to,
      '2026-04-01' AS recorded_from,
      '9999-12-31' AS recorded_to
   UNION ALL

    SELECT
      2 AS person_id,
      'acme' AS company_id,
      'engineer' AS role,
      '2023-03-01' AS valid_from,
      '2025-01-01' AS valid_to,
      '2023-03-02' AS recorded_from,
      '9999-12-31' AS recorded_to

) AS UNUSED_TABLE_NAME  ),
t_0_EmployedAt AS (SELECT
  Person.person_id AS person_id,
  Company.company_id AS company_id,
  EmploymentVersions.role AS role,
  EmploymentVersions.valid_from AS valid_from,
  EmploymentVersions.valid_to AS valid_to,
  EmploymentVersions.recorded_from AS recorded_from,
  EmploymentVersions.recorded_to AS recorded_to
FROM
  t_1_Person AS Person, t_3_Company AS Company, t_4_EmploymentVersions AS EmploymentVersions
WHERE
  (EmploymentVersions.person_id = Person.person_id) AND
  (EmploymentVersions.company_id = Company.company_id)
GROUP BY Person.person_id, Company.company_id, EmploymentVersions.role, EmploymentVersions.valid_from, EmploymentVersions.valid_to, EmploymentVersions.recorded_from, EmploymentVersions.recorded_to ORDER BY person_id, recorded_from)
SELECT
  EmployedAt.person_id AS person_id,
  EmployedAt.company_id AS company_id,
  EmployedAt.role AS role
FROM
  (SELECT strftime(current_date, '%Y-%m-%d') AS date) AS Today, t_0_EmployedAt AS EmployedAt
WHERE
  (EmployedAt.valid_from <= Today.date) AND
  (Today.date < EmployedAt.valid_to) AND
  (EmployedAt.recorded_from <= Today.date) AND
  (Today.date < EmployedAt.recorded_to)
GROUP BY EmployedAt.person_id, EmployedAt.company_id, EmployedAt.role ORDER BY person_id;

-- Executed on DuckDB:
| person_id | company_id | role |
|-----------|------------|------|
| 1         | acme       | lead |
(1 row)

$ synalog.compile('bitemporal.l', 'EmploymentAsKnownInMarch')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_2_People AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'Ada' AS name,
      'https://example.com/ada' AS profile_url
   UNION ALL

    SELECT
      2 AS person_id,
      'Grace' AS name,
      'https://example.com/grace' AS profile_url

) AS UNUSED_TABLE_NAME  ),
t_1_Person AS (SELECT
  People.person_id AS person_id,
  People.name AS name,
  People.profile_url AS profile_url
FROM
  t_2_People AS People
GROUP BY People.person_id, People.name, People.profile_url ORDER BY person_id),
t_3_Company AS (SELECT
  'acme' AS company_id,
  'https://acme.example.com' AS website
FROM
  (SELECT 'singleton' as s) as unused_singleton
GROUP BY ('acme' || ''), ('https://acme.example.com' || '') ORDER BY company_id),
t_4_EmploymentVersions AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'acme' AS company_id,
      'engineer' AS role,
      '2024-01-01' AS valid_from,
      '9999-12-31' AS valid_to,
      '2024-01-05' AS recorded_from,
      '2026-04-01' AS recorded_to
   UNION ALL

    SELECT
      1 AS person_id,
      'acme' AS company_id,
      'lead' AS role,
      '2024-01-01' AS valid_from,
      '9999-12-31' AS valid_to,
      '2026-04-01' AS recorded_from,
      '9999-12-31' AS recorded_to
   UNION ALL

    SELECT
      2 AS person_id,
      'acme' AS company_id,
      'engineer' AS role,
      '2023-03-01' AS valid_from,
      '2025-01-01' AS valid_to,
      '2023-03-02' AS recorded_from,
      '9999-12-31' AS recorded_to

) AS UNUSED_TABLE_NAME  ),
t_0_EmployedAt AS (SELECT
  Person.person_id AS person_id,
  Company.company_id AS company_id,
  EmploymentVersions.role AS role,
  EmploymentVersions.valid_from AS valid_from,
  EmploymentVersions.valid_to AS valid_to,
  EmploymentVersions.recorded_from AS recorded_from,
  EmploymentVersions.recorded_to AS recorded_to
FROM
  t_1_Person AS Person, t_3_Company AS Company, t_4_EmploymentVersions AS EmploymentVersions
WHERE
  (EmploymentVersions.person_id = Person.person_id) AND
  (EmploymentVersions.company_id = Company.company_id)
GROUP BY Person.person_id, Company.company_id, EmploymentVersions.role, EmploymentVersions.valid_from, EmploymentVersions.valid_to, EmploymentVersions.recorded_from, EmploymentVersions.recorded_to ORDER BY person_id, recorded_from)
SELECT
  EmployedAt.person_id AS person_id,
  EmployedAt.company_id AS company_id,
  EmployedAt.role AS role
FROM
  t_0_EmployedAt AS EmployedAt
WHERE
  (EmployedAt.valid_from <= '2026-03-01') AND
  ('2026-03-01' < EmployedAt.valid_to) AND
  (EmployedAt.recorded_from <= '2026-03-01') AND
  ('2026-03-01' < EmployedAt.recorded_to)
GROUP BY EmployedAt.person_id, EmployedAt.company_id, EmployedAt.role ORDER BY person_id;

-- Executed on DuckDB:
| person_id | company_id | role     |
|-----------|------------|----------|
| 1         | acme       | engineer |
(1 row)

$ synalog.compile('bitemporal.l', 'Correction')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_3_People AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'Ada' AS name,
      'https://example.com/ada' AS profile_url
   UNION ALL

    SELECT
      2 AS person_id,
      'Grace' AS name,
      'https://example.com/grace' AS profile_url

) AS UNUSED_TABLE_NAME  ),
t_2_Person AS (SELECT
  People.person_id AS person_id,
  People.name AS name,
  People.profile_url AS profile_url
FROM
  t_3_People AS People
GROUP BY People.person_id, People.name, People.profile_url ORDER BY person_id),
t_4_Company AS (SELECT
  'acme' AS company_id,
  'https://acme.example.com' AS website
FROM
  (SELECT 'singleton' as s) as unused_singleton
GROUP BY ('acme' || ''), ('https://acme.example.com' || '') ORDER BY company_id),
t_5_EmploymentVersions AS (SELECT * FROM (

    SELECT
      1 AS person_id,
      'acme' AS company_id,
      'engineer' AS role,
      '2024-01-01' AS valid_from,
      '9999-12-31' AS valid_to,
      '2024-01-05' AS recorded_from,
      '2026-04-01' AS recorded_to
   UNION ALL

    SELECT
      1 AS person_id,
      'acme' AS company_id,
      'lead' AS role,
      '2024-01-01' AS valid_from,
      '9999-12-31' AS valid_to,
      '2026-04-01' AS recorded_from,
      '9999-12-31' AS recorded_to
   UNION ALL

    SELECT
      2 AS person_id,
      'acme' AS company_id,
      'engineer' AS role,
      '2023-03-01' AS valid_from,
      '2025-01-01' AS valid_to,
      '2023-03-02' AS recorded_from,
      '9999-12-31' AS recorded_to

) AS UNUSED_TABLE_NAME  ),
t_1_EmployedAt AS (SELECT
  Person.person_id AS person_id,
  Company.company_id AS company_id,
  EmploymentVersions.role AS role,
  EmploymentVersions.valid_from AS valid_from,
  EmploymentVersions.valid_to AS valid_to,
  EmploymentVersions.recorded_from AS recorded_from,
  EmploymentVersions.recorded_to AS recorded_to
FROM
  t_2_Person AS Person, t_4_Company AS Company, t_5_EmploymentVersions AS EmploymentVersions
WHERE
  (EmploymentVersions.person_id = Person.person_id) AND
  (EmploymentVersions.company_id = Company.company_id)
GROUP BY Person.person_id, Company.company_id, EmploymentVersions.role, EmploymentVersions.valid_from, EmploymentVersions.valid_to, EmploymentVersions.recorded_from, EmploymentVersions.recorded_to ORDER BY person_id, recorded_from)
SELECT
  EmployedAt.person_id AS person_id,
  EmployedAt.role AS old_role,
  t_0_EmployedAt.role AS new_role,
  EmployedAt.recorded_to AS corrected_at
FROM
  t_1_EmployedAt AS EmployedAt, t_1_EmployedAt AS t_0_EmployedAt
WHERE
  (EmployedAt.recorded_to != '9999-12-31') AND
  (t_0_EmployedAt.person_id = EmployedAt.person_id) AND
  (t_0_EmployedAt.recorded_from = EmployedAt.recorded_to)
GROUP BY EmployedAt.person_id, EmployedAt.role, t_0_EmployedAt.role, EmployedAt.recorded_to ORDER BY person_id, corrected_at;

-- Executed on DuckDB:
| person_id | old_role | new_role | corrected_at |
|-----------|----------|----------|--------------|
| 1         | engineer | lead     | 2026-04-01   |
(1 row)

Key principles

  • Entity concepts are the vertices, relationship concepts are the edges, rules are traversals.
  • Every edge joins through node concepts, so referential integrity and every node-level filter come for free.
  • Reuse aggressively. Once nodes and edges exist, all rules build on them instead of going back to raw tables.
  • Keep the two time axes separate: valid time is what the world did, transaction time is what we knew.
  • The graph is the agent's memory. Each new concept or rule extends what every later query can express.