Core Tables

These tables form the foundation of Artbase Studio's multi-tenant architecture.

profiles

User profile data, linked to Supabase Auth.

CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  full_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Column	Type	Description
id	UUID	User ID from auth.users
email	TEXT	User's email address
full_name	TEXT	Display name
avatar_url	TEXT	Profile picture URL
created_at	TIMESTAMPTZ	Account creation time
updated_at	TIMESTAMPTZ	Last profile update

Trigger: Auto-created when user signs up via handle_new_user() function.

---

organizations

Creator stores/businesses. Each organization is an isolated tenant.

CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  description TEXT,
  logo_url TEXT,
  banner_url TEXT,
  plan_id UUID REFERENCES plans(id),
  is_founding_artist BOOLEAN DEFAULT FALSE,
  founding_artist_start_date TIMESTAMPTZ,
  stripe_customer_id TEXT,
  stripe_subscription_id TEXT,
  settings JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_organizations_slug ON organizations(slug);
CREATE INDEX idx_organizations_plan_id ON organizations(plan_id);

Column	Type	Description
id	UUID	Primary key
name	TEXT	Store display name
slug	TEXT	URL-friendly identifier
description	TEXT	Store description
logo_url	TEXT	Store logo
banner_url	TEXT	Store banner image
plan_id	UUID	FK to subscription plan
is_founding_artist	BOOLEAN	Founding artist status
founding_artist_start_date	TIMESTAMPTZ	When founding status began
stripe_customer_id	TEXT	Stripe customer reference
stripe_subscription_id	TEXT	Stripe subscription reference
settings	JSONB	Custom organization settings
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update

Settings JSONB Structure

{
  "currency": "USD",
  "timezone": "America/New_York",
  "notifications": {
    "new_order": true,
    "low_stock": true
  }
}

---

organization_members

Junction table linking users to organizations with roles.

CREATE TABLE organization_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'member',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(org_id, user_id)
);

CREATE INDEX idx_org_members_user_id ON organization_members(user_id);
CREATE INDEX idx_org_members_org_id ON organization_members(org_id);

Column	Type	Description
id	UUID	Primary key
org_id	UUID	FK to organization
user_id	UUID	FK to auth.users
role	TEXT	Permission level
created_at	TIMESTAMPTZ	When member was added

Roles

Role	Description
owner	Full access, can delete organization
admin	Full access except billing
member	Read/write products, orders
viewer	Read-only access

---

plans

Subscription tiers with feature definitions.

CREATE TABLE plans (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  description TEXT,
  price_monthly NUMERIC(10,2) NOT NULL,
  price_yearly NUMERIC(10,2),
  features JSONB NOT NULL DEFAULT '{}'::jsonb,
  is_active BOOLEAN DEFAULT TRUE,
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Column	Type	Description
id	UUID	Primary key
name	TEXT	Plan display name
slug	TEXT	URL identifier
description	TEXT	Plan description
price_monthly	NUMERIC	Monthly price
price_yearly	NUMERIC	Annual price (discounted)
features	JSONB	Feature flags and limits
is_active	BOOLEAN	Available for signup
sort_order	INTEGER	Display order
created_at	TIMESTAMPTZ	Creation timestamp

Features JSONB Structure

{
  "max_products": 100,
  "max_variants": 10,
  "commission_rate": 0.05,
  "custom_domain": false,
  "analytics_retention_days": 90,
  "email_subscribers_limit": 1000,
  "channels": ["etsy", "gumroad"],
  "priority_support": false
}

Default Plans

Plan	Price	Commission	Products
Free	$0	10%	10
Starter	$15/mo	5%	50
Pro	$39/mo	3%	Unlimited
Enterprise	Custom	0%	Unlimited

---

organization_settings

Extended settings for per-organization customization.

CREATE TABLE organization_settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  email_from_name TEXT,
  email_from_address TEXT,
  email_reply_to TEXT,
  shipping_from_address JSONB,
  tax_settings JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(org_id)
);

CREATE INDEX idx_org_settings_org_id ON organization_settings(org_id);

Column	Type	Description
id	UUID	Primary key
org_id	UUID	FK to organization
email_from_name	TEXT	Sender name for emails
email_from_address	TEXT	Sender email address
email_reply_to	TEXT	Reply-to address
shipping_from_address	JSONB	Return address
tax_settings	JSONB	Tax configuration

---

RLS Policies for Core Tables

-- Profiles: Users can view/edit own profile
CREATE POLICY "Users view own profile"
  ON profiles FOR SELECT
  USING (id = auth.uid());

CREATE POLICY "Users update own profile"
  ON profiles FOR UPDATE
  USING (id = auth.uid());

-- Organizations: View through membership
CREATE POLICY "Members view organization"
  ON organizations FOR SELECT
  USING (id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

-- Organization members: View within same org
CREATE POLICY "Members view org members"
  ON organization_members FOR SELECT
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

-- Plans: Public read access
CREATE POLICY "Anyone can view plans"
  ON plans FOR SELECT
  USING (is_active = true);

---

Common Queries

Get User's Organizations

SELECT o.*
FROM organizations o
JOIN organization_members om ON om.org_id = o.id
WHERE om.user_id = auth.uid()
ORDER BY o.created_at;

Get Organization with Plan

SELECT
  o.*,
  p.name as plan_name,
  p.features
FROM organizations o
LEFT JOIN plans p ON p.id = o.plan_id
WHERE o.id = $1;

Check User Role in Organization

SELECT role
FROM organization_members
WHERE org_id = $1 AND user_id = auth.uid();