Schema overview

Reference: the database schema behind every generated backoffice.

Every store you generate ships with a database. MaShop writes it into your own Supabase project from the migrations in templates/backend/supabase/migrations/. This page maps that schema at a high level: how many tables there are, how they group by domain, who can read and write each one, and the rules that hold across all of them. For the field-level breakdown of each domain, read Catalog tables, Commerce tables, and Content tables.

The number

The schema defines 52 tables, created across 22 migration files. The first migration, 20260516000001_init_universal_schema.sql, creates 36 of them in one transaction. Later migrations add 16 more. No migration drops a table, so the universal schema only grows.

The schema is universal: it carries no coffee, wine, or restaurant specifics. The same 52 tables back a physical-goods shop, a booking site, a subscription box, or a ticketed event. The AI specializes the surface and the seed data per business; the table set stays the same.

Tables by domain

The init migration files the tables under four headings: catalog, commerce, content, and a settings group. Later migrations slot into the same four.

Catalog (21 tables)

Everything you sell and how it is described, priced, stocked, and grouped.

• Products and type meta: products, product_variants, plus one meta table per product type that needs extra fields: product_subscriptions_meta, product_digital_meta, product_bundle_meta, product_giftcard_meta, product_service_meta.
• Variant options: product_options, product_option_values.
• Attributes and facets: attributes, attribute_values, product_attribute_values.
• Grouping: categories, category_seo, product_categories, collections, product_collections.
• Inventory: inventory_locations, inventory_stock, inventory_movements.
• Per-product SEO: product_seo.

The products.product_type column is an enum: simple, variable, subscription, digital, bundle, gift_card, and service. The matching product_*_meta table holds the fields only that type needs, so a simple product carries no subscription columns.

Commerce (15 tables)

Customers, orders, money movement, discounts, and shipping.

• Customers: customers, customer_segments, customer_segment_members.
• Orders: orders, order_items, order_status_transitions (an audit row per status change).
• Discounts: coupons, coupon_redemptions, product_coupons.
• Shipping: shipping_zones, shipping_methods, shipping_carriers.
• Payments: payment_providers, payment_transactions.
• Reviews: reviews.

The orders.number column defaults to a value generated from a Postgres sequence with an MR- prefix, so the first order reads MR-0001. The prefix is read from the settings table, so you can change it.

Content (5 tables)

The blog and its SEO.

• blog_categories, blog_posts, blog_post_seo, blog_post_tags, blog_comments.

Post bodies live in blog_posts.content_json as jsonb, not as an HTML string. See the blog editor for how that JSON is produced and rendered.

Settings, theme, SEO, and integrations (11 tables)

• Staff and config: staff_users, staff_roles, settings, branding.
• Analytics: analytics_events, ab_tests.
• SEO and integrations: marketing_page_seo, integrations, integration_connections, gsc_connection, gsc_metrics.

The settings table is a key/value store: a text key and a jsonb value. It seeds with currency set to "USD" and default_locale set to "en". Change a row to change the store default at runtime.

The staff role model

Access is built on four staff roles, defined by the staff_role enum:

Role	Rank	Can do
owner	4	Everything, including credentials and staff management.
admin	3	Everything except actions reserved to the owner; can manage payment credentials and staff.
editor	2	Read and write catalog, commerce, and content data.
viewer	1	Read-only across the dashboard.

A user's role lives in staff_roles.role, keyed to staff_users.id. Staff identity comes from MaShop single sign-on, not from this project's own auth: the 20260626000000 migration drops the foreign key from staff_users.id to auth.users so the signed hand-off can create the owner row.

The rank is enforced by a SECURITY DEFINER function, require_staff_role(min_role). It looks up the caller's role, compares the rank, and raises if it is too low:

-- caller has no staff row at all
RAISE EXCEPTION 'forbidden: not staff' USING ERRCODE = 'insufficient_privilege';

-- caller is staff but ranked below the required role
RAISE EXCEPTION 'forbidden: requires % role', min_role USING ERRCODE = 'insufficient_privilege';

Every write RPC calls require_staff_role before it touches a row, so a viewer who hits a privileged action gets one of these errors, not a partial write.

Row level security on every table

RLS is enabled on all 52 tables. Each one gets two base policies:

• staff_read (SELECT): any user with a row in staff_roles can read.
• staff_write (ALL): writers are gated by role.

Most write policies allow owner, admin, and editor. A smaller set restricts writes to owner and admin only, because they hold secrets or control access:

• staff_users and staff_roles (who can sign in and at what rank).
• payment_providers and integration_connections (encrypted credentials).
• integrations, branding, gsc_connection, gsc_metrics.

A handful of tables also carry a public read policy granted to the anon role, so the storefront can render without a login. Each is scoped:

Table	Public rows
products	status = 'active'
product_variants	parent product is active
product_seo	parent product is active
categories	is_published = true
collections	is_published = true
blog_posts	status = 'published'

A draft product, an unpublished category, or a draft post is invisible to anonymous visitors. Everything else, including customers, orders, and payment data, is staff-only and never reaches the anon role.