Metadata-Version: 2.4
Name: isitright
Version: 0.2.0
Summary: Evidence-first stress testing for text-to-SQL evaluations
Project-URL: Repository, https://github.com/isitright-ai/isitright
Author: isitright contributors
License: MIT License
        
        Copyright (c) 2026 isitright contributors
        
        Permission is hereby granted, free of charge, to any person obtaining a copy
        of this software and associated documentation files (the "Software"), to deal
        in the Software without restriction, including without limitation the rights
        to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
        copies of the Software, and to permit persons to whom the Software is
        furnished to do so, subject to the following conditions:
        
        The above copyright notice and this permission notice shall be included in all
        copies or substantial portions of the Software.
        
        THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
        IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
        FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
        AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
        LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
        OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
        SOFTWARE.
        
License-File: LICENSE
Classifier: Development Status :: 4 - Beta
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Requires-Python: >=3.11
Requires-Dist: jsonschema<5,>=4.23
Requires-Dist: pydantic<3,>=2.10
Requires-Dist: pyyaml<7,>=6.0.2
Requires-Dist: sqlglot<29,>=27
Requires-Dist: typer<1,>=0.15
Provides-Extra: all
Requires-Dist: duckdb<2,>=1.5; extra == 'all'
Requires-Dist: embedded-postgres<19,>=18.6.3; extra == 'all'
Requires-Dist: psycopg[binary]<4,>=3.2; extra == 'all'
Provides-Extra: dev
Requires-Dist: build<2,>=1.2.2; extra == 'dev'
Requires-Dist: duckdb<2,>=1.5; extra == 'dev'
Requires-Dist: embedded-postgres<19,>=18.6.3; extra == 'dev'
Requires-Dist: hypothesis<7,>=6.115; extra == 'dev'
Requires-Dist: mypy<2,>=1.14; extra == 'dev'
Requires-Dist: psycopg[binary]<4,>=3.2; extra == 'dev'
Requires-Dist: pytest-cov<8,>=6; extra == 'dev'
Requires-Dist: pytest<9,>=8.3; extra == 'dev'
Requires-Dist: ruff<1,>=0.9; extra == 'dev'
Requires-Dist: twine<7,>=6.1; extra == 'dev'
Provides-Extra: duckdb
Requires-Dist: duckdb<2,>=1.5; extra == 'duckdb'
Provides-Extra: postgres
Requires-Dist: embedded-postgres<19,>=18.6.3; extra == 'postgres'
Requires-Dist: psycopg[binary]<4,>=3.2; extra == 'postgres'
Description-Content-Type: text/markdown

# isitright

`isitright` stress-tests two SQL `SELECT` queries by searching for database states
where their results differ. When it finds one, it minimizes the data and writes a
replayable counterexample for human review.

| You have | Use | Engine |
|---|---|---|
| A SQLite database file (with or without rows) | `isitright check` / `isitright suite` | SQLite |
| Only PostgreSQL schema DDL, no data | `isitright postgres check` / `suite` | Disposable local PostgreSQL (default) |
| PostgreSQL DDL, emulated instead of native | `isitright postgres ... --engine duckdb` | DuckDB, labeled `NATIVE_UNVERIFIED` |

SQLite is included in the base install. The PostgreSQL and DuckDB paths are optional
extras that never touch a real database and use only generated synthetic data.

A disagreement is evidence, not a verdict about which query is correct. Likewise,
`AGREEMENT_ON_TESTED_STATES` means only that the queries agreed on the states tested
within the configured search budget.

## Quick start

The repository includes toy examples. From a source checkout (see
[Installation](#installation)), build the sample SQLite database and compare two ways
of totalling what is still owed:

```console
python examples/sqlite/create_database.py example.sqlite
isitright check --database example.sqlite --reference examples/sqlite/reference.sql --candidate examples/sqlite/candidate.sql --constraints examples/sqlite/constraints.yaml --output-dir artifacts/quick-start
```

The two queries agree on the three sample rows, but `isitright` finds a small state
(one amount missing) where they differ, and writes `case_report.md`,
`case_report.json`, and a replayable `counterexample.sql` to the output directory.
Add `--schema-only` when creating the database to see the same result without any
starting rows. Run all three example cases as a suite:

```console
isitright suite --database example.sqlite --cases examples/sqlite/cases.jsonl --constraints examples/sqlite/constraints.yaml --output-dir artifacts/quick-start-suite
```

This reports two counterexamples and one agreement. The PostgreSQL examples are
described under [Check PostgreSQL queries](#check-postgresql-queries).

## Installation

Python 3.11 or newer is required.

Install a released package with:

```console
python -m pip install isitright
isitright --version
```

Optional extras add the PostgreSQL and DuckDB engines. Install only what you need:

```console
python -m pip install "isitright[postgres]"  # SQLite and native PostgreSQL
python -m pip install "isitright[duckdb]"    # SQLite and DuckDB emulation
python -m pip install "isitright[all]"       # all of the above
```

From a source checkout (for development):

```console
uv sync --locked --extra dev
uv run isitright --version
```

The command name must come before its options:

```console
isitright check --help
isitright suite --help
```

## Check PostgreSQL queries

The PostgreSQL path accepts schema DDL instead of an existing database, so no data
is needed. It starts a disposable local PostgreSQL server (installed with the
`postgres` extra), generates small synthetic states from the schema, and runs both
queries on each. It requires no credentials and never reads production data. From the
source checkout, use the included example files:

```console
isitright postgres check --schema examples/postgres/schema.sql \
  --reference examples/postgres/reference.sql --candidate examples/postgres/candidate.sql \
  --output-dir artifacts/postgres-example
```

In PowerShell, use backticks rather than backslashes for line continuation, or put
the command on one line. Output directories must not already exist. To store results
outside the repository, pass an absolute path with `--output-dir`.

Replay a recorded counterexample on the engine named in its report:

```console
isitright postgres replay --report artifacts/postgres-example/case_report.json
```

For a suite, use the same JSONL case format as SQLite:

```console
isitright postgres suite --schema examples/postgres/schema.sql \
  --cases examples/postgres/cases.jsonl --output-dir artifacts/postgres-suite
isitright postgres replay --report artifacts/postgres-suite/cases/0001/case_report.json
```

The Python API mirrors the CLI and returns the report as a dictionary:

```python
from pathlib import Path
from isitright import check_postgres

report = check_postgres(
    ddl=Path("examples/postgres/schema.sql").read_text(encoding="utf-8"),
    reference_sql="SELECT id FROM item WHERE amount > 100",
    candidate_sql="SELECT id FROM item WHERE amount >= 100",
    engine="postgres",  # or "duckdb" for emulation
)
print(report["status"])
```

`check_postgres_suite(ddl=..., cases=[SuiteCase(...)])` and `replay_postgres(...)`
provide the suite and replay equivalents.

For a related-table example, use `examples/postgres/multitable-schema.sql`,
`multitable-reference.sql`, and `multitable-candidate.sql` with `postgres check`.
The queries differ when a customer has no matching order.

Suite output summarizes completed cases using `C` (counterexample), `.` (agreement
on tested states), `?` (inconclusive), and `U` (unsupported). Findings are ranked
first. Supply `--constraints constraints.yaml` to validate normalized generated
states against JSON Schema, or `--ordering unordered` when row order is irrelevant.
`--fail-on` is opt-in, including for DuckDB findings.

To execute translated SQL with DuckDB instead, add `--engine duckdb`. Reports label
this mode `EMULATED` and `NATIVE_UNVERIFIED`: a DuckDB disagreement may not occur on
PostgreSQL. Native execution is labeled `NATIVE_ISOLATED` and records the engine
version. Both modes use disposable synthetic states; neither uses a source database.
The PostgreSQL report and SQL reproduction use their own versioned schema, separate
from the SQLite report. Replay uses the engine recorded in the case report.

PostgreSQL support covers multiple tables with single-column integer, text,
or UUID keys, simple foreign keys, and common scalar types. Queries can use
read-only non-recursive CTEs, subqueries, selected window functions, set operations,
and INNER/LEFT joins. Cyclic and composite keys and other features are not yet
supported. See [supported SQL](docs/supported-sql.md) for the exact subset; unsupported
syntax and constraints are rejected, never silently weakened. Search
generates a small bounded set of boundary, NULL, duplicate, aggregation and related
join states, and minimizes by deleting rows while preserving foreign keys and any
supplied domain rules. Multi-table generation is heuristic and does not explore all
relationships or combinations. There is no automatic native cross-check of emulated
findings. Results use positional, type-aware comparison and preserve duplicates;
the default ordering follows the reference query's top-level `ORDER BY`. It is not
a general sandbox for arbitrary SQL or a compatibility guarantee for PostgreSQL.
The local PostgreSQL instance uses temporary data and a loopback-only connection;
the bundled server uses local trust authentication during the run.

## Why test more than the current rows?

Two queries can agree on today's database by accident. For example:

```sql
-- Sum only per-record shortfalls whose two operands are known.
SELECT SUM(amount_owed - amount_collected) FROM tribute;

-- Sum each column independently, then subtract the totals.
SELECT SUM(amount_owed) - SUM(amount_collected) FROM tribute;
```

These agree while both values are present in every row. They can diverge when one
amount is NULL. `isitright` searches for small states like that and gives you evidence
to review rather than inferring equivalence from one database snapshot.

## Check one query pair

Save the queries as UTF-8 text files, for example `reference.sql` and
`candidate.sql`, then run:

```console
isitright check --database example.sqlite --reference reference.sql --candidate candidate.sql --output-dir artifacts/check
```

This first compares the queries on the supplied database. If they agree, `isitright`
runs a bounded search using boundary, NULL, duplicate, join, filtering, and
aggregation mutations. The source database is opened read-only; all candidate states
are created on isolated in-memory copies.

If the database has rows, new rows are derived from the existing ones. If every
table is empty, the database is treated as a schema: starting states are synthesized
from it, honoring column defaults, `CHECK` and JSON Schema constraints, and
`VARCHAR(n)` / `DECIMAL(p,s)` limits. Schemas with features the generator does not
handle, such as composite keys, use a simpler built-in synthesis.

### Ignore row order

Use `--ordering unordered` when row order is not part of the expected answer:

```console
isitright check --database example.sqlite --reference reference.sql --candidate candidate.sql --ordering unordered --output-dir artifacts/check
```

The ordering choices are:

- `auto` (default): require row order only when the reference query has a top-level
  `ORDER BY`. An `ORDER BY` only in the candidate does not make order significant.
- `ordered`: always compare rows as a sequence.
- `unordered`: always ignore row order.

Unordered comparison is a **multiset** comparison, not a set comparison. It ignores
position but preserves duplicate multiplicity. For example, `[A, A, B]` does not
equal `[A, B]`.

### Control column and numeric comparison

The most useful comparison flags are:

| Flag | Default | Meaning |
|---|---|---|
| `--column-policy positional` | yes | Compare values by column position; aliases may differ. |
| `--column-policy by_name` | no | Match uniquely named result columns by alias, regardless of position. |
| `--column-policy names_and_positions` | no | Require both names and positions to match. |
| `--float-mode isclose` | yes | Compare floats using relative and absolute tolerances. |
| `--float-mode exact` | no | Require exact floating-point equality. |
| `--float-rtol NUMBER` | `1e-9` | Relative tolerance for `isclose`. |
| `--float-atol NUMBER` | `1e-12` | Absolute tolerance for `isclose`. |

For example, to ignore result-column order while matching columns by their unique
aliases:

```console
isitright check --database example.sqlite --reference reference.sql --candidate candidate.sql --column-policy by_name --ordering unordered --output-dir artifacts/check
```

`by_name` requires unique result-column names on both sides. Duplicate names produce
an `INCONCLUSIVE` result rather than a guessed mapping.

## Check a suite

A suite is a UTF-8 JSONL file with one object per line. Each case requires
`case_id`, `reference_sql`, and `candidate_sql`; `question` and arbitrary metadata
are optional:

```json
{"case_id":"shipping-01","question":"How many shipments are queued?","reference_sql":"SELECT COUNT(*) FROM shipments WHERE status = 'queued'","candidate_sql":"SELECT COUNT(tracking_code) FROM shipments WHERE status = 'queued'","model":"example-agent"}
{"case_id":"shipping-02","reference_sql":"SELECT code FROM hubs ORDER BY code","candidate_sql":"SELECT code FROM hubs"}
```

Run the suite with the `suite` subcommand:

```console
isitright suite --database example.sqlite --cases cases.jsonl --ordering unordered --output-dir artifacts/suite
```

While each case runs, candidate outcomes appear as they are evaluated: `.` means
the queries agreed on that state, `C` means a difference was found, `x` means the
state was rejected, and `?` means it could not be evaluated. Candidate progress
is in input order; the completed summary is evidence-ranked:

```text
CCC................... [22/22]
Legend: . agreement on tested states  C counterexample  ? inconclusive  U unsupported
22 cases | 19 agreements | 3 counterexamples | 0 inconclusive | 0 unsupported

Review:
  C medium_006  counterexample | schema-valid | join
  C medium_017  counterexample | schema-valid | null
  C medium_020  counterexample | schema-valid | aggregation
Reports: artifacts/suite
```

The symbols describe evidence, not grading: `C` means the queries disagreed on an
admitted state, not that the candidate was necessarily wrong. Cases are displayed in
the suite's evidence-ranked order. Use `--verbose` to show every case and every written
artifact, or `--quiet` to show only totals and the report directory.

Cases run independently, so one unsupported query, timeout, or execution error does
not abort the remaining cases. The suite report ranks results in this order:

1. Counterexamples on domain-validated states.
2. Counterexamples on schema-valid-only states.
3. Inconclusive or unsupported cases.
4. Cases with agreement on all tested states.

Unknown JSONL fields such as `model` are preserved as report metadata.

## Add domain constraints

Without a constraints file, generated counterexamples are labeled
`SCHEMA_VALID_ONLY`: they satisfy the engine's integrity and foreign-key checks, but
no additional domain rules were supplied.

Use `--constraints` with a JSON Schema Draft 2020-12 YAML or JSON document to declare
allowed values, ranges, required fields, and related data rules:

```console
isitright check --database example.sqlite --reference reference.sql --candidate candidate.sql --constraints constraints.yaml --output-dir artifacts/check
```

`isitright postgres check` and `postgres suite` accept the same `--constraints` file.

The schema validates a normalized object whose keys are table names and whose values
are arrays of row objects. For example:

```yaml
$schema: https://json-schema.org/draft/2020-12/schema
type: object
required: [shipments]
properties:
  shipments:
    type: array
    items:
      type: object
      required: [shipment_id, status]
      properties:
        shipment_id: {type: integer, minimum: 1}
        status: {type: string, enum: [queued, in_transit, delivered, cancelled]}
```

An admitted generated state that satisfies both SQLite and the supplied JSON Schema
is labeled `DOMAIN_VALIDATED`. This means it satisfied the rules you supplied; it
does not guarantee every unstated real-world assumption.

More complex trusted-local validation is available through the Python API. See
[the contract reference](docs/contracts.md#domain-constraints).

## Reports and replay

The default `--format both` writes authoritative JSON and readable Markdown.
`--format json` and `--format markdown` select one representation.

A single check writes:

```text
artifacts/check/
|-- case_report.json
|-- case_report.md
`-- counterexample.sql    # only when counterexample evidence exists
```

A suite writes `suite_report.json`, `suite_report.md`, and one subdirectory per
case. Open the Markdown report first, then use JSON for automation.

Replay a counterexample with the command recorded in its report, or directly with:

```console
python -m isitright.replay --report artifacts/check/case_report.json --fixture artifacts/check/counterexample.sql
```

A successful replay prints `"reproduced": true` with both canonical query results.
Replay builds a fresh temporary SQLite database and does not modify the source.

Use a new output directory for each important run. Reusing a directory can overwrite
files or leave stale evidence from an earlier suite.

## Interpret results

Every report keeps three questions separate.

### Equivalence status

- `COUNTEREXAMPLE_FOUND`: the queries returned different results on the supplied
  database or an admitted generated state.
- `AGREEMENT_ON_TESTED_STATES`: no difference was observed within this run's bounded
  search. This is not proof of equivalence.
- `INCONCLUSIVE`: execution, resource, or comparison conditions prevented a result.
- `UNSUPPORTED`: the input is outside the accepted SQL boundary.

### State validity

- `DOMAIN_VALIDATED`: SQLite checks and supplied domain rules accepted the state.
- `SCHEMA_VALID_ONLY`: SQLite checks passed, but no domain validator established
  real-world admissibility.
- `INVALID`, `INDETERMINATE`, and `NOT_ASSESSED` identify weaker or rejected validity
  conclusions.

### Attribution

Reports for both engines say `UNDETERMINED`. A counterexample does not by itself establish a
candidate defect: the reference may be defective, the question may be ambiguous, or
the generated state may expose an unstated domain assumption.

For example, if a task asks for all recorded payments, a reference using
`SELECT DISTINCT amount FROM payments` may discard two separate payments with the
same amount. A candidate using `SELECT amount FROM payments` will disagree on a
duplicate-containing state, but the candidate may be more faithful to the question.
The report surfaces the difference without silently declaring either query wrong.

## Search and resource controls

`check` and `suite` accept the same controls. The `postgres` commands accept the
same flags except `--seed`, and default `--minimization-attempts` to `100`:

| Flag | Default | Purpose |
|---|---:|---|
| `--seed` | `0` | Make candidate ordering deterministic. |
| `--max-attempts` | `100` | Bound generated-state search per case. |
| `--minimization-attempts` | `500` | Bound counterexample minimization. |
| `--timeout` | `30` | Per-query execution deadline in seconds. |
| `--max-result-rows` | `10000` | Stop materializing an oversized result. |

For fixed inputs, configuration, package version, Python version, and SQLite version,
reports are deterministic except for timing fields.

## Exit codes and CI

Finding a counterexample does not fail the command by default. This lets a user
inspect evidence before turning it into a policy decision.

Use `--fail-on` when running in CI:

```console
isitright suite --database example.sqlite --cases cases.jsonl --fail-on domain-counterexample --output-dir artifacts/suite
```

Policies:

- `none` (default): never fail merely because a counterexample was found.
- `domain-counterexample`: exit `3` for a domain-validated counterexample.
- `any-counterexample`: exit `3` for any counterexample.

Exit code `1` means an internal failure, and `2` means invalid input or invocation.
Reports are written before an applicable `--fail-on` policy returns exit code `3`.

## Python API

The Python API accepts SQL strings directly:

```python
from isitright import CheckConfig, OrderingPolicy, check

result = check(
    database="example.sqlite",
    reference_sql="SELECT province FROM tribute ORDER BY province",
    candidate_sql="SELECT province FROM tribute",
    constraints_file="constraints.yaml",
    config=CheckConfig(
        ordering_policy=OrderingPolicy.UNORDERED,
        seed=0,
        max_attempts=100,
    ),
)

print(result.equivalence_status)
print(result.state_validity)
print(result.headline)
```

`check_suite(...)` provides the corresponding suite API. Typed result objects expose
the same fields written to the versioned JSON reports.

## Supported SQL and limitations

Both commands accept one read-only `SELECT` statement per query.

- **SQLite:** ordinary CTEs, joins, grouping, aggregates, compound queries, and
  native window expressions when SQLite and `sqlglot` can parse and execute them
  consistently.
- **PostgreSQL:** a deliberately narrower allowlisted subset, with the schema subset
  described in [supported SQL](docs/supported-sql.md).

DDL, DML, multiple statements, `PRAGMA`, attachment, transactions, and extension
loading are rejected. Dialects other than SQLite and PostgreSQL are not supported.
Search is heuristic and bounded; it does not perform complete symbolic equivalence
checking, and DuckDB emulation is not evidence about native PostgreSQL behavior.

See [Supported SQL and known limitations](docs/supported-sql.md) for the precise
surface and [the contract reference](docs/contracts.md) for stable API and report
semantics.

## Background

`isitright` follows the test-generation lineage of test-suite accuracy, XData,
VeriEQL, Polygon, SpotIt/SpotIt+, and SQLancer. Its focus is practical auditing of a
team's own execution-passing SQLite and PostgreSQL evaluations with explicit
validity tiers,
evidence-first reporting, and minimized replayable fixtures. Its heuristic search is
not stronger than complete bounded symbolic verification.

## Development

```console
uv sync --locked --extra dev
uv run ruff check .
uv run mypy src
uv run python tools/export_schemas.py --check
uv run pytest
uv build
```

See [CONTRIBUTING.md](CONTRIBUTING.md) for contribution instructions,
[SECURITY.md](SECURITY.md) for private vulnerability reporting, and
[CHANGELOG.md](CHANGELOG.md) for release history.

## License

MIT
