# Database Scope

## Central DB — No New Migrations

CoreApp + CrmApp app/module/feature rows are **seeded**, not migrated.
Existing central tables reused as-is: `app_managements`, `module_managements`,
`feature_managements`, `packages`, `package_app`, `package_module`,
`package_feature`, `subscriptions`.

## Tenant DB — 16 New Tables

| Engine / Module | Tables |
|-----------------|--------|
| CoreApp/FieldEngine | `custom_fields`, `custom_field_values` |
| CoreApp/PipelineEngine | `pipelines`, `pipeline_stages`, `pipeline_stage_transitions` |
| CoreApp/ActivityEngine | `activity_logs` |
| CoreApp/WorkflowEngine | `workflow_rules`, `workflow_runs` |
| CoreApp/EntityEngine | `entity_types`, `entity_relationships` |
| CoreApp/LabelEngine | `entity_domain_maps` |
| CrmApp/Lead | `leads` |
| CrmApp/Deal | `deals` |
| CrmApp/Proposal scaffold | `proposal_templates`, `proposals`, `proposal_approvals` |
| CrmApp/Contact | *(no new tables — CRM view over existing `contacts`)* |
| CrmApp/Pipeline | *(no tables — reads PipelineEngine)* |
| CrmApp/Activity | *(no tables — reads ActivityEngine + Productivity)* |

## Migration File Order (CoreApp)

All live in `CoreApp/database/migrations/`. Prefix timestamps so they run in order:

```
000001_create_custom_fields_table
000002_create_custom_field_values_table
000003_create_pipelines_table
000004_create_pipeline_stages_table
000005_create_pipeline_stage_transitions_table
000006_create_activity_logs_table
000007_create_workflow_rules_table
000008_create_workflow_runs_table
000009_create_entity_types_table
000010_create_entity_relationships_table
000011_create_entity_domain_maps_table
000012_create_proposal_templates_table
000013_create_proposals_table
000014_create_proposal_approvals_table
```

Then CrmApp migrations:
```
000015_create_leads_table     (CrmApp/Lead/database/migrations/)
000016_create_deals_table     (CrmApp/Deal/database/migrations/)
```

## FK Cascade Behavior

| Column | Table | Behavior | Reason |
|--------|-------|----------|--------|
| `stage_id` | leads, deals | `nullOnDelete` | Stage soft-deleted → record becomes stage-less, not deleted |
| `pipeline_id` | leads, deals | `restrict` | Cannot delete pipeline with active records |
| `owner_id` | leads, deals | `nullOnDelete` | User leaves → unassigned lead/deal, re-assignable |
| `branch_id` | leads, deals | `nullOnDelete` | Branch deleted → record becomes branch-less |
| `contact_id` | deals | `nullOnDelete` | Contact deleted → deal preserved without contact link |
| `custom_field_id` | custom_field_values | `cascadeOnDelete` | Field removed → all values removed (is_system fields are never deleted) |
| `workflow_rule_id` | workflow_runs | `cascadeOnDelete` | Rule deleted → run history deleted too |

> **Soft-deletes on `pipeline_stages`:** Required because `stage_id` on leads/deals uses `nullOnDelete`. A hard delete of a stage with live leads would set `stage_id = null` on all those leads silently. Add `SoftDeletes` to `PipelineStage` model.

## Observability Columns

`activity_logs` must carry a `workflow_run_id` nullable FK to trace "which rule caused this activity entry":

```php
// Add to 000006_create_activity_logs_table migration:
$table->foreignId('workflow_run_id')->nullable()->constrained('workflow_runs')->nullOnDelete();
$table->index('workflow_run_id');
```

`ActivityService::log()` accepts an optional `workflowRunId` parameter. `AssignToUser` and other actions pass the current `$run->id`.

## Hybrid Storage (Searchable Custom Fields)

Filterable custom fields **also exist as real columns** in the model migration.
`is_searchable: true` in pack JSON is documentation only — no schema changes at seed time.

| Table | Real searchable columns |
|-------|------------------------|
| `leads` | `preferred_country`, `preferred_study_level`, `score` |
| `deals` | `country` |

**Rule:** `LeadFilter` and `DealFilter` NEVER join `custom_field_values` for these fields.
They filter on `leads.preferred_country`, `deals.country`, etc. directly.

## Seeder Scope

| Seeder | DB | Trigger |
|--------|----|---------|
| `FeatureManagement/CoreApp.php` | Central | `db:seed` on admin |
| `FeatureManagement/CrmApp.php` | Central | `db:seed` on admin |
| `CrmEntityTypeSeeder` | Tenant | `setup:crm` |
| `FeaturePackSeeder` | Tenant | `setup:crm` or `ProductProfileSeeder` hook |

Both tenant seeders run **synchronously inside tenant context** — not queued.
Never called directly from migrations. Always called from `setup:crm` command.