04 — Filesystem DOT Reconciler (P2)

Files: sql/02_filesystem_reconciler.sql (sha256 38be533a…f6f54) + sql/collect_dot_bin_inventory.sh (sha256 8fda1e31…204c36). Author-mode + operator-handoff.

Channel reality (gap G4)

/opt/incomex/dot/bin is not in the read_file allowlist — probed live, returns [DENIED] path is outside the allowlist. The Agent cannot enumerate or hash the filesystem DOTs. Therefore the filesystem side is collected by an operator-run script and reconciled read-only in the DB.

Design

• _recon_dot_fs_inventory staging table: file_name, file_path, sha256, is_executable, size_bytes, mtime, is_backup(.bak), collected_at. Truncate+reload each run by collect_dot_bin_inventory.sh (READ-ONLY on FS: find+sha256sum+stat; the only DB write is into this dedicated staging table; no DOT executed, no file modified).
• v_dot_fs_reconciliation joins snapshot ↔ dot_tools (by file_path/name) ↔ birth_registry. fs_status ∈ BACKUP_FILE | FILE_NO_REGISTRY | REGISTERED_NOT_BORN | OK_REGISTERED_BORN.
• v_dot_registry_no_file — active dot_tools whose declared file is absent from the snapshot.
• v_dot_pivot_update_status — single-row classifier for the staged artifact.

Classification map (macro P2)

Required class	Where surfaced
file exists, no registry row	fs_status='FILE_NO_REGISTRY'
registry row, file missing	v_dot_registry_no_file
file hash drift	not yet — dot_tools has no baseline hash column; documented follow-up: add dot_tools.baseline_sha256 or _recon_dot_baseline, then diff vs snapshot. Not silently dropped.
executable, no birth	fs_status='REGISTERED_NOT_BORN' (+ exec filter)
executable, no governance onboarding	join to v_governance_row_object_gap (P5)
new file outside system	FILE_NO_REGISTRY
staged-only / not production-valid	v_dot_pivot_update_status

dot-pivot-update (DB-side evidence, live)

SELECT … FROM dot_tools WHERE name/file_path/code ILIKE '%pivot-update%' → 0 rows. Combined with prior recon (root:root, +x, 14441B, sha256 3313c057…, mtime 2026-06-03 07:19, not in dot_tools): STAGED_FILE_ONLY / UNBORN / NOT_VALID_DOT (see report 09).

Operator path

BIN_DIR=/opt/incomex/dot/bin ./collect_dot_bin_inventory.sh | \
  ssh contabo "docker exec -i postgres psql -U workflow_admin -d directus"
SELECT fs_status, count(*) FROM v_dot_fs_reconciliation GROUP BY 1;
SELECT * FROM v_dot_pivot_update_status;