Relational contract calibration

The paid report for two paid seven-cent orders and two customers without paid orders is (1,2,14), (2,0,0), (3,0,0). COUNT(*) on an unmatched LEFT JOIN row gives one, while COUNT(order_id) gives zero. SUM(DISTINCT cents) destroys legitimate multiplicity. Filtering paid status in WHERE removes unmatched rows.

A guest order makes NOT IN unsafe for this nullable subquery because the nonmatching comparison can be unknown. Correlated NOT EXISTS asks whether a matching row exists. A name is not the customer key. ROW_NUMBER with amount descending and ID ascending selects one deterministic winner; RANK on amount alone can select several tied winners.

The schema must reject null amount independently of the nonnegative check. SQLite foreign-key enforcement is enabled and checked per connection before the transaction. A nullable customer reference deliberately permits guests. The stored-value type check is SQLite-specific; it is not a portable input-validation policy.

For either injected failure, only previously committed order 90 and its audit remain. For success, order 91 and its audit both appear. The connection is idle afterward. An already-active transaction is rejected before any change and stays active. Constraint exceptions also trigger rollback. Returning success before COMMIT or committing the partial operation in the exception handler fails SQL5.

A weak answer says “SQL is ACID so retries are safe.” A passing answer identifies the exact boundary and preserved rows. A strong answer constructs a lost-response case, proposes a durable request key and names the atomicity/concurrency checks still needed. None of the single-connection tests proves PostgreSQL plans, isolation under concurrent writers or hardware durability.

Repair using a different fixture after 48 hours. Do not merely memorize the three-customer example. Preserve any assistance; generated output must not be presented as an observed database result.