konnosDashboard
page.tsx190 lines · main
1import { DocsShell } from '../../../components/shell';
2
3export const metadata = {
4 title: 'doltgres history + time travel',
5};
6
7export default function DoltgresHistoryPage() {
8 return (
9 <DocsShell>
10 <h1 className="font-mono text-2xl tracking-tight">history + time travel</h1>
11 <p className="mt-2 font-mono text-sm text-[var(--color-text-muted)]">
12 Because DoltGres versions the whole database, every past state is still queryable.
13 You can read a table as it looked at any commit, branch, or moment in time, walk the
14 commit log, and ask &ldquo;who changed this exact row, and to what?&rdquo; — all in
15 plain SQL.
16 </p>
17
18 <Callout title="an audit log you get for free">
19 You do not have to build history tables, triggers, or change-data-capture. Every
20 cell&apos;s full history is already recorded by the storage engine and queryable.
21 The functions and tables on this page are how you read it.
22 </Callout>
23
24 <h2 className="mt-12 font-mono text-lg">time travel with AS OF</h2>
25 <p className="mt-3 font-mono text-sm text-[var(--color-text-muted)]">
26 DoltGres supports SQL:2011-style <code>AS OF</code>. The operand is any valid
27 reference — a commit hash, a branch name, or a timestamp — and it queries the table
28 exactly as it existed at that point.
29 </p>
30 <Snippet>{`-- as of a specific commit hash
31SELECT * FROM myTable AS OF 'kfvpgcf8pkd6blnkvv8e0kle8j6lug7a';
32
33-- as of the head of another branch
34SELECT * FROM myTable AS OF 'add-welcome-note';
35
36-- as of a moment in time
37SELECT * FROM myTable AS OF TIMESTAMP('2020-01-01');
38
39-- even the schema is versioned
40SHOW CREATE TABLE myTable AS OF 'add-welcome-note';`}</Snippet>
41 <p className="mt-2 font-mono text-xs text-[var(--color-text-subtle)]">
42 Each table in a query can carry its own <code>AS OF</code> — so you can join
43 today&apos;s <code>orders</code> against last month&apos;s <code>prices</code> in a
44 single statement to see how a number would have looked.
45 </p>
46
47 <h2 className="mt-12 font-mono text-lg">the commit log</h2>
48 <p className="mt-3 font-mono text-sm text-[var(--color-text-muted)]">
49 The commit history lives in the <code>dolt</code> schema. Read it like any table, or
50 use the <code>DOLT_LOG</code> table function when you want to scope the walk to
51 specific refs.
52 </p>
53 <Snippet>{`-- full history reachable from the current HEAD
54SELECT * FROM dolt.log;
55
56-- commits before a date (columns: commit_hash, committer, email, date, message)
57SELECT * FROM dolt.commits WHERE date < '2026-01-01';
58
59-- table function: commits on main that are NOT on feat
60SELECT * FROM DOLT_LOG('main', '--not', 'feat');`}</Snippet>
61
62 <h2 className="mt-12 font-mono text-lg">per-table history, diff &amp; blame</h2>
63 <p className="mt-3 font-mono text-sm text-[var(--color-text-muted)]">
64 Every user table gets a family of companion system tables — DoltGres builds them
65 automatically, named after your table. These are the workhorses of data forensics.
66 </p>
67 <div className="mt-3 overflow-x-auto rounded-md border border-[var(--color-border-subtle)]">
68 <table className="w-full border-collapse font-mono text-xs">
69 <thead>
70 <tr className="border-b border-[var(--color-border-subtle)] bg-[var(--color-surface)] text-left text-[var(--color-text)]">
71 <th className="p-3">system table</th>
72 <th className="p-3">what it gives you</th>
73 </tr>
74 </thead>
75 <tbody className="text-[var(--color-text-muted)]">
76 <TblRow
77 name="dolt_history_<table>"
78 desc="Every version of every row across all commits — the full revision trail of the data."
79 />
80 <TblRow
81 name="dolt_diff_<table>"
82 desc="Row-level changes with to_/from_ column pairs and a diff_type of added, modified, or removed."
83 />
84 <TblRow
85 name="dolt_commit_diff_<table>"
86 desc="The diff between any two commits or branches — you pass the two refs as filters."
87 />
88 <TblRow
89 name="dolt_blame_<table>"
90 desc="Who last changed each row, in which commit, when, and with what message."
91 />
92 </tbody>
93 </table>
94 </div>
95 <Snippet>{`-- the full history of one row's every value
96SELECT * FROM dolt_history_employees WHERE id = 0 ORDER BY commit_date;
97
98-- only the rows that were modified
99SELECT * FROM dolt_diff_employees WHERE diff_type = 'modified';
100
101-- diff one table between two refs (here: main vs the feat branch)
102SELECT * FROM dolt_commit_diff_employees
103WHERE from_commit = 'main' AND to_commit = 'feat';
104
105-- who last touched each row
106SELECT * FROM dolt_blame_employees LIMIT 5;`}</Snippet>
107 <p className="mt-2 font-mono text-xs text-[var(--color-text-subtle)]">
108 In a <code>dolt_diff_&lt;table&gt;</code> row, uncommitted working-set changes show
109 up with <code>to_commit = 'WORKING'</code>, so you can inspect pending edits the
110 same way you inspect committed ones.
111 </p>
112
113 <h2 className="mt-12 font-mono text-lg">diff table functions</h2>
114 <p className="mt-3 font-mono text-sm text-[var(--color-text-muted)]">
115 When you want a diff scoped by ref rather than reading the per-table system table,
116 DoltGres ships table functions that return rows. Pass the two refs and (where
117 relevant) a table name.
118 </p>
119 <Snippet>{`-- the per-row diff of mytable between two branches
120SELECT * FROM DOLT_DIFF('main', 'feat', 'mytable');
121
122-- a per-table summary of rows added / modified / deleted
123SELECT * FROM DOLT_DIFF_STAT('main', 'feat');
124
125-- a high-level "which tables changed" summary
126SELECT * FROM DOLT_DIFF_SUMMARY('main', 'feat');`}</Snippet>
127
128 <h2 className="mt-12 font-mono text-lg">what to read next</h2>
129 <ul className="mt-3 flex flex-col gap-2 font-mono text-sm">
130 <NextLink
131 href="/doltgres/version-control"
132 title="version control"
133 body="branches, commits, merges, and conflicts — how to write the history this page reads"
134 />
135 <NextLink
136 href="/undo"
137 title="undo + snapshots"
138 body="how briven turns commits and branches into one-click undo for your data"
139 />
140 <NextLink
141 href="/doltgres/limitations"
142 title="beta + limitations"
143 body="what doltgres does and does not support yet"
144 />
145 </ul>
146 </DocsShell>
147 );
148}
149
150function Callout({ title, children }: { title: string; children: React.ReactNode }) {
151 return (
152 <div className="mt-4 rounded-md border border-[var(--color-border-subtle)] bg-[var(--color-surface)] p-4 font-mono text-sm text-[var(--color-text-muted)]">
153 <p className="font-semibold text-[var(--color-text)]">{title}</p>
154 <div className="mt-1">{children}</div>
155 </div>
156 );
157}
158
159function Snippet({ children }: { children: string }) {
160 return (
161 <pre className="mt-3 overflow-x-auto rounded-md border border-[var(--color-border-subtle)] bg-[var(--color-surface)] p-4 font-mono text-xs">
162 <code>{children}</code>
163 </pre>
164 );
165}
166
167function TblRow({ name, desc }: { name: string; desc: string }) {
168 return (
169 <tr className="border-b border-[var(--color-border-subtle)] last:border-0">
170 <td className="p-3 align-top text-[var(--color-text)]">
171 <code>{name}</code>
172 </td>
173 <td className="p-3 align-top">{desc}</td>
174 </tr>
175 );
176}
177
178function NextLink({ href, title, body }: { href: string; title: string; body: string }) {
179 return (
180 <li>
181 <a
182 href={href}
183 className="block rounded-md border border-[var(--color-border-subtle)] bg-[var(--color-surface)] p-4 transition hover:border-[var(--color-border)]"
184 >
185 <p className="font-mono text-[var(--color-text)]">{title}</p>
186 <p className="mt-1 font-mono text-xs text-[var(--color-text-muted)]">{body}</p>
187 </a>
188 </li>
189 );
190}