AGENT SKILL

database-designer

Use when the user asks to design database schemas, plan data migrations, optimize queries, choose between SQL and NoSQL, or model data relationships.

License 许可
MIT
最近更新
2026-07-14
Stars
★ 0
Forks
0

01 前言 Front Matter

name
database-designer
description
Use when the user asks to design database schemas, plan data migrations, optimize queries, choose between SQL and NoSQL, or model data relationships.

02 资源文件 14

03 SKILL.md 正文

Database Designer - POWERFUL Tier Skill

Overview

A comprehensive database design skill that provides expert-level analysis, optimization, and migration capabilities for modern database systems. This skill combines theoretical principles with practical tools to help architects and developers create scalable, performant, and maintainable database schemas.

Core Competencies

Schema Design & Analysis

  • Normalization Analysis: Automated detection of normalization levels (1NF through BCNF)
  • Denormalization Strategy: Smart recommendations for performance optimization
  • Data Type Optimization: Identification of inappropriate types and size issues
  • Constraint Analysis: Missing foreign keys, unique constraints, and null checks
  • Naming Convention Validation: Consistent table and column naming patterns
  • ERD Generation: Automatic Mermaid diagram creation from DDL

Index Optimization

  • Index Gap Analysis: Identification of missing indexes on foreign keys and query patterns
  • Composite Index Strategy: Optimal column ordering for multi-column indexes
  • Index Redundancy Detection: Elimination of overlapping and unused indexes
  • Performance Impact Modeling: Selectivity estimation and query cost analysis
  • Index Type Selection: B-tree, hash, partial, covering, and specialized indexes

Migration Management

  • Zero-Downtime Migrations: Expand-contract pattern implementation
  • Schema Evolution: Safe column additions, deletions, and type changes
  • Data Migration Scripts: Automated data transformation and validation
  • Rollback Strategy: Complete reversal capabilities with validation
  • Execution Planning: Ordered migration steps with dependency resolution

Database Design Principles

→ See references/database-design-reference.md for details

Best Practices

Schema Design

  1. Use meaningful names: Clear, consistent naming conventions
  2. Choose appropriate data types: Right-sized columns for storage efficiency
  3. Define proper constraints: Foreign keys, check constraints, unique indexes
  4. Consider future growth: Plan for scale from the beginning
  5. Document relationships: Clear foreign key relationships and business rules

Performance Optimization

  1. Index strategically: Cover common query patterns without over-indexing
  2. Monitor query performance: Regular analysis of slow queries
  3. Partition large tables: Improve query performance and maintenance
  4. Use appropriate isolation levels: Balance consistency with performance
  5. Implement connection pooling: Efficient resource utilization

Security Considerations

  1. Principle of least privilege: Grant minimal necessary permissions
  2. Encrypt sensitive data: At rest and in transit
  3. Audit access patterns: Monitor and log database access
  4. Validate inputs: Prevent SQL injection attacks
  5. Regular security updates: Keep database software current

Query Generation Patterns

SELECT with JOINs

-- INNER JOIN: only matching rows
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

-- LEFT JOIN: all left rows, NULLs for non-matches
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

-- Self-join: hierarchical data (employees/managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Common Table Expressions (CTEs)

-- Recursive CTE for org chart
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e INNER JOIN org o ON o.id = e.manager_id
)
SELECT * FROM org ORDER BY depth, name;

Window Functions

-- ROW_NUMBER for pagination / dedup
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;

-- RANK with gaps, DENSE_RANK without gaps
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard;

-- LAG/LEAD for comparing adjacent rows
SELECT date, revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

Aggregation Patterns

-- FILTER clause (PostgreSQL) for conditional aggregation
SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'active') AS active,
  AVG(amount) FILTER (WHERE amount > 0) AS avg_positive
FROM accounts;

-- GROUPING SETS for multi-level rollups
SELECT region, product, SUM(revenue)
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), ());

Migration Patterns

Up/Down Migration Scripts

Every migration must have a reversible counterpart. Name files with a timestamp prefix for ordering:

migrations/
├── 20260101_000001_create_users.up.sql
├── 20260101_000001_create_users.down.sql
├── 20260115_000002_add_users_email_index.up.sql
└── 20260115_000002_add_users_email_index.down.sql

Zero-Downtime Migrations (Expand/Contract)

Use the expand-contract pattern to avoid locking or breaking running code:

  1. Expand — add the new column/table (nullable, with default)
  2. Migrate data — backfill in batches; dual-write from application
  3. Transition — application reads from new column; stop writing to old
  4. Contract — drop old column in a follow-up migration

Data Backfill Strategies

-- Batch update to avoid long-running locks
UPDATE users SET email_normalized = LOWER(email)
WHERE id IN (SELECT id FROM users WHERE email_normalized IS NULL LIMIT 5000);
-- Repeat in a loop until 0 rows affected

Rollback Procedures

  • Always test the down.sql in staging before deploying up.sql to production
  • Keep rollback window short — if the contract step has run, rollback requires a new forward migration
  • For irreversible changes (dropping columns with data), take a logical backup first

Performance Optimization

Indexing Strategies

Index Type Use Case Example
B-tree (default) Equality, range, ORDER BY CREATE INDEX idx_users_email ON users(email);
GIN Full-text search, JSONB, arrays CREATE INDEX idx_docs_body ON docs USING gin(to_tsvector('english', body));
GiST Geometry, range types, nearest-neighbor CREATE INDEX idx_locations ON places USING gist(coords);
Partial Subset of rows (reduce size) CREATE INDEX idx_active ON users(email) WHERE active = true;
Covering Index-only scans CREATE INDEX idx_cov ON orders(customer_id) INCLUDE (total, created_at);

EXPLAIN Plan Reading

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

Key signals to watch:

  • Seq Scan on large tables — missing index
  • Nested Loop with high row estimates — consider hash/merge join or add index
  • Buffers shared read much higher than hit — working set exceeds memory

N+1 Query Detection

Symptoms: application issues one query per row (e.g., fetching related records in a loop).

Fixes:

  • Use JOIN or subquery to fetch in one round-trip
  • ORM eager loading (select_related / includes / with)
  • DataLoader pattern for GraphQL resolvers

Connection Pooling

Tool Protocol Best For
PgBouncer PostgreSQL Transaction/statement pooling, low overhead
ProxySQL MySQL Query routing, read/write splitting
Built-in pool (HikariCP, SQLAlchemy pool) Any Application-level pooling

Rule of thumb: Set pool size to (2 * CPU cores) + disk spindles. For cloud SSDs, start with 2 * vCPUs and tune.

Read Replicas and Query Routing

  • Route all SELECT queries to replicas; writes to primary
  • Account for replication lag (typically <1s for async, 0 for sync)
  • Use pg_last_wal_replay_lsn() to detect lag before reading critical data

Multi-Database Decision Matrix

Criteria PostgreSQL MySQL SQLite SQL Server
Best for Complex queries, JSONB, extensions Web apps, read-heavy workloads Embedded, dev/test, edge Enterprise .NET stacks
JSON support Excellent (JSONB + GIN) Good (JSON type) Minimal Good (OPENJSON)
Replication Streaming, logical Group replication, InnoDB cluster N/A Always On AG
Licensing Open source (PostgreSQL License) Open source (GPL) / commercial Public domain Commercial
Max practical size Multi-TB Multi-TB ~1 TB (single-writer) Multi-TB

When to choose:

  • PostgreSQL — default choice for new projects; best extensibility and standards compliance
  • MySQL — existing MySQL ecosystem; simple read-heavy web applications
  • SQLite — mobile apps, CLI tools, unit test databases, IoT/edge
  • SQL Server — mandated by enterprise policy; deep .NET/Azure integration

NoSQL Considerations

Database Model Use When
MongoDB Document Schema flexibility, rapid prototyping, content management
Redis Key-value / cache Session store, rate limiting, leaderboards, pub/sub
DynamoDB Wide-column Serverless AWS apps, single-digit-ms latency at any scale

Use SQL as default. Reach for NoSQL only when the access pattern clearly benefits from it.


Sharding & Replication

Horizontal vs Vertical Partitioning

  • Vertical partitioning: Split columns across tables (e.g., separate BLOB columns). Reduces I/O for narrow queries.
  • Horizontal partitioning (sharding): Split rows across databases/servers. Required when a single node cannot hold the dataset or handle the throughput.

Sharding Strategies

Strategy How It Works Pros Cons
Hash shard = hash(key) % N Even distribution Resharding is expensive
Range Shard by date or ID range Simple, good for time-series Hot spots on latest shard
Geographic Shard by user region Data locality, compliance Cross-region queries are hard

Replication Patterns

Pattern Consistency Latency Use Case
Synchronous Strong Higher write latency Financial transactions
Asynchronous Eventual Low write latency Read-heavy web apps
Semi-synchronous At-least-one replica confirmed Moderate Balance of safety and speed

Cross-References

  • sql-database-assistant — query writing, optimization, and debugging for day-to-day SQL work
  • database-schema-designer — ERD modeling, normalization analysis, and schema generation
  • migration-architect — large-scale migration planning across database engines or major schema overhauls
  • senior-backend — application-layer patterns (connection pooling, ORM best practices)
  • senior-devops — infrastructure provisioning for database clusters and replicas

Conclusion

Effective database design requires balancing multiple competing concerns: performance, scalability, maintainability, and business requirements. This skill provides the tools and knowledge to make informed decisions throughout the database lifecycle, from initial schema design through production optimization and evolution.

The included tools automate common analysis and optimization tasks, while the comprehensive guides provide the theoretical foundation for making sound architectural decisions. Whether building a new system or optimizing an existing one, these resources provide expert-level guidance for creating robust, scalable database solutions.

04 同仓库更多 Skills

aneeba-pixel
aneeba-pixel/claude-skills

syllabus

Generates a curated supplementary reading list from any course syllabus using Consensus academic search. Grill-me intake (syllabus input format + course audience + year range) plus a grouping forcing-options checkpoint before any search runs — so the reading list matches the course's level and recency need. Parses the syllabus to extract topics and learning outcomes, searches Consensus for recent peer-reviewed papers per topic, and produces a professionally formatted .docx with clickable Consensus links, plain-language summaries calibrated to audience level, and Bloom-higher-order discussion questions tied to course learning goals. Triggers whenever a user uploads a syllabus, course outline, or curriculum document and wants supplementary readings. Also triggers on: 'syllabus reading list', 'find papers for my course', 'create a reading list from this syllabus', 'recent research for my class', 'supplementary readings', 'find journal articles for these topics', 'what recent papers cover this material', 'any new research on these course topics', 'update my syllabus with recent papers'. Even casual mentions when a syllabus is attached should trigger this skill.

★ 0
aneeba-pixel
aneeba-pixel/claude-skills

research

Default entry point for any research request — a hybrid router that classifies the question deterministically and either delegates to a specialist research skill (pulse for trends/sentiment, grants for NIH funding, litreview for academic literature, syllabus for course reading, patent for prior-art + IP landscape, dossier for entity research) or runs its own plan-decompose-multi-source-search-synthesize-cite fallback workflow when no specialist matches. Always surfaces the routing decision so users can override. Triggers — "research [topic]", "look into [topic]", "what do we know about [topic]", "investigate [topic]", "find me information on [topic]", "do some research on [topic]", "I need to understand [topic]", or any research request that doesn't obviously match a more-specific specialist skill. Output is a markdown briefing (default) or .docx document (on request) with full citations and an audit log.

★ 0
aneeba-pixel
aneeba-pixel/claude-skills

pulse

Multi-source recency research skill that takes the pulse of any topic across Reddit, Hacker News, the open web, and optionally X/Twitter within a configurable recent window (default 30 days). Forcing intake clarifies topic specificity, angle (trend/sentiment/problems/opportunities/comparison), time window, and platform scope before searching. Returns a synthesized briefing with citations, engagement metrics, and cross-platform pattern analysis. Triggers: 'pulse on [topic]', 'what's happening with [topic]', 'what are people saying about [topic]', 'current conversation about [topic]', 'take the pulse of [topic]', 'trending: [topic]', 'find me info on [topic]', or any variation requesting multi-source recency intelligence on a topic. Also use for competitor research, trend discovery, tool comparisons, and audience sentiment analysis.

★ 0
aneeba-pixel
aneeba-pixel/claude-skills

patent

Patent prior-art and landscape intelligence skill — not generic patent help. Commits to one of five sub-use-cases via forcing intake (novelty search / freedom-to-operate / competitive landscape / acquisition diligence / litigation prior-art) before any search runs. Searches Google Patents, Espacenet, USPTO, and optionally Lens.org for citation-graph signals. Output is an editable Word document (.docx) with verdict, ranked closest art (claim-text extracted), CPC-class-aware landscape, family-resolved hits, geographic coverage, FTO flags where applicable, strategy recommendations, and full audit log. Triggers: 'prior art search for [invention]', 'patent search on [topic]', 'freedom to operate analysis', 'FTO for [product]', 'patent landscape for [field]', 'is [invention] novel', 'patents on [topic]', 'competitive patent analysis', 'prior art for litigation', 'patent diligence on [company]'. Produces search signal, not legal advice — always recommends consulting a patent attorney before filing or licensing decisions. Trademark, copyright, and trade-secret questions are out of scope.

★ 0
aneeba-pixel
aneeba-pixel/claude-skills

notebooklm

Browser automation skill for controlling Google's NotebookLM. Handles reading and querying notebooks, adding sources (URLs, text, files, YouTube links, synthesized content), generating Studio outputs (Audio Overview, infographics, slide decks, study guides, briefing docs, mind maps, timelines, FAQs), and creating new notebooks. Triggers on any phrase involving NotebookLM — 'open NotebookLM', 'check my [name] notebook', 'pull info from NotebookLM', 'ask my notebook about X', 'add [source] to NotebookLM', 'create an infographic in NotebookLM', 'use NotebookLM Studio', 'generate a slide deck from my notebook', or any variation where the goal involves NotebookLM. Requires browser automation environment — fails gracefully when unavailable.

★ 0
aneeba-pixel
aneeba-pixel/claude-skills

litreview

Academic literature orientation skill that searches papers via Consensus, builds a strategic search plan using PICO (default) or SPIDER / Decomposition / hybrid as fallbacks, and synthesizes findings into a professionally formatted Word document (.docx) research guide. Grill-me intake (research question specificity + framework hint + tentative depth) before the recon search; a second forcing checkpoint after Phase 2 confirms framework + sub-areas + depth before searches consume budget. Configurable depth (5/10/20 queries) controls coverage vs. speed. Output is a 'launching pad' — not a finished review, but an orientation guide that lets a researcher dive in confidently. Triggers: 'litreview on [topic]', 'literature review on [topic]', 'I'm starting a literature review on X', 'I'm writing a paper on X', 'help me research X', 'I'm doing research on X', 'can you help me research X'. Do NOT trigger for single one-off paper searches where the user just wants a quick list — that's a plain Consensus search.

★ 0