Zodiac Tech CRM Docs

Database & migrations

Migration conventions, the tables that matter most, and how the schema is organised — with an ER map of the core relationships.

developer Medium ⏱ 10 min Updated 2026-10-05

Database & migrations

The schema is plain MySQL managed entirely by Laravel migrations (database/migrations/, currently 177 files). This page gives you the conventions and the map of the tables you will actually touch; the migrations themselves are the source of truth for columns.

Who this is for

Developers writing migrations, debugging data, or preparing a fresh install. For the tenancy mechanics behind tenant_id, read Multi-tenancy & isolation first.

Difficulty: Medium · Time: about 10 minutes.

Conventions

  1. Every tenant-owned table carries tenant_id (FK to tenants, indexed) and its model uses the BelongsToTenant trait.
  2. Uniqueness is composite, not global — natural keys are scoped: unique(tenant_id, source, source_id) on leads; unique(tenant_id, source, name) on lead_source_connections. The same external object can exist for two businesses.
  3. Migrations are chronological and additive — later migrations add columns/indexes (add_utm_fields_to_leads, add_missing_performance_indexes, …). Do not rewrite history; add a new migration.
  4. Soft deletes for businesses — tenants (and login-adjacent tables) use soft deletes; GDPR erasure hard-deletes after a cool-off (see Security model).
  5. JSON settings columns for per-tenant configuration (tenants.settings, tenants.branding) instead of wide nullable columns.
  6. No SQLite-specific SQL in app code — JSON-path checks are done in PHP (see BindHqTenant) so MySQL/SQLite test runs behave the same.

Core ER map

erDiagram
    TENANTS ||--o{ LEADS : "tenant_id"
    TENANTS ||--o{ LEAD_TASKS : "tenant_id"
    TENANTS ||--o{ USERS : "tenant_id"
    TENANTS ||--o{ WHATSAPP_CONNECTIONS : "tenant_id"
    TENANTS ||--o{ LEAD_SOURCE_CONNECTIONS : "tenant_id"
    TENANTS ||--o{ BUSINESS_ONBOARDING : "tenant_id"
    TENANTS ||--o{ DEVICES : "tenant_id"
    TENANTS ||--o{ COMMERCIAL_AGREEMENTS : "tenant_id"
    LEADS ||--o{ LEAD_TASKS : "lead_id"
    LEADS ||--o{ LEAD_CALLS : "lead_id"
    LEADS ||--o{ LEAD_NOTES : "lead_id"
    DEVICES ||--o{ DEVICE_TOKENS : "device_id"
    DEVICES ||--o{ DEVICE_CAPABILITY_SNAPSHOTS : "device_id"
    LEAD_CALLS ||--o| CALL_RECORDINGS : "lead_call_id"
    COMMERCIAL_AGREEMENTS ||--o{ COMMERCIAL_ENTITLEMENTS : "agreement_id"
    COMMERCIAL_AGREEMENTS ||--o{ BILLING_INVOICES : "agreement_id"

Tables you will meet first

Tenancy & users

tenants (name, slug, owner_id, domain/subdomain, max_seats/seat_count, subscription_status, plan, settings JSON, branding JSON, active, soft deletes) · users · tenant_users (pivot; role defaults to agent) · login_attempts · audit_logs · user_sessions.

Leads & sales

leads (source, source_id, source_connection_id, form_id, names, email, phone, phone_normalized, status default new, is_duplicate, raw_data, custom_fields, assigned_to, notes, contacted_at, consented_at) · lead_tasks (follow-ups) · lead_notes · lead_activities · lead_calls · call_recordings · pipelines / lead_pipeline_stages · tags · custom_field_definitions · lead_attachments · lead_duplicates.

Capture & messaging

lead_source_connections (per-tenant Meta/other connectors; webhook_token unique 64 chars, credentials/settings, status, last_received_at, last_error) · whatsapp_connections (WABA/phone, encrypted tokens, health fields, phone_number_id unique) · forms / form_fields / form_submissions / form_submission_values · conversations + WhatsApp message tables · webhook_logs.

Onboarding

business_onboarding + business_onboarding_steps (the guided 12-step client journey; see Roles & who does what for the business view).

Mobile

devices, device_tokens (only token_hash stored), device_capability_snapshots (append-only, pruned to 20 per device), excluded_numbers.

Commercial billing

commercial_agreements, commercial_agreement_versions, commercial_line_items, commercial_entitlements, payment_customers, payment_subscriptions, payment_mandates, billing_invoices, payment_transactions, billing_events (unique (provider, event_id)), billing_change_requests, billing_reconciliation_alerts.

Two different "invoice" families The CRM's own sales documents feature uses quotes, invoices, invoice_items, invoice_payments. The commercial billing subsystem (your subscription to the platform) uses billing_invoices + the commercial_* tables. Do not mix them up when debugging billing.

Platform & ops

settings (spatie/laravel-settings persistence), api_keys, outbound_webhooks, integrations / integration_sync_logs, notifications, scheduled_reports, jobs / failed_jobs, cache.

Running migrations

php artisan migrate            # apply pending migrations
php artisan migrate --seed     # fresh demo data (DemoSeeder / IndiaDemoSeeder)
php artisan migrate:fresh      # drop + re-run (local only!)

On Windows/XAMPP the PHP binary may not be on PATH — call it explicitly (D:/Xampp/php/php.exe artisan migrate).

The test database is separate PHPUnit runs against leadhub_testing (configured in phpunit.xml), never the leadhub working database. Credentials for both live in your local environment only — never in this documentation.

Was this page helpful?