BaasixBaasix

Database Schema & Relationships Guide

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

← Back to Documentation Home

Table of Contents

  1. Schema System Overview
  2. Field Types & Definitions
  3. Relationship Types
  4. Schema Creation Patterns
  5. Advanced Field Features
  6. Index Management
  7. Schema Validation
  8. Migration Strategies
  9. System Collections
  10. 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 Sequelize models.

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

BelongsTo (Many-to-One)

// Posts belong to Users (author)
// Schema creation
{
  "collectionName": "posts",
  "schema": {
    "name": "Post",
    "fields": {
      "id": {"type": "UUID", "primaryKey": true, "defaultValue": {"type": "UUIDV4"}},
      "title": {"type": "String", "allowNull": false},
      "content": {"type": "Text"},
      "author_Id": {
        "type": "UUID",
        "allowNull": false,
        "references": {
          "model": "baasix_User",
          "key": "id"
        }
      }
    }
  }
}

// Relationship definition
POST /schemas/relationships
{
  "sourceCollection": "posts",
  "targetCollection": "baasix_User",
  "type": "BelongsTo",
  "foreignKey": "author_Id",
  "as": "author"
}

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

HasMany (One-to-Many)

// Users have many Posts
POST /schemas/relationships
{
  "sourceCollection": "baasix_User",
  "targetCollection": "posts",
  "type": "HasMany",
  "foreignKey": "author_Id",
  "as": "posts"
}

// 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
  }
}

HasOne (One-to-One)

// User has one Profile
POST /schemas/relationships
{
  "sourceCollection": "baasix_User",
  "targetCollection": "profiles",
  "type": "HasOne",
  "foreignKey": "user_Id",
  "as": "profile"
}

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

BelongsToMany (Many-to-Many)

// Posts belong to many Tags (through PostTags junction table)
// First create the junction table
{
  "collectionName": "postTags",
  "schema": {
    "name": "PostTag",
    "fields": {
      "id": {"type": "UUID", "primaryKey": true, "defaultValue": {"type": "UUIDV4"}},
      "post_Id": {
        "type": "UUID",
        "references": {"model": "posts", "key": "id"}
      },
      "tag_Id": {
        "type": "UUID",
        "references": {"model": "tags", "key": "id"}
      }
    }
  }
}

// Create the relationship
POST /schemas/relationships
{
  "sourceCollection": "posts",
  "targetCollection": "tags",
  "type": "BelongsToMany",
  "through": "postTags",
  "foreignKey": "post_Id",
  "otherKey": "tag_Id",
  "as": "tags"
}

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

Polymorphic Relationships (M2A - Many-to-Any)

// Comments can belong to Posts or Pages
{
  "collectionName": "comments",
  "schema": {
    "name": "Comment",
    "fields": {
      "id": {"type": "UUID", "primaryKey": true, "defaultValue": {"type": "UUIDV4"}},
      "content": {"type": "Text", "allowNull": false},
      "related_Id": {"type": "UUID", "allowNull": false},
      "related_collection": {"type": "String", "allowNull": false}
    }
  }
}

// Create polymorphic relationship
POST /schemas/relationships
{
  "sourceCollection": "comments",
  "type": "M2A",
  "polymorphicField": "related",
  "allowedCollections": ["posts", "pages"],
  "as": "commentable"
}

// Reverse relationships
POST /schemas/relationships
{
  "sourceCollection": "posts",
  "targetCollection": "comments",
  "type": "HasMany",
  "polymorphicAs": "commentable",
  "as": "comments"
}

// Usage in queries
{
  "fields": [
    "id", "content",
    "commentable.title", // Works for both posts and pages
    "commentable.author.name" // Deep polymorphic queries
  ],
  "filter": {
    "related_collection": "posts",
    "commentable.status": "published"
  }
}

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

{
  "createdBy": {
    "type": "UUID",
    "defaultValue": {"type": "CURRENT_USER"} // Dynamic default
  },

  "tenantId": {
    "type": "UUID",
    "defaultValue": {"type": "CURRENT_TENANT"} // Multi-tenant default
  },

  "timestamp": {
    "type": "DateTime",
    "defaultValue": {"type": "NOW"} // Current timestamp
  },

  "status": {
    "type": "String",
    "defaultValue": "active" // Static default
  },

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

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

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}$"
    }
  }
}

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

Querying Data

Access Control

Automation

Deployment

← Back to Documentation Home

Schema Reference Guide

Schema Reference Guide

Notification Routes

Notification Routes

On this page

Database Schema & Relationships GuideTable of ContentsSchema System OverviewCore Schema StructureField Types & DefinitionsBasic Data TypesString TypesNumeric TypesBoolean and Date TypesUUID and JSON TypesArray TypesEnum TypesGeospatial Types (PostGIS)Field Validation OptionsRelationship TypesBelongsTo (Many-to-One)HasMany (One-to-Many)HasOne (One-to-One)BelongsToMany (Many-to-Many)Polymorphic Relationships (M2A - Many-to-Any)Schema Creation PatternsComplete Blog Schema ExampleAdvanced Field FeaturesDefault ValuesComputed FieldsField HooksIndex ManagementCreating IndexesIndex Performance ConsiderationsSchema ValidationBuilt-in ValidationsCustom Validation FunctionsMigration StrategiesAdding FieldsModifying FieldsSchema VersioningSystem CollectionsCore System CollectionsBest PracticesSchema Design PrinciplesPerformance OptimizationSecurity ConsiderationsRelated DocumentationSchema & TypesQuerying DataAccess ControlAutomationDeployment