Schema Guide

About This Guide

This document helps you write effective DDK schemas to define your data models. It focuses on practical guidance for creating entities, relationships, constraints, and validation rules that generate production-ready database-backed GraphQL APIs.

Who this is for: Anyone building data schemas for DDK servers—whether you're a backend engineer designing a new data layer or a domain expert defining your application's data model.

What you'll learn:

• How to define entities with the required DDK directives
• How to model relationships (one-to-one, one-to-many, many-to-many)
• How to apply constraints for data validation and integrity
• Best practices for schema design and evolution
• Common patterns for audit trails, hierarchies, and multi-tenant systems

What we don't cover: To keep this guide clear and accessible, we don't cover the internal mechanics of the DDK code generator, the complete generated code structure, or proprietary optimization algorithms. For advanced schema customization beyond these patterns, please contact our support team.

---

This guide covers how to define data schemas for the DDK, including entity definitions, relationships, constraints, and validation rules.

Table of Contents

• Basic Schema Structure
• Entity Definitions
• Relationships
• Constraints and Validation
• Field Types
• Schema Validation
• Auto-Generated Operations
• Best Practices

---

Basic Schema Structure

DDK schemas use a declarative approach to define your data model. You describe what your entities look like, how they relate to each other, and what constraints should be enforced—the DDK generates the complete database-backed API implementation.

Core Concepts

Entities: The fundamental objects in your system (users, orders, products, etc.)

Relationships: How entities connect to each other (one user has many orders, an order contains many products)

Constraints: Rules that ensure data integrity (required fields, unique values, validation checks)

Operations: The CRUD (Create, Read, Update, Delete) operations available for each entity

Schema Organization

Schemas are typically organized into two types:

ORM Schema Files: Define database-backed entities with auto-generated CRUD operations. These describe your core data model—the entities that persist to the database.

Custom Schema Files: Define custom operations that extend beyond standard CRUD. These describe specialized business logic that you'll implement yourself.

This separation keeps generated database operations distinct from custom business logic, making it clear which parts regenerate automatically and which contain custom code.

---

Entity Definitions

Defining Entities

An entity represents a type of object in your system that you want to persist to the database.

Required Elements:

• Entity directive marking it for database generation
• Primary key field for unique identification
• Field definitions describing the entity's attributes

Optional Elements:

• Relationships to other entities
• Validation constraints on fields
• Default values for fields
• Custom operations beyond CRUD

CRUD Operation Selection

When defining an entity, you specify which operations should be auto-generated:

Create Operations: Generate endpoints for creating new records

Read Operations: Generate endpoints for retrieving records—both single-record lookups and list queries with filtering and pagination

Update Operations: Generate endpoints for modifying existing records

Delete Operations: Generate endpoints for removing records

Partial CRUD: You can select any combination of operations. For example, a read-only audit log might only generate Read operations, while a fully-managed entity would generate all four.

Database Table Creation

Entities can be marked for database table creation:

Table-Backed Entities: Create a database table and generate repository code for querying and manipulating data

Non-Table Entities: Define types that don't persist to the database—useful for data transfer objects that aggregate or transform data without direct storage

Linked Entities: Create lightweight aliases that reference existing database models

Views and Materialized Views

Instead of regular database tables, entities can be defined as database views:

Views: Virtual tables defined by queries over other tables. Updated automatically when underlying data changes.

Materialized Views: Cached query results stored as physical tables. Faster queries but require manual refresh to update.

Use Cases:

• Aggregated reports and dashboards
• Complex joins pre-computed for performance
• Denormalized data for specific query patterns
• Read-optimized data structures

---

Relationships

Relationship Types

One-to-One: Each record in entity A relates to exactly one record in entity B (user has one profile)

One-to-Many: Each record in entity A relates to multiple records in entity B (user has many posts)

Many-to-Many: Multiple records in entity A relate to multiple records in entity B (users have many roles, roles belong to many users)

Back References: Navigate relationships in reverse (from child to parent, from owned to owner)

Defining Relationships

Relationships are defined through schema directives that specify:

Relationship Type: One-to-one, one-to-many, or many-to-many

Foreign Keys: Which fields contain the relationship links

Referenced Fields: Which fields are being linked to (usually primary keys)

Junction Tables: For many-to-many, which table manages the relationship

One-to-One Relationships

Each record in one entity connects to exactly one record in another entity.

Example: User has one Profile

Requirements:

• Foreign key on one side of the relationship
• Unique constraint on the foreign key to enforce one-to-one
• Relationship directive specifying foreign key and reference

Use Cases:

• User and extended profile information
• Order and shipping address
• Employee and employment contract

One-to-Many Relationships

One record relates to multiple other records.

Example: User has many Posts

Key Points:

• Foreign key exists on the "many" side
• Foreign key references the primary key of the "one" side
• Allows querying in both directions (user's posts, post's author)

Use Cases:

• User and their posts/comments/orders
• Category and products
• Department and employees

Many-to-Many Relationships

Multiple records in one entity relate to multiple records in another.

Example: Users and Roles (users can have multiple roles, roles belong to multiple users)

Requirements:

• Junction table to manage the relationship
• Composite primary key in junction table
• Foreign keys from junction table to both related entities
• Relationship directives on both sides

Junction Table: A separate entity that contains foreign keys to both sides of the relationship. The junction table's composite primary key ensures each relationship pair is unique.

Use Cases:

• Users and roles/permissions
• Products and categories
• Students and courses

Back References

Navigate relationships in reverse direction, from the "owned" side back to the "owner."

Example: Profile can navigate back to User

Purpose:

• Enable bidirectional relationship traversal
• Efficient queries from child to parent
• Avoid manually joining tables

Use Cases:

• From comment to post
• From line item to order
• From employee to department

---

Constraints and Validation

Primary Keys

Every database-backed entity requires a primary key:

Purpose:

• Uniquely identifies each record
• Required for updates and deletes
• Used as the target of foreign key relationships

Single Primary Keys: Most entities have one primary key field (typically an ID)

Composite Primary Keys: Junction tables in many-to-many relationships use multiple fields as a composite key

Unique Constraints

Enforce uniqueness for field values:

Use Cases:

• Email addresses must be unique across users
• Username must be unique
• Product SKUs must be unique
• Serial numbers must be unique

Database Level: Enforced at the database layer for guaranteed consistency

Index Creation: Unique constraints automatically create database indexes for efficient lookups

Check Constraints

Validate field values with custom SQL expressions:

Validation Types:

• Numeric ranges (age >= 18, price > 0)
• String length limits
• Enum-like value restrictions
• Cross-field validations

Database Level: Enforced when inserting or updating records, preventing invalid data from being stored

Examples:

• Age must be positive
• Price cannot be negative
• End date must be after start date
• Quantity must be greater than zero

Default Values

Specify default values for optional fields:

Use Cases:

• Boolean flags defaulting to false
• Status fields defaulting to "pending"
• Creation timestamps defaulting to current time
• Numeric fields defaulting to zero

Behavior:

• Applied when creating records if no value provided
• Optional fields can remain nullable
• Reduces client-side code for common defaults

Not Null Constraints

Mark fields as required:

Schema Syntax: Fields marked as required cannot accept null values

Database Enforcement: Not null constraints enforced at database level

Use Cases:

• Essential identifying information (name, email)
• Critical relationship foreign keys
• Fields required for business logic

Multiple Constraints

Fields can have multiple constraints applied simultaneously:

Combinations:

• Unique + Not Null (required unique identifier)
• Default + Check (default value that passes validation)
• Primary Key + Default (auto-generated IDs)

Layered Validation: Constraints stack to provide comprehensive validation

---

Field Types

Standard Types

ID: Unique identifiers (typically strings or numbers)

String: Variable-length text

Int: Integer numbers (whole numbers)

Float: Decimal numbers (floating-point)

Boolean: True/false values

Time: Unix timestamps (seconds since epoch)

DateTime: Date and time strings

JSON: Structured JSON data stored in the database

Custom Types

UUID: Universally unique identifiers with automatic generation

ULID: Lexicographically sortable unique identifiers

Geospatial Types: Points, lines, polygons, and geometry collections for geographic data

Array Types: One-dimensional and two-dimensional arrays of primitive types (integers, strings, floats, booleans)

Nullable vs Non-Nullable

Required Fields: Marked with special notation in the schema, cannot be null

Optional Fields: Can accept null values

Lists: Can be marked as required (list itself cannot be null but can be empty) or optional

Field Naming

Conventions:

• Use camelCase for field names
• Avoid database reserved keywords
• Choose descriptive, meaningful names
• Keep names concise but clear

Database Mapping: Field names map to database column names (typically converted to snake_case automatically)

---

Schema Validation

The DDK validates schemas before generation to catch errors early:

Validation Rules

Naming Limits:

• Entity names, field names, and enum values have maximum length restrictions
• Prevents database identifier overflow

Keyword Checks:

• Validates against programming language reserved words
• Prevents using database reserved keywords
• Catches naming conflicts early

Directive Requirements:

• All entities must have required directives
• Relationship fields must have mapping directives
• Primary keys must be defined for table-backed entities

Type Checking:

• Field types must be recognized (built-in or custom)
• Relationship references must point to defined entities
• Enum references must use defined enum types

JSON Fields:

• JSON fields must be optional (nullable)
• Prevents mandatory JSON which complicates validation

Schema Errors

Common validation errors include:

Missing Required Directives: Entities without proper directive annotations

Undefined Type References: Relationships or fields referencing non-existent types

Invalid Constraint Values: Check constraints with invalid SQL syntax

Naming Conflicts: Entity or field names conflicting with reserved words

Missing Primary Keys: Table-backed entities without primary key definitions

---

Auto-Generated Operations

The DDK automatically generates operations based on your schema:

Generated Queries

Single Record Retrieval: Get one record by its primary key

List Queries: Retrieve all records with optional filtering, sorting, and pagination

Filter Parameters: Automatically generated for each field in the entity

Sorting: Sort by any field in ascending or descending order

Pagination: Limit and offset parameters for efficient large result set handling

Generated Mutations

Create Operations: Create new records with all required fields

Update Operations: Update existing records by primary key with partial field updates

Delete Operations: Delete records by primary key

Return Values:

• Create and Update return the modified entity
• Delete returns success/failure boolean

Query Features

Filtering: Filter lists by any scalar field value

Sorting: Order results by specified fields

Pagination: Limit result count and offset for page-based retrieval

Relationship Loading: Option to include related entities in queries

---

Best Practices

Schema Design

Start Simple: Begin with core entities and add relationships incrementally

Primary Keys Always: Every table-backed entity needs a unique identifier

Use Enums: For fields with fixed value sets (status, role, type)

Nullable Wisely: Required fields enforce data integrity; optional fields add flexibility

Unique Constraints: Enforce uniqueness for fields that should be unique (emails, usernames)

Relationship Design

One-to-One: Always use unique constraint on foreign key to enforce cardinality

One-to-Many: Foreign key on the "many" side referencing the "one" side's primary key

Many-to-Many: Always create explicit junction table with composite primary key

Back References: Use sparingly to avoid circular dependencies in queries

Constraints

Validate at Database Level: Use check constraints for critical business rules

Default Values: Set sensible defaults for optional fields

Check Constraints: Enforce valid ranges and formats

Multiple Constraints: Combine constraints for comprehensive validation

Naming Conventions

Entities: PascalCase (User, OrderItem)

Fields: camelCase (firstName, createdAt)

Enums: UPPERCASE (ADMIN, PENDING, ACTIVE)

Junction Tables: Combine entity names (UserRole, PostTag)

Performance

Indexes: Unique constraints automatically create indexes

Eager Loading: Define relationships to enable efficient relationship queries

Pagination: Always support pagination for list queries

JSON Fields: Use sparingly—structured fields enable better indexing and querying

Schema Evolution

Adding Fields: Safe and straightforward, no breaking changes

Removing Fields: Requires database migration to drop columns

Changing Types: Requires careful migration with data transformation

Adding Relationships: Add foreign key constraints carefully, ensure referential integrity

---

Common Patterns

Audit Fields

Track when records were created, modified, or deleted:

Standard Fields:

• Created timestamp: When the record was created
• Updated timestamp: When the record was last modified
• Deleted timestamp: For soft deletes (marking as deleted without physical removal)

Audit User Fields:

• Created by: User who created the record
• Updated by: User who last modified the record

Soft Deletes

Mark records as deleted without removing them from the database:

Benefits:

• Preserve historical data
• Enable undelete functionality
• Maintain referential integrity
• Support audit trails

Implementation:

• Deleted timestamp field
• Queries automatically filter out deleted records
• Admin operations can view deleted records

Multi-Tenant Schemas

Separate data by organization or customer:

Organization Entity: Container for tenant-specific data

Foreign Keys: All tenant-scoped entities reference the organization

Queries: Automatically filter by organization ID

Benefits:

• Data isolation between tenants
• Single application instance
• Simplified deployment

Hierarchical Data

Model tree structures and parent-child relationships:

Self-Referencing Foreign Key: Entity references itself as parent

Back Reference: Navigate from child to parent

Use Cases:

• Category hierarchies
• Organizational structures
• Comment threads
• Folder trees

---

Validation Checklist

Before generating your server, verify:

• ✅ All entities have required directive
• ✅ All database tables have primary key
• ✅ Foreign keys match referenced field types
• ✅ One-to-one relationships have unique constraint
• ✅ Many-to-many relationships have junction table
• ✅ Junction tables have composite primary keys
• ✅ Check constraints use valid SQL syntax
• ✅ Default values match field types
• ✅ Enum values are uppercase
• ✅ No circular dependencies in back references
• ✅ Field names follow naming conventions
• ✅ Required fields use appropriate notation

---

Related Documentation

• Architecture - How the generated code is structured
• Custom Resolvers - Extending beyond CRUD operations
• Examples - Real-world schema examples
• FAQs - Common questions and troubleshooting