Data, Keys & Vocabularies

Data, Keys & Vocabularies

Everything you look up rather than read: the three tables and their columns, the two indexes that do the module's hardest work, the eighteen endpoints and what each one demands of you, the seven audit events, and where every file lives.

The three tables

All three live in the tenant database only. There is deliberately no provider-side or main-database routing index: ERPat's Advanced Cron Tick already fans the dispatch job out once per tenant with the connection switched, so the job reads whatever the current tenant's own automations table holds. Engine liveness is answered by the existing /cron/health endpoint and the shared cron_runs table — this module does not build a second one.

They are created by migrations/20260903124527_create_automation_tables.php, which runs against the primary database and every tenant database through php erpat migrate:modules. Every table ends with the standard ERPat audit block (created_by, created_at, updated_at, deleted) and every read filters deleted = 0.

automations — the rule definitions

One row per rule a tenant has authored. This is the current state of the rule; it is edited freely and it is not what a historical run is judged against.

Indexes: uniq_automations_uuid (unique), idx_automations_due on (status, next_run_at_utc, deleted) — the one the dispatcher's due-query rides — idx_automations_type on (type_key, status), and idx_automations_deleted.

automation_runs — one row per occurrence

One row per scheduled, manual or preview occurrence. A run is immutable in intent: it carries its own snapshot of the configuration it was claimed under, so reading history never depends on what the rule looks like today.

Indexes: uniq_automation_runs_idem (unique), uniq_automation_runs_uuid (unique), idx_automation_runs_sched on (automation_id, scheduled_for_utc), idx_automation_runs_status on (status, started_at), idx_automation_runs_corr on (correlation_id), and idx_automation_runs_mode on (automation_id, run_mode, status) — which exists so that "has this automation ever completed a Live run?" is one indexed lookup, not a scan.

automation_run_items — one row per record decided

The per-record trail. Note what it does not contain: a copy of the decided record. It stores a link (system_log_id) to the authoritative business audit row instead, so the record's own history stays owned by the module that owns the data.

Indexes: uniq_automation_items_subject (unique — see below), idx_automation_items_outcome on (run_id, outcome), idx_automation_items_subject on (subject_type, subject_id) — the "what has automation ever done to this record?" index — and idx_automation_items_reason.

The two constraints that do the real work

Most of this module's correctness under concurrency is not in PHP. It is in two UNIQUE indexes, and both are written to as INSERT IGNORE so that the database arbitrates rather than a check-then-write that has a race between the two halves.

// modules/Automations/models/Automation_runs_model.php — create_run()
// INSERT IGNORE: the UNIQUE(idempotency_key) index is the real guard, so this
// stays correct under concurrency where a SELECT-then-INSERT would not.
$this->db->query(
    "INSERT IGNORE INTO `$runs` (" . implode(',', $columns) . ") VALUES (" . implode(',', $values) . ");"
);

$created = $this->db->affected_rows() > 0;

$existing = $this->db->query(
    "SELECT `id` FROM `$runs` WHERE `idempotency_key`=" . $this->db->escape($idempotency_key) . " LIMIT 1;"
)->row();

Sidecar key reference

Every key a module may declare in its config/automations.php sidecar, with the one column that actually decides whether writing it changes anything: who reads it. Filter to Declared but unconsumed to see the keys the contract documents and the code never looks at — that is not an omission in this guide, it is the finding, and it is why publish_permissions currently gates nothing. Filter to Decorative for keys that are read but whose value is overridden on save.

Statuses, outcomes and codes

Four vocabularies are declared in helpers/automations_helper.php and a fifth — run error codes — is emitted by the executor without a declaring function. Use the lookup to find what a code on a screen means, and the Show only drift toggle to see where declaration and reality disagree: codes declared but never emitted by any shipped handler, and codes emitted by the shipped handler that no declaration lists.

Three of those vocabularies are enforced and one is not, and the difference matters when you are writing a handler:

Routes and their permission gates

All routes live in modules/Automations/config/routes.php — never in core application/config/routes.php, where a stranded copy would permanently shadow the module's own declaration. The file returns 20 array keys: 18 named endpoints, the canonical entry, and a catch-all that must stay last so it does not swallow future fragments.

Every route additionally passes the constructor's two gates first: with_module("automations") then with_permission("automations"). The column below lists what each endpoint demands on top of those.

Two naming notes worth knowing before you go looking. clone is a PHP keyword; it is legal as a method name in PHP 7+, but the method is called clone_automation so the URL stays readable without depending on that. And there is no settings-save route: the Settings tab is a read-only registry inspector today. Its write path is Milestone 4, which is why the settings:automation audit event below is declared and not yet emitted.

System-activity log events

Declared in config/system_logs.php and merged into the core system_logs_config at bootstrap, ungated by active state so that historical entries from a disabled module still resolve their labels and filter dropdowns. Keys are <field_name>:<module_name>, matching exactly what is passed to set_system_logs().

Run entries are attributed to the System actor seeded by core migration application/migrations/20260903232432_seed_system_actor_user.php and resolved by erpat_system_actor_id(), which is cached per database because one cron tick walks several tenants in a single process. It returns 0 when the actor is unseeded, and 0 must be treated as a refusal, not as a fallback: an approval with a blank approver is worse than an approval that did not happen.

Permission keys

Ten keys, all declared explicitly in config/permissions.php — including every child. Omitting a child would let the Roles editor's prefix auto-expansion silently swallow every later key sharing the automations prefix.

automations_go_live is highlighted because it is the one that lets records change unattended, and because it is the key whose holder's authority is revalidated at run time. Full semantics, including why publishing and going live are two separate permissions, are in Permissions, Authority & Scheduling.

Note what is not here: attendance decision permissions. An attendance automation reuses the existing attendance_approval and attendance_update keys rather than forking parallel ones. The module must never invent a second way to be allowed to approve attendance.

Helper functions

helpers/automations_helper.php is loadable, not auto-loaded: every consumer calls $this->load->helper('automations') explicitly. It is kept free of CI state so the identity and key functions can be unit-tested directly, which is why they live here rather than in the controller.

The last one carries the module's clearest statement about where the trust boundary sits, and it is worth reading in the original:

// modules/Automations/helpers/automations_helper.php
//
// The builder's scope picker is GUIDANCE, NOT SECURITY — like every other wizard
// field, its value arrives from a browser and is revalidated here before it can be
// stored. Anything unrecognised is DROPPED rather than persisted, so a crafted post
// cannot smuggle arbitrary text into scope_json ahead of the consumers that will
// read it.
//
// Duplicates collapse and order is preserved, so re-saving an unchanged selection
// produces an unchanged config hash and does not invalidate an approved preview.

That second paragraph is the non-obvious half: normalization is order-preserving and idempotent on purpose, because the config hash is computed over the stored blocks. A normalizer that reordered tokens would change the hash on a no-op save and quietly invalidate a preview the operator had already approved.

File map

The module owns its entire surface — routes, permissions, menu, migrations, cron job, tests, docs. Nothing about the Automation Center lives in application/ except the two things core genuinely owns: the module_automations setting on the primary database, and the System actor user.

The one shipped automation type is not in this module, and that is the whole architectural point: