Database Schema Design

Purpose

⚠IMPORTANT⚠This skill enforces mathematically rigorous Relational Calculus (Codd's rules) and normal form constraint validation. It strictly prohibits the algorithmic flattening of relational data into unscalable, non-atomic topologies.

When to Use This Skill

⚠NOTE⚠Use this skill during the architectural planning phase of any feature that requires persisting complex relational data.

What This Skill Prevents

This skill prevents:

• Denormalization Violation: Collapsing orthogonal $1:N$ domain entities into a single scalar vector.
• Dangling Pointers: Defining relational maps using raw integers without asserting strict Foreign Key constraint enforcement at the engine level.
• Index Sparsity: Generating $O(N)$ sequential scan bottlenecks by omitting B-Tree/Hash indexes on mutation/search vectors.
• Invariant Failure: Omitting NOT NULL constraints and default schemas, leading to unpredictable null-pointer state propagation.

Core Principle

Schema topologies MUST enforce strict Third Normal Form ($3NF$) invariants. Transitive dependencies are strictly prohibited. The relational topology serves as the foundational AST for all higher-level application logic.

Research-Backed Rules

• Normalization Execution ($3NF$): Execute orthogonal entity separation. Redundant property allocation across isolated domains is prohibited.
• Primary Key Specification (UUIDv7): Initialize all primary constraints utilizing UUIDv7 algorithms. UUIDv7 provides distributed initialization velocity while maintaining strict $B$-Tree sequential locality, mitigating index fragmentation. UUIDv4 is restricted exclusively to opaque entropy tokens.
• Referential Integrity Constraints: Explicitly map FOREIGN KEY bounds. Configure deterministic ON DELETE directives (CASCADE, SET NULL) to prevent orphan state vectors.
• Structured JSON Invariants (PostgreSQL 18): Restrict JSONB arrays exclusively to dynamic schema-less contexts. Flattening relational data into JSONB to evade normalization is prohibited. Leverage PostgreSQL 18's native JSON_TABLE logic to project JSON vectors into highly indexed relational sets.
• Audit Logging Topology: Mandate created_at and updated_at timestamps on all domain core entities.

Stack-Aware Guidance

Prisma Guidance

Declare relation boundaries explicitly across both object definitions in schema.prisma. Initialize keys via @default(cuid()) or @default(uuid()). Enforce @@index() on frequently scanned query vectors.

Drizzle ORM Guidance

Map explicit referential connections via .references(). Assert .notNull() boundary conditions, neutralizing Drizzle's default nullable generation.

Implementation Guidance

1. Entity Identification: Formulate discrete data topologies (User, Post, Organization).
2. Topology Connection: Establish $1:1$, $1:N$, and $M:N$ relation maps.
3. Join Stratification: Instantiate explicit bridge schemas for $M:N$ arrays.
4. Data Type Strictness: Assign highest-restriction types (VARCHAR(255) over TEXT, BOOLEAN over numeric equivalents).
5. Constraint Generation: Declare UNIQUE maps and append INDEX optimizations.

Mathematical / Measurable Rules

• The Indexing Threshold: Any vector subject to WHERE, JOIN, or ORDER BY operations exceeding estimated scale $N \ge 10,000$ MUST register an explicit INDEX.
• String Length Limits: Cap email string definitions at $N=255$ limits.

Quality Gates

Maintainability Gate

• Validated $3NF$ structural compliance.
• Asserted Foreign Key constraint maps and destruction rules.
• Confirmed audit timestamp parameters.

Performance Gate

• Validated index strategies on target constraint queries.

Anti-Patterns to Avoid

• First Normal Form ($1NF$) Violation: Emitting non-atomic tuple attributes (e.g., CSV strings) instead of deterministic relational intersections.
• Premature Soft Deletes: Injecting is_deleted flags indiscriminately. Soft deletes drastically increase query cyclomatic complexity and should only be initiated upon strict auditing constraints.

Agent Behavior Instructions

1. Suspend database engine operations pending strict architectural validation.
2. Emit relational schemas logically before emitting query code.
3. Execute deterministic cascading behavior checks before relation commitment.

Final Review Checklist

• Denormalization artifacts nullified.
• Referential integrity constraints enforced.
• Data type bounds optimized.
• Fully agent-optimized deterministic rules.