Przejdź do treści
Biblioteka skilli
Programowanie · Budowanie i kod

database-craft

/database-craft

Podpowiada standardy inżynierii baz danych dla PostgreSQL, MySQL, MongoDB, Supabase i Firestore: schemat, indeksy i bezpieczeństwo.

Repozytorium
skills ↗
Licencja
MIT
Ostatnia zmiana repozytorium
13.07.2026

Ocena i bezpieczeństwo

90/ 100Klasa: dobry
Format25 / 25
Frontmatter z nazwą i opisem, poprawna nazwa, opis do 1024 znaków, nazwa zgodna z folderem.
Odkrywalność25 / 25
Opis mówi, kiedy użyć skilla, ma sensowną długość i jest w 3. osobie.
Treść25 / 35
Co najmniej 150 słów i do 500 linii, nagłówki, listy kroków, przykłady albo kod.
Higiena15 / 15
Bez zaślepek typu TODO, bez ścieżek Windows, z licencją.
Ryzykobrak sygnałów w skanie
Pliki w paczce
1
Skrypty
0
Komentarze HTML
0
Adresy URL w SKILL.md
0
Przestarzałość

Brak sygnałów. Repozytorium zmienione 13.07.2026, bez odwołań do wycofanych modeli i API.

Licencja

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

  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
Sekcje z nagłówków SKILL.md, w oryginale · 133 linii, 1 130 słów
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

RepozytoriumWynikLicencja
Ta strona · ljojua1998/skills90MIT
jssblck/agents100GPL-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.

Uwagi i zgłoszenia

Wiadomość trafi do Bartosza Sokolińskiego. Adres e-mail jest potrzebny tylko do odpowiedzi. Szczegóły w polityce prywatności.