Przejdź do treści
Biblioteka skilli
Programowanie · Planowanie

database-schema-designer

/database-schema-designer

Projektuje nowe tabele z wymagań, przegląda schemat pod kątem normalizacji i wydajności, dodaje wielodostępność, planuje migracje i generuje typy TypeScript lub Pythona.

Repozytorium
gaffer ↗
Licencja
Apache-2.0
Ostatnia zmiana repozytorium
29.09.2026

Ocena i bezpieczeństwo

100/ 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ść35 / 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 29.09.2026, bez odwołań do wycofanych modeli i API.

Licencja

Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.

Co robi i kiedy

Projektuje nowe tabele z wymagań, przegląda schemat pod kątem normalizacji i wydajności, dodaje wielodostępność, planuje migracje i generuje typy TypeScript lub Pythona.

Wyzwalacze z opisu

design the schemaERDtable relationshipsschema migrationnormalise thisdatabase model
Oryginalny opis (EN)

Use when designing new database tables from requirements, reviewing a schema for normalisation or performance issues, adding multi-tenancy, planning a breaking migration, or generating TypeScript/Python types from a schema. Triggers on "design the schema", "ERD", "table relationships", "schema migration", "normalise this", or "database model".

Jak działa

  1. Steps
  2. Migration safety (Postgres; MySQL: prefer ALGORITHM=INSTANT/INPLACE, LOCK=NONE)
  3. Tests
  4. Done when
  5. Review checklist
  6. Capture lore
Sekcje z nagłówków SKILL.md, w oryginale · 106 linii, 1 042 słów
Podgląd SKILL.md
---
name: database-schema-designer
description: Use when designing new database tables from requirements, reviewing a schema for normalisation or performance issues, adding multi-tenancy, planning a breaking migration, or generating TypeScript/Python types from a schema. Triggers on "design the schema", "ERD", "table relationships", "schema migration", "normalise this", or "database model".
stack: [node, python, go, java, rust]
area: data
---

# Design schemas that survive production

A schema is the one place an invariant holds no matter which code path, process or
concurrent request writes. Normalise first, let constraints enforce the rules, index for
the real query patterns, and change it in steps that never block production. Sources:
PostgreSQL docs (constraints, `CREATE INDEX`, `ALTER TABLE`, transaction isolation, row
security); MySQL 8 reference (InnoDB locking, online DDL); Codd's normal forms as taught
in standard database texts.

## Steps

1. **Read the existing schema and conventions.** `search_lore` for the ORM, migration
   tool, naming, key strategy and tenancy model. Extend them; never introduce a second
   pattern.
2. **Model entities and relationships.** Nouns with attributes become tables. State
   cardinality before structure: 1:N → FK on the N side; M:N → junction table with a
   composite primary or unique key on the two FKs; 1:1 → FK with a UNIQUE constraint.
   Sketch the ERD (Mermaid is fine) in the plan or the evidence summary.
3. **Normalise to 3NF for transactional data.** 1NF: atomic values, no repeating groups.
   2NF: no column depends on part of a composite key. 3NF: no column depends on another
   non-key column. Denormalise only for a measured read need, and document who keeps the
   copy consistent (trigger, same-transaction write, or rebuild job).
4. **Write every invariant as a constraint** — the application check alone is a race.
   Under Read Committed (Postgres default) and Repeatable Read (MySQL InnoDB default),
   two transactions can both "check, then insert" and both succeed:
   - uniqueness → `UNIQUE`; conditional ("one active subscription per user") → partial
     unique index `WHERE status = 'active'` (Postgres) or a generated column (MySQL);
   - no overlapping ranges (bookings) → Postgres `EXCLUDE USING gist (room_id WITH =,
     during WITH &&)` with `btree_gist`, or serialise via `SELECT … FOR UPDATE` on a parent;
   - value rules (`amount >= 0`, allowed states) → `CHECK`; references → FK with an
     explicit `ON DELETE` (`RESTRICT` unless cascade is truly intended);
   - counters and balances → a single `UPDATE … SET n = n + :d WHERE … AND n + :d >= 0`,
     never read-modify-write in application code; concurrent edits to one row →
     optimistic `version` column (`UPDATE … WHERE id = ? AND version = ?`, 0 rows → `409`).
   Map constraint violations to a domain error (`409`/`422`), not a `500`. If you use
   `SERIALIZABLE`, retry on SQLSTATE `40001`.
5. **Choose types that cannot drift.** Timestamps `timestamptz` (MySQL: UTC `DATETIME`
   by convention); money as `numeric`/integer minor units, never float; `text` with a
   `CHECK` length over arbitrary `varchar(n)` in Postgres; enums via `CHECK` or a lookup
   table (easier to extend than a native enum). Keys: `bigint` identity for single-node,
   UUIDv7 (time-ordered, index-friendly; Postgres 18 has `uuidv7()`) when ids are
   generated outside the database. `NOT NULL` by default; nullable only with a meaning.
   Absent a repo convention: `created_at`/`updated_at timestamptz NOT NULL DEFAULT now()`
   on every table; soft delete as a nullable `deleted_at` with partial indexes and
   unique keys `WHERE deleted_at IS NULL`; and an append-only audit table (`entity_type`,
   `entity_id`, `action`, `actor_id`, `changed_at`, `before`, `after`) for user-facing
   data with compliance implications.
6. **Tenancy.** `tenant_id NOT NULL` on every tenant-scoped table, leading in composite
   unique keys and indexes; composite FKs `(tenant_id, parent_id)` make cross-tenant
   references impossible. With Postgres RLS: `ENABLE` and `FORCE ROW LEVEL SECURITY`
   (owners bypass otherwise) and connect as a non-owner role.
7. **Index for the query patterns in the ticket.** Postgres does not index FK columns
   automatically (InnoDB does): add them. Composite indexes: equality columns, then range,
   then sort. Partial indexes for hot subsets. Every index costs every write; add none
   without a query that needs it (the `sql-query-performance` skill).
8. **Migrate without blocking** (mechanics: the `add-db-migration` skill; review: the
   `migration-review` skill). Set `lock_timeout` for DDL. See the table below.
9. **Generate types from the schema** (Prisma/Drizzle/sqlc/SQLAlchemy/pydantic tooling
   the repo uses); never hand-write a parallel model.

## Migration safety (Postgres; MySQL: prefer `ALGORITHM=INSTANT/INPLACE, LOCK=NONE`)

| Change                               | Safe pattern                                                                                          |
| ------------------------------------ | ----------------------------------------------------------------------------------------------------- |
| Add nullable column                  | Safe.                                                                                                 |
| Add column with constant default     | Safe on PG 11+ (metadata only); volatile defaults rewrite the table.                                  |
| Make column `NOT NULL`               | Backfill in batches; `ADD CHECK (c IS NOT NULL) NOT VALID`; `VALIDATE`; then `SET NOT NULL` (PG 12+). |
| Add FK or CHECK                      | `ADD CONSTRAINT … NOT VALID`, then `VALIDATE CONSTRAINT` in a later step.                             |
| Add index                            | `CREATE INDEX CONCURRENTLY`, outside a transaction; if it fails, drop the `INVALID` index and retry.  |
| Rename / drop column, change type    | Expand-contract across deploys: add new, dual-write, backfill, switch reads, drop old.                |

## Tests

- Migrations apply up (and down, if the repo supports it) on a fresh database and on
  one seeded with representative data.
- **Concurrency per invariant:** N parallel transactions attempting to violate it (two
  active subscriptions, overlapping bookings, overdraft) → exactly the allowed number
  commit; the rest get the domain error, none a `500`. Run against the real engine, not
  an in-memory substitute with different semantics (the `concurrency-and-async` skill's
  step 9 recipe).
- `EXPLAIN` on the ticket's main queries shows the intended indexes.

## Done when

The ERD and DDL match the requirements, every invariant is a constraint with a
concurrency test, migrations follow the safety table, types are generated, and each
acceptance criterion has evidence via the `record-evidence` skill.

## Review checklist

- [ ] 3NF, or denormalisation documented with its consistency mechanism.
- [ ] Every business invariant enforced by a constraint, not only application code.
- [ ] FKs indexed (Postgres) with an explicit `ON DELETE`.
- [ ] Types: `timestamptz`, exact money, `NOT NULL` by default.
- [ ] Tenant id in keys, composite FKs, RLS forced if used.
- [ ] Non-blocking migration steps; `lock_timeout` set.

## Capture lore

This skill is one of the places durable, reusable knowledge naturally surfaces:
**While modelling tables you learn the ORM, the migration tool, the key strategy, the tenancy and RLS model and the invariants the schema enforces.** That kind of fact is *lore*. Capture it via the **lore-capture
protocol in your brief** (`CLAUDE.factory.md`, step 11 "Memory contribution"):
call the Memory MCP `suggest_lore` once at the close of your work — reusable
conventions, gotchas, decisions, and boundaries only, never per-ticket trivia.

Wersje

RepozytoriumWynikLicencja
Ta strona · tmj-90/gaffer100Apache-2.0
TerminalSkills/skills100Apache-2.0
jeremylongshore/tons-of-skills-marketplace100MIT
dannykkh/skill-olympus100MIT
Qadri-uk/claude-skills100MIT
alirezarezvani/claude-skills100MIT
borghei/Claude-Skills100MIT
OneWave-AI/claude-skills100MIT
JPeetz/agent-skills100MIT

Migawka bazy z 13.11.2026 · wersja paczki 5d1e8de22f72 (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

  • 100supabase-schema-from-requirements
    Projektuje schemat Postgres w Supabase z wymagań biznesowych: migracje, polityki RLS i typy TypeScript.
  • 100database-design-document
    Projektuje produkcyjny schemat bazy danych z diagramem ERD, definicjami tabel, słownikiem danych, strategią indeksów, decyzjami o normalizacji i planem migracji.
  • 90database-design
    Dobiera bazę danych i ORM, projektuje schemat oraz indeksy i planuje bezpieczne migracje.
  • 100database-design-reviewer
    Sprawdza schematy, zapytania i migracje pod kątem spójności, normalizacji, indeksów i bezpiecznego wdrożenia oraz wycofania.
  • 100database-designer
    Projektuje schemat bazy: tabele, relacje, indeksy, normalizację, wybór SQL lub NoSQL i plan migracji, zanim powstanie DDL.
  • 100prisma-migration
    Tworzy modele schematu Prisma i generuje migracje bazy danych na podstawie modelu encji.

Uwagi i zgłoszenia

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