Supabase Architecture¶

This page explains how Supabase is used as the backend for Workbench AI: schema, auth, row‑level security, and edge functions.

This is a high-level map, not a full schema dump. See the API/Data section for table-by-table details.

Supabase project & configuration¶

Supabase project configuration lives under the supabase/ directory:

• supabase/config.toml
  • Contains the project_id and CLI configuration.
  • Used by the Supabase CLI for local development and migrations.
• supabase/migrations/
  • Timestamped SQL files that define the Postgres schema and RLS policies.
  • Examples include:
    • 20250110000000_create_companies_and_user_profiles.sql
    • 20250110000001_add_tenant_id_to_tables.sql
    • 20250111000000_create_repair_settings.sql
• supabase/functions/
  • Edge Functions written in TypeScript, deployed to Supabase’s Deno runtime.
  • Handle Stripe webhooks, checkout/portal sessions, subscription sync, etc.

Local vs production configuration¶

• Local dev

  • Run via Supabase CLI (supabase start, supabase db reset, etc.).
  • Uses supabase/config.toml to connect to a local Postgres instance and apply migrations.
  • Local env vars (Supabase URL, anon key, service role key, Stripe keys) are set in the local environment (e.g. .env files or CLI config).

• Production

  • Managed Supabase project in the cloud.
  • Migrations from supabase/migrations/ are applied via CI/CD or Supabase migration tools.
  • Production env vars are configured in Supabase dashboard:
    • SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY
    • STRIPE_SECRET_KEY, STRIPE_WEBHOOK_SECRET, price IDs, etc.

High-level schema¶

The exact schema is defined by the SQL migrations, but at a high level the system is organized around:

• Companies / tenants

  • Table(s) representing each store/company (multitenancy).
  • Likely fields: id, name, contact info, plan, status.
  • Each company is the root of a tenant, and most business data is tied to a company ID.

• Users & profiles

  • auth.users (managed by Supabase Auth) holds base user accounts.
  • user_profiles table (referenced from migrations and RPC) stores:
    • id matching auth.users.id.
    • tenant_id / company_id linking the user to a company.
    • role (owner, tenant, admin).
    • Additional metadata (email, name, etc.).

• Subscriptions & billing mirror

  • subscriptions table mirrors Stripe subscription state:
    • company_id (FK to companies).
    • stripe_subscription_id, stripe_customer_id.
    • Status fields (active, trialing, canceled, etc.).
    • Plan information (starter/professional/enterprise).
  • webhook_events_processed table records processed Stripe webhook event IDs for idempotency.
  • Additional tables like payment_history or invoice tracking are maintained by edge functions.

• Inventory & catalog

  • Tables to represent inventory items, categories, and attributes.
  • Likely keyed by company_id with RLS enforcing tenant isolation.

• Orders & work orders

  • Tables for customer orders and work orders (repairs, custom pieces).
  • Status fields for workflow (active, completed, etc.).
  • Linked to clients and inventory items.

• Repairs & pricing configuration

  • repair_settings (from migrations) stores pricing defaults and rules.
  • Repair calculators in the frontend (ring sizing, soldering, etc.) read/write these settings.

• Content & AI artifacts

  • Tables for blog requests, library files, and certificates (from migrations like create_blog_requests_table, create_library_files_table, create_certificates).
  • Used by AI/automation flows for content generation and document storage.

Auth & roles¶

Supabase Auth is the identity layer; application roles and tenancy are layered on top.

Identity¶

• auth.users holds the canonical user identity (email, auth provider data).
• The frontend uses the Supabase JS client to sign in/out and read sessions.

Profiles and roles¶

• user_profiles table is linked 1:1 to auth.users (user_profiles.id = auth.users.id).
• It stores:
  • tenant_id / company_id – which company the user belongs to.
  • role – owner, tenant, or admin.

In the frontend:

• AuthProvider (React context) wraps the app and
  • Reads the Supabase session.
  • Calls a Postgres RPC (get_tenant_info_for_user) to fetch:
    • Role
    • Tenant/company ID
    • Company name
  • Caches some of this profile in localStorage for faster reloads.
• ProtectedRoute uses the role and helper functions (hasRequiredRole, isOwner) to gate routes.

Row-Level Security (RLS)¶

• RLS policies (defined in migrations) ensure that:
  • Regular users only access rows belonging to their tenant_id / company_id.
  • Owners/admins can see/manage data across their company.
• Edge Functions authenticate using the service role key, allowing them to bypass RLS when necessary (e.g., for cross-tenant operations or admin tasks).

Edge Functions¶

Edge Functions live under supabase/functions/ and are written in TypeScript targeting Deno.

Current functions include (one folder per function):

• stripe-webhook/

  • index.ts implements Stripe webhook handling.
  • Uses STRIPE_SECRET_KEY, STRIPE_WEBHOOK_SECRET, SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY.
  • Verifies signatures, ensures idempotency via webhook_events_processed, and updates subscription/payment tables.

• create-checkout-session/

  • Creates Stripe Checkout Sessions for subscriptions.
  • Ensures a Stripe customer exists for the company.
  • Uses environment variables for Stripe price IDs.
  • Returns a checkout.url for the frontend to redirect to.

• create-portal-session/

  • Creates Stripe Billing Portal sessions.
  • Looks up stripe_customer_id (or derives it from a subscription) and returns a portal URL.

• sync-stripe-subscriptions/

  • Keeps local subscriptions table in sync with Stripe data.

• sync-payment-history/

  • Synchronizes payment history records from Stripe into Supabase.

• get-stripe-subscription/, get-upcoming-invoice/, get-customer-invoices/

  • Read-only helpers for the frontend to fetch subscription and invoice details via secure backend calls.

• reactivate-subscription/

  • Handles reactivation flows for canceled or paused subscriptions.

• generate-nameplate/

  • Backend logic for the nameplate generator (part of the design studio).

• submit-affiliate-application/

  • Handles affiliate application submissions and persistence.

Data flow overview¶

flowchart LR
  U[User Browser] --> SPA[Frontend SPA]
  SPA -->|Supabase JS client| SBAUTH[Supabase Auth]
  SPA -->|queries/mutations| SBD[(Supabase DB)]
  SPA -->|HTTP calls| SBFX[Supabase Edge Functions]
  SBFX --> ST[Stripe]
  ST -->|webhooks| SBFX
  SBFX -->|DB writes| SBD

  click SPA "../architecture/frontend/" "Frontend architecture"
  click SBAUTH "../architecture/supabase/" "Auth & roles (this page)"
  click SBD "../api/supabase-tables/" "Supabase tables overview"
  click SBFX "../api/edge-functions/" "Edge functions API surface"
  click ST "../flows/billing-stripe/" "Billing & Stripe flows"

At this level:

• The frontend uses the Supabase client for most reads/writes.
• Edge Functions handle:
  • Stripe webhooks
  • Sensitive operations requiring service role access
  • Stripe session creation/portal access
• Stripe is the authoritative source of billing events, while Supabase stores a mirrored view for application logic.