SQL diagnostics for purchase_trade business rules

These scripts are read-only diagnostics for a PostgreSQL test database.

They exist to support the same business rules enforced by Python guards. The expected workflow is:

1. write the consultant/developer rule in the thematic documentation;
2. enforce the invariant in the application code when feasible;
3. provide a read-only SQL diagnostic to audit existing data.

quantity_consistency_checks.sql

Checks the two core lot quantity invariants documented in lots-and-quantities.md and lots-and-quantities.en.md.

Zero lot_qt rows are ignored completely. They are treated as legitimate memory of an open quantity consumed by a physical lot. This is required because lot_qt represents usable open forecast and stops at zero, while the virtual lot may become negative to compensate the difference between theoretical and executed quantity. A non-zero lot_qt row without both lot_p and lot_s is reported as anomalous.

When a physical lot overconsumes the selected forecast after a tolerance confirmation, lot_qt stops at zero but the virtual lot absorbs the full physical quantity. The SQL diagnostic therefore reports only open forecast lower than the positive virtual lot. Open forecast greater than the virtual lot is accepted as a consequence of this overconsumption rule.

Run it on a restored test database:

\i modules/purchase_trade/docs/business/sql/quantity_consistency_checks.sql

The script returns rows only when it finds a potential issue.

Main columns:

• check_name: invariant or diagnostic that failed.
• contract_model: purchase.purchase or sale.sale.
• contract_id: database id of the contract.
• contract_number: purchase or sale contract number.
• line_id: purchase.line or sale.line id depending on the check.
• virtual_lot_id: virtual lot involved in the inconsistency.
• observed_value: value found in the database.
• expected_value: value required by the business rule.
• diff: observed minus expected.
• detail: human-readable explanation.

Cross-category UoM rows are reported as manual-review diagnostics because the Python code may pass explicit conversion factors that cannot be inferred safely from SQL alone.

fee_quantity_consistency_checks.sql

Checks the fee quantity invariant documented in fees.md and fees.en.md.

The script ignores mode = 'ppack' because Per packing follows a packing quantity, not the lot net/gross weight.

For every other fee, it recomputes the expected quantity from the effective fee.lots:

• physical lots if at least one physical lot is linked;
• otherwise virtual lots;
• fee.qt_state when filled, capped by the contract weight basis;
• otherwise the contract weight basis;
• closest previous lot.qt.hist state by lot.qt.type.sequence when the exact state is missing;
• lot.qt.hist.quantity for net fees and lot.qt.hist.gross_quantity for gross fees.

The diagnostic is grouped by fee_id. Purchase and sale columns are displayed only to help identify related contracts; they do not split the expected quantity. This matters for purchase fees whose linked lots may also be matched to several sale lines.

Run it on a restored test database:

\i modules/purchase_trade/docs/business/sql/fee_quantity_consistency_checks.sql

The script returns rows only when it finds a potential issue.

Main columns:

• check_name: failed invariant or manual-review diagnostic.
• fee_id: fee.fee id.
• purchase_id / purchase_number: related purchase contract when available.
• sale_id / sale_number: related sale contract when available.
• observed_value: current fee.quantity.
• expected_value: recomputed quantity from effective lots.
• diff: observed minus expected.
• detail: human-readable explanation.

Rows reported as fee_quantity_uom_manual_review need manual analysis because SQL cannot safely infer cross-category conversion factors.