Ah yes, sorry to have missed replying to this! Busy day 🙂 Your suggestion certainly isn't bad. You could also model dedicated tx metadata entity(s) that work more generically, getting updated alongside each transaction

thanks, I didn't consider a separate metadata entity... that's one option

@U11SJ6Q0K that's what we're doing, we have a "transaction history" type object that stores the user, source microservice, transaction id, rationale, and a computed field-level diff

interesting, so do you store a tx history document along with each tx you do? does the tx history document link to the documents that were touched in the tx

Hoping for something like this in my when arguments:

[['doc :crux.tx/tx-time tx-time]
 `(<= ~start-timestamp tx-time)
 `(<= tx-time ~end-timestamp)]

Alternatively, can I somehow join to the transaction log by transaction id and get the time of the transaction in results for post-query filtering?

Another useful alternative would be, given a crux node and a transaction id, get the transaction object with the timestamp

Hey @U0D5Y2403 there's nothing built-in for this, but you can pull in the tx data like this (using the magic $ var which denotes the db context)

(defn get-valid-time [db e]
  (with-open [h (crux/open-entity-history db e :desc)]
    (:crux.db/valid-time (first (iterator-seq h)))))

(crux/q (crux/db (crux-node))
        '{:find [e vt]
          :limit 1
          :where [[e :crux.db/id]
                  [(my-ns/get-valid-time $ e) vt]]})
;;=> [[:bar #inst "2021-08-05T15:18:45.952-00:00"]]

Woof, I don't have the entity on-hand, I'm trying to query ALL transactions as of a timestamp and get the latest id given a date

but maybe the magic $ will do it

I think we'll just hit the postgres table directly with

SELECT max(event_offset) as max_tx_id, min(event_offset) as min_tx_id
	FROM public.tx_events
	where tx_time between '2021-09-03T21:27:16-00:00' and now()

ahh my bad, I was too focused on the initial message 😅 > Another useful alternative would be, given a crux node and a transaction id, get the transaction object with the timestamp open-tx-log essentially does this, if I understand correctly - or have you dismissed that already?

the only caveat to using open-tx-log is that it doesn't use the node indexes, so it will hit the postgres table each time with something similar to the above anyway

interesting. If performance is an issue with open-tx-log I may go that route, but this is like 7ms currently

One option is to add a dummy predicate clause like [(int? 1)] and then manually increment the value whenever you want to bypass the cache

Thanks!