Database Schema & Relationships Guide

Note: Conceptual and patterns-focused. For the API/field property reference, see Schema Reference Guide.

Schema System Overview
Field Types & Definitions
Relationship Types
Schema Creation Patterns
Advanced Field Features
Index Management
Schema Validation
Migration Strategies
System Collections
Best Practices

Schema System Overview

BAASIX uses a dynamic schema system that allows you to create and modify database tables and relationships at runtime. All schemas are stored in the baasix_Schema collection and automatically create corresponding PostgreSQL tables with Drizzle ORM.

Core Schema Structure

{
  "collectionName": "posts", // Table name
  "schema": {
    "name": "Post", // Model name
    "timestamps": true, // Add createdAt/updatedAt
    "paranoid": false, // Soft deletes (deletedAt)
    "indexes": [ /* custom indexes */ ],
    "fields": {
      "id": {
        "type": "UUID",
        "primaryKey": true,
        "defaultValue": {"type": "UUIDV4"}
      },
      // ... other fields
    }
  }
}

Field Types & Definitions

Basic Data Types

String Types

{
  "title": {
    "type": "String",
    "allowNull": false,
    "values": {
      "length": 255 // VARCHAR(255)
    },
    "unique": false,
    "defaultValue": null
  },

  "description": {
    "type": "Text", // Unlimited length
    "allowNull": true
  },

  "slug": {
    "type": "String",
    "allowNull": false,
    "unique": true,
    "validate": {
      "is": "^[a-z0-9-]+$" // Regex validation
    }
  }
}

Numeric Types

{
  "age": {
    "type": "Integer",
    "allowNull": false,
    "validate": {
      "min": 0,
      "max": 150
    }
  },

  "price": {
    "type": "Decimal",
    "values": {
      "precision": 10,
      "scale": 2
    },
    "allowNull": false,
    "defaultValue": 0.00
  },

  "rating": {
    "type": "Double",
    "validate": {
      "min": 0.0,
      "max": 5.0
    }
  },

  "views": {
    "type": "BigInt", // For large integers
    "defaultValue": 0
  }
}

Boolean and Date Types

{
  "isPublished": {
    "type": "Boolean",
    "allowNull": false,
    "defaultValue": false
  },

  "publishedAt": {
    "type": "DateTime",
    "allowNull": true
  },

  "birthDate": {
    "type": "Date", // Date only (no time)
    "allowNull": true
  },

  "workingHours": {
    "type": "Time", // Time only
    "allowNull": true
  }
}

UUID and JSON Types

{
  "id": {
    "type": "UUID",
    "primaryKey": true,
    "defaultValue": {"type": "UUIDV4"}
  },

  "externalId": {
    "type": "UUID",
    "allowNull": true,
    "unique": true
  },

  "metadata": {
    "type": "JSON",
    "allowNull": true,
    "defaultValue": {}
  },

  "settings": {
    "type": "JSONB", // PostgreSQL JSONB (indexed)
    "allowNull": true,
    "defaultValue": {}
  }
}

Array Types

{
  "tags": {
    "type": "Array",
    "values": {
      "type": "String"
    },
    "allowNull": true,
    "defaultValue": []
  },

  "scores": {
    "type": "Array",
    "values": {
      "type": "Integer"
    },
    "allowNull": true
  },

  "permissions": {
    "type": "Array",
    "values": {
      "type": "UUID"
    },
    "allowNull": true,
    "defaultValue": []
  }
}

Enum Types

{
  "status": {
    "type": "Enum",
    "values": {
      "enums": ["draft", "published", "archived", "deleted"]
    },
    "allowNull": false,
    "defaultValue": "draft"
  },

  "priority": {
    "type": "Enum",
    "values": {
      "enums": ["low", "medium", "high", "urgent"]
    },
    "allowNull": false,
    "defaultValue": "medium"
  }
}

Geospatial Types (PostGIS)

{
  "location": {
    "type": "Geometry",
    "values": {
      "srid": 4326, // WGS84 coordinate system
      "type": "Point"
    },
    "allowNull": true
  },

  "boundary": {
    "type": "Geometry",
    "values": {
      "srid": 4326,
      "type": "Polygon"
    },
    "allowNull": true
  },

  "path": {
    "type": "Geometry",
    "values": {
      "srid": 4326,
      "type": "LineString"
    },
    "allowNull": true
  }
}

Field Validation Options

{
  "email": {
    "type": "String",
    "allowNull": false,
    "unique": true,
    "validate": {
      "isEmail": true, // Built-in email validation
      "len": [5, 255] // Length validation
    }
  },

  "phone": {
    "type": "String",
    "validate": {
      "is": "^\\+?[1-9]\\d{1,14}$" // Regex pattern
    }
  },

  "website": {
    "type": "String",
    "validate": {
      "isUrl": true // Built-in URL validation
    }
  },

  "age": {
    "type": "Integer",
    "validate": {
      "min": 0,
      "max": 120,
      "isInt": true
    }
  }
}

Relationship Types

BAASIX supports five relationship types that automatically create and manage foreign keys, junction tables, and indexes.

Key Features

Auto-indexing: Foreign key columns are automatically indexed for better query performance
Junction tables: M2M and M2A relationships auto-generate junction tables (or use custom names via through)
Bidirectional access: Use alias to enable reverse relationship queries

M2O / BelongsTo (Many-to-One)

// Posts belong to Users (author)
POST /schemas/posts/relationships
{
  "type": "M2O",
  "target": "baasix_User",
  "name": "author",           // Creates author_Id foreign key field
  "alias": "posts",           // Reverse relation on users (optional)
  "onDelete": "CASCADE"       // CASCADE, RESTRICT, SET NULL
}
// Auto-creates index on author_Id column

// Usage in queries
{
  "fields": ["id", "title", "author.name", "author.email"],
  "filter": {
    "author.status": "active"
  }
}

O2M / HasMany (One-to-Many)

// O2M is automatically created as the reverse of M2O when alias is provided
// Access: user.posts (returns array)

// Usage in queries
{
  "fields": [
    "id", "name", "email",
    "posts.title", "posts.createdAt"
  ],
  "filter": {
    "posts.status": "published",
    "posts.$count": {"gt": 5} // Users with more than 5 posts
  }
}

O2O / HasOne (One-to-One)

// User has one Profile
POST /schemas/baasix_User/relationships
{
  "type": "O2O",
  "target": "profiles",
  "name": "profile",
  "alias": "user"
}

// Usage in queries
{
  "fields": [
    "id", "email",
    "profile.firstName", "profile.lastName", "profile.avatar"
  ],
  "filter": {
    "profile.verified": true
  }
}

M2M / BelongsToMany (Many-to-Many)

M2M relationships automatically create a junction table. You don't need to create it manually.

// Posts have many Tags
POST /schemas/posts/relationships
{
  "type": "M2M",
  "target": "tags",
  "name": "tags",
  "alias": "posts"            // Reverse relation on tags
}
// Auto-generates junction table: posts_tags_tags_junction
// Auto-creates indexes on both FK columns

// With custom junction table name (useful for long collection names)
POST /schemas/posts/relationships
{
  "type": "M2M",
  "target": "tags",
  "name": "tags",
  "alias": "posts",
  "through": "post_tags"      // Custom name (max 63 chars for PostgreSQL)
}

// Usage in queries
{
  "fields": [
    "id", "title",
    "tags.name", "tags.color"
  ],
  "filter": {
    "tags.name": {"in": ["javascript", "nodejs"]}
  }
}

M2A / Many-to-Any (Polymorphic)

M2A relationships allow relating to multiple different collections (polymorphic).

// Comments can belong to Posts OR Products
POST /schemas/comments/relationships
{
  "type": "M2A",
  "name": "commentable",
  "tables": ["posts", "products"],  // Can relate to multiple collections
  "alias": "comments",
  "through": "comment_refs"          // Optional custom junction table name
}
// Creates polymorphic junction table with collection column

// Usage in queries
{
  "fields": [
    "id", "content",
    "commentable.title"   // Works for both posts and products
  ],
  "filter": {
    "commentable.status": "published"
  }
}

Junction Tables

Junction tables are automatically created for M2M and M2A relationships.

Feature	Description
Auto-generated name	`{source}_{target}_{name}_junction`
Custom name	Use `through` property (max 63 chars for PostgreSQL)
Schema property	Junction tables have `isJunction: true` in their schema
Auto-indexed	All FK columns are automatically indexed for performance

// Example junction table schema (auto-generated)
{
  "name": "posts_tags_tags_junction",
  "isJunction": true,
  "fields": {
    "id": {"type": "Integer", "primaryKey": true, "defaultValue": {"type": "AUTOINCREMENT"}},
    "posts_id": {"type": "UUID", "allowNull": false},
    "tags_id": {"type": "UUID", "allowNull": false}
  },
  "indexes": [
    {"name": "..._unique", "fields": ["posts_id", "tags_id"], "unique": true},
    {"name": "..._posts_id_idx", "fields": ["posts_id"]},
    {"name": "..._tags_id_idx", "fields": ["tags_id"]}
  ]
}

Schema Creation Patterns

Complete Blog Schema Example

// 1. Create Categories
POST /schemas
{
  "collectionName": "categories",
  "schema": {
    "name": "Category",
    "timestamps": true,
    "fields": {
      "id": {"type": "UUID", "primaryKey": true, "defaultValue": {"type": "UUIDV4"}},
      "name": {"type": "String", "allowNull": false, "unique": true},
      "slug": {"type": "String", "allowNull": false, "unique": true},
      "description": {"type": "Text"},
      "parent_Id": {"type": "UUID", "allowNull": true}
    }
  }
}

// 2. Create Tags
POST /schemas
{
  "collectionName": "tags",
  "schema": {
    "name": "Tag",
    "timestamps": true,
    "fields": {
      "id": {"type": "UUID", "primaryKey": true, "defaultValue": {"type": "UUIDV4"}},
      "name": {"type": "String", "allowNull": false, "unique": true},
      "color": {"type": "String", "defaultValue": "#000000"}
    }
  }
}

// 3. Create Posts
POST /schemas
{
  "collectionName": "posts",
  "schema": {
    "name": "Post",
    "timestamps": true,
    "paranoid": true, // Soft deletes
    "fields": {
      "id": {"type": "UUID", "primaryKey": true, "defaultValue": {"type": "UUIDV4"}},
      "title": {"type": "String", "allowNull": false},
      "slug": {"type": "String", "allowNull": false, "unique": true},
      "content": {"type": "Text"},
      "excerpt": {"type": "Text"},
      "featured_image": {"type": "UUID", "allowNull": true},
      "status": {
        "type": "Enum",
        "values": {"enums": ["draft", "published", "archived"]},
        "defaultValue": "draft"
      },
      "publishedAt": {"type": "DateTime", "allowNull": true},
      "views": {"type": "Integer", "defaultValue": 0},
      "likes": {"type": "Integer", "defaultValue": 0},
      "author_Id": {"type": "UUID", "allowNull": false},
      "category_Id": {"type": "UUID", "allowNull": true},
      "metadata": {"type": "JSONB", "defaultValue": {}}
    },
    "indexes": [
      {"fields": ["status"]},
      {"fields": ["publishedAt"]},
      {"fields": ["author_Id"]},
      {"fields": ["category_Id"]},
      {"fields": ["slug"], "unique": true},
      {"fields": ["title"], "type": "fulltext"}
    ]
  }
}

// 4. Create junction table for Post-Tag relationship
POST /schemas
{
  "collectionName": "postTags",
  "schema": {
    "name": "PostTag",
    "fields": {
      "id": {"type": "UUID", "primaryKey": true, "defaultValue": {"type": "UUIDV4"}},
      "post_Id": {"type": "UUID", "allowNull": false},
      "tag_Id": {"type": "UUID", "allowNull": false}
    },
    "indexes": [
      {"fields": ["post_Id", "tag_Id"], "unique": true}
    ]
  }
}

// 5. Create all relationships
POST /schemas/relationships
[
  {
    "sourceCollection": "posts",
    "targetCollection": "baasix_User",
    "type": "BelongsTo",
    "foreignKey": "author_Id",
    "as": "author"
  },
  {
    "sourceCollection": "posts",
    "targetCollection": "categories",
    "type": "BelongsTo",
    "foreignKey": "category_Id",
    "as": "category"
  },
  {
    "sourceCollection": "posts",
    "targetCollection": "tags",
    "type": "BelongsToMany",
    "through": "postTags",
    "foreignKey": "post_Id",
    "otherKey": "tag_Id",
    "as": "tags"
  },
  {
    "sourceCollection": "categories",
    "targetCollection": "categories",
    "type": "BelongsTo",
    "foreignKey": "parent_Id",
    "as": "parent"
  },
  {
    "sourceCollection": "categories",
    "targetCollection": "categories",
    "type": "HasMany",
    "foreignKey": "parent_Id",
    "as": "children"
  }
]

Advanced Field Features

Default Values

BAASIX supports multiple ways to set default values for fields. Default values are automatically applied when creating new records if no value is provided.

Default Value Types

Type	Description	Use Case
`UUIDV4`	Random UUID v4	Primary keys, unique identifiers
`SUID`	Short unique ID (compact, URL-safe)	Short IDs, slugs, public identifiers
`NOW`	Current timestamp	createdAt, timestamps
`AUTOINCREMENT`	Auto-incrementing integer	Sequential IDs, order numbers
`SQL`	Custom SQL expression	Complex calculations, sequences
`CURRENT_USER`	Current user's ID	createdBy, ownership tracking
`CURRENT_TENANT`	Current tenant's ID	Multi-tenant isolation
Static	Any constant value	Default states, initial values

Default Value Examples

{
  "id": {
    "type": "UUID",
    "primaryKey": true,
    "defaultValue": {"type": "UUIDV4"}
  },

  "shortId": {
    "type": "SUID",
    "unique": true,
    "defaultValue": {"type": "SUID"}
  },

  "orderNumber": {
    "type": "Integer",
    "defaultValue": {"type": "AUTOINCREMENT"}
  },

  "createdAt": {
    "type": "DateTime",
    "defaultValue": {"type": "NOW"}
  },

  "createdBy": {
    "type": "UUID",
    "defaultValue": {"type": "CURRENT_USER"}
  },

  "tenantId": {
    "type": "UUID",
    "defaultValue": {"type": "CURRENT_TENANT"}
  },

  "status": {
    "type": "String",
    "defaultValue": "active"
  },

  "priority": {
    "type": "Integer",
    "defaultValue": 0
  },

  "isActive": {
    "type": "Boolean",
    "defaultValue": true
  },

  "settings": {
    "type": "JSONB",
    "defaultValue": {
      "notifications": true,
      "theme": "light"
    }
  },

  "sortOrder": {
    "type": "Integer",
    "defaultValue": {
      "type": "SQL",
      "value": "(SELECT COALESCE(MAX(sort_order), 0) + 1 FROM products)"
    }
  }
}

Computed Fields

{
  "fullName": {
    "type": "Virtual", // Not stored in database
    "get": "CONCAT(firstName, ' ', lastName)"
  },

  "ageInYears": {
    "type": "Virtual",
    "get": "EXTRACT(YEAR FROM AGE(birthDate))"
  },

  "slug": {
    "type": "String",
    "autoGenerate": {
      "from": "title",
      "transform": "slug" // Auto-generate slug from title
    }
  }
}

Field Hooks

{
  "password": {
    "type": "String",
    "allowNull": false,
    "hooks": {
      "beforeCreate": "hashPassword",
      "beforeUpdate": "hashPassword"
    }
  },

  "email": {
    "type": "String",
    "hooks": {
      "beforeSave": "toLowerCase"
    }
  }
}

Index Management

Creating Indexes

{
  "collectionName": "posts",
  "schema": {
    // ... fields ...
    "indexes": [
      // Simple index
      {"fields": ["status"]},

      // Compound index
      {"fields": ["status", "publishedAt"]},

      // Unique index
      {"fields": ["slug"], "unique": true},

      // Partial index
      {"fields": ["title"], "where": {"status": "published"}},

      // Full-text index
      {"fields": ["title", "content"], "type": "fulltext"},

      // GIN index for JSON
      {"fields": ["metadata"], "using": "GIN"},

      // Spatial index (PostGIS)
      {"fields": ["location"], "using": "GIST"}
    ]
  }
}

Index Performance Considerations

// ✅ Good: Index frequently filtered fields
{"fields": ["status"]}, // Often filtered
{"fields": ["createdAt"]}, // Often sorted

// ✅ Good: Compound indexes for multi-field queries
{"fields": ["author_Id", "status"]}, // Filtered together

// ❌ Bad: Too many indexes on write-heavy tables
// Every index slows down INSERT/UPDATE operations

// ✅ Good: Partial indexes for specific conditions
{"fields": ["publishedAt"], "where": {"status": "published"}}

Schema Validation

BAASIX provides comprehensive field-level validation that is enforced during create and update operations. When validation fails, the API returns a 400 Bad Request with details about which field and rule failed.

Validation Rules Reference

Rule	Applies To	Type	Description
`min`	Integer, Float, Decimal	number	Minimum value (inclusive)
`max`	Integer, Float, Decimal	number	Maximum value (inclusive)
`isInt`	Integer	boolean	Validates that value is an integer
`notEmpty`	String, Text	boolean	String must not be empty
`isEmail`	String	boolean	Validates email format
`isUrl`	String	boolean	Validates URL format
`len`	String, Text	[min, max]	String length range
`is` / `matches`	String, Text	regex string	Pattern matching with regex

Built-in Validations

{
  "email": {
    "type": "String",
    "validate": {
      "isEmail": true,
      "notEmpty": true,
      "len": [1, 255]
    }
  },

  "age": {
    "type": "Integer",
    "validate": {
      "min": 0,
      "max": 150,
      "isInt": true
    }
  },

  "website": {
    "type": "String",
    "validate": {
      "isUrl": true
    }
  },

  "phoneNumber": {
    "type": "String",
    "validate": {
      "is": "^\\+?[1-9]\\d{1,14}$"
    }
  },

  "username": {
    "type": "String",
    "validate": {
      "notEmpty": true,
      "len": [3, 50],
      "matches": "^[a-z0-9_-]+$"
    }
  },

  "price": {
    "type": "Decimal",
    "values": { "precision": 10, "scale": 2 },
    "validate": {
      "min": 0,
      "max": 999999.99
    }
  }
}

Custom Validation Functions

{
  "price": {
    "type": "Decimal",
    "validate": {
      "isPositive": function(value) {
        if (value <= 0) {
          throw new Error('Price must be positive');
        }
      }
    }
  },

  "endDate": {
    "type": "Date",
    "validate": {
      "isAfterStartDate": function(value) {
        if (this.startDate && value <= this.startDate) {
          throw new Error('End date must be after start date');
        }
      }
    }
  }
}

Migration Strategies

Adding Fields

// Safe field addition
PATCH /schemas/posts
{
  "schema": {
    "fields": {
      "newField": {
        "type": "String",
        "allowNull": true, // Important for existing records
        "defaultValue": "default_value"
      }
    }
  }
}

Modifying Fields

// Safe field modification
PATCH /schemas/posts
{
  "schema": {
    "fields": {
      "existingField": {
        "type": "String",
        "allowNull": true, // Relaxing constraint
        "values": {"length": 500} // Increasing length
      }
    }
  }
}

Schema Versioning

// Version tracking in schema
{
  "collectionName": "posts",
  "schema": {
    "version": "1.2.0",
    "migrations": [
      {
        "version": "1.1.0",
        "description": "Added metadata field",
        "date": "2025-01-15"
      },
      {
        "version": "1.2.0",
        "description": "Added full-text search index",
        "date": "2025-01-20"
      }
    ]
    // ... rest of schema
  }
}

System Collections

BAASIX uses several system collections for its operation:

Core System Collections

// Users
"baasix_User": {
  "id": "UUID",
  "email": "String",
  "password": "String",
  "firstName": "String",
  "lastName": "String",
  "status": "Enum",
  "lastLoginAt": "DateTime",
  "tenant_Id": "UUID" // Multi-tenant
}

// Roles
"baasix_Role": {
  "id": "UUID",
  "name": "String",
  "description": "Text",
  "tenant_Id": "UUID"
}

// Permissions
"baasix_Permission": {
  "id": "UUID",
  "role_Id": "UUID",
  "collection": "String",
  "action": "Enum", // read, create, update, delete
  "fields": "Array",
  "filter": "JSONB",
  "tenant_Id": "UUID"
}

// Schema definitions
"baasix_Schema": {
  "id": "UUID",
  "collectionName": "String",
  "schema": "JSONB",
  "version": "String",
  "tenant_Id": "UUID"
}

// Files
"baasix_File": {
  "id": "UUID",
  "filename": "String",
  "type": "String",
  "size": "Integer",
  "path": "String",
  "metadata": "JSONB",
  "tenant_Id": "UUID"
}

// Notifications
"baasix_Notification": {
  "id": "UUID",
  "type": "String",
  "title": "String",
  "message": "Text",
  "data": "JSONB",
  "userId": "UUID",
  "seen": "Boolean",
  "tenant_Id": "UUID"
}

// Settings
"baasix_Settings": {
  "id": "UUID",
  "key": "String",
  "value": "JSONB",
  "category": "String",
  "tenant_Id": "UUID"
}

Best Practices

Schema Design Principles

// ✅ Good: Use descriptive names
{
  "collectionName": "blog_posts", // Clear purpose
  "schema": {
    "fields": {
      "author_Id": {"type": "UUID"}, // Clear relationship
      "publishedAt": {"type": "DateTime"} // Clear meaning
    }
  }
}

// ❌ Bad: Cryptic names
{
  "collectionName": "bp", // Unclear
  "schema": {
    "fields": {
      "uid": {"type": "UUID"}, // Unclear relationship
      "dt": {"type": "DateTime"} // Unclear purpose
    }
  }
}

// ✅ Good: Consistent naming conventions
{
  "author_Id": "UUID", // Foreign keys end with _Id
  "createdAt": "DateTime", // Timestamps use camelCase
  "isPublished": "Boolean" // Booleans start with is/has
}

// ✅ Good: Proper field types
{
  "price": {"type": "Decimal", "values": {"precision": 10, "scale": 2}}, // Money
  "quantity": {"type": "Integer"}, // Counts
  "description": {"type": "Text"}, // Long text
  "status": {"type": "Enum", "values": {"enums": ["active", "inactive"]}} // Fixed values
}

Performance Optimization

// ✅ Index frequently queried fields
{
  "indexes": [
    {"fields": ["status"]}, // Often filtered
    {"fields": ["createdAt"]}, // Often sorted
    {"fields": ["author_Id", "status"]}, // Compound queries
    {"fields": ["title"], "type": "fulltext"} // Search
  ]
}

// ✅ Use appropriate field sizes
{
  "title": {"type": "String", "values": {"length": 255}}, // Not too long
  "slug": {"type": "String", "values": {"length": 100}}, // Reasonable limit
  "content": {"type": "Text"} // Unlimited when needed
}

// ✅ Normalize when appropriate
// Separate collections for reusable entities
{
  "categories": { /* category definition */ },
  "posts": {
    "category_Id": "UUID" // Reference, not embed
  }
}

Security Considerations

// ✅ Use UUIDs for primary keys (harder to guess)
{
  "id": {
    "type": "UUID",
    "primaryKey": true,
    "defaultValue": {"type": "UUIDV4"}
  }
}

// ✅ Add audit fields
{
  "createdBy": {"type": "UUID", "defaultValue": {"type": "CURRENT_USER"}},
  "updatedBy": {"type": "UUID", "defaultValue": {"type": "CURRENT_USER"}},
  "createdAt": {"type": "DateTime", "defaultValue": {"type": "NOW"}},
  "updatedAt": {"type": "DateTime", "defaultValue": {"type": "NOW"}}
}

// ✅ Use paranoid mode for sensitive data
{
  "paranoid": true, // Soft deletes only
  "fields": {
    "deletedAt": {"type": "DateTime", "allowNull": true},
    "deletedBy": {"type": "UUID", "allowNull": true}
  }
}

Schema & Types

Schema Routes - API for schema management
Schema Reference Guide - Complete field type reference (Range, Array, PostGIS)

Querying Data

Item Routes - CRUD operations
Item Query Reference Guide - Query parameters and pagination
Complete Filter Reference - All filter operators including array/range/geospatial
Advanced Query Guide - Complex filtering and aggregation

Access Control

Permission Routes - Field-level permission management
Multi-tenant Guide - Tenant-scoped schemas

Automation

Hooks System - Schema event hooks
Hooks and Endpoints Guide - Hook patterns for schema changes

Deployment

Deployment Guide - Database configuration and PostGIS setup

← Back to Documentation Home

On this page