Database craft
Database engineering standards for PostgreSQL, MySQL, MongoDB, Supabase and Firebase/Firestore — schema/index design, security, and the per-engine gotchas an agent gets wrong. Loaded on top of backend-craft when a ticket touches the database. Use when designing schema, queries, migrations or data-access.From its SKILL.md
npx -y skills add ljojua1998/skills --skill database-craftAssembled from the repository path, not quoted from the project. Check it against their README if it does not work.
One thing to look at
- 5 stars5 stars. Stars are a popularity signal and not a quality one, but at this level it is likely that nobody has read this closely except its author, and you would be relying on your own review.
SKILL.md
8.4 KB, as published. Nobody here has run it
Database Craft
Standards for the five supported databases. Use the engine the project already uses; for greenfield, pick per the decision matrix and record it. Read only the section for the engine in play, plus Universal.
Decision matrix
| Need | Pick |
|---|---|
| Relational integrity, transactions, complex queries, JSON+relational | PostgreSQL |
| Existing MySQL ecosystem / managed read-heavy web app | MySQL |
| Flexible docs, access-pattern-shaped aggregates, horizontal scale | MongoDB |
| Postgres + auth/realtime/storage, client-direct, fast MVP | Supabase |
| Mobile/web realtime + offline, minimal ops, client-direct | Firestore |
Universal (every engine)
- Parameterized queries only — never concatenate user input (SQL or NoSQL operator injection). This is the #1 injection defense.
- Migrations in version control — every schema change is a checked-in, reviewable, forward-only file; never mutate prod schema by hand. Expand → migrate → contract for zero-downtime (never drop/rename a column in the same deploy that stops using it).
- No secrets in code; least-privilege runtime role (DML only, separate from the DDL/migration role); connection pooling (never a connection per request; serverless → external transaction-mode pooler).
- Index every column you filter/join/sort on; no
SELECT *; avoid N+1 (JOIN /IN (...)batch / eager-load, never query-in-a-loop); statement + pool-acquire timeouts; automated backups with a tested restore.
PostgreSQL
- Types are constraints: money →
numeric(12,2)(never float); time →timestamptz(UTC); ids →bigint GENERATED ALWAYS AS IDENTITYfor internal, UUIDv7 (not v4 — v4 destroys B-tree locality) for public/distributed. Neverserial. - Index FK columns — Postgres does NOT auto-index them (unindexed FK = slow
joins + heavy locks on parent delete). Push integrity into the DB: FK with
explicit
ON DELETE,UNIQUE(partial for soft-delete:... WHERE deleted_at IS NULL),CHECK,NOT NULL. - Indexes: composite order = (equality cols, then range/sort);
INCLUDEfor covering/index-only; partial for subsets; GIN for JSONB/@>/full-text/trigram (ILIKE '%x%'can't use btree); expression index must match the query's function. Build withCREATE INDEX CONCURRENTLYon live tables. - Pool small (5–10/instance); serverless → pgbouncer/Supavisor transaction mode
(prepared statements need pgbouncer ≥1.21 / Supavisor 1.0, else disable them in
the driver).
EXPLAIN (ANALYZE, BUFFERS)— watch for Seq Scan on big tables, estimate/actual divergence (→ANALYZE), disk sorts. Short transactions; prevent lost updates withFOR UPDATEor a version column; retry40001on SERIALIZABLE.
MySQL (8.x, InnoDB)
- InnoDB always;
utf8mb4charset+collation at DB/table/connection (plainutf8is a 3-byte trap that corrupts emoji). Verify strictsql_mode(else silent truncation). - InnoDB clusters rows on the PK → PK must be small + monotonic
(
BIGINT UNSIGNED AUTO_INCREMENT; if UUID, ordered/v7 asBINARY(16)notCHAR(36)); every secondary index carries the PK. - Respect what MySQL lacks vs Postgres: no partial indexes, no
INCLUDE, noRETURNING(useLAST_INSERT_ID()), noILIKE, CHECK only 8.0.16+, DDL is non-transactional (a failed migration can leave partial state — keep them small; use online DDL / gh-ost for big tables). Can't index a JSON column directly — add a generated column and index that.
MongoDB
- Model around query/access patterns, not entities. Embed when read-together, owned, and bounded; reference when shared, large, or unbounded. Never embed an unbounded array (16MB doc cap) — use referencing/bucket pattern. Avoid deep nesting.
- Compound index = ESR order (Equality, Sort, Range). Verify with
explain("executionStats"): wantIXSCAN, noSORTstage. TTL indexes for expiry; partial/unique where needed. - Aggregation:
$match/$projectfirst (before$group/$lookup/$sort); use$lookupsparingly and index the foreign field. Enable collection-level$jsonSchemavalidation (Mongoose validation alone is bypassable);.lean()for read-only. Cache the client in serverless (never connect per request); avoid N+1.populate()in loops. Transactions need a replica set — prefer single-document atomicity. - Security: enable auth (never expose an unauthenticated instance — classic
breach); reject
$/.in user-supplied field names (NoSQL injection); TLS; private network.
Supabase (Postgres + Auth/Realtime/Storage) — read §PostgreSQL too
The client talks to the DB directly from the browser; Row Level Security is the only thing protecting your data. RLS misconfig is the #1 Supabase failure.
- RULE 0: enable RLS on EVERY table in an exposed schema. RLS off = the table is world-readable/writable via the auto REST API. RLS on with no policy = deny-all (safe); then add policies. Verify with the Security Advisor.
- Keys: publishable/anon key → client (RLS enforced); secret/service_role key
→ server ONLY, bypasses ALL RLS — never in client code, a
NEXT_PUBLIC_/VITE_/EXPO_PUBLIC_var, or a repo. Do privileged work in edge functions. - Policies: one per operation (SELECT/INSERT/UPDATE/DELETE);
USINGfilters existing rows,WITH CHECKvalidates written rows (INSERT needs WITH CHECK; UPDATE needs both — or users reassign ownership). Wrap as(select auth.uid())(cached once/query, not per row) and index the policy columns (user_id). Role/team checks via aSECURITY DEFINERhelper (avoids recursive-RLS errors). - Migrations via the Supabase CLI (
supabase/migrations/, checked in);supabase gen types typescripton every schema change (commit it). Storage buckets have their own policies — default private + signed URLs. Realtime respects RLS — confirm the table's policies actually restrict rows before publishing changes.
Firebase / Firestore
- Security Rules are the authorization layer — deny by default, grant narrowly.
Validate auth + shape + immutability (
request.auth.uid,.diff().affectedKeys(), custom claims for roles — never a client-writable role field). Rules are not filters: a broad query that returns docs the rules forbid fails, so constrain queries to match rules.get()/exists()in rules cost billed reads — minimize. - Model for reads; denormalize (no joins — fan-out writes to keep copies consistent). Subcollections keep parent docs small; cross-parent needs a collection-group query + composite index. Avoid >1MB docs, unbounded arrays, and monotonic-ID write hotspots (use random IDs; distributed counters for hot counts).
- Cost is per operation, not per byte — always
limit(). Nooffset()pagination (it reads+bills skipped docs) — use cursorstartAfter(lastDoc). Use aggregationcount()/sum()/avg()instead of reading all docs. Composite queries need a committed composite index (firestore.indexes.json). - Admin SDK bypasses all rules → server-only; never ship service-account creds to a client/repo. App Check to block non-app clients. Default to Firestore over RTDB (RTDB only for presence/high-frequency ephemeral state).
Checklist additions
- Parameterized queries; least-privilege role; pooling; migrations in VC; indexes on filter/join/sort cols; no SELECT *; N+1 checked.
- PG: FK columns indexed; correct types (timestamptz/numeric); UUIDv7 not v4.
- MySQL: utf8mb4 + strict mode; no assumption of partial-index/INCLUDE/RETURNING; JSON via generated column.
- Mongo: no unbounded embedded arrays; ESR index order; auth on; NoSQL-injection-safe.
- Supabase: RLS enabled on every exposed table with per-op policies (USING+WITH CHECK), (select auth.uid()) + indexed; service_role never client-side; Advisor checked.
- Firestore: deny-by-default rules validating auth+shape; cursor (not offset) pagination; limit() everywhere; Admin SDK server-only.