# DB Architect Security

> Veritabanı mimarisi, güvenlik standartları, ORM yapılandırmaları ve veritabanı tasarımı için yetenek.

- Skill: `gktuoktay/db-architect-security` (Agent Skill)
- Install (CLI): `npx skillmds@latest add gktuoktay/db-architect-security`
- Raw SKILL.md: https://api.skillmd.com/api/skills/gktuoktay/db-architect-security/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Security
- Author: GktuOktay (https://skillmd.com/u/gktuoktay)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/gktuoktay/db-architect-security

---


# Database Architect & Security Auditor

This skill designs database schemas, configures ORM models (Prisma, Drizzle), optimizes queries, and audits code for OWASP security vulnerabilities.

## 🛠️ 1. Database Architecture & ORM

When designing or inspecting DB structures:
- **Relational & NoSQL Schema Design:** 1-to-N, N-to-N relationships, FK constraints, indexing strategies.
- **ORM Schema Generation:** Production-ready schemas for Prisma (`schema.prisma`) or Drizzle ORM.
- **Migrations & Data Integrity:** Soft deletes (`deletedAt`), audit timestamps (`createdAt`, `updatedAt`), UUID/CUID PKs.
- **Performance Optimization:** Identify N+1 query patterns, suggest indexes using `EXPLAIN ANALYZE` principles.

## 🛡️ 2. Security Audit & Vulnerability Checks

Audit code against OWASP Top 10 standards:
- **SQL Injection:** Detect unparameterized queries and convert to parameterized statements.
- **XSS (Cross-Site Scripting):** Ensure user input sanitization and context escaping.
- **Auth & Session Security:** Secure JWT handling (HttpOnly cookies), password hashing (Argon2/bcrypt with salt), RBAC enforcement.
- **CSRF & Rate Limiting:** Form token validation, API rate limiting / debouncing.
- **Sensitive Data Leakage:** Audit hardcoded secrets, `.env` leakage, unencrypted PII in logs.

## Output Template

1. 🗄️ **Database / ORM Blueprint:** Production code (Prisma / SQL).
2. 🔒 **Security Audit & Risk Assessment:** Vulnerabilities categorized by severity (Low / Medium / Critical).
3. 🛠️ **Remediation Code & Fixes:** Secure, optimized code snippets.


## Mandatory Schema Separation
Right after the business plan is approved, BEFORE writing code, you MUST divide the database tables into logical schemas (e.g., `identity`, `audit`, `inventory`, `sales`). DO NOT dump all tables into the default `public` or `dbo` schema. Clean database segregation is critical.

## Advanced Query Optimization & Indexing
- **Indexing:** Always define logical Composite Indexes, Filtered Indexes, and Covering Indexes for frequently queried columns. Never allow full table scans on large tables.
- **EF Core Optimization:** Enforce `AsNoTracking()` for read-only queries. Prevent N+1 queries by explicitly using `.Include()` or projection (`.Select()`).
- Never write a `GetAll` endpoint without Mandatory Pagination.

## Pagination & Dynamic Filtering
- **Lists MUST be Paginated:** Any structure returning a list of items must implement pagination by default.
- **Dynamic Filtering:** If an API request comes with no filters, return the full paginated dataset. If filters are provided, apply them dynamically. CRITICAL: Always apply these filters at the database level using `IQueryable` (e.g., LINQ `.Where()`) BEFORE materializing the data (never in memory).

<added_enterprise_rules>
- FORCE 2-stage Data Migration plans to prevent data loss on schema changes.
- REJECT N+1 Query vulnerabilities; FORCE `.Include()` or `.Select()` projections.
</added_enterprise_rules>