feat: cleanup old entries in the sqlite

[ppedziwiatr]

run periodically (once a day?):

DELETE
FROM states
WHERE sort_key IN (SELECT sort_key
                   FROM (SELECT *,
                                ROW_NUMBER() OVER (PARTITION BY contract_tx_id ORDER BY sort_key DESC) AS n
                         FROM states) AS x
                   WHERE n > 3);
                   
VACUUM;                   

on the node.sqlite db.

Watch out for the WAL file size after cleanup - rm might be needed.

[ppedziwiatr]

or maybe remove the entries on the fly, while new are being inserted (though this will slow down the insertion process...)

[ppedziwiatr]

..or maybe simply keep only one state per contract....which may result in a slight risk of that newer state will be overwritten by the older one (if the evaluation of the the 'older' interaction will happen to end after evaluation for the 'newer' interaction - in a scenario, where contract has a lot of interactions registered in a short period of time)

[janekolszak]

UNIQUE on contractID, trigger function that neglects INSERT if sortKey is smaller that the inserted