Skip to main content

Database Normalization in ValkyrAI (3NF & BCNF)

⚙️ Database Normalization in ValkyrAI

ValkyrAI’s backend architecture follows strict data normalization principles to ensure consistency, integrity, and deterministic behavior across generated APIs. All schemas generated by ThorAPI are designed to at least meet Third Normal Form (3NF), with critical core services adhering to Boyce–Codd Normal Form (BCNF) when functional dependencies warrant it.

⸻

🧱 Why Normalization Matters

In a system with 180+ schema objects spanning ACLs, SecureFields, Workflows, and Execution modules, redundant data or transitive dependencies can cause chaos. Normalization ensures: • No redundant data across generated entities • Predictable joins across APIs and database layers • Stable OpenAPI and ORM generation from schema-first definitions • Simplified migration and code generation pipelines

ThorAPI enforces normalization at the schema definition level, before generating SQL, Java JPA entities, or TypeScript clients.

⸻

🔢 Third Normal Form (3NF)

Definition

A table is in 3NF if:

  1. It’s already in Second Normal Form (2NF) — every non-key column depends on the whole key.
  2. No non-key column depends on another non-key column (no transitive dependencies).

Example (ThorAPI context)

UserWorkflow: type: object properties: userId: type: string userEmail: type: string workflowName: type: string assignedAgentName: type: string assignedAgentEmail: type: string

Here, both assignedAgentName and assignedAgentEmail depend on the same logical entity: Agent, not on UserWorkflow. This violates 3NF — if an agent updates their email, all rows must be updated.

✅ Fix (3NF-compliant split)

UserWorkflow: type: object properties: userId: type: string workflowName: type: string agentId: type: string $ref: "#/components/schemas/Agent"

Agent: type: object properties: agentId: type: string agentName: type: string agentEmail: type: string

Now all non-key attributes depend only on their primary key. When ThorAPI generates REST endpoints, this results in UserWorkflow referencing /agents/{id} as a foreign key relationship.

⸻

🧩 Boyce–Codd Normal Form (BCNF)

Definition

A table is in BCNF if, for every dependency X → Y, the determinant X is a superkey — i.e., uniquely identifies each row.

BCNF eliminates rare dependency anomalies that 3NF sometimes allows, especially in multi-tenant or composite-key contexts.

Example (ThorAPI multi-tenant case)

AppEnvironment: type: object properties: appId: type: string environment: type: string enum: [ "dev", "staging", "prod" ] baseUrl: type: string deployRegion: type: string

Functional dependencies: • appId, environment → baseUrl • deployRegion → baseUrl

Here, deployRegion determines baseUrl, but deployRegion isn’t a superkey. If multiple apps deploy to the same region, this design causes redundancy — violating BCNF.

✅ Fix (BCNF-compliant refactor)

DeployRegion: type: object properties: deployRegion: type: string baseUrl: type: string

AppEnvironment: type: object properties: appId: type: string environment: type: string deployRegion: type: string $ref: "#/components/schemas/DeployRegion"

Now DeployRegion defines baseUrl once and can be reused across applications. ThorAPI automatically generates joinable endpoints for this pattern.

⸻

🧠 In Practice: ValkyrAI Normalization Policy

Layer Normal Form Enforcement Example Core Entities (User, Workflow, SecureField) BCNF Strict Unique constraints + referential integrity Operational Tables (Execution, LogEntry) 3NF Standard Foreign key integrity, denormalized for performance Analytical Views (Aggregates, AI Stats) Partial 3NF Relaxed Optimized for query speed

⸻

🧮 Code Generation Impact

Because ThorAPI is schema-driven: • Each normalized entity automatically maps to its own REST path. • Relationships are inferred via $ref or allOf OpenAPI constructs. • Liquibase/Flyway migrations are auto-generated with foreign keys and unique constraints. • The TypeScript client and Spring JPA layer maintain consistent normalization boundaries.

⸻

✅ Takeaways • 3NF ensures data consistency across generated APIs. • BCNF enforces stricter logical integrity where multiple candidate keys exist. • ThorAPI acts as a compiler: it enforces normalization before code generation. • Following normalization rules leads to safer AI-driven automation, predictable schema evolution, and simpler debugging.

⸻

💡 Pro Tip: Use thor-cli schema analyze --normalize to check a schema’s normal form compliance before committing. This helps maintain architectural integrity as ValkyrAI scales beyond 200+ schema objects.