db-schema

Name: db-schema
Author: sordi-ai

17stars3forks

How to install db-schema?

sordi-ai/skill-everything/db-schema

$npx -y skills add sordi-ai/skill-everything --skill db-schema

Installs into the current project.

Summary

Apply when designing database schemas, writing migrations, or reviewing table structure. Covers naming, keys, indexes, constraints, nullability, and migration safety.

Files · 1

View on GitHub

SKILL.md

1	# Sub-Skill: Database Schema Design
2	<!-- target: tokens_target above. Run `python tools/render_readme_table.py` to update README. -->
3
4	Purpose: Prevent data-integrity bugs, race conditions, and irreversible migrations by enforcing database-level constraints and safe migration patterns before code reaches production.
5
6	---
7
8	## Rules
9
10	### Naming Conventions
11
12	1. Snake case everywhere. Always use `snake_case` for table names, column names, and index names; never use camelCase or PascalCase in SQL identifiers.
13	2. Plural table names. Always name tables in the plural (`users`, `orders`, `line_items`); singular names cause inconsistency across ORMs and query builders.
14	3. Consistent FK column naming. Always name foreign-key columns `<referenced_table_singular>_id` (e.g., `user_id`, `order_id`) so the reference is self-documenting.
15
16	### Primary Keys
17
18	4. Surrogate primary key. Always define an explicit surrogate primary key (`id`) on every table; never rely on natural keys as the sole PK unless the domain guarantees immutability.
19	5. UUID vs serial. Prefer `uuid` PKs for distributed systems or public-facing IDs; prefer `bigserial` / `bigint GENERATED ALWAYS AS IDENTITY` for high-insert internal tables where sort order matters.
20
21	### Foreign Keys & Referential Integrity
22
23	6. Declare FK constraints. Always declare `FOREIGN KEY` constraints in the schema; never rely on application-layer join logic to enforce referential integrity.
24	7. Explicit ON DELETE behaviour. Always specify `ON DELETE` behaviour (`CASCADE`, `SET NULL`, or `RESTRICT`) on every FK; omitting it defaults to `RESTRICT`, which can cause surprising errors at runtime.
25	8. Missing FK is a data-integrity bug. Never ship a column that references another table without a corresponding FK constraint — orphaned rows accumulate silently. Reference: ERR-2026-022
26
27	### Indexes
28
29	9. Index every FK column. Always add an index on every foreign-key column; unindexed FKs cause full-table scans on joins and cascade operations.
30	10. Covering indexes for hot queries. Use covering indexes (include all columns in the `SELECT`) for read-heavy queries rather than adding redundant columns to the base table.
31	11. Unique constraints at DB level. Always enforce uniqueness at the database level rather than relying on application-layer deduplication; race conditions between concurrent writes bypass app checks. Reference: ERR-2026-022
32
33	### Nullability & Defaults
34
35	12. Explicit NOT NULL. Always mark columns `NOT NULL` unless `NULL` carries distinct semantic meaning (unknown vs. absent); nullable columns complicate query logic and ORM mapping.
36	13. Meaningful defaults. Always supply a `DEFAULT` for columns that have a sensible zero-value (`0`, `''`, `false`, `now()`); avoid forcing callers to supply values the DB can compute.
37
38	### Timestamps & Soft Delete
39
40	14. Standard audit columns. Always include `created_at TIMESTAMPTZ NOT NULL DEFAULT now()` and `updated_at TIMESTAMPTZ NOT NULL DEFAULT now()` on every table; omitting them makes debugging and auditing impossible.
41	15. Soft delete with deleted_at. Prefer `deleted_at TIMESTAMPTZ` over hard deletes for user-facing entities; ensure all queries filter `WHERE deleted_at IS NULL` or use a view/partial index.
42
43	### Enum Columns
44
45	16. DB-native enum or check constraint. Always enforce enum-like columns with a `CHECK` constraint or a native `ENUM` type; never store unconstrained strings and validate only in the application.
46
47	### Migrations
48
49	17. Up and down migrations. Always write both an `up` and a `down` migration; a rollback path is mandatory before any migration reaches production.
50	18. Idempotent migrations. Always guard DDL statements with `IF NOT EXISTS` / `IF EXISTS` so re-running a migration does not error; this is required for blue-green and multi-replica deployments.
51	19. Non-blocking index creation. Use `CREATE INDEX CONCURRENTLY` (Postgres) or equivalent for indexes on large tables; blocking index creation causes downtime.
52
53	### Constraints Over App Logic
54
55	20. Check constraints for invariants. Always encode business invariants (e.g., `price > 0`, `quantity >= 0`) as `CHECK` constraints; application-layer validation is bypassed by direct DB writes, scripts, and migrations.
56
57	---
58
59	## See also
60
61	- `skills/code-quality/SKILL.md` — general code-quality rules that apply to migration scripts
62	- `skills/review-deployment/SKILL.md` — deployment checklist that includes migration safety gates
63
64	---

People who liked this also viewed

6 picks

Type

azure-storageAzure Storage Services including Blob Storage, File Shares, Queue Storage, Table Storage, and Data Lake. Answers questions about storage access tiers (hot, cool, cold, archive), when to use each tier,SkillsJul 2026471k1.3k
supabase-postgres-best-practicesPostgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.SkillsJul 2026302k2.4k
supabaseUse when doing ANY task involving Supabase. Triggers: Supabase products (Database, Auth, Edge Functions, Realtime, Storage, Vectors, Cron, Queues); client libraries and SSR integrations (supabase-js, SkillsJul 2026179k2.4k
convex-quickstartCreates or adds Convex to an app. Use for new Convex projects, npm create convex@latest, frontend setup, env vars, or the first npx convex dev run.SkillsJul 202690k39
convex-performance-auditAudits Convex performance for reads, subscriptions, write contention, and function limits. Use for slow features, insights findings, OCC conflicts, or read amplification.SkillsJul 202689k39
convex-setup-authSets up Convex auth, identity mapping, and access control. Use for login, auth providers, users tables, protected functions, or roles in a Convex app.SkillsJul 202689k39

db-schema

17stars3forks

View on GitHub

How to install db-schema?

sordi-ai/skill-everything/db-schema

$npx -y skills add sordi-ai/skill-everything --skill db-schema

Installs into the current project.

Summary

Apply when designing database schemas, writing migrations, or reviewing table structure. Covers naming, keys, indexes, constraints, nullability, and migration safety.

Files · 1

View on GitHub

SKILL.md

1	# Sub-Skill: Database Schema Design
2	<!-- target: tokens_target above. Run `python tools/render_readme_table.py` to update README. -->
3
4	Purpose: Prevent data-integrity bugs, race conditions, and irreversible migrations by enforcing database-level constraints and safe migration patterns before code reaches production.
5
6	---
7
8	## Rules
9
10	### Naming Conventions
11
12	1. Snake case everywhere. Always use `snake_case` for table names, column names, and index names; never use camelCase or PascalCase in SQL identifiers.
13	2. Plural table names. Always name tables in the plural (`users`, `orders`, `line_items`); singular names cause inconsistency across ORMs and query builders.
14	3. Consistent FK column naming. Always name foreign-key columns `<referenced_table_singular>_id` (e.g., `user_id`, `order_id`) so the reference is self-documenting.
15
16	### Primary Keys
17
18	4. Surrogate primary key. Always define an explicit surrogate primary key (`id`) on every table; never rely on natural keys as the sole PK unless the domain guarantees immutability.
19	5. UUID vs serial. Prefer `uuid` PKs for distributed systems or public-facing IDs; prefer `bigserial` / `bigint GENERATED ALWAYS AS IDENTITY` for high-insert internal tables where sort order matters.
20
21	### Foreign Keys & Referential Integrity
22
23	6. Declare FK constraints. Always declare `FOREIGN KEY` constraints in the schema; never rely on application-layer join logic to enforce referential integrity.
24	7. Explicit ON DELETE behaviour. Always specify `ON DELETE` behaviour (`CASCADE`, `SET NULL`, or `RESTRICT`) on every FK; omitting it defaults to `RESTRICT`, which can cause surprising errors at runtime.
25	8. Missing FK is a data-integrity bug. Never ship a column that references another table without a corresponding FK constraint — orphaned rows accumulate silently. Reference: ERR-2026-022
26
27	### Indexes
28
29	9. Index every FK column. Always add an index on every foreign-key column; unindexed FKs cause full-table scans on joins and cascade operations.
30	10. Covering indexes for hot queries. Use covering indexes (include all columns in the `SELECT`) for read-heavy queries rather than adding redundant columns to the base table.
31	11. Unique constraints at DB level. Always enforce uniqueness at the database level rather than relying on application-layer deduplication; race conditions between concurrent writes bypass app checks. Reference: ERR-2026-022
32
33	### Nullability & Defaults
34
35	12. Explicit NOT NULL. Always mark columns `NOT NULL` unless `NULL` carries distinct semantic meaning (unknown vs. absent); nullable columns complicate query logic and ORM mapping.
36	13. Meaningful defaults. Always supply a `DEFAULT` for columns that have a sensible zero-value (`0`, `''`, `false`, `now()`); avoid forcing callers to supply values the DB can compute.
37
38	### Timestamps & Soft Delete
39
40	14. Standard audit columns. Always include `created_at TIMESTAMPTZ NOT NULL DEFAULT now()` and `updated_at TIMESTAMPTZ NOT NULL DEFAULT now()` on every table; omitting them makes debugging and auditing impossible.
41	15. Soft delete with deleted_at. Prefer `deleted_at TIMESTAMPTZ` over hard deletes for user-facing entities; ensure all queries filter `WHERE deleted_at IS NULL` or use a view/partial index.
42
43	### Enum Columns
44
45	16. DB-native enum or check constraint. Always enforce enum-like columns with a `CHECK` constraint or a native `ENUM` type; never store unconstrained strings and validate only in the application.
46
47	### Migrations
48
49	17. Up and down migrations. Always write both an `up` and a `down` migration; a rollback path is mandatory before any migration reaches production.
50	18. Idempotent migrations. Always guard DDL statements with `IF NOT EXISTS` / `IF EXISTS` so re-running a migration does not error; this is required for blue-green and multi-replica deployments.
51	19. Non-blocking index creation. Use `CREATE INDEX CONCURRENTLY` (Postgres) or equivalent for indexes on large tables; blocking index creation causes downtime.
52
53	### Constraints Over App Logic
54
55	20. Check constraints for invariants. Always encode business invariants (e.g., `price > 0`, `quantity >= 0`) as `CHECK` constraints; application-layer validation is bypassed by direct DB writes, scripts, and migrations.
56
57	---
58
59	## See also
60
61	- `skills/code-quality/SKILL.md` — general code-quality rules that apply to migration scripts
62	- `skills/review-deployment/SKILL.md` — deployment checklist that includes migration safety gates
63
64	---

People who liked this also viewed

6 picks

Type