Was that the right price—on the order date?
An invoice can match today’s catalog and still differ from the price that applied when the order was placed. “Was that the right price?” is not just a lookup. It is a question about which rule applied at a particular time.
The published description of jmkty legon’s invoice-checking demo connects that question to structured data: compare a billed price with a catalog rate valid on the order date. That is the inspiration here. We reviewed the description, not a transcript, and are not reproducing its architecture or validating its advertised speed.
Below is an independent, fictional Postgres example. Invoice lines are already stored as structured records. Bufflehead supplies a read-only connection to that database; this workflow does not extract PDFs, federate the video’s data sources, or automatically dispute an invoice.
Agree on what “right” means first
For this example, the rule is: compare the billed unit price against the catalog price for the same SKU, currency, and unit of measure on the order date. Prices exclude tax, freight, and discounts. There are no customer-specific contracts or quantity tiers.
Those are assumptions, not universal invoicing rules. A real agreement might use shipment date, a negotiated quote, or a customer price list instead. Establish the applicable policy before asking an AI tool to write SQL. A catalog difference is a review finding, not proof of an overcharge.
We use date-only periods with an inclusive start and exclusive end: valid_from <= order_date < valid_to. A null end means no recorded end date. A price ending September 1 does not also apply on September 1. If your data uses timestamps, define the business time zone and boundary instant explicitly instead of casting timestamps to dates without review.
A small fixture with useful failure cases
An administrator can run this setup in a fresh, disposable Postgres database using an ordinary SQL client, not through Bufflehead. It creates fictional records in a new schema. For your own data, map the query to existing, approved tables instead.
CREATE SCHEMA price_demo;
CREATE TABLE price_demo.invoice_lines (
line_id integer PRIMARY KEY,
sku text NOT NULL,
order_date date NOT NULL,
currency text NOT NULL,
uom text NOT NULL,
billed_unit_price numeric(12,2)
);
CREATE TABLE price_demo.catalog_prices (
price_id integer PRIMARY KEY,
sku text NOT NULL,
currency text NOT NULL,
uom text NOT NULL,
valid_from date NOT NULL,
valid_to date,
unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
CHECK (valid_to IS NULL OR valid_to > valid_from)
);
INSERT INTO price_demo.invoice_lines VALUES
(101, 'WIDGET', '2026-08-15', 'USD', 'each', 48.00),
(102, 'WIDGET', '2026-09-01', 'USD', 'each', 48.00),
(103, 'GAP', '2026-08-15', 'USD', 'each', 12.00),
(104, 'OVERLAP', '2026-08-15', 'USD', 'each', 22.00),
(105, 'WIDGET', '2026-08-15', 'EUR', 'each', 48.00),
(106, 'WIDGET', '2026-08-15', 'USD', 'case', 48.00),
(107, 'WIDGET', '2026-08-15', 'USD', 'each', NULL);
INSERT INTO price_demo.catalog_prices VALUES
(1, 'WIDGET', 'USD', 'each', '2026-01-01', '2026-09-01', 40.00),
(2, 'WIDGET', 'USD', 'each', '2026-09-01', NULL, 48.00),
(3, 'GAP', 'USD', 'each', '2026-01-01', '2026-08-01', 12.00),
(4, 'OVERLAP', 'USD', 'each', '2026-01-01', NULL, 20.00),
(5, 'OVERLAP', 'USD', 'each', '2026-08-01', NULL, 22.00);The table-level checks reject reversed or empty periods, but intentionally allow overlapping rows so the query can demonstrate detecting them. In an operational catalog, administrators should also prevent invalid overlaps at ingestion or with an appropriate database constraint. This fixture is not a complete production schema.
Count compatible prices before comparing money
Connect Bufflehead to the demo database with a role permitted to read these two tables. Keep setup and grants outside the read-only workflow. The query below is a single read-only statement that can also be inspected in Bufflehead’s SQL editor.
WITH matched AS (
SELECT i.*, p.match_count, p.candidate_price, p.price_evidence
FROM price_demo.invoice_lines AS i
LEFT JOIN LATERAL (
SELECT COUNT(*) AS match_count,
MAX(c.unit_price) AS candidate_price,
jsonb_agg(
jsonb_build_object(
'price_id', c.price_id,
'valid_from', c.valid_from,
'valid_to', c.valid_to,
'unit_price', c.unit_price
) ORDER BY c.price_id
) AS price_evidence
FROM price_demo.catalog_prices AS c
WHERE c.sku = i.sku
AND c.currency = i.currency
AND c.uom = i.uom
AND c.valid_from <= i.order_date
AND (c.valid_to IS NULL OR i.order_date < c.valid_to)
) AS p ON TRUE
)
SELECT line_id, sku, order_date, currency, uom,
billed_unit_price, match_count,
CASE WHEN match_count = 1 THEN candidate_price END
AS expected_unit_price,
CASE WHEN match_count = 1 AND billed_unit_price IS NOT NULL
THEN billed_unit_price - candidate_price END AS unit_difference,
CASE WHEN match_count = 1 AND billed_unit_price IS NOT NULL
THEN ROUND(100 * (billed_unit_price - candidate_price)
/ NULLIF(candidate_price, 0), 2) END
AS difference_percent,
CASE
WHEN match_count = 0 THEN 'no_compatible_price'
WHEN match_count > 1 THEN 'ambiguous_price'
WHEN billed_unit_price IS NULL THEN 'missing_billed_price'
WHEN billed_unit_price = candidate_price THEN 'matches'
WHEN billed_unit_price > candidate_price THEN 'above_catalog'
ELSE 'below_catalog'
END AS review_status,
price_evidence
FROM matched
ORDER BY line_id;The correlated subquery returns a count and the supporting price rows for each invoice line. No match remains visible instead of disappearing in an inner join. MAX is only used as the expected price when there is exactly one match; it is not a rule for choosing between overlapping prices. Postgres explains this pattern in its LATERAL subquery documentation.
What the results actually establish
- 101: above_catalog. On August 15, the one compatible catalog row is price ID 1 at USD 40.00 per each. The billed USD 48.00 is USD 8.00 higher per unit, or 20%. Using the September price would incorrectly report a match for this historical comparison.
- 102: matches. September 1 belongs only to the new period. The expected and billed prices are both USD 48.00.
- 103: no_compatible_price. The only GAP price ended before the order date. The query does not substitute a stale price.
- 104: ambiguous_price. Two periods cover the order date. Both price IDs remain in the evidence, but no expected price or difference is reported—even though one candidate equals the invoice.
- 105 and 106: no_compatible_price. The catalog contains USD-per-each prices, not EUR-per-each or USD-per-case prices. The query performs no implicit currency or unit conversion.
- 107: missing_billed_price. A valid catalog rate alone is not enough to compare an absent invoice amount.
A zero catalog price can still be compared in absolute terms; its percentage difference is left null because division by zero is undefined. Monetary values use decimal numeric fields. Apply your actual rounding policy if your source prices carry more precision. This query compares unit prices, not line totals; quantity, line rounding, tax, freight, rebates, and credits need their own agreed treatment.
For a missing compatible price, inspect the SKU’s history to distinguish missing dates from currency or unit mismatches. Do not quietly add a fallback or use ORDER BY valid_from DESC LIMIT 1 to conceal overlapping records. If multiple issues coexist, the status reports the matching problem first; the output still includes the billed value and match count for review.
Give the AI a bounded question and ask for evidence
Compare invoice unit prices with the catalog valid on each order date, matching SKU, currency, and unit exactly. Require one compatible price row. Show the invoice line ID, order date, price row IDs and validity periods, and any unit-price difference. Flag missing or overlapping prices without guessing. Treat differences as review items, not confirmed billing errors.
Run this on a specific approved order or invoice in real use, with a filter on the invoice source. The fixture scans all seven rows for demonstration. Inspect the generated SQL before accepting the explanation, and retain the supporting result with the review. If catalog history can be edited retroactively, this answers against the history currently stored—not necessarily what the system knew when the order was entered.
Where Bufflehead fits
Bufflehead provides read-only access to existing Postgres data. The AI tool can help formulate a question and explain retrieved rows; the database evaluates the date and price conditions. Bufflehead does not supply the commercial policy, infer missing conversion factors, or resolve contradictory catalog history.
The useful outcome is a traceable statement: “Under this rule, this line differs from this dated catalog row.” That is a much stronger starting point for a human review than “the latest price looks right.”
Download Bufflehead to explore existing structured data, and see why read-only access and read scope are separate controls before choosing a database credential for your workflow.