## Domain Rules

### Database Connections
- Core database (`mysql`) is for job queue management only.
- Tenant database (`tenant_connection`) is for all tenant-specific data.
- Models must explicitly declare their connection.
- Cross-database relationships must use `setConnection()`.
- Tenant models must use `UsesTenantConnection` trait.
- Jobs must call `setupTenantConnection()` before tenant operations.
- Never connect to real databases in tests — use testing/tenant connections.
- Testing database must use SQLite in-memory for speed.
- In tests, use a temporary SQLite file instead of `:memory:` for better isolation.
- Always purge and reconnect DB connections in test setup.
- Create all required tables in test setup using `Schema::create`.
- Clean up temporary database files in test teardown.
- Configure both `mysql` and `tenant_connection` to use the same test database.
- Always create fresh database connections in `beforeEach`.
- Use unique temporary file paths for each test suite.
- Verify database cleanup in `afterEach`.
- Handle database connection errors explicitly in tests.
- Test database reconnection scenarios.

### Database Schema Management
- All tenant-specific tables must be created via migrations in `database/migrations/tenants/`
- Core system tables must be created via migrations in `database/migrations/`
- When creating new migrations, always consider data isolation requirements
- Tenant migrations must include proper foreign key constraints within tenant scope
- Never create cross-database foreign keys between core and tenant databases
- Tenant isolation is enforced at the connection level (`tenant_connection`), NOT via a `tenant_id` column. Never add `tenant_id` to tenant tables — it is both redundant and misleading. See `100-backend-laravel.md §Database & Models`.
- Use appropriate connection in migrations based on target database
- Ensure migrations are reversible with proper down() methods
- Document any data dependencies between migrations in comments
- Follow Laravel's timestamp naming convention for migration files
- Group related schema changes in a single migration when appropriate
- Include descriptive comments for complex schema changes
- Test migrations in isolation before committing

### SQL Query Safety Rules - Column Qualification

**CRITICAL: Prevent SQL Ambiguity Errors**

When writing database queries, especially with JOINs or self-referential tables, follow these mandatory rules:

#### Self-Referential Tables
Any table that joins against itself (e.g., parent-child hierarchies, adjacency lists, closure tables) requires special attention. In a self-join, every column name in `WHERE`, `ORDER BY`, `SELECT`, and `JOIN ON` clauses is ambiguous to the database engine unless explicitly table-qualified.

#### Mandatory Column Qualification Rules

**1. Always Qualify Columns in Self-Join Queries**
```php
// ❌ BAD - Ambiguous columns cause SQL errors
$query->where('isparent', true)
      ->whereNull('parent')
      ->orderBy('eventid', 'asc');

// ✅ GOOD - Explicitly qualified columns
$query->where('projecttasks.isparent', true)
      ->whereNull('projecttasks.parent')
      ->orderBy('projecttasks.eventid', 'asc');
```

**2. Qualify When Using leftJoin on Same Table**
```php
// ❌ BAD - Self-join without qualification
$query->leftJoin('projecttasks as ref_parent', 'parent', '=', 'ref_parent.refid')
      ->where('isparent', true);  // AMBIGUOUS!

// ✅ GOOD - Qualified in all clauses
$query->leftJoin('projecttasks as ref_parent', 'projecttasks.parent', '=', 'ref_parent.refid')
      ->where('projecttasks.isparent', true);
```

**3. Qualify in ALL Query Clauses**
Must qualify in:
- WHERE clauses: `where('table.column', value)`
- ORDER BY clauses: `orderBy('table.column')`
- SELECT clauses: `select('table.column')`
- JOIN conditions: `on('table1.column', '=', 'table2.column')`
- HAVING clauses: `having('table.column', value)`

**4. Qualify Even Without Explicit JOINs**
When queries might be extended with JOINs later, proactively qualify:
```php
// ✅ DEFENSIVE - Safe if JOIN added later
$query->select('projecttasks.id', 'projecttasks.title')
      ->where('projecttasks.project', $projectId)
      ->orderBy('projecttasks.eventid', 'asc');
```

#### Field Metadata Null-Safety Rules

When accessing field metadata (especially in helpers), always use null-safe operators:

```php
// ❌ BAD - Assumes content_type always exists
$contentType = $field['content_type'];
if ($contentType === 'record') { ... }

// ✅ GOOD - Null-safe with fallback
$contentType = $field['content_type'] ?? null;
$scriptId = $field['scriptid'] ?? null;
$references = $field['references'] ?? null;

// Only proceed if all required fields exist
if ($contentType === 'record' && $scriptId && $references) {
    // Safe to use
}
```

#### Detection During Code Review

**Red Flags to Watch For:**
1. Query builder methods without table prefix: `where('isparent', ...)`
2. Self-referential table queries without qualification
3. JOIN operations without checking column qualification
4. Field metadata access without null checks: `$field['content_type']`

**PR Review Checklist:**
- [ ] All columns qualified when JOINs present?
- [ ] Self-referential tables use fully qualified columns?
- [ ] Field metadata accessed with null-safety?
- [ ] Tests verify query structure for ambiguity?

#### Testing Requirements

When modifying query-building code:
1. **Add unit tests** verifying SQL structure contains qualified columns
2. **Add integration tests** executing queries against test database
3. **Verify PHPStan** reports no new errors in modified files

**Example Test Pattern:**
```php
test('query qualifies columns when self-join present', function() {
    $query = DB::table('projecttasks')
        ->leftJoin('projecttasks as ref_parent', 'projecttasks.parent', '=', 'ref_parent.refid')
        ->where('projecttasks.isparent', true);

    $sql = $query->toSql();

    // Verify qualification (SQLite uses quotes)
    expect($sql)->toContain('"projecttasks"."isparent"');
});
```

#### Verification During Code Review

**Red flags that indicate a missing qualification:**
- A `where()`, `orderBy()`, or `select()` clause that references a column by name alone on a query that includes any `JOIN`
- A self-join query (`->leftJoin('table as alias', ...)`) without full qualification of all filter/sort columns
- Any new JOIN added to an existing query that did not previously have qualified columns — all existing clauses must be retrofitted

### Import Jobs Domain
- Must use `BatchJobProcessor` for chunked record processing.
- Must use `ImportJobCoordinator` for orchestration.
- Must implement dependency resolution via `DependencyResolver`.
- Batch size is fixed at 1000 records per API limitations.
- Each batch gets fresh database connections.
- Connection health checks every 200 records.
- 30-second delay between batch starts to prevent overload.
- Master jobs timeout after 1 hour.
- Batch jobs timeout after 5 minutes.
- Maximum 3 retry attempts for failed batches.
- Must handle external API rate limits appropriately.
- Must use `JobStatusTrackingService` for progress.
- Must use `JobCancellationService` for cancellation.
- Must emit appropriate domain events for state changes.
- Must store all import-related data in tenant database.
- Must follow defined record type dependency patterns.
- Protected methods should remain protected — use test doubles for testing.
- Jobs must validate all dependencies before processing.
- Jobs must handle API response validation explicitly.
- Jobs must properly propagate and log API errors.
- Test retry logic with exponential backoff.
- Test job cancellation at different stages.
- Test progress tracking across batches.
- Test dependency resolution with real record types.
- Progress calculations must be bounded and type-consistent.
- State transitions must be explicitly tracked and verified.
- Event emissions must occur at appropriate state change points.
- Cancellation must properly clean up all related resources.

### Service Validation Process Rules

**CRITICAL: Schema-First Validation is Mandatory**

**Never Skip Steps in Documented Processes**
- The AI agent must NEVER skip steps in any documented or proven process
- Every step in an established workflow must be completed in sequence
- Deviating from proven processes without explicit user authorization is prohibited
- If a process seems inefficient, discuss alternatives with the user before deviating

**Schema-First Validation Plan - Non-Negotiable**
- The AI agent must NOT deviate from the schema-first validation approach
- Database schema inspection is the mandatory first step for all service validation work
- Schema analysis must be completed before making any validation rule changes
- The schema serves as the single source of truth for field definitions, constraints, and relationships

**Historical Context - Learning from Past Issues**
Recent incidents occurred where the AI agent deviated from the schema-first approach during service validation updates. Instead of reviewing database schema first, the agent copied validation rules and field mappings directly from other service files. This approach caused:
- Multiple validation errors due to incorrect field constraints
- Inconsistent field mappings across services
- Significant time waste debugging schema mismatches
- Breaking changes to existing functionality

**Mandatory Service Validation Workflow**
For ALL future service validation work, the AI agent MUST:

1. **Always pull and review the database schema first**
   - Examine table structure, column definitions, constraints, and relationships
   - Identify required fields, nullable fields, data types, and length limits
   - Review foreign key relationships and cascading rules
   - Document any unique constraints or business logic constraints

2. **Use the schema as the single source of truth**
   - All validation rules must align with actual database constraints
   - Field mappings must match actual column names and types
   - Required/optional field logic must reflect database nullability
   - Data transformation rules must respect database data types

3. **Reference other service files for patterns only**
   - Other services may be consulted for coding patterns and structure
   - NEVER copy field mappings, validation rules, or constraints from other services
   - Use other services as examples of implementation style, not data definitions
   - Always validate that patterns from other services are appropriate for the target schema

**Prohibited Actions**
- Copying validation rules from other services without schema verification
- Assuming field names, types, or constraints based on other service implementations
- Skipping schema review in favor of "similar" service examples
- Making validation changes without understanding the underlying database structure

**Validation Process Checkpoints**
Before implementing any service validation changes:
- [ ] Database schema has been reviewed and documented
- [ ] All field constraints have been identified from schema
- [ ] Validation rules align with actual database constraints
- [ ] No assumptions have been made based on other service files
- [ ] Schema-first approach has been maintained throughout

**Rule: Collaborative & Critical Code Partner**

- Do not be sycophantic. Avoid uncritical agreement or flattery.
- Treat coding sessions as a collaboration: ask clarifying questions when requirements are unclear.
- Actively challenge proposed code design ideas by considering trade-offs, alternatives, or potential pitfalls.
- Always present at least one viable alternative if trade-offs exist.
- If a proposed idea is the best option, acknowledge it explicitly and briefly explain why it works well.
- Provide reasoning grounded in performance, maintainability, scalability, or readability.
- Admit uncertainty when appropriate, and suggest ways to test or validate assumptions.

---