Schema & Data Engine
The backbone of TotalApp's no-code side — it gives users a way to describe the data behind a screen or feature the platform doesn't ship out of the box, and guarantees every record written to it is deterministic and trustworthy. No SQL, no migrations, no code.
What Is the Schema & Data Engine?
The Schema & Data Engine is the backbone of the no-code side of TotalApp. It is the structure that makes it possible to offer users screens and features the platform doesn't ship out of the box — instead of waiting for a developer to hand-build a new module, the engine gives the user a way to describe the data that module needs, and it takes care of the rest.
Its core purpose starts from a simple observation: whenever a user needs a new screen or a new feature, what they actually need underneath it is data — a place to reliably store the records that screen will read and write. And that data can't just be "somewhere" — it has to be deterministic and trustworthy, or every screen and every automation built on top of it inherits the same unreliability. A table with no enforced shape, no required fields, and no validation eventually fills up with malformed, inconsistent rows that no report, dashboard, or AI agent can be trusted to reason about correctly.
This is exactly the gap the Schema & Data Engine closes. It lets a user design their own data table — called a collection — the same way they would sketch out columns in a spreadsheet: give the table a name, add fields, choose each field's type (text, number, yes/no, or a fixed choice list), and mark which fields are required. Once that definition is saved, every record anyone tries to add to that collection is automatically checked against those rules before it is ever persisted.
In short: define "Order ID: text, required", "Total Price: number, required", "Status: must be one of pending / approved / cancelled" once — and from then on, every row submitted to that table is validated against those exact rules server-side. A row that breaks a rule is rejected outright.
In one sentence
Give the Schema & Data Engine a set of field definitions once — it turns that into a real, validated storage schema and rejects any record that doesn't conform, with a clear, field-level error message explaining exactly what was wrong.
How It Works — Define Once, Validate Every Time
Every collection goes through the same two-stage lifecycle:
| Stage | What happens |
|---|---|
| 1. Design the Collection | The user names the collection and adds one field at a time: a field name, a type (text, number, yes/no, or a choice list with a fixed set of allowed values), and whether the field is required. This definition is persisted once and becomes the schema every future record is checked against. |
| 2. Insert & Validate Records | Every time a record is submitted to that collection, the engine looks up its field definitions and runs each incoming value through three checks — required, type, and choice-list membership — before the record is ever written to storage. A single failed check rejects the whole record with a specific, human-readable error. |
Why validate on every insert, not just on save?
Because collections in TotalApp aren't only filled in by a person typing into a form — they are also written to by workflow nodes, AI agents, and inbound webhooks. Validating once at design time would do nothing to stop a malformed automated write later. Re-checking every single insert against the schema is what makes the collection a reliable guardrail regardless of what's writing to it.
Input — What a Collection Designer Configures
Every collection is described by a name and a list of fields. Each field carries exactly four pieces of information:
Field Name
The column's name, exactly as it will appear on every record — e.g. order_id, total_price, status.
Field Type
One of four types: text (string), number, yes/no (boolean), or choice list (enum) — a fixed set of allowed string values.
Required Flag
Whether the field must be present and non-empty on every record. A required field that is missing, null, or an empty string is rejected immediately.
Choices (choice-list fields only)
The exact set of values a choice list field is allowed to take — e.g. pending, approved, cancelled. Any value outside this list is rejected.
Output — What You Get Back
A successful insert returns the fully-formed record; a failed one returns a specific, field-level error:
| Result | Meaning |
|---|---|
| Success (200) | The record is returned with a generated id, its collectionId, the submitted data, and createdAt/updatedAt timestamps. |
| Missing required field (400) | Missing required field: "order_id" — names the exact field that was left empty. |
| Wrong type (400) | Field "total_price" must be a number — names the field and the type it expected. |
| Invalid choice value (400) | Field "status" must be one of: pending, approved, cancelled — lists every value that would have been accepted. |
| Unknown collection (404) | The referenced collection does not exist for the caller's tenant — either it was never created, or it belongs to a different tenant. |
Rejected, never silently coerced
The engine never attempts to "fix" bad input on the caller's behalf — it never coerces a string into a number or silently drops an invalid field. A record that fails validation is rejected outright with a precise error, so the caller (a person, a workflow node, or an AI agent) always knows exactly what to correct.
Storage — Tenant-Isolated, Dual-Backend
Every collection and every record is scoped to the authenticated caller's tenant, resolved from the request's JWT — never from a client-supplied header. One tenant can never see, list, or write to another tenant's collections, and a request for a collection ID that belongs to a different tenant is rejected with a 404 rather than leaking whether it exists.
The engine supports two interchangeable storage backends for where collections and records are actually persisted:
Tenant JSON Files
The lightweight default — each tenant gets its own schema-data-collections.json (field definitions) and schema-data-records.json (record rows) on disk, following the same server-backed JSON persistence pattern used across TotalApp. Zero setup, ideal for small-to-medium record volumes and fast prototyping.
PostgreSQL (JSONB)
For tenants with larger or higher-throughput data needs, the same collection/record model maps onto a hybrid PostgreSQL schema: a system_collections table stores each tenant's field definitions as JSONB, while a tenant_records table stores the actual row data as JSONB with a GIN index for fast querying and a composite index on (tenant_id, collection_id). Adding a field to a collection is a metadata update, never a costly ALTER TABLE migration.
Same validation, either backend
Whichever backend a tenant is on, the validation rules, the API shape, and the error messages are identical — the storage layer is an implementation detail behind the same insertSchemaRecord / fetchSchemaRecords contract. A collection designed once behaves the same way whether its rows land in a JSON file or a Postgres table.
How End Users Actually Use It
Design Your Own Table
From the Custom Collections screen, a user:
- Names a new collection — anything freeform, like "Inventory Count", "Event Attendees", or "Freelancer Payments".
- Adds fields one at a time: a name, a type (text / number / yes-no / choice list), and whether it's required.
- Saves the design — the collection definition is persisted immediately.
- A data-entry form is generated automatically from the field definitions: a choice-list field becomes a dropdown, a yes/no field becomes a toggle, a number field becomes a numeric input.
- Submitting the form inserts a validated record; the server enforces every rule before it's stored.
- All records for that collection are then listed in a live, filterable grid.
A Clean-Data Gate for AI Agents and Integrations
When a workflow node — an inbound webhook from Shopify, or a structured output produced by an AI agent — tries to write into a collection, the rules the user defined act as a firewall. Unvalidated output from an AI model is never written straight to permanent storage; it always passes through this engine first.
Example: a workflow node performs "AI-assisted data cleanup" and POSTs its result into a collection. Any output that violates the user-defined rules — wrong type, a missing required field, an invalid choice value — is rejected automatically. This gives a structural defense against LLM hallucination, independent of how well the upstream prompt was written.
Fast Prototyping and Mini-CRM Needs
When a user's need — simple inventory tracking, a lightweight CRM, event management — doesn't match an existing TotalApp module, this engine lets them design their own mini data store without writing a line of code, the same way Airtable or Notion's database feature works, but running on TotalApp's own tenant-isolated infrastructure.
Where the Schema & Data Engine Fits
This engine is the data layer other engines write validated records through, and the backing store behind the Database Tools screens:
| Consumer | How it uses the Schema & Data Engine |
|---|---|
| Matrix Agent | A scoring result (pass/fail gate, total score, category breakdown) can be persisted as a validated row in a user-designed "Evaluation Results" collection, making every score filterable and reportable independently of the screen that produced it. |
| Audit Agent | Each PPTX/PDF audit finding (slide number, rule, severity, evidence) can be written into a user-designed "Audit Findings" collection instead of staying siloed inside the audit's own report file. |
| Database Tools | The ORM Mapper, Schema Visualizer, Visual Query Builder, and Seed Data Generator all read and write through this same collection/record model — a collection designed here is the "entity" those tools inspect, diagram, query, and seed. |
| Workflow / Integration nodes | Inbound webhook payloads and AI agent outputs write into collections through the same validated insert path as a human filling in a form. |