Related Skills
- Designing the domain objects that the schema persists? → Use domain-modeling
- Schema change requires code restructuring? → Use refactoring
- Unsure which architectural layer the DTO belongs in? → Use clean-architecture
Applicability Rubric
| Condition |
Pass |
Fail |
| Database structure change |
Adding/modifying tables, columns, or indexes |
No persistence changes |
| API contract work |
Designing or changing request/response shapes |
No API boundary changes |
| Data boundary crossing |
Serialization/deserialization between layers |
Data stays within single layer |
| Schema restructuring |
Normalizing, denormalizing, or optimizing existing schemas |
No structural changes needed |
Apply when: Any condition passes
Core Principles
Schema Design Principles
| Principle |
Description |
Violation Sign |
| Single Source of Truth |
Each fact stored exactly once |
Same data duplicated across tables without clear denormalization rationale |
| Explicit Contracts |
API shapes and DB schemas defined clearly |
Implicit structures, untyped fields, or missing documentation |
| Boundary Alignment |
Schema matches system boundary responsibilities |
Schema leaks internal details or couples unrelated concerns |
| Minimal Exposure |
Expose only what consumers need |
Internal fields visible in API responses, over-fetching by default |
Schema Types
| Type |
Clean Architecture Layer |
Purpose |
Boundary with Domain Modeling |
| Database Schema |
Infrastructure |
Persist and query data efficiently |
Translates domain entities into storage format |
| API Contract |
Interface Adapters |
Define external communication shape |
Maps domain output to external representation |
| DTO |
Interface Adapters |
Transfer data between layers |
Decouples domain model from transport format |
Database Schema Decisions
Normalization vs. Denormalization
| Factor |
Normalize |
Denormalize |
| Write frequency |
High writes, frequent updates |
Read-heavy, rare updates |
| Data consistency |
Critical consistency required |
Eventual consistency acceptable |
| Query complexity |
Simple joins acceptable |
Complex joins causing performance issues |
| Storage |
Minimize redundancy |
Trade storage for speed |
Index Selection Guide
| Scenario |
Index Type |
When to Use |
| Exact lookups |
B-tree (default) |
Primary keys, foreign keys, unique constraints |
| Text search |
Full-text / GIN |
Search within text content |
| Range queries |
B-tree |
Date ranges, numeric ranges |
| Multi-column filter |
Composite |
Frequently queried column combinations |
Avoiding Redundant Indexes
Before adding an index, check whether existing indexes already cover the query:
| Redundant |
Why |
Covered By |
| INDEX on (a, b) when PK is (a, b) |
PK already creates a B-tree index |
Primary key |
| INDEX on (a) when composite index (a, b) exists |
Leading prefix of composite index covers single-column lookups |
Composite index |
| INDEX on (a, b, c) identical to PK (a, b, c) |
Exact duplicate |
Primary key |
| UNIQUE constraint + separate INDEX on same columns |
UNIQUE already creates an index |
Unique constraint |
| INDEX on (a) when UNIQUE on (a, b) exists |
Leading prefix of unique index covers single-column lookups |
Unique constraint's B-tree |
Rule of thumb: Every index has write-cost overhead. Only add an index when you have a specific query pattern it serves that isn't already covered by existing PKs, unique constraints, or composite indexes with matching leading columns.
API Contract Design
API Design Principles
| Principle |
REST |
GraphQL / RPC |
| Structure |
Model endpoints around resources |
Model around operations or queries |
| Naming |
Plural nouns, consistent casing |
Verb-based or domain-aligned naming |
| Status/errors |
Match HTTP status codes to outcomes |
Use typed error responses |
| Pagination |
Paginate collection endpoints |
Use cursor-based pagination |
| Consistency |
Uniform error format across endpoints |
Uniform error format across operations |
Versioning Strategy
| Strategy |
Pros |
Cons |
Best For |
URL path (/v1/) |
Simple, explicit |
URL pollution |
Public APIs with clear major versions |
| Header-based |
Clean URLs |
Less discoverable |
Internal APIs with frequent changes |
| Query parameter |
Easy to test |
Caching complexity |
Transitional versioning |
Schema vs. Domain Modeling
| Aspect |
Schema (this skill) |
Domain Modeling |
| Focus |
Data structure at boundaries |
Business concepts and rules |
| Layer |
Infrastructure / Interface Adapters |
Domain (Entities) |
| Artifacts |
Tables, columns, indexes, API contracts, DTOs |
Entities, Value Objects, Aggregates |
| Question answered |
How is data stored and transmitted? |
What are the business rules and concepts? |
Completion Rubric
Before
| Criterion |
Pass |
Fail |
| Purpose confirmed |
Schema purpose and consumers identified |
Unclear who uses this schema or why |
| Existing structure reviewed |
Current schema/contracts examined |
Designing without knowing existing state |
| Naming convention |
Follows project naming conventions |
Inconsistent or arbitrary naming |
| Backward compatibility |
Breaking changes identified and planned |
Unaware of downstream impact |
During
| Criterion |
Pass |
Fail |
| Domain model independence |
Schema can change without altering domain |
Domain entities shaped by storage format |
| Constraint correctness |
NOT NULL, foreign keys, unique constraints match business rules |
Missing constraints or overly permissive schema |
| Contract completeness |
All required fields, types, and validation documented |
Partial or ambiguous contract definition |
| Minimal exposure |
Only necessary fields exposed to consumers |
Internal details leaked through API or schema |
After
| Criterion |
Pass |
Fail |
| Contract verified |
API contracts tested with request/response examples |
No contract verification |
| No domain leakage |
Storage/transport details absent from domain layer |
Domain layer references DB columns or API fields |
| Documentation updated |
Schema changes reflected in docs/migrations |
Undocumented schema changes |
Converted and distributed by TomeVault — claim your Tome and manage your conversions.
1---2name: schema3description: Design database schemas and API contracts at system boundaries. Use when creating or modifying database tables, API endpoints, or data serialization formats. Make sure to use this skill whenever the user adds database migrations, designs REST/GraphQL APIs, creates DTOs, defines request/response shapes, or works on data serialization — even for adding a single column or endpoint. Use when this capability is needed.4---56## Related Skills78- Designing the domain objects that the schema persists? → Use **domain-modeling**9- Schema change requires code restructuring? → Use **refactoring**10- Unsure which architectural layer the DTO belongs in? → Use **clean-architecture**1112## Applicability Rubric1314| Condition | Pass | Fail |15|-----------|------|------|16| Database structure change | Adding/modifying tables, columns, or indexes | No persistence changes |17| API contract work | Designing or changing request/response shapes | No API boundary changes |18| Data boundary crossing | Serialization/deserialization between layers | Data stays within single layer |19| Schema restructuring | Normalizing, denormalizing, or optimizing existing schemas | No structural changes needed |2021**Apply when**: Any condition passes2223## Core Principles2425### Schema Design Principles2627| Principle | Description | Violation Sign |28|-----------|-------------|----------------|29| Single Source of Truth | Each fact stored exactly once | Same data duplicated across tables without clear denormalization rationale |30| Explicit Contracts | API shapes and DB schemas defined clearly | Implicit structures, untyped fields, or missing documentation |31| Boundary Alignment | Schema matches system boundary responsibilities | Schema leaks internal details or couples unrelated concerns |32| Minimal Exposure | Expose only what consumers need | Internal fields visible in API responses, over-fetching by default |3334### Schema Types3536| Type | Clean Architecture Layer | Purpose | Boundary with Domain Modeling |37|------|--------------------------|---------|-------------------------------|38| Database Schema | Infrastructure | Persist and query data efficiently | Translates domain entities into storage format |39| API Contract | Interface Adapters | Define external communication shape | Maps domain output to external representation |40| DTO | Interface Adapters | Transfer data between layers | Decouples domain model from transport format |4142### Database Schema Decisions4344#### Normalization vs. Denormalization4546| Factor | Normalize | Denormalize |47|--------|-----------|-------------|48| Write frequency | High writes, frequent updates | Read-heavy, rare updates |49| Data consistency | Critical consistency required | Eventual consistency acceptable |50| Query complexity | Simple joins acceptable | Complex joins causing performance issues |51| Storage | Minimize redundancy | Trade storage for speed |5253#### Index Selection Guide5455| Scenario | Index Type | When to Use |56|----------|------------|-------------|57| Exact lookups | B-tree (default) | Primary keys, foreign keys, unique constraints |58| Text search | Full-text / GIN | Search within text content |59| Range queries | B-tree | Date ranges, numeric ranges |60| Multi-column filter | Composite | Frequently queried column combinations |6162#### Avoiding Redundant Indexes6364Before adding an index, check whether existing indexes already cover the query:6566| Redundant | Why | Covered By |67|-----------|-----|------------|68| INDEX on (a, b) when PK is (a, b) | PK already creates a B-tree index | Primary key |69| INDEX on (a) when composite index (a, b) exists | Leading prefix of composite index covers single-column lookups | Composite index |70| INDEX on (a, b, c) identical to PK (a, b, c) | Exact duplicate | Primary key |71| UNIQUE constraint + separate INDEX on same columns | UNIQUE already creates an index | Unique constraint |72| INDEX on (a) when UNIQUE on (a, b) exists | Leading prefix of unique index covers single-column lookups | Unique constraint's B-tree |7374**Rule of thumb**: Every index has write-cost overhead. Only add an index when you have a specific query pattern it serves that isn't already covered by existing PKs, unique constraints, or composite indexes with matching leading columns.7576### API Contract Design7778#### API Design Principles7980| Principle | REST | GraphQL / RPC |81|-----------|------|---------------|82| Structure | Model endpoints around resources | Model around operations or queries |83| Naming | Plural nouns, consistent casing | Verb-based or domain-aligned naming |84| Status/errors | Match HTTP status codes to outcomes | Use typed error responses |85| Pagination | Paginate collection endpoints | Use cursor-based pagination |86| Consistency | Uniform error format across endpoints | Uniform error format across operations |8788#### Versioning Strategy8990| Strategy | Pros | Cons | Best For |91|----------|------|------|----------|92| URL path (`/v1/`) | Simple, explicit | URL pollution | Public APIs with clear major versions |93| Header-based | Clean URLs | Less discoverable | Internal APIs with frequent changes |94| Query parameter | Easy to test | Caching complexity | Transitional versioning |9596### Schema vs. Domain Modeling9798| Aspect | Schema (this skill) | Domain Modeling |99|--------|---------------------|-----------------|100| Focus | Data structure at boundaries | Business concepts and rules |101| Layer | Infrastructure / Interface Adapters | Domain (Entities) |102| Artifacts | Tables, columns, indexes, API contracts, DTOs | Entities, Value Objects, Aggregates |103| Question answered | How is data stored and transmitted? | What are the business rules and concepts? |104105## Completion Rubric106107### Before108109| Criterion | Pass | Fail |110|-----------|------|------|111| Purpose confirmed | Schema purpose and consumers identified | Unclear who uses this schema or why |112| Existing structure reviewed | Current schema/contracts examined | Designing without knowing existing state |113| Naming convention | Follows project naming conventions | Inconsistent or arbitrary naming |114| Backward compatibility | Breaking changes identified and planned | Unaware of downstream impact |115116### During117118| Criterion | Pass | Fail |119|-----------|------|------|120| Domain model independence | Schema can change without altering domain | Domain entities shaped by storage format |121| Constraint correctness | NOT NULL, foreign keys, unique constraints match business rules | Missing constraints or overly permissive schema |122| Contract completeness | All required fields, types, and validation documented | Partial or ambiguous contract definition |123| Minimal exposure | Only necessary fields exposed to consumers | Internal details leaked through API or schema |124125### After126127| Criterion | Pass | Fail |128|-----------|------|------|129| Contract verified | API contracts tested with request/response examples | No contract verification |130| No domain leakage | Storage/transport details absent from domain layer | Domain layer references DB columns or API fields |131| Documentation updated | Schema changes reflected in docs/migrations | Undocumented schema changes |132133---134> Converted and distributed by [TomeVault](https://tomevault.io/claim/elct9620) — claim your Tome and manage your conversions.135<!-- tomevault:4.0:skill_md:2026-04-13 -->
tomevault-io