The Confidence Ledger (SQL Schema)
Design a persistent ledger that records every agent decision with its claimed confidence, the evidence basis, and the eventual outcome — the substrate for calibration audits and trust scoring. Schema design, indexing, outcome-resolution lag handling, and the SQL query that answers 'is this agent's 80% actually 80%?'
The confidence scoring lesson introduced the confidence ledger as the mechanism that makes scoring a learning system rather than a static formula. This lesson builds that mechanism in concrete detail: what the schema looks like, what to index, how to handle the lag between a decision and its verified outcome, and the exact SQL that answers the question the whole architecture exists to answer.
The question: is this agent's 80% confidence actually 80%?
An agent that claims 80% confidence should be correct approximately 80% of the time on those decisions. If it is correct 95% of the time, the confidence formula is too conservative — it is underconfident, routing decisions to human review that it could handle autonomously. If it is correct 55% of the time, the formula is dangerously overconfident — it is auto-approving outputs that should be reviewed.
The ledger is the instrument that measures this gap. Without persistent, structured storage of both the claimed confidence and the eventual outcome, the question cannot be answered at all. With it, the question becomes a SQL query.
Schema Design
The ledger has two core tables: decisions and outcomes. The relationship is one decision to many outcomes — though in practice each decision should have at most one authoritative outcome (the final verified result). The schema allows multiple outcomes per decision to accommodate partial verifications (a quick automated check early, a thorough human review later) without forcing you to overwrite the initial verdict.
The decisions table
Every agent decision that passes through the confidence scoring system writes one row to decisions. The key fields and their design rationale:
agent_id — identifies which agent produced the output. Do not conflate the model name with the agent identity. Two different deployments of the same model, with different system prompts and different task assignments, are different agents with different calibration profiles. Use a stable identifier that encodes the specific deployment configuration, not just the underlying model.
task_type — the classification of what the agent was asked to do. This is the grouping key for calibration: you cannot compute "accuracy on research synthesis tasks" if all tasks are stored under a single category. Task type classification should happen at decision time, not retroactively. A text classifier applied to the decision's prompt is sufficient; it does not need to be perfect, only consistent.
claimed_confidence — the numeric score the confidence formula produced at decision time, stored as a REAL between 0 and 100. The CHECK constraint enforces the range at the database level. Store the raw score, not the zone — zone thresholds may change, but the raw score is the permanent record of what the formula said.
evidence_sources — the signals that fed into the confidence formula, stored as a JSON array. Example: ["validator_pass", "cross_source_2_of_3", "historical_accuracy_0.87"]. This field makes calibration audits debuggable: when you find that the 90-bucket has poor empirical accuracy, you can query which evidence sources were claimed on those decisions and audit whether those sources are actually providing the signal they claim to.
zone — the routing decision. Store it even though it is derivable from claimed_confidence and the zone thresholds, because zone thresholds change. The zone stored here is the zone that was applied at decision time.
decided_at — ISO-8601 UTC timestamp. Use UTC always; converting at query time is error-prone and unnecessary.
resolution_lag_sec — NULL at decision time, filled when an outcome is recorded. The lag between decision and outcome is a calibration signal in its own right: task types with multi-day resolution lags require different bootstrapping strategies than task types with same-hour resolution.
The outcomes table
An outcome is a verified judgment on a past decision: was it correct? The key design choices:
correct — stored as BOOLEAN (INTEGER in SQLite, 0 or 1). Do not store confidence in the outcome itself; the outcome is a binary judgment. A human reviewer who says "this output was mostly right" must make a binary call: correct or not. The calibration math requires binary outcomes.
verifier — who or what verified the outcome: 'human' (a reviewer made the judgment), 'monitor' (a downstream monitoring signal detected an error), or 'downstream' (the output fed into a subsequent process that produced a verifiable result). This field is used to weight outcomes in calibration — human reviews are authoritative, monitor detections are indicative, downstream verifications are probabilistic.
resolved_at — when the verification happened, not when the decision was made. The combination of decided_at and resolved_at allows computing resolution_lag_sec and understanding the temporal structure of your calibration data.
The DDL
The full schema, runnable as-is in SQLite:
CREATE TABLE decisions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
agent_id TEXT NOT NULL,
task_type TEXT NOT NULL,
claimed_confidence REAL NOT NULL
CHECK (claimed_confidence >= 0 AND claimed_confidence <= 100),
evidence_sources TEXT NOT NULL DEFAULT '[]', -- JSON array
zone TEXT NOT NULL,
decided_at TEXT NOT NULL, -- ISO-8601 UTC
resolution_lag_sec INTEGER -- NULL until an outcome resolves
);
CREATE TABLE outcomes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
decision_id INTEGER NOT NULL REFERENCES decisions(id),
correct INTEGER NOT NULL CHECK (correct IN (0, 1)), -- BOOLEAN
verifier TEXT NOT NULL
CHECK (verifier IN ('human', 'monitor', 'downstream')),
resolved_at TEXT NOT NULL, -- ISO-8601 UTC
notes TEXT
);
The CHECK constraints enforce the claimed_confidence range, the binary outcome, and the closed verifier vocabulary at the database level — bad writes fail loudly at insert time instead of corrupting calibration math later.
Indexes
Four indexes cover the primary query patterns:
-- Calibration queries: compute per-agent, per-task-type accuracy rates
CREATE INDEX idx_decisions_agent_task ON decisions(agent_id, task_type);
-- Time-range queries: "how did this agent perform last week?"
CREATE INDEX idx_decisions_time ON decisions(decided_at DESC);
-- Zone distribution: "what fraction of auto_approve decisions were correct?"
CREATE INDEX idx_decisions_zone ON decisions(zone, decided_at);
-- Outcome join: fast lookup of outcomes for a specific decision
CREATE INDEX idx_outcomes_decision ON outcomes(decision_id);
Without idx_decisions_agent_task, the calibration query does a full table scan for every agent-task-type grouping. As the ledger grows to tens of thousands of rows per agent, this becomes slow enough to impact the trust architecture's responsiveness to drift signals.
The Calibration View
The calibration_buckets view groups decisions into 10-point confidence buckets and computes the empirical accuracy rate per bucket. It is the workhorse of the calibration audit:
CREATE VIEW calibration_buckets AS
SELECT
d.agent_id,
d.task_type,
CAST(d.claimed_confidence / 10 AS INTEGER) * 10 AS conf_bucket,
COUNT(*) AS attempts,
SUM(o.correct) AS correct_count,
ROUND(
CAST(SUM(o.correct) AS REAL) / COUNT(*),
3
) AS empirical_rate,
ROUND(
ABS(
CAST(CAST(d.claimed_confidence / 10 AS INTEGER) * 10 AS REAL) / 100.0
- CAST(SUM(o.correct) AS REAL) / COUNT(*)
),
3
) AS calibration_error
FROM decisions d
JOIN outcomes o ON o.decision_id = d.id
GROUP BY d.agent_id, d.task_type, conf_bucket;
calibration_error is the key metric: the absolute difference between the bucket's lower bound (as a fraction — the 80-bucket compares against 0.80) and the empirical accuracy rate. A perfectly calibrated agent has calibration_error = 0.0 at every bucket. Real agents have non-zero error — the goal is to minimize it over time, using the view as the feedback loop.
The confidence ledger is the instrument that reduces that fog to numbers. An agent operating with no calibration data is operating in a fog of complete uncertainty. An agent with a calibrated ledger has empirical, auditable evidence of exactly where its self-assessment is reliable and where it diverges from reality.
Calibration Audit Queries
Four queries that any calibration audit must run. They map directly to the schema.
Query 1: Is the 80-bucket actually 80%?
SELECT
agent_id,
conf_bucket,
attempts,
empirical_rate,
calibration_error
FROM calibration_buckets
WHERE agent_id = 'atlas-researcher'
AND conf_bucket = 80
AND attempts >= 30; -- minimum sample before calibration is meaningful
The WHERE clause on attempts >= 30 is important. A bucket with 3 decisions showing 100% accuracy is not evidence of calibration — it is evidence of insufficient data. Set the minimum sample threshold based on the statistical precision your system requires; 30 is a reasonable floor for preliminary evidence, 50 for production decisions.
Interpret the results:
calibration_error < 0.05— the agent is well-calibrated in this bucket. The formula's weights are working.calibration_errorbetween 0.05 and 0.10 — mild miscalibration. Monitor, but no immediate action required.calibration_error > 0.10— significant miscalibration. The confidence formula's weights need adjustment. Ifempirical_rateis much lower than the bucket midpoint, the formula is overconfident: reduce the weights on the signals it relies on. Ifempirical_rateis much higher, the formula is underconfident: increase the weights.
Query 2: Drift detection
WITH
recent AS (
SELECT d.agent_id, d.task_type,
ROUND(CAST(SUM(o.correct) AS REAL) / COUNT(*), 3) AS accuracy
FROM decisions d JOIN outcomes o ON o.decision_id = d.id
WHERE d.agent_id = 'atlas-researcher'
AND d.decided_at >= date('now', '-7 days')
GROUP BY d.agent_id, d.task_type
),
baseline AS (
SELECT d.agent_id, d.task_type,
ROUND(CAST(SUM(o.correct) AS REAL) / COUNT(*), 3) AS accuracy
FROM decisions d JOIN outcomes o ON o.decision_id = d.id
WHERE d.agent_id = 'atlas-researcher'
AND d.decided_at >= date('now', '-30 days')
AND d.decided_at < date('now', '-7 days')
GROUP BY d.agent_id, d.task_type
)
SELECT r.task_type,
r.accuracy AS this_week,
b.accuracy AS prior_30d,
r.accuracy - b.accuracy AS delta
FROM recent r JOIN baseline b USING(agent_id, task_type)
WHERE r.accuracy < b.accuracy - 0.05 -- detect drops of 5+ points
ORDER BY delta;
A drop of 5+ percentage points in a single week is the drift alert threshold. When this query returns rows, it means something changed: the model API was updated, the task distribution shifted, a dependency degraded, or the agent's operating conditions changed. This query should run nightly as an automated check, with results posted to the monitoring system.
Query 3: Resolution lag analysis
SELECT
task_type,
ROUND(AVG(resolution_lag_sec) / 3600.0, 1) AS avg_lag_hours,
ROUND(MAX(resolution_lag_sec) / 3600.0, 1) AS max_lag_hours,
COUNT(resolution_lag_sec) AS resolved_count, -- NULLs (pending) excluded
SUM(CASE WHEN resolution_lag_sec IS NULL THEN 1 ELSE 0 END) AS pending_count
FROM decisions
WHERE agent_id = 'atlas-researcher'
GROUP BY task_type
ORDER BY avg_lag_hours DESC;
This query reveals the resolution structure of your calibration data. Task types with average resolution lag of 48+ hours require a different bootstrapping strategy than task types where outcomes are known within an hour. The pending_count column shows how many decisions are still awaiting outcome — decisions that cannot contribute to calibration yet and that will eventually shift the empirical rates when they resolve.
Understanding resolution lag is critical for not over-interpreting early calibration data. If task type X shows 95% empirical accuracy with 10 resolved outcomes and 200 pending, the 95% rate is not yet meaningful evidence of calibration. The 200 pending decisions will eventually resolve; the final rate may look quite different.
Query 4: Zone audit
SELECT
zone,
COUNT(*) AS decisions,
ROUND(CAST(SUM(o.correct) AS REAL) / COUNT(*), 3) AS accuracy,
SUM(CASE WHEN o.verifier = 'human' THEN 1 ELSE 0 END) AS human_reviewed,
ROUND(
CAST(SUM(CASE WHEN o.verifier = 'human' THEN 1 ELSE 0 END) AS REAL)
/ COUNT(*),
3
) AS human_review_rate
FROM decisions d
JOIN outcomes o ON o.decision_id = d.id
WHERE d.agent_id = 'atlas-researcher'
GROUP BY zone
ORDER BY zone;
The zone audit answers a different question: are the zone thresholds correctly placed?
If human_review zone decisions have accuracy = 0.97, the Zone 2 threshold is too conservative — the agent is routing decisions to human review that it could handle autonomously at high accuracy. Lower the Zone 2 floor toward Zone 3.
If auto_approve zone decisions have accuracy = 0.81, the Zone 4 threshold is too liberal — the agent is auto-approving decisions at a rate much lower than the 91–100 score range should imply. Raise the Zone 4 floor.
The zone audit is how you calibrate the thresholds themselves, not just the formula weights.
Handling Outcome-Resolution Lag
Outcome-resolution lag is the delay between when a decision is recorded and when its outcome is known. Most calibration implementations fail here — they assume outcomes are available quickly and do not design for the structure where decisions accumulate for days before verification arrives.
The lag recording approach:
from datetime import datetime, timezone
import sqlite3
def record_outcome(
conn: sqlite3.Connection,
decision_id: int,
correct: bool,
verifier: str,
notes: str | None = None,
) -> None:
resolved_at = datetime.now(timezone.utc).isoformat()
conn.execute(
"""INSERT INTO outcomes
(decision_id, correct, verifier, resolved_at, notes)
VALUES (?, ?, ?, ?, ?)""",
(decision_id, int(correct), verifier, resolved_at, notes),
)
# Backfill resolution_lag_sec on the parent decision
conn.execute(
"""UPDATE decisions
SET resolution_lag_sec = CAST(
(julianday(?) - julianday(decided_at)) * 86400 AS INTEGER
)
WHERE id = ?""",
(resolved_at, decision_id),
)
conn.commit()
The julianday() function converts ISO timestamps to decimal days; multiplying the difference by 86400 gives seconds. The UPDATE runs atomically with the INSERT — both in the same connection transaction — so resolution_lag_sec is always populated when an outcome exists.
Calibration windows and lag-aware queries:
When computing empirical accuracy for the trust formula's historical accuracy signal, exclude decisions whose outcomes arrived very recently. A decision resolved 2 hours ago may not have received thorough verification. A decision resolved 7+ days ago has likely received authoritative verification.
-- Lag-aware accuracy computation: only use outcomes resolved at least 24h ago
SELECT
ROUND(CAST(SUM(o.correct) AS REAL) / COUNT(*), 3) AS accuracy
FROM decisions d
JOIN outcomes o ON o.decision_id = d.id
WHERE d.agent_id = 'atlas-researcher'
AND d.task_type = 'research_synthesis'
AND o.resolved_at <= datetime('now', '-24 hours') -- resolved at least 24h ago
AND d.decided_at >= date('now', '-90 days'); -- rolling 90-day window
Bootstrapping without outcome data:
Every new agent and new task type starts with an empty ledger. The calibration query returns no rows. The historical accuracy signal is unavailable. The confidence formula falls back entirely to the weaker signals.
The practical approach: start with conservative thresholds. Route all Zone 2 and Zone 3 outputs to human review during the bootstrap period. Set a target: 50 resolved outcomes per task type before enabling the historical accuracy signal. Track progress toward that target in the monitoring dashboard.
The bootstrap period is not a failure state — it is the cost of starting without evidence. The system accumulates evidence through operation. Within 2–4 weeks for most agent types, the ledger has sufficient data to activate the historical accuracy signal and begin calibrating thresholds.
Connecting the Ledger to the Trust Formula
The schema and queries are the measurement layer. The connection back to the trust formula is what makes the measurement actionable.
The historical accuracy signal in the confidence formula receives its value from the ledger:
def get_historical_accuracy(
agent_id: str,
task_type: str,
min_samples: int = 50,
) -> float | None:
"""
Query the ledger for empirical accuracy on this agent+task_type,
aggregated across ALL resolved outcomes — not a single confidence
bucket. The calibration_buckets view answers a different question
(per-bucket calibration error); this signal needs the overall rate.
Returns None if insufficient samples (formula falls back to weaker signals).
"""
row = db.execute(
"""SELECT
ROUND(CAST(SUM(o.correct) AS REAL) / COUNT(*), 3) AS empirical_rate
FROM decisions d
JOIN outcomes o ON o.decision_id = d.id
WHERE d.agent_id = ?
AND d.task_type = ?
GROUP BY d.agent_id, d.task_type
HAVING COUNT(*) >= ?""",
(agent_id, task_type, min_samples),
).fetchone()
if row is None:
return None # insufficient data; formula uses conservative defaults
return row["empirical_rate"]
Two implementation notes. First, the query aggregates over the raw decisions/outcomes join rather than selecting one row from calibration_buckets — pulling a single bucket's empirical_rate (even the most-populated bucket) would feed the formula the accuracy of one confidence band, not the agent's accuracy on the task type. Second, the row["empirical_rate"] access requires conn.row_factory = sqlite3.Row to be set on the connection; without it, use positional indexing (row[0]).
This function is called by the confidence formula at decision time. The result becomes the strongest signal in the composite score. When the ledger says an agent achieves 0.91 empirical accuracy on research synthesis tasks, that empirical rate dominates the formula — 70% weight — and the agent's outputs on that task type score high enough to reach Zone 3 (log and proceed) or Zone 4 (auto-approve) consistently.
When the drift query detects that empirical accuracy has dropped to 0.74 this week from 0.88 last month, the formula automatically responds: the historical accuracy signal drops, more outputs fall into Zone 2 (human review), the escalation frequency metric rises, and the engineering team receives a drift alert. The system is self-correcting — the ledger is the mechanism by which the correction happens.
Lesson Drill
Implement the confidence ledger for one agent in your stack.
-
Schema: Create the
decisionsandoutcomestables using the DDL above. Add the four indexes. Verify the schema with a test insert and retrieval on both tables. -
Calibration view: Implement the
calibration_bucketsview. Insert 10 test decisions at a single confidence level (e.g., 80%) with outcomes split 7 correct, 3 wrong. Verify the view returnsempirical_rate = 0.7andcalibration_error ≈ 0.1. -
Resolution lag: Record a decision, then record its outcome 5 seconds later. Verify that
resolution_lag_secis populated correctly (approximately 5). -
Calibration query: Run Query 1 (the 80-bucket audit) against your test data. What does it tell you about calibration at this bucket?
-
Drift simulation: Insert a second batch of 10 decisions at 80% confidence with outcomes split 4 correct, 6 wrong (representing a drift event). Run the drift detection query (Query 2). Does it detect the drop?
The drill produces a working ledger. The goal is not perfect calibration data — you cannot generate that in a drill. The goal is a schema that handles the edge cases correctly: NULL resolution lag before outcomes arrive, correct lag arithmetic when they do, calibration queries that handle both well-sampled and under-sampled buckets without errors.
Bottom Line
The confidence ledger is the substrate that makes the entire trust architecture auditable over time.
Schema design: decisions records every scored output with its claimed confidence and evidence sources. outcomes records verified results when they arrive, with resolution lag tracked automatically. The calibration view computes the gap between claimed and empirical accuracy per confidence bucket.
Indexing: three indexes on decisions (agent+task, time, zone) and one on outcomes (decision_id) cover the query patterns without overfitting to a single query shape.
Lag handling: record decided_at at write time, resolved_at at outcome time, and compute resolution_lag_sec as a backfill. Calibration queries exclude outcomes resolved very recently to avoid using unvetted verifications.
The key query: calibration_error = |conf_bucket/100 - empirical_rate|. Minimize it by reweighting the confidence formula signals against the evidence in the ledger.
The system only gets better if you run it. An agent with a calibrated ledger and 1,000+ resolved outcomes is operating with empirical evidence of its own reliability. An agent without a ledger is operating on the formula's theoretical properties alone. Build the ledger.