Skip to content
Skills Library
Programming · Building & coding

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.

Repository
skills ↗
Licence
MIT
Last repository change
13.07.2026

Assessment and security

90/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content25 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 13.07.2026, without references to retired models or APIs.

Licence

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

  1. Decision matrix
  2. Universal (every engine)
  3. PostgreSQL
  4. MySQL (8.x, InnoDB)
  5. MongoDB
  6. Supabase (Postgres + Auth/Realtime/Storage) — read §PostgreSQL too
  7. Firebase / Firestore
  8. Checklist additions
Original SKILL.md headings · 133 lines, 1,130 words
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

RepositoryScoreLicence
This page · ljojua1998/skills90MIT
jssblck/agents100GPL-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.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.