Schema Reference Guide

Note: API-style reference for defining schemas. For a conceptual overview, patterns, and best practices, see Database Schema & Relationships Guide.

← Back to Documentation Home

Introduction
Schema Structure
Field Types
Field Properties
Relationships
Schema Properties
Indexing
Default Values
Examples
Best Practices

Introduction

In BAASIX, schemas define the structure of your data collections. A schema specifies the fields, their types, relationships between collections, and other properties that determine how data is stored and validated.

Schema Structure

A schema definition contains the following key components:

{
  "collectionName": "products",
  "schema": {
    "name": "Product",
    "timestamps": true,
    "paranoid": true,
    "freezeTableName": true,
    "fields": {
      // Field definitions go here
    },
    "indexes": [
      // Index definitions go here
    ]
  }
}

Property	Type	Description
collectionName	string	Name of the database collection/table
schema	object	Object containing the schema definition
schema.name	string	Display name of the schema
schema.timestamps	boolean	Add createdAt and updatedAt fields automatically (default: true)
schema.paranoid	boolean	Enable soft deletes with deletedAt field (default: false)
schema.freezeTableName	boolean	Prevent pluralization of table name (default: false)
schema.fields	object	Object containing field definitions
schema.indexes	array	Array of index definitions

Field Types

BAASIX supports the following field types. For a complete guide on filtering these types, see Complete Filter Reference.

Supported Field Type Categories:

String Types: String, Text, CiText, TOKEN
Numeric Types: Integer, BigInt, Real, Double, Decimal
Date/Time Types: DateTime, DateTime_NO_TZ, Date, Time, Time_NO_TZ
Other Basic Types: Boolean, JSON, JSONB, UUID, ENUM
Array Types: Array_Integer, Array_String, Array_Double, Array_Decimal, Array_DateTime, Array_DateTime_NO_TZ, Array_Date, Array_Time, Array_Time_NO_TZ, Array_UUID, Array_Boolean
Range Types: Range_Integer, Range_DateTime, Range_DateTime_NO_TZ, Range_Date, Range_Decimal, Range_Double, Range_Time, Range_Time_NO_TZ
PostGIS Types: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, GeometryCollection, Geography
Computed Types: VIRTUAL

String Types

Type	Description	Example Value	Configuration
String	Variable-length string (default 255)	"Hello world"	`values.stringLength` for custom length
Text	Unlimited-length text	"Long paragraph of text..."	-
TEXT	Alias for Text	"Long paragraph of text..."	-
CiText	Case-insensitive text (requires citext extension)	"hello" matches "HELLO"	-
TOKEN	9-character unique string token	"ABC123XYZ"	Auto-generated

Numeric Types

Type	Description	Example Value	Configuration
Integer	32-bit signed integer	42	-
BigInt	64-bit signed integer	9007199254740991	-
Real	Single-precision floating-point	3.14	-
Double	Double-precision floating-point	3.141592653589793	-
DOUBLE	Alias for Double	3.141592653589793	-
Decimal	Arbitrary precision decimal	123.45	`values.precision`, `values.scale`

Date/Time Types

Timezone Pattern: Base types include timezone; append _NO_TZ for types without timezone.

Type	PostgreSQL Type	Description	Example Value
DateTime	timestamp with time zone	Timestamp WITH timezone	"2023-06-01T14:30:00Z"
DateTime_NO_TZ	timestamp	Timestamp WITHOUT timezone	"2023-06-01T14:30:00"
Date	date	Date only (no time)	"2023-06-01"
Time	time with time zone	Time WITH timezone	"14:30:00+05:30"
Time_NO_TZ	time	Time WITHOUT timezone	"14:30:00"

Other Basic Types

Type	Description	Example Value
Boolean	True/false value	true
JSON	Standard JSON type	`{"key": "value"}`
JSONB	Binary JSON (more efficient)	`{"key": "value"}`
UUID	Universally unique identifier	"550e8400-e29b-41d4-a716-446655440000"
ENUM	Predefined list of values	"active" (stored as varchar)

Array Types (PostgreSQL Arrays)

For filtering array fields, see Complete Filter Reference - Array Operators.

Type	Description	Example Value
Array_Integer	Array of integers	`[1, 2, 3, 4, 5]`
Array_String	Array of strings/text	`["red", "green", "blue"]`
Array_Double	Array of doubles	`[1.5, 2.7, 3.9]`
Array_Decimal	Array of decimals	`[10.50, 20.75, 30.25]`
Array_DateTime	Array of timestamps WITH TZ	`["2023-01-01T00:00:00Z", ...]`
Array_DateTime_NO_TZ	Array of timestamps WITHOUT TZ	`["2023-01-01T00:00:00", ...]`
Array_Date	Array of dates (date only)	`["2023-01-01", "2023-02-01"]`
Array_Time	Array of times WITH TZ	`["14:30:00+05:30", ...]`
Array_Time_NO_TZ	Array of times WITHOUT TZ	`["14:30:00", "15:45:00"]`
Array_UUID	Array of UUIDs	`["uuid1", "uuid2"]`
Array_Boolean	Array of booleans	`[true, false, true]`

Range Types (PostgreSQL Ranges)

Range types store lower/upper bounds with inclusivity flags. For filtering range fields, see Complete Filter Reference.

Type	PostgreSQL Type	Description	Example Value
Range_Integer	int4range	Integer range	`{"lower": 1, "upper": 10}`
Range_DateTime	tstzrange	Timestamp range WITH timezone	`{"lower": "2023-01-01T00:00:00Z", ...}`
Range_DateTime_NO_TZ	tsrange	Timestamp range WITHOUT timezone	`{"lower": "2023-01-01T00:00:00", ...}`
Range_Date	daterange	Date range (date only)	`{"lower": "2023-01-01", "upper": "2023-12-31"}`
Range_Decimal	numrange	Numeric/decimal range	`{"lower": 10.5, "upper": 100.5}`
Range_Double	numrange	Double precision range	`{"lower": 1.5, "upper": 99.9}`
Range_Time	timetzrange	Time range WITH timezone	`{"lower": "09:00:00+05:30", ...}`
Range_Time_NO_TZ	timerange	Time range WITHOUT timezone	`{"lower": "09:00:00", "upper": "17:00:00"}`

Range Value Structure:

{
  "lower": "value", // Lower bound
  "upper": "value", // Upper bound
  "lowerInclusive": true, // Include lower bound (default: true)
  "upperInclusive": true // Include upper bound (default: true)
}

PostGIS Geometry Types (requires PostGIS extension)

For geospatial filtering (within, contains, intersects, dwithin), see Complete Filter Reference - Geospatial Operators.

Type	Description	Example Value
Point	Single point in 2D space	`{"type": "Point", "coordinates": [lng, lat]}`
LineString	Line in 2D space	`{"type": "LineString", "coordinates": [[lng1,lat1], [lng2,lat2]]}`
Polygon	Polygon in 2D space	`{"type": "Polygon", "coordinates": [[[lng1,lat1], ...]]}`
MultiPoint	Multiple points	`{"type": "MultiPoint", "coordinates": [[lng1,lat1], ...]}`
MultiLineString	Multiple line strings	`{"type": "MultiLineString", ...}`
MultiPolygon	Multiple polygons	`{"type": "MultiPolygon", ...}`
GeometryCollection	Collection of mixed geometries	`{"type": "GeometryCollection", ...}`
Geography	Geographic data (Earth spheroid)	GeoJSON format

PostGIS Configuration: Use values.srid to set Spatial Reference System ID (default: 4326 for WGS84).

Computed/Virtual Types

Type	Description	Configuration
VIRTUAL	Computed field with SQL expression	Requires `calculated` property with SQL expression

Virtual Field Example:

{
  "fullName": {
    "type": "VIRTUAL",
    "calculated": "CONCAT(\"firstName\", ' ', \"lastName\")"
  }
}

Field Type Examples

{
  "fields": {
    "id": {
      "type": "UUID",
      "primaryKey": true,
      "defaultValue": {
        "type": "UUIDV4"
      }
    },
    "name": {
      "type": "String",
      "allowNull": false,
      "length": 100
    },
    "description": {
      "type": "Text"
    },
    "price": {
      "type": "Decimal",
      "values": {
        "precision": 10,
        "scale": 2
      }
    },
    "status": {
      "type": "Enum",
      "values": ["draft", "published", "archived"],
      "defaultValue": "draft"
    },
    "tags": {
      "type": "Array",
      "itemType": "String"
    }
  }
}

Field Properties

Each field can have various properties that define its behavior:

Property	Type	Description
type	string	The data type of the field
allowNull	boolean	Whether the field can be null (default: true)
unique	boolean	Whether the field must be unique (default: false)
primaryKey	boolean	Whether the field is the primary key (default: false)
autoIncrement	boolean	Auto-increment integer field (default: false)
defaultValue	various	Default value if none is provided
values	object/array	Additional values for types like Enum or Decimal
length	number	Maximum length for String type
comment	string	Comment/description for the field
validate	object	Validation rules for the field
hidden	boolean	Hide this field in API responses (default: false)
immutable	boolean	Field cannot be updated after creation (default: false)
description	string	Human-readable description of the field

Field Validation

BAASIX provides comprehensive field-level validation to ensure data integrity. Validation rules are applied during create and update operations.

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

Numeric Validation Examples

{
  "fields": {
    "age": {
      "type": "Integer",
      "validate": {
        "min": 0,
        "max": 120
      }
    },
    "price": {
      "type": "Decimal",
      "values": { "precision": 10, "scale": 2 },
      "validate": {
        "min": 0,
        "max": 999999.99
      }
    },
    "quantity": {
      "type": "Integer",
      "validate": {
        "isInt": true,
        "min": 1
      }
    }
  }
}

String Validation Examples

{
  "fields": {
    "email": {
      "type": "String",
      "validate": {
        "isEmail": true,
        "notEmpty": true
      }
    },
    "website": {
      "type": "String",
      "validate": {
        "isUrl": true
      }
    },
    "username": {
      "type": "String",
      "validate": {
        "notEmpty": true,
        "len": [3, 50]
      }
    },
    "zipCode": {
      "type": "String",
      "validate": {
        "matches": "^\\d{5}(-\\d{4})?$"
      }
    },
    "phone": {
      "type": "String",
      "validate": {
        "is": "^\\+?[1-9]\\d{1,14}$"
      }
    }
  }
}

Combined Validation Example

{
  "fields": {
    "shortDescription": {
      "type": "String",
      "validate": {
        "notEmpty": true,
        "len": [10, 255]
      }
    },
    "rating": {
      "type": "Integer",
      "validate": {
        "isInt": true,
        "min": 1,
        "max": 5
      }
    }
  }
}

When validation fails, the API returns a 400 Bad Request with details about which field and validation rule failed.

Relationships

BAASIX supports the following relationship types:

One-to-Many (hasMany/belongsTo)

{
  "fields": {
    "id": {
      "type": "UUID",
      "primaryKey": true
    },
    "name": {
      "type": "String"
    },
    // Foreign key for the belongsTo relationship
    "category_id": {
      "type": "UUID",
      "allowNull": true
    },
    // Relationship definition
    "category": {
      "relType": "BelongsTo",
      "target": "categories",
      "foreignKey": "category_id",
      "as": "category",
      "onDelete": "SET NULL"
    },
    "products": {
      "relType": "HasMany",
      "target": "products",
      "foreignKey": "category_id",
      "as": "products"
    }
  }
}

Many-to-Many (belongsToMany)

{
  "fields": {
    "id": {
      "type": "UUID",
      "primaryKey": true
    },
    "name": {
      "type": "String"
    },
    "tags": {
      "relType": "BelongsToMany",
      "target": "tags",
      "through": "product_tags",
      "foreignKey": "product_id",
      "otherKey": "tag_id",
      "as": "tags"
    }
  }
}

One-to-One (hasOne/belongsTo)

{
  "fields": {
    "id": {
      "type": "UUID",
      "primaryKey": true
    },
    "user_id": {
      "type": "UUID",
      "allowNull": false,
      "unique": true
    },
    "user": {
      "relType": "BelongsTo",
      "target": "baasix_User",
      "foreignKey": "user_id",
      "as": "user"
    },
    "profile": {
      "relType": "HasOne",
      "target": "profiles",
      "foreignKey": "user_id",
      "as": "profile"
    }
  }
}

Relationship Properties

Property	Type	Description
relType	string	Type of relationship (BelongsTo, HasMany, etc.)
target	string	Target collection name
foreignKey	string	Foreign key field name
otherKey	string	Other key in many-to-many relationships
through	string	Junction table for many-to-many relationships
as	string	Alias for the relationship
onDelete	string	Action on delete (CASCADE, SET NULL, etc.)
onUpdate	string	Action on update (CASCADE, RESTRICT, etc.)

Schema Properties

Additional properties you can set at the schema level:

Timestamps

When timestamps: true is set, the following fields are automatically added:

{
  "createdAt": {
    "type": "DateTime",
    "allowNull": false
  },
  "updatedAt": {
    "type": "DateTime",
    "allowNull": false
  }
}

Paranoid (Soft Delete)

When paranoid: true is set, the following field is added:

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

Records with a non-null deletedAt value are excluded from normal queries.

Junction Table

The isJunction: true property is automatically set on junction tables created for M2M and M2A relationships:

{
  "name": "posts_tags_tags_junction",
  "isJunction": true,
  "fields": {
    "id": { "type": "Integer", "primaryKey": true },
    "posts_id": { "type": "UUID", "allowNull": false },
    "tags_id": { "type": "UUID", "allowNull": false }
  }
}

Note: Junction tables are automatically created when you define M2M or M2A relationships. You don't need to create them manually. Use the through property to specify a custom junction table name.

Indexing

Define indexes to optimize query performance.

Auto-Indexing for Foreign Keys

BAASIX automatically creates indexes on foreign key columns when you create relationships:

M2O/O2O: Index on the FK column (e.g., author_Id)
M2M/M2A: Indexes on both FK columns in junction tables

This improves JOIN and filter performance without manual configuration.

Manual Index Definition

{
  "schema": {
    "fields": {
      // Field definitions
    },
    "indexes": [
      {
        "name": "products_name_idx",
        "fields": ["name"]
      },
      {
        "name": "products_category_status_idx",
        "fields": ["category_id", "status"]
      },
      {
        "name": "products_price_idx",
        "fields": [["price", "DESC"]]
      },
      {
        "name": "products_description_fulltext",
        "type": "FULLTEXT",
        "fields": ["description"]
      },
      {
        "name": "products_created_at_idx",
        "method": "BTREE",
        "fields": ["createdAt"]
      },
      {
        "name": "products_location_geo_idx",
        "type": "SPATIAL",
        "fields": ["location"]
      }
    ]
  }
}

Index Properties

Property	Type	Description
name	string	Name of the index
fields	array	Fields to index
type	string	Index type (BTREE, HASH, GIST, FULLTEXT, SPATIAL, etc.)
unique	boolean	Whether the index enforces uniqueness (default: false)
nullsNotDistinct	boolean	When true, NULL values are treated as equal in unique indexes (PostgreSQL 15+). Only applies when unique is true.
method	string	Index method (database-specific)

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.

Static Default Values

Simple constant values that are used when no value is provided:

{
  "status": {
    "type": "String",
    "defaultValue": "active"
  },
  "isVerified": {
    "type": "Boolean",
    "defaultValue": false
  },
  "score": {
    "type": "Integer",
    "defaultValue": 0
  },
  "priority": {
    "type": "Integer",
    "defaultValue": 100
  }
}

Dynamic Default Values

Dynamic default values are generated at runtime when a record is created:

{
  "id": {
    "type": "UUID",
    "primaryKey": true,
    "defaultValue": {
      "type": "UUIDV4"
    }
  },
  "shortId": {
    "type": "SUID",
    "defaultValue": {
      "type": "SUID"
    }
  },
  "createdAt": {
    "type": "DateTime",
    "defaultValue": {
      "type": "NOW"
    }
  },
  "orderNumber": {
    "type": "Integer",
    "defaultValue": {
      "type": "AUTOINCREMENT"
    }
  }
}

Default Value Types Reference

Type	Description	Use Case
`NOW`	Current date and time	Timestamps, createdAt fields
`UUIDV4`	Version 4 UUID (random)	Primary keys, unique identifiers
`SUID`	Short Unique ID (compact, URL-safe)	Short IDs, slugs, public identifiers
`AUTOINCREMENT`	Auto-incrementing integer	Sequential IDs, order numbers
`SQL`	Custom SQL expression	Complex calculations, sequences

SQL Default Values

For advanced use cases, you can use custom SQL expressions:

{
  "sortOrder": {
    "type": "Integer",
    "defaultValue": {
      "type": "SQL",
      "value": "(SELECT COALESCE(MAX(sort_order), 0) + 1 FROM products)"
    }
  },
  "randomCode": {
    "type": "String",
    "defaultValue": {
      "type": "SQL",
      "value": "md5(random()::text)"
    }
  },
  "fiscalYear": {
    "type": "Integer",
    "defaultValue": {
      "type": "SQL",
      "value": "EXTRACT(YEAR FROM CURRENT_DATE)"
    }
  }
}

SQL default values are executed directly on the database. Ensure your SQL expressions are valid for your database system and don't introduce security vulnerabilities.

Complete Default Values Example

{
  "collectionName": "orders",
  "schema": {
    "name": "Order",
    "timestamps": true,
    "fields": {
      "id": {
        "type": "UUID",
        "primaryKey": true,
        "defaultValue": { "type": "UUIDV4" }
      },
      "orderCode": {
        "type": "SUID",
        "unique": true,
        "defaultValue": { "type": "SUID" }
      },
      "orderNumber": {
        "type": "Integer",
        "defaultValue": { "type": "AUTOINCREMENT" }
      },
      "status": {
        "type": "String",
        "defaultValue": "pending"
      },
      "priority": {
        "type": "Integer",
        "defaultValue": 0
      },
      "isUrgent": {
        "type": "Boolean",
        "defaultValue": false
      },
      "placedAt": {
        "type": "DateTime",
        "defaultValue": { "type": "NOW" }
      }
    }
  }
}

Examples

Complete Product Schema Example

{
  "collectionName": "products",
  "schema": {
    "name": "Product",
    "timestamps": true,
    "paranoid": true,
    "fields": {
      "id": {
        "type": "UUID",
        "primaryKey": true,
        "defaultValue": {
          "type": "UUIDV4"
        },
        "description": "Unique identifier for the product"
      },
      "name": {
        "type": "String",
        "allowNull": false,
        "length": 100,
        "validate": {
          "notEmpty": true
        },
        "description": "Product name"
      },
      "slug": {
        "type": "String",
        "unique": true,
        "allowNull": false,
        "description": "URL-friendly product identifier"
      },
      "description": {
        "type": "Text",
        "description": "Detailed product description"
      },
      "price": {
        "type": "Decimal",
        "allowNull": false,
        "values": {
          "precision": 10,
          "scale": 2
        },
        "validate": {
          "min": 0
        },
        "description": "Product price"
      },
      "status": {
        "type": "Enum",
        "values": ["draft", "active", "archived"],
        "defaultValue": "draft",
        "description": "Current product status"
      },
      "featured": {
        "type": "Boolean",
        "defaultValue": false,
        "description": "Whether the product is featured"
      },
      "inventory": {
        "type": "Integer",
        "defaultValue": 0,
        "validate": {
          "min": 0
        },
        "description": "Current inventory level"
      },
      "metadata": {
        "type": "JSON",
        "description": "Additional product metadata"
      },
      "tags": {
        "type": "Array",
        "itemType": "String",
        "description": "Product tags"
      },
      "category_id": {
        "type": "UUID",
        "allowNull": true,
        "description": "Foreign key for category"
      },
      "category": {
        "relType": "BelongsTo",
        "target": "categories",
        "foreignKey": "category_id",
        "as": "category",
        "onDelete": "SET NULL",
        "description": "Product category"
      },
      "images": {
        "relType": "HasMany",
        "target": "product_images",
        "foreignKey": "product_id",
        "as": "images",
        "description": "Product images"
      }
    },
    "indexes": [
      {
        "name": "products_name_idx",
        "fields": ["name"]
      },
      {
        "name": "products_status_featured_idx",
        "fields": ["status", "featured"]
      },
      {
        "name": "products_price_idx",
        "fields": [["price", "DESC"]]
      },
      {
        "name": "products_description_fulltext",
        "type": "FULLTEXT",
        "fields": ["description"]
      }
    ]
  }
}

User Authentication Schema Example

{
  "collectionName": "users",
  "schema": {
    "name": "User",
    "timestamps": true,
    "fields": {
      "id": {
        "type": "UUID",
        "primaryKey": true,
        "defaultValue": {
          "type": "UUIDV4"
        }
      },
      "email": {
        "type": "String",
        "allowNull": false,
        "unique": true,
        "validate": {
          "isEmail": true
        }
      },
      "password": {
        "type": "String",
        "allowNull": false,
        "hidden": true
      },
      "firstName": {
        "type": "String",
        "allowNull": false
      },
      "lastName": {
        "type": "String",
        "allowNull": false
      },
      "role_id": {
        "type": "UUID",
        "allowNull": false
      },
      "role": {
        "relType": "BelongsTo",
        "target": "baasix_Role",
        "foreignKey": "role_id",
        "as": "role"
      },
      "isActive": {
        "type": "Boolean",
        "defaultValue": true
      },
      "lastLogin": {
        "type": "DateTime",
        "allowNull": true
      },
      "profile": {
        "relType": "HasOne",
        "target": "user_profiles",
        "foreignKey": "user_id",
        "as": "profile"
      }
    }
  }
}

Best Practices

Use UUID Primary Keys: UUIDs help prevent predictable IDs and are more suitable for distributed systems.
Apply Proper Validation: Define validation rules to ensure data integrity.
Include Descriptive Comments: Use the description field to document the purpose of each field.
Consider Indexing Performance: Only create indexes for fields that are frequently queried.
Use Relationships Properly: Define relationships to maintain data consistency and enable efficient querying.
Implement Soft Deletes: Use the paranoid option for collections where data should be preserved even after deletion.
Be Consistent with Naming: Follow a consistent naming pattern for fields and relationships.
Set Default Values: Provide sensible defaults to ensure data consistency.
Add Constraints: Use allowNull, unique, and validation rules to enforce data integrity.
Design with Future Changes in Mind: Create flexible schemas that can accommodate future changes.

Database Schema Guide - Conceptual overview, patterns, and best practices
Schema Routes API - API endpoints for schema management
Item Routes API - API endpoints for data operations
Item Query Reference Guide - Query language for data retrieval
Complete Filter Reference - All filter operators including array, range, and geospatial
Advanced Query Guide - Complex filtering, aggregation, and spatial queries

← Back to Documentation Home

On this page