What you will take away from this lesson
A schema holds a system’s rules. Automations are the system’s behavior: what happens when a customer submits a checkout, when a storefront asks for the products, when an order lands, when the night comes. In an application each behavior is one automation, written by the agent against the schema of lesson 902 and tested against a real copy of it.
This lesson writes the shop’s four automations. They are short. The reason they can be short is the lesson: the database carries the rules, the platform carries the identity, and each automation is left with only the work that is actually its own.
Learning Objectives
- 01Name the calls an automation uses to read and write an application database, and what each commits.
- 02Read a checkout that places an order, its lines and its stock decrements in one transaction.
- 03Explain why the orders API needs no filter for the caller.
- 04Say why a confirmation is an event handled after the commit, never a call made inside it.
- 05Describe a test that runs on a real copy of the database, and what it proves that a recording cannot.
- 06Place each part of the shop under the trigger that runs it.
Where this lesson sits
Lesson 902 gave the shop its schema. This lesson covers steps two to five of the order of work: automations against the schema, the front, side effects as events, and upkeep on a schedule.
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.
Reading and writing the application database
An automation reaches an application database through a small set of calls. Which one it uses decides what commits together.
How an automation reaches the application database
platform.query
- One statement in a read-only transaction that is always rolled back. A statement that writes fails there, so a read cannot write by mistake.
platform.execute
- One statement, committed in a transaction of its own.
platform.transaction
- Opens one transaction, calls a function with it, and commits when the function returns. When the function fails, everything it did is rolled back. This is how statements commit together.
platform.batch
- Several statements sent in one round trip, committed only when every one succeeds.
platform.query_iter
- A result of any size, read a page at a time, for work that totals a year of orders without holding them all.
platform.copy_in, copy_out
- Bulk rows into a table, and a query’s rows out to a file, streamed without ever becoming the automation’s values.
Every value is sent as a bound parameter and never written into the SQL text. The automation cannot issue BEGIN or COMMIT itself: the platform holds the transaction, so a transaction is always closed when the run ends, however it ends.
The checkout
Asked for a checkout that records an order and takes the stock in one step, the agent wrote a form whose submit handler is one transaction.
The checkout: one transaction
CONNECTION = "acme-shop"
PLACE_ORDER = """
INSERT INTO shop.orders (customer_email)
VALUES (nullif(current_setting('mcp.email', true), ''))
RETURNING id
"""
ADD_LINE = """
INSERT INTO shop.order_lines (order_id, sku, qty, price)
SELECT :order_id, sku, :qty, price FROM shop.products
WHERE sku = :sku AND active
"""
TAKE_STOCK = "UPDATE shop.inventory SET on_hand = on_hand - :qty WHERE sku = :sku"
def place(lines):
"""Places one order and its lines and takes their stock, in one transaction."""
def work(tx):
order_id = tx.query(PLACE_ORDER)["rows"][0]["id"]
for line in lines:
tx.execute(ADD_LINE, params = {"order_id": order_id,
"sku": line["sku"], "qty": line["qty"]})
tx.execute(TAKE_STOCK, params = line)
tx.execute(SET_TOTAL, params = {"id": order_id})
return order_id
return platform.transaction(CONNECTION, work, on_error = "return")
def form_submit(req, values):
"""Places the order; refuses the form when the stock is short."""
got = place(lines_of(values))
if type(got) == "dict" and got.get("error"):
if "check_violation" in str(got["error"]):
return {"errors": {"qty_1": "There is not enough stock for this order."}}
fail("the order was not placed: " + str(got["error"]))
return {"confirmation": "Thank you. Order " + str(got) + " is placed."}The checkout is a form, as in lesson 703: form_spec returns the fields, with the product choices read from the catalog each time the form opens, and form_submit places the order. The field list, the helper that reads the submitted lines, the total and the tests are left out here.
What one transaction buys
The phrase "in one step" in the request is a correctness requirement. A transaction is how it is met.
What the one transaction guarantees
- 1
The order, its lines and the stock commit together
Everything the checkout writes is one transaction. Either the order exists with every line and every decrement, or nothing exists. There is no state in which a customer was promised goods the shelf no longer has.
- 2
Short stock is the database’s refusal
The decrement that would take on_hand below zero violates the check from migration 1. The transaction rolls back, and the form answers the customer with a message on the quantity field. Two customers racing for the last unit cannot both win: the database serializes them.
- 3
The customer is who the platform says
The order’s customer_email is written from mcp.email in SQL, never from a submitted field. A submission cannot place an order as someone else.
- 4
The price is the shop’s
Each line reads its price from products inside the same transaction. A submission cannot carry its own price.
- 5
The transaction never outlives the request
A transaction is held by the run, never by the agent and never across calls. It is rolled back when the run ends, fails or times out.
Most of this is not code in the checkout. The stock rule was written once in lesson 902, the caller’s identity comes from the platform, and the platform closes the transaction. This is what building on a schema means: the automation states the intent and the database refuses the exceptions.
The API
One automation answers the shop’s JSON API: the products, and the caller’s own orders.
shop-api: products for the storefront, orders for the caller
def my_orders():
"""The caller's orders, newest first: the policy hides everyone else's."""
rows = platform.query("""
SELECT o.id, o.total, o.placed_at,
json_agg(json_build_object('sku', l.sku, 'qty', l.qty)
ORDER BY l.sku) AS lines
FROM shop.orders o JOIN shop.order_lines l ON l.order_id = o.id
GROUP BY o.id ORDER BY o.placed_at DESC
""", connection = CONNECTION)
return {"json": {"orders": rows["rows"]}}
def handle(req):
"""Routes /products and /orders."""
if req["params"]["resource"] == "products":
return products()
if req["params"]["resource"] == "orders":
return my_orders()
return {"status": 404, "json": {"error": "no such resource"}}The orders query has no WHERE clause for the caller. It asks for every order, and the row-level security policy returns only the caller’s. An agent that later adds a third resource to this handler cannot leak another customer’s orders by forgetting a filter, because there was never a filter to forget.
Putting both at addresses
The API and the checkout are bound the way every endpoint and form is bound. Inside an application, the bindings travel with their automations.
Binding the API and the checkout
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_script
{
"command": "endpoint_set",
"name": "shop-api",
"endpoint": "shop",
"methods": ["GET"],
"path": "/{resource}",
"auth_mode": "authenticated",
"callers": ["shop-customer"]
}
← {
"endpoint": "shop",
"enabled": true,
"url": "https://mcp-demo.plexara.io/endpoints/shop",
"message": "shop-api now answers GET at
https://mcp-demo.plexara.io/endpoints/shop/{resource}."
}
→ manage_script
{
"command": "endpoint_set",
"name": "shop-checkout",
"endpoint": "checkout",
"methods": ["GET", "POST"],
"auth_mode": "authenticated",
"callers": ["shop-customer"]
}
← { "endpoint": "checkout", "enabled": true,
"url": "https://mcp-demo.plexara.io/endpoints/checkout" }Both bindings require sign-in and admit one persona, so mcp.email is the signed-in customer on every request. Binding is an administrator’s action, as lesson 701 established. Before the checkout is bound, the agent previews the form the way a customer will see it, so the person who asked for the shop approves a page, not a field list.
An event for every order
The confirmation is the shop’s first side effect. The schema is told to write an event for each order.
Migration 4: every new order becomes an event
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": 4,
"template": "outbox",
"capture": ["orders"]
}
← { "version": 4, "name": "outbox", "transactional": true,
"findings": [] }The template writes the migration: an outbox table in the shop’s schema and a trigger that copies each inserted, updated or deleted order into it. An administrator then adds a webhook source that reads that table, and each row becomes one event. The row is written inside the order’s own transaction, so an order that did not commit produces no event, and one that did always does.
The confirmation
A third automation handles each of those events.
shop-confirmation: one run per order
CHANNEL = "order-confirmations"
def confirm(event):
"""Sends the confirmation of the order an event carries."""
if event["payload"]["op"] != "INSERT":
return False
row = event["payload"]["row"]
platform.notify(
CHANNEL,
"Order " + str(row["id"]) + " is placed",
body = "Thank you for your order. Its total is " + str(row["total"]) + ".",
data = {"order_id": row["id"], "customer_email": row["customer_email"],
"total": row["total"]},
)
return True
def main():
"""Confirms the order this run's event carries."""
platform.result({"sent": confirm(run.event)})The automation names the outbox source as its event source, so it runs once for each order, seconds after the order is stored, exactly as lesson 706 ran an automation once per submission. It posts to a notification channel an administrator set up. A run that fails alerts its owner.
Why not send it from the checkout
The shorter program would send the confirmation from inside the checkout. It would also be wrong in three ways.
Why the confirmation is an event
- 1
A slow mail system delays the mail, never the checkout
The customer’s order commits in the time the database takes. The confirmation is a separate run that happens after. Nothing outside the database can make a checkout slow or make it fail.
- 2
An order cannot be confirmed without existing
The event is a row written in the order’s transaction. A rolled-back order leaves no row, so no confirmation is ever sent for an order that was refused.
- 3
Each side effect is its own automation
A second reaction to an order, such as reserving a delivery slot, is a second automation on the same source. The checkout is not edited and not re-released for it to exist.
The rule the agent follows is one sentence: a side effect is never a call made inside the transaction. It is what keeps a system correct when the things around it are not.
Testing against the real rules
Each automation is saved with tests, as in the 600 series. In an application the tests can run on the database itself.
A test that runs on a real copy of the database
def test_the_last_unit_sells_once():
"""Two orders for the last mug: one is placed, one is refused."""
testing.database(CONNECTION, schemas = ["shop"])
platform.execute(
"INSERT INTO shop.products (sku, name, price) VALUES ('mug', 'Mug', 12.50)",
connection = CONNECTION)
platform.execute(
"INSERT INTO shop.inventory (sku, on_hand) VALUES ('mug', 1)",
connection = CONNECTION)
first = place([{"sku": "mug", "qty": 1}])
second = place([{"sku": "mug", "qty": 1}])
assert.true(type(first) == "int")
assert.contains(str(second["error"]), "check_violation")
left = platform.query("SELECT on_hand FROM shop.inventory WHERE sku = 'mug'",
connection = CONNECTION)["rows"][0]["on_hand"]
assert.eq(left, 0)testing.database builds a database from the shop’s migrations, pending ones included, and every statement the test makes runs on it. The platform clones each test’s database from a template it builds once, so a test starts in well under a second, and the copy is dropped when the test ends. Nothing reaches the shop’s real rows.
A recording can prove an automation sends the statements it meant to. Only a real database proves the constraint fires, the policy filters and the second buyer is refused. A save runs these tests and is refused while one fails, so an automation that breaks the shop’s rules cannot be saved, let alone released.
Four triggers, one system
With a nightly maintenance automation added, the shop uses every way an automation can run.
The shop’s automations by what runs them
Once per request
shop-api, shop-checkout
- The API answers the storefront and the signed-in customer. The checkout form answers each submission. Both run when someone calls their address.
Once per event
shop-confirmation
- Runs once for each order the outbox source stores, seconds after it arrives.
On a schedule
shop-maintenance
- Runs nightly: it analyzes the database and vacuums the tables that churn, so the shop stays fast without anyone remembering to tend it.
On demand
any of them
- The owner or an administrator can run an automation by hand from its page or through the agent, for a one-off stock load or a rerun.
Four triggers, four kinds of work, one application. Whichever way a run starts, inside a released application it executes the version the release pinned. That is the subject of lesson 904.
The release
Key terms
Four terms cover how an application does its work.
Key Terms
- Transaction
- A set of statements that commit together or not at all. On Plexara an automation opens one with platform.transaction, and the platform closes it when the run ends.
- Outbox
- A table the application writes a row to for each change it wants handled as an event, inside the same transaction as the change. A webhook source turns each row into one event.
- Event source
- The webhook source an automation names so that it runs once for each event the source stores.
- Test database
- A throwaway copy of the application database, built from its migrations, that a test’s statements run against. It exercises constraints, policies and concurrency for real.
