Data Modeling Guide

Learn how to design robust, scalable data models that form the foundation of your Appivo applications.

Understanding Models

A Model represents a type of business object in your application. When you create a model, Appivo automatically:

• Creates the database table
• Generates API endpoints
• Handles data persistence
• Provides query capabilities

Model Structure

Every model consists of:

• Attributes - The fields that store data
• Relationships - Connections to other models
• Validations - Rules that ensure data quality
• System Fields - Automatically managed fields

Attribute Types

Basic Types

Specialized Types

Choosing the Right Type

Use this decision guide:

1. Will users enter this data? Consider input widgets
2. Does it need validation? Use specialized types (Email, Phone, URL)
3. Is it a list of options? Use Selection type
4. Could values be very long? Use Long Text instead of Text
5. Does it store files? Use File or Image types

Defining Relationships

Relationships connect models together, enabling you to build complex data structures.

One-to-Many

The most common relationship. One parent record relates to many child records.

Example: Customer Orders

Customer (1) ───────> (*) Order
One customer has many orders
Each order belongs to one customer

Implementation:

1. Create both models (Customer, Order)
2. Add a reference attribute to Order pointing to Customer
3. Appivo automatically creates the foreign key

Many-to-Many

Both sides can have multiple related records.

Example: Products and Categories

Product (*) <──────> (*) Category
Products can belong to multiple categories
Categories can contain multiple products

Implementation:

1. Create both models
2. Add a many-to-many relationship
3. Appivo creates a junction table automatically

One-to-One

Each record relates to exactly one record in another model.

Example: User Profile

User (1) ─────────── (1) Profile
Each user has exactly one profile
Each profile belongs to one user

Relationship Best Practices

1. Name relationships clearly - Use descriptive names that indicate the relationship
2. Consider cascade behavior - What happens when parent records are deleted?
3. Add appropriate indexes - Foreign keys are indexed automatically
4. Plan navigation - Consider how users will traverse relationships

Validation Rules

Protect data integrity with validation rules on attributes.

Available Validations

Validation Examples

Email Attribute

Attribute: email
Type: Email
Validations:
  - Required: true
  - Unique: true

Quantity Attribute

Attribute: quantity
Type: Integer
Validations:
  - Required: true
  - Min Value: 0
  - Max Value: 10000

Product Code

Attribute: product_code
Type: Text
Validations:
  - Required: true
  - Unique: true
  - Pattern: ^[A-Z]{3}-[0-9]{4}$
  - Max Length: 8

Indexes

Indexes improve query performance for frequently searched fields.

Automatic Indexes

Appivo automatically creates indexes for:

• Primary keys (id field)
• Foreign keys (relationship fields)
• Unique constraints

Custom Indexes

Add custom indexes for:

• Fields used in filters
• Fields used in sorting
• Fields used in search

Index Best Practices

1. Index search fields - Any field users search by frequently
2. Don't over-index - Each index adds write overhead
3. Consider composite indexes - For queries filtering multiple fields
4. Monitor query performance - Add indexes where queries are slow

System Fields

Every model automatically includes these system-managed fields:

Optimistic Locking

The ver field prevents conflicts when multiple users edit the same record:

1. User A loads record (ver: 1)
2. User B loads record (ver: 1)
3. User A saves changes (ver becomes 2)
4. User B tries to save - conflict detected (expected ver: 1, found ver: 2)
5. User B must reload and retry

Common Patterns

Reference Data

Create models for lookup values:

Model: Status
Attributes:
  - name (Text, required)
  - code (Text, required, unique)
  - display_order (Integer)
  - active (Boolean, default: true)

Audit Trail

Track all changes to important data:

Model: AuditLog
Attributes:
  - action (Selection: CREATE, UPDATE, DELETE)
  - model_name (Text)
  - record_id (Text)
  - changes (Long Text) - JSON of old/new values
  - user_id (Reference to User)
  - timestamp (DateTime)

Soft Delete

Mark records as deleted without removing them:

Model: Customer
Attributes:
  - ... (other fields)
  - deleted (Boolean, default: false)
  - deleted_at (DateTime, optional)
  - deleted_by (Reference to User, optional)

Hierarchical Data

Model tree structures:

Model: Category
Attributes:
  - name (Text, required)
  - parent (Reference to Category, optional)
  - path (Text) - for quick ancestry lookup
  - level (Integer) - depth in tree

Best Practices

Naming Conventions

• Use singular nouns for model names (Customer, not Customers)
• Use snake_case for attribute names (first_name, not firstName)
• Be descriptive but concise
• Avoid abbreviations unless universally understood

Data Normalization

• Store each piece of information in one place only
• Use references instead of duplicating data
• Create separate models for repeating groups

Planning Your Schema

1. List your business objects - What entities does your app manage?
2. Identify attributes - What information do you store for each?
3. Define relationships - How do entities connect?
4. Add validations - What rules ensure data quality?
5. Plan for growth - Consider future requirements

Performance Considerations

• Keep models focused - don't create mega-models with dozens of fields
• Index fields used in filters and searches
• Use pagination for large data sets
• Consider denormalization for read-heavy operations

Next Steps

• Rules and Actions - Automate based on data changes
• User Interfaces - Display and edit data
• Validation Guide - Advanced validation patterns