Skip to main content
Architecture12 min read

902 - A database of its own

An application database is a transactional PostgreSQL schema the application owns: constraints the database enforces, row-level security keyed on the caller, and a role only the application’s own automations log in as. Every table is a migration the agent writes, the platform reviews, plans against a rolled-back transaction, and proves from zero on a throwaway database before it saves.

What you will take away from this lesson

A report can be wrong and corrected tomorrow. An order cannot. The moment software takes an order, counts stock or records a payment status, it needs a place where two writes cannot both win, where a rule is enforced on every path, and where a half-finished change leaves nothing behind. That place is a transactional database, and on Plexara every application can have one of its own.

This lesson gives the shop its schema: products, inventory, orders and order lines, a policy that shows each customer only their own orders, and the roles the shop’s automations run as. Every piece of it is a migration the agent writes and the platform proves before it is kept.

Learning Objectives

  1. 01Say why an application needs a transactional database and what the warehouse is for instead.
  2. 02Read a migration and name the seven things an agent can do with one.
  3. 03Describe the three gates a migration passes before it saves: the review, the plan, and the build from zero.
  4. 04Explain how a row-level security policy keyed on the caller keeps each customer to their own orders.
  5. 05State what the application’s run role may do, and why no other automation reaches the schema.

Where this lesson sits

Lesson 901 created the shop’s record and added its schema as a part. This lesson fills the schema. It is the first of the six steps in the order of work, and every later step is written against what it produces.

900 Series: Applications

Open index

The 900 series assembles what the 600 series and the 700 series taught one part at a time: automations, endpoints and forms become one application with a database of its own, released as a whole.

The warehouse answers questions. The application database commits rows.

Plexara has always read your data where it lives. An application needs something the warehouse was never meant to be: a place to write.

Two kinds of store, two jobs

The warehouse

answers questions

Queried through the SQL engine across every connected source. It reads enormous tables fast and joins them freely. It holds no transaction, no row lock and no enforced constraint, because analysis does not need them.

An application database

commits rows

A PostgreSQL schema the application owns. Writes are transactions that commit whole or not at all, constraints are enforced by the database on every write path, and rows are locked while they change. It is the system of record for what the application decides: an order, a reservation, a status.

How they meet

The application database is not a data source. Its tables do not appear in search or the catalog, and no agent tool reads them directly. The application’s own endpoints and exports are how its data reaches anyone, under the access each one was given. Reporting over application data reads what the application exports.

That last row is a design position. An application’s rows are reached only through the application, so the rules the application enforces cannot be walked around by a query from somewhere else.

Every table is a migration

An application’s tables are created and changed through versioned migrations the platform keeps, applies and reports. The agent works with them through one tool.

manage_schema, the one tool for the schema

add

Adds a migration: a version, a name, the SQL that applies it and the SQL that reverts it. Its version must be above every applied version. A migration still pending can be replaced; an applied one never changes.

plan

Runs the pending migrations in a transaction, reports the tables and columns they change and the locks they take, and rolls back. The plan is exactly what an apply would do to the database as it is now.

apply

Applies the pending migrations, each in a transaction of its own that also writes the ledger row. A migration that fails partway leaves no change. Inside an application, a release applies for you.

down

Reverts the latest applied migration with its down SQL.

status

What is applied, what is pending, which application holds the schema, and any drift between the database and the migrations.

history

What happened to each migration: added, edited, applied, reverted, failed, by whom and when.

describe

Reads the schema back from the database itself: tables, columns, constraints, indexes, policies, triggers and functions. This is how the agent reads the contract while it writes automations against it.

There is no other way to change a table. An automation’s statement that tries to create, alter or drop one is refused by the database, so the migrations are a complete account of the schema, in order.

The catalog and its stock

The shop’s first migration creates the products and the stock held for each. The agent adds it and reads the plan.

The first migration: add, then plan

What the agent calls. This is the exchange the agent has with Plexara on your behalf, shown for the technical reader. You ask in plain language; you never type any of it.

→ manage_schema
  {
    "command": "add",
    "connection": "acme-shop",
    "schema": "shop",
    "version": 1,
    "name": "catalog",
    "up": "CREATE TABLE products (
             sku text PRIMARY KEY,
             name text NOT NULL,
             price numeric(10,2) NOT NULL CHECK (price >= 0),
             active boolean NOT NULL DEFAULT true);
           CREATE TABLE inventory (
             sku text PRIMARY KEY REFERENCES products (sku),
             on_hand integer NOT NULL CHECK (on_hand >= 0));",
    "down": "DROP TABLE inventory; DROP TABLE products;"
  }

← { "version": 1, "name": "catalog", "transactional": true,
    "findings": [] }

→ manage_schema
  { "command": "plan", "connection": "acme-shop", "schema": "shop" }

← {
    "migrations": [1],
    "changed": ["shop.products", "shop.inventory"],
    "locks": [
      { "relation": "shop.products", "mode": "AccessExclusiveLock" },
      { "relation": "shop.inventory", "mode": "AccessExclusiveLock" }
    ],
    "unplanned": []
  }

The check on_hand >= 0 is the shop’s oversell protection, and it lives in the database. A decrement that would take stock below zero fails, and the transaction it is part of rolls back. No automation has to remember to check; no future automation can forget.

The plan names the tables the migration creates and the locks it takes. On new tables the locks cost nothing. On a live table the same report is how the agent and the administrator see, before anything is applied, that a change would hold a busy table.

Orders and lines

The second migration is added the same way. Its constraints say what an order is.

Migration 2: orders and their lines

CREATE TABLE orders (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_email  text NOT NULL,
    total           numeric(10,2) NOT NULL DEFAULT 0,
    placed_at       timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_lines (
    order_id    bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    sku         text NOT NULL REFERENCES products (sku),
    qty         integer NOT NULL CHECK (qty > 0),
    price       numeric(10,2) NOT NULL,
    PRIMARY KEY (order_id, sku)
);

CREATE INDEX order_lines_sku ON order_lines (sku);
CREATE INDEX orders_customer ON orders (customer_email, placed_at DESC);

A line cannot exist without its order or name a product that does not exist, and a quantity is always positive. These are four sentences of business policy, stated once, held on every write for as long as the shop runs.

The gates before a migration saves

Adding a migration is not writing a file. The platform reads it, tries it, and refuses it when it would hurt.

What a migration passes before it saves

  1. 1

    The review reads every statement

    Before a migration saves, the platform reads it for what it would do to a live table. An index built without CONCURRENTLY on an existing table, a new NOT NULL column with no default, a type change that rewrites a table, a constraint that validates every row at once: each is a warning that names the statement, the table, the effect and the safe form.

  2. 2

    Destructive changes need a person

    DROP TABLE, DROP COLUMN, TRUNCATE, and a DELETE or UPDATE with no WHERE stop the save. The migration saves only with a plain-language summary of what it removes and the agreement of the person the application is built for, and both are kept in its history.

  3. 3

    Some statements are refused outright

    A migration that would run a program on the database server, install untrusted code, change server settings, or write a password is refused. So is one that creates or grants to a role outside the application’s own, or grants to everyone. An application cannot widen its own reach.

  4. 4

    Security mistakes are named

    Row-level security enabled without being forced, a view that would read a protected table as its owner, a privileged function open to everyone: each is flagged with the statement that fixes it.

  5. 5

    Everything is built from zero

    Every migration of the schema, the new one included, is applied in order on a throwaway database. A set that does not apply cleanly from nothing is refused, naming the migration and the error. The database that results is kept as a template for the automations’ tests.

An agent writes SQL quickly and well. The review exists so that speed never becomes an outage: the dangerous forms are caught at the moment they are written, with the safe form beside them, and the agent rewrites the migration before a person ever sees it.

Each customer’s own orders

The third migration puts the shop’s privacy rule in the database, where every query is held to it.

Migration 3: a customer sees only their own orders

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
CREATE POLICY own_orders ON orders
    USING (customer_email = nullif(current_setting('mcp.email', true), ''))
    WITH CHECK (customer_email = nullif(current_setting('mcp.email', true), ''));

ALTER TABLE order_lines ENABLE ROW LEVEL SECURITY;
ALTER TABLE order_lines FORCE ROW LEVEL SECURITY;
CREATE POLICY own_lines ON order_lines
    USING (EXISTS (SELECT 1 FROM orders o WHERE o.id = order_id));

The policy reads mcp.email, which the platform sets in every transaction from who it authenticated the caller as. On an endpoint that requires sign-in, that is the signed-in customer. On a public endpoint it is empty, and an empty value matches no row.

A line is visible when its order is, because the subquery is itself held to the orders policy. The API that lists a customer’s orders in lesson 903 therefore needs no filter for the caller. The database adds it, on every query, including the ones nobody has written yet.

How the database knows who is asking

A policy can only be as good as what it reads. The platform supplies the caller’s identity itself, in every transaction.

What every transaction is told about its caller

mcp.user_id

The id the platform authenticated an endpoint’s caller as. Outside an endpoint, the author of the automation’s version. Empty on a public or bot-checked endpoint.

mcp.email

The same person’s address. The shop’s policies and its checkout both read this one.

mcp.persona

The persona the statement runs as.

mcp.source

endpoint for a request, script for a run.

Each value is set in every transaction, from bound values, before its first statement, and set empty when absent, so a statement never reads what an earlier caller’s transaction left behind. The caller never supplies these. A submission cannot claim to be someone else, because the claim does not come from the submission.

Who may touch the schema

The last migration of the set defines what the shop’s automations may do. The role they log in as was created when the schema joined the application.

The role the application’s automations run as

Created with the schema

When the schema is added to the application, the platform creates a login role for it on the database. Every statement of the application’s automations logs in as that role: a run, a draft, an endpoint request, a form, an event run alike, whoever wrote the automation.

An ordinary role

The run role cannot bypass a policy, create other roles or alter the database. The policies in migration 3 hold it like anyone else, which is why they can be trusted.

Granted by a migration

What the role may do is the application’s to define, in a migration that creates shop_read and shop_write and grants them table by table. The shop writes products and stock, inserts and updates orders, and never deletes an order, because no grant lets it.

Closed to everything else

An automation of another application, or of none, has no grant on the schema. Its statement is refused by the database, and the error names the application that holds the schema and says to use its endpoints and exports.

The role’s password is generated by the platform, stored sealed, and appears in no tool answer, log, audit row or run record. Nobody on your team handles it, and neither does the agent.

What the database guarantees

Key terms

Five terms cover the application database and how it is built.

Key Terms

Application database
A transactional PostgreSQL schema an application owns: the system of record for the rows the application commits. Reached only through the application’s own automations.
Migration
One versioned change to the schema, with the SQL that applies it and the SQL that reverts it. Reviewed and built from zero before it saves; immutable once applied.
Plan
The pending migrations run in a transaction that is rolled back, reporting what they change and the locks they take.
Caller claims
The settings the platform sets in every transaction naming who it runs for. Row-level security policies read them.
Run role
The database role the platform creates for an application, which every statement of its automations logs in as. It holds only what the application’s migrations grant.