Database Architecture

trait KsuidTrait
{
    protected static function bootKsuidTrait(): void
    {
        static::creating(function ($model) {
            if (empty($model->{$model->getKeyName()})) {
                $model->{$model->getKeyName()} = (string) new Ksuid();
            }
        });
    }

    public function getIncrementing(): bool { return false; }
    public function getKeyType(): string { return 'string'; }
}

users
├── id (KSUID)
├── first_name, last_name, email, password
└── email_verified_at, phone_number

profiles
├── id (KSUID)
├── name, company_id, organization_id
└── is_active

user_profiles
├── id (KSUID)
├── user_id → users.id
├── profile_id → profiles.id
├── company_id, organization_id
├── is_default, is_active
└── extra_permissions (JSON)

roles (Spatie)
├── id, name, guard_name
└── group_id

permissions (Spatie)
├── id, name, guard_name
└── group_id

model_has_roles / model_has_permissions / role_has_permissions

organizations
├── id (KSUID)
├── name
└── user_id → iam_users.id

companies
├── id (KSUID)
├── name, organization_id
├── parent_id (self-referencing for hierarchy, null = main company)
├── type, owner_id → iam_users.id
├── tax_registration_number, business_registration_number
├── address, city, country, logo
├── is_active (default: true)
├── subscription_activated_at, trial_ended_at, next_billing_at
└── activated_at

agents
├── id (KSUID)
├── company_id, organization_id
├── username, password, first_name, last_name, phone_number
├── profile_id
└── is_active

currencies
├── id (KSUID)
├── code (3 chars), name, exponent (0–30)
└── company_id

products
├── id (KSUID)
├── title, description, handle
├── type (physical/digital), status (active/draft/archived)
├── is_tracked, tags, published_at
├── company_id, organization_id
└── (no vendor_id — vendors are managed separately)

categories
├── id (KSUID)
├── title, handle, image_path, sort_order
└── company_id, organization_id

product_categories (pivot)
├── product_id, category_id

product_variants
├── id (KSUID)
├── product_id, sku, barcode
├── price, image_id
├── weight, weight_unit, is_taxable
└── company_id, organization_id

variant_prices
├── id (KSUID)
├── variation_id, name
├── amount, currency
└── organization_id

assets
├── id (KSUID)
├── name, type (location, etc.)
└── organization_id

vendors
├── id (KSUID)
├── name, email, phone, address
└── organization_id

orders
├── id (KSUID)
├── number, company_id
├── customer_id, customer_name
├── currency_code, currency_exponent
├── subtotal, discount, charge, tax_total, total
├── status (pending/rejected/completed/cancelled/updating)
├── seller_id, seller_type (polymorphic)
├── cashier_id (direct FK, not polymorphic)
├── completed_at, cancelled_at, rejected_at
└── deleted_at (soft delete)

order_items
├── id (KSUID)
├── order_id, company_id
├── product_id, variant_id, inventory_item_id
├── quantity, weight, weight_unit
├── currency_code, currency_exponent
├── unit_price, line_subtotal, line_discount
└── line_tax_total, line_total

order_payments
├── id (KSUID)
├── order_id, amount
├── payment_method_id
└── company_id

ledger_accounts
├── id (KSUID)
├── name, description, external_id
├── normal_balance (debit/credit)
├── currency_code, currency_exponent
├── balance, version (optimistic locking)
├── is_shared, status
└── (no company_id — scoped via organization or is_shared flag)

ledger_entries
├── id (KSUID)
├── ledger_account_id, ledger_transaction_id
├── amount (decimal), direction (debit/credit)
└── balance_before, balance_after (decimal)

ledger_transactions
├── id (KSUID)
├── description, external_id
├── activity_type, activity_id (polymorphic)
├── ledger_transaction_id (self-referencing)
└── ledger_account_id

ledger_*_metadata (3 tables)
├── parent_id, key, value

customers
├── id (KSUID)
├── name, email, phone_number
├── address, city, country
└── company_id

# Run all migrations (application + packages)
php artisan migrate

# Fresh install (drop all tables and re-run)
php artisan migrate:fresh --seed

# Rollback last batch
php artisan migrate:rollback

trait OptimisticLockingTrait
{
    public function saveWithLock(): bool
    {
        $currentVersion = $this->version;
        $this->version = $currentVersion + 1;

        $updated = static::where('id', $this->id)
            ->where('version', $currentVersion)
            ->update($this->getDirty());

        if ($updated === 0) {
            throw new OptimisticLockException('Record was modified by another process');
        }

        return true;
    }
}