database-craft
/database-craft
Podpowiada standardy inżynierii baz danych dla PostgreSQL, MySQL, MongoDB, Supabase i Firestore: schemat, indeksy i bezpieczeństwo.
- Autor
- ljojua1998 ↗
- Repozytorium
- skills ↗
- Licencja
- MIT
- Ostatnia zmiana repozytorium
- 13.07.2026
Ocena i bezpieczeństwo
- Pliki w paczce
- 1
- Skrypty
- 0
- Komentarze HTML
- 0
- Adresy URL w SKILL.md
- 0
Brak sygnałów. Repozytorium zmienione 13.07.2026, bez odwołań do wycofanych modeli i API.
MIT, otwarta: można używać i zmieniać, z zachowaniem informacji o autorze.
Co robi i kiedy
Podpowiada standardy inżynierii baz danych dla PostgreSQL, MySQL, MongoDB, Supabase i Firestore: schemat, indeksy i bezpieczeństwo.
Oryginalny opis (EN)
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.
Jak działa
- Decision matrix
- Universal (every engine)
- PostgreSQL
- MySQL (8.x, InnoDB)
- MongoDB
- Supabase (Postgres + Auth/Realtime/Storage) — read §PostgreSQL too
- Firebase / Firestore
- Checklist additions
Podgląd SKILL.md
---
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.
Wersje
| Repozytorium | Wynik | Licencja |
|---|---|---|
| Ta strona · ljojua1998/skills | 90 | MIT |
| jssblck/agents | 100 | GPL-3.0 |
Migawka bazy z 13.11.2026 · wersja paczki 8129caf7a551 (skrót SHA-256 całej paczki)
Pobrania w ostatnich 30 dniach: 0 · ranking
Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.
Podobne skille
- 90database-engineer
Projektuje architekturę baz, optymalizuje schematy i zapytania oraz przygotowuje migracje w PostgreSQL, MySQL, MongoDB i Redis. - 90database-designer
Projektuje bazy danych z analizą schematu, optymalizacją indeksów i generowaniem migracji dla PostgreSQL, MySQL, MongoDB i DynamoDB oraz analizuje wydajność bazy. - 100moai-domain-database
Wspiera pracę z bazami danych PostgreSQL, MongoDB, Redis, Oracle i chmurowymi (Neon, Supabase, Firestore): projekt schematu, optymalizacja zapytań, indeksy, modelowanie danych i wybór bazy w chmurze. - 90database
Sprawdza bezpieczeństwo bazy danych w migracjach SQL, ORM-ach, Postgresie, MySQL, Supabase i regułach Firebase, tylko w celach obronnych. - 90database-design
Pomaga projektować schematy, przeglądać modele danych i planować migracje w PostgreSQL, MySQL, MongoDB, Prisma i Drizzle. - 100database-design
Projektuje schematy, migracje i optymalizuje zapytania w PostgreSQL, MySQL i bazach NoSQL.