PostgreSQL First

TotalBase is built on one rule: PostgreSQL is the authority. If PostgreSQL can express a rule, the rule lives in PostgreSQL. TotalBase exposes it, secures it and reports on it, but never makes its own copy.

What belongs where

If a rule must hold no matter how the data is written (through the API, from the SQL Console, or by a script with database credentials), it belongs in PostgreSQL.

In practice: Acme's work orders

Acme's rules about work orders live in the database:

-- A technician can't be booked for two visits at the same time.
ALTER TABLE visits ADD CONSTRAINT visits_no_double_booking
  EXCLUDE USING gist (technician WITH =, tstzrange(scheduled_start, scheduled_end) WITH &&);

-- Stock can't go negative.
ALTER TABLE stock ADD CONSTRAINT stock_quantity_check CHECK (quantity >= 0);

A trigger calculates the deadline from the customer's contract, and another trigger refuses invalid status changes with a readable message:

RAISE EXCEPTION 'A cancelled work order can''t be completed';

None of this needs anything from TotalBase. When a dispatcher books an overlapping visit or a technician uses more parts than the van holds, PostgreSQL rejects the write and the API passes its decision to the caller:

{ "errors": [{ "source": "POSTGRESQL", "code": "CHECK_VIOLATION",
               "message": "Not enough stock in this warehouse.", "field": "quantity" }] }

The message comes from a COMMENT ON CONSTRAINT. Without a comment, the message is PostgreSQL's own. A RAISE EXCEPTION message is passed through unchanged.

How errors map

TotalBase maps each SQLSTATE to a stable error code and HTTP status. Some examples:

source tells you who refused: POSTGRESQL for the database, TOTALBASE for TotalBase's own checks (permissions, query syntax, limits). The full list is in Errors.

What TotalBase doesn't do

The same rule means TotalBase deliberately leaves some things out:

• No pre-checks. TotalBase never runs a query first to see whether a value is unique or a referenced row exists. Two concurrent requests would both pass such a check. PostgreSQL resolves the conflict atomically.
• No second schema. Columns, types, keys and relations are read from PostgreSQL's catalog every time. TotalBase stores only what PostgreSQL can't hold: exposure, permissions and presentation.
• No virtual relations. A relation is a foreign key. site.customer.name works because those foreign keys exist.
• No computed fields or saved queries. Use a generated column, a view or a function. Each one becomes a resource the API can expose.
• No superuser. TotalBase can do what its database role can do, and nothing more. If an operation needs more, it fails with PostgreSQL's own permission error.

Changes made outside TotalBase

You can keep using psql, migration tools or anything else that talks to PostgreSQL. TotalBase watches the catalog: within seconds of a change, a new table appears as a hidden resource, a renamed column keeps its presentation, and a dropped table disappears from the API.

Next steps

• Resources: how tables, views and functions become part of the API.
• Tables: creating tables, keys and constraints from the admin.