---
name: strother-schema
description: >
  Authoritative database schema conventions for the Strother Fabrication estimating app.
  ALWAYS load this skill before writing, editing, or reviewing ANY migration, model,
  relationship, or query. Also load when adding new tables, new columns, new Eloquent
  relations, or any raw DB query. These conventions are non-negotiable — do not deviate
  based on Laravel defaults or general best practices if they conflict with these rules.
---

# Strother Schema Conventions

## 1. Critical Table and Column Names

These are non-obvious names that differ from what you might assume:

| What you might assume | Actual name | Why |
|---|---|---|
| `jobs` | `jobs_estimating` | Avoids MySQL reserved word conflict |
| `estimator_id` | `assigned_estimator` | FK to users.id, non-standard name |
| `start_date` | `anticipated_start_date` | Full name per spec |
| `prevailing_wage` (bool) | `prevailing_wage` | Correct — just confirming |
| `is_prevailing_wage` | ❌ does not exist | Wrong name — do not use |
| `is_union` | ❌ does not exist | No column, no migration |
| `is_bonded` | ❌ does not exist | No column, no migration |
| `is_certified_payroll` | ❌ does not exist | No column, no migration |

The Job model is `App\Models\Job`, its `$table = 'jobs_estimating'`.

## 2. Money Storage — Always DECIMAL, Never FLOAT

```php
// CORRECT
$table->decimal('unit_cost', 10, 2);      // dollar amounts
$table->decimal('overhead_rate', 5, 4);   // rates (0.2000 = 20%)
$table->decimal('margin_rate', 5, 4);     // rates
$table->decimal('tax_rate', 5, 4);        // rates
$table->decimal('total_weight', 10, 4);   // weights

// WRONG — never use these for money or rates
$table->float('unit_cost');
$table->double('margin_rate');
```

Decimal precision conventions:
- Dollar amounts: `decimal(10, 2)` — max $99,999,999.99
- Rates (overhead, margin, tax): `decimal(5, 4)` — stored as 0.2000, not 20
- Weights (lbs): `decimal(10, 4)`
- Linear feet: `decimal(10, 2)`

## 3. Enums — Always VARCHAR + CHECK, Never MySQL ENUM

```php
// CORRECT
$table->string('estimate_type')
      ->default('fab_install');
// Add CHECK in a separate statement or via DB::statement after table creation:
// CHECK (estimate_type IN ('fab_install', 'install_focus'))

$table->string('status')->default('draft');
// CHECK (status IN ('draft', 'in_progress', 'submitted', 'won', 'lost', 'cancelled'))

// WRONG — never use MySQL ENUM
$table->enum('estimate_type', ['fab_install', 'install_focus']); // ❌
```

Why: MySQL ENUM is painful to alter, not portable, and causes silent failures on
invalid values in some configurations.

## 4. Soft Deletes on All Content Tables

Every table that holds user-created content gets soft deletes:

```php
$table->softDeletes(); // adds deleted_at column
```

And the model uses the trait:

```php
use Illuminate\Database\Eloquent\SoftDeletes;

class Scope extends Model
{
    use SoftDeletes;
}
```

Tables that are NOT content (pivot tables, log tables, settings): soft deletes optional.
Tables that ARE content: soft deletes required, no exceptions.

Content tables: jobs_estimating, scopes, scope_materials, scope_equipment, scope_labor,
scope_labor_tasks, scope_notes, customers, customer_contacts, vendors, proposals,
scope_proposal_groups, job_documents, terms_and_conditions.

## 5. Optimistic Locking

High-contention tables (scopes, scope_materials, scope_labor) use optimistic locking
to prevent lost updates from concurrent auto-saves:

```php
$table->unsignedInteger('lock_version')->default(0);
```

On update, the application must:
1. Include `lock_version` in the WHERE clause
2. Increment it on successful update
3. Return 409 Conflict if the version doesn't match

## 6. Idempotent Creates — client_uuid

Forms that auto-save (estimate builder) use a `client_uuid` to prevent duplicate
records from double-submits or network retries:

```php
$table->uuid('client_uuid')->unique()->nullable();
```

On create: `INSERT ... ON DUPLICATE KEY UPDATE` or check-then-create using `client_uuid`.

## 7. Timestamps — Always Present

All tables use Laravel's default timestamps:

```php
$table->timestamps(); // created_at, updated_at
```

No exceptions. Even pivot tables get timestamps.

## 8. Foreign Keys — Naming Convention

```php
// Pattern: {table}_{column}_foreign
$table->foreignId('scope_id')->constrained('scopes')->cascadeOnDelete();
$table->foreignId('job_id')->constrained('jobs_estimating')->cascadeOnDelete();

// assigned_estimator is a non-standard FK — explicit reference required
$table->foreignId('assigned_estimator')->nullable()
      ->constrained('users')->nullOnDelete();
```

## 9. Sort Order Column

Tables with user-defined ordering use:

```php
$table->unsignedSmallInteger('sort_order')->default(0);
```

Tables that need it: scopes, scope_materials, scope_equipment, scope_labor_tasks,
scope_notes, scope_proposal_groups.

## 10. Key Relationships to Know

```
Job (jobs_estimating)
  └── hasMany Scope (scopes)
        ├── hasMany ScopeMaterial (scope_materials)
        ├── hasMany ScopeEquipment (scope_equipment)
        ├── hasMany ScopeLabor (scope_labor)
        │     └── hasMany ScopeLaborTask (scope_labor_tasks)
        └── hasMany ScopeNote (scope_notes)

Job
  └── hasMany Proposal (proposals)
        └── hasMany ScopeProposalGroup (scope_proposal_groups)
              └── belongsToMany Scope (via proposal_group_scopes)

Job
  └── hasMany JobDocument (job_documents)

Customer
  ├── hasMany CustomerContact (customer_contacts)
  └── hasMany Job
```

## 11. Migrations Are Append-Only

Never modify a migration file that has already run on any environment.
Create a new migration for any schema change.

Migration naming convention:
```
YYYY_MM_DD_HHMMSS_description_of_change.php
```

Keep descriptions precise:
- `add_lock_version_to_scopes_table` ✅
- `update_scopes` ❌ (too vague)

## 12. Models With No Migration Equivalent (yet)

These tables exist but have no Eloquent model in app code yet:
- `system_settings` → `App\Models\SystemSetting` (to be created)
- `tax_rates` → `App\Models\TaxRate` (to be created)
- `terms_and_conditions` → `App\Models\TermsAndConditions` (to be created)
- `audit_log` → `App\Models\AuditLog` (to be created)
- `proposal_group_scopes` (pivot) → no model needed, via belongsToMany

Do not create these models speculatively — wait for the feature that needs them.