Schema Reference

This is the field-by-field reference for schema.json — for app developers defining their app's database tables. If you're new to revenexx Apps, this is probably where you feel the platform's value most directly: instead of learning a migration tool, defining tables in code, scoping data to the tenant by hand, and writing CRUD endpoints, you write one JSON file and deploy.

When you ship a schema.json, the platform does four things automatically:

• Creates or updates the tables.
• Adds the tenant_id column to every table — invisible to you, protected from accidental modification.
• Scopes every query to the current tenant, so your data is automatically scoped to your tenant.
• Generates a REST and GraphQL API from the tables. You don't write endpoint code.

The generated endpoints support filtering, sorting, pagination, and joins; complex business logic you still write in your app's functions.

Migration history lives in git. No embedded version numbers, no up()/down() migration files. The diff between your current schema.json and the deployed version is the migration; the platform computes it at deploy time.

Complete Example

{
  "$schema": "https://revenexx.com/schemas/schema.schema.json",
  "tables": {
    "orders": {
      "columns": {
        "id": {
          "type": "uuid",
          "primary": true,
          "default": "gen_random_uuid()"
        },
        "customer_id": {
          "type": "uuid",
          "required": true,
          "references": { "table": "customers", "column": "id" }
        },
        "status": {
          "type": "string",
          "enum": ["draft", "confirmed", "shipped", "cancelled"],
          "default": "draft"
        },
        "total_amount": {
          "type": "decimal",
          "precision": 10,
          "scale": 2
        },
        "po_number": {
          "type": "string",
          "required": false
        },
        "created_at": {
          "type": "timestamp",
          "default": "now()"
        },
        "updated_at": {
          "type": "timestamp",
          "default": "now()"
        }
      },
      "indexes": [
        { "columns": ["customer_id"], "unique": false },
        { "columns": ["status"], "unique": false },
        { "columns": ["created_at"], "unique": false }
      ]
    },
    "order_items": {
      "columns": {
        "id": { "type": "uuid", "primary": true },
        "order_id": {
          "type": "uuid",
          "required": true,
          "references": { "table": "orders", "column": "id" }
        },
        "product_id": { "type": "uuid", "required": true },
        "quantity": { "type": "integer", "required": true },
        "unit_price": { "type": "decimal", "precision": 10, "scale": 2 },
        "created_at": { "type": "timestamp", "default": "now()" }
      },
      "indexes": [
        { "columns": ["order_id"], "unique": false }
      ]
    }
  }
}

Column Types

Column Properties

Foreign Keys (references)

"customer_id": {
  "type": "uuid",
  "required": true,
  "references": {
    "table": "customers",
    "column": "id"
  }
}

From that one references block the platform creates the foreign-key constraint, enables cascading deletes if you've asked for them, and enforces referential integrity at the database level — so an order_item can never point at an order that doesn't exist.

The tenant_id Column

This is the single most important thing the schema does not contain. You never define tenant_id, and yet every one of your tables has it — the platform adds the column, indexes it, and scopes every query on the table to the current tenant automatically:

-- Your schema defines:
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  customer_id UUID,
  status TEXT,
  ...
)

-- The platform adds a tenant_id column and scopes every query to the tenant.

Handing this to the platform rather than the developer is a deliberate safety decision. Multi-tenant data leaks almost always trace back to a single query that forgot its tenant filter — and you can't forget a filter you never write. Because the scoping is enforced by the platform itself, every query is scoped to the current tenant whether it comes from your app, the generated API, or a hand-written query, and there's no application-level mistake that can bypass it.

Indexes

Indexes are how you keep queries fast as a table grows, declared alongside the columns they cover. A single-column index speeds lookups on that column; a multi-column index covers queries that filter or sort on those columns together; and unique: true doubles as a constraint that forbids duplicate values:

"indexes": [
  { "columns": ["customer_id"], "unique": false },
  { "columns": ["status", "created_at"], "unique": false },
  { "columns": ["po_number"], "unique": true }
]

The columns worth indexing are the ones the database has to search or order by: foreign keys, so joins don't scan the whole table; columns you frequently filter on, like a WHERE status = ...; columns you sort by, like ORDER BY created_at; and anything that must stay unique. But indexes aren't free — each one adds work on every insert and update — so resist the urge to index everything and let real query patterns, not guesses, tell you what to add.

Enums

An enum constrains a string column to a fixed set of allowed values, which is the cleanest way to model something like an order's status:

"status": {
  "type": "string",
  "enum": ["draft", "confirmed", "shipped", "cancelled"],
  "default": "draft"
}

The platform validates every insert and update against that list and can enforce it at the database level with a PostgreSQL CHECK constraint. The payoff is that an impossible state — a typo'd "shiped", a status your code never anticipated — can't reach the table in the first place, rather than surfacing as a confusing bug somewhere downstream.

Decimal vs Float

For financial data (prices, amounts), always use decimal:

"total_amount": {
  "type": "decimal",
  "precision": 10,
  "scale": 2  // Two decimal places (cents)
}

Floats have precision issues. 1.23 + 4.56 might equal 5.7899999999.

Timestamps with Defaults

Always include created_at and updated_at:

"created_at": {
  "type": "timestamp",
  "default": "now()"
},
"updated_at": {
  "type": "timestamp",
  "default": "now()"
}

Your app can update updated_at on record changes, or the platform can do it automatically.

Migrations

Changing a schema after it's live is just editing schema.json and deploying again — the platform diffs the new version against the deployed one and applies the difference. But not all changes are equally safe. Adding things is harmless; renaming and removing things can strand existing data, so they need a little more care. The next three sections walk through each case.

Add a Column

Adding a column is the safe, everyday case. You add it to schema.json and mark it required: false so existing rows, which have no value for it, remain valid:

"orders": {
  "columns": {
    // ... existing columns ...
    "expected_delivery": {
      "type": "date",
      "required": false  // ← Non-required for existing rows
    }
  }
}

When you deploy, the platform:

1. Detects the new column
2. Generates ALTER TABLE orders ADD COLUMN expected_delivery DATE
3. Applies the migration
4. Discovers the new column and exposes it on the REST/GraphQL endpoints

Rename a Column

There's no in-place rename, because to the migrator a rename is indistinguishable from "drop one column, add another" — and that would throw away the data. So you rename in three deliberate steps, spread across more than one deploy: add the new column alongside the old one, copy the data across in your app logic, and only once the data is safely moved do you remove the old column in a later schema update.

// Old version:
"po_number": { "type": "string" }

// New version:
"po_number": { "type": "string" },
"purchase_order_number": { "type": "string" }  // New name

// After data migration:
// Remove "po_number" from schema

Delete a Column

Deleting is the simplest edit and the most final one: remove the column from schema.json and the platform drops it on the next deploy, taking its data with it.

// Before:
{
  "id": { ... },
  "deprecated_field": { ... }  // ← Remove this line
}

// After:
{
  "id": { ... }
}

Because the data is gone for good, treat a drop as a one-way door — make sure nothing still reads the column, and consider keeping it through one release as unused before removing it.

API Generation

Once your schema is deployed, the platform automatically generates:

REST Endpoints

# Read
GET /api/orders?status=eq.confirmed

# Create
POST /api/orders
{ "customer_id": "...", "status": "draft" }

# Update
PATCH /api/orders?id=eq.123
{ "status": "shipped" }

# Delete
DELETE /api/orders?id=eq.123

GraphQL Queries

query {
  orders(where: { status: { eq: "confirmed" } }) {
    id
    customer_id
    status
    order_items {
      product_id
      quantity
    }
  }
}

All endpoints are automatically scoped to your tenant — no app-level filtering needed.

Best Practices

Most of what makes a schema age well comes down to a few choices made at the start.

Pick durable types for the things that matter. Use UUIDs for primary keys rather than sequential integers — they're immutable and safe to generate anywhere, where auto-incrementing integers collide across distributed systems. Use decimal for anything financial; floats will quietly turn 1.23 + 4.56 into 5.7899999999, which is the last thing you want on an invoice. And give every table created_at and updated_at timestamps from day one, because retrofitting them later is far more painful.

Index with intent and reference responsibly. Always index your foreign keys and the columns you filter on most, so joins and lookups stay fast as data grows. When you reference another table, keep to tables in your own app or in well-known platform apps — pointing at an uncommitted or private app couples you to something that may not exist for every tenant.

Treat schema changes as migrations, not edits. New columns should be required: false so existing rows stay valid, and any removal or rename should follow the staged process above rather than a quick delete. The git diff is your migration history, so a careless edit is a careless migration.

Next Steps

• Concept — Understand the declarative app model
• Manifest Reference — App metadata and permissions
• Cockpit Integration — UI extension points