Afrexai Database Engineer

# Database Engineering Mastery

> Complete database design, optimization, migration, and operations system. From schema design to production monitoring — covers PostgreSQL, MySQL, SQLite, and general SQL patterns.

## Phase 1 — Schema Design

### Design Brief

Before writing any DDL, fill this out:

```yaml
project: ""
domain: ""
primary_use_case: "OLTP | OLAP | mixed"
expected_scale:
  rows_year_1: ""
  rows_year_3: ""
  concurrent_users: ""
  read_write_ratio: "80:20 | 50:50 | 20:80"
compliance: [] # GDPR, HIPAA, PCI-DSS, SOX
multi_tenancy: "none | schema-per-tenant | row-level | database-per-tenant"
```

### Normalization Decision Framework

| Form | Rule | When to Denormalize |
|------|------|---------------------|
| 1NF | No repeating groups, atomic values | Never skip |
| 2NF | No partial dependencies on composite keys | Never skip |
| 3NF | No transitive dependencies | Reporting tables, read-heavy aggregations |
| BCNF | Every determinant is a candidate key | Rarely needed unless complex key relationships |

**Denormalization triggers:**
- Query joins > 4 tables consistently
- Read latency > 100ms on indexed queries
- Cache invalidation complexity exceeds denormalization maintenance
- Reporting queries block OLTP workloads

### Naming Conventions

```
Tables:      snake_case, plural (users, order_items, payment_methods)
Columns:     snake_case, singular (first_name, created_at, is_active)
PKs:         id (bigint/uuid) or {table_singular}_id
FKs:         {referenced_table_singular}_id
Indexes:     idx_{table}_{columns}
Constraints: chk_{table}_{rule}, uq_{table}_{columns}, fk_{table}_{ref}
Enums:       Use VARCHAR + CHECK, not DB enums (easier to migrate)
Booleans:    is_, has_, can_ prefix (is_active, has_subscription)
Timestamps:  _at suffix (created_at, updated_at, deleted_at)
```

### Column Type Decision Tree

```
Text < 255 chars, fixed set?     → VARCHAR(N) + CHECK
Text < 255 chars, variable?      → VARCHAR(255)
Text > 255 chars?                → TEXT
W...

# Database Engineering Mastery

Complete database design, optimization, migration, and operations system for AI agents. Covers PostgreSQL, MySQL, and SQLite — from schema design to production monitoring.

## Install

```bash
clawhub install afrexai-database-engineer
```

## What You Get

- **Schema Design** — Normalization framework, naming conventions, column type decision tree, relationship patterns, table templates
- **Indexing Strategy** — Index type selection guide (B-tree, GIN, GiST, BRIN, partial, covering), unused/missing index detection
- **Query Optimization** — EXPLAIN interpretation, 6 anti-pattern fixes, window functions, CTE patterns, keyset pagination
- **Migration Safety** — Zero-downtime patterns, batched backfills, concurrent index creation, rollback templates
- **Performance Monitoring** — Connection tracking, cache hit ratio, bloat detection, slow query analysis, lock monitoring
- **Backup & Recovery** — Strategy decision matrix, verification checklist, restore procedures
- **Security** — Least privilege roles, Row-Level Security, SQL injection prevention, audit triggers
- **PostgreSQL Tuning** — Configuration by server size, connection pooling (PgBouncer)
- **Common Patterns** — Soft delete, optimistic locking, event sourcing, time-series partitioning, full-text search, JSONB
- **Operational Runbooks** — Emergency procedures for overload and disk full, weekly maintenance checklist
- **100-point quality scoring** across 7 dimensions

## Quick Start

Tell your agent:
- "Design a schema for an e-commerce platform"
- "Optimize this query: SELECT * FROM orders WHERE..."
- "Audit this database for performance issues"
- "Write a safe migration to add a column"

## ⚡ Level Up

Get the complete **SaaS Context Pack** ($47) — includes database patterns plus full SaaS architecture, billing, auth, and scaling strategies.

👉 [Browse all context packs](https://afrexai-cto.github.io/context-packs/)

## 🔗 More Free Skills by AfrexAI

- [afrexai-code-reviewer]...