Data: migrations, SQL and undo
Migrations, the SQL guard, snapshots and undo.
Migrations
A migration is a .sql file in migrations/ named NNNN-name.sql: four digits, then lowercase letters, digits and hyphens. {prefix} becomes the site's table prefix. PHP migrations are refused, because PHP would run before the canary where nothing could judge it. Executable comments such as /*! */ are refused for the same reason.
-- migrations/0001-people.sql
CREATE TABLE {prefix}pen_pals_people (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(190) NOT NULL,
status VARCHAR(20) NOT NULL,
created DATETIME NOT NULL,
KEY status_created (status, created)
); - Each migration runs once. A ledger holds one row per app and number, and a migration that already ran cannot be edited.
- A migration may only touch tables the manifest declares. WordPress tables and Respira's own never belong to an app.
- Every statement of every pending file is read before the first one runs.
- During a deploy, while old code still serves, a migration may only create tables, create indexes and add columns. Drop, rename, modify, change, truncate and any change to data are refused, and the refusal quotes the statement.
- Destructive migrations are not supported in a deploy. To drop or rewrite data, use a guarded write (below) with the owner's approval. A test run with
respira app testapplies the additive migrations only, snapshots them first, and lists them in the answer. - If a statement fails, the declared tables are restored from the snapshot taken first, and the result is checked against its hash. MySQL commits DDL at once, so that snapshot is the undo.
The SQL guard
Agents reach the database through two routes, db/query and db/write. Both need the developer:sql scope. The guard reads SQL with a tokenizer, not patterns, because a word inside a string is data and the same word after ALTER TABLE is not.
respira db query "SELECT status, COUNT(*) FROM {prefix}pen_pals_people GROUP BY status"
respira db query --write "UPDATE {prefix}pen_pals_people SET status = 'ended' WHERE id = %d" --args '[42]' Reads
- One statement, a SELECT. INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE, SET, CALL, LOAD, INTO OUTFILE and locking clauses are refused.
- Functions that read files, sleep, take locks or leak an argument in an error message (
LOAD_FILE,SLEEP,EXTRACTVALUEand similar) are refused. - The
mysql,performance_schema,sysandinformation_schemaschemas are refused, and so is any statement that names another database. - A read returns at most 1,000 rows a page, runs for at most 5 seconds and returns at most 65,536 bytes, with an offset to page on.
- You can read WordPress core tables, the app's own tables and tables the owner has allow-listed. Respira's own tables cannot be reached.
user_passanduser_activation_keyread as[redacted]. So do columns and values that look like secrets (token, password, api_key). The redaction happens inside the query, so a WHERE clause cannot be used to guess them.
Writes
- A write is a dry run first. It returns the row count and a receipt bound to the statement, the keys and the count. The receipt works once and lasts 10 minutes.
- Only
UPDATE,DELETEandINSERTrun on the write route.REPLACEis refused, because it deletes and inserts behind your back. - The write then runs only on the keys that were snapshotted. If the rows changed in between, it is refused.
- A snapshot is capped at 200,000 rows or 64 MB. Over the cap, the owner has to confirm a host backup (
--backup-confirmed). - Writes to users, user meta and protected options need the owner's approval. The token lives 30 minutes and works once.
- Options whose name mentions respira are never writable, even with approval.
Column names that MariaDB reserves
The lint and the migration check read your SQL, but neither checks a column name against the reserved words of the database. A migration with a reserved word as a column name is accepted by Respira and fails, or misbehaves, on the host that reserves it. MariaDB added returning as a reserved word in 10.5, and the lint did not catch it in a test app with a column of that name.
Words that look like good column names and are reserved in MariaDB: returning, order, group, key, index, range, read, write, rows, repeat, interval, option, condition, release, check, default and match. The list is not complete, and MariaDB's own list of reserved words is the authority. Pick a plainer name such as repeat_buyer instead of returning, or wrap the name in backticks everywhere you use it. Run the migration on a MariaDB site before you rely on it.
What the guard does not have
- No route gives a table's definition.
information_schemais refused, and a bareDESCRIBEis not a SELECT. A refusal message may point toGET /respira-code/v1/db/schema. That route is not registered in this release, so do not call it. Read your own migration files for the shape of your tables, orSELECTa row. - No route drops a table. A migration may only create tables and indexes and add columns, the write route takes row statements only, and schema changes are migrations. To remove an app's data, delete its rows with a guarded write, with the owner's approval where it needs one.
Snapshots and undo
Every write has a snapshot id in its audit row, or it does not happen. A snapshot covers a whole table (tbl-) or the rows a write touches (rows-). One undo of a session, respira_restore_session, puts back pages, options, tables and release pointers together. A restore by a key that did not make the change needs the owner's approval. Sent mail is the exception: it cannot be unsent, and undo never lets it be sent twice.
Adopting existing tables
An app owns the tables its own migrations created. An older site may already hold the data, for example sign-ups from a code snippet. The owner can give such a table to an app. A manifest cannot do this itself.
- The agent names the tables in
POST /apps/<slug>/adopt. Each must exist, sit under the snapshot cap, and belong to nobody: not WordPress, not Respira, not another app, not a reserved plugin prefix. - The owner sees each table with its row count and the hash of its definition, and approves in wp-admin.
- Both are read again. If either changed, it is refused. Then each table is snapshotted in full and recorded as adopted, with the approver's name.
An adopted table is restored row by row and is never dropped. Undoing the adoption removes the ledger row only; the table and its rows stay. There is no respira CLI command for it. Adoption goes through this route, with the owner's approval.
Personal data in snapshots
Snapshots of a table that holds personal data expire after 30 days, or sooner if the manifest's retention for that table is shorter. A daily purge deletes them and writes an audit row per table first. After that, undo cannot bring those rows back, which is the point.