# Eloquent Safe Patterns

## 🔒 1. Column Reference Integrity

**Core principle**: Any point in the code where you reference a model attribute or column name
MUST be verified against the **actual DB schema**. This applies equally to:

| Interaction | Example |
|-------------|---------|
| `select()` / column lists | `Model::select(['id', 'startdate'])` |
| `where()` / `orWhere()` | `->where('startdate', '>=', now())` |
| `orderBy()` / `groupBy()` | `->orderBy('enddate', 'desc')` |
| `$fillable` / `$casts` | `'completedquantity' => 'integer'` |
| Mass assignment (`create`, `update`) | `Model::create(['startdate' => ...])` |
| Direct attribute access | `$record->manufacturingworkcenter` |
| JSON column access | `$record->fields['key']` — confirm `fields` is a JSON column |
| Static column lists | `getKanbanColumns()`, `getReportColumns()`, `getFillable()` |

**Mandatory verification for any new column reference:**
```bash
php artisan tinker --execute="print_r(Schema::connection('tenant_connection')->getColumnListing('your_table'));"
```

> ⚠️ **NetSuite naming convention**: This project stores columns exactly as NetSuite exports them —
> often lowercase with no underscores. NEVER assume `snake_case`.
> `startdate` ≠ `start_date` | `enddate` ≠ `end_date` | `completedquantity` ≠ `completed_quantity`

**❌ BAD** — referencing column without schema verification:
```php
->where('start_date', '>=', $date)      // column doesn't exist
$record->completed_quantity              // wrong attribute name
Model::create(['due_date' => $date])     // fails silently or throws
```

**✅ GOOD** — all names verified against `Schema::getColumnListing()` first:
```php
->where('startdate', '>=', $date)
$record->completedquantity
Model::create(['enddate' => $date])
```

### 1B. JSON Column & Schema Casting
When a Model utilizes JSON columns (e.g., `fields` for custom fields) or dynamic property bags:
1. **The column MUST be requested:** If the model defines a `getColumns()` or similar array used for `select()` statements, the JSON column name (like `'fields'`) **must** be explicitly included. Omitting it will silently drop custom data during serializations.
2. **The column MUST be cast:** Eloquent requires that any JSON/Array column be explicitly defined in the model's `$casts` array.
```php
// If 'fields' is selected but not cast, testing or creating records will throw a PDO "Array to string conversion" Exception.
protected $casts = [
    'fields' => 'array',
];
```

### 1C. JOIN Clause Ambiguity
In queries implementing `JOIN`, ALL columns utilized in `WHERE`, `ORDER BY`, and `SELECT` clauses MUST be strictly qualified with their table prefix or alias (e.g., `table.column`). Never assume Laravel will resolve dynamic column name references; this prevents "Column is ambiguous" PDO errors.

---

## 🔗 2. Eager Load Completeness

For every `->with('relation')` or `->load('relation')`:

**Rule A — Non-empty callbacks:** Every `with()` callback MUST include at least `select('id', ...)`.
An empty callback `with('relation', fn($q) => $q)` silently loads all columns (N+1 risk, payload bloat).

**❌ BAD:**
```php
$relationships['assignees'] = function ($query) {}; // empty = loads everything
```
**✅ GOOD:**
```php
$relationships['assignees'] = function ($query) {
    $query->with(['employee', 'vendor'])->select('id', 'refid');
};
```

**Rule B — FK in select list:** If you eager load `relation`, the FK column for that relation
MUST be in the parent model's `select()`.

**❌ BAD:**
```php
WorkOrder::select(['id', 'title'])->with('assemblyItem'); // missing FK 'assemblyitem'
```
**✅ GOOD:**
```php
WorkOrder::select(['id', 'title', 'assemblyitem'])->with('assemblyItem');
```

**Rule C — Relationship Existence Verification (Crucial for Dynamic Types):** 
Before dynamically appending to an eager-load array based on variable configurations, you MUST verify that the relationship method exists on the target eloquent model class. Failure to do so results in a `RelationNotFoundException`.

**❌ BAD:**
```php
if ($type === 'task') {
    $relationships['project'] = fn($query) => $query->select('id', 'name'); // Assume 'project' exists
}
```
**✅ GOOD:**
```php
if ($type === 'task' && method_exists($modelClass, 'project')) {
    $relationships['project'] = fn($query) => $query->select('id', 'name');
}
```

**Checklist before any `with()` call:**
- [ ] FK column for the relation is in `select()`
- [ ] Callback is non-empty
- [ ] Nested relations (e.g., `employee->user`) are eager-loaded if accessed downstream

---

## 🧹 3. Dead Code & Unreachable Branches

Before committing, verify that every newly created or modified method, class constant, or conditional key is actually reachable across the codebase. Do not leave "orphan" methods or "future" variables.

**Verification Command Heuristic:**
```bash
grep -r "MethodName\|VariableName\|'config_key'" src/ tests/
```

**Rule:** If a `grep` search for a specific method name, trait, or configuration key returns 0 or 1 match (its own definition), it is Dead Code. It MUST be deleted. Do not comment it out, delete it completely.

---

## 🚫 4. Duplicate Field Assignments

A controller or service MUST NOT assign the same response key twice in the same method.

**❌ BAD:**
```php
$cardData['work_center'] = $record->manufacturingworkcenter; // block A
// ... 50 lines later ...
$cardData['work_center'] = $record->manufacturingworkcenter; // block B — duplicate
```

**Detection command:**
```bash
grep -n "\$cardData\['" src/App/Http/Controllers/CardDetailController.php | \
awk -F"'" '{print $2}' | sort | uniq -d
```
Any key appearing more than once is a duplicate — keep only the most specific assignment.

---

## 📋 5. Generic Variable Names

**FORBIDDEN** in new code: `$data`, `$item`, `$result`, `$response` as standalone variable names.

**✅ GOOD alternatives:**
| Generic | Descriptive |
|---------|-------------|
| `$data` | `$cardData`, `$reportPayload`, `$apiResponse` |
| `$item` | `$workOrder`, `$projectTask`, `$record` |
| `$result` | `$kanbanColumns`, `$assigneeList` |

---

## 🛡️ 6. Defensive Relationship Access

**Core principle**: Never blindly assume the return type of a dynamic or conditionally-loaded Eloquent relationship (e.g., `getRelation('assigned')`). Depending on the model definition, what might be a `belongsTo` (returning a single `Model`) in one domain might be a `belongsToMany` or `hasMany` (returning a `Collection`) in another.

Calling `$assignee->property` on a `Collection` will cause a fatal runtime error or silently return null arrays depending on the context.

**❌ BAD** — Assuming singular model:
```php
if ($record->relationLoaded('assigned')) {
    $assignee = $record->getRelation('assigned');
    return [
        'id' => $assignee->id // Fatal error if $assignee is a Collection
    ];
}
```

**✅ GOOD** — Defensive Iterable Check:
```php
if ($record->relationLoaded('assigned')) {
    $assignees = $record->getRelation('assigned');
    
    // Check if it's a Collection/array
    if (is_iterable($assignees)) {
        return collect($assignees)->map(fn($item) => ['id' => $item->id])->toArray();
    }
    
    // Single model fallback
    if ($assignees instanceof Model) {
        return [['id' => $assignees->id]];
    }
}
```

**Rule**: If a service or controller retrieves a relationship that can vary by model context (Polymorphic, Interfaced, or Dynamic), you MUST implement an `is_iterable` check before accessing its properties.

---

## ☣️ 7. Payload Pollution via Generic Formatters

**Core principle**: When creating service formatters (e.g., `CardFormatterService`, `Resource` classes), do NOT consolidate field assignments into a single generic method if the models being formatted do not share the exact same domain data structure.

**❌ BAD** — Merging unrelated fields for convenience (causes payload pollution on models that lack the exclusive fields):
```php
protected function mapManufacturingFields(array &$data, Model $record) {
    // Shared fields between Work Orders and Manufacturing Operations
    $data['item_name'] = $record->item_name;
    // Exclusive fields being accidentally applied to all!
    $data['setup_time'] = $record->setuptime; 
}
```

**✅ GOOD** — Separating shared vs exclusive data into distinct helpers:
```php
protected function mapSharedEntityFields(array &$data, Model $record) {
    $data['item_name'] = $record->item_name;
}

protected function mapManufacturingStats(array &$data, Model $record) {
    $data['setup_time'] = $record->setuptime;
}
```

**Rule**: If two models share *some* fields but not all, extract only the strictly shared fields into a common helper. Exclusive fields MUST remain isolated in their own specific helpers or blocks to prevent Frontend payload pollution with nulls, and to prevent semantic errors (like a "Work Order" having a "setup_time").

---

## 🕐 9. TIMESTAMP(6) Microsecond Precision Contract

**Rule:** When a migration defines a `TIMESTAMP(n)` column with `n > 0` (microsecond precision), the corresponding Eloquent model MUST declare `$dateFormat = 'Y-m-d H:i:s.u'`. Without it, Laravel's default `'Y-m-d H:i:s'` format silently truncates all sub-second values to `.000000` on **both write and read**.

**Why the default breaks silently:**
- **Write path:** `Model::create(['timestamp' => now()])` calls `fromDateTime()` → `getDateFormat()` → default `'Y-m-d H:i:s'` → microseconds discarded before INSERT.
- **Read path:** Eloquent calls `asDateTime()` with the same format — if the DB returns `'2026-01-01 12:00:00.123456'`, the `.123456` suffix is silently ignored.
- **`->useCurrent()` does NOT protect `created_at`:** Eloquent's `updateTimestamps()` writes `created_at` explicitly on every INSERT, bypassing the MySQL column DEFAULT.

**❌ BAD — precision silently lost:**
```php
// Migration: $table->dateTimeTz('timestamp', 6);
// Model — no $dateFormat declared:
protected $casts = [
    'timestamp' => 'datetime', // uses default 'Y-m-d H:i:s' — truncates microseconds
];
```

**✅ GOOD — precision preserved on write and read:**
```php
// Model — must declare $dateFormat when any TIMESTAMP(n>0) column exists:
protected $dateFormat = 'Y-m-d H:i:s.u';

protected $casts = [
    'timestamp' => 'datetime:Y-m-d H:i:s.u', // explicit — documents precision intent
];
```

**Special case — `$timestamps = false` models:** If the model sets `public $timestamps = false` (e.g., `created_at` is set by MySQL DEFAULT), Eloquent never writes datetime values directly. In this case, `$dateFormat` is not required, but any `datetime` cast used for reading MUST include the format argument:
```php
protected $casts = [
    'created_at' => 'datetime:Y-m-d H:i:s.u', // read-only cast with explicit format
];
```

**Checklist when adding a `TIMESTAMP(n)` column to a migration:**
- [ ] Model declares `protected $dateFormat = 'Y-m-d H:i:s.u'`
- [ ] All `datetime` casts use `'datetime:Y-m-d H:i:s.u'`
- [ ] Tests assert microsecond round-trip: `$fresh->timestamp->format('u')` equals the original

---

## 🔑 10. Never Use Auto-Generated UUIDs as `updateOrCreate` Match Keys

**Rule:** Never use `updateOrCreate` with an auto-generated UUID as the match criteria. When the "find" key is generated on every call, the lookup always fails and the method degrades to a pure INSERT, producing infinite duplicate rows.

Always use the **business natural key** (e.g., `[task_slug, type]`) as the match array. Use `firstOrNew` to handle the UUID assignment safely — assign the UUID only on the CREATE path (`!$memory->exists`), leaving existing entries' IDs intact.

**❌ BAD** — UUID regenerated on every call; `updateOrCreate` never finds the record:
```php
$id = $this->option('id') ?: (string) Str::uuid(); // new UUID on every standard invocation

AgentMemory::updateOrCreate(
    ['id' => $id],      // always misses — the UUID was just invented above
    ['content' => $content, ...]
);
```

**✅ GOOD** — Match on the natural business key; assign UUID only when creating:
```php
$id = $this->option('id') ?: (string) Str::uuid();

$memory = AgentMemory::firstOrNew(['task_slug' => $slug, 'type' => $type]);

if (! $memory->exists) {
    $memory->id = $id; // UUID only assigned on INSERT
}

$memory->content = $content;
$memory->metadata = [...];
$memory->save();
```

**Why `firstOrNew` over `updateOrCreate` here:**
- `updateOrCreate($match, $values)` always runs a SELECT then INSERT/UPDATE in one operation. If `$match` never resolves (because the key is freshly generated), it silently degrades to INSERT-only.
- `firstOrNew` makes the intent explicit: find by natural key, check `$model->exists`, then branch. The UUID is conditional — assigned once at creation, never overwritten.

**Checklist:**
- [ ] The match array in `updateOrCreate` or `firstOrNew` uses stable, business-meaningful columns
- [ ] Auto-generated values (UUIDs, tokens, hashes) are NEVER in the match array
- [ ] When using `firstOrNew`, the UUID/generated key is assigned inside the `!$model->exists` guard

---

## 🏎️ 8. Model Event Race Conditions (afterCommit)

**Core principle**: When writing Eloquent Observers or hooking into Model events (`saved`, `updated`, `created`, `deleted`) that trigger external side-effects (like clearing a Redis Cache, firing an external API webhook, or dispatching a queued Job), you MUST ensure these side-effects do not fire *before* the database transaction commits. 

If a side-effect fires inside a transaction block (`DB::transaction()`), and a concurrent request processes that side-effect before the original transaction commits, the concurrent request will read stale pre-commit data from the database.

**❌ BAD** — Invalidation fires immediately, creating a race condition window:
```php
static::saved(function ($model) {
    // Cache is cleared BEFORE the transaction commits!
    $model->invalidateKanbanCache(); 
});
```

**✅ GOOD** — Invalidation waits for the transaction to complete:
```php
static::saved(function ($model) {
    \Illuminate\Support\Facades\DB::connection('tenant_connection')->afterCommit(function () use ($model) {
        $model->invalidateKanbanCache();
    });
});
```
*Note: If using dedicated Observer classes, you can simply set `public $afterCommit = true;` on the class.*