Fastify Drizzle Backend Reviewer

• CHECKLIST.md
• REPORT.md
• ENDPOINTS.md
• IDEMPOTENCY_DESIGN.md
• DRIZZLE_QUERY_PATTERNS.md
• PERFORMANCE_GUIDE.md
• RED_FLAGS.md

Security Documents: docs/security/

• THREAT_MODEL.md
• SUPPLY_CHAIN.md
• DATA_HANDLING.md
• AUDIT_PLAYBOOK.md

Vendor Pattern References: docs/vendor/claude-mpm-skills/drizzle-orm/

• advanced-schemas.SUMMARY.md
• query-patterns.SUMMARY.md
• performance.SUMMARY.md
• vs-prisma.SUMMARY.md

All analysis MUST consult these files where applicable. They serve as source-of-truth constraints.

HARD GATE

You MUST NOT:

• Implement endpoints
• Modify schema
• Refactor services
• Optimize queries
• Introduce dependencies
• Apply fixes

Until:

1. A full audit is completed
2. Findings are structured according to docs/backend-review/REPORT.md
3. Severity levels are assigned

This skill is diagnostic-first. Implementation follows only after explicit risk classification.

Audit Execution Order

The following domains MUST be evaluated in order.

Domain 1: Idempotency & Concurrency Safety

Reference:

• docs/backend-review/IDEMPOTENCY_DESIGN.md
• docs/backend-review/CHECKLIST.md

Verify:

• GET and HEAD are side-effect free
• PUT operations are idempotent
• DELETE tolerates repetition
• Retryable POST endpoints require Idempotency-Key
• Idempotency keys are:
  • Scoped to authenticated principal
  • Scoped to route
  • Compared using request hash
  • Replayed with identical response
  • Rejected if hash mismatch
• Unique constraints exist for natural keys
• Multi-step writes use transactions
• No race condition permits duplicate inserts
• Upserts use proper conflict targets

Failure in this domain is P0 or P1.

Domain 2: Request-Purpose Alignment

Reference:

• docs/backend-review/CHECKLIST.md
• docs/backend-review/ENDPOINTS.md

For each endpoint:

• Unknown fields are rejected
• No mass assignment
• Minimal required fields accepted
• Proper authentication tokens required
• Authorization enforced at resource level
• Response contains sufficient but not excessive data
• Pagination enforced with maximum bounds
• No unbounded list endpoints

Requests must be minimal, sufficient, and deterministic.

Domain 3: Redundancy & Query Optimization

Reference:

• docs/backend-review/DRIZZLE_QUERY_PATTERNS.md
• docs/backend-review/PERFORMANCE_GUIDE.md
• docs/backend-review/RED_FLAGS.md
• docs/vendor/claude-mpm-skills/drizzle-orm/query-patterns.SUMMARY.md

Detect:

• N+1 queries
• Insert-then-select without returning()
• Wide selects on large tables
• Multiple DB round-trips for related data
• Unbounded queries
• Duplicate logic across services
• Missing indexes on filter/sort columns

Enforce:

• Explicit column projection
• Bounded pagination
• Cursor pagination for scale
• Index-backed filtering
• Parameterized queries only
• No raw SQL string concatenation

Domain 4: Schema & Constraint Integrity

Reference:

• docs/vendor/claude-mpm-skills/drizzle-orm/advanced-schemas.SUMMARY.md
• docs/backend-review/DRIZZLE_QUERY_PATTERNS.md

Verify:

• Typed JSON columns using $type<T>()
• Database enums instead of free text where appropriate
• Foreign keys enforced
• CHECK constraints enforce invariants
• Composite keys used correctly
• Partial indexes for soft deletes
• Unique constraints match business rules
• Tenant isolation enforced consistently
• Migrations reflect actual schema definitions

The database must prevent invalid state transitions even if application logic fails.

Domain 5: Authentication & Authorization

Reference:

• docs/security/THREAT_MODEL.md
• docs/security/DATA_HANDLING.md

Verify:

• Explicit authentication requirement per route
• Role enforcement explicit
• Resource ownership validated
• No IDOR exposure
• Tenant scoping cannot be bypassed
• No token leakage in logs
• CORS scoped properly
• CSRF mitigated if cookies used

Authentication without authorization is a defect.

Domain 6: Security & Supply Chain

Reference:

• docs/security/SUPPLY_CHAIN.md
• docs/security/AUDIT_PLAYBOOK.md
• SECURITY.md
• .github/workflows/

Verify:

• Lockfile committed
• CodeQL workflow present
• Dependency Review workflow present
• Socket scanning configured
• SBOM generation configured
• No secrets committed
• Secrets not logged
• SSRF protections on outbound requests
• Structured logging with redaction
• Stable error envelope without stack traces

Supply chain misconfiguration is P1 or higher.

Severity Classification

P0 Critical:

• Auth bypass
• IDOR
• Duplicate writes under retry
• Injection risk
• Secret leakage
• Data integrity violation

P1 High:

• Missing validation
• Missing transaction
• Improper idempotency enforcement
• Missing rate limiting
• Broken tenant isolation

P2 Medium:

• Performance inefficiency
• Redundant queries
• Missing indexes

P3 Low:

• Minor contract mismatch
• Non-breaking inconsistencies

Required Outputs

Audit must produce:

1. Endpoint inventory
2. Structured findings per docs/backend-review/REPORT.md
3. Severity classification
4. Root cause explanation
5. Risk explanation
6. Remediation guidance
7. Patch recommendations for P0 and P1 issues

Strict Enforcement Rules

• Never assume idempotency. Prove it.
• Never assume authorization. Verify it.
• Never assume performance. Measure it.
• Never assume ORM implies safety.
• Prefer database constraints over application logic.
• Prefer elimination of redundancy over abstraction.
• Prefer determinism over convenience.

Final Objective

Guarantee:

• No duplicate writes under retries
• No unauthorized data access
• No redundant database operations
• No unbounded queries
• Audit-readiness for security tooling
• Deterministic and resilient backend behavior

This skill ensures Fastify + Drizzle systems are concurrency-safe, audit-ready, and production-safe.37:["$","$L3f",null,{"content":"$40","frontMatter":{"name":"fastify-drizzle-backend-reviewer","description":"Enterprise-grade backend audit skill for Fastify + Drizzle ORM + PostgreSQL systems serving React/TypeScript apps. Ensures idempotency, strict validation, elimination of redundancy, optimal query design, and security posture including authn/authz, IDOR prevention, injection safety, CORS/CSRF protection, rate limiting, and supply chain compliance."}}]