database-patterns

The database-patterns skill provides a robust framework for designing and interacting with persistent data layers in modern software applications. It is intended for software engineers and architects who need to ensure data integrity, performance, and scalability while managing relational or NoSQL database systems. This skill acts as a technical advisor for the entire database lifecycle, from initial normalization and schema modeling to the implementation of production-grade data access layers.

Key features and capabilities:

• Schema design assistance covering normalization levels (1NF to 3NF) and denormalization strategies for read-heavy analytical workloads.
• Support for common table patterns including soft deletes, audit columns, and complex relationship modeling (one-to-many, many-to-many, and hierarchical self-referential structures).
• Advanced indexing strategies including B-tree, Hash, GIN for JSONB/Full-text, and GiST for geometric or range data types.
• Query performance optimization, including efficient pagination (cursor vs. keyset), upsert logic, and bulk operation management to reduce latency.
• Boilerplate implementation for the repository pattern in TypeScript, ensuring a clean separation between business logic and database-specific query implementation.
• Transaction pattern guidance to prevent race conditions using locking mechanisms (e.g., SELECT FOR UPDATE) and atomic operations.

Practical tips and constraints:

• Use this skill when initiating new database schemas, refactoring legacy tables, or debugging performance bottlenecks.
• Provide context on the database engine (PostgreSQL, MySQL, MongoDB) to receive dialect-specific SQL or query implementation advice.
• When optimizing indexes, consider the tradeoff between read performance and write overhead; avoid indexing low-cardinality columns or frequently updated fields.
• Always define interfaces for repository classes to facilitate unit testing and potential database driver abstraction.
• Expect outputs to include optimized SQL statements, schema migration suggestions, and clean, type-safe data access code snippets.