Skip to content
Skills Library
Programming · Building & coding

database-engineering

/database-engineering

Design, query, migrate, secure, and optimize relational databases, especially PostgreSQL. Use for schema design, SQL, indexes, transactions, concurrency, data integrity, backup/recovery, and production migrations.

Repository
agent-forge ↗
Licence
MIT
Last repository change
04.11.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
3
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 04.11.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Design, query, migrate, secure, and optimize relational databases, especially PostgreSQL. Use for schema design, SQL, indexes, transactions, concurrency, data integrity, backup/recovery, and production migrations.

Original author description

Design, query, migrate, secure, and optimize relational databases, especially PostgreSQL. Use for schema design, SQL, indexes, transactions, concurrency, data integrity, backup/recovery, and production migrations.

How it works

  1. Mission
  2. 1. Discover the database and workload
  3. 2. Model data and invariants
  4. 3. SQL and query design
  5. 4. Transactions and concurrency
  6. 5. Schema and data migrations
  7. 6. PostgreSQL-specific guidance
  8. 7. Security and privacy
  9. 8. Backup, restore, and incident readiness
  10. Required deliverables
Original SKILL.md headings · 127 lines, 1,111 words
SKILL.md preview
---
name: database-engineering
description: Design, query, migrate, secure, and optimize relational databases, especially PostgreSQL. Use for schema design, SQL, indexes, transactions, concurrency, data integrity, backup/recovery, and production migrations.
license: MIT
---

# Database engineering

## Mission

Protect data correctness first, then optimize for performance, operability, and cost. Database design is part of the application's domain model and deployment strategy. A migration is a production change with lock, compatibility, and recovery implications.

## 1. Discover the database and workload

Before proposing changes, determine:
- Database engine/version, hosting model, extensions, collation, timezone, and connection architecture.
- Current schema, constraints, indexes, migration history, and ownership of each table.
- Approximate row counts, growth, row width, write/read ratio, concurrency, and retention.
- Query patterns, slow-query evidence, lock waits, connection pool behavior, and replication topology.
- Availability, recovery point/time objectives, backup policy, data residency, and deletion requirements.
- Application versions that may run concurrently during deployment.

Inspect real migrations and query call sites. Do not assume a schema diagram reflects the live database.

## 2. Model data and invariants

For every entity, define identity, lifecycle, ownership, relationships, uniqueness, nullability, and retention. Decide which constraints must be enforced by the database because multiple code paths, jobs, or services can write the same data.

Prefer:
- Primary keys that are stable and never repurposed.
- Foreign keys for referential integrity when the deployment and ownership model permits them.
- Unique constraints for natural invariants such as one active membership per user/team.
- Check constraints for local invariants the database can validate.
- Explicit status/state transition rules rather than loosely related booleans.
- Junction tables for many-to-many relationships with their own lifecycle or metadata.

Avoid storing the same fact in multiple authoritative locations. Derived aggregates and caches must have a documented rebuild or reconciliation strategy. Do not use JSON columns as a substitute for modeling stable, frequently queried fields relationally.

## 3. SQL and query design

- Parameterize all values. Do not concatenate untrusted input into SQL.
- Whitelist dynamic identifiers such as sort columns and directions; values cannot be safely parameterized as identifiers.
- Select only needed columns and rows.
- Use bounded pagination and deterministic ordering. Include a unique tie-breaker in pagination order.
- Prefer cursor/keyset pagination for large or frequently changing datasets.
- Check N+1 patterns, accidental Cartesian products, correlated subqueries, and duplicate joins.
- Understand how NULL, collation, timezone, decimals, and implicit casts affect semantics.
- Use batch operations deliberately; consider memory, lock duration, and transaction log growth.

For PostgreSQL, use EXPLAIN (ANALYZE, BUFFERS) on safe representative data when investigating query plans. Interpret estimates, actual rows, loops, buffer hits/reads, sort methods, and sequential/index scans together. Do not add an index solely because a column appears in a WHERE clause: inspect cardinality, write amplification, index size, and query selectivity.

## 4. Transactions and concurrency

Define the invariant that must be atomic and make the transaction boundary match it. Keep transactions short. Avoid remote calls, long file operations, or user interaction while holding locks.

Choose concurrency control deliberately:
- Optimistic locking/version columns for low-conflict updates.
- Unique constraints and atomic upserts for uniqueness races.
- Row-level locking for short critical sections with clear lock order.
- Advisory locks only when their scope and lifecycle are rigorously defined.
- Serializable isolation only when the invariant requires it and retry behavior is implemented.

Test concurrent requests and duplicate jobs. A read-then-write check in application code is not sufficient to guarantee uniqueness under concurrency.

## 5. Schema and data migrations

Classify every migration as additive, backfill, constraint tightening, rename, type conversion, or destructive removal. Evaluate table locks, index build behavior, table rewrite risk, replication lag, disk space, runtime, and application compatibility.

Prefer expand/contract:
1. Add the new schema in a backward-compatible form.
2. Deploy code that can work with old and new representations.
3. Backfill in bounded, restartable batches with checkpoints and rate controls.
4. Verify counts, constraints, and data equivalence.
5. Switch reads/writes and monitor.
6. Remove the old representation in a later release after rollback is no longer needed.

For backfills, define idempotency, resume marker, batch size, throttling, failure handling, progress logging, and stop condition. Avoid a single huge transaction for very large datasets.

Before destructive changes, require explicit approval and confirm backup/restore capability. A database rollback is not automatically safe after information is discarded or external side effects have occurred.

## 6. PostgreSQL-specific guidance

- Use timestamptz for absolute instants and define how local business dates/time zones are represented.
- Use numeric for exact decimal/money calculations; document currency and rounding rules.
- Understand MVCC, vacuum/autovacuum, long-running transactions, bloat, and lock behavior.
- Size connection pools with total application replicas in mind; each process-local pool contributes to the global connection count.
- Use partial, expression, or multicolumn indexes only when they match measured predicates and ordering.
- Be deliberate about JSONB GIN indexes, generated columns, and expression indexes.
- Use concurrent index creation where operationally appropriate and verify cleanup after failures.
- Use ON CONFLICT only when the upsert's conflict target and update semantics match the business invariant.
- Avoid long-lived idle-in-transaction sessions and unbounded cursors.

## 7. Security and privacy

Use least-privilege database roles. Separate application runtime, migration, read-only reporting, and administrative access where practical. Protect credentials, enforce TLS, restrict network access, and audit privileged actions. Avoid exposing raw SQL errors or connection strings. Apply row-level security only with a verified, tested model for role/session context and connection pooling; do not treat RLS as a substitute for application authorization.

## 8. Backup, restore, and incident readiness

Document backup frequency, retention, encryption, restore location, point-in-time recovery, and ownership. A backup is not verified until a restore test succeeds. Record recovery procedures and acceptable data loss. Monitor storage, connections, replication lag, deadlocks, slow queries, failed jobs, and backup freshness.

## Required deliverables

For material schema/query work, provide:
- Schema or query change and rationale.
- Invariants and constraints.
- Expected query access pattern and index rationale.
- Migration sequence, lock/backfill risk, and deployment compatibility.
- Validation queries and test plan.
- Backup, rollback, or forward-fix considerations.
- Measured performance evidence when optimization is claimed.

## Quality gates

Verify correctness with constraints and tests, inspect query plans for performance work, test representative data volume, consider concurrent access, and confirm the application can run during the planned migration sequence.

## Anti-patterns

Never run production DDL or mass updates without approval. Avoid unbounded selects, N+1 access, arbitrary dynamic SQL, implicit transaction assumptions, long-held locks, duplicated authoritative data, undocumented time-zone rules, and irreversible migrations disguised as reversible deploys.

## Supporting reference material

Load these resources when the task needs a reusable template or deeper checklist:
- [migration plan template](references/migration-plan-template.md)
- [query review checklist](references/query-review-checklist.md)

The procedure and quality gates in this SKILL.md remain the primary workflow.


## Completion report

State schema/query changes, data impact, migration sequence, test/plan validation, measured results, backup/recovery implications, and residual risk.
Files in this package (3)
  • SKILL.md 8 KB
  • references/migration-plan-template.md 2 KB
  • references/query-review-checklist.md 1 KB

Versions

RepositoryScoreLicence
This page · mhingan/agent-forge100MIT
hmfareed/Frontier-Engineer100MIT
Twistor-Labs/twistor-engineer100MIT
lingxiaobc/claude_code_template100MIT

Database snapshot: 13.11.2026 · package version cbddf512b75e (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 90postgresql
    Design, query, migrate, or troubleshoot a PostgreSQL-backed application. Use for schema, indexes, constraints, transactions, row-level security, query plans, extensions, or backup concerns.
  • 100postgresql
    Use when the affected system uses PostgreSQL and the task changes schemas, SQL, indexes, transactions, migrations, roles, or database performance.
  • 95postgresql-data-engineering
    Design, review, troubleshoot, or improve PostgreSQL schemas, queries, transactions, migrations, pooling, security, and production recovery. Use for PostgreSQL engineering decisions; skip unrelated backend work and trivial SQL or text edits.
  • 100database-design
    Database schema design, optimization, and migration patterns for PostgreSQL, MySQL, and NoSQL databases. Use for designing schemas, writing migrations, or optimizing queries.
  • 100database-schema-designer
    Design relational schemas from requirements with normalization, migrations, ERDs, RLS policies, and indexes for PostgreSQL, MySQL, and SQLite. Use when designing new features, reviewing schemas, or adding multi-tenancy.
  • 100databases
    Use when creating, changing, or reviewing MongoDB/PostgreSQL/SQLite database work in a real repository, including schema, models, migrations, queries, indexes, transactions, connection config, backup/restore assumptions, dual-database desktop sync, or performance; follow…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.