DEV Community: Mads Hansen

LIMIT 20 is not a sampling strategy

Mads Hansen — Sat, 15 Aug 2026 01:21:47 +0000

“Show me twenty examples” sounds harmless.

The generated SQL adds LIMIT 20 without a stable order.

The database returns whichever rows happen to arrive first. The assistant finds a pattern, and the team treats that pattern as evidence.

But a row limit is not a sample design.

Physical layout, indexes, query plans, parallel workers, recent inserts, and cache state can all change which rows appear.

Before examples support a conclusion, define:

the population and cutoff
authorization scope
the sampling method
stable row identity
seed and algorithm version
strata and weights
redaction
what the sample cannot prove

Use deterministic ordering for debugging. Use a stable hash sample for repeatable pseudo-random selection. Use stratification when important cohorts must be represented. Oversample rare cases deliberately—and disclose the bias.

Most importantly, separate observation from inference.

“7 of 20 sampled cases had a missing category” does not automatically mean “35% of all cases are missing a category.” Use an approved aggregate when prevalence matters.

A sampling receipt should make the population, method, seed, version, limits, freshness, source coverage, and result checksum reviewable.

LIMIT protects the system. A sampling contract protects the conclusion.

Full guide: ChatGPT database queries need a deterministic sampling contract

Three correct database queries can produce one contradictory AI answer

Mads Hansen — Sat, 15 Aug 2026 01:21:36 +0000

An AI assistant asks the database three reasonable questions:

how many incidents are open?
which services are affected?
which incidents are oldest?

While those calls run, one incident closes and another is created.

Every query can be correct on its own while the final answer contradicts itself.

The fix is not to keep a transaction open for the entire AI conversation. Model reasoning and user clarification can turn seconds into minutes.

Instead, define a short bounded data operation. Compose related reads inside one transaction, return a structured result, close the transaction, and let the model reason afterward.

Make the consistency promise explicit:

single statement
single snapshot
watermark-aligned sources
best-effort live reads
unknown

If several tool calls cannot share a snapshot, return an observation time and watermark for each result. Do not let the final prose quietly upgrade them into one point-in-time claim.

Retries matter too. After a serialization failure, deadlock, failover, or timeout, rerun the complete logical operation in a clean transaction. Never combine half of attempt one with half of attempt two.

A snapshot receipt should carry identity, scope, isolation mode, source/replica, observation boundary, filters, limits, retry attempts, completion state, and trace ID.

The conversation is not the transaction.

Full guide: MCP database answers need multi-query snapshot consistency

Zero rows is not proof that nothing exists

Mads Hansen — Fri, 14 Aug 2026 01:20:29 +0000

“No failed payments exist” is a much stronger claim than “this query returned zero rows.”

The result may be empty because:

tenant or environment scope was wrong
a partition has not arrived
one source timed out
a join removed unmatched records
authorization hid the matching rows
pagination or a timeout ended the search

So an MCP database result should distinguish:

none found in the complete authorized population
no visible matches in the caller's scope
source unavailable
incomplete search
not answerable
unknown

The final prose must preserve that state. “No visible matches” must never become “none exist.”

A negative-answer receipt should carry normalized scope, effective identity and policy, expected and observed sources, watermarks, counts before and after material joins, NULL/unmatched counts, pagination and truncation state, metric version, and trace ID.

Test the empty path deliberately: delayed sources, known hidden rows, unmatched foreign keys, timezone boundaries, stale definitions, and forced timeouts.

An empty result is data. An absence claim is a conclusion that needs evidence.

Full guide: Prove negative database answers before saying none exist

Your AI did not change the metric. Your business did.

Mads Hansen — Fri, 14 Aug 2026 01:20:28 +0000

Ask an AI assistant for MRR today and again next quarter.

The SQL can be valid both times while the answers use different definitions.

Finance may have changed which plans count, how credits are handled, when cancellation becomes effective, or which exchange rate applies.

If the assistant returns only a number and a query, that semantic change disappears inside a plausible answer.

A production metric needs an immutable version with:

population and grain
filters and exclusions
dimensions
timezone and cutoff
source systems
effective dates
implementation and policy digests

Choose the version deliberately: the definition used when the period was originally reported, today's restated definition, or an explicitly pinned version.

Before rollout, calculate old and new definitions over the same snapshot. Explain material cohort and dimension deltas. Keep historical versions reproducible.

Metric version also belongs in cache keys, scheduled reports, exports, continuation tokens, and follow-up questions. Fresh rows do not make an old definition current.

The model may explain a metric. It should not invent the business semantics.

Full guide: AI database answers need versioned metric definitions

Two safe database aggregates can reveal one person's value

Mads Hansen — Thu, 13 Aug 2026 01:21:27 +0000

An aggregate can hide every row and still reveal one person's value.

Imagine an AI assistant returns payroll total for a six-person team.

The user asks again with one employee excluded.

Subtract the two answers and you have that employee's salary.

Each query is read-only. Each answer is an aggregate. Each cohort may even pass a minimum-size rule. The disclosure exists only across the sequence.

That means aggregate privacy cannot be enforced one SQL statement at a time.

The policy layer needs to evaluate:

minimum cohort size
dimensional granularity
overlap with recent cohorts
dominance by one or two records
rare categories
query history across sessions and clients
cumulative disclosure budget

Approved aggregate operations should limit dimensions and drill-down paths. Cached answers and conversation memory must participate in the same disclosure history.

And when a result is suppressed, return a typed privacy reason—not zero. Zero is a factual claim, and the model must not infer the hidden value from adjacent cells.

Test query sequences, not isolated prompts.

Full guide: Protect ChatGPT database aggregates from differencing attacks

Read-only database access does not control where the result goes

Mads Hansen — Thu, 13 Aug 2026 01:21:26 +0000

A database connection can be read-only and still move sensitive data into places nobody approved.

The result may enter:

an AI client's context
a hosted model
application traces
an observability vendor
a cache
an export
a support screenshot

The database credential controls where data can be read. It does not define where the result may go next.

That requires a data egress contract.

For every result class, define the allowed destination, purpose, region, retention, redaction, caching, export, and deletion behavior. Enforce those facts before the payload crosses the connector boundary.

Auditability does not require logging the raw result. Keep identity, tenant, operation and policy versions, normalized inputs, source watermark, row and byte counts, redaction decisions, destination class, checksum, and trace ID. Raw payload capture should be exceptional and short-lived.

Then test the uncomfortable paths: unapproved regions, aliases for sensitive fields, partial streaming, policy changes mid-session, log leakage, and deletion from caches and backups.

Scoped database access answers what may be read. An egress contract answers where that data may go next.

Full guide: MCP database access needs a data egress contract

A silent row limit turns a sample into a lie

Mads Hansen — Wed, 12 Aug 2026 01:21:03 +0000

Every production MCP server for PostgreSQL needs result limits.

The dangerous part is not the limit.

It is returning the first 1,000 rows without making truncation impossible to miss. The query succeeded, the model received data, and a bounded sample becomes a confident claim about the full population.

Use independent budgets for:

rows
bytes
statement and end-to-end time
query cost
concurrency
total pages per logical request

Then make truncation a typed result state: completeness, reason, observed rows and bytes, stable ordering, and whether continuation is available.

Continuation tokens are also authorization artifacts. Bind them to the principal, tenant, approved operation, query digest, policy version, stable-order keys, snapshot, and expiry.

And choose semantics before adding LIMIT. A request for “top customers” cannot be answered from the first 1,000 invoice rows. It needs an approved aggregate operation over the complete scoped population.

The rule is simple: if the operation is incomplete, the model must not infer totals, rankings, maxima, or absence as if every row was examined.

Full guide: PostgreSQL MCP result limits and truncation

A correct database answer can still be incomplete

Mads Hansen — Wed, 12 Aug 2026 01:21:02 +0000

A database answer can be fresh, numerically correct, and still be incomplete.

One regional source may be unavailable. An inner join may silently drop unmatched records. A connector may stop after the first page while the model describes the result as the whole population.

Production AI database access needs a completeness contract.

Start by defining the expected population: sources, entities, regions, transaction states, reporting interval, and metric version.

Then return evidence with the result:

expected versus observed sources
source and partition watermarks
row counts before and after filters and joins
unknown, null, rejected, and duplicate counts
missing or timed-out sources
pagination and truncation state
reconciliation status

The critical distinction is between zero, unknown, not received, redacted, and not applicable. Turning all five into zero creates a clean answer that cannot be audited.

Partial-result policy also belongs in the workflow contract. An exploratory trend may tolerate named gaps. A regulatory total may require every source and completed reconciliation.

The model can explain why a result is partial. It should not decide whether the missing data is acceptable, and it should never upgrade a partial result to a complete one.

Full guide: AI database answers need a completeness contract

Your MCP cache key is part of your authorization model

Mads Hansen — Tue, 11 Aug 2026 01:25:00 +0000

Caching makes a PostgreSQL MCP server faster.

A weak cache key can also erase the authorization boundary.

Two users may call the same tool with identical arguments while having different tenants, roles, approved views, policies, or environments.

If the key contains only the tool name and JSON arguments, discovery metadata or results can cross scopes.

Start by separating cache classes:

tool discovery
schema metadata
policy decisions
prepared operation shapes
database results
resumable state handles

Then include every attribute that changes what the caller may discover or receive: principal, tenant, execution role, environment, policy version, tool-catalog version, schema version, normalized arguments, and source watermark.

Three details matter in practice:

Tool discovery itself can reveal sensitive structure.
Single-flight request coalescing must use the same authorization-aware key.
A cache hit must preserve freshness, truncation, redaction, and trace evidence.

The negative test is simple: warm the cache, then repeat identical arguments across another tenant, role, environment, revoked approval, and changed schema.

Measure the absence of cross-scope hits—not only the aggregate hit rate.

Full guide: Build an authorization-aware cache for a PostgreSQL MCP server

Your enterprise AI answer needs more than a generated-at timestamp

Mads Hansen — Tue, 11 Aug 2026 01:24:59 +0000

An enterprise AI answer can be correct and already too old.

The query ran seconds ago. The warehouse snapshot is six hours old. A late event changes the total after the answer is delivered.

A “generated at” timestamp does not expose any of that.

For database-connected AI, define freshness as a chain:

event time
ingestion time
source snapshot or replication watermark
query time
answer time

Then set a workflow-specific contract: source, maximum age, allowed cache age, timezone, late-arrival policy, and what happens when the threshold is exceeded.

The result should carry trusted evidence from the connector—not timestamps invented by the model:

approved source and operation
tenant and environment scope
source watermark
metric and schema version
query time
metadata and result-cache age
filters, truncation, and late-arrival cutoff
trace ID

Also separate schema, authorization, and result caches. They have different keys and invalidation rules. A result cached for one tenant must never become reusable by another because the prompt looks the same.

Fluent prose is not a freshness guarantee.

Full implementation guide: Define an answer freshness contract for a ChatGPT enterprise database connection

Stop retrying a PostgreSQL MCP connection you have not classified

Mads Hansen — Mon, 10 Aug 2026 01:22:45 +0000

Retrying a PostgreSQL MCP connection without classifying the failure can destroy the best evidence.

The next attempt may resolve to another IP, use another certificate path, receive another pool connection, or replace the original driver error with a generic timeout.

Capture a sanitized evidence bundle first:

environment, region, runtime, and connector build
target fingerprint, database name, and credential version — never the secret
DNS answers and timing
TCP outcome and duration
TLS certificate fingerprint and verification result
PostgreSQL SQLSTATE and sanitized message
effective role, schema version, and catalog digest
pool state, timeout settings, attempt number, and trace ID

Then classify the layer: configuration, DNS, TCP, TLS, authentication, authorization, schema, pool, or statement execution.

Retry bounded transient failures with backoff and jitter. Do not retry invalid credentials, certificate identity failures, authorization denials, wrong environments, missing approved objects, or policy rejections.

“Connection failed” is not a useful error contract. A stable failure code, layer, retryability decision, safe message, and trace ID is.

Full guide: MCP server Postgres connection troubleshooting with an evidence bundle

The credential that discovers your database should not query it

Mads Hansen — Mon, 10 Aug 2026 01:22:44 +0000

The credential that helps an AI discover a database should not automatically be able to query production data.

Combining discovery and execution in one service account feels convenient. It also means a metadata refresh, indexing job, or compromised discovery component inherits live data access.

Treat them as two trust boundaries.

Discovery identity

approved schemas, views, columns, types, and safe descriptions
no table reads or arbitrary function execution
no unrelated schemas or sensitive comments
produces a versioned catalog snapshot and digest

Execution identity

approved read operations only
authenticated user, tenant, purpose, and environment
database-enforced scope, statement limits, and result limits
evidence tied to the promoted catalog digest

Promote metadata from discovery to execution. Never promote the credential.

Keep development, staging, and production identities separate. Rotate and revoke discovery and execution independently. Then test that each identity cannot do the other one's job.

A successful connection test proves that a credential works. Separation tests prove that its authority stops where it should.

Full guide: Separate discovery and execution credentials for a ChatGPT enterprise database connection