confidence-and-manual-review
The dualdb confidence rubric and the triggers that force manual review. Use when scoring a finding, when deciding whether a conversion may be applied automatically, when writing a MANUAL_REVIEW.md or
SKILL.mdskills/confidence-and-manual-review/SKILL.md
GitHubConfidence and manual review
When to use
Scoring a finding; deciding add-pg-alternate versus manual-review; writing a
review or risk entry; judging whether the engine is being over-confident.
Decision procedure
- Classify the finding into one of the three failure classes below.
- Score it with the rubric under Scoring.
- Apply the escalation triggers - any one of them forces
manual-reviewwhatever the score says. - Write the entry:
MANUAL_REVIEW.mdfor an unresolved item,BEHAVIOUR_RISKS.mdfor every class-3 hit, both if it is both.
The three failure classes
Keeping these separate is what makes the tool honest.
- Compile break - the code will not build. Loud, cheap to find, easy to trust.
- Runtime error - builds, throws at run time. Bad SQL, a missing function, a
wrong parameter type, an
InvalidCastExceptionon a reader. - Silent behaviour change - builds, runs, returns different data. The dangerous class, and the reason this tool is worth building.
Class 3 is never auto-converted-and-forgotten. Every class-3 hit gets a
BEHAVIOUR_RISKS.md entry even when the code change itself is confidently
correct.
Scoring
score = base(rule.tier)
x schema_penalty (1.0 known / 0.6 inferred / 0.0 unknown-and-required)
x resolution_penalty (fully-resolved 1.0 / partially dynamic 0.5 / opaque 0.0)
x context_penalty (test coverage present, single call site, etc.)
score < --confidence-threshold (default 0.85) -> manual-review.
Class-3 hits cap the score at the threshold regardless of the rest, so they can never auto-pass silently. A zero in any factor is a zero overall - an opaque statement or a required-but-absent schema fact is not something the other factors can compensate for.
The breakdown is stored per change unit, not just the product, so a low score can be explained rather than merely reported.
Escalate when
Any one of these forces manual-review, whatever the score says:
- an unresolved dynamic fragment (
PG-DYN-004) - any rule with
noEquivalent: true- and its two to three options must be carried into the entry (guardrail R9) - a rule with
requiresSchema: trueand no schema hints supplied - never infer schema - a
behaviour-riskhit the classification cannot resolve from the code alone (the twoNOLOCKs that look identical and are not) - a
mixedunit whose data access cannot be cleanly separated from its business logic - duplicating logic is forbidden (guardrail R2) - a provider type in a public signature: converting it changes the contract, and the callers are outside the unit
- the scope guard rejected the unit under
--strict-scope
What decides schema-dependence
requiresSchema: true marks a rule whose decision needs facts the source alone
does not carry:
- column nullability - affects
COALESCEand join semantics, and NULL ordering - whether a
bitcolumn is read asboolorint(partly inferable from code) - the actual collation of a text column - case-sensitivity behaviour
- whether identifiers were created quoted
- the precision of a
decimalormoneycolumn - the existence of an index referenced by a hint
- the primary key, for
DELETE TOP (n)rewrites
--schema-hints <file> supplies these without the tool ever connecting to a
database. Re-running with hints upgrades previous manual-review items into
generated alternates - which is what turns Phase 1 into a repeatable process
rather than a one-shot.
Writing a good entry
MANUAL_REVIEW.md - one entry per unresolved item, grouped by cause with the
highest-impact group first. Each carries: what, where (file:line), why it could
not be decided, the rule ids, and - for a no-equivalent construct - two to three
options with their trade-offs stated honestly. An option list where one choice is
obviously right is not a real option list; say so and recommend it.
BEHAVIOUR_RISKS.md - short, ranked, and separate. Every competing tool
reports "conversions made"; reporting silent behaviour changes is the
differentiator and the thing a reviewer actually needs.
Write each risk as an executable test intent - endpoint or entry point, input, expected invariant - so Phase 2 can turn it into a dual-run comparison instead of re-deriving it.
Worked examples
1. Auto - a fully resolved statement with one auto rule
SELECT ISNULL(Discount, 0) FROM dbo.Orders, in a unit that already uses
DbConnection.
base(auto) 1.00 x schema 1.00 x resolution 1.00 x context 1.00 = 1.00
One hit (PG-NULL-001), no class-3 hit, nothing schema-dependent, nothing
unresolved. Above the threshold, so the rule may be applied without human
judgement - inside a generated alternate, or as a portable-rewrite if every
condition of the narrow exception holds.
2. Capped - a correct conversion that still needs a risk entry
SELECT Id FROM dbo.[Order] ORDER BY ShippedUtc, where ShippedUtc is nullable.
base(auto) 1.00 x schema 0.60 (nullability inferred) x resolution 1.00 x context 1.00
= 0.60, then capped at 0.85 by the class-3 hit -> 0.60
PG-ORD-001 is a behaviour-risk: NULLs sort first on SQL Server and last on
PostgreSQL. Emitting NULLS FIRST is mechanically correct and the entry still goes
into BEHAVIOUR_RISKS.md, because the reason the ordering changes is worth a
reviewer's attention. Below the threshold here because nullability was inferred
rather than known.
3. manual-review - a zero factor
SqlHelper.Fill(sql) where sql is a method parameter.
base(auto) 1.00 x schema 1.00 x resolution 0.00 (opaque) x context 1.00 = 0.00
A zero in any factor is a zero overall. No amount of confidence elsewhere
compensates for not knowing what the statement is. manual-review, grouped under
"generic SQL helper", with the call sites listed so the reviewer can see the scale.
Note what the tool does not do here: it does not generate a Fill_PostgreSql
that forwards the same text to Npgsql. That would compile and fail on the first
T-SQL statement handed to it.
Traps
- A high score on a class-3 hit is a bug in the scoring, not a green light.
- "It looks obviously fine" is not evidence. The two
NOLOCKexamples in the hub skill differ only in what the caller depends on. - A run dominated by
portable-rewritemeans the engine is over-confident. Investigate it as a defect, not as good news. - Inferring schema from naming - assuming
IsActiveis abit, or thatEmailis case-insensitive - is exactly the guess this rubric exists to prevent. - An option list with a hidden default. If the tool picked one and listed the others for form's sake, that is a silent approximation (guardrail R9).
Do not
- Do not auto-convert anything hand-labelled
manualin a corpus. That is a release blocker. - Do not let a class-3 hit pass without a risk entry.
- Do not write "review this" as an entry. Say what to check and what would make it safe.
- Do not lower the threshold to make a run look better.