This is an old revision of the document!
Title: Consistency Manager (Edges + Policy-Aware Deletion) Version: 1.2 Updated: 2025-11-11
—
1) Purpose
Maintain application-level referential integrity across PHP models (Invoice, InvoiceLine, Account, Partner, Contract, User, …) using: - An edges registry table (`consistency_edges`) that stores directed links like `invoice → user` with a relation label (e.g., `invoice:owner`). - A centralized policy map that decides what to do when deleting a node: RESTRICT | NULLIFY | CASCADE | DETACH. - A single, policy-aware deletion method: `_destroyMySelf()` on every model.
What changed today - Edges can reliably reference string identifiers (e.g., `partners.uniqueid`) as `dst_id`. - Per-edge resolve_by support: pass `['resolve_by' ⇒ 'uniqueid']` to resolve a string key to the destination’s numeric `id` before storing. - Optional edge UID templating saved in `meta_json.uid`. - A registry-driven reindex CLI builds all edges from a single PHP registry file. - Safer table-name resolution for `\Lib\GlobalOptions::*` across runtime and CLI. - Inbound/outbound lookups accept string `dst_id` and numeric `src_id` consistently.
—
2) One deletion method
Only call `_destroyMySelf()` from application code. It is policy-aware by default. It: - Inspects inbound edges into the node being deleted. - Applies the policy for that node’s table. - Performs NULLIFY/DETACH/CASCADE where configured. - Physically deletes the row and removes its edges.
No separate `delete()` or wrapper is required.
—
3) GlobalOptions table names
In model edge maps and in the policy map, table names are configured as `\Lib\GlobalOptions::*` identifiers. At runtime, these identifiers are resolved to physical table names by calling the corresponding `GlobalOptions` method.
Examples: - `\Lib\GlobalOptions::CPTDB_TBL_USERS()` → actual users table - `\Lib\GlobalOptions::CPTDB_TBL_ACCOUNTING_INVOICES()` → actual invoices table
Models set `$this→table = \Lib\GlobalOptions::XYZ();` so they always use the physical table when persisting/loading.
—
4) Edges map (per model)
Each model defines `$edgesMap` to declare outbound relations. Example (Invoice):
``` protected array $edgesMap = [
'owner_user_id' => [
'relation' => 'invoice:owner',
'dst_table' => 'GlobalOptions::CPTDB_TBL_USERS',
],
'address_id' => [
'relation' => 'invoice:address',
'dst_table' => 'GlobalOptions::CPTDB_TBL_ADDRESSES',
],
'contact_id' => [
'relation' => 'invoice:contact',
'dst_table' => 'GlobalOptions::CPTDB_TBL_CONTACTS',
],
'rawfile_id' => [
'relation' => 'invoice:rawfile',
'dst_table' => 'GlobalOptions::CPTDB_TBL_RAWFILES',
],
// SPECIAL: invoice.partner_id stores the Partner’s UNIQUE string key (partners.uniqueid)
'partner_id' => [
'relation' => 'invoice:partner',
'dst_table' => 'GlobalOptions::CPTDB_TBL_PARTNERS',
// choose ONE of the following, depending on how you want to store:
// A) Keep edges.dst_id as the UNIQUE string (no lookup):
// (no resolve_by set)
// B) Resolve the unique string to partner.id before storing:
'resolve_by' => 'uniqueid',
// Optional audit UID template (goes to meta_json.uid):
// 'uid' => 'inv:partner:{src_id}:{dst_id}',
],
]; ```
On every `_save()`, the base model: - Deletes old outgoing edges for this record. - Inserts new edges derived from current field values (using the rules above).
—
5) Policy map
Defined in `Lib/Model/ConsistencyPolicy.php`. Keys and `nullify.table` entries can use `GlobalOptions::*` identifiers.
``` $T_USER = 'GlobalOptions::CPTDB_TBL_USERS'; $T_INVOICES = 'GlobalOptions::CPTDB_TBL_ACCOUNTING_INVOICES'; $T_INVOICE_LINES = 'GlobalOptions::CPTDB_TBL_ACCOUNTING_INVOICE_LINES';
return [
$T_USER => [
'inbound' => [
'invoice:owner' => [
'action' => 'NULLIFY',
'nullify' => [
['table' => 'GlobalOptions::CPTDB_TBL_ACCOUNTING_INVOICES', 'field' => 'owner_user_id'],
],
],
'*' => ['action' => 'RESTRICT'],
],
],
$T_INVOICES => [
'inbound' => [
'invoice_line:invoice' => ['action' => 'CASCADE'],
'*' => ['action' => 'RESTRICT'],
],
],
$T_INVOICE_LINES => [
'inbound' => [
'*' => ['action' => 'RESTRICT'],
],
],
]; ```
Actions - RESTRICT: block deletion if any inbound edges match. - NULLIFY : set referencing field(s) to `NULL` based on `nullify` list. - CASCADE : recursively delete referencing sources (policy-aware). - DETACH : remove edges only; no data changes.
—
6) Storage model & SQL schema
What the runtime stores - `src_table` / `dst_table`: raw physical table names (GlobalOptions are resolved before write). - `src_id`: integer (the source record’s `id`). - `dst_id`: string (can be a numeric id or a unique string such as `partners.uniqueid`). - `meta_json`: free JSON; the manager can write `{“_resolved”:{“by”:“uniqueid”,“from”:“NVG-2025-…”,“to”:“55090”}}` and/or a `uid`.
Recommended schema (`sql/consistency_edges.sql`) ``` CREATE TABLE `consistency_edges` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `src_table` VARCHAR(128) NOT NULL, `src_id` BIGINT UNSIGNED NOT NULL, `relation` VARCHAR(128) NOT NULL, `dst_table` VARCHAR(128) NOT NULL, `dst_id` VARCHAR(128) NOT NULL, -- CHANGED: allow string identifiers `weight` INT NOT NULL DEFAULT 1, `meta_json` JSON NULL, `created` DATETIME NOT NULL, `updated` DATETIME NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `u_edge` (`src_table`,`src_id`,`relation`,`dst_table`,`dst_id`), KEY `i_dst` (`dst_table`,`dst_id`), KEY `i_src` (`src_table`,`src_id`), KEY `i_rel` (`relation`)
); ```
Migration note - If you previously had `dst_id BIGINT`, migrate to `VARCHAR(128)` if you want to store string keys. - If you prefer to keep `dst_id` numeric, use `resolve_by` (see §7) so the manager resolves the string to the destination’s numeric `id` before insert.
—
7) How destination identifiers are chosen (numeric vs string)
There are two supported modes per edge:
A) Store the string key as `dst_id` (no resolution) - Do not set `resolve_by`. - The edge stores whatever the model field contains (e.g., `partner_id = 'NVG-2025-1181A'`). - Schema must allow strings for `dst_id`.
B) Resolve the string key → destination numeric `id` - In the edge definition (model or registry), set: `'resolve_by' ⇒ 'uniqueid'`. - The manager runs: `SELECT id FROM partners WHERE uniqueid = :val LIMIT 1`. - If found, it stores that numeric `id` as `dst_id`, and writes a hint into `meta_json._resolved`. - If not found, it keeps the original value and logs a warning.
Which is correct? Both are supported. Pick a single convention per table/relation and keep it consistent. - Prefer resolution to numeric `id` when you control the destination table and want faster joins. - Prefer string `dst_id` when referencing external identifiers you don’t want to dereference now.
—
8) CLI (with registry-based reindex)
Usage: ``` php bin/consistency show inbound <TABLE|GlobalOptionsId> <ID> php bin/consistency show outbound <TABLE|GlobalOptionsId> <ID> php bin/consistency delete <TABLE|GlobalOptionsId> <ID> php bin/consistency sync <TABLE|GlobalOptionsId> <ID>
# NEW php bin/consistency reindex table <TABLE|GlobalOptionsId> [–truncate-first] php bin/consistency reindex all [–truncate-first] ```
- Registry file: `Lib/Model/edges.registry.php`
Central place to declare source tables, their PKs, and edges (including `resolve_by` and optional `uid` template).
Registry example (Invoice) ``` return [
'nvg_cpt_accounting_invoices' => [
'pk' => 'id',
'chunk' => 1000,
'edges' => [
'owner_user_id' => [
'relation' => 'invoice:owner',
'dst_table' => 'GlobalOptions::CPTDB_TBL_USERS',
'uid' => '{src_table}:{relation}:{src_id}->{dst_table}:{dst_id}',
],
'address_id' => [
'relation' => 'invoice:address',
'dst_table' => 'GlobalOptions::CPTDB_TBL_ADDRESSES',
'uid' => 'inv:addr:{src_id}:{dst_id}',
],
'contact_id' => [
'relation' => 'invoice:contact',
'dst_table' => 'GlobalOptions::CPTDB_TBL_CONTACTS',
],
'rawfile_id' => [
'relation' => 'invoice:rawfile',
'dst_table' => 'GlobalOptions::CPTDB_TBL_RAWFILES',
],
// SPECIAL
'partner_id' => [
'relation' => 'invoice:partner',
'dst_table' => 'GlobalOptions::CPTDB_TBL_PARTNERS',
'resolve_by' => 'uniqueid', // resolve 'NVG-2025-…' → partners.id
],
],
],
]; ```
UID templating You can add a stable edge identifier (for audits) via `uid`: - Example: `{src_table}:{relation}:{src_id}→{dst_table}:{dst_id}` - Example: `inv:addr:{src_id}:{dst_id}` The CLI and runtime write this into `meta_json.uid`.
—
9) Runtime hooks (where edges are written)
In the base model, the simplified hook:
``` protected function registerConsistencyEdges(): void {
if (!class_exists('\Lib\Model\_ConsistencyManager')) return;
$mgr = \Lib\Model\_ConsistencyManager::get(); $tbl = $this->fetchTableName(); if (!$mgr || !$tbl || $this->id <= 0) return;
// Use raw table name (resolved already in fetchTableName) $srcSpec = $tbl;
// Remove old outbound edges $mgr->removeLinksFor(['table' => $srcSpec, 'id' => $this->id], 'src');
// Recreate outbound edges
foreach ($this->edgesFromSelf() as $edge) {
$mgr->ensureLink(
['table' => $srcSpec, 'id' => $this->id],
$edge['relation'],
[
'table' => $edge['dst_table'],
'id' => $edge['dst_id'],
// Pass-through: allow special resolution per edge
'resolve_by' => $edge['resolve_by'] ?? null,
],
$edge['meta'] ?? [],
1
);
}
} ```
And the minimal `edgesFromSelf()` update:
``` public function edgesFromSelf(): array {
$edges = [];
foreach ($this->edgesMap as $field => $map) {
$raw = $this->record[$field] ?? null;
if ($raw === null || $raw === '' || $raw === 0 || $raw === '0') continue;
$edges[] = [
'relation' => (string)($map['relation'] ?? (static::class . ':' . $field)),
'dst_table' => $this->canonicalizeSpec((string)($map['dst_table'] ?? '')),
'dst_id' => (string)$raw, // keep as-is (string or number)
'resolve_by' => isset($map['resolve_by']) ? (string)$map['resolve_by'] : null,
'meta' => (array)($map['meta'] ?? []),
];
}
return $edges;
} ```
—
10) Worked examples
1) Delete a User who owns invoices (owner_user_id) Policy: `invoice:owner → NULLIFY invoice.owner_user_id`
Result: Sets `owner_user_id = NULL` on referencing invoices; edges removed; user deleted.
2) Delete a Partner referenced by invoices Policy: `invoice:partner → RESTRICT`
Result: Blocked. Reassign or change policy to NULLIFY/CASCADE.
3) Delete an Invoice that has lines Policy: `invoice_line:invoice → CASCADE`
Result: Deletes lines first (policy-aware), then the invoice.
4) Delete an Account referenced by invoice lines Policy: `invoice_line:account → RESTRICT`
Result: Blocked. Migrate lines or change policy to NULLIFY.
5) Delete an InvoiceLine With default `'*' ⇒ RESTRICT` on line destinations, deletions are constrained by inbound relations (if any).
6) DETACH-only scenario If an inbound relation is DETACH, deletion removes the edge row, data stays untouched.
7) Partner unique key on Invoice
- If `edgesMap['partner_id']['resolve_by']='uniqueid'`: `partner_id='NVG-2025-…'` resolves to `partners.id` and stores that numeric id in `dst_id`.
- If omitted: the edge stores the string `NVG-2025-…` directly as `dst_id` (schema must allow strings).
—
11) Tips
- Always call `_destroyMySelf()` to delete. It’s policy-aware. - Keep `edgesMap` minimal and meaningful. - Prefer NULLIFY for optional FKs that can be cleared. - Use CASCADE sparingly and deliberately (e.g., Invoice → InvoiceLine). - RESTRICT is a safe default when in doubt. - Decide one convention per relation for `dst_id` (numeric vs string) and stick to it; if you need numeric, add `resolve_by`.
—
12) Quick migration checklist
- If you will store string `dst_id` values:
`ALTER TABLE consistency_edges MODIFY dst_id VARCHAR(128) NOT NULL;`
- (Optional) Add an edge UID to meta via registry `uid` templates (no schema change). - Run CLI reindex to rebuild edges from the new registry:
- One table: `php bin/consistency reindex table GlobalOptions::CPTDB_TBL_ACCOUNTING_INVOICES –truncate-first`
- All: `php bin/consistency reindex all –truncate-first`
- Verify deletion flows through `_destroyMySelf()` against your policy map.