Skip to content

Supabase Tables Overview

This page is a high-level catalog of the main Supabase tables and how they map to features in the app. It’s meant as a quick lookup for “where does this data live?” — not a full schema dump.

For underlying migrations, see supabase/migrations/.

Companies & tenancy

companies

Purpose: One row per tenant/store. Root of the multi-tenant data model.

Key columns (conceptual):

  • id – primary key; referenced by most other tables as tenant_id / company_id
  • name – store name (used in UI, invoices, etc.)
  • Contact fields – address, phone, email, etc.
  • Plan / status fields – current plan, onboarding status, etc.
  • Timestamps – created_at, updated_at

Used by:

  • AuthProvider / get_tenant_info_for_user RPC to resolve which company a user belongs to.
  • Subscriptions (subscriptions.company_id).
  • RLS policies: most business tables are scoped via tenant_id / company_id.

Users & profiles

auth.users (Supabase built-in)

Purpose: Authentication identities (email/password or OAuth).

Key columns:

  • id – user ID
  • email, email_confirmed_at
  • Provider-specific fields (metadata, identities)

Used by:

  • Supabase Auth (login/signup/session handling).
  • user_profiles.id (1:1 mapping).

user_profiles

Purpose: Application-level metadata for each user.

Key columns:

  • id – PK, also FK to auth.users.id
  • tenant_id / company_id – which company the user belongs to
  • role – owner, tenant, admin
  • full_name and other profile fields
  • Timestamps

Used by:

  • AuthProvider to resolve role + tenant via get_tenant_info_for_user.
  • RLS: many policies use get_user_tenant_id() logic that ultimately depends on this table.
  • Feature gating and dashboard behavior (owner vs staff).

Subscriptions & billing

subscriptions

Purpose: Local mirror of Stripe subscription state per company.

Key columns:

  • id
  • company_id – FK to companies.id
  • stripe_subscription_id
  • stripe_customer_id
  • plan_name – e.g. starter, professional
  • status – trialing, active, past_due, canceled, etc.
  • Period fields – current_period_start, current_period_end
  • trial_end, trial_days_remaining
  • Timestamps

Used by:

  • useSubscription, isSubscriptionActive, getLockoutReason in ProtectedRoute.
  • Billing & Stripe flows (see flows/billing-stripe.md).
  • Plan limit checks (e.g. getPlanLimits in subscription-utils).

webhook_events_processed

Purpose: Idempotency and auditing for Stripe webhooks.

Key columns:

  • event_id – Stripe event ID (unique)
  • event_type
  • company_id
  • subscription_id (Stripe or local)
  • status – success, error, skipped
  • error_message
  • processed_at, created_at

Used by:

  • stripe-webhook edge function to skip already-processed events.
  • Operations/debugging (runbooks).

payment_history (name may vary)

Purpose: Historical record of Stripe invoice/payment events mirrored into Supabase.

Key columns (conceptual):

  • id
  • company_id
  • stripe_invoice_id / stripe_payment_intent_id
  • amount, currency
  • status
  • invoice_url
  • paid_at, created_at

Used by:

  • Billing views, owner analytics, and troubleshooting.

Clients & CRM

clients

Purpose: Master record for each customer.

Key columns:

  • id
  • tenant_id – FK to companies.id
  • first_name, last_name
  • email
  • phone
  • address
  • notes
  • date_of_birth
  • preferred_contact_method
  • created_at, updated_at

Used by:

  • /clients screen (list, filters, stats).
  • orders.client_id.
  • invoices.client_id, pay_links.client_id.
  • CRM segmentation (active/new/VIP) via useClients and related RPCs.

RPCs: client metrics

  • get_client_total_spent(client_id_param) – sum of order totals per client.
  • get_client_order_count(client_id_param) – number of orders per client.
  • get_client_last_order_date(client_id_param) – most recent order date.

Used by:

  • useClients.fetchClients and fetchClientStats for CRM dashboards and filters.

Orders & workflows

orders

Purpose: Represents a job/order (repair, custom piece, etc.).

Key columns:

  • id
  • tenant_id
  • client_id (nullable)
  • Contact snapshot – first_name, last_name, phone, email
  • piece_description
  • price – total for the order (sum of line items)
  • Legacy links – inventory_item_id, product_variation_id
  • Board placement – column_id, card_position
  • Billing links – invoice_id
  • Payment flags – is_paid, paid_at
  • Timestamps

Used by:

  • /orders board + list views.
  • CRM stats (order count, revenue).
  • Invoice/Pay link creation flows.

order_line_items

Purpose: Line items attached to an order (inventory-backed or ad-hoc).

Key columns:

  • id
  • order_id – FK to orders.id
  • inventory_item_id – FK to inventory_items.id
  • product_variation_id – FK to product_variations.id
  • item_name
  • description
  • quantity
  • unit_price
  • line_total
  • position
  • Timestamps

Used by:

  • Orders UI (line item tables).
  • Invoices (invoice_line_items.order_line_item_id).
  • Pay links (pay_link_line_items.order_line_item_id).
  • Stock adjustment RPCs (deduct_inventory_stock, restore_inventory_stock).

order_attachments

Purpose: Files attached to orders (images, PDFs, etc.).

Key columns:

  • order_id
  • file_url, storage_path
  • file_name, file_type, file_size
  • Timestamps

Used by:

  • Order details drawer to show work photos, documents, and signed forms.

order_columns

Purpose: Kanban board columns for orders.

Key columns:

  • id
  • tenant_id
  • name
  • position
  • color
  • Timestamps

Used by:

  • /orders board layout (see OrderBoard, OrderColumn components).
  • Default columns created when a tenant is initialized.

Inventory & variants

inventory_items

Purpose: Core inventory/catalog table.

Key columns (subset):

  • id
  • tenant_id
  • sku
  • product_name
  • category, subcategory
  • item_description
  • retail_price, wholesale_price, cost_of_goods
  • current_stock_qty
  • track_quantity (boolean)
  • low_stock_threshold
  • track_variant_inventory (boolean)
  • is_active / archive flags
  • Timestamps

Used by:

  • /inventory views (create/edit/list).
  • Orders (order_line_items.inventory_item_id).
  • Pay links (pay_link_line_items.inventory_item_id).

Variants & attributes

Key tables:

  • product_variations
    • Per-variant rows linked to inventory_items.
    • Fields like size, color, current_stock_qty, pricing overrides.
  • variation_attributes
    • Attribute definitions (size, metal, etc.).
  • variation_attribute_values
    • Allowed values per attribute.
  • product_variation_attribute_values
    • Join table for many-to-many variant attributes.
  • variation_photos
    • Photos linked to specific variants.

Used by:

  • Inventory variant management UI.
  • Orders & pay links when selling specific variants.

Inventory photos & storage

  • inventory_photos – metadata and storage paths for product images.
  • Storage buckets & RLS policies (separate migrations) govern file access.

Repairs & pricing

repair_settings

Purpose: Per-tenant configuration for repair pricing calculators.

Key columns:

  • tenant_id
  • repair_type – e.g. ring-sizing-up, solder-chain, tighten-stones, prong-rebuild, rhodium-plating, polish-clean
  • pricing_mode – flat-rate or hourly-rate
  • Labor fields – base_labor_cost, labor_rate_per_hour, estimated_hours
  • Material fields – material_cost_per_gram, grams_per_size_up, grams_per_break, average_clasp_weight, grams_per_prong
  • Stone/prong/pricing fields – various *_cost, *_markup columns
  • Polish & rhodium minimums and complexity percentages
  • Markup/overrides – material_markup_percent, labor_markup_percent, complexity_multiplier, base_price, minimum_price

Used by:

  • /repairs/* calculators via useRepairSettings and calculateRepairPrice.

gold_prices

Purpose: Track latest gold price per gram.

Key columns:

  • price_per_gram
  • updated_at

Used by:

  • useRepairSettings to seed material cost logic for gold-related repairs.

Design & library

nameplates and nameplate_usage

Purpose: Store AI-generated nameplates and track usage.

Key columns:

  • nameplates
    • id, user_id, tenant_id
    • name, metal_type, purity, with_diamonds, font
    • image_url, storage_path, prompt_used
    • folder_id (from migration adding folder support)
    • Timestamps
  • nameplate_usage
    • user_id, tenant_id, nameplate_id
    • usage_month
    • Timestamps

Used by:

  • Library screen for combined file/nameplate gallery.
  • Future Nameplate Studio flows.

library_folders

Purpose: Folder organization for library content.

Key columns:

  • id, tenant_id
  • name, description
  • is_protected (from later migration)
  • created_by
  • Timestamps

Used by:

  • Library sidebar and folder selection.

library_files_new

Purpose: Metadata for uploaded library files (images, PDFs, CAD, etc.).

Key columns:

  • id
  • tenant_id, user_id
  • folder_id (nullable)
  • title, description
  • tags (text array)
  • file_name, file_size, mime_type
  • storage_path, image_url
  • Timestamps

Used by:

  • Library gallery.
  • Auto-generated invoice PDFs (stored here).

invoices and invoice_line_items

Purpose: Non-subscription billing records.

Key columns:

  • invoices
    • id, tenant_id
    • order_id, client_id
    • invoice_number (unique per tenant)
    • status (draft, sent, paid, overdue, cancelled)
    • issue_date, due_date
    • subtotal, tax_amount, discount_amount, total_amount
    • currency
    • pdf_url, pdf_storage_path
  • invoice_line_items
    • invoice_id
    • order_line_item_id
    • item_name, description
    • quantity, unit_price, line_total
    • position

Used by:

  • Invoice creation flow (/payments/invoice).
  • PDF generation and storage.

Purpose: Ad-hoc payment links separate from invoices.

Key columns:

  • pay_links
    • id, tenant_id
    • order_id, client_id
    • link_code, link_url
    • link_status (active, expired, paid, cancelled)
    • expires_at
    • payment_subtotal, payment_tax, payment_fee, payment_total
    • currency
    • recipient_email
    • link_description
    • gateway_payment_id, gateway_link_id
  • pay_link_line_items
    • pay_link_id
    • order_line_item_id, inventory_item_id, product_variation_id
    • item_name, description
    • quantity, unit_price, line_total
    • position

Used by:

  • Pay link creation flow (/payments/pay-link).
  • Future external gateway integrations.

Other supporting tables (high level)

There are additional tables supporting operations, feature flags, analytics, and system health. These include (non-exhaustive):

  • feature_flags – toggle features per tenant.
  • audit_logs – record significant actions.
  • support_tickets – track support issues.
  • api_keys – manage API credentials for integrations.
  • system_health – internal monitoring.
  • product_categories – inventory category metadata.
  • Various RLS and helper function migrations (functions_*).

For details on server-side logic that interacts with these tables (especially Stripe and Supabase-managed flows), see API & Data → Edge Functions.

How to use this section

  • Need to know where a field lives?
    Find the feature (e.g. repairs, orders, invoices) and use the table summary to jump into the right migration or schema.

  • Need to add a new feature?
    Identify whether it belongs in an existing table (e.g., new field on orders) or warrants a new table. Follow the conventions here (tenant scoping, RLS, created_at/updated_at).

  • Unsure about relationships?
    Cross-reference this page with: