Appendix: the bare schema
When you tell the world you built an operating system for your AI out of MySQL, PHP and bash, the first honest reply from any technical forum is: show it or it didn't happen. It is a fair reply. This appendix is my answer.
I will not publish my code or my data — this blog runs on "patterns, not blueprints", and what lives inside the loom is my clients' real operations. But the whole pattern fits in four tables, and a schema has no secrets worth protecting. Here it is, sanitised of names but faithful in everything else, in the spirit of the comments it carries in my SQL. Anyone can rebuild it in an afternoon. The schema is the easy part; the hard part comes at the end.
The nodes
Everything that exists is a node of one of five types: job (an assignment with a lifecycle), resource (a system, server or account that gets used), procedure (a runbook with verification), person (who they are, how to reach them, what they operate) and rule (a cross-cutting criterion with its check). One table:
CREATE TABLE node (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(200) NOT NULL DEFAULT '',
type VARCHAR(15) NOT NULL DEFAULT 'job', -- job/resource/procedure/person/rule
state VARCHAR(20) NOT NULL DEFAULT 'pending', -- phase (job) / health (resource)
previous_state VARCHAR(20) NOT NULL DEFAULT '', -- restored on unblock
priority VARCHAR(10) NOT NULL DEFAULT 'normal',
project VARCHAR(40) NOT NULL DEFAULT 'general', -- slug, not a numeric FK
created_at INT UNSIGNED NOT NULL DEFAULT 0,
closed_at INT UNSIGNED NOT NULL DEFAULT 0,
remind_at INT UNSIGNED NOT NULL DEFAULT 0, -- reminders built in
remind_every INT UNSIGNED NOT NULL DEFAULT 0, -- days; 0 = one-shot
modified_at INT UNSIGNED NOT NULL DEFAULT 0, -- epoch: the sync engine
deleted TINYINT(1) NOT NULL DEFAULT 0, -- soft-delete ALWAYS
PRIMARY KEY (id)
) ENGINE=InnoDB;The states are not decoration: a job can only be pending → open → blocked → closed, and a resource declares its health (operational, degraded, down, maintenance, retired). The state machine is what lets you ask "what is alive right now?" without reading anything else.
The entries
Every node carries an append-only log. The past does not get edited; a new entry gets added. And every entry is typed — the types are the reading key: assignment (what was asked), action (what was done), decision (why it was chosen), result (how it ended), access/usage/status (a resource's living card), verification (how to check it).
CREATE TABLE entry (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
node_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
body TEXT,
type VARCHAR(12) NOT NULL DEFAULT 'action',
author VARCHAR(20) NOT NULL DEFAULT 'ai', -- ai / human
created_at INT UNSIGNED NOT NULL DEFAULT 0,
modified_at INT UNSIGNED NOT NULL DEFAULT 0,
deleted TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (id),
KEY idx_node (node_id)
) ENGINE=InnoDB;The house rule: no job closes without a result entry. That single obligation turns a task queue into institutional memory — months later, "how did we solve that?" has a literal answer, dated and signed.
The edges
The graph appears with the third table. Seven relations, each with fixed semantics:
CREATE TABLE edge (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
source_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
target_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
relation VARCHAR(20) NOT NULL DEFAULT '',
modified_at INT UNSIGNED NOT NULL DEFAULT 0,
deleted TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY uq_edge (source_id, relation, target_id),
KEY idx_target (target_id, relation)
) ENGINE=InnoDB;- uses — a job uses a resource ("this assignment touches that server")
- follows — a job follows a procedure or a rule (compliance)
- applies-to — a procedure applies to a resource
- contains — hierarchy: a server contains its services; an organisation, its people
- blocked-by — what is missing: a person, or another job (and when the blocker closes, the blocked job frees itself)
- documents — a job documents the nodes it created or corrected (provenance of knowledge)
- operates — a person operates a machine or an account
The "coherence matrix" that refuses nonsense links is not declarative magic: it is the list of allowed source–relation–target combinations, validated by the CLI before inserting. A WHERE clause with opinions. With this in place, "give me the context of job 147" is a query, not a search: the assignment, its resources with access details, its procedures with steps and the rules that apply — resolved transitively — in a single output.
The vault
Fourth table, the only one with a trick. Secrets are encrypted per value (secretbox: nonce + MAC), and the key is not in the database: it derives from a PIN only the human knows plus a pepper that lives outside the repository and outside the sync. Leak the entire table and the ciphertexts are noise.
CREATE TABLE secret (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
resource_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
name VARCHAR(100) NOT NULL DEFAULT '', -- "SSH root", "such-and-such API"
encrypted_value TEXT NOT NULL, -- base64(nonce ‖ ciphertext+MAC)
modified_at INT UNSIGNED NOT NULL DEFAULT 0,
deleted TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY uq_resource_name (resource_id, name)
) ENGINE=InnoDB;And the honest nuance, already stated in the loom article: this design minimises the exposure of secrets — encrypted at rest, opened by a human PIN, retrieved by name at the moment of use. It does not eliminate it. Anyone selling you total elimination is selling you something.
My favourite detail: two writers, no central server
The loom lives in two places: my development machine (where the AI writes) and production (where I write from the web). No queues, no brokers, no "real time". The whole trick is three decisions:
1. Disjoint id ranges: development starts its AUTO_INCREMENT at 1; production, at 1,000,000,000. Both insert without ever colliding.
2. modified_at on every table and last-write-wins at reconciliation.
3. Physical DELETE is forbidden: deletion is a column, which is why removals travel through the sync like any other change.
What if both writers touch the same node before a sync? The honest answer, in parts. Almost all of the loom's traffic is INSERTs of new entries — an append-only log with disjoint id ranges — and there a collision is impossible by construction. Last-write-wins only bites on the headers (state, priority, title), where the two writers rarely coincide on the same node on the same day; and when it happens, the stomp does not erase history: both writers' entries survive, and the log betrays the discrepancy to anyone who reads it. It is not a consensus protocol; it is a conflict surface deliberately shrunk to the point where the remaining risk is watched by eye.
It is half a CRDT built out of what was already lying around. For two writers who trust each other, it is plenty. With ten distrustful writers you would need vector clocks or a central server — and then it would no longer be this system, nor this article.
A real rule, as a sample
I have been asked for a real example of the motto "a rule without a verification is a wish". Take this one, among the simplest in the house (names abstracted, shape faithful):
node: rule #NNN — "Every deploy ends with a smoke test"
entry[note]: After every production deploy, the critical routes
are checked BEFORE the job may be closed.
entry[verification]: curl -s -o /dev/null -w '%{http_code}' https://DOMAIN/
== 200 (repeat for each critical route of the deploy)
edge: procedure "deploy" --follows--> rule #NNNThe rule is not the text: it is the text plus the command that checks it plus the edge that puts it in front of whoever deploys. When the guard starts a deploy job, that verification shows up in the context package. Running it is not optional: the job does not close without a result entry, and a result without a smoke test shows.
What is NOT here — and it is the hard part
The schema rebuilds in an afternoon. What does not install with a CREATE TABLE:
- The rules with verification. Writing them — and pruning them when they expire — is human work.
- The discipline of the result. Closing without a result entry is forbidden. Every day. Also when in a hurry.
- The hierarchy of truth. The loom outranks the documents; reality outranks the loom; and whoever finds the discrepancy has the obligation to fix it on the spot.
My harshest critic to date — an AI, as it happens — put it better than I can: this discipline "cannot be bought by installing anything". Exactly. That is why I publish the schema without fear: the tables are free. The other thing is what costs.
(As for the plumbing — how the AI talks to all this? No mystery and no resident daemons: it is told in the loom article, in the section "The plumbing, demystified".)
— an old programmer · 64 years old · rss