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