sql-database-patterns
- Repo stars 0
- Author updated Live
- Author repo skills-registry
- Domain
- Security
- Compatible agents
-
- Claude Code
- Cursor
- Cline
- Codex
- Windsurf
- Gemini CLI
- +20
- Trust score
- 88 / 100 · community maintained
- Author / version / license
- @tomevault-io · no license declared
- Token usage
- Lean
- Setup complexity
- Manual integration
- External API key
- Not required
- Operating systems
- Windows
- Runtime requirements
- Node.js · Python
- Permissions
-
- Read-only
- Write / modify
- Shell exec
- Env read
- Network behavior
- Local-only
- Install commands
- 26 variants
Profile is derived at build time from SKILL.md and install vectors. Subject to drift from author intent.
Heads up: 未限定 allowed-tools,默认拥有全部工具权限。
---
name: sql-database-patterns
description: Use this skill when the agent is designing schemas, reviewing migrations, tuning queries, modeli…
category: security
runtime: Node.js / Python
---
# sql-database-patterns output preview
## PART A: Task fit
- Use case: Use this skill when the agent is designing schemas, reviewing migrations, tuning queries, modeling NoSQL access patterns, configuring replication, planning partitioning, auditing indexes, or onboarding to database conventions — the database patterns knowledge base for PostgreSQL, MySQL, SQL Server, MongoDB, Redis, Cassandra, DynamoDB engines, schema desig….
- Inputs: target material, constraints, expected output, and acceptance criteria.
- Evidence boundary: follow “When to Use / When NOT to Use / SQL Database Engines” and do not present inference as author intent.
## PART B: Execution result
- **01** The card summarizes the use case; runtime output centers on “Use this skill when the agent is designing schemas, reviewing migrations, tuning queries, modeling NoSQL access patterns, configuring replication, planning partitioning, auditing indexes, or onboarding to database conventions — the database patterns knowledge base for PostgreSQL, MySQL, SQL Server, MongoDB, Redis, Cassandra, DynamoDB engines, schema desig…”.
- **02** When the source has headings, the agent prioritizes “When to Use / When NOT to Use / SQL Database Engines” so the result follows the author’s structure.
- **03** Typical output includes task judgment, concrete steps, required commands or file edits, validation, and follow-up options.
- **04** Risk context follows the fingerprint: read files, write/modify files, run shell commands, read environment variables; mostly runs locally; usually needs no extra API key.
## Running Rules
- read files, write/modify files, run shell commands, read environment variables; mostly runs locally; usually needs no extra API key.
- Validate with a small sample before expanding scope.
- Return the result, validation criteria, and next iteration options. The source mentions slash commands such as `/develop`, `/code-review`, `/architecture-design`, `/migrate`, `/bugfix`; use them first when your agent supports command triggers.
Name target files or source material, expected output, forbidden changes, and whether network or shell access is allowed. Permission fingerprint: read files, write/modify files, run shell commands, read environment variables.
Start with a small task and check whether the result follows “When to Use / When NOT to Use / SQL Database Engines”. Inspect diffs, logs, previews, or tests before expanding scope.
Confirm the final output includes a concrete result, evidence, and next action. If it stays generic, tighten inputs, boundaries, and acceptance criteria.
---
name: sql-database-patterns
description: Use this skill when the agent is designing schemas, reviewing migrations, tuning queries, modeli…
category: security
source: tomevault-io/skills-registry
---
# sql-database-patterns
## When to use
- Use this skill when the agent is designing schemas, reviewing migrations, tuning queries, modeling NoSQL access patter…
- Use it when the task has clear inputs, repeatable steps, and validation criteria.
## What to provide
- Target material, scope, expected result, and forbidden changes.
- Whether network, commands, file writes, or external services are allowed.
## Execution rules
- Organize steps around “When to Use / When NOT to Use / SQL Database Engines” and keep inference separate from source facts.
- read files, write/modify files, run shell commands, read environment variables; mostly runs locally; usually needs no extra API key.
- Validate with a small sample before expanding the task.
## Output requirements
- Return the deliverable, key evidence, validation method, and next action.
- Mark missing information as unknown; do not invent commands, platforms, or dependencies. The author source anchors workflow facts; repository files anchor sources and commands; Fluxly only adds fit, limitations, and quality judgment.
skill "sql-database-patterns" {
input -> user goal + target files + boundaries + acceptance criteria
context -> When to Use / When NOT to Use / SQL Database Engines
rules -> SKILL.md triggers / order / output contract
runtime -> Node.js / Python | read files, write/modify files, run shell commands, read environment variables | mostly runs locally
guardrails -> usually needs no extra API key + small-sample validation + diff/log review
output -> copyable result + checklist + next iteration
} Database Patterns
Reference knowledge for relational and NoSQL database engineering — schema design, query optimization, indexing, partitioning, replication, NoSQL modeling, monitoring, and backup.
When to Use
- Designing or evolving a schema; reviewing a migration PR
- Tuning a slow query (EXPLAIN, indexing)
- Modeling access patterns in MongoDB, DynamoDB, Cassandra, Redis
- Planning partitioning, sharding, or replication topology
- Configuring monitoring, slow-query logging, vacuum/bloat checks
- Designing backup, restore, RTO/RPO strategy
When NOT to Use
- ORM-layer modeling in app code →
python-engineer/java-engineer(reference this skill) - Data pipelines / warehousing →
data-engineer - Database infra provisioning →
devops-engineer
SQL Database Engines
PostgreSQL
timestamptznottimestamp;textnotvarchar;numeric(p,s)for moneypg_stat_statements,pg_stat_user_tables,pg_stat_activityfor diagnosticspgauditfor audit trail;pgcryptofor column-level encryptionpg_repackfor online bloat reorganization;REINDEX CONCURRENTLYfor index bloat- Streaming replication: async for read replicas, sync for zero data loss
- PITR via
pg_basebackup+ WAL archiving - Failover: Patroni,
pg_auto_failover, or cloud-managed
MySQL
- InnoDB default; check
innodb_buffer_pool_sizesizing (60-80% RAM) - Use
EXPLAIN FORMAT=JSONfor execution plan;performance_schemafor diagnostics - Slow query log:
long_query_time,log_queries_not_using_indexes - Replication: row-based (RBR) default; GTID for failover; semi-sync for durability
- ProxySQL for read/write split and connection pooling
- Avoid
utf8(3-byte) — useutf8mb4(4-byte) for full Unicode
SQL Server
- Clustered index = physical row order; pick narrow, increasing, unique key
- Statistics auto-updated; use
UPDATE STATISTICSafter bulk loads sys.dm_exec_query_stats,sys.dm_db_missing_index_detailsfor tuning- Always On Availability Groups for HA; log shipping for DR
- Use
OPTION (RECOMPILE)sparingly — parameter sniffing is usually preferable - TDE for encryption at rest; Always Encrypted for column-level
Schema Design
- Normalization: 3NF by default. Denormalize only with measured read-performance justification
- Primary keys: Surrogate (
BIGSERIAL, UUID) for most tables. Natural keys only when immutable and unique - Foreign keys: Always explicit ON DELETE/ON UPDATE. CASCADE for child lifecycle, RESTRICT for references, SET NULL for optional
- Naming:
snake_case, plural tables (users,orders). FK:{table_singular}_id - Constraints: NOT NULL by default. CHECK for value ranges. UNIQUE for business keys
- Data types: Most specific type for storage + correctness
- Audit columns:
created_at timestamptz DEFAULT now(),updated_at timestamptzon every table
SQL Optimization
- EXPLAIN ANALYZE: Always use actual execution. Compare estimated vs actual rows — gaps = stale statistics
- Seq scan elimination: Add indexes. Check
work_memfor hash joins and sorts - Join order: Optimizer handles most cases. 10+ joins → use
join_collapse_limitor explicit order - CTEs: PG 12+ inlines by default. Use
MATERIALIZEDonly for optimization fences - Subquery vs JOIN: Prefer JOIN for correlated subqueries. EXISTS over IN for large subsets
- Batch ops:
INSERT ... ON CONFLICT(upsert).COPYfor bulk. Avoid row-by-row loops - Locking:
FOR UPDATE SKIP LOCKEDfor queues.NOWAITfor fast fail. Advisory locks for app coordination - Statistics:
ANALYZEafter bulk loads. Increasedefault_statistics_targetfor skewed distributions - No
SELECT *in production code — specify columns explicitly
Indexing Strategies
- B-tree (default): Equality + range. Composite: equality columns first, then range
- Hash: Equality-only, faster than B-tree for
=but no range - GIN: Full-text search, JSONB
@>, arrays.gin_trgm_opsforLIKE '%term%' - GiST: Spatial (PostGIS), range types, nearest-neighbor
- BRIN: Large append-only tables with natural ordering (time-series). Tiny index size
- Partial indexes:
WHERE active = true— index only filtered rows - Covering indexes:
INCLUDE (col)for index-only scans - Expression indexes:
CREATE INDEX ON t (lower(email))for case-insensitive lookups - Composite order: Most selective first for equality; leftmost prefix rule applies
- Maintenance:
REINDEX CONCURRENTLYfor bloat. Monitorpg_stat_user_indexesfor unused indexes - Justify cost: Every index must have a documented query pattern. Indexes slow writes and waste storage
Partitioning + Replication
Partitioning
- Range: Time-series (month/year), numeric ranges — most common
- List: Categorical (region, status, tenant_id) — good for multi-tenant
- Hash: Even distribution when no natural range/list. High-throughput writes
- Pruning: Queries must include partition key in WHERE for pruning
- Maintenance: Pre-create future partitions. Detach and archive old ones
- Constraints: PK and UNIQUE must include partition key
Replication
- Streaming replication (PostgreSQL): Async for read replicas, sync for zero data loss. Monitor replication lag
- Connection routing: Read queries to replicas, writes to primary. Use PgBouncer or application-level routing
- Failover: Automated via Patroni,
pg_auto_failover, or cloud-managed. Test failover regularly - Logical replication: For selective table replication, version upgrades, multi-master
- RTO/RPO: Define Recovery Time Objective and Recovery Point Objective. Design backup frequency and failover speed accordingly
NoSQL
MongoDB
- Schema design follows access patterns, not normalization. Embed for 1:1 and 1:few. Reference for 1:many and many:many
- Indexes: compound indexes follow ESR rule (Equality → Sort → Range). Use
explain()withexecutionStats - Aggregation pipeline for complex queries. Avoid
$lookupon large collections — denormalize instead - Sharding: choose shard key based on cardinality + write distribution + query isolation. Avoid monotonic keys (ObjectId) as shard key
- Write concern:
{w: "majority"}for durability;j: truefor journaled - Replica sets: minimum 3 voting members; use hidden/delayed secondaries for analytics
Redis
- Data structures: Strings (cache), Hashes (objects), Lists (queues), Sets (tags), Sorted Sets (leaderboards), Streams (event log)
- Key naming:
{service}:{entity}:{id}:{field}— consistent, scannable, eviction-friendly - TTL on every key — never store data without expiration unless explicitly persistent
- Memory management:
maxmemory-policy(allkeys-lrufor cache,noevictionfor queues). Monitorused_memoryvsmaxmemory - Persistence: RDB snapshots + AOF for durability; AOF-only for stricter RPO
- Cluster mode for horizontal scaling; sentinel for HA with single primary
Cassandra / ScyllaDB
- Model by query, not entity. Each query pattern = one table. Denormalization expected
- Partition key = distribution, clustering key = sort within partition
- Avoid partitions >100MB. Time bucketing for growing partitions
LOCAL_QUORUMfor strong consistency,ONEfor eventual- Avoid secondary indexes on high-cardinality columns; use materialized views or denormalized tables
- Tombstones:
gc_grace_secondstuning critical for deletion-heavy workloads
DynamoDB
- Single-table design: PK + SK overloading. GSIs for alternative access patterns
- Avoid hot partitions — random suffix on PK for high-throughput items
ProjectionExpressionto minimize read cost. Batch for bulk ops. TTL for auto-expiration- On-demand vs provisioned capacity — choose based on traffic predictability
- DAX for sub-millisecond read cache
- Streams for change data capture into Lambda / Kinesis
Time-Series + Graph
Time-Series
- TimescaleDB (PG extension): hypertables = automatic time-range partitioning + compression
- InfluxDB: tag/field separation, downsampling via continuous queries
- Use BRIN indexes on append-only time columns in PostgreSQL
- Retention policies: drop or compress partitions older than N days
- Pre-aggregate (continuous aggregates) for dashboard queries
Graph
- Neo4j: model relationships as first-class. Cypher queries for path/traversal
- Property graphs vs RDF/SPARQL — choose based on schema rigidity
- Index on node label + property for entry points
- Avoid unbounded variable-length paths in production queries
Monitoring + Capacity Planning
- Key metrics: QPS, latency (p50/p95/p99), active connections, replication lag, cache hit ratio, disk I/O, bloat
- PostgreSQL:
pg_stat_statements(query analysis),pg_stat_user_tables(seq scans, dead tuples),pg_stat_activity(locks) - Slow query log: Enable with threshold (100ms). Review top offenders weekly
- Connection pooling: PgBouncer or pgpool-II. Monitor saturation and wait time
- Vacuum: Tune
autovacuumper table. Monitorn_dead_tup. VACUUM FULL only in maintenance windows - Bloat: Monitor table/index bloat.
pg_repackfor online reorganization - Capacity: Project growth from current rate + business plan. Plan disk + memory + connection headroom 6–12 months ahead
Backup + Recovery
- PostgreSQL:
pg_basebackup+ WAL archiving for PITR.pg_dumpfor logical backups - MySQL:
mysqldump(logical),xtrabackup(physical, hot), binlog for PITR - Verify restores on schedule — untested backups are not backups
- Off-site copy: Backups must survive primary site failure
- Encryption at rest for backup files
- RTO/RPO documented and tested per database tier
Database Security
- Least privilege: App accounts get only required permissions on specific tables. Never use superuser for applications
- Row-Level Security (RLS): PostgreSQL RLS for multi-tenant isolation. Enable
FORCE ROW LEVEL SECURITYon the table owner - Encryption: TLS for connections (
sslmode=verify-full). Encryption at rest via disk/tablespace. Column-level (pgcrypto) for PII - Audit logging:
pgauditfor DDL/DML audit trail. Log all privileged operations - SQL injection: Parameterized queries only. Never concatenate user input. Review stored procedures for dynamic SQL
- No secrets in SQL files: Connection strings, passwords, keys → env vars or secret managers
When this applies
| Workflow | Apply this knowledge |
|---|---|
Agent(db-engineer) |
Auto-loaded |
/develop (DB work package) |
Spawned db-engineer loads this |
/code-review (migration PRs) |
Indexing + schema sections |
/architecture-design (data model) |
NoSQL + partitioning sections |
/migrate (schema migrations) |
Replication + backup sections |
/bugfix (query/perf) |
EXPLAIN + indexing sections |
Integration
- Consumed by:
db-engineer(primary),data-engineer,python-engineer/java-engineer(ORM review),solution-architect - External references: PostgreSQL, MySQL, SQL Server, MongoDB, Redis, Cassandra, DynamoDB official docs
Source: alex-voloshin-dev/ai-assets — distributed by TomeVault.
Decide Fit First
Design Intent
How To Use It
Boundaries And Review