# Registries and SQL

An optional layer for teams that keep a data catalog. Publish tether's history as SQL tables that dashboards and pipelines can join, or drive which objects tether tracks from a registry query.

> **Note: Experimental**
>
> `export`, `publish`, and `import` are tested and documented but sit outside tether's core loop, and the code lives under [tether.experimental.registry](../reference/experimental.registry.md#tether.experimental.registry). The commands print a one-line note saying so. The exported schema (`TABLES`) and the import columns (`CANONICAL_COLUMNS`) may change before 0.2; the commands, and the `tether.ExportBundle` / `tether.ImportSpec` re-exports, stay. See [Writing a Backend](extending.md#experimental-and-graduation) for what "experimental" promises.

Data registries keep one row per object in SQL, saying what exists and where. tether keeps its facts as TOML manifests in the VCS, which is what gives it diffs, merges, undo and a cheap history walk. Three commands treat SQL tables as a *derived view* of the manifests, never the other way round:

| Direction | Command | What moves |
|----|----|----|
| manifests -\> file | `tether export` | commits, objects, states, refs as SQLite / Parquet / CSV / JSONL |
| manifests -\> database | `tether publish` | the same tables upserted into a Postgres schema |
| registry -\> manifests | `tether import` | which objects to track (and where to start), as [add](../reference/testing.ObjectDiff.md#tether.testing.ObjectDiff.add) / `update` / `remove` |

Nothing in this page changes how tether records or pins state: [commit](../reference/vcs.VcsAdapter.md#tether.vcs.VcsAdapter.commit) still does that, and only [commit](../reference/vcs.VcsAdapter.md#tether.vcs.VcsAdapter.commit) does.


# The tables

Every output shares one schema (`tether.experimental.registry.TABLES`), so a SQLite export and a Postgres schema have the same columns. JSON columns are `TEXT` in SQLite and `JSONB` in Postgres; timestamps are ISO-8601 text and `TIMESTAMPTZ`.

| Table | Key | Row |
|----|----|----|
| `commits` | [commit_id](../reference/vcs.CommitInfo.md#tether.vcs.CommitInfo.commit_id) | one VCS commit: [change_id](../reference/vcs.CommitInfo.md#tether.vcs.CommitInfo.change_id) (jj), author, [authored_at](../reference/vcs.CommitInfo.md#tether.vcs.CommitInfo.authored_at), [committed_at](../reference/vcs.CommitInfo.md#tether.vcs.CommitInfo.committed_at), [message](../reference/CommitResult.md#tether.CommitResult.message), [manifest_hash](../reference/manifest_hash.md#tether.manifest_hash) |
| `commit_parents` | `commit_id, parent_id` | one edge of the commit graph ([position](../reference/vcs.VcsAdapter.md#tether.vcs.VcsAdapter.position) 0 = first parent) |
| [refs](../reference/vcs.VcsAdapter.md#tether.vcs.VcsAdapter.refs) | `name, kind` | a bookmark / branch / tag / `head` and the commit it points at |
| [objects](../reference/StatusReport.md#tether.StatusReport.objects) | `commit_id, key` | one manifest at one commit: [kind](../reference/Action.md#tether.Action.kind), [uri](../reference/handles.FileHandle.md#tether.handles.FileHandle.uri), `locator_json`, `identity_json`, `identity_hash`, `policy_*`, `state_json`, `state_hash`, `pin_id`, `pin_ref`, [recoverable](../reference/ObjectStatus.md#tether.ObjectStatus.recoverable), `captured_at` |
| `object_states` | `kind, identity_hash, state_hash` | one distinct (system, state): the *versions* a registry keys on, with `first_commit_id` |
| `listings`, `listing_entries` | [listing_name](../reference/listing_name.md#tether.listing_name) (`, path`) | per-file listings for directory / prefix objects (`--listings`) |
| `workspace` | `workspace_id, key` | this checkout's bookmark branches (working refs), fork points, and last snapshot (`--workspace`) |
| `tether_meta` | [key](../reference/Action.md#tether.Action.key) | `schema_version`, `exported_at`, `head`, `vcs`, [tether_version](../reference/upgrade.Plan.md#tether.upgrade.Plan.tether_version) |

Two views come along: `objects_head` (the objects at the exported head) and `object_pins` (each pin and how many commits reference it). [uri](../reference/handles.FileHandle.md#tether.handles.FileHandle.uri) is the locator's [uri](../reference/handles.FileHandle.md#tether.handles.FileHandle.uri) when it has one - the natural join key to a catalog's URI column. `identity_hash` and `state_hash` are blake2b digests of the same canonical JSON that produces `pin_id`, so the same state on the same system hashes the same everywhere.


# Export

``` bash
tether export tether.sqlite                       # every reachable commit
tether export tether.sqlite --rev main            # one revision
tether export dump/ --format parquet --listings   # one file per table + schema.json
tether export tether.sqlite --append              # upsert into an existing file
```

`--append` never deletes: a jj commit that was later rewritten or abandoned keeps its rows. Rebuild without `--append` now and then, or let `publish` (below) do the incremental work against a database.

Query it in place:

``` bash
sqlite3 tether.sqlite "SELECT key, pin_ref, state_json FROM objects_head"
```

``` python
import duckdb

con = duckdb.connect()
con.sql("ATTACH 'tether.sqlite' AS t (TYPE sqlite)")
con.sql("""
    SELECT key, json_extract_string(state_json, '$.snapshot_id') AS snapshot
    FROM t.objects_head WHERE kind = 'icechunk'
""").show()
```

Or skip the file:

``` python
from tether import Repo

bundle = Repo.find(".").export(listings=True)
tables = bundle.to_arrow()          # dict[str, pyarrow.Table]
bundle.to_sqlite("tether.sqlite")   # or .to_dir("dump", "parquet")
```


## Joining a registry

Suppose a catalog has `catalog(uri, name, ...)` and a `runs` table records the VCS commit each job read as `tether_rev` (e.g. from `TETHER_REV`). Which Icechunk snapshot did each run read?

``` sql
SELECT r.run_id, c.name,
       json_extract_string(o.state_json, '$.snapshot_id') AS snapshot_id,
       o.pin_ref
FROM runs r
JOIN t.objects o ON o.commit_id = r.tether_rev AND o.kind = 'icechunk'
JOIN catalog c ON c.uri = o.uri;
```

No tether process is involved; the export is a plain table.


# Publish

`publish` writes the same tables into Postgres and is meant to run after every `tether commit`, from CI or an orchestrator step:

``` bash
export TETHER_PUBLISH_DSN=postgresql://tether@db.example.org/catalog   # or --to
tether publish --dry-run          # per-table row counts, nothing written
tether publish                    # schema `tether`; --schema to change it
```

    published 9 row(s) to schema 'tether' (12 commit(s) already present)
      commits            1 row(s)
      commit_parents     1 row(s)
      refs               3 row(s)
      objects            2 row(s)
      object_states      1 row(s)
      tether_meta        5 row(s)

It is idempotent and incremental. Commits are immutable, so rows of `commits`, `commit_parents` and [objects](../reference/StatusReport.md#tether.StatusReport.objects) whose commit is already in the schema are skipped. [refs](../reference/vcs.VcsAdapter.md#tether.vcs.VcsAdapter.refs), `workspace` and `tether_meta` describe *now* and are rewritten. Everything runs in one transaction, with `INSERT ... ON CONFLICT DO UPDATE`. Tables and views are created `IF NOT EXISTS`, so only the first run needs [CREATE](../reference/testing.Capability.md#tether.testing.Capability.CREATE) on the database. Like `--append`, it never deletes commit rows: a jj commit rewritten after publishing keeps its row under its old id, which `objects_head` ignores. Drop and republish the schema when that matters.

The DSN comes from `--to` or `$TETHER_PUBLISH_DSN`, never from `tether.toml` - the same rule as backend credentials. Needs the `postgres` extra (`pip install 'tether-vcs[postgres]'`).

From Python, `bundle.to_postgres(dsn, schema="tether")` returns a `PublishReport`; pass `connection=` to reuse an open connection or pool.


# Import

`import` runs the other way. The registry says what to track; tether writes the manifests. Rows must have tether's canonical columns:

| Column | Required | Meaning |
|----|----|----|
| [key](../reference/Action.md#tether.Action.key) | yes | object key |
| [kind](../reference/Action.md#tether.Action.kind) | yes | backend kind |
| [uri](../reference/handles.FileHandle.md#tether.handles.FileHandle.uri) | one of | becomes `locator.uri` |
| `locator_json` | [uri](../reference/handles.FileHandle.md#tether.handles.FileHandle.uri) / `locator_json` | full locator as a JSON object (merged over [uri](../reference/handles.FileHandle.md#tether.handles.FileHandle.uri)) |
| `policy_file`, `policy_pin` | no | default from `[defaults]` in `tether.toml` |
| `at` | no | initial position, exactly like `add --at` |

Other columns are ignored (and named in the plan). The mapping from your registry's columns is SQL on your side, which keeps tether free of per-registry options:

``` bash
tether import postgresql://reader@db.example.org/catalog --query "
  SELECT 'zarr/' || name AS key,
         'icechunk'      AS kind,
         uri
  FROM catalog
  WHERE format = 'icechunk'
" --dry-run
```

    plan: import (2 write(s))
                     add  zarr/run-041  [icechunk] s3://bucket/run-041.icechunk  register
                  update  zarr/run-042  [icechunk] s3://bucket/run-042.icechunk  locator changed; committed state kept
                          zarr/run-043: unchanged

Sources: a Postgres DSN, a SQLite file (`--table` or `--query`), a `.csv`, or a `.jsonl`. The query runs as one statement in a read-only transaction. A standing query lives in `.tether/secrets.toml`, which `import` uses when neither `--table` nor `--query` is given. The committed `tether.toml` may not hold one, or its author would choose SQL that runs with every cloner's DSN.

``` toml
# .tether/secrets.toml
[import]
query = "SELECT 'zarr/' || name AS key, 'icechunk' AS kind, uri FROM catalog WHERE format = 'icechunk'"
```

Then `tether import postgresql://...` is enough. The plan has three verbs:

- [add](../reference/testing.ObjectDiff.md#tether.testing.ObjectDiff.add): the key is new.
- `update`: the key exists and its locator or policy differs. The manifest's committed `state` and [pin](../reference/testing.ObjectBackend.md#tether.testing.ObjectBackend.pin) are kept; the next [status](../reference/backends.VerifyReport.md#tether.backends.VerifyReport.status) shows whether the object moved.
- `remove`: only with `--sync`, for registered keys the source no longer lists. Like `tether remove`, this leaves working branches for `gc`.

A key whose [kind](../reference/Action.md#tether.Action.kind) would change is refused: remove and add it again. `--plan FILE` and `--from-plan FILE` work as for other planned commands; an import plan checks the manifests, not the checkout. After importing, run `tether status` and `tether commit`: import changed *what is tracked*, and commit records *what state it is in*.


## Round trip

The canonical columns are a subset of the [objects](../reference/StatusReport.md#tether.StatusReport.objects) table, so an export can be edited and imported back:

``` bash
tether export snap.sqlite
sqlite3 snap.sqlite "UPDATE objects SET policy_pin = 'record' WHERE key LIKE 'scratch/%'"
tether import snap.sqlite --sync --query "
  SELECT key, kind, locator_json, policy_file, policy_pin
  FROM objects_head
"
```

An unmodified export imports as an empty plan.


# Prior art

Files are canonical, tables are a rebuildable index - every comparable tool lands on the same shape:

| Tool | Shape | tether analogue |
|----|----|----|
| [git-history](https://github.com/simonw/git-history) | a file's git history as `commits` / `item` / `item_version` tables | `export` / `publish` |
| [Quilt](https://docs.quilt.bio) | package manifests in S3, Iceberg tables beside them | `export` / `publish` |
| [Dolt](https://www.dolthub.com), Iceberg | version graph as `dolt_log` / `$snapshots` system tables | `export` / `publish` |
| [Kart](https://kartproject.org) | git holds the data; a SQL database is the working copy; `kart commit` folds edits back | `import` then `tether commit` |
| [Fossil](https://fossil-scm.org) | a VCS *in* SQLite whose SQL metadata `fossil rebuild` recomputes from the artifacts | the manifests are canonical |