restura.schema.json

restura.schema.json defines your database tables, API endpoints, types, and access control. Restura reads this file and generates everything needed to run your API.

Restura automatically regenerates your database, types, and API whenever the schema changes.

Structure

The schema has six required top-level properties (plus optional extensions):

{
  "customTypes": [...],
  "database": [...],
  "endpoints": [...],
  "globalParams": ["companyId", "userId"],
  "roles": ["admin", "user", "anonymous"],
  "scopes": ["read:user", "write:user"],
  "extensions": ["pg_trgm"]
}

Property	Purpose
`customTypes`	TypeScript interfaces for non-standard response shapes
`database`	Table definitions with columns, keys, and indexes
`endpoints`	API route groups and their configurations
`globalParams`	User context values available in all endpoints
`roles`	User roles for access control
`scopes`	Fine-grained permissions (OAuth-style)
`extensions`	Optional PostgreSQL extensions (e.g. `pg_trgm`). Emitted as `CREATE EXTENSION IF NOT EXISTS` before any table DDL in full-SQL generation, and in diffs when missing from the live database

Custom Types

Custom Types are reusable response shapes for endpoints with code-driven logic. When you write custom handler code that joins tables, transforms data, or returns computed results, define your response shape here instead of relying on Restura’s automatic type generation.

{
  "customTypes": [
    "export interface DashboardStats {\n    totalOrders: number;\n    revenue: number;\n    activeUsers: number;\n    topSellingProductId: number;\n}",
    "export interface OrderSummary {\n    orderId: number;\n    customerName: string;\n    itemCount: number;\n    subtotal: number;\n    tax: number;\n    total: number;\n}"
  ]
}

Reference these in your endpoint configuration using responseType:

{
  "method": "GET",
  "path": "/dashboard/stats",
  "name": "GetDashboardStats",
  "description": "Returns aggregated dashboard statistics",
  "responseType": "DashboardStats",
  "action": "getDashboardStats"
}

When to use Custom Types:

Your endpoint has a custom action handler that manually constructs its response
You’re joining multiple tables in code rather than using Restura’s subquery feature
The response shape doesn’t map directly to any database table
You need computed or aggregated fields that Restura can’t infer

Restura extracts these definitions during generation and creates proper .d.ts files, giving your custom handlers full type safety.

Database

An array of table definitions. Each table includes its columns, foreign keys, indexes, and optional access control. Restura uses these definitions to generate SQL migrations and keep your database synchronized.

Example:

{
  "name": "user",
  "notify": ["id", "firstName", "lastName", "email"],
  "roles": [],
  "scopes": [],
  "columns": [
    {
      "name": "id",
      "type": "BIGSERIAL",
      "isPrimary": true,
      "hasAutoIncrement": true,
      "isNullable": false
    },
    {
      "name": "firstName",
      "type": "VARCHAR",
      "length": 30,
      "isNullable": false
    },
    {
      "name": "email",
      "type": "VARCHAR",
      "length": 100,
      "isNullable": false
    },
    {
      "name": "role",
      "type": "ENUM",
      "value": "'admin','user'",
      "isNullable": false
    },
    {
      "name": "companyId",
      "type": "BIGINT",
      "isNullable": false
    },
    {
      "name": "departmentId",
      "type": "BIGINT",
      "isNullable": false
    }
  ],
  "foreignKeys": [
    {
      "name": "user_companyId_company_id_fk",
      "column": "companyId",
      "refTable": "company",
      "refColumn": "id",
      "onDelete": "NO ACTION",
      "onUpdate": "NO ACTION"
    },
    {
      "name": "user_departmentId_companyId_fk",
      "columns": ["departmentId", "companyId"],
      "refTable": "department",
      "refColumns": ["id", "companyId"],
      "onDelete": "CASCADE",
      "onUpdate": "CASCADE"
    }
  ],
  "indexes": [
    { "name": "PRIMARY", "columns": ["id"], "isPrimaryKey": true, "isUnique": true, "order": "ASC" },
    {
      "name": "user_email_unique_index",
      "columns": ["email"],
      "isUnique": true,
      "isPrimaryKey": false,
      "order": "ASC"
    },
    {
      "name": "user_email_trgm_index",
      "using": "gin",
      "columns": [{ "column": "email", "opclass": "gin_trgm_ops" }],
      "isUnique": false,
      "isPrimaryKey": false,
      "order": "ASC"
    }
  ],
  "checkConstraints": []
}

Index Properties:

Property	Description
`name`	Index name in the database
`columns`	Array of entries: a plain column name string, `{ "column": "name", "opclass": "..." }`, or `{ "expression": "(\"col\"::text)", "opclass": "..." }`
`using`	Access method: `btree` (default), `gin`, `gist`, `brin`, or `hash`
`isUnique`	Enforce uniqueness (btree only)
`isPrimaryKey`	Marks the primary-key index
`order`	`ASC` or `DESC` — applies to btree only; ignored for other access methods
`where`	Optional partial-index predicate

Only specify opclass for non-default operator classes (e.g. gin_trgm_ops) — default opclasses are omitted from diff comparison automatically. Expression entries must match PostgreSQL’s per-column canonical form — what SELECT pg_get_indexdef(indexrelid, column_number, true) prints — or the diff will flag them as changed. Careful: this differs from the full indexdef text; e.g. indexdef shows (("orderNumber")::text) but the per-column form is ("orderNumber"::text) — write the latter. Non-btree methods usually require an extension — declare it in the root extensions array.

Index introspection and diffing require PostgreSQL 11 or later (they read pg_index.indnkeyatts). Invalid indexes left behind by a failed CREATE INDEX CONCURRENTLY are detected and rebuilt by the diff.

Index, foreign-key, and check-constraint names are limited to PostgreSQL’s 63-byte identifier maximum. Restura truncates over-long names consistently in generated SQL and diffs, and schema validation warns with the truncated form — but prefer hand-shortening the name (e.g. abbreviate the longest column part) so the schema matches what actually exists in the database.

Foreign Key Properties:

Property	Description
`name`	Constraint name in the database
`column`	Single referencing column — mutually exclusive with `columns`
`columns`	Array of referencing columns for a composite key — mutually exclusive with `column`
`refTable`	The table being referenced
`refColumn`	Single referenced column — mutually exclusive with `refColumns`
`refColumns`	Array of referenced columns for a composite key — mutually exclusive with `refColumn`
`onDelete`	`NO ACTION`, `RESTRICT`, `CASCADE`, `SET NULL`, or `SET DEFAULT`
`onUpdate`	Same actions as `onDelete`

Use the singular column/refColumn form for ordinary single-column keys, or the plural columns/refColumns form for a composite key. Exactly one form per side, and both sides must list the same number of columns — the pairing is positional, so "columns": ["a", "b"] with "refColumns": ["c", "d"] emits FOREIGN KEY ("a", "b") REFERENCES t ("c", "d"). Reordering either list is a real change and the diff will drop and re-add the constraint.

Composite keys are the standard way to enforce same-tenant integrity when a tenant discriminator is denormalized onto child rows: pairing storeId with the reference column guarantees the child can never point at a parent belonging to another store.

That guarantee depends on every referencing column being "isNullable": false. PostgreSQL’s default MATCH SIMPLE semantics skip the constraint entirely for a row where any referencing column is NULL, so a nullable storeId would let a mismatched pair through unchecked.

PostgreSQL only accepts a foreign key whose referenced columns are covered by a unique constraint or index on exactly that column set — declare one on the referenced table as an indexes entry with "isUnique": true. That index must also be non-partial (no where) and btree (the default using); PostgreSQL rejects partial and non-btree unique indexes as foreign-key targets. Schema validation warns when it can’t find a qualifying one.

name is required on every foreign key and Restura never generates it for composite keys — the {table}_{column}_{refTable}_{refColumn}_fk auto-naming is a convenience of the single-column editor UI. Pick an explicit, stable name, because the diff engine matches constraints by name: renaming one is a drop and re-add.

Composite foreign keys are authored in JSON. The schema editor UI creates and edits single-column foreign keys only; it leaves composite entries in the file untouched.

Table Properties:

Property	Description
`name`	Table name in the database
`columns`	Array of column definitions
`foreignKeys`	Relationships to other tables
`indexes`	Performance and uniqueness indexes
`checkConstraints`	Custom SQL check constraints
`notify`	Columns to include in real-time notifications. Set to `"ALL"` for all columns, or an array of specific column names
`roles` / `scopes`	Table-level access restrictions

Column Properties:

Property	Description
`name`	Column name (required)
`type`	Data type (required)
`length`	Character limit for VARCHAR
`value`	For ENUM: quoted values like `"'a','b'"`. For DECIMAL: precision like `"10,2"`
`default`	Default value expression
`isPrimary`	Part of primary key
`isNullable`	Allows NULL
`hasAutoIncrement`	Auto-incrementing value
`comment`	Documentation note

Supported Types: BIGSERIAL, BIGINT, INTEGER, MEDIUMINT, VARCHAR, TEXT, BOOLEAN, DATETIME, DECIMAL, ENUM, JSON

Endpoints

Endpoints are organized into groups, each with a base URL. This lets you version your API (/api/v1, /api/v2) or organize by domain.

{
  "endpoints": [
    {
      "name": "V1",
      "description": "V1 Endpoints",
      "baseUrl": "/api/v1",
      "routes": [...]
    }
  ]
}

Each route defines an HTTP endpoint. Standard routes (ONE, ARRAY, PAGED) generate SQL queries automatically based on the table, response selectors, and where clauses. Custom routes (CUSTOM_ONE, CUSTOM_ARRAY) require you to write the handler logic yourself.

Route Types:

Type	Returns	Use Case
`ONE`	Single object	Get one record by ID or condition
`ARRAY`	Array of objects	List all matching records
`PAGED`	Paginated response with metadata	Large datasets with page/perPage support
`CUSTOM_ONE`	Single object (you write the handler)	Complex logic, external APIs, auth flows
`CUSTOM_ARRAY`	Array (you write the handler)	Complex aggregations, multi-step operations

Basic Endpoint

This endpoint returns the authenticated user. The #userId in the where clause references a global parameter extracted from the auth token.

{
  "name": "Get My User",
  "description": "Get the authenticated user",
  "method": "GET",
  "path": "/user/me",
  "type": "ONE",
  "table": "user",
  "roles": ["user", "admin"],
  "scopes": [],
  "request": [],
  "response": [
    { "name": "id", "selector": "user.id" },
    { "name": "firstName", "selector": "user.firstName" },
    { "name": "lastName", "selector": "user.lastName" },
    { "name": "email", "selector": "user.email" }
  ],
  "where": [{ "tableName": "user", "columnName": "id", "operator": "=", "value": "#userId" }],
  "joins": [],
  "assignments": []
}

The response array controls which columns are returned and how they’re named. The selector uses table.column format.

Paged Endpoint

Paged endpoints automatically handle pagination, filtering, and sorting. The request params (page, perPage, filter, sortBy, sortOrder) are standard for PAGED type routes.

{
  "name": "Get Users Paged",
  "description": "List users with pagination",
  "method": "GET",
  "path": "/user/paged",
  "type": "PAGED",
  "table": "user",
  "roles": ["admin"],
  "scopes": [],
  "request": [
    { "name": "page", "required": false, "validator": [{ "type": "TYPE_CHECK", "value": "number" }] },
    { "name": "perPage", "required": false, "validator": [{ "type": "TYPE_CHECK", "value": "number" }] },
    { "name": "filter", "required": false, "validator": [{ "type": "TYPE_CHECK", "value": "string" }] },
    { "name": "sortBy", "required": false, "validator": [{ "type": "TYPE_CHECK", "value": "string" }] },
    {
      "name": "sortOrder",
      "required": false,
      "validator": [{ "type": "ONE_OF", "value": ["ASC", "DESC", "NONE", "RAND"] }]
    }
  ],
  "response": [
    { "name": "id", "selector": "user.id" },
    { "name": "firstName", "selector": "user.firstName" },
    { "name": "email", "selector": "user.email" }
  ],
  "where": [],
  "joins": [],
  "assignments": []
}

The response includes pagination metadata alongside the data array.

Endpoint with Joins

Joins let you include data from related tables. Each join needs an alias to reference in the response selectors. The type can be INNER, LEFT, or RIGHT.

{
  "name": "Get Orders with User Info",
  "description": "List orders with user details",
  "method": "GET",
  "path": "/order/all",
  "type": "ARRAY",
  "table": "order",
  "roles": [],
  "scopes": [],
  "joins": [
    {
      "alias": "userId_user",
      "table": "user",
      "localColumnName": "userId",
      "foreignColumnName": "id",
      "type": "INNER"
    }
  ],
  "response": [
    { "name": "id", "selector": "order.id" },
    { "name": "amount", "selector": "order.amountCents" },
    { "name": "userFirstName", "selector": "userId_user.firstName" },
    { "name": "userLastName", "selector": "userId_user.lastName" }
  ],
  "where": [],
  "request": [],
  "assignments": []
}

For chained joins (joining a table through another join), use localTable and localTableAlias to specify which joined table to join from.

Endpoint with Subquery

Subqueries nest related data inside each record. This returns users with their orders as a nested array.

{
  "name": "Users With Orders",
  "description": "Get users with their order history",
  "method": "GET",
  "path": "/user/with-orders",
  "type": "ARRAY",
  "table": "user",
  "roles": [],
  "scopes": [],
  "response": [
    { "name": "id", "selector": "user.id" },
    { "name": "firstName", "selector": "user.firstName" },
    {
      "name": "orders",
      "subquery": {
        "table": "order",
        "joins": [],
        "properties": [
          { "name": "id", "selector": "order.id" },
          { "name": "createdOn", "selector": "order.createdOn" }
        ],
        "where": [{ "tableName": "order", "columnName": "userId", "operator": "=", "value": "\"user\".\"id\"" }]
      }
    }
  ],
  "where": [],
  "request": [],
  "joins": [],
  "assignments": []
}

The subquery’s where clause links it to the parent record. Use quoted identifiers ("table"."column") for raw column references.

Request Validation

Each request parameter can have validators that run before the handler executes.

{
  "name": "email",
  "required": true,
  "validator": [{ "type": "TYPE_CHECK", "value": "string" }]
}

Validator Types:

Type	Value	Purpose
`TYPE_CHECK`	`"string"`, `"number"`, `"boolean"`	Type validation
`ONE_OF`	`["a", "b", "c"]`	Must be one of the listed values

Set required: false for optional parameters. Use isNullable: true if the parameter can be explicitly set to null.

Global Config

globalParams

Values extracted from the authenticated user and available in where clauses. Reference them with #:

"globalParams": ["companyId", "userId"]

"where": [{ "tableName": "user", "columnName": "id", "operator": "=", "value": "#userId" }]

roles

Define all user roles your application uses. Endpoints reference these to restrict access.

"roles": ["admin", "user", "anonymous"]

An endpoint with "roles": ["admin"] only allows admin users. An endpoint with "roles": [] is public.

scopes

OAuth-style permissions for finer control than roles. Useful when the same role might have different permission levels in different contexts.

"scopes": ["read:user", "write:user", "read:company", "write:company"]

Troubleshooting

Type errors

Custom type strings must be valid TypeScript. Check for syntax errors.
responseType must reference a type defined in customTypes.

Migration failures

ENUM values must be single-quoted: "value": "'admin','user'"
Foreign key target tables and columns must exist.
Valid referential actions: NO ACTION, CASCADE, SET NULL, SET DEFAULT

Route conflicts

Paths must be unique per HTTP method within an endpoint group.
Specific routes (/user/me) must come before parameterized routes (/user/:id).

Auth not working

"roles": [] means public—no authentication required.
Any role used in an endpoint must exist in the top-level roles array.
Verify your auth handler sets the user’s role correctly.

Where clause issues

Global params use #: "value": "#userId"
Raw column references use quoted identifiers: "value": "\"user\".\"id\""
String literals need quotes: "value": "'active'"

Joins returning wrong data

Always set a unique alias for each join.
LEFT joins make columns nullable in generated types.
For chained joins, specify localTable and localTableAlias to join from a joined table.

Validation failing unexpectedly

TYPE_CHECK values are lowercase: "string", "number", "boolean"
ONE_OF requires an array, even for one value: ["admin"]
Check required and isNullable settings match your intent.