db-types-precision
Audit column types and precision — money stored as float/double (severity 5), naive timestamp vs timestamptz/UTC, jsonb used to evade schema, enum-vs-lookup-table choice, and charset/collation (utf8mb4, case-insensitive collation). Module M4. Feeds the Design & Integrity score (Tipos category).
How do I install this agent skill?
npx skills add https://github.com/hainrixz/claude-db --skill db-types-precisionIs this agent skill safe to install?
- Gen Agent Trust Hubpass
The skill is a database schema auditing tool that identifies data integrity risks such as improper precision for currency or naive timezone handling. It operates by parsing DDL files and querying database metadata using standard command-line tools.
- Socketpass
No alerts
- Snykpass
Risk: LOW · No issues
What does this agent skill do?
db-types-precision (M4)
The type is the first and cheapest constraint: it decides what values are even representable and how they sort, compare, and round. The classic data-loss bug — money in a float — lives here. This module is design-axis (Tipos category, shared with M6). It applies across paradigms with paradigm-appropriate type vocabularies (Postgres numeric, Mongo Decimal128).
What it checks
- Money as float:
float/real/double precision(or MongoDouble) for currency/amounts — binary floating point cannot represent decimal cents exactly. Usenumeric/decimal/Decimal128/integer-minor-units. This is the severity-5 cap. - Timestamps & timezone: naive
timestamp/datetime(without time zone) for instants that cross zones; prefertimestamptzstored in UTC. Flagtimestampcolumns named*_atlacking tz. - jsonb as schema evasion: a
jsonb/jsoncolumn carrying what should be first-class typed/constrained columns (stable, queried, FK-related keys buried in JSON). Embedding flexible blobs is fine; hiding the schema is not. - enum vs lookup: native
ENUM(esp. MySQLENUM, hard to alter) where a referenced lookup table would be safer to evolve; or free-text status where a constrained domain is needed. - Charset/collation: MySQL
utf8(3-byte, no emoji/astral) instead ofutf8mb4; an unintended case-sensitive or accent-sensitive collation on identifiers/emails.
Axis & severity
- Money in float/double: severity 5,
fail, axisdesign, confidenceestablished(caps the Design score). - Naive timestamp for cross-zone instant: severity 3–4,
warn, axisdesign. - jsonb hiding a stable queried schema: severity 3,
warn,fixable: proposed. - MySQL
utf8(not utf8mb4): severity 3,warn. - enum-as-lock-in / wrong collation: severity 2–3,
warn.
Tier-0 static check
Parse DDL/snapshot via scripts/parse-schema.mjs: match money-like column names (price, amount, total, balance, cost, *_cents) against float|real|double|Double types; flag timestamp/datetime without tz on *_at columns; detect json/jsonb columns and ENUM(...); read declared charset/collation. Directional program-source parses never raise the money-float sev-5 cap.
Tier-1 verification query
Confirm float money columns from the catalog:
-- $DATABASE_URL, read-only
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type IN ('real','double precision')
AND column_name ~* '(price|amount|total|balance|cost|fee|tax)';
MySQL charset check:
SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns WHERE character_set_name = 'utf8';
Findings
Emit per schema/finding.schema.json. Examples:
M4.orders.total_float_money—total double precisionloses cents (severity 5,fail, axisdesign, confidenceestablished).M4.events.created_at_naive_timestamp—created_at timestamplacks time zone (severity 4,warn, axisdesign,fixable: proposed).M4.products.attributes_jsonb_schema_evasion— stable queried keys buried injsonb(severity 3,warn).M4.users.email_utf8_not_utf8mb4— MySQLutf8truncates astral chars (severity 3,warn). Each finding:evidence.observedquotes the column DDL verbatim;verification.reproduceis a runnable query above (method: ddl_parse/schema_introspect);expected_impactbanded + confidence-tagged.
Honesty
- Float money caps regardless of paradigm (relational and Mongo
Double); never soften it. jsonbis the right tool for genuinely flexible/sparse data — only flag it when it conceals a stable, queried, relational schema.- Do not assert collation breakage without showing the declared collation; band the impact, never invent a sort-error rate.
How can the creator link this skill?
Add the canonical catalog link to the repository README so users can inspect current installs and available audits. The publishing guide covers the complete discovery path.
<a href="https://skillzs.dev/skills/hainrixz/claude-db/db-types-precision">View db-types-precision on skillZs</a>