How to read and export Grist’s action history for monitoring or auditing?

Ask for Help
, ,
1

1 – Context & Objective

  • What I have
    • A .grist file (internally SQLite 3).
    • Inside, the table _gristsys_ActionHistory contains one row per user action.
    • Each row’s body column is a BLOB that stores the full action payload (time stamp, user, list of userActions, etc.).
  • What I need
    • Decode those body blobs into readable JSON (or any Python/JS object) so I can build a monitoring / audit pipeline.

2 – What I already tried

Approach Tools / Code Result / Error
Direct SQL
SELECT id, actionNum, body FROM _gristsys_ActionHistory … DB Browser for SQLite, online viewers Shows the blob as a hex/byte array. No decoding offered.
Grist API
/docs/{docId}/sql Returns JSON objects like: json{ “type”:“Buffer”,“data”:[123,117,10,0,…]}`

4 – Goal restated

I’m looking for the simplest way to decode the blob and get something like:

{
  "actionNum": 7760,
  "info": [0, {
    "time": 1716040312456,
    "user": "alice@example.com"
  }],
  "userActions": [["BulkUpdateRecord", "_grist_ACLRules", [5, 6], {...}]],
  "undo": [...],
  "calc": ...
}

so that I can export to CSV and feed my monitoring dashboard.

5 – Questions for the community

  1. What exact encoding/serialization does Grist use for _gristsys_ActionHistory.body?

  2. If you’ve already parsed these blobs : what was your decoding pipeline (language, libs, flags)?

Any pointer would be hugely appreciated. :pray:

2

There’s one example of decoding here that might give you a lead:

3

My Solution In Python :

import sqlite3, marshal, json

# Path grist file
conn = sqlite3.connect("C:/Users/IOO7155/Downloads/Indicateurs_Cycle_Eau (2).grist")
cur = conn.cursor()

# list to stock all actions
historique = []

# extract and decode each record
for (id, action_num, blob) in cur.execute("SELECT id, actionNum, body FROM _gristsys_ActionHistory"):
    try:
        action_obj = marshal.loads(blob)  # binary decoding
        action_obj['id'] = id             # add id and action number 
        action_obj['actionNum'] = action_num
        historique.append(action_obj)
    except Exception as e:
        print(f"Error in decoding {action_num} : {e}")

# export 
with open("historique.json", "w", encoding="utf-8") as f:
    json.dump(historique, f, ensure_ascii=False, indent=2)

print("✅")
1 Like