> ## Documentation Index
> Fetch the complete documentation index at: https://ploti.ai/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Record ids

> What `_ploti_record_id` identifies, how it carries across versions, and what to do when it changes

Every row Data Feeds delivers carries a `_ploti_record_id`. It is the handle you key your copy of a dataset on, and the one to store when your own systems point at a record. This page says what the id means, how Ploti decides that a record in a new version is the same record as before, and what to do when it cannot tell.

## In short

* Key your tables on `_ploti_record_id`. Store it with `_ploti_dataset_id` when one table holds several datasets.
* Store the id as it is. Don't parse it or try to rebuild it from the record's data.
* Apply each changes file as written: upsert or delete by id. Your copy then matches the version.
* A record Ploti could not match to the version before arrives as a delete of the old id and an `added` row with a new one. Anything you attached to the old id needs attaching again.
* When a version arrives as `feed.dataset.resync_required` with reason `record_ids_changed`, reload the dataset and rewrite the ids you store with the version's [id map](#when-ids-are-replaced).

The rest of the page is the detail behind those five rules.

## What an id identifies

A record id names one record within one dataset, for as long as Ploti can tell from one version to the next that it is the same record.

* It is scoped to its dataset. The same parcel in two datasets has two ids, and nothing about the ids links them. To key records from several datasets in one table, store the id with `_ploti_dataset_id`.
* It is not a source key, a row number or a hash of the row. It cannot be recomputed from the record's data, so a record whose values all change can keep its id, and two identical rows have different ids.
* It is opaque. It is a string that starts with `rec_`. Nothing else about its format is promised: store it as it is and do not parse it.
* It names one record and is not given to another. A record you were sent as removed that later returns at the source usually comes back under a new id. It takes back its old id only when Ploti ties it to that record, in a version that says so with an [id map](#when-ids-are-replaced) or in a reload of a version you had.

## What you can rely on

* No two rows of a version share an id, and a version's snapshot, changes file and id map use the same ids.
* A record matched to the version before keeps its id. A record that is not matched arrives as a delete of its old id and an `added` row with a new one, or, in a version you reload, as a new id in its snapshot.
* A matched record's id changes only in a version sent as `feed.dataset.resync_required` with reason `record_ids_changed`, and that version's [id map](#when-ids-are-replaced) gives its new id.
* When a version you already had is released again, its rebuild is matched to what Ploti kept for that version, and a record it matches keeps the id you hold. The rebuild arrives as `feed.dataset.resync_required`: reload it, and a record it did not match has a new id in its snapshot. When it kept records but none could be compared, the rebuild is held for review rather than sent.
* A version waits for Ploti's matching agent before it is released, so the ids you receive are normally the ones matching settled on. When a later match ties a record you hold under one id to an earlier one, the version that applies it says so with an [id map](#when-ids-are-replaced).

## How records are matched

Ploti assigns an id when a record first appears. Each new version's records are then matched to the dataset's previous release, and a matched record keeps its id.

The previous release is usually the version before. The exception is a version that was held for review and discarded in between. Ploti still published that one in the Ploti app, where reads and dataset watches see it, though Data Feeds never sent it, so the next version is matched to it. A keyed record that the discarded version dropped takes back the id you hold when it returns. A record without a key gets a new id.

Matching goes in this order:

1. **Key.** When the dataset is keyed (`keyed` is `true`, with `keyColumns` such as a permit number), a record whose key value appears once in each version is the same record. An edit arrives as one `changed` upsert.
2. **Same content.** A record identical to one in the version before is the same record, compared over the columns both versions have in the same shape (the same name and type). A column holding one value in every row of a version, such as the time the source was read, is a property of the version and is left out. Identical rows pair one to one, so duplicates keep distinct ids.
3. **Agent.** A record left over in the new version goes to Ploti's matching agent when it shares values with a left-over record of the version before, or lies within about 50 m of it. It keeps the old id when the agent judges it the same record, edited or renumbered.
   * When a column turned over in most left-over records and the records are otherwise identical, the agent judges that column once for the whole version. Examples are a source renumbering its ids, or a time it stamps on every edit.
   * At most 200 records a version are judged one by one, chosen among the first 20,000 left over, and none when more than 250,000 are left over. The rest get new ids.

Every other record gets a new id. A `changed` row in a changes file says which rule matched it in `_ploti_match` (`key`, `content` or `agent`), and on an `agent` match gives the agent's confidence in `_ploti_match_score`.

The version waits for the agent before it is released, up to 24 hours for each ask. When the agent fails or runs past that wait, it is asked once more, so a version can wait up to 48 hours. A version is held for review instead of sent when:

* the agent still has not decided, or cannot be asked at all (`identity_unresolved`). A version whose last ask is cancelled is held at once.
* most of its records would get new ids while its row count barely moved (`identity_ambiguous`)

When a version you had is released again, its records are matched to the key values and content Ploti kept for it instead, and the agent does not take part.

* A record matches by key only when the key columns are unchanged.
* A record matches by content only when both versions have the same set of columns.
* When Ploti kept records but nothing can be compared, a version that leaves any of its records unmatched is held for review (`identity_ambiguous`).

### Keys and schema changes

The key is pinned the first time the source's declared key holds: every key column present, and the key unique and never null. It is kept from then on.

* A version where the key has duplicates or nulls is held for review (`key_violation`). The key is not unpinned.
* Pinning a key for the first time is a MINOR schema change with `keyColumnsChanged: true`.
* A key that changes from one set of columns to another, or is dropped, is a MAJOR change and is held for review.

A record keeps its id through either change when it is matched. After a MAJOR change, fewer records may match, and those that do not get new ids:

* Matching against the previous release uses only the columns both versions have in the same shape, and the new key when the version before has its columns.
* Matching against what Ploti kept uses the key only if it is unchanged.

## What is not promised

* **That every edit keeps its id.** Matching is conservative. A record the key, its content and the agent cannot tie to the version before gets a new id, and Ploti prefers that to guessing.
* **Identity across datasets.** Two datasets that describe the same real-world thing give it unrelated ids. To have Ploti tie records of different datasets together, ask for a [custom feed](https://ploti.ai/docs/feeds/custom-feeds.md#how-feeds-tie-together): its link columns hold the record id of the row each record belongs to.
* **Matching across a gap.** A record is matched only to the release before it. One that leaves the source and returns in a later version you receive is a new record. The exception is a keyed record dropped by a version that was discarded, [as above](#how-records-are-matched).
* **Anything about the id's format** beyond the `rec_` prefix, including its length and how it is made.

## The hard cases

### A record that was not matched

An edit Ploti could not match, such as a renumbered record with no key and too few shared values, arrives in the changes file as two rows:

* a `delete` of the old id, with `_ploti_delete_reason` `removed_at_source`
* an `upsert` with `_ploti_change` `added`, under a new id

This is an accepted outcome, not a fault in the feed: applying the file as written leaves your copy correct. What you lose is the link between the two ids, so anything your own systems attached to the old id needs to be attached again. If you need that link, match the version's `added` rows to its deletes by your own rules.

### Merges and splits

Matching is one to one: one record of the version before continues as at most one record of the new version.

* **Merge.** When the source combines two records into one, one of them keeps its id, as a `changed` row when its values changed. The other arrives as a delete with `removed_at_source`. The delete reason is the same as for a record that left the source, so it cannot tell you a merge happened.
* **Split.** When the source divides one record into several, one of them keeps the id and the rest arrive as `added` rows with new ids.

### When ids are replaced

A version with `recordIdsChanged: true` arrives as `feed.dataset.resync_required` with reason `record_ids_changed`. It has no changes file. Its `files.idMap` lists `_ploti_previous_record_id`, `_ploti_record_id` and `_ploti_dataset_id` for every record carried from the version you hold.

Ploti sends one when its staff replace a dataset's ids, and also when Ploti's matching agent found that a record you hold under one id continues one under another. So a version can change a few ids or all of them.

* Read the map as old id to new id for every record that continues. Many of its rows can map an id to itself: those records kept their ids.
* An id you hold that is not in `_ploti_previous_record_id` belongs to a record the version no longer has. A record of the version whose id is not in `_ploti_record_id` is new in it.
* A map covers one step: from the version you hold to this version. Apply versions in order, and apply each one's map before the next.
* Reload the dataset from the version's snapshot as usual, then rewrite any ids your own systems store with the map.

### A reload without an id map

A `resync_required` carries no id map when its reason is `schema_changed`, `key_columns_changed` or `previous_unavailable`. That last one means the snapshot of the version before is past retention, or a version you had was released again.

Records it matched keep the ids you hold, and the rest have new ids. To find out which, compare the ids you hold with the snapshot's before you replace your rows:

* an id in both is the same record
* an id only you hold is gone
* an id only in the snapshot is new

When a version is both a reload and an id replacement, its reason is `record_ids_changed` and it carries a map. See [resync reasons](https://ploti.ai/docs/feeds/events.md#event-types).

### A held version

A version held for review is not sent, and neither is anything after it for that dataset, until Ploti's team release or discard it, or a newer version replaces it.

From your side, the dataset sends one `feed.dataset.delayed` event and then goes quiet, and `current` stays on the version you hold. A version waiting for the matching agent can wait up to 48 hours (two asks of up to 24 hours each) before it is either released or held for staff.

Keep applying events for your other datasets. The held one resumes in chain order. See [holds](https://ploti.ai/docs/feeds/schemas.md#holds).

## Ids on the map and in dataset watches

The same ids name rows outside Data Feeds. A record's `_ploti_record_id` is given when a version of the dataset goes live on Ploti's map, and Data Feeds reads it from there. So an id in a feed file, a row on the map and a `recordId` from the `datasets changes` tool (which dataset watches send to assistants) all name the same record.

The map's change log is a separate record, though, and differs from a feed in a few ways:

* It has an entry for every version that goes live on the map, numbered by the map, not by `_ploti_version`. A feed version can span several of them, such as a version that was held for review and discarded.
* It does not wait for Ploti's matching agent. A record can show as removed and a new id as added in one version, and a `renamed` change in the next says the new id now names the old record.
* A keyed record that a map version dropped can take back its old id when it returns, for a dataset no subscriber was sent that removal.

Where you consume both, key on the id and follow `renamed` changes from the map's log. Feed subscribers follow the version chain and id maps described above.

## Worked example

This keeps a local DuckDB copy of one dataset in step with its feed. It assumes you have downloaded a version's file parts into a folder per kind (`snapshot/`, `changes/`, `id_map/`), and that you keep two tables of your own: `feed_state`, with the version you hold of each dataset, and `my_notes`, rows of yours that point at records by id.

Load a snapshot, keyed on the record id:

```sql theme={null}
CREATE OR REPLACE TABLE permits AS
SELECT * FROM read_parquet('v42/snapshot/*.parquet');
CREATE UNIQUE INDEX permits_id ON permits (_ploti_record_id);

INSERT OR REPLACE INTO feed_state (dataset_id, version)
VALUES ('ds_orange_fl_building_permits', 42);
```

Apply a `feed.dataset.updated` in one transaction, after checking its `previousVersion` against the version you hold. The changes file carries the envelope columns, then `_ploti_op`, `_ploti_change`, `_ploti_delete_reason`, `_ploti_changed_columns`, `_ploti_match` and `_ploti_match_score`, then the record; leave the change columns out when you write rows back:

```python theme={null}
import duckdb

con = duckdb.connect("feeds.duckdb")
dataset, version, previous = "ds_orange_fl_building_permits", 43, 42

(held,) = con.execute(
    "SELECT version FROM feed_state WHERE dataset_id = ?", [dataset]
).fetchone()
if version <= held:
    pass  # already applied: skip
elif previous != held:
    raise RuntimeError("chain broken: reload from the dataset's current version")
else:
    con.execute("BEGIN")
    # Every upsert and delete removes the row it names; upserts then insert
    # the record as of this version.
    con.execute("""
        DELETE FROM permits WHERE _ploti_record_id IN (
          SELECT _ploti_record_id FROM read_parquet('v43/changes/*.parquet'))
    """)
    con.execute("""
        INSERT INTO permits BY NAME
        SELECT * EXCLUDE (_ploti_op, _ploti_change, _ploti_delete_reason,
                          _ploti_changed_columns, _ploti_match, _ploti_match_score)
        FROM read_parquet('v43/changes/*.parquet')
        WHERE _ploti_op = 'upsert'
    """)
    con.execute(
        "UPDATE feed_state SET version = ? WHERE dataset_id = ? AND version = ?",
        [version, dataset, held],
    )
    con.execute("COMMIT")
```

A `feed.dataset.schema_changed` with a MINOR change comes first when the version adds a column: add it to `permits` before applying the changes.

For a `resync_required` with reason `record_ids_changed`, reload from the version's snapshot as above, and in the same transaction rewrite the ids you store with its map. One `UPDATE` reads each id once, so an id is rewritten at most once:

```sql theme={null}
UPDATE my_notes SET record_id = m._ploti_record_id
FROM read_parquet('v44/id_map/*.parquet') AS m
WHERE my_notes.record_id = m._ploti_previous_record_id
  AND m._ploti_previous_record_id <> m._ploti_record_id;

-- Ids that no longer name a record in this version.
SELECT n.* FROM my_notes n
WHERE n.record_id NOT IN (SELECT _ploti_record_id FROM permits);
```

For a reload without an id map, run the comparison before you replace `permits`:

```sql theme={null}
SELECT p._ploti_record_id AS gone
FROM permits p
ANTI JOIN read_parquet('v45/snapshot/*.parquet') s USING (_ploti_record_id);
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.