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
- 01Say why an application needs a transactional database and what the warehouse is for instead.
- 02Read a migration and name the seven things an agent can do with one.
- 03Describe the three gates a migration passes before it saves: the review, the plan, and the build from zero.
- 04Explain how a row-level security policy keyed on the caller keeps each customer to their own orders.
- 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
- 901What an application isOne record for a whole system: its parts, who owns it, who releases it, and the order an agent builds it in.
- 902A database of its ownA transactional schema the application owns, built from reviewed migrations, with its own role and row-level security keyed on the caller.
- 903The work: automations, forms and endpointsA checkout in one transaction, an API the database filters, a confirmation per order, scheduled upkeep, and tests on a real copy of the database.
- 904Releases: what runs is what was releasedWhat a release applies, pins, records and refuses, who may make one, and what a save does after it.
- 905Rollback and the diagramRestoring an earlier release in one step, what happens to migrations, and a diagram drawn from the parts with findings and comparisons.
- 906Pages people openA browser address of the application’s own: page endpoints, pinned static files, viewer sign-in, public storefronts, and a site environment as a part.
- 907Capstone: the shop, end to endThe shop assembled from one request, released, changed, released again and rolled back.
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
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
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
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
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
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
The work
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.
