Metadata-Version: 2.5
Name: plinxore-tap-diffgen
Version: 0.1.3
Summary: Singer tap that replays a generic application audit-log table ('diffgen': insert/update/delete journal) into fresh repulls of the affected rows plus delete tombstones, so a target sees inserts, updates, and deletes -- not just a timestamp-filtered snapshot. Supports PostgreSQL and MySQL. Built with the Meltano Singer SDK.
Project-URL: Homepage, https://github.com/plinxore/tap-diffgen
Project-URL: Repository, https://github.com/plinxore/tap-diffgen
Project-URL: Issues, https://github.com/plinxore/tap-diffgen/issues
Author-email: Ibrahim Gado Mayaki Hamza <ibrahim.mayaki@plinxore.com>
License-Expression: Apache-2.0
License-File: LICENSE
Keywords: ELT,audit-log,cdc,mysql,postgres,singer-io
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Requires-Python: >=3.10
Requires-Dist: mysql-connector-python~=26.7.0
Requires-Dist: psycopg2-binary~=2.9.12
Requires-Dist: singer-sdk~=0.54.5
Requires-Dist: typing-extensions>=4.5.0; python_version < '3.13'
Description-Content-Type: text/markdown

# tap-diffgen

[![PyPI version](https://img.shields.io/pypi/v/plinxore-tap-diffgen.svg)](https://pypi.org/project/plinxore-tap-diffgen/)
[![PyPI downloads](https://img.shields.io/pypi/dm/plinxore-tap-diffgen.svg)](https://pypi.org/project/plinxore-tap-diffgen/)
[![CI](https://github.com/plinxore/tap-diffgen/actions/workflows/ci.yml/badge.svg?branch=main)](https://github.com/plinxore/tap-diffgen/actions/workflows/ci.yml)
[![Python](https://img.shields.io/pypi/pyversions/plinxore-tap-diffgen.svg)](https://pypi.org/project/plinxore-tap-diffgen/)
[![License: Apache 2.0](https://img.shields.io/badge/License-Apache%202.0-blue.svg)](LICENSE)

`tap-diffgen` is a generic Singer tap for the "diffgen" pattern: an application-level audit-log table that journals every `INSERT`/`UPDATE`/`DELETE` made to other tables in the same database (columns: `id`, `tablename`, `table_id`, `op`, `ts_created`). Instead of reading a target table directly, the tap replays that log since the last bookmark and, per event:

- `i`/`u` (insert/update): repulls the affected row fresh from the target table and emits it with `is_deleted: false`.
- `d` (delete): emits a tombstone -- just the primary key, `is_deleted: true`, every other business column `null`.

Every emitted record also carries `_sdc_diffgen_ts_created` (the diffgen event's own timestamp), regardless of `op`. This is what a plain timestamp-filtered incremental extraction can never represent on its own: a row that no longer exists doesn't show up in a `WHERE updated_at > ...` query, so deletes silently vanish downstream. Built with the [Meltano Singer SDK](https://sdk.meltano.com), supporting PostgreSQL and MySQL.

## Installation

```bash
pip install plinxore-tap-diffgen
```

## Configuration

Connection settings are global to the tap -- one tap instance targets one database, shared by every configured table (a single connector is built once per run, not one per table).

| Setting | Required | Description |
|---|---|---|
| `sql_dialect` | yes | `postgres` or `mysql`. |
| `host` | yes | Database host. |
| `port` | no | Database port. Defaults to the dialect's standard port (5432 for postgres, 3306 for mysql) if omitted. |
| `user` | yes | Database user. |
| `password` | yes | Database password. |
| `database` | yes | Database name. |
| `schema` | no | Schema to look up the diffgen and target tables under. Postgres only (defaults to `public` if omitted); ignored for MySQL, where `database` already plays this role. |
| `tables` | yes | One entry per target table to replicate via diffgen -- see below. |

### `tables`

Each entry is an object:

| Field | Required | Description |
|---|---|---|
| `table_name` | yes | The target table to replicate -- also the diffgen `tablename` filter value. |
| `pk_column` | yes | The target table's primary key column. |
| `mode` | no (default `full`) | `full` repulls inserts/updates fresh and tombstones deletes. `tombstones_only` emits deletes only -- for a table that already has a separate, conventional incremental tap and just needs the DELETE gap filled. |

```json
{
  "sql_dialect": "postgres",
  "host": "db.example.com",
  "port": 5432,
  "user": "diffgen_reader",
  "password": "...",
  "database": "app",
  "tables": [
    { "table_name": "invoices", "pk_column": "id", "mode": "full" },
    { "table_name": "orders", "pk_column": "id", "mode": "tombstones_only" }
  ]
}
```

The stream's JSON Schema is derived by introspecting the target table's own columns (`information_schema.columns`), plus `is_deleted` (boolean) and `_sdc_diffgen_ts_created` (date-time) -- never anything from the diffgen table itself (`op`, `tablename`, ...).

### Configuration via environment variables

Copy `.env.example` to `.env` and fill in the real values (never committed). Meltano convention: `TAP_DIFFGEN_<SETTING_NAME>` in uppercase, e.g. `TAP_DIFFGEN_PASSWORD`.

The full list of settings is available via:

```bash
tap-diffgen --about
```

## Usage

### Direct CLI (without Meltano)

```bash
tap-diffgen --config config.json --discover > catalog.json
tap-diffgen --config config.json --catalog catalog.json --state state.json
```

### Via Meltano (recommended)

```bash
# Install the Meltano CLI (if not already done)
pipx install meltano

# Install the plugins declared in meltano.yml
meltano install

# Check the config
meltano config tap-diffgen list
meltano config tap-diffgen test

# Run the pipeline
meltano run tap-diffgen target-jsonl
```

Meltano automatically manages state (bookmarks) between runs via its own system database -- no need to manually handle a `state.json` file.

## Development

### Tests

Unit tests (fake connector, no database) run standalone:

```bash
uv run pytest tests/test_client.py tests/test_tap.py tests/test_connectors_base.py
```

Connector and end-to-end tests need real Postgres/MySQL instances -- they skip cleanly (not a failure) if none is reachable:

```bash
docker compose -f tests/fixtures/docker-compose.yml up --wait
uv run pytest
docker compose -f tests/fixtures/docker-compose.yml down -v
```

### Notable implementation details

- **Bookmark**: the replication cursor is diffgen's own `id`, which never appears on an emitted record (records mirror the target table's columns, not diffgen's) -- so the SDK's usual per-record auto-advance is neutralized (`DiffgenStream._increment_stream_state` is a no-op) and the bookmark is instead advanced manually, once per processed page, to that page's max diffgen `id`. Batches are always committed in ascending `id` order, so an interrupted run's last-committed bookmark is always a safe resume point.
- **Dedup**: within one page, the same row can appear more than once in diffgen (e.g. two updates, or an insert followed by a delete) -- only the latest event per `table_id` drives a repull/tombstone.
- **Decimal precision**: `NUMERIC`/`DECIMAL` columns are mapped to a JSON Schema `multipleOf` derived from the column's scale, the same convention [target-s3](https://github.com/plinxore/target-s3) uses for its Parquet `decimal128` mapping -- a value introspected here and later written to Parquet round-trips exact, not through a `float64` approximation.
- **MySQL `BOOLEAN`**: stored as `TINYINT(1)`, indistinguishable from a genuine small integer by `data_type` alone -- schema introspection checks `column_type` too, to avoid misclassifying every boolean column as an integer.
- **Read-only connections use autocommit**: found the hard way, via a real end-to-end test against a real, long-lived Postgres instance -- without it, every `SELECT` left an open transaction that could block DDL on the source database indefinitely.

See the code in [tap_diffgen/client.py](tap_diffgen/client.py) (stream logic) and [tap_diffgen/connectors/](tap_diffgen/connectors/) (Postgres/MySQL implementations) for details.

### SDK Dev Guide

See the [Meltano Singer SDK dev guide](https://sdk.meltano.com/en/latest/dev_guide.html) for more information.
