## Backfill Transactions Table (transactionable records)

### Problem

During bulk upserts, model events are disabled, so the `booted`/`created` hooks that normally create a row in the `transactions` table do not fire. As a result, many parent transaction rows (e.g., `salesorders`, `invoices`, etc.) have no corresponding `transactions` record, breaking the intended linkage:

transaction_lines → transactions → {parent_transaction_table}

### Goal

Backfill the `transactions` table to ensure a transaction row exists for every parent record across all transaction types, preserving the polymorphic relation (`transactionable_id`, `transactionable_type`) and the canonical identifier (`refid`).

### Scope (Covered Transaction Types)

The backfill covers all core parent transaction tables that transaction lines reference:

- Sales Orders (table: `salesorders`, model: `Domain\SalesOrders\Models\SalesOrder`)
- Purchase Orders (table: `purchaseorders`, model: `Domain\PurchaseOrders\Models\PurchaseOrder`)
- Estimates (table: `estimates`, model: `Domain\Estimates\Models\Estimate`)
- Opportunities (table: `opportunities`, model: `Domain\Opportunities\Models\Opportunity`)
- Invoices (table: `invoices`, model: `Domain\Invoices\Models\Invoice`)
- Item Fulfillments (table: `itemfulfillments`, model: `Domain\ItemFulfillments\Models\ItemFulfillment`)
- Item Receipts (table: `itemreceipts`, model: `Domain\ItemReceipts\Models\ItemReceipt`)
- Work Orders (table: `workorders`, model: `Domain\WorkOrders\Models\WorkOrder`)

Notes:
- Use the concrete model FQCNs used by the application.
- If any table is not present for a tenant, skip gracefully.

### Fit in Current Flow

1) Parent transactions imported first via waves.
2) Transaction lines imported in derived waves.
3) After line import completes, run a single job to backfill the `transactions` table for all parent rows created during the job (or for all missing rows when scoping is unavailable).

### Inputs

- job_id: current import job identifier
- parentIds per record type (preferred): cache key pattern `parent_ids_{jobId}_{recordTypeId}` already used for TransactionLine batching
- Fallback: if parent IDs not available, operate on parents with no corresponding `transactions` record

### Backfill Strategy

Perform one `INSERT … SELECT` per table to create missing `transactions` rows using a LEFT JOIN against `transactions` to detect absence. The insert will populate:

- `refid` = parent.refid (canonical NetSuite ID)
- `transactionable_id` = parent.id
- `transactionable_type` = model FQCN (e.g., `Domain\SalesOrders\Models\SalesOrder`)
- Timestamps (`created_at`, `updated_at`) = NOW() or database function as appropriate

Idempotent behavior: inserts only where a row does not already exist.

### Example Patterns (per table)

Use the version that scopes by `:parentIds` when available; otherwise use the unscoped version.

#### Sales Orders

Scoped version:
```sql
INSERT INTO transactions (refid, transactionable_id, transactionable_type, created_at, updated_at)
SELECT so.refid, so.id, 'Domain\SalesOrders\Models\SalesOrder', NOW(), NOW()
FROM salesorders so
LEFT JOIN transactions t
  ON t.transactionable_type = 'Domain\SalesOrders\Models\SalesOrder'
 AND t.transactionable_id = so.id
WHERE t.id IS NULL
  AND so.id IN (:parentIds);
```

Unscoped version (fallback):
```sql
INSERT INTO transactions (refid, transactionable_id, transactionable_type, created_at, updated_at)
SELECT so.refid, so.id, 'Domain\SalesOrders\Models\SalesOrder', NOW(), NOW()
FROM salesorders so
LEFT JOIN transactions t
  ON t.transactionable_type = 'Domain\SalesOrders\Models\SalesOrder'
 AND t.transactionable_id = so.id
WHERE t.id IS NULL;
```

#### Purchase Orders
```sql
INSERT INTO transactions (refid, transactionable_id, transactionable_type, created_at, updated_at)
SELECT po.refid, po.id, 'Domain\PurchaseOrders\Models\PurchaseOrder', NOW(), NOW()
FROM purchaseorders po
LEFT JOIN transactions t
  ON t.transactionable_type = 'Domain\PurchaseOrders\Models\PurchaseOrder'
 AND t.transactionable_id = po.id
WHERE t.id IS NULL
  AND po.id IN (:parentIds);
```

#### Estimates
```sql
INSERT INTO transactions (refid, transactionable_id, transactionable_type, created_at, updated_at)
SELECT e.refid, e.id, 'Domain\Estimates\Models\Estimate', NOW(), NOW()
FROM estimates e
LEFT JOIN transactions t
  ON t.transactionable_type = 'Domain\Estimates\Models\Estimate'
 AND t.transactionable_id = e.id
WHERE t.id IS NULL
  AND e.id IN (:parentIds);
```

#### Opportunities
```sql
INSERT INTO transactions (refid, transactionable_id, transactionable_type, created_at, updated_at)
SELECT o.refid, o.id, 'Domain\Opportunities\Models\Opportunity', NOW(), NOW()
FROM opportunities o
LEFT JOIN transactions t
  ON t.transactionable_type = 'Domain\Opportunities\Models\Opportunity'
 AND t.transactionable_id = o.id
WHERE t.id IS NULL
  AND o.id IN (:parentIds);
```

#### Invoices
```sql
INSERT INTO transactions (refid, transactionable_id, transactionable_type, created_at, updated_at)
SELECT i.refid, i.id, 'Domain\Invoices\Models\Invoice', NOW(), NOW()
FROM invoices i
LEFT JOIN transactions t
  ON t.transactionable_type = 'Domain\Invoices\Models\Invoice'
 AND t.transactionable_id = i.id
WHERE t.id IS NULL
  AND i.id IN (:parentIds);
```

#### Item Fulfillments
```sql
INSERT INTO transactions (refid, transactionable_id, transactionable_type, created_at, updated_at)
SELECT f.refid, f.id, 'Domain\ItemFulfillments\Models\ItemFulfillment', NOW(), NOW()
FROM itemfulfillments f
LEFT JOIN transactions t
  ON t.transactionable_type = 'Domain\ItemFulfillments\Models\ItemFulfillment'
 AND t.transactionable_id = f.id
WHERE t.id IS NULL
  AND f.id IN (:parentIds);
```

#### Item Receipts
```sql
INSERT INTO transactions (refid, transactionable_id, transactionable_type, created_at, updated_at)
SELECT r.refid, r.id, 'Domain\ItemReceipts\Models\ItemReceipt', NOW(), NOW()
FROM itemreceipts r
LEFT JOIN transactions t
  ON t.transactionable_type = 'Domain\ItemReceipts\Models\ItemReceipt'
 AND t.transactionable_id = r.id
WHERE t.id IS NULL
  AND r.id IN (:parentIds);
```

#### Work Orders
```sql
INSERT INTO transactions (refid, transactionable_id, transactionable_type, created_at, updated_at)
SELECT w.refid, w.id, 'Domain\WorkOrders\Models\WorkOrder', NOW(), NOW()
FROM workorders w
LEFT JOIN transactions t
  ON t.transactionable_type = 'Domain\WorkOrders\Models\WorkOrder'
 AND t.transactionable_id = w.id
WHERE t.id IS NULL
  AND w.id IN (:parentIds);
```

### Orchestration Job

Create `BackfillTransactionsTableJob` with inputs `{ job_id }`. Flow:

1) Resolve parent record types processed for the job (and their model FQCNs/table names).
2) Load `parentIds` for each record type from cache where available.
3) For each parent table, execute the appropriate `INSERT … SELECT` statement:
   - Use the scoped version with `:parentIds` if provided, else the unscoped fallback.
4) Log metrics per table: inserted rows, already-present count (derived by difference), and runtime.

Trigger this job after the TransactionLine wave is complete for the job (same timing as subsidiary backfill). Both jobs can run sequentially or combined under a coordinator if desired.

#### Orchestration Integration & References

- Orchestration trigger points (existing):
  - `src/App/Services/ImportJobs/WaveCoordinator.php`
    - **Primary dispatch location**: `WaveCoordinator::checkAndTriggerNextWave()` determines when all derived (TransactionLine) waves are complete and dispatches post-completion jobs.
    - Add `BackfillTransactionsTableJob` dispatch alongside the existing `UpdateTransactionSubsidiariesJob` dispatch in the coordinator's derived-wave-completion logic (lines ~2176 and ~2202).
    - Wave lifecycle enforcement (`checkWaveCompletion()`, `checkAndDispatchDerivedWaves()`) confirms all line batches are done.
  - `src/App/Jobs/ImportJobs/ImportNetSuiteRecordsBatch.php`
    - Context of batch execution and record-type metadata.
  - `config/waves.php`
    - Coordination thresholds for wave completion.

- Recommended placement:
  - In `WaveCoordinator`, add a new `dispatchTransactionsBackfill(string $jobId)` method (mirroring `dispatchSubsidiaryBackfill`).
  - Call this method at the same two points where `dispatchSubsidiaryBackfill` is invoked:
    1. When all main and derived waves complete successfully (after line 2176).
    2. When marking the job as complete with derived waves present (after line 2202).
  - Use cache flag `transactions_backfill_dispatched_{jobId}` for idempotency.
  - Both backfills run independently (not chained) and are dispatched from the coordinator when derived waves complete.

- Related design docs:
  - Subsidiary backfill (companion): `/docs/designs/backfill-transaction-subsidiary.md`
  - Parent-first/wave fixes: `/docs/design/import-fixes-sonnet.md`

- Implementation notes:
  - **Do not** dispatch from `BatchJobCompletedListener`; batch events stop firing after the last batch completes, so gating logic in the listener never passes.
  - The coordinator is the correct dispatch point because it actively monitors wave completion and triggers actions when all derived waves finish.

### Idempotency & Safety

- Uses LEFT JOIN to create only missing rows; safe to re-run.
- Scoping by `:parentIds` limits work to the current job; fallback remains correct but may touch more rows.
- Wrap statements per table; failures in one table should not block others (best-effort backfill).

### Indexes & Performance

- Ensure supporting indexes:
  - `transactions(transactionable_type, transactionable_id)` (composite)
  - Parent tables on `id` (PK)
- Chunk `:parentIds` for very large jobs.
- One batched `INSERT … SELECT` per table minimizes round trips.

### Monitoring & Logging

For each table, log:
- `rows_inserted`
- `rows_skipped` (estimated where applicable)
- runtime (ms)

Emit an overall summary for the job after all tables are processed.

### Validation Compatibility

- This backfill inserts only into `transactions` and does not conflict with per-table upsert validation (no parent record fields are modified).
- Polymorphic linkage is consistent with existing `booted` event behavior (`transactionable_type`, `transactionable_id`, `refid`).

### Acceptance Criteria

- Every parent transaction row across covered tables has a corresponding `transactions` row post-backfill.
- Job is idempotent and safe to run after any bulk upsert sequence.
- Operates efficiently (single insert-select per table, chunked by parentIds where available).
- Works with or without cached parent IDs.