# Database Layer

## Environment

- **Production:** MySQL 8.0+
- **Development / tests:** SQLite (in-memory for tests)
- Migrations MUST work on both — avoid MySQL-only syntax.

## Core Domain ER Diagram

```mermaid
erDiagram
    sites {
        bigint id PK
        bigint user_id FK
        string name
        string domain
        timestamp deleted_at "soft delete"
    }
    analysis_runs {
        bigint id PK
        bigint site_id FK
        string status
        timestamp before_start
        timestamp before_end
        timestamp after_start
        timestamp after_end
        json summary
    }
    findings {
        bigint id PK
        bigint analysis_run_id FK
        string dimension_type
        float delta_absolute
        float delta_percentage
        json supporting_data
    }
    recommendations {
        bigint id PK
        bigint analysis_run_id FK
        bigint finding_id FK
        string action_type
        string lifecycle_status
        float impact_score
        text page_url
    }
    ai_drafts {
        bigint id PK
        bigint ai_job_id FK
        bigint recommendation_id FK
        longtext content
        json token_usage
    }

    sites ||--o{ analysis_runs : "has many"
    analysis_runs ||--o{ findings : "has many"
    findings ||--o{ recommendations : "has many"
    recommendations ||--o| ai_drafts : "may have"
```

## Schema Topology

```
users ─── sites (has_many, soft deletes)
           ├── gsc_connections (has_one) ─── gsc_{daily,page,query}_metrics
           ├── wp_connections (has_one) ──── wp_posts
           ├── analysis_runs ── findings ── recommendations ── ai_drafts ── wp_publish_logs
           │                                         └── recommendation_roi_snapshots
           ├── *_runs (cannibalization, page_decay, topic_cluster, keyword_opportunity)
           ├── site_{ai,analysis}_settings, site_analysis_schedules
           ├── content_briefs, content_snapshots, seo_calendar_entries
           ├── traffic_alerts, serp_snapshots
           ├── opportunity_feedback (polymorphic)
           └── site_members (team permissions)
users ─── user_ai_keys (encrypted), user_serp_api_keys (encrypted), user_brandings
users ─── ai_templates (has_many; user_id nullable for system templates)
users ─── reports ── shared_report_links
sites ─── limit_policies (polymorphic); users ─── limit_policies (polymorphic)
blog_posts (standalone marketing content; soft deletes; no user/site FK)
```

To get the current full table list: `php artisan db:show` or `ls database/migrations/`.

## Encrypted Columns (Laravel `encrypted` cast)

| Table | Column(s) | Contents |
|-------|-----------|----------|
| `gsc_connections` | `access_token`, `refresh_token` | Google OAuth tokens |
| `wp_connections` | `shared_secret`, `pending_secret` | HMAC-SHA256 secret (active + rotation pending) |
| `user_ai_keys` | `api_key` | OpenAI API key (BYOK) |
| `user_serp_api_keys` | `api_key` | DataForSEO API key (BYOK) |

**Rule:** any column holding a credential, OAuth token, or HMAC secret MUST use the `encrypted` cast and be excluded from `$fillable` mass assignment from user input.

## Enum Columns

Enum classes live in `app/Enums/` and are cast on the model. Common enums:

| Enum | Used By | Values |
|------|---------|--------|
| `JobStatus` | `analysis_runs.status`, `ai_jobs.status`, `*_runs.status` | pending, processing, completed, failed, cancelled |
| `SyncStatus` | `gsc_connections.sync_status` | pending, syncing, synced, failed |
| `LifecycleStatus` | `recommendations.lifecycle_status` | pending, reviewed, approved, rejected, deferred, applied, tracking |
| `ActionType` | `recommendations.action_type` | noindex, content_rewrite, meta_tag_optimization, internal_linking, thin_content, cannibalization_consolidation, light_refresh, section_refresh, full_rewrite, reposition_intent, eeat_improvement, geo_improvement |
| `DimensionType` | `findings.dimension_type` | overall, country, device, search_type |
| `DecayPatternType` | `page_decay_signals.decay_pattern` | gradual_decay, step_drop, post_update_decline, query_mix_erosion, position_drift, seasonal_normalized_decay, volatility |
| `KeywordOpportunityType` | `keyword_opportunities.opportunity_type` | striking_distance, ctr_gap, rising_query, content_gap |
| `TopicGapSuggestionType` | `topic_gap_suggestions.suggestion_type` | expand_hub, create_page, add_section, add_faq |
| `SiteMemberRole` | `site_members.role` | viewer, editor, admin |

To enumerate all enums: `ls app/Enums/`.

## Audit Logs (post-consolidation)

The `audit_logs` table was consolidated by migration `2026_03_03_020000_consolidate_audit_logs_legacy_columns`. Current truth:

- **Live columns:** `action`, `site_id`, `context` (JSON), `ip_address`, `user_id`, `user_agent`.
- **Dropped:** `ip`, `metadata`.
- **Deprecated, nullable, kept for retention:** `event` (will be dropped in a future migration).

`AuditService::logAction()` writes to `action` / `site_id` / `context` / `ip_address` only. **Do not** read or write `ip`, `metadata`, or use `$row->action ?? $row->event` fallbacks — those are dead columns.

## Limit Policies (cascade)

Limits resolve in this order, first match wins:

1. Site-scoped override (`scope_type = 'site'`, `scope_id = site.id`)
2. User-scoped override (`scope_type = 'user'`, `scope_id = user.id`)
3. Global override (`scope_type = 'global'`, `scope_id = NULL`)
4. Config default in `config/limits.php`

`LimitService::current($key, $user, $site)` counts actual DB records — there are no counter columns. `LimitCacheService` caches counts; observers invalidate on relevant model changes.

## Migration Rules (invariants)

1. **New columns on existing tables:** always `nullable()` or `->default()`. Never bare `NOT NULL`.
2. **Foreign keys:** `->constrained()->cascadeOnDelete()->index()`.
3. **Column guards:** wrap add/drop in `Schema::hasColumn()` checks for idempotency.
4. **Two-phase deploys for destructive changes:** ship code that stops using the column first, drop the column in a later deploy.
5. **Soft deletes are the exception, not the default.** Models using soft deletes: `Site`, `User`, `BlogPost`, `WebhookEndpoint`, `ChangelogEntry`. Everything else hard-deletes.
6. **Composite indexes** for common query patterns (e.g., `(site_id, created_at)`, `(site_id, status)`).
7. **Test on both engines.** SQLite in-memory in CI catches MySQL-only syntax.
8. **Encrypted columns** never participate in WHERE/ORDER BY (the value is ciphertext at rest).

### Migration Templates

```php
// Add nullable column to existing table
Schema::table('table_name', function (Blueprint $table) {
    if (! Schema::hasColumn('table_name', 'new_column')) {
        $table->string('new_column')->nullable()->after('existing_column');
    }
});

// New table with FK
Schema::create('child_records', function (Blueprint $table) {
    $table->id();
    $table->foreignId('parent_id')->constrained()->cascadeOnDelete()->index();
    $table->string('name');
    $table->timestamps();
});

// Drop column (only after the code stopped using it in a previous deploy)
Schema::table('table_name', function (Blueprint $table) {
    if (Schema::hasColumn('table_name', 'old_column')) {
        $table->dropColumn('old_column');
    }
});
```

## SQLite Gotchas

| Gotcha | Workaround |
|--------|------------|
| `Model::where(...)->delete()` does NOT cascade FKs | Delete leaf records explicitly before the parent |
| `mergeBindings` on raw subquery joins corrupts binding order | Use PHP in-memory composition or single-pass CASE WHEN aggregation |
| `->jsonb()` not supported (use `->json()`) | Use `->json()` — works on both engines |
| MySQL-only types (POINT, ENUM, SET, etc.) | Avoid — use `string` + enum class cast for enums |
| Tests use in-memory SQLite | Always test migrations against both before shipping |

## Factories

- **Every model has a factory.** Always use factories in tests, never raw `Model::create()`.
- Named states live on the factory class itself — read the factory file for the current state list:
  - `database/factories/UserFactory.php` — `unverified()`, `admin()`, `withTwoFactor()`, `onboardingIncomplete()`
  - `database/factories/<ModelName>Factory.php` — check directly
- **`UserFactory::configure()` auto-marks onboarding complete on create** when both `features.onboarding.enabled` and `features.user_settings.enabled` are true. Use `->onboardingIncomplete()` to opt out.
- **`Recommendation::factory()->dismissed()` sets the legacy `status` field**, NOT `lifecycle_status`. To set lifecycle status: `Recommendation::factory()->create(['lifecycle_status' => LifecycleStatus::Rejected])`.
- **Factory FK drift:** factories for `TrafficAlert`, `AiDraft`, etc. auto-create their own `Site` / `AnalysisRun`. In site-scoped tests, always pass `site_id` (and `analysis_run_id`) explicitly — otherwise the records attach to a different site and assertions return 0.

To enumerate available factories: `ls database/factories/`.
To enumerate states on a factory: `grep -E '^\s+public function \w+\(\)' database/factories/<Name>Factory.php`.

## Seeders

| Seeder | Purpose |
|--------|---------|
| `DatabaseSeeder` | Creates `test@example.com` user only (default for `migrate:fresh --seed`) |
| `AdminDemoSeeder` | Demo admin user + sample data |
| `DefaultAiTemplatesSeeder` | Idempotent — seeds 5 system AI prompt templates (skips if system templates exist) |

Run an individual seeder: `php artisan db:seed --class=SeederName`.

## Commands

```bash
php artisan make:migration create_table_name_table
php artisan make:migration add_column_to_table_name_table
php artisan migrate
php artisan migrate:rollback
php artisan migrate:fresh --seed     # dev only — tests use in-memory SQLite
php artisan db:show                  # current schema overview
php artisan db:show --counts         # row counts per table
```