Data Model

Overview

NexusCommerce uses Supabase (PostgreSQL) as its primary database with 25+ tables organized into functional domains. All tables are multi-tenant with RLS policies enforcing data isolation. This document covers the core tables and their relationships.

Key Concepts

Multi-tenant isolation — Every table includes a tenant_id uuid NOT NULL column. RLS policies ensure tenant_id = auth.jwt()->>'tenant_id' on all reads and writes.

Soft deletes — Most tables use deleted_at timestamptz for soft deletion rather than DELETE statements. Queries filter WHERE deleted_at IS NULL.

Audit columns — All tables include created_at timestamptz DEFAULT now() and updated_at timestamptz DEFAULT now() (updated via trigger).

Core Tables

`tenants`

CREATE TABLE tenants (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  slug text UNIQUE NOT NULL,
  primary_currency text NOT NULL DEFAULT 'EUR',
  timezone text NOT NULL DEFAULT 'UTC',
  plan text NOT NULL DEFAULT 'starter',
  created_at timestamptz DEFAULT now()
);

`profiles`

Links Supabase Auth users to tenants.

CREATE TABLE profiles (
  id uuid PRIMARY KEY REFERENCES auth.users(id),
  tenant_id uuid REFERENCES tenants(id),
  full_name text,
  role text NOT NULL DEFAULT 'viewer',
  created_at timestamptz DEFAULT now()
);

`marketplace_connections`

CREATE TABLE marketplace_connections (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  marketplace text NOT NULL,           -- 'amazon_us', 'bol', 'ebay', etc.
  account_name text,
  credentials_encrypted jsonb,         -- Encrypted with CREDENTIALS_ENCRYPTION_KEY
  config jsonb DEFAULT '{}',           -- Adapter configuration
  status text DEFAULT 'pending',       -- 'active', 'error', 'rate_limited', 'pending'
  last_synced_at timestamptz,
  error_message text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

`products`

CREATE TABLE products (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  sku text NOT NULL,
  title text NOT NULL,
  description text,
  status text NOT NULL DEFAULT 'draft', -- 'active', 'inactive', 'draft', 'archived'
  gtin text,
  brand text,
  category text,
  cost_price numeric(10,2),
  weight_kg numeric(8,3),
  dimensions_cm jsonb,                  -- {length, width, height}
  images jsonb DEFAULT '[]',            -- Array of image URLs
  attributes jsonb DEFAULT '{}',        -- Arbitrary product attributes
  deleted_at timestamptz,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  UNIQUE(tenant_id, sku)
);

`marketplace_listings`

CREATE TABLE marketplace_listings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  product_id uuid NOT NULL REFERENCES products(id),
  connection_id uuid NOT NULL REFERENCES marketplace_connections(id),
  marketplace text NOT NULL,
  external_id text,                     -- ASIN, item ID, offer ID, etc.
  external_product_id text,
  price numeric(10,2),
  currency text DEFAULT 'EUR',
  quantity integer DEFAULT 0,
  status text DEFAULT 'active',         -- 'active', 'inactive', 'suppressed', 'error'
  override_data jsonb DEFAULT '{}',     -- Marketplace-specific title/desc overrides
  last_synced_at timestamptz,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

`orders`

CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  connection_id uuid REFERENCES marketplace_connections(id),
  external_order_id text NOT NULL,
  marketplace text NOT NULL,
  status text NOT NULL DEFAULT 'pending',
  fulfillment_method text,              -- 'FBM', 'FBA', 'FBS'
  total_amount numeric(10,2),
  currency text,
  customer_name text,
  shipping_address jsonb,
  line_items jsonb NOT NULL DEFAULT '[]',
  raw_payload jsonb,                    -- Original marketplace API response
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  UNIQUE(tenant_id, marketplace, external_order_id)
);

`inventory_items`

CREATE TABLE inventory_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  product_id uuid NOT NULL REFERENCES products(id),
  warehouse_id uuid NOT NULL REFERENCES warehouses(id),
  on_hand integer NOT NULL DEFAULT 0,
  reserved integer NOT NULL DEFAULT 0,
  safety_stock integer NOT NULL DEFAULT 0,
  UNIQUE(tenant_id, product_id, warehouse_id)
);

`ai_jobs`

CREATE TABLE ai_jobs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  type text NOT NULL,
  status text NOT NULL DEFAULT 'queued', -- 'queued', 'running', 'completed', 'failed', 'cancelled'
  priority text DEFAULT 'normal',
  params jsonb NOT NULL DEFAULT '{}',
  result jsonb,
  error jsonb,
  started_at timestamptz,
  completed_at timestamptz,
  created_at timestamptz DEFAULT now()
);

Relationships

tenants
  ├── profiles (users)
  ├── marketplace_connections
  │     └── marketplace_listings ──── products
  ├── orders ──── returns
  ├── inventory_items ──── warehouses
  ├── pricing_rules ──── price_recommendations
  ├── reviews ──── review_sentiment_results
  ├── ai_jobs
  └── audit_log_entries

Key Indexes

Critical indexes for query performance:

-- Products
CREATE INDEX products_tenant_sku ON products(tenant_id, sku);
CREATE INDEX products_tenant_status ON products(tenant_id, status);

-- Orders
CREATE INDEX orders_tenant_status ON orders(tenant_id, status);
CREATE INDEX orders_tenant_marketplace ON orders(tenant_id, marketplace);
CREATE INDEX orders_created_at ON orders(created_at DESC);

-- Inventory
CREATE INDEX inventory_tenant_product ON inventory_items(tenant_id, product_id);

-- Listings
CREATE INDEX listings_tenant_marketplace ON marketplace_listings(tenant_id, marketplace);

On this page