Recording the date that connections were made

I’d like to have a list of new connections made between nodes in org-roam. (Similar to the example here: Patterns — Gordon Brander)

I can query org-roam.db for:

select, n1.title,, n2.title
from links
inner join nodes as n1 on links.source =
inner join nodes as n2 on links.dest
where type = '"id"'
order by links.rowid desc
limit 42

but the rowid is not helpful for this - you can’t rely on it being in order of links added.

Has anyone tried to achieve this and had any success? I wonder if I would need to patch the code to have either a unique id in the links table, or a created_at field or similar.

I suspect that you’d need to patch the code to do something extra.

The link table in org-roam-db has properties field but it’s a text field, which you may struggle to do SQL-level sorting and other operations for dates.

I’d consider creating my own custom table that stores the create_at field with a date or integer data type, and adapt the code in org-roam-db to populate it when I newly created a link (and take care of the delete…, but ignore updates).

Or… add a date field to the org-roam-db’s links table… Not sure which is easier.

1 Like

Thanks @nobiot.

I consider line 589 here in org-roam-db-insert-link is a good place to start looking into this: org-roam/org-roam-db.el at main · org-roam/org-roam · GitHub

But then I realise that org-roam.db is only a cache layer over the data in the org files (right?). And could be rebuilt at any time, losing any externally added datetimes. So if I wished to store a created_at field for links then really I must also have it stored somewhere in the org files themselves. I think.

Which doesn’t seem particularly feasible. I would need to be able to annotate links - a bit like in Add Link-tags feature.

Oh, yes, I forgot about this. You are right. I have almost never had to rebuild the db file and don’t sync my notes with other machines. If you also sync notes between machines, the created_at would be different too (!).

This means… You’d probably have to extend the Org’s ID link syntax so that you can represent the timestamp directly in the link itself – as extensively discussed in the “link-tag” thread…

1 Like