schema

name	schema
description	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.

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 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	Schemas defined clearly, with non-obvious values (status codes, control bytes, enum semantics) described inline at the boundary	Implicit structures, untyped fields, or values that require service code to interpret
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