git:20260519.bd4658a to git:20260712.a867952

31 added, 25 removed. Audit A to A.

---

name: database-design-engineering

- description: Use when designing or reviewing relational or document-backed data architecture

- for SaaS platforms, ERP systems, APIs, analytics stores, or mobile sync. Covers

- domain modeling, tenancy, indexing, migrations, integrity, retention, and performance

- tradeoffs.

+ description: Use when designing or reviewing relational or document-backed data architecture for SaaS platforms, ERP systems, APIs, analytics stores, or mobile sync. Covers domain modeling, tenancy, indexing, migrations, integrity, retention, and performance tradeoffs.

metadata:

portable: true

compatible_with:

- - Codex

+ - claude-code

- codex

---

# Database Design Engineering

Acknowledgement: Shared by Peter Bamuhigire, techguypeter.com, +256 784 464178.

## Use When

- Use when designing or reviewing relational or document-backed data architecture for SaaS platforms, ERP systems, APIs, analytics stores, or mobile sync. Covers domain modeling, tenancy, indexing, migrations, integrity, retention, and performance tradeoffs.

- - The task needs reusable judgment, domain constraints, or a proven workflow rather than ad hoc advice.

## Do Not Use When

- - The task is unrelated to `database-design-engineering` or would be better handled by a more specific companion skill.

- - The request only needs a trivial answer and none of this skill's constraints or references materially help.

+ - The task is query tuning or production incident diagnosis without a schema-design decision; use the database operations or reliability skill.

+ - The datastore is a search index, cache, or vector store whose source-of-truth semantics belong elsewhere.

## Required Inputs

- - Gather relevant project context, constraints, and the concrete problem to solve; load `references` only as needed.

- - Confirm the desired deliverable: design, code, review, migration plan, audit, or documentation.

+ | Input | Required | Why it matters |

+ |---|---|---|

+ | Domain entities, invariants, and lifecycle rules | yes | Determines tables, keys, constraints, and valid state transitions |

+ | Read and write access patterns | yes | Drives indexes, transaction boundaries, and denormalization choices |

+ | Data volume, growth, and retention expectations | yes | Exposes scaling and archival constraints |

+ | Existing schema and migration constraints | conditional | Enables safe evolution without data loss or prolonged locking |

## Workflow

- - Read this `SKILL.md` first, then load only the referenced deep-dive files that are necessary for the task.

- - Apply the ordered guidance, checklists, and decision rules in this skill instead of cherry-picking isolated snippets.

- - Produce the deliverable with assumptions, risks, and follow-up work made explicit when they matter.

+ Model invariants and ownership, choose keys and relationships, enforce integrity in the database, design indexes from verified access paths, plan reversible expand-contract migrations, then test concurrency, rollback, and representative query plans.

## Quality Standards

- - Keep outputs execution-oriented, concise, and aligned with the repository's baseline engineering standards.

- - Preserve compatibility with existing project conventions unless the skill explicitly requires a stronger standard.

- - Prefer deterministic, reviewable steps over vague advice or tool-specific magic.

- ## Anti-Patterns

- - Treating examples as copy-paste truth without checking fit, constraints, or failure modes.

- - Loading every reference file by default instead of using progressive disclosure.

+ The schema enforces material invariants with types, constraints, and transactions rather than application convention alone. Each index supports a named query, and every migration states lock, backfill, compatibility, rollback, and validation behavior.

## Outputs

- - A concrete result that fits the task: implementation guidance, review findings, architecture decisions, templates, or generated artifacts.

- - Clear assumptions, tradeoffs, or unresolved gaps when the task cannot be completed from available context alone.

- - References used, companion skills, or follow-up actions when they materially improve execution.

+ | Output | Consumer | Acceptance condition |

+ |---|---|---|

+ | Logical and physical schema | Application and data engineers | Identifies ownership, keys, constraints, relationships, types, and intentional denormalization |

+ | Index and transaction plan | Implementers | Links access patterns to indexes and defines concurrency behavior |

+ | Migration packet | Release and operations teams | Includes sequencing, compatibility window, backfill, validation, rollback, and risk notes |

## Evidence Produced

|----------|----------|--------|---------|

## References

- Use the `references/` directory for deep detail after reading the core workflow below.

Use this skill when schema choices will shape correctness, performance, or future maintainability. It complements vendor-specific database skills by focusing on design logic that survives framework changes.

## Load Order

1. Load `world-class-engineering`.

2. Load this skill to design the data model and lifecycle.

3. Load engine-specific parent skills afterward: `mysql-engineering`, `mysql-operations`, `postgresql-engineering`, or `postgresql-operations`.

## Database Workflow

### 1. Model the Domain

Define:

- Core entities and their lifecycles.

- Ownership and tenancy boundaries.

- Transactional invariants.

- Reporting and audit requirements.

- Retention, archival, and deletion rules.

- The release and migration constraints for live data.

Model events and states, not just forms and screens.

### 2. Choose the Storage Shape

Prefer relational design when:

- Consistency matters.

- Multi-table invariants exist.

- Reporting and joins are central.

Prefer document or key-value structures for:

- Flexible metadata.

- Cached projections.

- Denormalized read models or unstructured payloads.

Do not use schemaless storage as a substitute for undecided modeling.

### 3. Define Table Boundaries

- One table should represent one durable business concept or event stream.

- Separate current state from append-only history when auditability matters.

- Distinguish master data, transactions, and derived projections.

- Avoid overloading one table with mutually exclusive concepts.

### 4. Design for Scale and Safety

- Add tenant or ownership keys early.

- Design indexes around real query predicates and sort order.

- Keep writes idempotent where retries are possible.

- Plan archival, purging, and partitioning before large-volume tables arrive.

- Use expand-contract migrations for live systems.

- Separate transactional truth from projections, integrations, and analytical read models.

- Design test-data and backfill strategies for major schema evolution before the first risky migration.

## Core Standards

### Integrity

- Enforce invariants at the strongest reasonable layer: schema, transaction, application, worker.

- Use foreign keys where ownership is mandatory and module boundaries permit it.

- Use append-only ledgers for financial and audit-critical domains.

- Never encode important business rules only in UI validation.

### Tenancy

- Every tenant-scoped table must carry the tenant key.

- Every tenant-scoped query must constrain by tenant key.

- Every unique constraint must reflect tenancy where appropriate.

- Cross-tenant analytics should use controlled derived datasets, not raw shared queries.

### Indexing

- Index for the access path, not for column popularity.

- Prefer composite indexes that match filter plus sort order.

- Remove duplicate or low-value indexes that tax writes.

- Validate index usefulness against actual queries and execution plans.

### Migrations

- Make every migration forward-safe and observable.

- Prefer additive changes, backfills, then cutovers, then cleanup.

- Keep application code compatible across deployment overlap where possible.

- Rehearse destructive or high-volume changes before production.

- Tag migrations with rollback posture: reversible, compensating-only, or forward-fix-only.

- For web projects, provide a root pull-time migration script that reads the app's environment database settings, compares tracked migrations with live migration history, and applies only missing migrations.

- Never bundle seeds into the normal migration-apply path. Demo data, reference seeds, fixtures, and production bootstrap scripts must require a separate explicit command.

### Operable Data Systems

- Emit release markers and migration identifiers into logs and dashboards where possible.

- Track replication lag, queue lag, backfill progress, and lock or saturation risk on critical stores.

- Treat long-running migrations and backfills as operational workflows with owners and stop conditions.

- Document how data correctness will be verified after migration, replay, or recovery.

### Scale, Replication, and Change Data

- Know which queries must read current truth and which can tolerate replica lag.

- Design outbox, CDC, or projection pipelines so integration needs do not corrupt the transactional model.

- Budget storage, index, and retention cost for hot tables before volume becomes painful.

- Validate the hottest access paths with execution plans, cardinality assumptions, and realistic filters.

## Decision Heuristics

Normalize when:

- Data duplication would create correctness risk.

- Multiple workflows update the same fact.

Denormalize when:

- Read performance or query simplicity clearly outweighs duplication cost.

- The source of truth remains obvious.

- Refresh or reconciliation strategy is explicit.

Use soft delete when:

- Recovery, auditability, or legal hold matters.

Use hard delete when:

- Data minimization or cost pressure is stronger and downstream references are safely handled.

## Deliverables

For substantive database work, produce:

- Entity and lifecycle summary.

- Table or collection design.

- Index plan.

- Migration strategy.

- Data verification and rollback posture.

- Data retention and audit plan.

- Top 5 critical queries or access patterns.

- Projection, outbox, or replication notes where external reads or integrations exist.

## Review Checklist

- [ ] Domain entities reflect real business concepts.

- [ ] Tenant and ownership boundaries are explicit.

- [ ] Invariants are protected at durable layers.

- [ ] Indexes match real access paths.

- [ ] Migrations support live deployment safety.

- [ ] Migration verification and rollback posture are explicit.

- [ ] Audit, retention, and archival rules are documented.

- [ ] Reporting needs do not distort the transactional model without justification.

- [ ] Replica lag, projections, or CDC assumptions are explicit where they matter.

## References

- [references/data-review-checklist.md](references/data-review-checklist.md): Schema and migration review prompts.

- [references/live-data-evolution.md](references/live-data-evolution.md): Expand-contract, backfills, verification, and rollback posture.

- - [../world-class-engineering/references/source-patterns.md](../world-class-engineering/references/source-patterns.md): Source-derived patterns for design quality and website/data analysis.

+ - [../../sdlc-meta/world-class-engineering/references/source-patterns.md](../../sdlc-meta/world-class-engineering/references/source-patterns.md): Source-derived patterns for design quality and website/data analysis.

+ ## Capability contract

+ Design schemas and migration plans by default. Apply DDL, backfills, retention jobs, or production queries only with explicit database and environment authority.

+ ## Degraded mode

+ If workload samples or schema access are unavailable, provide a read-only logical design and mark index, capacity, and migration claims unverified.

+ ## Domain Anti-Patterns

+ - Omitting foreign-key or application integrity rules. Fix: state enforcement explicitly.

+ - Indexing every column. Fix: derive indexes from measured access paths.

+ - Mixing tenant data without a tenant key. Fix: enforce isolation in schema and queries.

+ - Shipping destructive migrations in one step. Fix: use expand-contract.

+ - Retaining data indefinitely. Fix: define retention and deletion ownership.