db-verify-checks

DB verification — detect store → measure → verdict

The largest failure pattern is inferring DB shape from code. Code says what SHOULD be there; only a query says what IS. This skill is the canonical replacement: prove or disprove a data-shape claim with a real query against the real store, before any data-touching change is reported done.

You are a fresh-context critic when you load this: you did NOT design the schema and you do NOT assume the code's shape is correct. Falsify; do not edit.

Step 0 — DETECT the store first

Read the repo's own config / env to determine which store backs the change:

• DATABASE_URL (or equivalent) with sqlite:///... or postgresql://... / postgres://... ⇒ SQL store. A mongodb://... / mongodb+srv://... URI (often MONGO_URI / MONGODB_URL) ⇒ Mongo store.
• If absent, grep the backend for the actual client (sqlalchemy / create_engine / pymongo / motor) and its config module.
• Prefer the repository's own DB dependency over assuming a system CLI exists. Read the connection target from config — never hard-code a production host.

Setup

1. From the invocation prompt, extract: (collection/table, field/column name(s), expected type, expected presence — required / optional / migrated).
2. If the repo defines an ownership SSOT (e.g. a collections/tables ownership doc), confirm the caller owns this collection/table. Wrong owner = a finding.
3. Load DB credentials from the repo env (read only — never write .env directly).
4. Test the connection with the repo's own client. Real prod hostnames in fixtures BANNED — use the env's actual host, never hardcode.
5. Default target = local store (dev default, e.g. sqlite:///./*.db or mongodb://localhost:27017). Touch a shared / production store only on schema-change + explicit approval, and only to READ.

The checks (run every one — do not skip)

For each claimed field/column:

Mongo idiom

1. $exists: true count — db.<coll>.countDocuments({"<field>": {"$exists": true}}). State the number and compare to total doc count. "every document has X" with $exists ratio <1.0 is a finding.
2. $type distribution — db.<coll>.aggregate([{$group: {_id: {$type: "$<field>"}, count: {$sum: 1}}}]). State each type + count. A "string" field with a null / missing bucket is a finding — code must use doc.get("<field>"), never doc["<field>"].
3. Sample 10 real documents — db.<coll>.find({"<field>": {"$exists": true}}).limit(10). Dump them; confirm the value shape matches what the code expects.
4. Index coverage (write-path changes only) — db.<coll>.getIndexes(). A new filter on <field> that no index covers = perf-risk finding.
5. Owner crosscheck — confirm the changed code lives in the owner defined by the ownership SSOT. Wrong-owner write is a blocking finding.