database-craft
/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.
- Author
- ljojua1998 ↗
- Repository
- skills ↗
- Licence
- MIT
- Last repository change
- 13.07.2026
Assessment and security
- Files in this package
- 1
- Scripts
- 0
- HTML comments
- 0
- URLs in SKILL.md
- 0
No signals detected. Repository changed on 13.07.2026, without references to retired models or APIs.
MIT, open: use and modify while retaining author attribution.
What it does and when to use it
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.
Original author description
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.
How it works
- Decision matrix
- Universal (every engine)
- PostgreSQL
- MySQL (8.x, InnoDB)
- MongoDB
- Supabase (Postgres + Auth/Realtime/Storage) — read §PostgreSQL too
- Firebase / Firestore
- Checklist additions
SKILL.md preview
---
name: database-craft
description: 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.
user-invocable: false
---
# 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 IDENTITY` for internal, **UUIDv7** (not
v4 — v4 destroys B-tree locality) for public/distributed. Never `serial`.
- **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); `INCLUDE` for
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 with `CREATE INDEX CONCURRENTLY` on 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 with `FOR UPDATE` or a version column; retry `40001` on SERIALIZABLE.
## MySQL (8.x, InnoDB)
- **InnoDB always**; **`utf8mb4`** charset+collation at DB/table/connection (plain
`utf8` is a 3-byte trap that corrupts emoji). Verify **strict `sql_mode`** (else
silent truncation).
- InnoDB clusters rows on the PK → PK must be **small + monotonic**
(`BIGINT UNSIGNED AUTO_INCREMENT`; if UUID, ordered/v7 as `BINARY(16)` not
`CHAR(36)`); every secondary index carries the PK.
- **Respect what MySQL lacks vs Postgres**: no partial indexes, no `INCLUDE`, no
`RETURNING` (use `LAST_INSERT_ID()`), no `ILIKE`, 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")`: want `IXSCAN`, no `SORT` stage. TTL indexes for
expiry; partial/unique where needed.
- Aggregation: `$match`/`$project` first (before `$group`/`$lookup`/`$sort`); use
`$lookup` sparingly and index the foreign field. Enable collection-level
`$jsonSchema` validation (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); `USING` filters
existing rows, `WITH CHECK` validates 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 a `SECURITY DEFINER` helper (avoids recursive-RLS errors).
- Migrations via the Supabase CLI (`supabase/migrations/`, checked in);
`supabase gen types typescript` on 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()`. **No `offset()`
pagination** (it reads+bills skipped docs) — use cursor `startAfter(lastDoc)`.
Use aggregation `count()/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.
Versions
| Repository | Score | Licence |
|---|---|---|
| This page · ljojua1998/skills | 90 | MIT |
| jssblck/agents | 100 | GPL-3.0 |
Database snapshot: 13.11.2026 · package version 8129caf7a551 (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 90database-engineer
Database architecture design, schema optimization, query performance tuning, and data migration. Handles PostgreSQL, MySQL, MongoDB, Redis. Use when user asks to design database, optimize queries, create migrations, or improve database performance. Do NOT use for frontend or application logic. - 90database-designer
Database design with schema analysis, index optimization, and migration generation for PostgreSQL, MySQL, MongoDB, and DynamoDB. Use when designing schemas, optimizing queries, planning migrations, or analyzing database performance. - 100moai-domain-database
Database specialist covering PostgreSQL, MongoDB, Redis, Oracle, and cloud database platforms (Neon, Supabase, Firestore). Use for schema design, query optimization, indexing strategies, data modeling, or cloud database selection. Cloud vendor guide absorbed from moai-platform-database-cloud. - 90database
Database security lens. Use on any task touching SQL migrations, ORMs, Postgres, MySQL, Supabase, Firebase rules, Firestore, MongoDB, and when the user says "dachshund" or asks if this is safe. Defensive use only. - 90database-design
Use when designing database schemas, reviewing data models, planning migrations, optimizing slow queries, or establishing database patterns. Covers relational (PostgreSQL, MySQL, SQLite), document (MongoDB), and ORM-integrated (Prisma, Drizzle, TypeORM) projects. - 100database-design
Database schema design, optimization, and migration patterns for PostgreSQL, MySQL, and NoSQL databases. Use for designing schemas, writing migrations, or optimizing queries.