Creating a link from Holder1 -> Transaction -> Holder2 with properties

[georgelza]

Hi guys

I have a outbound account1:

{
  "fullName": "Dom Silva Blues",
  "accountEntityId": "JJIVESZA-15412754683",
  "accountId": "15412754683",
  "tenantId": "JJIVESZA",
  "accountAgentId": "JJIVESZA"
}

I have a inbound account2:

{
  "fullName": "Martie Smartie",
  "accountEntityId": "CABLZAJJ-1148157091",
  "accountId": "1148157091",
  "tenantId": "Z",
  "accountAgentId": "Z"
}

I now have a outbound transaction -> topic ob_txn_edge

{
  "transactionId": "1bbaf5de-737a-49d3-8ad1-f404daa0fb78",
  "tenantId": "JJIVESZA",
  "accountEntityId": "JJIVESZA-15412754683",
  "counterpartyEntityId": "CABLZAJJ-1148157091",
  "eventTime": "2025-06-20T14:59:59+02:00",
  "baseValue": 2000,
  "currency": "ZAR"
}

• and Inbound transaction -> topic ib_txn_edge

{
  "transactionId": "1bbaf5de-737a-49d3-8ad1-f404daa0fb89",
  "tenantId": "CABLZAJJ",
  "accountEntityId": "CABLZAJJ-1148157091",
  "counterpartyEntityId": "JJIVESZA-15412754683",
  "eventTime": "2025-06-20T14:59:59+02:00",
  "baseValue": 2000,
  "currency": "ZAR"
}

My thinking to link the 3 (ah1, txn, ah2) together is the below, I am however not getting a relation created.
I'd like to create the 2 edge/links, as per below.

this is:

1. not creating either of the links.
2. and if it did, not sure how these values would be pulled into the link as properties ?
   Please help

Sink definition

{
  "name": "neo4j-ah1-txn-ah2-edges-sink",
  "config": {
    "connector.class": "org.neo4j.connectors.kafka.sink.Neo4jConnector",
    "topics": "ob_txn_edge,ib_txn_edge",
    "neo4j.uri": "bolt://neo4j:7687",
    "neo4j.authentication.basic.username": "neo4j",
    "neo4j.authentication.basic.password": "dbpassword",
    "neo4j.cypher.topic.ob_txn_edge": "MATCH (a1:AccountHolder {accountEntityId: event.accountEntityId}) MATCH (a2:AccountHolder {counterpartyEntityId: event.counterpartyEntityId}) MERGE (ah1)-[r:PAID_FUNDS_TO]->(ah2)",
    "neo4j.cypher.topic.ib_txn_edge": "MATCH (a1:AccountHolder {accountEntityId: event.accountEntityId}) MATCH (a2:AccountHolder {counterpartyEntityId: event.counterpartyEntityId}) MERGE (ah1)-[r:RECIEVED_FUNDS_FROM]->(ah2)",
    "key.converter": "org.apache.kafka.connect.storage.StringConverter",
    "value.converter": "org.apache.kafka.connect.json.JsonConverter",
    "value.converter.schemas.enable": "false",
    "tasks.max": "2",
    "neo4j.batch.size": "1000",
    "neo4j.batch.timeout.msecs": "5000",
    "neo4j.retry.backoff.msecs": "3000",
    "neo4j.retry.max.attemps": "5"
  }
}

[ameyasoft]

1 and 2:

Couple of things:
variables in the MERGE statement should be a1, a2.
Requires a WITH a1, a2 statement between MATCH and MERGE statements. Try this I added a property to the relationship

MATCH (a1:AccountHolder {accountEntityId: event.accountEntityId}) MATCH (a2:AccountHolder {counterpartyEntityId: event.counterpartyEntityId}) 
    WITH a1, a2
    MERGE (a1)-[r:PAID_FUNDS_TO {counterpartyEntityId: event.counterpartyEntityId}]->(a2)

[georgelza]

guess as they say allot of water in the bridge since I posted that... going to combine what you shared with what I have... there is anther thread where I try and unpack this a bit more.

Still stuck on the challenge of how to model the a.accountHolder -> Txn -> a2.accountHolder relationship.

Outbound on ob_txn_edge topic
a1 -> Payer
a2 -> Payee

Inbound on ib_txn_edge topic
a1 -> Payee
a2 -> Payer

Each Fin transaction gets broken into 2 Events... an Outbound event as far as a1 is concerned and an inbound even as far as a2 is concerned.

For Outbound it's -> PaidFundsTo
a1. accountEntityId (this is the payers accountId) => event.accountEntityId
a2. accountEntityId (this is the payee's accountId) => event.counterpartyEntityId
properties = r:PaidFundsTo

For Inbound it's -> ReceivedFundsFrom.
a2.accountEntityId (this is the payee's accountId) => event.accountEntityId
a2.accountEntityId (this is the payers's accountId) => event.counterpartyEntityId
properties = r:ReceivedFundsFrom

Extra properties like txn.amount, txn.eventTime, txn.currency, tx.riscScore etc. to be added as a +=

some notes. each event.* has a eventId, that is unique, the events/Txn's also have a transactionId that is share over the 2 events. that allow me to relate a outbound with a inbound.

One of the worries is, if I model it as per above at the moment it might become very messy between 2 accounts that do allot of transactions between them, also it might brake the below make a transaction and eventType idea... so was playing with idea to make a eventType a node, that way each even comes a node with relationship from it to a payer and it to a payee. making nodes make it possible to show each transaction ad a node, instead of 2 edges.

I added a larger description of what I'm busy with as MVP1 in the Projects section. Over night came up with idea to make a transaction and eventType, allowing me to handle other types of events on the account also.