Skip to content

Loader Work Assignment — DB Preflight Template Validation 2026-08-08

  • Status: DOCUMENTATION VALIDATION / QUERY PACKAGE NOT EXECUTED
  • Repository baseline: qa/master@1db9420b796e4c7ca07d6703bd161b9feec7ef02
  • Query package: RBR-AWCO-002-READONLY-DB-PREFLIGHT.md
  • Caller inventory: LOADER_WORK_ASSIGNMENT_CURRENT_CALLER_AND_GUARD_INVENTORY_2026-08-08.md
  • Evidence worksheet: RBR-AWCO-002-EVIDENCE-WORKSHEET.md
  • Program state: PHASE IMPLEMENTATION USER HOLD
  • Effects: documentation review only

1. Objective

Validate that the DB preflight package is:

  • read-only by construction;
  • tenant-scoped;
  • compatible with the current MySQL source contract;
  • ordered by prerequisites;
  • free of prohibited locking and mutation statements;
  • free of SQL subqueries and derived-table dependencies;
  • bounded in row detail;
  • capable of characterizing both Loader assignment representations;
  • capable of locating terminal/general Clock Out exposure and Final Break divergence;
  • explicit about what each result proves and does not prove.

2. Query Register

Query Purpose Validation
AWCO-DB-001 Database/server/timezone identity PASS
AWCO-DB-002 Required table existence PASS
AWCO-DB-003 Required column definitions PASS
AWCO-DB-004 Relevant index inventory PASS
AWCO-DB-010 Active representation counts PASS
AWCO-DB-011 Active/deleted representation history PASS
AWCO-DB-012 JSON/discriminator validity PASS
AWCO-DB-020 Status counts by representation PASS
AWCO-DB-021 CANCELED / CANCELLED inventory PASS
AWCO-DB-030 Active terminal Clock Out rows PASS
AWCO-DB-031 Duplicate terminal rows per Loader PASS
AWCO-DB-032 Rows ordered after terminal Clock Out PASS
AWCO-DB-033 Route-owned Started Clock Out bypass candidates PASS
AWCO-DB-040 Multiple Started Planner rows PASS
AWCO-DB-041 Duplicate active queue positions PASS
AWCO-DB-050 Route Final Break Planner linkage PASS
AWCO-DB-051 Typed Final Break Staff Time linkage PASS
AWCO-DB-052 Mixed Route row / typed Break event evidence PASS
AWCO-DB-060 Typed request-token duplicates PASS
AWCO-DB-061 Local notification evidence summary PASS
AWCO-DB-070 Duplicate linked Staff Time events PASS
AWCO-DB-071 Clock Out Planner/event consistency PASS
AWCO-DB-080 Bounded safe row sample PASS

The query names remain stable for future evidence results.

3. Read-Only Validation

The package contains only:

SELECT
JOIN / LEFT JOIN
GROUP BY
HAVING
ORDER BY
LIMIT
information_schema reads
JSON extraction
aggregate calculation

The package contains none of:

INSERT
UPDATE
DELETE
REPLACE
ALTER
CREATE
DROP
TRUNCATE
FOR UPDATE
LOCK TABLES
GET_LOCK
RELEASE_LOCK
mutating stored procedure calls

Result: PASS.

4. Query-Shape Validation

Current project DB rules prohibit runtime subqueries and FOR UPDATE. The preflight package follows the same conservative shape.

  • no scalar subquery;
  • no correlated subquery;
  • no IN (SELECT ...);
  • no derived table in FROM;
  • no common table expression;
  • no row lock;
  • no temporary table;
  • no schema write.

Cross-row questions are expressed through explicit self-joins and grouped aggregates.

Result: PASS.

5. Tenant Scope Validation

Every business-table query uses:

CLIENT_ID = <CLIENT_ID>

Every cross-table join preserves:

child.CLIENT_ID = parent.CLIENT_ID

The placeholder rule requires one verified positive deployment-local tenant ID. The package explicitly prohibits deriving the value from a customer name or another deployment.

Result: PASS.

6. Representation Coverage Validation

The package distinguishes:

TYPED
ROUTE
BOTH
OTHER

Using current source discriminators:

TYPED
-> ASSIGNMENT_DATA IS NOT NULL

ROUTE
-> SPECIAL_INSTRUCTION = 'QUEUE_WORK_ASSIGNMENT'

BOTH
-> both predicates

It inventories active and deleted rows separately and does not infer canonical ownership from population counts.

Result: PASS.

7. Terminal Guard Validation

The package identifies terminal Clock Out using either current source representation:

ASSIGNMENT_DATA.type = CLOCK_OUT
OR
SPECIAL_INSTRUCTION = QUEUE_WORK_ASSIGNMENT
AND COMMODITY_NAME = CLOCK_OUT

It then checks:

  • active terminal rows;
  • duplicates per Loader;
  • later active Planner rows by queue order and ID tie-break;
  • Route-owned Started Clock Out rows with no typed payload.

The queue-order query is correctly labeled as diagnostic. It does not claim that numeric order alone proves physical work execution.

Result: PASS.

8. General Clock Out Exposure Validation

AWCO-DB-033 returns exactly the current data condition that a typed-only Staff Time guard cannot see:

JOB_STATUS = STARTED
SPECIAL_INSTRUCTION = QUEUE_WORK_ASSIGNMENT
COMMODITY_NAME = CLOCK_OUT
ASSIGNMENT_DATA IS NULL

The package correctly states:

candidate condition in current data
!= proof the general action was invoked

Result: PASS.

9. Final Break Validation

The package separately queries:

Route representation

  • FINAL_DRAFT.finalBreak;
  • FINAL_DRAFT.finalBreakId;
  • linked QUEUE_BREAK Planner identity/status/times.

Typed representation

  • ASSIGNMENT_DATA.finalBreakRequired;
  • linked BREAK_IN, BREAK_OUT, JOB_START, JOB_END, and CLOCK_OUT Staff Time events.

Mixed evidence

  • Route-owned Clock Out rows that also have Break events linked directly to the Clock Out Planner.

This is sufficient to classify Route, typed, mixed, orphan, and contradictory current evidence without mutating records.

Result: PASS.

10. Cancellation And Request Identity Validation

The package inventories both cancellation spellings without changing them.

Typed request-token duplicates are grouped by:

CLIENT_ID
LOADER_NAME
ASSIGNMENT_DATA.requestToken

The query does not expose complete assignment JSON.

Result: PASS.

11. Notification Evidence Validation

The local evidence summary distinguishes:

typed telegram.status
Route LAST_SENT_TIME present/absent

It explicitly states that local evidence does not prove provider delivery.

Result: PASS.

12. Staff Time Validation

The package checks:

  • duplicate event groups;
  • Planner-linked JOB_START;
  • JOB_END;
  • CLOCK_OUT;
  • BREAK_IN;
  • BREAK_OUT;
  • Clock Out Planner/event consistency.

The validation does not assume every duplicate Break event is invalid and requires representation/workflow classification.

Result: PASS.

13. Data-Minimization Validation

The bounded detail query includes:

  • IDs;
  • Loader local ID token;
  • status;
  • queue order;
  • representation markers;
  • safe JSON discriminator fields;
  • request token;
  • timestamps.

It excludes:

  • full JSON payloads;
  • Telegram target IDs;
  • employee names;
  • credentials;
  • session identifiers;
  • unrelated business detail.

Result: PASS.

14. Performance And Operational Risk

The package does not claim that every query is safe on every live database size.

Before execution:

  • record current table size and index shape;
  • use a read-only account;
  • execute aggregates in the stated order;
  • stop if the plan or latency creates unacceptable operational pressure;
  • retain LIMIT on bounded row-detail queries;
  • do not add broad JSON payload export.

Because no database was available in this documentation session:

EXPLAIN review: NOT RUN
query latency: NOT RUN
index adequacy: NOT ESTABLISHED

This limitation is correctly stated.

15. Classification Validation

The package provides distinct outcomes:

SOURCE DISCREPANCY ONLY
CURRENT DATA EXPOSURE
CURRENT INTEGRITY CONFLICT
HISTORICAL DIVERGENCE
BLOCKED

It also separates database evidence from API/browser/operator impact.

Result: PASS.

16. Authorization And Stop Validation

The package requires explicit:

  • environment;
  • database identity;
  • read-only account;
  • tenant;
  • executor;
  • retention owner;
  • approver.

Stop conditions cover:

  • ambiguous target;
  • writable account;
  • uncertain tenant;
  • missing schema;
  • PII exposure;
  • query operational risk;
  • active incident evidence;
  • concurrent investigation;
  • schema/source mismatch.

Result: PASS.

17. Effect Validation

Database connection: NOT PERFORMED
Queries executed: 0
Rows read: 0
Rows written: 0
Schema changes: 0
Provider effects: 0
Physical effects: 0
Wiki effects: 0
Runtime changes: 0

Result: PASS — documentation-only boundary preserved.

18. Final Validation

Read-only statement review: PASS
Prohibited SQL review: PASS
Subquery/locking review: PASS
Tenant-scope review: PASS
Current representation coverage: PASS
Terminal/general Clock Out coverage: PASS
Final Break coverage: PASS
Cancellation/request/notification coverage: PASS
Staff Time coverage: PASS
Data minimization: PASS
Result/stop/cleanup contract: PASS
Actual schema compatibility: NOT RUN
Actual query syntax against target server: NOT RUN
EXPLAIN/performance: NOT RUN
Database results: NONE
Implementation authorization: NONE

19. Maintenance

Revalidate the package when current schema, representation markers, JSON fields, queue ordering, Final Break ownership, Staff Time linkage, cancellation status, request identity, or notification evidence changes.

---

Source: docs/reconstruction/LOADER_WORK_ASSIGNMENT_DB_PREFLIGHT_TEMPLATE_VALIDATION_2026-08-08.md — ctc repository · This page is an English translation of the source document. Identifiers, file paths and code symbols are unchanged.