# Database Schema (MongoDB)

**Status:** Core models implemented (Task 004). Registry: `src/server/models/index.ts`. Enums: `src/lib/enums.ts`.

MongoDB database name suggestion: `anwal_growth` (via `MONGODB_URI`).

## Design principles

- **Flexible imports:** MongoDB documents support both **simple** imports (raw columns) and **processed GPT** imports (enriched fields, `semanticNotes`, `service_matches`, confidence scores) without rigid relational migrations.
- **`sourceFile` / `Source` / `ImportLog`:** Trace every record back to an Excel/file and import run.
- **`importType`:** `simple` | `processed` on `sources` and `import_logs`.
- **Secrets:** Default SMTP/IMAP env vars remain the platform fallback. Per-mailbox SMTP/IMAP passwords are stored encrypted (`smtpPasswordEncrypted`, `imapPasswordEncrypted`) using `NEXTAUTH_SECRET`. AI API keys stay in env only.
- **Mixed types:** `filters`, `metadata`, `rawOutput`, `payload` use `Schema.Types.Mixed` for evolving shapes.

## Import system usage (Task 005)

- Every import creates a **`sources`** row (`importType`: `simple` | `processed`) and an **`import_logs`** row with counters and `errorMessages`.
- Processed imports resolve `entity_id` / `person_id` from Excel to MongoDB `_id` maps during a single run.
- Simple imports use user column mapping JSON (see [18-import-system.md](./18-import-system.md)).
- APIs: `POST /api/imports/detect|preview|execute`, `GET /api/imports/history`.

## Import flows (model-level)

| Flow | Models involved |
|------|-----------------|
| Simple import | `Source` → `ImportLog` → `Company`, `Person`, `ContactPoint` |
| Processed GPT import | Above + `ServiceMatch`, higher `dataConfidence` usage. `DataQualityIssue` / `DuplicateReview` models kept for legacy DB rows; **no longer created on import** (GPT preprocessing). |
| Post-import CRM | `Lead`, `Opportunity`, `Activity` |
| Campaigns | `Segment` → `Campaign` → `EmailMessage` → `EmailReply` → `Lead` |
| AI | `AiClassification` on replies/leads/companies |
| Automation | `AutomationLog` for n8n/system/email/ai events |

## Entity relationship overview

```mermaid
erDiagram
  User ||--o{ ImportLog : creates
  Source ||--o{ ImportLog : tracks
  Source ||--o{ DataQualityIssue : has
  ImportLog ||--o{ DataQualityIssue : has
  Company ||--o{ Person : employs
  Company ||--o{ ContactPoint : has
  Person ||--o{ ContactPoint : has
  Company ||--o{ ServiceMatch : matches
  Company ||--o{ Lead : generates
  Person ||--o{ Lead : generates
  Lead ||--o| Opportunity : converts
  Segment ||--o{ Campaign : targets
  EmailTemplate ||--o{ Campaign : uses
  Campaign ||--o{ EmailMessage : sends
  EmailMessage ||--o| EmailReply : receives
  EmailReply ||--o| AiClassification : classified
  EmailReply ||--o| Lead : may_create
  Company ||--o{ Activity : logs
  Lead ||--o{ Activity : logs
  Campaign ||--o{ AutomationLog : triggers
```

---

## Collections

### `users` (Task 003)

Model: `user.model.ts` — unchanged in Task 004.

| Field | Type | Notes |
|-------|------|-------|
| name, email, passwordHash, role, status, lastLoginAt | — | See Task 003 |
| role | enum | `admin`, `manager`, `sales`, `viewer` |

---

### `companies`

Model: `company.model.ts`

| Field | Type | Notes |
|-------|------|-------|
| nameAr | string | Required |
| nameEn | string | Optional |
| entityType | enum | company, factory, government, engineering_office, nonprofit, education, healthcare, supplier, unknown |
| sector, subSector, activity, city, region | string | |
| country | string | Default `Saudi Arabia` |
| website, linkedinUrl, address, description | string | |
| semanticNotes | string | GPT/processed import notes |
| tags | string[] | |
| sourceFile | string | Origin file |
| dataConfidence | enum | high, medium, low, unknown |
| leadScore | number | Default 0 |
| priority | enum | very_high … unknown |
| status | enum | new, active, archived, blacklisted |

**Indexes:** text nameAr/nameEn; sector, city, entityType, leadScore, priority; website sparse

---

### `people`

Model: `person.model.ts`

| Field | Type | Notes |
|-------|------|-------|
| companyId | ObjectId | ref Company |
| companyNameAr | string | Denormalized for imports |
| fullName | string | Required |
| jobTitle, department | string | |
| seniorityLevel | enum | owner, ceo, executive, manager, specialist, admin, unknown |
| isDecisionMaker | enum | yes, no, maybe, unknown |
| decisionMakerReason | string | |
| email, mobile, phone, linkedinUrl | string | |
| sourceFile, dataConfidence, notes | | |

**Indexes:** text fullName; companyId; email/mobile sparse; seniorityLevel; isDecisionMaker

---

### `contact_points`

Model: `contact-point.model.ts`

| Field | Type | Notes |
|-------|------|-------|
| entityType | enum | company, person |
| entityId | ObjectId | Required |
| companyId, personId | ObjectId | Optional refs |
| contactType | enum | email, mobile, phone, website, linkedin, whatsapp, other |
| contactValue | string | Required |
| normalizedValue | string | Dedup/search |
| isPrimary, isVerified | boolean | |
| dataConfidence, sourceFile, notes | | |

**Indexes:** entityType+entityId; contactType; normalizedValue; companyId; personId

---

### `sources`

Model: `source.model.ts`

| Field | Type | Notes |
|-------|------|-------|
| fileName | string | Required |
| fileType, description, notes | string | |
| importType | enum | **simple**, **processed** |
| uploadedBy | ObjectId | ref User |
| recordsExtracted | number | |

**Indexes:** fileName, importType, uploadedBy

---

### `import_logs`

Model: `import-log.model.ts`

| Field | Type | Notes |
|-------|------|-------|
| sourceId | ObjectId | ref Source |
| importType | enum | simple, processed |
| status | enum | pending, processing, completed, failed, partially_completed |
| fileName | string | |
| totalRowsFound, companiesExtracted, peopleExtracted, … | number | Counters |
| duplicatesDetected, issuesFound | number | |
| errorMessages | string[] | Stored as `errorMessages` (not `errors` — Mongoose conflict) |
| startedAt, finishedAt | Date | |
| createdBy | ObjectId | ref User |

**Indexes:** status, importType, createdBy, createdAt

---

### `service_matches`

Model: `service-match.model.ts` — typical of **processed** imports.

| Field | Type | Notes |
|-------|------|-------|
| companyId | ObjectId | ref Company, required |
| recommendedService | string | Required |
| fitScore | number | 0–100 |
| fitReason, priority, sourceFile | | |

**Indexes:** companyId, recommendedService, fitScore, priority

---

### `segments`

Model: `segment.model.ts`

| Field | Type | Notes |
|-------|------|-------|
| name | string | Required |
| description | string | Optional |
| targetType | enum | `companies`, `people`, `contact_points` |
| filters | Mixed | Query definition |
| type | enum | static, dynamic |
| staticIds | ObjectId[] | Optional fixed members for static segments |
| status | enum | active, inactive |
| createdBy | ObjectId | ref User |

---

### `email_templates`

Model: `email-template.model.ts`

| Field | Type | Notes |
|-------|------|-------|
| name, subject | string | Required |
| description | string | Optional |
| category | string | Optional (e.g. `hr`, `follow_up`, `custom`) |
| htmlContent | string | Required |
| textContent | string | Optional |
| variables | string[] | Merge fields |
| language | enum | ar, en, mixed |
| status | enum | draft, active, archived |

---

### `campaigns`

Model: `campaign.model.ts`

| Field | Type | Notes |
|-------|------|-------|
| name | string | Required |
| segmentId, templateId | ObjectId | refs |
| senderEmail | string | |
| status | enum | draft, scheduled, sending, sent, paused, completed, failed, archived |
| scheduledAt, sentAt | Date | |
| subjectSnapshot | string | Template subject snapshot at campaign setup |
| templateSnapshot | object | Template metadata snapshot before send |
| segmentSnapshot | object | Segment metadata/filter snapshot before send |
| stats | object | totalRecipients, sent, delivered, opened, clicked, replied, bounced, unsubscribed, convertedToLead |

---

### `campaign_recipients`

Model: `campaign-recipient.model.ts`

| Field | Type | Notes |
|-------|------|-------|
| campaignId | ObjectId | ref Campaign, required |
| companyId, personId, contactPointId | ObjectId | Optional refs |
| email | string | Required, normalized |
| companyName, personName, jobTitle | string | Snapshot personalization |
| sector, city, recommendedService | string | Snapshot personalization |
| status | enum | pending, queued, sent, delivered, opened, clicked, replied, bounced, failed, unsubscribed, skipped |
| skipReason | string | missing_email, duplicate_email, etc. |
| trackingId | string | sparse unique |
| sentAt, openedAt, clickedAt, repliedAt, bouncedAt | Date | Event timestamps |
| errorMessage | string | Failure reason |

**Indexes:** campaignId, email, status, trackingId (unique sparse), companyId, personId

---

### `email_messages`

Model: `email-message.model.ts` — outbound campaign messages.

| Field | Type | Notes |
|-------|------|-------|
| campaignId, companyId, personId | ObjectId | |
| toEmail | string | Required |
| status | enum | queued … unsubscribed |
| trackingId | string | Unique, required |
| providerMessageId | string | |
| sentAt, openedAt, clickedAt, repliedAt, bouncedAt | Date | |

---

### `email_threads` (Task 020)

Model: `email-thread.model.ts` — conversation grouping for inbox.

| Field | Type | Notes |
|-------|------|-------|
| subject, normalizedSubject | string | Thread subject |
| participants | array | `{ email, name?, role? }` |
| lastMessageAt, lastInboundAt, lastOutboundAt | Date | |
| status | enum | open, archived, snoozed, closed |
| companyId, personId, leadId, campaignId, sequenceId | ObjectId | CRM links |
| unreadCount | number | |
| hasAiClassification, lastIntent, lastSentiment | | AI summary |
| lastSnippet, lastFromEmail, lastDirection | | List display |

**Indexes:** lastMessageAt, normalizedSubject, participants.email, companyId, personId, leadId, campaignId, sequenceId, status, unreadCount

---

### `email_replies`

Model: `email-reply.model.ts` — business inbox / reply tracking.

| Field | Type | Notes |
|-------|------|-------|
| threadId | ObjectId | ref EmailThread |
| direction | enum | inbound, outbound |
| isRead | boolean | Per-message read state |
| snippet | string | List preview (no full body) |
| uid | number | IMAP UID (optional) |
| internetMessageId | string | Message-ID header from inbox mail |
| inReplyTo | string | In-Reply-To header |
| references | string[] | References header split |
| messageId, campaignId, companyId, personId | ObjectId | |
| linkedCompanyId, linkedPersonId, linkedLeadId, linkedCampaignId, linkedSequenceId | ObjectId | Explicit CRM links |
| campaignRecipientId, sequenceRecipientId, mailboxId | ObjectId | |
| fromEmail | string | Required |
| toEmail, subject | string | Optional |
| bodyText, bodyHtml | string | Parsed content |
| rawHeaders | object | Optional parsed headers snapshot |
| receivedAt | Date | Required |
| classificationId | ObjectId | ref AiClassification |
| status | enum | new, classified, linked_to_lead, ignored, spam |

**Indexes:** threadId+receivedAt, fromEmail, internetMessageId (sparse), campaignId, sequenceId, isRead, direction, receivedAt

---

### `leads`

Model: `lead.model.ts`

| Field | Type | Notes |
|-------|------|-------|
| companyId, personId, campaignId | ObjectId | |
| emailReplyId | ObjectId | ref EmailReply (unique sparse) |
| aiClassificationId | ObjectId | ref AiClassification |
| source | enum | import, campaign, campaign_reply, inbox, manual, website, referral, other |
| leadScore, temperature, status, priority | | |
| assignedTo | ObjectId | ref User |
| serviceInterest, notes | string | |

---

### `opportunities`

Model: `opportunity.model.ts`

| Field | Type | Notes |
|-------|------|-------|
| leadId, companyId, personId | ObjectId | |
| sourceCampaignId, sourceEmailReplyId | ObjectId | Attribution |
| title | string | Required |
| stage | enum | new_lead … postponed |
| valueEstimate, probability | number | |
| expectedCloseDate | Date | |
| assignedTo | ObjectId | ref User |

---

### `activities`

Model: `activity.model.ts` — CRM timeline.

| Field | Type | Notes |
|-------|------|-------|
| entityType, entityId | | company, person, lead, opportunity, campaign, email |
| type | enum | import, email_*, call, meeting, note, task_created, stage_changed, lead_created, ai_classified, system |
| title | string | Required |
| metadata | Mixed | |
| createdBy | ObjectId | ref User |

---

### `tasks`

Model: `task.model.ts` — user tasks (not import tasks).

| Field | Type | Notes |
|-------|------|-------|
| title | string | Required |
| status | enum | open, in_progress, completed, cancelled |
| priority | enum | urgent, high, medium, low |
| relatedEntityType, relatedEntityId | | Polymorphic link |

---

### `ai_classifications`

Model: `ai-classification.model.ts`

| Field | Type | Notes |
|-------|------|-------|
| sourceType, sourceId | | email_reply, lead, company, other |
| provider | enum | openai, fallback_rules, manual |
| providerModel | string | LLM model id (spec: `model`; renamed for Mongoose) |
| intent, sentiment, leadTemperature, serviceInterest | | |
| rawOutput | Mixed | |
| confidence | number | 0–1 |

---

### `notifications`

Model: `notification.model.ts` (Task 015)

| Field | Type | Notes |
|-------|------|-------|
| userId | ObjectId | Optional — broadcast when unset |
| type | enum | email_reply_received, lead_created, … |
| title, message | string | |
| entityType, entityId | | Polymorphic link |
| priority | enum | low, medium, high, critical |
| status | enum | unread, read |
| actionUrl | string | In-app deep link |
| metadata | Mixed | |
| readAt | Date | |

---

### `automation_logs`

Model: `automation-log.model.ts` — n8n and system automation.

| Field | Type | Notes |
|-------|------|-------|
| name | string | Required |
| provider | enum | n8n, system, email, ai |
| direction | enum | inbound, outbound |
| status | enum | success, failed, pending |
| webhookUrl, payload, response, errorMessage | | |

---

### `data_quality_issues`

Model: `data-quality-issue.model.ts`

| Field | Type | Notes |
|-------|------|-------|
| sourceId, importLogId | ObjectId | |
| issueType, severity, status | | |
| recommendedFix | string | |

---

### `duplicate_reviews`

Model: `duplicate-review.model.ts`

| Field | Type | Notes |
|-------|------|-------|
| groupId | string | Required |
| entityType | enum | company, person |
| entityId1/2, entityName1/2 | | |
| recommendedAction | enum | merge, review_manually, keep_separate |
| status | enum | pending, merged, kept_separate, ignored |

---

### `mailboxes`

Model: `mailbox.model.ts` (Task 016)

| Field | Type | Notes |
|-------|------|-------|
| name, email | string | email unique |
| provider | enum | smtp_imap, google, microsoft, other |
| smtpHost, smtpPort, smtpUser | | |
| smtpPasswordEncrypted | string | AES-256-GCM; `select: false`; never returned in API |
| imapHost, imapPort, imapUser | | |
| imapPasswordEncrypted | string | AES-256-GCM; `select: false` |
| isDefault | boolean | One default per org |
| status | enum | `active`, `inactive` |
| lastSyncAt, notes | | |
| createdAt, updatedAt | | |

`Campaign.mailboxId` and `EmailMessage.mailboxId` optional refs for outbound routing (future send path).

---

### `system_settings`

Model: `system-setting.model.ts` (Task 016)

Singleton `key: "global"`.

| Field | Type | Notes |
|-------|------|-------|
| appName, companyName | string | Legacy branding (synced with `branding`) |
| branding | object | `platformName`, `companyName`, `logoLight`, `logoDark`, `favicon`, `primaryColor`, `secondaryColor`, `website`, `supportEmail`, `supportPhone`, `address` |
| defaultLanguage, timezone | string | Locale |
| defaultSenderName, defaultSenderEmail | string | Campaign defaults |
| duplicateThreshold | number | Default `0.85` |
| companyMatchFields, personMatchFields, contactMatchFields | string[] | Import dedup |
| autoDuplicateDetection, autoDataQualityChecks | boolean | Import toggles |
| aiProvider, openaiModel, geminiModel, anthropicModel | string | Override env AI selection (no keys) |

---

## Schema implementation notes (Task 004)

| Spec field | Stored as | Reason |
|------------|-----------|--------|
| `errors` on ImportLog | `errorMessages` | Avoids Mongoose `Document.errors` |
| `model` on AiClassification | `providerModel` | Avoids Mongoose `Document.model` |

## Migration strategy

- Indexes created on first model use in dev/prod (Mongoose `autoIndex` in development).
- Future: `scripts/sync-indexes.ts` if needed (Task 023).

## Related

- [01-architecture.md](./01-architecture.md)
- [07-leads-pipeline.md](./07-leads-pipeline.md)
- [06-campaign-system.md](./06-campaign-system.md)