Schema reference

A table schema declares its fields, primary key, and optional foreign keys. This reference covers managed relational tables. TaruviBase stores a Frictionless descriptor with each table and maps it to a managed PostgreSQL table.

Storage and schema compatibility

Every table records a storage mode and a schema format. The Database workflows in this documentation use:

Setting	Required value
Storage mode	`flat_table`
Schema format	`frictionless`
Materialization state	`is_materialized: true`

flat_table maps declared fields to PostgreSQL columns and supported constraints. A field declared as {"type":"object"} becomes one JSONB column inside the managed table. Whole-table JSONB storage follows a different record and query contract, so apply the SDK examples here only to tables that match the settings above.

Before applying these examples to an existing table, confirm that provider_type is flat_table, schema_format is frictionless, and is_materialized is true.

Descriptor

{
  "title": "Tasks",
  "description": "Work tracked by the application",
  "fields": [
    {
      "name": "id",
      "type": "string",
      "format": "uuid",
      "constraints": {
        "required": true
      }
    },
    {
      "name": "title",
      "type": "string",
      "constraints": {
        "required": true,
        "maxLength": 200
      }
    }
  ],
  "primaryKey": ["id"],
  "foreignKeys": []
}

Property	Type	Purpose
`fields`	array	Fields stored in every record
`primaryKey`	array containing one field name	Field used to identify a record in SDK and REST operations
`foreignKeys`	array	Connections from local fields to another table
`title`	string	Optional display title
`description`	string	Optional explanation of the table

Declare primaryKey as an array, including when the table has one key.

Fields

Every item in fields has a name and type:

{
  "name": "priority",
  "type": "integer",
  "description": "Priority from 1 to 5",
  "constraints": {
    "required": true,
    "minimum": 1,
    "maximum": 5
  }
}

Property	Required	Purpose
`name`	Yes	Key used in records and queries
`type`	Yes	Logical value type
`format`	No	Type refinement, such as `uuid` for a string
`arrayItem`	For typed arrays	Declares the type stored in each array element
`constraints`	No	Required, unique, length, range, or enum rules
`description`	No	Human-readable field description
`x-pg-type`	For range-family fields	Declares a PostgreSQL range or multirange type

Use names that start with a letter or underscore and contain only letters, numbers, and underscores. Lowercase snake_case is the recommended convention for every interface.

Field types

For a managed flat table, these declarations produce:

Schema declaration	PostgreSQL value
`{"type":"string"}`	Text, or bounded text with `maxLength`
`{"type":"string","format":"uuid"}`	UUID
`{"type":"integer"}`	Integer
`{"type":"number"}`	Numeric
`{"type":"boolean"}`	Boolean
`{"type":"date"}`	Date
`{"type":"datetime"}`	Timestamp
`{"type":"object"}`	PostgreSQL JSONB column containing a JSON object
`{"type":"array"}`	PostgreSQL text array
`{"type":"array","arrayItem":{"type":"integer"}}`	PostgreSQL big-integer array
`{"type":"any","x-pg-type":"numrange"}`	PostgreSQL range

Managed flat tables also accept these provisioned field declarations:

Declaration	PostgreSQL value
`{"type":"time"}`	Time
`{"type":"year"}`	Integer year
`{"type":"geojson"}`	JSONB

Text

maxLength creates bounded text. minLength adds a minimum-length check:

{
  "name": "code",
  "type": "string",
  "constraints": {
    "minLength": 3,
    "maxLength": 20
  }
}

UUID

Declare UUID values as strings with the uuid format:

{
  "name": "id",
  "type": "string",
  "format": "uuid",
  "constraints": {
    "required": true
  }
}

TaruviBase generates the UUID when it is omitted. Supply one with an SDK or REST write when the application owns record identity.

JSON objects

Use object for a structured JSON value in an otherwise ordinary flat table. Database stores this field in a PostgreSQL JSONB column:

{
  "name": "preferences",
  "type": "object"
}

Send an object in the record payload, not an encoded JSON string. This field type is distinct from choosing the whole-table jsonb storage mode. Portable Database queries treat the object as one field. Promote values that need independent filtering or sorting into typed flat-table fields.

Arrays

Use array with arrayItem when every element has a known type:

{
  "name": "priority_ids",
  "type": "array",
  "arrayItem": {
    "type": "integer"
  }
}

Database maps these arrayItem.type values:

`arrayItem.type`	PostgreSQL element type
`string`	Text
`integer`	Big integer
`number`	Numeric
`boolean`	Boolean
`date`	Date
`datetime`	Timestamp

If arrayItem is omitted or its type is not recognized, TaruviBase uses text elements. Send the field value as a JSON array in record payloads.

PostgreSQL ranges

Ranges use type: "any" with x-pg-type:

{
  "name": "active_window",
  "type": "any",
  "x-pg-type": "tstzrange"
}

Database accepts these PostgreSQL range declarations:

Range	`x-pg-type`
Numeric	`numrange`
Integer	`int4range`
Big integer	`int8range`
Date	`daterange`
Timestamp	`tsrange`
Timestamp with time zone	`tstzrange`

Use the range declarations above when an application needs PostgreSQL range operators. Keep the field's value shape consistent across every interface that reads or writes it.

Constraints

Constraints belong inside a field's constraints object:

{
  "name": "status",
  "type": "string",
  "constraints": {
    "required": true,
    "enum": ["open", "active", "done"]
  }
}

Constraint	Applies to	Effect
`required`	all field types	Stores a non-null value
`unique`	database-compatible field types	Prevents duplicate values
`minLength`	string	Sets the minimum text length
`maxLength`	string	Sets the maximum text length
`minimum`	integer, number	Sets the minimum numeric value
`maximum`	integer, number	Sets the maximum numeric value
`enum`	string	Limits values to the declared list

Constraint failures from the Database API use the structured error shape described in Requests, responses, and errors.

Primary keys

Declare the key field in fields, then list it in primaryKey:

{
  "fields": [
    {
      "name": "id",
      "type": "string",
      "format": "uuid",
      "constraints": {
        "required": true
      }
    }
  ],
  "primaryKey": ["id"]
}

Use one UUID key named id for new application tables. TaruviBase generates its value when a create omits it; an SDK or REST client can supply a UUID when the application owns record identity.

A schema descriptor can represent more than one primary-key field, and TaruviBase uses that shape for some junction tables. The public record routes address a record through one path value, so this guide does not define composite-key CRUD behavior.

Foreign keys

Each foreign key names the local field and the referenced table and field:

{
  "fields": [
    {
      "name": "project_id",
      "type": "string",
      "format": "uuid",
      "constraints": {
        "required": true
      }
    }
  ],
  "foreignKeys": [
    {
      "fields": ["project_id"],
      "reference": {
        "resource": "projects",
        "fields": ["id"]
      },
      "x-actions": {
        "onDelete": "NO ACTION"
      }
    }
  ]
}

reference.resource uses the logical table name. Local and referenced field types must be compatible.

`onDelete` value	Result when the referenced record is deleted
`NO ACTION`	Keep the reference protected
`RESTRICT`	Reject the delete while dependent values exist
`CASCADE`	Delete dependent values
`SET NULL`	Clear nullable local fields

TaruviBase defaults to NO ACTION. SET NULL requires nullable local fields.

Test CASCADE with representative data before using it in an important environment.

Schema design conventions

Use these conventions for schemas that must work consistently across the SDK, REST, Console, and Refine paths:

Requirement	Pattern
Default values	Generate the value in application code and include it in the record payload
Foreign-key deletion	Choose `NO ACTION`, `RESTRICT`, `CASCADE`, or `SET NULL`
Pattern validation	Validate the pattern in application code before sending the record
Custom indexing	Keep custom index definitions out of application-supplied descriptors; see Plan indexes
Record identity	Use the generated UUID `id`; supply a UUID only when the application owns the identity
Schema changes	Follow Plan schema changes

Storage and schema compatibility​

Descriptor​

Fields​

Field types​

Text​

UUID​

JSON objects​

Arrays​

PostgreSQL ranges​

Constraints​

Primary keys​

Foreign keys​

Schema design conventions​

Continue​