اسکیمای کامل دیتابیس و ERD
Database Schema & Entity Relationships

۱ اصول و قواعد طراحی اسکیما

پیش از طراحی هر جدول، این ۱۲ قاعده به‌عنوان قانون غیرقابل تغییر پروژه تعیین می‌شوند. هر جدول جدید باید از این قواعد تبعیت کند؛ در غیر این صورت در بازبینی رد می‌شود.

۲ نقشه کلان داده (Domain Map)

۳۸ جدول در سه گروه مستقل سازمان می‌یابند. مرز گروه‌ها دقیقاً با مرز کد (Core / Platform / Modules) منطبق است.

┌──────────────────────────────────────────────────────────────────────────────┐
│                       GROUP A — CORE  (موتور اتوماسیون)                       │
│                          جداول بدون پیشوند · ۱۳ جدول                          │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│   tenants ──┬─► workspaces ──┬─► workflows ──┬─► workflow_nodes             │
│       │     │                │       │       └─► workflow_edges             │
│       │     │                │       ├─► executions ──► execution_logs       │
│       │     │                │       └─► workflow_versions                   │
│       │     │                └─► secrets                                     │
│       │     ├─► webhooks                                                    │
│       │     └─► schedules                                                   │
│       │                                                                     │
│   node_types (رجیستری گره‌ها — سراسری و مشترک، بدون tenant_id)              │
│   idempotency_keys (کش درخواست‌های تکراری)                                   │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────────────────────────┐
│                     GROUP B — PLATFORM (لایه تجاری)                          │
│                        فقط در حالت SaaS بارگذاری می‌شود · ۱۱ جدول             │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│   plans ──┬─► plan_modules ──► modules                                       │
│           └─► subscriptions ──► tenants                                      │
│                                                                              │
│   module_access  (دسترسی tenant به ماژول: از پلن / add-on / trial)            │
│   usage_counters (شمارنده مصرف ماهانه per tenant per metric)                  │
│   usage_periods  (خلاصه مصرف دوره گذشته برای صورتحساب)                        │
│   invoices       (فاکتورها)                                                  │
│   api_keys       (کلیدهای API با hash)                                       │
│   licenses       (لایسنس نسخه Self-Hosted)                                   │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────────────────────────┐
│                    GROUP C — MODULE CRM (پیشوند crm_)                        │
│                             قابل نصب/حذف کامل · ۱۷ جدول                        │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│   crm_companies ──► crm_contacts ──┬─► crm_deals ──► crm_deal_stage_history  │
│                            │       ├─► crm_activities                        │
│                            │       └─► crm_tasks                             │
│                            └─► crm_taggables ──► crm_tags                    │
│                                                                              │
│   crm_pipelines ──► crm_stages ──► crm_deals                                 │
│   crm_segments · crm_custom_fields · crm_field_values                        │
│   crm_assignment_rules · crm_scoring_rules · crm_lead_forms                  │
│   crm_merge_logs · crm_scoring_events                                        │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘

۲.۱ اصل وابستگی یک‌طرفه

        GROUP A (CORE)        ◄──── خوانده می‌شود توسط همه
             ▲  ▲  ▲
             │  │  └──────────────────────┐
             │  └──────────────┐          │
             │                 │          │
     GROUP B (PLATFORM)   GROUP C (CRM)   ماژول‌های آینده
        ─ هیچ FK‌ای به CRM ندارد
        ─ CRM هیچ FK‌ای به PLATFORM ندارد
        ─ ارتباط فقط از طریق tenant_id مشترک

  نتیجه: حذف ماژول CRM ⇒ فقط DROP ۱۷ جدول crm_*
          هیچ جدول هسته‌ای آسیب نمی‌بیند و نیازی به تغییر نیست.

۳ راهنمای نمادها (Legend)

در تمام جداول این سند از نمادهای زیر استفاده می‌شود:

۴ اسکیمای هسته اتوماسیون

۱۳ جدول که موتور اتوماسیون را می‌سازند. این جداول هرگز توسط ماژول‌ها تغییر نمی‌کنند.

۵ اسکیمای لایه پلتفرم

۱۱ جدول که فقط در حالت SaaS وجود دارند. در نسخه Self-Hosted این Migrationها اجرا نمی‌شوند و سرویس PlanGate با پیاده‌سازی AllowAll جایگزین می‌شود.

۶ اسکیمای ماژول CRM

۱۷ جدول با پیشوند crm_. همه دارای tenant_id با ایندکس ترکیبی هستند (به‌جز crm_stages که از طریق pipeline_id به tenant می‌رسد). گروه‌بندی در سه کارت زیر آمده است: ارتباطی (۴)، قیف و فرصت (۴)، عملیاتی و پیکربندی (۹).

۷ جداول بحرانی — جزئیات ستونی

سه جدول پرکاربرد به‌صورت کامل ستون‌به‌ستون. بقیه جداول از همین الگو پیروی می‌کنند: کلیدها + tenant_id + timestamps + ایندکس‌های توجیه‌شده.

۸ DDL نمونه (Migration)

چهار Migration کلیدی که الگوی کل پروژه را تعریف می‌کنند. بقیه Migrationها از همین الگو تبعیت می‌کنند.

۸.۱ جریان اتوماسیون

// database/migrations/2026_01_01_000010_create_workflows_table.php
Schema::create('workflows', function (Blueprint $t) {
    $t->id();
    $t->foreignId('tenant_id')->constrained()->cascadeOnDelete();
    $t->foreignId('workspace_id')->constrained()->cascadeOnDelete();
    $t->ulid('public_id')->unique();
    $t->string('name', 140);
    $t->string('slug', 140);
    $t->text('description')->nullable();

    // وضعیت جریان: draft | active | paused | archived
    $t->string('status', 20)->default('draft');

    // محرک: webhook | schedule | event | manual
    $t->string('trigger_type', 24);
    $t->json('trigger_config')->nullable();

    // تنظیمات اجرایی: timeout, retry, concurrency, error_branch
    $t->json('settings')->nullable();
    $t->unsignedInteger('version')->default(1);

    // آمار (denormalized برای لیست بدون count روی executions)
    $t->unsignedBigInteger('run_count')->default(0);
    $t->unsignedBigInteger('failed_count')->default(0);
    $t->timestamp('last_run_at')->nullable();

    $t->timestamps();
    $t->softDeletes();

    $t->unique(['tenant_id', 'slug']);
    $t->index(['tenant_id', 'status']);
    $t->index(['tenant_id', 'trigger_type', 'status']);
    $t->index(['tenant_id', 'last_run_at']);
});

۸.۲ لاگ اجرای هر گره

// database/migrations/2026_01_01_000040_create_execution_logs_table.php
Schema::create('execution_logs', function (Blueprint $t) {
    $t->id();
    $t->foreignId('execution_id')->constrained()->cascadeOnDelete();
    $t->string('node_key', 16);
    $t->string('node_type', 80)->index();   // برای فیلتر «گره‌های شکست‌خورده»
    $t->string('status', 20)->index();        // success | failed | skipped
    $t->unsignedTinyInteger('attempt')->default(1);

    $t->json('input')->nullable();   // ← ماسک‌شده هنگام ثبت
    $t->json('output')->nullable();  // ← ماسک‌شده هنگام ثبت
    $t->json('error')->nullable();   // {code, message, retryable}

    $t->unsignedInteger('duration_ms')->nullable();
    $t->timestamp('started_at')->nullable();
    $t->timestamps();   // بدون softDeletes — داده بایگانی‌شده است، نه قابل حذف

    $t->index(['execution_id', 'node_key']);
    $t->index(['status', 'node_type']);
});

۸.۳ مخاطب (ماژول CRM)

// app/Modules/Crm/Database/Migrations/..._create_crm_contacts_table.php
Schema::create('crm_contacts', function (Blueprint $t) {
    $t->id();
    $t->ulid('public_id')->unique();
    $t->foreignId('tenant_id')->constrained()->cascadeOnDelete();
    $t->foreignId('company_id')->nullable()->constrained('crm_companies')->nullOnDelete();
    $t->foreignId('owner_user_id')->nullable()->constrained('users')->nullOnDelete();

    $t->string('first_name', 80)->nullable();
    $t->string('last_name',  80)->nullable();

    // لایه نمایش + لایه فهرست‌سازی
    $t->string('email',  190)->nullable();
    $t->string('email_normalized', 190)->nullable();
    $t->string('mobile', 32)->nullable();
    $t->string('mobile_normalized', 32)->nullable();

    $t->string('status', 24)->default('new');
    $t->integer('score')->default(0);
    $t->string('source', 32)->nullable();
    $t->string('source_detail', 120)->nullable();
    $t->string('external_id', 64)->nullable();

    $t->timestamp('last_activity_at')->nullable();
    $t->timestamp('next_activity_at')->nullable();
    $t->timestamp('scored_at')->nullable();

    $t->json('meta')->nullable();
    $t->timestamps();
    $t->softDeletes();

    // یکتا: جلوگیری مطلق از تکراری ایمیلی
    $t->unique(['tenant_id', 'email_normalized']);

    // مسیرهای پرکوئری — ترتیب ستون‌ها مهم است (tenant اول)
    $t->index(['tenant_id', 'status', 'score']);
    $t->index(['tenant_id', 'owner_user_id', 'status']);
    $t->index(['tenant_id', 'mobile_normalized']);
    $t->index(['tenant_id', 'last_activity_at']);
    $t->index(['tenant_id', 'next_activity_at']);
    $t->index(['tenant_id', 'source']);
    $t->unique(['tenant_id', 'external_id'], 'crm_contacts_tenant_external_unique');
});

۹ استراتژی ایندکس‌گذاری

ایندکس‌ها چون خواندن را تند و نوشتن را کند می‌کنند، باید توجیه‌پذیر باشند. این سه قاعده کل را تعیین می‌کنند:

۹.۱ نقشه ایندکس‌های پرکاربرد

۹.۲ ایندکس‌هایی که عمداً نداریم

۱۰ سیاست استفاده از JSON

JSON قدرتمند است و خطرناک. بدون سیاست روشن، به مرور «جای فیلد شکسته» تبدیل می‌شود.

۱۰.۱ قاعده «سه آزمون» پیش از افزودن ستون JSON

  سؤال ۱: آیا باید روی این داده WHERE یا ORDER BY زده شود؟
        ├─ بله  ⇒ ستون مستقل بساز
        └─ خیر ↓

  سؤال ۲: آیا ساختار هر رکورد متفاوت است (Schema های ناهمگون)؟
        ├─ خیر  ⇒ آیا نیاز به فیلتر در آینده دارم؟ اگر بله ⇒ ستون مستقل
        └─ بله ↓

  سؤال ۳: آیا فقط برای خواندن و نمایش استفاده می‌شود؟
        ├─ بله  ⇒ JSON مناسب است ✅
        └─ خیر  ⇒ دوباره برگرد به سؤال ۱

۱۰.۲ قرارداد ساختار JSON هر ستون

// برای هر ستون JSON یک cast در Model تعریف کنید + یک FormRequest اعتبارسنج.

// app/Core/Workflow/Models/Workflow.php
class Workflow extends Model
{
    protected $casts = [
        'trigger_config' => 'array',
        'settings'       => 'array',
    ];

    // نرمال‌سازی هنگام ذخیره — کلیدها همیشه یکسان باشند
    protected static function saving($model): void
    {
        $model->settings = static::normalizeSettings($model->settings);
    }
}

۱۱ ماشین‌های وضعیت (Enums)

هسته‌ای‌ترین تصمیم مدل داده: هر وضعیت، یک ماشین با گذارهای مجاز است. این نه‌تنها اعتبارسنجی، بلکه گزارش‌گیری و حتی طراحی API را شکل می‌دهد.

۱۱.۱ گذار وضعیت مخاطب (Contact Status)

                    ┌────────────────────────────────────────┐
                    │            crm_contacts.status          │
                    └────────────────────────────────────────┘

   new ──► qualified ──► contacted ──► nurturing ──► customer
    │          │             │            │             │
    │          │             │            │             ▼
    │          │             │            └──────► churned / inactive
    │          │             │                              │
    │          └──────► lost │                              │
    │                       │                              │
    └──(بدون نیاز)──────────┘◄───────────────(بازگشت ممکن)──┘

   ممنوع‌ها (throws InvalidStatusTransition):
     new ⇄ customer   |   lost → contacted   |   churned → qualified

۱۱.۲ گذار وضعیت اجرا (Execution Status)

  queued ──► running ──► success
    │           ├──────► failed ──────► (retry) ──► queued
    │           ├──────► timeout ─────► (retry) ──► queued
    │           └──────► canceled
    │                        │
    └──► canceled            └──► dead_letter (پس از اتمام retry)

  قواعد:
    ✗ هرگز running ⇄ queued  (نشانه job گم‌شده ⇒ watchdog باید تعمیر کند)
    ✗ success یک‌طرفه است (فقط با اجرای مجدد، رکورد جدید ساخته می‌شود)
    ✔ failed/canceled هر دو نهایی هستند و مسیر بازگشت، اجرای مجدد است
    ✔ stuck ⇐ اگر running بیش از timeout جریان ماند ⇒ watchdog آن را timeout می‌کند

۱۱.۳ پیاده‌سازی با Enum بومی PHP

// app/Modules/Crm/Enums/ContactStatus.php
enum ContactStatus: string
{
    case New        = 'new';
    case Qualified   = 'qualified';
    case Contacted   = 'contacted';
    case Nurturing   = 'nurturing';
    case Customer    = 'customer';
    case Lost        = 'lost';
    case Churned     = 'churned';

    private function transitions(): array
    {
        return match ($this) {
            self::New       => [self::Qualified, self::Lost],
            self::Qualified => [self::Contacted, self::Lost],
            self::Contacted => [self::Nurturing, self::Customer, self::Lost],
            self::Nurturing => [self::Customer, self::Lost, self::Churned],
            self::Customer  => [self::Churned],
            self::Lost      => [self::New],
            self::Churned   => [self::New],
        };
    }

    public function canTransitionTo(self $to): bool
    {
        return in_array($to, $this->transitions(), true);
    }
}

۱۲ حذف نرم و چرخه حیات داده

۱۲.۱ قواعد سطل آشغال (Trash)

  حذف توسط کاربر
       │
       ▼
  ┌──────────────────────────────────────┐
  │ Soft Delete  →  deleted_at = now()   │
  │ (به‌جز جداول بایگانی و کش)            │
  └──────────────────┬───────────────────┘
                     ▼
  ┌──────────────────────────────────────┐
  │ سطل آشغال (نمایش در فرانت)            │
  │  · مشاهده اقلام حذف‌شده               │
  │  · بازیابی (restore)                  │
  │  · حذف قطعی (Force Delete)           │
  └──────────────────┬───────────────────┘
                     ▼ پس از ۳۰ روز
  ┌──────────────────────────────────────┐
  │ Job شبانه: حذف دائمی                 │
  │  · با CASCADE از رکوردهای مرتبط       │
  │  · ثبت خلاصه در audit_logs           │
  └──────────────────────────────────────┘

۱۳ حجم داده و مقیاس ۱۰۰ مشتری

تخمین بر پایه: ۱۰۰ مشتری در سال دوم، با فرض میانگین ۳۰ اجرا در روز برای هر مشتری و ۱۵ گره در هر جریان.

۱۴ استراتژی پارتیشن‌بندی و بایگانی داده‌ها

برای جداول حجیم، عملیات DELETE سنتی پرهزینه است و سبب قفل شدن جدول و پدیده Table Bloat می‌شود. راهکار قطعی، Range Partitioning ماهانه بر مبنای تاریخ است تا آزادسازی فضا با یک DROP TABLE ارزان در کسری از ثانیه انجام شود.

۱۴.۱ نمونه تعریف پارتیشن در PostgreSQL

-- تبدیل execution_logs به جدول پارتیشن‌بندی شده ماهانه
CREATE TABLE execution_logs (
    id BIGSERIAL,
    tenant_id UUID NOT NULL,
    execution_id BIGINT NOT NULL,
    node_key VARCHAR(64) NOT NULL,
    status VARCHAR(20) NOT NULL,
    input_payload JSONB,
    output_payload JSONB,
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- ایجاد پارتیشن ماه فروردین / آوریل
CREATE TABLE execution_logs_y2026m04 PARTITION OF execution_logs
    FOR VALUES FROM ('2026-04-01 00:00:00+00') TO ('2026-05-01 00:00:00+00');

-- آزادسازی فضای ماه منقضی شده با صفر اثر روی دیتابیس زنده:
-- DROP TABLE execution_logs_y2026m01;

۱۵ سیاست اجرای مایگریشن‌ها در محیط عملیاتی (Zero-Downtime)

سیستم باید بدون توقف سرویس و بدون ایجاد قفل طولانی (Exclusive Locks) روی جدول‌های زنده بروزرسانی شود.

۱۵.۱ چرخه سه‌مرحله‌ای تغییر ستون (Expand & Contract)

  مرحله ۱ (نسخه N):
    ├─ افزودن ستون جدید (Nullable)
    └─ کد جدید: خواندن از قدیم، نوشتن همزمان در هر دو (Dual-Write)
  
  مرحله ۲ (نسخه N+1):
    ├─ اجرای Job پس‌زمینه برای پر کردن ردیف‌های قدیمی در ستون جدید
    └─ کد: تغییر منبع خواندن به ستون جدید
  
  مرحله ۳ (نسخه N+2):
    ├─ حذف نوشتن به ستون قدیمی
    └─ DROP ستون قدیمی بدون ریسک داون‌تایم

۱۶ معماری پشتیبان‌گیری و بازیابی (Backup & Disaster Recovery)

۱۷ مقایسه موتور دیتابیس: PostgreSQL vs MySQL 8

هر دو موتور گزینه‌های معتبری هستند، اما با توجه به ویژگی‌های زیرساختی، تحلیل و اولویت سیستم به شرح زیر است:

۱۸ استراتژی تست و تضمین یکپارچگی داده‌ها

• تست نشت بین تننت‌ها (Tenant Leakage Test): اجرای خودکار تست‌های یکپارچگی (Integration Tests) در CI که در آن کاربر تننت A با ارسال شناسه‌های تننت B تلاش برای واکشی اطلاعات می‌کند و همیشه با پاسخ 404 ModelNotFound مواجه می‌شود.
• تست رفتار کلیدهای خارجی و آبشارها: اطمینان از اینکه حذف نرم (Soft Delete) یک موجودیت، رکوردهای فرعی وابسته را ناخواسته Hard Delete نکند.
• تست بن‌بست و هم‌زمانی (Concurrency & Deadlocks): اجرای سناریوهای استرس با ۲۰ پردازشگر صف هم‌زمان روی به‌روزرسانی ردیف مشترک (مثل افزایش شمارنده usage_counters) با استفاده از تراکنش‌های ایزوله و قفل‌های بدبینانه lockForUpdate().

۱۹ ترتیب قطعی اجرای مایگریشن‌ها (Migration Dependency Order)

برای جلوگیری از خطاهای قید کلید خارجی (Foreign Key Constraint Failures)، مایگریشن‌های ۴۱ جدول باید دقیقاً بر اساس لایه‌بندی زیر اعمال گردند:

1. لایه ۰ (زیرساخت پایه): tenants، سپس users، roles، permissions، tenant_user
2. لایه ۱ (محیط ابری پلتفرم): plans، subscriptions، usage_counters، usage_periods، api_keys، secrets
3. لایه ۲ (موتور اتوماسیون Core): node_types، workflows، triggers، idempotency_keys، executions، execution_logs، audit_logs
4. لایه ۳ (پایه CRM): crm_tags، crm_custom_fields، crm_pipelines، crm_pipeline_stages
5. لایه ۴ (موجودیت‌های اصلی CRM): crm_companies، crm_contacts، crm_deals، crm_lead_forms
6. لایه ۵ (رابط‌ها و رویدادهای فرعی): crm_activities، crm_tasks، crm_deal_stage_history، crm_field_values، crm_taggables، crm_merge_logs، crm_segments

۲۰ نتیجه‌گیری و گام‌های بعدی پیاده‌سازی