AutoCom Docs

Database Schema

Complete database schema documentation for the reseller platform.

Overview

The reseller platform uses a hybrid database approach:

  • Central Database: Shared tables for cross-tenant data
  • Tenant Databases: Isolated per-tenant data
┌─────────────────────────────────────────────────────────────┐
│                    CENTRAL DATABASE                          │
├─────────────────────────────────────────────────────────────┤
│  tenants (extended)          │  master_products             │
│  reseller_product_pricing    │  chain_orders                │
│  chain_order_status_history  │  tenant_wallets              │
│  wallet_transactions         │  cod_collections             │
│  cod_remittances            │  settlement_batches           │
└─────────────────────────────────────────────────────────────┘

Tenant Hierarchy

tenants (Extended)

Additional columns added to the existing tenants table:

-- Migration: 2026_01_05_000001_add_reseller_hierarchy_to_tenants.php

ALTER TABLE tenants ADD COLUMN type VARCHAR(20) DEFAULT 'standard';
ALTER TABLE tenants ADD COLUMN parent_tenant_id VARCHAR(36);
ALTER TABLE tenants ADD COLUMN depth INTEGER DEFAULT 0;
ALTER TABLE tenants ADD COLUMN reseller_settings JSONB;

-- Add foreign key
ALTER TABLE tenants
    ADD CONSTRAINT fk_tenant_parent
    FOREIGN KEY (parent_tenant_id)
    REFERENCES tenants(id)
    ON DELETE SET NULL;

-- Add indexes
CREATE INDEX idx_tenant_type ON tenants(type);
CREATE INDEX idx_tenant_parent ON tenants(parent_tenant_id);
CREATE INDEX idx_tenant_hierarchy ON tenants(parent_tenant_id, type, depth);

Column Details:

Column Type Description
type enum 'super_admin', 'reseller', 'standard'
parent_tenant_id uuid Reference to parent tenant
depth integer Level in hierarchy (0 = root)
reseller_settings jsonb Configuration for reseller features

reseller_settings JSON Structure:

{
  "can_onboard_sub_resellers": true,
  "max_sub_reseller_depth": 3,
  "default_margin_percent": 15,
  "fulfillment_preference": "forward_to_parent",
  "credit_limit": 10000,
  "settlement_frequency": "weekly"
}

ResellerCatalog Tables

master_products

-- Migration: 2026_01_05_020001_create_master_products_table.php

CREATE TABLE master_products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_tenant_id VARCHAR(36) NOT NULL,
    sku VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    base_cost DECIMAL(14,2) NOT NULL,
    minimum_margin_percent DECIMAL(5,2) DEFAULT 0,
    recommended_retail_price DECIMAL(14,2),
    stock_quantity INTEGER DEFAULT 0,
    track_inventory BOOLEAN DEFAULT true,
    low_stock_threshold INTEGER DEFAULT 10,
    images JSONB DEFAULT '[]',
    attributes JSONB DEFAULT '{}',
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_master_product_sku UNIQUE (owner_tenant_id, sku),
    CONSTRAINT fk_master_product_owner FOREIGN KEY (owner_tenant_id)
        REFERENCES tenants(id) ON DELETE CASCADE
);

CREATE INDEX idx_master_products_owner ON master_products(owner_tenant_id);
CREATE INDEX idx_master_products_sku ON master_products(sku);
CREATE INDEX idx_master_products_status ON master_products(status);

Column Details:

Column Type Description
id uuid Primary key
owner_tenant_id varchar(36) Super admin tenant ID
sku varchar(100) Stock keeping unit
name varchar(255) Product name
description text Product description
base_cost decimal(14,2) Base cost (super admin's cost)
minimum_margin_percent decimal(5,2) Minimum margin for resellers
recommended_retail_price decimal(14,2) Suggested selling price
stock_quantity integer Available stock
track_inventory boolean Enable inventory tracking
low_stock_threshold integer Alert when stock below this
images jsonb Array of image URLs
attributes jsonb Custom attributes (color, size, etc.)
status varchar(20) active, inactive, discontinued

reseller_product_pricing

-- Migration: 2026_01_05_020002_create_reseller_product_pricing_table.php

CREATE TABLE reseller_product_pricing (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    master_product_id UUID NOT NULL,
    reseller_tenant_id VARCHAR(36) NOT NULL,
    parent_tenant_id VARCHAR(36) NOT NULL,
    cost_to_reseller DECIMAL(14,2) NOT NULL,
    minimum_margin_percent DECIMAL(5,2) DEFAULT 0,
    is_available BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_reseller_pricing UNIQUE (master_product_id, reseller_tenant_id),
    CONSTRAINT fk_pricing_product FOREIGN KEY (master_product_id)
        REFERENCES master_products(id) ON DELETE CASCADE,
    CONSTRAINT fk_pricing_reseller FOREIGN KEY (reseller_tenant_id)
        REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_pricing_parent FOREIGN KEY (parent_tenant_id)
        REFERENCES tenants(id) ON DELETE CASCADE
);

CREATE INDEX idx_pricing_product ON reseller_product_pricing(master_product_id);
CREATE INDEX idx_pricing_reseller ON reseller_product_pricing(reseller_tenant_id);
CREATE INDEX idx_pricing_parent ON reseller_product_pricing(parent_tenant_id);

Column Details:

Column Type Description
id uuid Primary key
master_product_id uuid Reference to master product
reseller_tenant_id varchar(36) Who this pricing is for
parent_tenant_id varchar(36) Who set this pricing
cost_to_reseller decimal(14,2) Price the reseller pays
minimum_margin_percent decimal(5,2) Min margin for their sub-resellers
is_available boolean Whether product is available

ResellerOrders Tables

chain_orders

-- Migration: 2026_01_05_030001_create_chain_orders_table.php

CREATE TABLE chain_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Origin (where order was placed)
    origin_tenant_id VARCHAR(36) NOT NULL,
    origin_order_id VARCHAR(36),
    origin_order_number VARCHAR(100),

    -- Current handler
    current_tenant_id VARCHAR(36) NOT NULL,
    current_order_id VARCHAR(36),

    -- Fulfiller (who will ship)
    fulfiller_tenant_id VARCHAR(36),
    fulfiller_order_id VARCHAR(36),

    -- Chain info
    chain_path JSONB NOT NULL DEFAULT '[]',
    chain_depth INTEGER DEFAULT 1,
    chain_status VARCHAR(30) DEFAULT 'pending_forward',

    -- Pricing
    origin_total DECIMAL(14,2) NOT NULL,
    fulfiller_total DECIMAL(14,2),
    cod_amount DECIMAL(14,2),
    margin_breakdown JSONB DEFAULT '[]',

    -- Customer info
    customer_name VARCHAR(255),
    customer_phone VARCHAR(20),
    customer_email VARCHAR(255),

    -- Shipping
    shipping_address TEXT,
    shipping_city VARCHAR(100),
    shipping_state VARCHAR(100),
    shipping_pincode VARCHAR(10),
    shipping_country VARCHAR(50) DEFAULT 'India',

    -- Payment
    payment_method VARCHAR(20) DEFAULT 'cod',
    payment_status VARCHAR(20) DEFAULT 'pending',

    -- Tracking
    tracking_number VARCHAR(100),
    carrier VARCHAR(50),
    shipped_at TIMESTAMP,
    delivered_at TIMESTAMP,

    -- Order items
    items JSONB DEFAULT '[]',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_chain_order_origin FOREIGN KEY (origin_tenant_id)
        REFERENCES tenants(id),
    CONSTRAINT fk_chain_order_current FOREIGN KEY (current_tenant_id)
        REFERENCES tenants(id),
    CONSTRAINT fk_chain_order_fulfiller FOREIGN KEY (fulfiller_tenant_id)
        REFERENCES tenants(id)
);

CREATE INDEX idx_chain_orders_origin ON chain_orders(origin_tenant_id);
CREATE INDEX idx_chain_orders_current ON chain_orders(current_tenant_id);
CREATE INDEX idx_chain_orders_fulfiller ON chain_orders(fulfiller_tenant_id);
CREATE INDEX idx_chain_orders_status ON chain_orders(chain_status);
CREATE INDEX idx_chain_orders_created ON chain_orders(created_at);
CREATE INDEX idx_chain_orders_payment ON chain_orders(payment_method, payment_status);

chain_status Values:

Status Description
pending_forward Awaiting forwarding decision
forwarded Sent to parent
accepted Accepted by handler
processing Being prepared
shipped Handed to courier
out_for_delivery With delivery agent
delivered Successfully delivered
cancelled Cancelled
returned Returned by customer
rto Return to origin

chain_path JSON Structure:

[
  "super-admin-uuid",
  "distributor-uuid",
  "sub-reseller-uuid"
]

margin_breakdown JSON Structure:

[
  {
    "tenant_id": "super-admin-uuid",
    "cost": 100.00,
    "selling_price": 120.00,
    "margin_amount": 20.00,
    "margin_percent": 20.00
  },
  {
    "tenant_id": "distributor-uuid",
    "cost": 120.00,
    "selling_price": 138.00,
    "margin_amount": 18.00,
    "margin_percent": 15.00
  }
]

items JSON Structure:

[
  {
    "product_id": "master-product-uuid",
    "sku": "PROD-001",
    "name": "Product Name",
    "quantity": 2,
    "unit_price": 77.50,
    "total": 155.00
  }
]

chain_order_status_history

-- Migration: 2026_01_05_030002_create_chain_order_status_history_table.php

CREATE TABLE chain_order_status_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    chain_order_id UUID NOT NULL,
    status VARCHAR(30) NOT NULL,
    previous_status VARCHAR(30),
    changed_by_tenant_id VARCHAR(36),
    changed_by_user_id VARCHAR(36),
    notes TEXT,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_status_history_order FOREIGN KEY (chain_order_id)
        REFERENCES chain_orders(id) ON DELETE CASCADE
);

CREATE INDEX idx_status_history_order ON chain_order_status_history(chain_order_id);
CREATE INDEX idx_status_history_status ON chain_order_status_history(status);
CREATE INDEX idx_status_history_created ON chain_order_status_history(created_at);

ResellerFinance Tables

tenant_wallets

-- Migration: 2026_01_05_040001_create_tenant_wallets_table.php

CREATE TABLE tenant_wallets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id VARCHAR(36) NOT NULL UNIQUE,
    balance DECIMAL(14,2) DEFAULT 0,
    pending_credits DECIMAL(14,2) DEFAULT 0,
    pending_debits DECIMAL(14,2) DEFAULT 0,
    total_earned DECIMAL(14,2) DEFAULT 0,
    total_paid_out DECIMAL(14,2) DEFAULT 0,
    credit_limit DECIMAL(14,2) DEFAULT 0,
    currency VARCHAR(3) DEFAULT 'INR',
    last_settlement_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_wallet_tenant FOREIGN KEY (tenant_id)
        REFERENCES tenants(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX idx_wallet_tenant ON tenant_wallets(tenant_id);

wallet_transactions

-- Migration: 2026_01_05_040001_create_tenant_wallets_table.php (continued)

CREATE TABLE wallet_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wallet_id UUID NOT NULL,
    type VARCHAR(20) NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    balance_after DECIMAL(14,2) NOT NULL,
    reference_type VARCHAR(50),
    reference_id VARCHAR(36),
    description TEXT,
    metadata JSONB DEFAULT '{}',
    created_by VARCHAR(36),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_txn_wallet FOREIGN KEY (wallet_id)
        REFERENCES tenant_wallets(id) ON DELETE CASCADE,
    CONSTRAINT chk_txn_type CHECK (type IN ('credit', 'debit', 'hold', 'release'))
);

CREATE INDEX idx_wallet_txn_wallet ON wallet_transactions(wallet_id);
CREATE INDEX idx_wallet_txn_type ON wallet_transactions(type);
CREATE INDEX idx_wallet_txn_ref ON wallet_transactions(reference_type, reference_id);
CREATE INDEX idx_wallet_txn_created ON wallet_transactions(created_at);

type Values:

Type Description
credit Money added to wallet
debit Money removed from wallet
hold Amount reserved (pending_debits)
release Hold removed

cod_collections

-- Migration: 2026_01_05_040002_create_cod_collections_table.php

CREATE TABLE cod_collections (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    chain_order_id UUID NOT NULL,
    collected_by_tenant_id VARCHAR(36) NOT NULL,
    collected_amount DECIMAL(14,2) NOT NULL,
    collected_at TIMESTAMP NOT NULL,
    flow_type VARCHAR(20) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    settlement_batch_id UUID,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_collection_order FOREIGN KEY (chain_order_id)
        REFERENCES chain_orders(id),
    CONSTRAINT fk_collection_tenant FOREIGN KEY (collected_by_tenant_id)
        REFERENCES tenants(id),
    CONSTRAINT chk_flow_type CHECK (flow_type IN ('upstream', 'downstream'))
);

CREATE INDEX idx_cod_tenant ON cod_collections(collected_by_tenant_id);
CREATE INDEX idx_cod_order ON cod_collections(chain_order_id);
CREATE INDEX idx_cod_flow ON cod_collections(flow_type);
CREATE INDEX idx_cod_status ON cod_collections(status);

flow_type Values:

Type Description
upstream Super admin fulfilled, COD collected by courier
downstream Reseller fulfilled locally, collected COD directly

cod_remittances

-- Migration: 2026_01_05_040002_create_cod_collections_table.php (continued)

CREATE TABLE cod_remittances (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    cod_collection_id UUID NOT NULL,
    from_tenant_id VARCHAR(36) NOT NULL,
    to_tenant_id VARCHAR(36) NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    breakdown JSONB DEFAULT '{}',
    status VARCHAR(20) DEFAULT 'pending',
    due_date TIMESTAMP,
    completed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_remit_collection FOREIGN KEY (cod_collection_id)
        REFERENCES cod_collections(id),
    CONSTRAINT fk_remit_from FOREIGN KEY (from_tenant_id)
        REFERENCES tenants(id),
    CONSTRAINT fk_remit_to FOREIGN KEY (to_tenant_id)
        REFERENCES tenants(id)
);

CREATE INDEX idx_remit_from ON cod_remittances(from_tenant_id);
CREATE INDEX idx_remit_to ON cod_remittances(to_tenant_id);
CREATE INDEX idx_remit_status ON cod_remittances(status);
CREATE INDEX idx_remit_due ON cod_remittances(due_date);

breakdown JSON Structure:

{
  "type": "cod_remittance",
  "order_id": "chain-order-uuid",
  "your_margin": 17.00
}

Entity Relationship Diagram

┌─────────────┐     ┌─────────────────────┐     ┌─────────────────┐
│   tenants   │────<│ reseller_product_   │>────│ master_products │
│             │     │     pricing         │     │                 │
└──────┬──────┘     └─────────────────────┘     └─────────────────┘
       │
       │ parent_tenant_id
       │
       ├──────────────────────────────────────────────────────────┐
       │                                                          │
       ▼                                                          │
┌─────────────┐     ┌─────────────────────┐     ┌─────────────────┐
│   tenants   │────<│    chain_orders     │>────│ chain_order_    │
│  (parent)   │     │                     │     │ status_history  │
└─────────────┘     └──────────┬──────────┘     └─────────────────┘
                               │
                               │
       ┌───────────────────────┼───────────────────────┐
       │                       │                       │
       ▼                       ▼                       ▼
┌─────────────┐     ┌─────────────────────┐     ┌─────────────────┐
│tenant_wallet│────<│ wallet_transactions │     │ cod_collections │
│             │     │                     │     │                 │
└─────────────┘     └─────────────────────┘     └────────┬────────┘
                                                         │
                                                         ▼
                                               ┌─────────────────┐
                                               │ cod_remittances │
                                               │                 │
                                               └─────────────────┘

Indexes Summary

Performance Indexes

Table Index Columns
tenants idx_tenant_hierarchy (parent_tenant_id, type, depth)
master_products idx_master_products_owner (owner_tenant_id)
reseller_product_pricing idx_pricing_reseller (reseller_tenant_id)
chain_orders idx_chain_orders_status (chain_status)
chain_orders idx_chain_orders_created (created_at)
wallet_transactions idx_wallet_txn_created (created_at)
cod_remittances idx_remit_due (due_date)

Unique Constraints

Table Constraint Columns
master_products uq_master_product_sku (owner_tenant_id, sku)
reseller_product_pricing uq_reseller_pricing (master_product_id, reseller_tenant_id)
tenant_wallets idx_wallet_tenant (tenant_id)

Migration Order

Run migrations in this order:

  1. 2026_01_05_000001_add_reseller_hierarchy_to_tenants.php
  2. 2026_01_05_020001_create_master_products_table.php
  3. 2026_01_05_020002_create_reseller_product_pricing_table.php
  4. 2026_01_05_030001_create_chain_orders_table.php
  5. 2026_01_05_030002_create_chain_order_status_history_table.php
  6. 2026_01_05_040001_create_tenant_wallets_table.php
  7. 2026_01_05_040002_create_cod_collections_table.php
php artisan migrate