Metadata-Version: 2.4
Name: tlf-pgreconcile
Version: 0.1.1
Summary: Normalization-aware schema and data reconciliation for Postgres databases
Author: Pujan Pandey
License-Expression: MIT
Project-URL: Homepage, https://github.com/PujanPandey07/tlf-pgreconcile
Project-URL: Repository, https://github.com/PujanPandey07/tlf-pgreconcile
Classifier: Programming Language :: Python :: 3
Classifier: Operating System :: OS Independent
Classifier: Topic :: Database
Classifier: Intended Audience :: Developers
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: psycopg[binary]
Requires-Dist: pyyaml
Requires-Dist: tlf-core
Dynamic: license-file

# tlf-pgreconcile

**Normalization-aware schema and data reconciliation for two Postgres databases.**

`pgreconcile` compares a source and a target Postgres database — schema and
row data — and tells you not just _what_ differs, but _why_: whether a
mismatch is a real data discrepancy, or just a formatting difference
(casing, whitespace, date format) that normalizes away to the same value.

It was built to solve a specific, recurring problem: reconciling a central
server database against field devices that sync intermittently and don't
always have internet access. But it's general-purpose — useful anywhere
you have two Postgres databases that are supposed to represent the same
data and you need to know exactly where, and why, they've drifted apart.

---

## Table of contents

- [Why this exists](#why-this-exists)
- [What makes this different from other tools](#what-makes-this-different-from-other-tools)
- [Installation](#installation)
- [Quick start](#quick-start)
- [The config file](#the-config-file)
- [Walkthrough: reconciling two tables](#walkthrough-reconciling-two-tables)
- [Understanding the output](#understanding-the-output)
- [The HTML report](#the-html-report)
- [How it works internally](#how-it-works-internally)
- [Known limitations](#known-limitations)
- [Contributing](#contributing)
- [License](#license)

---

## Why this exists

This project originated from a real pattern seen in Nepal's disaster
reconstruction (RIMS) system: a central RIMS server, plus field inspection
officers each carrying an ODK-based local database. Officers work in areas
that often have no internet access, so their devices sync to the central
server _intermittently_ — sometimes hours or days later. That opportunistic
sync pattern causes two kinds of problems:

1. **Records that simply haven't synced yet** — normal, expected, temporary.
2. **Records that genuinely disagree** — a value was entered differently on
   each side, and one of them is wrong.

Existing tools don't distinguish these well. Real-time CDC tools (Debezium,
WAL-based replication) assume continuous connectivity, which doesn't hold
here. General-purpose DB-diff tools report every difference flatly, without
asking whether a "difference" is actually just a casing or whitespace
inconsistency versus a real conflict that needs a human to resolve it.

`pgreconcile` is a **periodic reconciliation/audit tool**, not a real-time
sync tool — designed to be run on a schedule (e.g. nightly) against
whatever has synced so far, and to make the signal-to-noise ratio of its
report as high as possible.

## What makes this different from other tools

|                                                       | pgreconcile     | pgCompare / generic diff tools | Debezium / CDC |
| ----------------------------------------------------- | --------------- | ------------------------------ | -------------- |
| Requires continuous connectivity                      | No              | No                             | Yes            |
| Postgres-to-Postgres only                             | Yes (by design) | Usually multi-DB               | N/A            |
| Distinguishes real mismatch vs. formatting difference | **Yes**         | No                             | N/A            |
| Column-level classification                           | **Yes**         | Row-level only (typically)     | N/A            |
| Designed for offline/opportunistic sync scenarios     | **Yes**         | No                             | No             |

The core differentiator is **normalization-aware, three-way classification**
at the column level:

- `same` — values match exactly
- `same_after_normalization` — values differ raw, but match after applying
  a normalizer (casing, whitespace, digit format, date format, etc.)
- `value_mismatch` — values differ even after normalization; a genuine
  conflict

This means a report tells you "1 real mismatch, 1 same after
normalization" instead of just "2 differences" — so you know where to
actually spend your attention.

## Installation

Requires Python 3.10+ and access to both Postgres databases you want to
compare.

### From PyPI (recommended)

```bash
pip install tlf-pgreconcile
```

This installs the `pgreconcile` command, plus its dependencies:
`psycopg[binary]`, `pyyaml`, and [`tlf-core`](https://github.com/PujanPandey07/tlf-project)
(shared field/value normalizers).

Verify it installed correctly:

```bash
pgreconcile --help
```

### From source (for development)

```bash
git clone https://github.com/PujanPandey07/tlf-pgreconcile.git
cd tlf-pgreconcile
python -m venv tlfenv
# Windows (PowerShell):
tlfenv\Scripts\Activate.ps1
# macOS/Linux:
source tlfenv/bin/activate

pip install -e .
```

The editable install (`-e .`) means changes to the source are picked up
immediately without reinstalling — useful if you're contributing or
customizing the tool.

## Quick start

`pgreconcile` has three subcommands.

### 1. Generate a starter config

```bash
pgreconcile init --output config/my_config.yaml
```

This writes a template YAML file with placeholder connection details and
a placeholder table mapping — edit it to match your real databases (see
[The config file](#the-config-file) below).

### 2. Validate your config

Before running a real comparison (which opens live DB connections), sanity
check the config file itself:

```bash
pgreconcile validate --config config/my_config.yaml
```

This checks the YAML is well-formed and that every table mapping has the
required fields — without connecting to either database.

### 3. Run the reconciliation

```bash
pgreconcile run --config config/my_config.yaml
```

This connects to both databases, diffs the schema, diffs the data
table-by-table, and prints a full report to the terminal.

To also save a shareable, styled HTML report:

```bash
pgreconcile run --config config/my_config.yaml --html-output report.html
```

`--html-output` is optional and additive — it never replaces the terminal
report, and a failure to write the HTML file won't fail a reconciliation
that otherwise succeeded.

## The config file

A config file has two top-level sections: `databases` and `tables`.

```yaml
databases:
  source:
    host: localhost
    port: 5434
    dbname: sourcedb
    user: postgres
    password: changeme
  target:
    host: localhost
    port: 5433
    dbname: targetdb
    user: postgres
    password: changeme

tables:
  - source_table: inspections
    target_table: inspection_reports
    key_columns: [officer_name]
    columns: [officer_name, damage_severity]
    column_settings:
      damage_severity:
        normalizer: normalize_casing
        severity: medium
```

### `databases`

Standard Postgres connection details for `source` and `target`. Each side
can point at a different host/port/database — they don't need to be on
the same server.

### `tables`

A list, so **one config file can describe multiple table-pair comparisons**
in a single run.

| Field             | Required | Meaning                                                                                  |
| ----------------- | -------- | ---------------------------------------------------------------------------------------- |
| `source_table`    | Yes      | Table name in the source database                                                        |
| `target_table`    | Yes      | Table name in the target database (can differ from `source_table`)                       |
| `key_columns`     | Yes      | Column(s) that uniquely identify a row **across both databases**. See warning below.     |
| `columns`         | Yes      | Which columns to compare (usually the key columns plus the value columns you care about) |
| `column_settings` | No       | Per-column overrides: a `normalizer` to apply before comparing, and a `severity` label   |

#### ⚠️ Choosing `key_columns` correctly

This is the single most important thing to get right, and the most common
way to get a misleading report:

- **Never use an auto-incrementing ID as a key column.** Independent
  auto-increment counters on two separate databases can produce the same
  ID for two completely unrelated rows. Use a column that's genuinely
  the same identity on both sides — a natural key like a name, a UUID
  generated at creation time, or a composite of several columns.
- **Never put a column you're actually comparing into `key_columns`.**
  If `damage_severity` is a value you want to check for mismatches, it
  must not also be part of how you identify the row — otherwise a real
  value mismatch just looks like "this row only exists in source" and
  "this row only exists in target" as two separate ghost rows, instead of
  one row with a mismatched column.

`key_columns` can be a single column or a list (composite key) — either
way, at least one key column is required; an empty key list is rejected
rather than silently guessing.

#### Available normalizers

Provided via `tlf-core` and referenced by name in `column_settings`:

- `normalize_casing` — case-insensitive comparison
- `normalize_devanagari_digits` — treats Devanagari and Arabic numerals as equivalent
- `normalize_date` — compares dates regardless of format/separator

## Walkthrough: reconciling two tables

Given a `source.inspections` table and a `target.inspection_reports` table
with these rows:

| officer_name | damage_severity (source) | damage_severity (target) |
| ------------ | ------------------------ | ------------------------ |
| Raju         | Minor                    | Minor                    |
| Sita         | Minor                    | Moderate                 |
| Kiran        | Severe                   | SEVERE&nbsp;&nbsp;       |
| Hari         | Minor                    | _(missing)_              |
| Gita         | _(missing)_              | Moderate                 |

Running:

```bash
pgreconcile run --config config/example_config.yaml
```

Produces:

```
============================================================
SCHEMA COMPARISON
============================================================
Tables only in SOURCE: inspections
Tables only in TARGET: inspection_reports
Comparing: inspections (source) vs inspection_reports (target)
  [MISSING] Hari — exists in source only
  [MISSING] Gita — exists in target only
  [MISMATCH] Sita
      damage_severity: "Minor" (source) vs "Moderate" (target)
  [SAME AFTER NORMALIZATION] Kiran
      damage_severity: "Severe" (source) vs "SEVERE  " (target)
Summary: 4 rows compared, 1 real mismatch(es), 1 same after normalization, 2 missing
--- Column-level differences: inspections vs inspection_reports ---
KEY             COLUMN               SOURCE VALUE         TARGET VALUE         STATUS
--------------------------------------------------------------------------------------------------------
Sita            damage_severity      Minor                Moderate             REAL MISMATCH
Kiran           damage_severity      Severe               SEVERE               SAME (normalized)
```

Raju doesn't appear in the mismatch table at all — it's an exact match on
every compared column, which is exactly the point: the report surfaces
what needs attention, not everything that was checked.

## Understanding the output

Every row lands in exactly one of these categories:

| Status                                      | Meaning                                                                                                                                                                                                                                                                       |
| ------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| _(not shown)_                               | Row and all compared columns match exactly — no action needed                                                                                                                                                                                                                 |
| `SAME AFTER NORMALIZATION`                  | Values differ raw, but are equivalent once normalized (e.g. `Severe` vs `SEVERE  `) — usually just a formatting inconsistency, not a data problem                                                                                                                             |
| `MISMATCH`                                  | Values genuinely disagree even after normalization — needs a human to decide which value is correct                                                                                                                                                                           |
| `MISSING` (only in source / only in target) | The row hasn't synced to the other side yet, or was deleted on one side. **Not automatically an error** — in an opportunistic-sync scenario, this is expected for recently-created records. It only becomes a concern if the same record stays unsynced across repeated runs. |

## The HTML report

`--html-output report.html` produces a single self-contained HTML file
(no external assets, no server needed) with:

- Summary counts across all tables at a glance
- One collapsible section per table
- A sortable mismatch table (click any column header to sort)
- Color-coded rows by classification

Useful for sharing a reconciliation run with someone who doesn't have
terminal access, or for keeping a dated archive of past runs.

## How it works internally

1. **`introspect.py`** reads both databases' schemas via `information_schema`
   and `pg_catalog`.
2. **`schema_diff.py`** compares the two schemas — table and column names
   are matched exactly, falling back to a basic casing/whitespace-only
   normalization when an exact match isn't found (see
   [Known limitations](#known-limitations) for what this does and doesn't
   catch).
3. **`data_diff.py`** fetches rows from both tables by the configured
   `key_columns`, and does a column-by-column comparison (not a whole-row
   hash), applying any configured normalizers before classifying each
   difference.
4. **`reconcile.py`** orchestrates this across every table pair in the
   config, using one connection per database, reused across all tables.
5. **`reports/`** builds the terminal report (`printer.py`) and, if
   requested, the HTML report (`html_renderer.py`) from the same
   underlying comparison results.

`row_hash.py` also computes Merkle-style table/database-level hashes,
intended as a future fast-path to skip unchanged tables entirely — this
is computed today but not yet wired into the actual comparison flow.

## Known limitations

Being upfront about what this tool doesn't do (yet):

- **No fuzzy schema/column name matching.** Table and column names are
  matched exactly, with one deliberately narrow exception: casing and
  surrounding-whitespace differences (`Inspections` vs `inspections`, or
  `" damage_severity "` vs `"damage_severity"`) are recognized
  automatically as the same table/column, the same way value-level
  normalization already works for row data. A genuinely different name —
  e.g. `inspections` vs `inspection_reports` — is **not** matched; it's
  still reported as a real schema difference, since guessing at a rename
  is a much riskier failure mode than just flagging it for a human to
  confirm. Full fuzzy/similarity-based matching (rapidfuzz-style) was
  considered and intentionally deprioritized: an incorrect fuzzy match on
  a data-integrity tool is worse than no match at all, so it needs its
  own careful design pass rather than a quick addition.
- **Append-style sync is assumed.** The tool doesn't yet distinguish
  "a record was edited after its first sync" from "a record was never
  synced" — it currently assumes field devices only append new records
  rather than editing previously-submitted ones.
- **No drift-over-time tracking yet.** Each run is a snapshot; the tool
  doesn't currently remember previous runs, so it can't yet tell you
  whether a `MISSING` row is brand new or has been stuck for a week.
- **Postgres-to-Postgres only**, by design — not a general multi-database
  tool.

## Contributing

Issues and PRs welcome. If you're proposing a new normalizer or comparison
strategy, a quick note on the motivating use case helps a lot — this
project grew out of one very specific real-world scenario, and it's most
useful when new features are grounded in an actual reconciliation problem
rather than a hypothetical one.

## License

MIT — see [LICENSE](LICENSE).
