DEV Community: Mads Hansen

Your pgvector benchmark is incomplete until you add real access filters

Mads Hansen — Sat, 22 Aug 2026 01:20:16 +0000

A pgvector query can be fast and accurate on the full corpus, then lose relevant results when you add a tenant or business filter.

That does not mean the authorization filter is wrong.

It means retrieval quality must be measured inside the authorized subset.

Build an exact filtered baseline and compare ANN results using:

recall@k
empty and underfilled result rate
p95 latency
rank movement
exact-fallback rate

Report by filter selectivity. A global average hides tiny tenants and rare categories—the cases most likely to lose candidates.

Pin the embedding model, dataset snapshot, distance metric, PostgreSQL/pgvector versions, index parameters, and query settings. Test rebuilds, revocations, concurrent writes, and model upgrades.

For small eligible subsets, an exact distance scan may be both simpler and more accurate. For larger subsets, use ANN with a measured, bounded candidate budget.

Access controls are not a retrieval tuning knob. Keep them fixed and make the search strategy prove it works behind them.

Full guide: pgvector filtered recall tests for AI database search

Your PostgreSQL RLS test is meaningless if it runs as the table owner

Mads Hansen — Sat, 22 Aug 2026 01:20:15 +0000

Your tenant policies exist. Your tests pass. Production still bypasses them.

The usual cause: migrations, tests, and runtime all use the role that owns the tables.

PostgreSQL table owners normally bypass RLS unless FORCE ROW LEVEL SECURITY applies.

A safer acceptance test uses the exact serving role and proves:

it owns no tenant tables
it has no BYPASSRLS or superuser attribute
tenant context comes from authenticated state
context is transaction-scoped with SET LOCAL
cross-tenant rows, joins, and aggregates fail closed
rollback and pool reuse cannot leak the previous tenant

Run the same matrix as the serving role and the migration owner. The difference should be visible and deliberate.

Catalog flags are configuration evidence. A negative query under the production identity is enforcement evidence.

Full guide: PostgreSQL RLS serving-role acceptance tests for AI agents

An immutable audit log cannot prove a decision that was never written

Mads Hansen — Fri, 21 Aug 2026 01:23:22 +0000

An agent proposes an action from database evidence. A reviewer changes it and approves. The action succeeds.

But the decision record is lost between chat, workflow state, and the destination system.

A hash chain cannot detect a record that was never written.

The safer order:

bind approval to the exact artefact digest
durably admit the decision receipt
issue a short-lived, single-use operation capability
verify and consume it at dispatch
reconcile the receipt, attempt, and observed external state

Test admitted/no action, action/no receipt, duplicate delivery, crashes around commit, changed artefacts, cross-tenant replay, expiry in queue, and indeterminate timeouts.

Keep proposal, human decision, and execution as separate linked records. Approval is not execution. External state is not proof of approval.

Receipt-before-action turns missing and replayed decisions into testable system states.

Full guide: AI database approvals: write the receipt before the action

Two authenticated MCP gateways can still disagree about authorization

Mads Hansen — Fri, 21 Aug 2026 01:23:11 +0000

An internal IAM gateway and an external OAuth gateway expose the same database tool.

Both authenticate correctly.

One rejects a cross-tenant filter. The other accepts it.

That is authorization drift.

Use one versioned contract for:

stable capability IDs
trusted tenant and environment scope
allowed tool and argument shapes
approved views and fields
row, byte, and time limits
redaction and approval requirements

Then replay the same negative fixtures through every front door: cross-tenant IDs, stale scopes, resource substitution, aliases, oversized queries, revoked principals, queued requests during policy rollout, and prompt attempts to widen scope.

Emit the contract version with every decision. Compare structured allow/deny evidence, not only status codes.

Authentication tells you who arrived. A shared authorization contract keeps every entrance honest about what they may do.

Full guide: MCP database authorization contracts and drift tests

One hard question is a better database acceptance test than ten easy ones

Mads Hansen — Thu, 20 Aug 2026 01:20:48 +0000

Do not approve a PostgreSQL-to-ChatGPT connection because ten easy questions returned plausible answers.

Use one representative business question with:

a known answer
an explicit reporting cutoff
a tenant boundary
a join
a versioned metric definition
a meaningful stale, incomplete, or empty state

Then require evidence for identity, PostgreSQL role, source/replica, freshness, coverage, normalized filters, query limits, truncation, retries, and provenance.

Run the negative twin too: unauthorized tenant, missing partition, stale replica, unknown enum, and forced timeout. The final prose must preserve refusal, incomplete, stale, and unknown states instead of collapsing them into zero rows.

One hard question tests the contract. Ten easy questions mostly test fluency.

Full guide: Connect PostgreSQL to ChatGPT: the first-query acceptance test

SELECT 1 is not a production MySQL connection test

Mads Hansen — Thu, 20 Aug 2026 01:20:47 +0000

The connection test says “success.” ChatGPT still cannot see the right tables—or it can see far more than intended.

SELECT 1 proves reachability. A production preflight should prove:

the exact service identity and effective grants
certificate and hostname verification
visible schemas, tables, views, and columns
time zone, SQL mode, character set, collation, and isolation
read-only behavior and query limits
pooled-session cleanup after errors and cancellation
positive and negative query fixtures

Test sensitive-table access, unbounded ranges, writes, DDL, expensive joins, and cross-tenant requests. The expected result is a structured refusal—not a plausible answer.

The connection is ready only when identity, scope, session semantics, limits, and evidence all match the production contract.

Full guide: Connect MySQL to ChatGPT with a production connection preflight

A successful pg_dump is not a recovery test

Mads Hansen — Wed, 19 Aug 2026 10:24:01 +0000

A nightly pg_dump exits zero. That proves a backup command ran—not that the system is recoverable.

A useful restore-verification contract defines:

RPO and RTO
covered databases, roles, extensions, and keys
an isolated clean restore
schema, integrity, permissions, and representative query checks
backup age and restore-verification age
artifact checksum and recovery-point evidence
ownership for failed drills

Test corrupt archives, missing keys, version mismatch, full disks, expired credentials, and interrupted restores. Then verify application and MCP authorization with the same roles and approved operations used in production.

A database that starts with the wrong grants or stale schema is not recovered.

Full guide: PostgreSQL for AI workloads needs a restore-verification contract

Your RLS policy can work while the error still leaks data

Mads Hansen — Wed, 19 Aug 2026 10:23:47 +0000

A forbidden row never appears. Then the database says duplicate key or foreign key violation—and the caller learns that a hidden value exists.

Errors are outputs. They can reveal tenant existence, schema details, identifiers, query structure, and infrastructure state.

For database tools used by AI clients:

keep raw SQLSTATE, constraint, and traces in a restricted audit path
return stable public error classes
authorize before distinguishing “missing” from “exists elsewhere”
prefer tenant-scoped uniqueness where semantics allow it
normalize globally unique conflicts
rate-limit repeated probes
retry only explicitly transient classes

Test body, status, timing, and retry behavior—not just returned rows. A green RLS read test says nothing about constraint and error oracles.

Full guide: MCP database errors need a side-channel contract

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