How do I keep every version of a table so I can audit changes?

Recipe verified on 2026-10-29 Ran on a private test instance

Short answer

Write each change as a new version instead of overwriting: every load, sync or replace_rows in mcpdata is kept, with its time. AS OF ALLVERSIONS reads all of them at once, so "when did this value change?" is a query.

Steps

Connect your assistant to your instance (this writes, so it needs your own instance with a ReadWrite key from the accounts portal; the public demo is read-only). In Claude Code:

claude mcp add --transport http mcpdata https://[you].mcpdata.org/mcp --header "Authorization: Bearer $CDB_API_KEY"

Replace [you] with your instance's name. Other apps: Connect your AI.

Load the daily files as a history.

cdb-localdata add ./samples/positions --to https://[you].mcpdata.org --namespace audit --name audit
cdb-localdata sync

Read every version of one position.

import cdb
s = cdb.Session("https://[you].mcpdata.org")
for day in ("2026-10-01", "2026-10-02", "2026-10-05"):
    df = s.execute_sql(f"SELECT quantity, price FROM audit.book AS OF LASTDATA BETWEEN #{day}# AND #{day}# "
                       "WHERE position_id = 'P0001'").to_dataframe()
    print(day, df.to_dict("records"))
all_days = s.execute_sql("SELECT COUNT(*) AS n FROM audit.book AS OF ALLVERSIONS WHERE position_id = 'P0001'")
print("versions of P0001 kept:", int(all_days.to_dataframe()["n"][0]))

Ask your question. Give your AI this prompt:

Using mcpdata, save a view of every version of position P0001 in audit.book, and report it.

What your AI does: the tool calls it makes for this prompt, and what came back

create_view(name="audit.p0001_history", select_sql="SELECT position_id, quantity, price FROM audit.book AS OF ALLVERSIONS WHERE position_id = 'P0001'") → created true, view audit.p0001_history

create_report(name="P0001, every version", view="audit.p0001_history") → report_id 7aeada21ef674bc2b52075a9347fe0b9

run_report(report_id="7aeada21ef674bc2b52075a9347fe0b9") → status ok, rows 3

Output of the steps above, from the last run

2026-10-01 [{'quantity': 3360, 'price': 140.69}]
2026-10-02 [{'quantity': 3560, 'price': 140.48}]
2026-10-05 [{'quantity': 3560, 'price': 139.51}]
versions of P0001 kept: 3

The assistant needs mcpdata connected (step 1) to run the query.

An audit needs the old values as well as the new. Because versions are kept automatically, nothing has to be remembered at write time, and the history can't be quietly edited: a correction is just another version.

How do I keep every version of a table so I can audit changes?

Steps

Why this works

Related questions