elct9620/schema

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 |