> For the complete documentation index, see [llms.txt](https://docs.stacksync.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.stacksync.com/connectors/postgres/capture-field-history.md).
# Capture Field History
Stacksync can maintain a full field-level change history for any connected source, including Postgres, Salesforce, and HubSpot. Every insert, update, and delete is recorded as a row that captures the previous value, the new value, and exactly which fields changed.
### How it works
**Producer.** A trigger on your source watches for inserts, updates, and deletes. Each change is pushed onto a queue, which buffers and flushes in batches. Turn on historical backfill to record a baseline version of every existing row up front.
**Consumer.** Reads changes off the queue and, for each one:
1. Looks up the record's most recent stored version in the history table. This is the old value.
2. Pairs it with the change from the queue. This is the new value.
3. Computes the field-level diff.
4. Writes one history row holding the old version, the new version, and what changed.
That stored new version becomes the old value the next time the record changes. The first time a record is seen there is no prior row, so the old value is empty as a baseline, and every change after that shows a full before and after.
The history table does double duty: it is both the output and the memory of previous values.
### What gets recorded
Each row in the history table contains:
| Field | Description |
| --------------------- | ------------------------------------------- |
| `change_id` | Unique id for the change |
| `change_timestamp` | When the change occurred |
| `record_id` | The changed record's id |
| `update_type` | `create`, `update`, or `delete` |
| `past_record_version` | The full record before the change |
| `new_record_version` | The full record after the change |
| `changes` | Field-level diff, `{ field: { old, new } }` |
### Setup
1. Create the history table (see below).
2. Build the **producer** workflow: source trigger (with historical backfill on) into a queue.
3. Build the **consumer** workflow: queue trigger into a query for the latest version per record, into a transform that pairs old and new and computes the diff, into an insert back to the history table.
#### Example: history table
```sql
CREATE TABLE field_history (
change_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
change_timestamp timestamptz NOT NULL DEFAULT now(),
table_name text,
record_id text,
update_type text, -- create | update | delete
past_record_version jsonb,
new_record_version jsonb,
changes jsonb, -- { field: { old, new } }
seq bigserial
);
```
#### Example: fetch the latest version per record
The consumer uses this to find the old value. It returns one row per record, the most recent stored version, so the transform can match each incoming change to its previous state. Note that it does not filter by what is in the queue; it pulls the latest per record and the transform does the matching.
```sql
SELECT DISTINCT ON (table_name, record_id)
table_name, record_id, new_record_version
FROM field_history
ORDER BY table_name, record_id, change_timestamp DESC, seq DESC;
```
### How changes are captured
Each distinct change is captured as it occurs and recorded as its own row. Changes to the same record need a small gap between them to each be picked up individually. Edits spaced out in normal time, which covers typical auditing, are all captured separately. Several updates to the same record landing back to back, with effectively no gap, are captured as the latest state.
### Works with any source
The same pattern works across every connected system. Because the old value is reconstructed inside the workflow rather than read from the source, Postgres, Salesforce, HubSpot, and others all produce the same clean before-and-after history.
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:
```
GET https://docs.stacksync.com/connectors/postgres/capture-field-history.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.