Catalog and inventory tables

Reference

This page lists the catalog and inventory tables MaShop creates in your own Supabase project, with their purpose and key columns. Every name below comes from the migrations that ship with the backoffice template, applied to your database when you provision a project. All money is stored as bigint cents, never floats. For the full schema map see the schema overview; for orders, customers, coupons, and payments see commerce tables.

products

One row per sellable item. The merchant owns this table; the storefront reads it and the backoffice writes it.

Column	Type	Purpose
id	uuid PK	Generated by gen_random_uuid().
sku	text, unique	Stock-keeping unit at the product level. Nullable.
name	text, not null	Display name.
slug	text, unique, not null	URL segment. Indexed by idx_products_slug.
description	text	Long copy.
status	product_status	One of draft, active, archived. Defaults to draft.
product_type	product_type	One of simple, variable, subscription, digital, bundle, gift_card. Defaults to simple.
base_price_cents	bigint	Price in cents. Defaults to 0.
currency	text	Defaults to USD.
weight_grams	integer	For shipping rate calculation.
images	jsonb	Image gallery array. Defaults to [].
track_inventory	boolean	Decrement stock on sale. Defaults to true.
continue_selling	boolean	Allow ordering past zero stock (back-orders). Defaults to false.
created_at / updated_at	timestamptz	updated_at is maintained by the trg_products_updated_at trigger.

A later refonte added merchandising fields the product editor edits: subtitle, brand, origin, barcode, compare_at_price_cents (the struck-through price), cost_cents (unit cost), a tags text array, plus grouped jsonb columns specs, visibility, inventory, shipping, and related. Cross-border shipping fields length_mm, width_mm, height_mm, and hs_code were added alongside the typed inventory toggles.

Indexes exist on slug, sku, status, and product_type. Anonymous and authenticated roles can read a product only when status = 'active', via the public_read_products policy and a GRANT SELECT... TO anon.

Type-specific meta tables

Each non-simple product type stores its extra fields in a one-to-one meta table keyed by product_id. The row exists only when relevant.

• product_subscriptions_meta: billing_interval (day, week, month, year; defaults to month), interval_count, trial_days, max_cycles.
• product_digital_meta: file_url, license_key_pattern, download_limit, expires_after_days.
• product_bundle_meta: components jsonb array of bundled items.
• product_giftcard_meta: denominations_cents (a bigint[]), expires_after_days.
• product_seo: meta_title, meta_description, og_image_url, schema_jsonld, and SEO-score fields focus_keyword, secondary_keywords, og_title, og_description, canonical_url, is_indexed, is_followed.

product_variants

One row per buyable variation of a product (for example a size or color combination). A simple product can have a single variant; a variable product has many.

Column	Type	Purpose
id	uuid PK	Variant identifier.
product_id	uuid FK	References products(id); deletes cascade.
sku	text, unique	Per-variant stock-keeping unit.
name	text, not null	Variant label.
price_cents	bigint	Variant price in cents. Defaults to 0.
cost_cents	bigint	Per-variant unit cost.
stock_quantity	integer	A denormalized quantity on the variant row; the per-location source of truth lives in inventory_stock (see below).
option_values	jsonb	The chosen option values for this variant. Defaults to {}.
position	integer	Sort order.

Variants are indexed by product_id. The storefront can read a variant only when its parent product is active, enforced by the public_read_product_variants policy.

product_options and product_option_values

These two tables hold the option sets (for example "Size" with values "S", "M", "L") that feed the variant generator. product_options has product_id, name, and position; product_option_values has option_id, value, and position, with a unique constraint on (option_id, value). Generated combinations are stored back into product_variants.option_values.

attributes, attribute_values, product_attribute_values

Attributes are reusable facets and variant axes shared across products. The three tables separate the definition, the allowed values, and the per-product assignment.

attributes defines a facet: name, slug (unique), and type (one of list, multi, color, bool, range, text; defaults to list). Boolean flags control behavior: is_variant_axis (this attribute drives variants), is_facet (filterable on the storefront), is_required, and is_visible_on_product_page. Range attributes carry range_min, range_max, and range_step; a check constraint requires range_max > range_min when both are set.

attribute_values lists the allowed values for an attribute: attribute_id, value, optional label, swatch_hex (for color swatches), and position. The pair (attribute_id, value) is unique.

product_attribute_values assigns an attribute to a product. It carries product_id, attribute_id, an optional value_id (for list-type attributes), and free-form text_value or numeric_value for text, numeric, bool, and range attributes. A unique index treats a NULL value_id as a sentinel so the (product, attribute, value) triple stays unique even when value_id is absent.

categories, category_seo, product_categories

Categories form a tree and group products for navigation. categories carries parent_id (a self-reference; deleting a parent cascades to children), name, slug (unique), description, image_url, position, is_published (defaults to true), and in_menu (defaults to true). The category editor added accent_color (a preset id rendered as a gradient thumbnail when no image is set), is_indexed (include in sitemap), has_custom_page, and show_facets (show attribute filters on the category grid).

category_seo stores the per-category SEO override keyed by category_id: meta_title, meta_description, and og_image_url.

product_categories is the many-to-many join. Its primary key is (product_id, category_id), with a position column for ordering products within a category.

Categories are public-readable when is_published = true (the public_read_categories policy plus a grant to anon).

collections, product_collections

Collections are a flat, curated grouping that sits alongside the category tree. collections carries name, slug (unique), description, image_url, and is_published (defaults to true). product_collections is the join table with primary key (product_id, collection_id) and a position column. Collections are public-readable when is_published = true.

Inventory: locations, stock, movements

Inventory is multi-location and append-only-audited. Three tables work together.

inventory_locations is a warehouse or store: id, name, an address jsonb, is_default, and is_origin (the ship-from point for customs and rate calculation, distinct from default). The template seeds one row named Main warehouse with is_default = true so the inventory page is usable on day one.

inventory_stock holds the on-hand quantity per variant per location. Its primary key is (variant_id, location_id). Columns: quantity (defaults to 0) and reorder_threshold (defaults to 0) for low-stock alerts. The default-location seed backfills 50 units at reorder_threshold = 10 for every seeded variant.

inventory_movements is the audit log. Each row records a variant_id, location_id, a signed delta, an optional reason string, an optional ref_id (linking to the order or document that caused the change), and created_at. A check constraint enforces delta <> 0, so a zero-quantity movement can never be written.

adjust_stock RPC

Never write inventory_stock and inventory_movements separately. Call the adjust_stock RPC, which applies the delta and writes the audit row in one transaction.

SELECT * FROM adjust_stock(p_variant_id:= '...'::uuid,
 p_location_id:= '...'::uuid,
 p_delta:= -2,
 p_reason:= 'order MR-0001',
 p_ref_id:= NULL -- optional, defaults to NULL
);

The function is SECURITY DEFINER and runs require_staff_role('editor') first, so a caller without at least the editor staff role is rejected with forbidden: requires editor role. A zero delta raises delta must be non-zero. It upserts the inventory_stock row (creating it if absent), clamps the new quantity with GREATEST(0,...) so stock can never go negative, inserts the matching inventory_movements row, and returns the updated inventory_stock row. Execution is granted to the authenticated role.

Row Level Security

Every catalog and inventory table has RLS enabled. Staff with a role in staff_roles can read; only owner, admin, and editor roles can write. The public-read exceptions above (active products, active variants, published categories, published collections, and active-product SEO) are the only rows reachable by the anonymous storefront. For the policy details across all tables see the RLS reference, and for the full RPC list see RPCs.