Metadata-Version: 2.4
Name: hindsightdb
Version: 0.1.1
Summary: Capture PostgreSQL row changes for per-field history, blame, and time travel.
License-Expression: Apache-2.0
Project-URL: Homepage, https://hindsightdb.com
Keywords: postgresql,audit-log,change-data-capture,data-lineage,temporal-data
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Topic :: Database
Classifier: Topic :: System :: Logging
Classifier: Typing :: Typed
Requires-Python: >=3.11
Description-Content-Type: text/markdown
License-File: LICENSE
Provides-Extra: sqlalchemy
Requires-Dist: sqlalchemy>=2.0; extra == "sqlalchemy"
Provides-Extra: yaml
Requires-Dist: pyyaml>=6.0; extra == "yaml"
Provides-Extra: outbox
Requires-Dist: psycopg[binary]>=3.1; extra == "outbox"
Provides-Extra: wal
Requires-Dist: psycopg[binary]>=3.1; extra == "wal"
Provides-Extra: dev
Requires-Dist: sqlalchemy>=2.0; extra == "dev"
Requires-Dist: pyyaml>=6.0; extra == "dev"
Requires-Dist: psycopg[binary]>=3.1; extra == "dev"
Requires-Dist: pytest>=8.0; extra == "dev"
Requires-Dist: black>=24.0; extra == "dev"
Requires-Dist: ruff>=0.4; extra == "dev"
Requires-Dist: mypy>=1.10; extra == "dev"
Requires-Dist: types-PyYAML; extra == "dev"
Dynamic: license-file

# hindsightdb

Capture PostgreSQL row changes in Hindsight for per-field history, blame, and time travel.

## Choose a capture mode

| Mode | Best when | What it sees |
| --- | --- | --- |
| `orm` | One Python app owns every write | SQLAlchemy ORM operations in that process |
| `outbox` | More than one writer, raw SQL, jobs, or manual fixes touch the database | Every committed insert, update, and delete through PostgreSQL triggers |

`orm` is the smallest installation. `outbox` is the safer operational default because it
captures writes regardless of which application made them. It requires a migration and a
small shipper in the backend process.

The mode registered in the Hindsight console records the **expected** capture strategy. It
selects setup instructions and health checks; it is not an ingest firewall. If a source is
registered as `orm` but sends `outbox` events, the events are accepted. The console warns
about the mismatch and follows the observed producer for live setup and checks.

## Outbox quick start

```bash
uv add "hindsightdb[outbox,yaml]"
# or: pip install "hindsightdb[outbox,yaml]"
```

```yaml
# capture.yaml
mode: outbox
```

Set the source key minted by Hindsight and the server URL in the backend runtime:

```bash
HINDSIGHT_KEY=hs_your_source_...
HINDSIGHT_URL=https://hindsightdb.com
```

Start the shipper where the application creates its SQLAlchemy engine:

```python
import os
import hindsight

hindsight.init(
    engine,
    key=os.environ["HINDSIGHT_KEY"],
    mode="outbox",
)
```

Generate the trigger kit as an application migration:

```bash
uv run hindsight apply \
  --dsn "$DATABASE_URL" \
  --config capture.yaml \
  --emit-migration alembic/versions/0042_hindsight.py \
  --down-revision 0041
```

The migration creates `capture_outbox`, `capture_state`, capture functions, and one trigger
per captured table. Run it through the application's normal migration pipeline. Do not use
automatic installation in a production app that already owns schema changes with Alembic.

If `HINDSIGHT_KEY` is absent after the migration, application writes still succeed and the
triggers still queue changes locally, but nothing drains `capture_outbox`. Treat the key and
shipper as required runtime configuration and monitor outbox depth.

## CI and schema drift

Run this against the migrated CI database, after migrations:

```bash
uv run hindsight check --dsn "$DATABASE_URL" --config capture.yaml
```

For outbox capture, the check fails on:

- a new captured table without a trigger;
- a missing or disabled trigger;
- drift in ignored columns, primary keys, tenant columns, or fail-open/fail-closed policy;
- excluded tables whose old trigger is still installed;
- missing primary keys, unexplained exclusions, and captured secret-looking columns.

Use the database catalog (`--dsn` without `--metadata`) as the production gate when the
database contains legacy or unmapped tables. An ordinary new captured column needs no new
trigger: the trigger reads the row dynamically. A new table or a policy change does require
a regenerated migration, and the check makes that omission fail CI.

The Hindsight console shows the manifest reported by the SDK, last contact, lag, and outbox
health. It cannot query the private source database's PostgreSQL catalog, so it complements
this CI gate rather than replacing it.

## Load the existing rows once

Live capture starts recording new changes immediately. Genesis is separate: it establishes
the current state of rows that existed before capture was installed. Run it once **inside the
backend app container** or as a one-off job where both the source database credentials and
the Hindsight source key are available:

```bash
uv run hindsight genesis \
  --dsn "$DATABASE_URL" \
  --config capture.yaml \
  --run-id your-source-initial
```

Some platforms expose `DB_HOST`, `DB_PORT`, `DB_DATABASE`, `DB_USER`, and `DB_PASSWORD`
instead of `DATABASE_URL`. Build the DSN with the application's existing database helper or
pass a complete PostgreSQL DSN to `--dsn`.

For a busy production database, a consistent dump avoids scanning a changing live snapshot:

```bash
uv run hindsight genesis \
  --from-dump source.dump \
  --scratch-dsn "$DATABASE_ADMIN_URL" \
  --config capture.yaml \
  --run-id your-source-initial
```

Keep the same `--run-id` when retrying; the server deduplicates that run. Genesis should not
run in normal CI, after every migration, or at every application start. Those environments
either contain no real baseline or may run several replicas and resend a large database.
Make it an explicit, default-off release job if your deployment platform supports one.

## ORM quick start

```bash
uv add "hindsightdb[sqlalchemy,yaml]"
```

```python
import os
import hindsight

hindsight.init(engine, key=os.environ["HINDSIGHT_KEY"], mode="orm")
```

ORM mode needs no database migration, but it cannot see raw SQL, another service, database
jobs, or manual changes. Move to outbox mode when that limitation is no longer true.
