Database Reference Overview

Artbase Studio uses PostgreSQL through Supabase, with Row-Level Security (RLS) for multi-tenant data isolation.

Database Architecture

┌─────────────────────────────────────────────────────────────┐
│                        PostgreSQL                           │
├─────────────────────────────────────────────────────────────┤
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐         │
│  │   auth.*    │  │  storage.*  │  │  public.*   │         │
│  │  (Supabase) │  │  (Supabase) │  │  (App Data) │         │
│  └─────────────┘  └─────────────┘  └─────────────┘         │
├─────────────────────────────────────────────────────────────┤
│                   Row-Level Security                        │
│           (Organization-scoped access control)              │
└─────────────────────────────────────────────────────────────┘

Schema Overview

Core Tables

Table	Description
`profiles`	User profile data
`organizations`	Creator stores/businesses
`organization_members`	User ↔ Organization mapping
`plans`	Subscription tiers

Product Tables

Table	Description
`products`	Product catalog
`product_types`	Product categories
`product_variants`	Size/color variants
`product_images`	Product photos
`inventory_items`	Stock tracking

Order Tables

Table	Description
`orders`	Customer orders
`order_items`	Line items per order
`customers`	Customer records
`abandoned_carts`	Cart recovery tracking

Channel Tables

Table	Description
`channels`	Sales channels (Etsy, Gumroad)
`channel_accounts`	OAuth connections
`channel_orders`	Raw order data

Email Tables

Table	Description
`email_subscribers`	Newsletter subscribers
`email_campaigns`	Marketing campaigns
`email_automations`	Automated sequences
`email_segments`	Subscriber segments

Analytics Tables

Table	Description
`analytics_daily`	Daily metrics
`analytics_products`	Product performance

Entity Relationship Diagram

┌──────────────┐       ┌──────────────────┐       ┌──────────────┐
│   profiles   │       │  organizations   │       │    plans     │
│──────────────│       │──────────────────│       │──────────────│
│ id (PK)      │       │ id (PK)          │◀──────│ id (PK)      │
│ email        │       │ name             │       │ name         │
│ created_at   │       │ slug             │       │ features     │
└──────────────┘       │ plan_id (FK)     │       │ price        │
       │               │ is_founding_artist│       └──────────────┘
       │               └──────────────────┘
       │                       │
       │                       │
       ▼                       ▼
┌────────────────────────────────────────┐
│         organization_members           │
│────────────────────────────────────────│
│ id (PK)                                │
│ org_id (FK) ─────────────────────────▶ │
│ user_id (FK) ────────────────────────▶ │
│ role                                   │
└────────────────────────────────────────┘
                │
                │
                ▼
┌──────────────┐       ┌──────────────────┐       ┌──────────────┐
│   products   │       │  product_variants │       │   orders     │
│──────────────│       │──────────────────│       │──────────────│
│ id (PK)      │◀──────│ product_id (FK)  │       │ id (PK)      │
│ org_id (FK)  │       │ sku              │       │ org_id (FK)  │
│ title        │       │ name             │       │ order_number │
│ base_price   │       │ price            │       │ customer_id  │
│ status       │       │ inventory_count  │       │ total_amount │
└──────────────┘       └──────────────────┘       │ platform_fee │
                                                  └──────────────┘
                                                         │
                                                         ▼
                                                  ┌──────────────┐
                                                  │  order_items │
                                                  │──────────────│
                                                  │ order_id(FK) │
                                                  │ product_id   │
                                                  │ variant_id   │
                                                  │ quantity     │
                                                  │ unit_price   │
                                                  └──────────────┘

Data Types

Common Types

-- UUID for all primary keys
id UUID PRIMARY KEY DEFAULT gen_random_uuid()

-- Timestamps with timezone
created_at TIMESTAMPTZ DEFAULT NOW()
updated_at TIMESTAMPTZ DEFAULT NOW()

-- Status enums stored as TEXT with CHECK constraints
status TEXT CHECK (status IN ('draft', 'active', 'archived'))

-- Money stored as NUMERIC(10,2)
price NUMERIC(10,2) NOT NULL

JSON Columns

Several tables use JSONB for flexible data:

-- Organization settings
settings JSONB DEFAULT '{}'::jsonb

-- Plan features
features JSONB DEFAULT '{
  "max_products": 100,
  "commission_rate": 0.05
}'::jsonb

-- Shipping address
shipping_address JSONB

Migrations

Migrations are stored in supabase/migrations/ and applied in order:

Migration	Description
`001_initial_schema.sql`	Core tables
`002_products_schema.sql`	Product tables
`003_orders_schema.sql`	Order tables
`004_email_schema.sql`	Email marketing
`005_channels_schema.sql`	Sales channels
`013b_product_variants_schema.sql`	Variant support
`020_inventory_items_schema.sql`	Inventory tracking
`021_customers_schema.sql`	Customer normalization
`022_orders_platform_fee.sql`	Platform fees
`023_shipping_automation_trigger.sql`	Shipping notifications
`024_cart_abandonment.sql`	Cart recovery

Running Migrations

# Apply all migrations
supabase db push

# Reset and reapply (development only)
supabase db reset

Security Model

Row-Level Security

All tables have RLS enabled with organization-scoped policies:

-- Enable RLS
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- Select policy
CREATE POLICY "Users view org products"
  ON products FOR SELECT
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

-- Insert policy
CREATE POLICY "Users create org products"
  ON products FOR INSERT
  WITH CHECK (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

Service Role Access

Background services use the service role key to bypass RLS:

const supabase = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_SERVICE_ROLE_KEY // Bypasses RLS
);

Indexes

Key indexes for query performance:

-- Organization lookups
CREATE INDEX idx_products_org_id ON products(org_id);
CREATE INDEX idx_orders_org_id ON orders(org_id);

-- Status filtering
CREATE INDEX idx_products_status ON products(status);
CREATE INDEX idx_orders_status ON orders(status);

-- Time-based queries
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Customer lookups
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_customers_email ON customers(email);

Next Steps

Core Tables - Organizations, users, plans
Product Tables - Products, variants, inventory
Order Tables - Orders, items, customers
RLS Policies - Security configuration

Database Architecture​

Schema Overview​

Core Tables​

Product Tables​

Order Tables​

Channel Tables​

Email Tables​

Analytics Tables​

Entity Relationship Diagram​

Data Types​

Common Types​

JSON Columns​

Migrations​

Running Migrations​

Security Model​

Row-Level Security​

Service Role Access​

Indexes​

Next Steps​