Skip to main content

✅ 1. How Metabase Works with Multi-Tenant Data

Metabase​

Image

Image

Image

Yes — I’m familiar with Metabase. It’s an open-source business intelligence (BI) / analytics tool that many teams use for dashboarding, self-service reporting, embedded analytics, and so on. Here’s a summary of what it is, its strengths/weaknesses, and whether it might be a good fit for your context (given your stack and goals).

✅ What it is​

Here are some of the key features of Metabase:

  • Connects directly to your data sources (databases, warehouses) without requiring an extract/ETL into its own store — you can query your production DB or a warehouse. (Metabase)
  • A visual/no-code “question” / query builder so non-technical users can explore data (filter, group, breakout) without writing raw SQL. (Metabase)
  • Also supports writing raw SQL when needed (for more advanced queries) via its SQL editor. (Metabase)
  • Dashboarding: you can build interactive dashboards, share them with teams, embed them in other apps (white-label) for customer-facing use. (Metabase)
  • Permissions, data segregation / multi-tenant support (important when you have many users/businesses or embed dashboards). (Metabase)
  • It supports “semantic layer” type modelling (defining metrics/fields) so you have a trusted layer of business logic. (Metabase)
  • It comes in a Cloud version and an open-source/self-hosted version (so you can host it yourself, which seems relevant given your GCP + Docker + monorepo stack). (Metabase)

⚠️ Strengths & Weaknesses (relative to your context)​

Given your stack and goals (you’re building a POS full-stack system for retail/restaurants, deployed on GCP, with modular microservices, multi-tenant, etc) here are how Metabase’s pros & cons might map:

Strengths

  • Because you want multi-tenant/business management software (you mentioned POS + business/inventory pages etc), embedding analytics for each business/customer might be valuable — Metabase supports embedded analytics and white-labeling, which is a strong plus.
  • If you have non-technical users (store-managers, business users) who need dashboards without involving your engineering team each time — Metabase’s self-serve capabilities are beneficial.
  • Since you are deploying on GCP/Docker etc, the self-hosted model gives you flexibility/control (you’re not locked into a fully managed SaaS if you prefer operating it yourself).
  • Open-source: you’ll avoid heavy licensing costs initially (especially relevant for startups/SMBs).
  • Good for quickly getting dashboards up and running, prototyping, iterating.

Weaknesses / things to watch

  • Performance & scale: if you have very large data volumes, complex queries, or high concurrency (many businesses, many users, real-time / sub-second dashboards) you’ll want to ensure Metabase’s architecture supports your scale (caching, query optimization, data warehouse choice).
  • Customization complexity: while embedding is supported, you may run into limitations with highly customized UI/UX, or very specific analytics workflows — you’ll need to evaluate how flexible its embedding/SDK layer is.
  • Multi-tenant complexity: you will likely need to design your data model to support business separation (segments, filters, scoped dashboards) and ensure you configure permissions correctly. Metabase supports this, but you’ll need to implement the multi-tenant logic (e.g., row-level security) on your side or via the DB.
  • Advanced analytics: If your needs go beyond dashboards (e.g., heavy predictive modelling, machine learning pipelines, real-time streaming analytics) then Metabase might be a part of the stack, but you’ll likely need additional tooling.
  • Maintenance: If self-hosting, you’ll need to handle upgrades, scaling, backup, etc — given your GCP deployment, this is probably fine, but worth keeping in mind.
  • Security/compliance: Since you’re building for retail/restaurants in Guatemala (and maybe beyond) you’ll want to ensure your deployment meets your data-governance, compliance, SLA, etc.

🎯 Fit for Your Use Case​

Given your context (POS/retail/restaurant software, modular monorepo, GCP deployment, multi-tenant, you want dashboards etc) I think Metabase could be a very good fit — here’s how you might reason about adopting it:

  • If you want to give your business-users an analytics portal (e.g., “business owner logs in and sees sales dashboard, inventory dashboard, by location, by product, by time period”) then Metabase is well suited.
  • If you want to embed analytics inside your product (for each business/customer) then Metabase’s embedded SDK/white-label features would let you integrate dashboards into your app.
  • If you expect moderate scale (say dozens to hundreds of businesses, each with their own data) rather than tens of thousands of extremely high-concurrency users, then self-hosting is likely manageable.
  • On the backend side: you’ll need to ensure your database model supports analytics (good schema design, maybe event tables, or transactions tables for POS) and that performance is acceptable. Metabase does not replace your data-warehouse; you still need a good data architecture.
  • Because you’re already using PostgreSQL (you mentioned) and have containers/GCP, you could spin up Metabase in a Docker container, connect it to your data warehouse/DB, and start prototyping quickly.

🛠 Implementation / Architecture Suggestions​

Here are some suggestions to consider when implementing Metabase in your system:

  • Data modelling: Make sure your POS/retail system stores data in a way that’s analytics-friendly: e.g., properly timestamped transactions, dimension tables for products, business, location, etc. Having a clean data model will make dashboards easier.

  • Row‐level security / multi-tenant: For each business (tenant) you will want to ensure a business only sees their own data. Options:

    • Use separate schemas or databases per business and configure Metabase to point accordingly.
    • Use a single database but include a “business_id” dimension on every relevant table and configure Metabase to use filters or segmenting so dashboards are scoped.
    • Use Metabase’s “data segregation” features. (Metabase)

  • Embedding: If you want to embed dashboards into your PWA or other frontend, explore Metabase’s SDK/embedding APIs (iframe or React SDK). (Metabase)

  • Caching / performance: If you have heavy queries, pre-aggregations or result caching might be needed. Metabase supports “result and model caching” to improve performance. (Metabase)

  • User roles/permissions: Define roles (admin, business-owner, manager, view-only) and configure in Metabase so each user sees appropriate dashboards/queries.

  • Deployment: Since you’re on GCP + Docker, you could use a containerised Metabase instance, possibly using Kubernetes or App Engine/GKE, with appropriate scaling and backups.

  • Integration with your workflow: Since your stack is modular (inventory, business, product, etc), you might design dashboard templates for each module (e.g., sales by product, inventory by location) and roll them out per business.

  • Training / adoption: Since Metabase aims at self-service, invest some time in onboarding your business-users: teach them how to use the “Ask a question”, filtering, drill-downs etc so they can derive insights without relying 100% on engineering.

📌 Summary​

In short: Yes, Metabase is a viable, well‐regarded BI tool and given your use case it looks like a strong candidate. Some key questions you should ask (and I can help you explore, if you like) are:

  • How many businesses/tenants do you expect, how many users/queries, what concurrency? Will the scale challenge Metabase’s performance?
  • What level of embedding/customization do you need (just dashboards vs deep integrated analytics)?
  • What are your data architecture needs (warehouse, ETL, pre-aggregations) to feed the BI tool effectively?
  • How do you plan to manage row-level security/multi-tenant isolation?
  • What are your compliance/security & operational requirements (backups, SLA, upgrade path)?

If you like, I can walk through a sample integration of Metabase with a Postgres + Docker + GCP environment (based on your monorepo setup) and map out how this could fit with your modules (business, inventory, product etc). Would you like me to do that?

Below is the clean, correct, and real-world way to integrate Metabase Open-Source with your full-stack multi-tenant POS (flowpos-workspace) using one shared PostgreSQL database where all tables include a business_id column.

This answer is fully adapted to your stack:

  • Backend: NestJS
  • Frontend: Next.js, React PWA, Flutter/React Native desktop
  • Database: PostgreSQL (Cloud SQL, GCP)
  • Deployment: Docker + Cloud Run
  • Multi-tenant model: Single DB, all tables contain business_id

You will see 1) Architecture, 2) Row-level security options, 3) Metabase configuration, 4) Embedding into your PWA, 5) Recommended best practices, and 6) Example implementation.

✅ 1. How Metabase Works with Multi-Tenant Data

Metabase supports three approaches to restricting data:

Option A — Database-level Row-Level Security (RLS)​

✔️ Most secure ✔️ Works automatically ✔️ Recommended for your POS system

Option B — Metabase "Data Sandboxing" (formerly “row filters”)​

✔️ Works without touching your DB ❌ Requires Metabase Enterprise (not available in OSS)

Option C — Separate DB connections per tenant​

❌ Not possible in your case (1 DB for all businesses)

👉 Since you're using the open-source version, the ONLY correct solution is: ✔ Use PostgreSQL Row-Level Security (RLS) + Metabase impersonation

🎯 2. The Right Solution: PostgreSQL RLS + Metabase DB Impersonation

This is the architecture used by 90% of SaaS companies with Metabase OSS.

✅ Step 1 — Create one PostgreSQL role per business (tenant)

Your DB already has a structure like this:

business
product
customer
sale
inventory
...

All tables include a column:

business_id UUID NOT NULL

Create a PG role for each business:

CREATE ROLE business_abc LOGIN PASSWORD 'strongpass';
CREATE ROLE business_xyz LOGIN PASSWORD 'anotherpass';

✅ Step 2 — Enable RLS on tables

For example, for your product table:

ALTER TABLE product ENABLE ROW LEVEL SECURITY;

Repeat for all tenant tables (sale, customer, inventory, etc.).

✅ Step 3 — Add RLS policies per business role

Example:

CREATE POLICY product_policy_per_business
ON product
FOR SELECT
USING (business_id = current_setting('app.current_business', true)::uuid);

You can reuse the same policy for all business roles.

✅ Step 4 — Inject the tenant ID using SET app.current_business

For each PG role:

ALTER ROLE business_abc SET app.current_business = 'e2c1d4a0-...';
ALTER ROLE business_xyz SET app.current_business = '79d12e2f-...';

This is the magic.

When a Metabase user connects with the "business_abc" PG user, PostgreSQL internally runs:

SET app.current_business = {their_business_id}

And due to RLS policies, they ONLY see their own data.

🎯 Why this works

✔ You do NOT modify Metabase OSS at all ✔ Metabase never sees another business's data ✔ Works with SQL and GUI questions ✔ Extremely secure ✔ Zero performance issues

🧩 3. How to Configure Metabase with RLS

Create a separate Postgres connection in Metabase per business​

Example:

Connection NamePG UserBusiness Allowed
Business ABC DBbusiness_abcOnly ABC
Business XYZ DBbusiness_xyzOnly XYZ

Each business will have:

  • Their own login to your app (your PWA)
  • Your backend generates a JWT for Metabase embedding (see next section)
  • That JWT specifies which DB connection to use

🚀 4. How to Embed Metabase in Your Next.js / React PWA

Metabase OSS supports JWT signing to embed dashboards:

Step A — Enable embedding​

In metabase.conf:

MB_EMBEDDING_SECRET_KEY="your-very-long-secret"

Step B — In your NestJS backend, generate a signed URL​

import jwt from "jsonwebtoken";

export function getMetabaseEmbeddedUrl({
  dashboardId,
  businessId,
  dbConnectionId,
}) {
  const payload = {
    resource: { dashboard: dashboardId },
    params: {},

    // VERY IMPORTANT — restrict to specific connection!
    exp: Math.round(Date.now() / 1000) + 10 * 60,
    database: dbConnectionId,
  };

  const token = jwt.sign(payload, process.env.METABASE_EMBED_SECRET);

  return `${process.env.METABASE_SITE_URL}/embed/dashboard/${token}#bordered=false&titled=false`;
}

You already have:

  • multi-tenant users
  • sessions
  • business_id
  • RBAC

So this backend endpoint automatically generates the correct dashboard for the tenant.

Step C — In React PWA, render the dashboard with an <iframe>​

export default function ReportsPage() {
  const { data } = useGetEmbeddedUrl(); // from backend

  return (
    <iframe
      src={data.url}
      style={{
        width: "100%",
        height: "100vh",
        border: "none",
      }}
    />
  );
}

🧩 5. Overview Architecture Diagram

Your PWA/Next.js App
         │
         ▼
     Your Backend (NestJS)
         │ Generates JWT embed URL
         ▼
   Metabase embedded dashboard
         │
         ▼
PostgreSQL with RLS enforcing business_id = X

🔐 6. Recommended Best Practices

✔ Create a Metabase database connection per tenant​

  • DB name always same
  • PG user is the tenant
  • RLS identifies the business

✔ Put Metabase behind Cloud Run with your VPC​

  • Whitelist access
  • Private internal endpoint if possible

✔ For SaaS multi-tenant​

Metabase OSS + PostgreSQL RLS is the industry-standard approach.

🎁 BONUS — How to automate RLS user creation (NestJS)

When a new business registers:

await db.execute(`
  CREATE ROLE business_${id} LOGIN PASSWORD '${password}';
  ALTER ROLE business_${id} SET app.current_business = '${businessId}';
`);

If you want, I can generate

✅ Cloud Run deployment YAML for Metabase ✅ Dockerfile optimized for Metabase in your infra ✅ NestJS service that generates embedded dashboard URLs ✅ Example RLS migrations for ALL your tables ✅ Multi-tenant database connection strategy for Metabase ✅ PWA React component for embedded analytics ✅ Automated Node script to provision tenant PG users + RLS policies

Want me to generate a full production-ready Metabase integration guide tailored to your flowpos-workspace monorepo?

Nice, let’s wire Metabase into flowpos-workspace properly 👇

I’ll give you a production-ready integration plan tailored to your stack and multi-tenant model (single DB with business_id).

0. Big Picture Architecture​

Your final setup will look like this:

PWA / Next.js / Desktop apps
        │   (user is logged in, has businessId)
        ▼
     NestJS backend
  `/reports/embed-url` endpoint
        │  (signs a Metabase embed JWT with businessId)
        ▼
   Metabase (Cloud Run)
        │  (runs dashboards / questions)
        ▼
PostgreSQL (Cloud SQL)
  (all tables have business_id)

We’ll do:

  1. Deploy Metabase on GCP (Cloud Run + Cloud SQL).
  2. Protect tenant data using business_id filters (and optionally RLS later).
  3. Generate signed Metabase embed URLs from NestJS.
  4. Embed dashboards in your PWA / web-app using <iframe>.

1. Running Metabase in Your Infra (GCP + Cloud SQL + Docker)​

1.1. Create Dockerfile​

Create a Dockerfile for Metabase in your deploy/gcp/ directory:

File: deploy/gcp/metabase.Dockerfile

FROM metabase/metabase:latest

# Metabase runs on port 3000 by default
EXPOSE 3000

# Health check for Cloud Run
HEALTHCHECK --interval=30s --timeout=10s --start-period=60s --retries=3 \
  CMD curl -f http://localhost:3000/api/health || exit 1

This follows the same pattern as your other Dockerfiles (backend.Dockerfile, web-app.Dockerfile, etc.).

1.2. Local Development with Docker Compose​

Your docker-compose.yml already includes a Metabase service (lines 147-162). To use it with your local PostgreSQL:

Update the Metabase service in docker-compose.yml:

metabase:
  image: metabase/metabase:latest
  ports:
    - "127.0.0.1:3002:3000" # Expose on port 3002 to avoid collision with web_app (3000)
  volumes:
    - metabase_data:/metabase-data
  environment:
    # Use PostgreSQL instead of H2 for local dev (optional but recommended)
    - MB_DB_TYPE=postgres
    - MB_DB_DBNAME=flowpos_dev
    - MB_DB_USER=flowpos
    - MB_DB_PASS=flowpos
    - MB_DB_HOST=postgres_dev
    - MB_DB_PORT=5432
    # Or use H2 for quick testing (current setup)
    # - MB_DB_TYPE=h2
    # - MB_DB_FILE=/metabase-data/metabase.db
  depends_on:
    postgres_dev:
      condition: service_healthy
  healthcheck:
    test: ["CMD", "curl", "-f", "http://localhost:3000/api/health"]
    interval: 15s
    timeout: 5s
    retries: 5

Access Metabase locally at: http://localhost:3002

1.3. Cloud Run + Cloud SQL Configuration​

Important: Metabase requires TWO separate database connections:

  1. Metabase Metadata Database (internal): Stores dashboards, questions, users, settings

    • Should be a separate small Cloud SQL instance (1 vCPU, 1-2 GB RAM)
    • Connection name: metabase-db (e.g., barto-prod:us-central1:metabase-db)

  2. Your App Database (for analytics): Your POS data with business_id columns

    • This is your existing Cloud SQL instance (e.g., flowpos-db-production)
    • Metabase connects to this after setup via the Metabase UI to create data sources

Key env vars for Metabase in Cloud Run:

For the Metabase metadata database (set in Cloud Run):

  • MB_DB_TYPE=postgres
  • MB_DB_DBNAME=metabase (or your chosen database name)
  • MB_DB_HOST=/cloudsql/<METABASE_DB_CONNECTION_NAME>
  • MB_DB_PORT=5432
  • MB_DB_USER=<metabase_db_user>
  • MB_DB_PASS=<metabase_db_password>
  • MB_SITE_URL=https://metabase.your-domain.com
  • MB_EMBEDDING_SECRET_KEY=<long-random-string> (must match backend secret)
  • JAVA_TOOL_OPTIONS=-Xms512m -Xmx1024m (tune based on load)

Setup Steps:

  1. Create a separate Cloud SQL instance for Metabase metadata (recommended):

    gcloud sql instances create metabase-db \
      --database-version=POSTGRES_15 \
      --tier=db-f1-micro \
      --region=us-central1 \
      --project=barto-prod

  2. Create a database and user:

    CREATE DATABASE metabase;
    CREATE USER metabase_user WITH PASSWORD 'strong-password';
    GRANT ALL PRIVILEGES ON DATABASE metabase TO metabase_user;

  3. Configure Cloud Run to connect via Cloud SQL connector (see GitHub Actions workflow below).

  4. After Metabase is running, connect it to your app database via the Metabase UI:

    • Go to Settings → Admin → Databases → Add Database
    • Use your app database connection string (same Cloud SQL instance or different)
    • This connection is stored in Metabase's metadata DB, not as env vars

Note: Using Doppler for secrets (recommended) - see GitHub Actions workflow below.

2. Multi-Tenant Data Isolation (Single DB, business_id)​

You have two realistic paths:

Option 1 (Recommended now): Embedding + Locked Params​

Start here - This is the simplest and most secure approach for embedded-only analytics.

  • Clients do not log into Metabase UI.
  • They only see embedded dashboards inside your app.
  • We enforce business_id in every question via a locked parameter.

Security logic:

  • Metabase questions always include WHERE business_id = {{business_id}}.

  • In the embed config, that parameter is:

    • hidden in the UI
    • locked so users can't change it

  • Your NestJS backend injects the correct value into the JWT as params.business_id = <user's businessId>.

🎯 Benefits:

  • Simple to implement
  • Works perfectly with Metabase OSS
  • No database changes required
  • Secure (users can't modify business_id)

Option 2 (Later / Advanced): PostgreSQL Row-Level Security (RLS)​

Future enhancement - Add this layer for defense-in-depth or if you want to allow native Metabase UI access.

  • Each tenant gets a DB role (tenant_abc, tenant_xyz).
  • Tables use RLS policies: business_id = <role's business>.
  • Metabase uses per-tenant DB connections (more setup & maintenance).

When to use RLS:

  • You want to allow customers direct access to Metabase UI (not just embedded)
  • You want database-level security as an additional layer
  • You need to ensure data isolation even if application logic fails

Migration Path:

  1. Start with Option 1 (locked params) - get analytics working
  2. Gradually enable RLS on non-critical tables
  3. Eventually enable RLS on all tables for defense-in-depth
  4. Option 1 and Option 2 can coexist - RLS adds an extra security layer

3. How to Design Questions/Dashboards in Metabase​

Once Metabase is running and connected to your Cloud SQL app DB:

3.1. Make business_id a required parameter​

For example, SQL question (Sales by day):

SELECT
  date_trunc('day', s.sale_date) AS day,
  SUM(s.total_amount) AS total_sales
FROM sale s
WHERE s.business_id = {{business_id}}
  AND s.sale_date BETWEEN {{start_date}} AND {{end_date}}
GROUP BY 1
ORDER BY 1;
  • Define business_id as a Number / ID / UUID param.
  • Define start_date, end_date as date parameters.

For GUI-built questions, you can use filters like [Filter: business_id] = {{business_id}}.

3.2. Lock & hide business_id for embedding​

In Metabase:

  1. Go to the dashboard → ⋮ → Embed this dashboard.

  2. In the embed config:

    • Mark business_id as locked.
    • Optionally mark it as hidden from the UI.

  3. Leave start_date, end_date as interactive (so user can change dates).

The embed JSON Metabase shows you will have a structure like:

{
  "resource": { "dashboard": 42 },
  "params": {
    "business_id": 123,
    "start_date": "2025-01-01",
    "end_date": "2025-01-31"
  }
}

In production, your NestJS backend will take care of filling these params.

4. Backend Integration (NestJS) – Generating Embed URLs​

You’ll add a MetabaseEmbeddingService to your NestJS backend.

4.1. Install JWT lib​

In your backend:

pnpm add jsonwebtoken
pnpm add -D @types/jsonwebtoken

4.2. Metabase config in your backend​

Example config (e.g. apps/backend/src/config/metabase.config.ts):

export const metabaseConfig = {
  siteUrl: process.env.METABASE_SITE_URL ?? "https://metabase.your-domain.com",
  secretKey: process.env.METABASE_EMBED_SECRET_KEY ?? "",
};

Make sure METABASE_EMBED_SECRET_KEY in NestJS matches Metabase’s env var.

4.3. MetabaseEmbeddingService​

// apps/backend/src/metabase/metabase-embedding.service.ts
import { Injectable } from "@nestjs/common";
import jwt from "jsonwebtoken";
import { metabaseConfig } from "../config/metabase.config";

interface EmbedParams {
  dashboardId: number;
  businessId: string; // uuid
  extraParams?: Record<string, any>;
}

@Injectable()
export class MetabaseEmbeddingService {
  getEmbeddedDashboardUrl({
    dashboardId,
    businessId,
    extraParams = {},
  }: EmbedParams): string {
    if (!metabaseConfig.secretKey) {
      throw new Error("METABASE_EMBED_SECRET_KEY is not configured");
    }

    // This payload shape matches Metabase embed spec
    const payload = {
      resource: { dashboard: dashboardId },
      params: {
        business_id: businessId,
        ...extraParams,
      },
      exp: Math.round(Date.now() / 1000) + 10 * 60, // 10 minutes
    };

    const token = jwt.sign(payload, metabaseConfig.secretKey);

    return `${metabaseConfig.siteUrl}/embed/dashboard/${token}#bordered=false&titled=false`;
  }
}

4.4. NestJS Controller endpoint​

Example: GET /reports/dashboard/:dashboardId/embed-url

Important: This matches your existing authentication pattern using Firebase tokens and RolesGuard:

// apps/backend/src/metabase/metabase.controller.ts
import { Controller, Get, Param, Query, Req, BadRequestException } from "@nestjs/common";
import { UseGuards } from "@nestjs/common";
import { AuthGuard } from "@/auth/infrastructure/auth.guard";
import { RolesGuard } from "@/roles/infrastructure/roles.guard";
import { MetabaseEmbeddingService } from "./metabase-embedding.service";
import type { RequestWithCookiesAndFirebaseUser } from "@/auth/infrastructure/auth.types";

@Controller("reports")
@UseGuards(AuthGuard, RolesGuard)
export class MetabaseController {
  constructor(
    private readonly metabaseEmbeddingService: MetabaseEmbeddingService,
  ) {}

  @Get("dashboard/:dashboardId/embed-url")
  async getDashboardEmbedUrl(
    @Param("dashboardId") dashboardId: string,
    @Query("businessId") businessId?: string,
    @Req() req?: RequestWithCookiesAndFirebaseUser,
  ) {
    // Extract businessId from query params (matches your RolesGuard pattern)
    // Or from request body/params if passed that way
    const tenantBusinessId = businessId || req?.body?.businessId || req?.params?.businessId;

    if (!tenantBusinessId) {
      throw new BadRequestException("businessId is required");
    }

    const url = this.metabaseEmbeddingService.getEmbeddedDashboardUrl({
      dashboardId: Number(dashboardId),
      businessId: tenantBusinessId,
      extraParams: {
        // You can set default dates here if you want:
        // start_date: "2025-01-01",
        // end_date: "2025-01-31",
      },
    });

    return { url };
  }
}

Usage: Your frontend calls this endpoint with the businessId query parameter:

GET /reports/dashboard/42/embed-url?businessId=<uuid>

This aligns with your existing auth flow where businessId is extracted from request params/query/body in RolesGuard.

5. Frontend Integration (PWA / Next.js / Desktop)​

Your React PWA & Next.js app will:

  1. Call your backend for /reports/dashboard/:id/embed-url.
  2. Get back { url }.
  3. Render an <iframe src={url} />.

5.1. Simple React component (PWA or Next.js)​

Important: This component needs to pass the businessId to match your backend's expected pattern:

// apps/frontend-pwa/src/features/reports/MetabaseDashboardFrame.tsx
import { useEffect, useState } from "react";
import { useQuery } from "@tanstack/react-query"; // or your data fetching library

interface Props {
  dashboardId: number;
  businessId: string; // UUID of the current business
}

export function MetabaseDashboardFrame({ dashboardId, businessId }: Props) {
  const [url, setUrl] = useState<string | null>(null);

  useEffect(() => {
    const fetchUrl = async () => {
      // Pass businessId as query parameter (matches your backend pattern)
      const res = await fetch(
        `/api/reports/dashboard/${dashboardId}/embed-url?businessId=${businessId}`,
        {
          credentials: "include", // Include cookies for Firebase auth
          headers: {
            // Your auth headers if needed
          },
        }
      );
      
      if (!res.ok) {
        throw new Error("Failed to fetch embed URL");
      }
      
      const data = await res.json();
      setUrl(data.url);
    };

    fetchUrl();
  }, [dashboardId, businessId]);

  if (!url) {
    return (
      <div className="flex items-center justify-center h-[80vh]">
        <div>Loading report…</div>
      </div>
    );
  }

  return (
    <div className="w-full h-[80vh]">
      <iframe
        src={url}
        className="w-full h-full"
        style={{ border: "none" }}
        allowTransparency
        title={`Metabase Dashboard ${dashboardId}`}
      />
    </div>
  );
}

Usage in your app:

// Example: In a reports page
import { MetabaseDashboardFrame } from "@/features/reports/MetabaseDashboardFrame";
import { useBusiness } from "@/hooks/useBusiness"; // Your business context hook

export function ReportsPage() {
  const { currentBusinessId } = useBusiness(); // Get current business from your context
  
  if (!currentBusinessId) {
    return <div>Please select a business</div>;
  }

  return (
    <div>
      <h1>Sales Dashboard</h1>
      <MetabaseDashboardFrame 
        dashboardId={42} // Your Metabase dashboard ID
        businessId={currentBusinessId}
      />
    </div>
  );
}

For Next.js: You can proxy /api/reports/... to your backend or hit it directly via env-configured base URL (e.g., process.env.NEXT_PUBLIC_API_URL).

5.2. Desktop (Flutter / React Native)​

  • For Flutter: use WebView widget pointing to the same embed URL.
  • For React Native: use react-native-webview.

Your flow is the same:

  1. Mobile app calls your API to get the embed URL.
  2. Renders it in a WebView.

6. Where This Fits in Your Monorepo​

You can keep it clean like this:

apps/
  backend/
    src/
      metabase/
        metabase-embedding.service.ts
        metabase.controller.ts
      config/
        metabase.config.ts
  frontend-pwa/
    src/features/reports/MetabaseDashboardFrame.tsx
  web-app/        # if you want admin reports in here as well
    src/features/reports/MetabaseDashboardFrame.tsx

packages/
  global/
    src/
      types/
        user.ts        # user includes businessId

Env management:

  • Add METABASE_SITE_URL and METABASE_EMBED_SECRET_KEY to Doppler for:

    • backend (so NestJS can sign)
    • metabase Docker/Cloud Run service (so Metabase uses same secret)

7. Hardening / Next Steps Checklist​

Once basic embedding works:

✅ Immediate Steps​

  1. Lock & hide business_id for all dashboards used in embedding.

    • In Metabase UI: Dashboard → ⋮ → Embed this dashboard
    • Mark business_id as locked and hidden

  2. Create a Metabase "App Admin" user that you use to build content

    • Do NOT share this with customers
    • Use strong password + 2FA
    • This user creates dashboards and questions

  3. Set tracking to false in production:

    # Add to Cloud Run env vars
    MB_ANON_TRACKING_ENABLED=false

  4. Secure Metabase access:

    • Option A: Put behind internal domain (e.g., metabase.internal.flowpos)
    • Option B: IP-restrict ingress (only allow your backend IPs)
    • Option C: Use Cloud Run authentication (require IAM)
    • Recommended: Use Cloud Run IAM + only allow your backend service account

  5. Verify JWT secret matches:

    • Ensure METABASE_EMBED_SECRET_KEY in Metabase Cloud Run
    • Matches METABASE_EMBED_SECRET_KEY in your NestJS backend Doppler config
    • Test embedded dashboard loads correctly

🔒 Security Best Practices​

  1. Never expose Metabase UI to customers:

    • Only use embedded dashboards
    • Customers should never have Metabase login credentials
    • All access goes through your backend → JWT signing → embed URL

  2. Monitor Metabase logs:

    • Set up Cloud Logging alerts for failed authentication
    • Monitor query performance
    • Watch for unusual access patterns

  3. Regular backups:

    • Backup Metabase metadata database (Cloud SQL automated backups)
    • Export dashboard definitions as JSON (Metabase UI → Settings → Export)

🚀 Future Enhancements​

  1. Optionally: Introduce Postgres RLS later if:

    • You want database-level defense in depth
    • You ever allow clients into the full Metabase UI
    • You need additional security guarantees

  2. Performance optimization:

    • Enable query caching in Metabase
    • Set up result caching for frequently accessed dashboards
    • Monitor Cloud Run metrics and adjust CPU/memory as needed

If you want, next step I can:

  • Draft a GitHub Actions job that deploys Metabase to Cloud Run (similar style to your existing backend/web-app actions).
  • Sketch the SQL template for RLS (Option 2) so you can gradually migrate critical tables later without changing your embedding flow.

Great question — and it’s a very important architectural decision when building a multi-tenant SaaS BI environment on GCP.

Here is the correct, scalable, and industry-standard way to deploy Metabase OSS for your scenario (multi-tenant POS with ~hundreds of businesses, single DB with business_id).

I’ll give you:

  1. Recommended Infrastructure
  2. Whether you need per-client containers
  3. When Kubernetes is needed
  4. How to scale Metabase properly
  5. Example architecture diagrams
  6. Limits & best practices

Let’s go step by step.

✅ 1. Recommended Infrastructure for Metabase OSS on GCP

You only need one deployment of Metabase.

The recommended stack is:

GCP Cloud Run
    └── Metabase (Docker container)
Cloud SQL (Postgres)
    └── App DB with multi-tenant schema
GCS bucket (optional)
    └── Metabase logs / export storage
Secret Manager or Doppler
    └── Metabase secrets (MB_EMBED_SECRET_KEY)

✔ One Metabase container​

✔ One Cloud Run service​

✔ One Cloud SQL database cluster​

✔ Many tenants isolated by business_id (via parameter locking or RLS)​

You do NOT need: ❌ A container per client ❌ A VM per client ❌ A Kubernetes deployment per client

Metabase is designed to be multi-tenant at the application level, NOT at the container level.

🎯 Why a single container works?

Metabase does NOT store tenant data.

It only:

  • Connects to your DB (Cloud SQL)
  • Runs queries
  • Renders dashboards
  • Applies filtering based on your embedding parameters (business_id)
  • Uses PostgreSQL to isolate data (via query or RLS)

This is lightweight. All multi-tenancy happens at:

  • The DB layer (RLS or filters)
  • The backend (JWT embedding)
  • Not the Docker container

⚡ 2. Should you create a Docker container per client?

❌ No. Never​

This is a common beginner misunderstanding.

If you had 200 clients:

  • 200 Docker containers?
  • 200 Cloud Run services?
  • 200 Cloud SQL connections?

It would be:

  • Expensive
  • Slow
  • Impossible to manage
  • No benefit

One Metabase can securely serve unlimited tenants as long as row-level filtering is correctly applied.

🚀 3. Should you run Metabase on Kubernetes (GKE)?

✔ Only if you have VERY high load​

Examples:

  • Tens of millions of BI queries per month
  • Thousands of concurrent heavy SQL queries
  • Complex dashboards with subqueries
  • Need custom autoscaling based on CPU
  • Need custom plugins or custom image patches

But for 99% of SaaS apps:

❌ GKE = unnecessary complexity​

✔ Cloud Run = best option​

Reasons:

  • Simple deployment
  • Autoscaling built-in
  • Fast container rollouts
  • Easy integration with Cloud SQL
  • Zero maintenance
  • Good for low to medium BI traffic
  • Cheap (Metabase only consumes CPU when queried)

Kubernetes (GKE) is useful only when:

  • You outgrow Cloud Run concurrency limits
  • You need multi-node scaling
  • You have massive BI load

This rarely happens until you reach thousands of clients.

⚙️ 4. Scaling Metabase on Cloud Run (Best Practice)

🔸 Cloud Run: Autoscaling​

Set Cloud Run service config:

--min-instances=1   # avoid cold starts
--max-instances=10  # allow autoscaling
--concurrency=16    # default, adjust later

Metabase is a JVM app, so you want at least:

CPU: 1 vCPU
Memory: 1–2 GB

If dashboards get complex:

CPU: 2 vCPU
Memory: 4 GB

Cloud Run automatically scales out horizontally.

🧠 5. Diagram: Recommended Architecture (Multi-Tenant SaaS)

          Your Frontend (PWA / Next.js)
                       │
                🔒 User Auth (business_id)
                       │
                       ▼
               Your NestJS Backend
                       │ (creates Metabase embed token)
                       ▼
                 Metabase (Cloud Run)
            ┌────────────────────────────────┐
            │ Single container / single URL  │
            │ No per-tenant deployments      │
            └────────────────────────────────┘
                       │
                       ▼
             Cloud SQL Postgres (Shared)
                   │ business_id filter
                   ▼
        All tenant data securely separated

🧱 6. Should you separate the Metabase metadata DB?

Yes — strongly recommended. The metadata DB stores:

  • Dashboards definitions
  • Saved questions/queries
  • Parameters and filters
  • Audit logs
  • User accounts (if you use Metabase auth)
  • Card definitions
  • Database connection configurations
  • Human-readable metadata

Use a separate small Cloud SQL instance for Metabase metadata (1 vCPU, 1–2 GB RAM).

Why separate instances?

  • Performance isolation: Avoid noisy neighbor issues with your production POS database
  • Security: Protect your production DB from Metabase schema introspection queries
  • Cleaner backups: Separate backup schedules and retention policies
  • Easier migrations: Metabase upgrades won't affect your app database
  • Cost optimization: Small instance for metadata, larger instance for app data

Architecture:

Cloud SQL Instance 1: Metabase Metadata DB (small)
  └── Stores: dashboards, questions, users, settings

Cloud SQL Instance 2: Your App Database (existing)
  └── Stores: business, product, sale, inventory, etc.
  └── Metabase connects to this AFTER setup via UI

Important:

  • The metadata DB is configured via Cloud Run env vars (see GitHub Actions workflow)
  • Your app database is connected after Metabase is running via the Metabase UI (Settings → Databases → Add Database)
  • Both connections use Cloud SQL connectors, but they're separate instances

📌 7. Real limits to consider

Metabase OSS has no user isolation features beyond embedding.

So:

  • If clients access the Metabase UI directly → they can theoretically see other businesses
  • Solution: DO NOT give clients access to the Metabase UI
  • Use embedded dashboards only
  • Control all parameters from your backend

This solves all security concerns.

🧰 8. TL;DR: The correct setup for your POS SaaS

✔ One Metabase container​

✔ One Cloud Run service​

✔ One Cloud SQL database​

✔ Lock business_id via embed parameters​

✔ Never expose Metabase UI to customers​

✔ Backend generates signed embed URLs​

✔ Autoscaling via Cloud Run​

✔ Logs + metrics via Cloud Logging + Monitoring​

✔ Optional: RLS later for DB-level security​

NO per-client containers NO Kubernetes needed initially

Your setup is multi-tenant via DB logic, not infrastructure.

⭐ Want me to generate the full Terraform + Docker + Cloud Run YAML + SQL RLS policies for your POS setup?

I can create:

  • Complete GCP Terraform for Metabase deployment
  • Cloud Run deployment script
  • Doppler secret configuration
  • SQL migrations for RLS across all your tables
  • Example NestJS backend module
  • Example embedding component for your PWA
  • CI/CD pipeline for Metabase via GitHub Actions

Just tell me: “Yes, generate the full deployment pack.”

Perfect — I’ll generate both items you asked for:

1️⃣ GitHub Actions job to deploy Metabase to Cloud Run, following the style of your existing workflows (deploy-staging.yml, deploy-production.yml, premerge-validate.yml). 2️⃣ PostgreSQL RLS SQL template, aligned with your POS schema (all tables using business_id) so you can gradually migrate critical tables without breaking your embedding flow.

And since you told me you have two GCP projects:

  • barto-dev → staging
  • barto-prod → production

…I will generate two workflows, each using the correct GCP project.

✅ Part 1 — GitHub Actions Workflow: Deploy Metabase to Cloud Run

This is a clean, production-ready workflow consistent with your monorepo and Cloud Run patterns.

Prerequisites​

Before using the workflow, create the required files:

1. Create Metabase Dockerfile​

File: deploy/gcp/metabase.Dockerfile

FROM metabase/metabase:latest

# Metabase runs on port 3000 by default
EXPOSE 3000

# Health check for Cloud Run
HEALTHCHECK --interval=30s --timeout=10s --start-period=60s --retries=3 \
  CMD curl -f http://localhost:3000/api/health || exit 1

2. Create GitHub Actions Workflow​

File: .github/workflows/deploy-metabase.yml

Here is the complete workflow:

🚀 GitHub Actions: .github/workflows/deploy-metabase.yml​

This workflow follows your existing patterns:

  • PR-based deployment (triggers on merged PRs)
  • Uses Doppler for secrets (like your other workflows)
  • Path filters to only deploy when Metabase files change
  • Separate staging/production environments
name: Deploy Metabase

on:
  pull_request:
    types:
      - closed
    branches:
      - main       # deploy to production
      - develop    # deploy to staging

permissions:
  id-token: write
  contents: read
  pull-requests: read

env:
  GAR_LOCATION: us-central1
  SERVICE_NAME: metabase
  REGION: us-central1
  DOCKERFILE_PATH: deploy/gcp/metabase.Dockerfile

jobs:
  setup:
    name: Setup Environment
    runs-on: ubuntu-latest
    if: github.event.pull_request.merged == true
    outputs:
      version: ${{ steps.version.outputs.version }}
      metabase_changed: ${{ steps.filter.outputs.metabase_changed }}
      environment: ${{ steps.set-env.outputs.ENVIRONMENT }}
      project_id: ${{ steps.set-env.outputs.PROJECT_ID }}

    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Set PNPM version (with fallback)
        run: echo "PNPM_VERSION=${{ secrets.PNPM_VERSION || '10.8.0' }}" >> $GITHUB_ENV

      - uses: pnpm/action-setup@v2
        with:
          version: ${{ env.PNPM_VERSION }}

      - name: Calculate version
        id: version
        run: echo "version=${{ github.event.pull_request.base.ref }}-${{ github.sha }}" >> $GITHUB_OUTPUT

      - name: Set PROJECT_ID & ENVIRONMENT
        id: set-env
        run: |
          if [[ "${{ github.event.pull_request.base.ref }}" == "main" ]]; then
            echo "PROJECT_ID=barto-prod" >> $GITHUB_OUTPUT
            echo "ENVIRONMENT=production" >> $GITHUB_OUTPUT
          else
            echo "PROJECT_ID=barto-dev" >> $GITHUB_OUTPUT
            echo "ENVIRONMENT=staging" >> $GITHUB_OUTPUT
          fi

      - name: Filter changed files by paths
        id: filter
        uses: dorny/paths-filter@v3
        with:
          filters: |
            metabase_changed:
              - 'deploy/gcp/metabase.Dockerfile'
              - '.github/workflows/deploy-metabase.yml'
              - 'docs/metabase/**'

  deploy-metabase:
    name: Deploy Metabase
    needs: setup
    runs-on: ubuntu-latest
    if: ${{ github.event.pull_request.merged == true && needs.setup.outputs.metabase_changed == 'true' }}
    environment: ${{ needs.setup.outputs.environment }}

    env:
      PROJECT_ID: ${{ needs.setup.outputs.project_id }}
      REGION: ${{ env.REGION }}
      ENVIRONMENT: ${{ needs.setup.outputs.environment }}

    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Authenticate to Google Cloud via WIF
        uses: google-github-actions/auth@v2
        with:
          token_format: "access_token"
          workload_identity_provider: ${{ secrets.GCP_WORKLOAD_IDENTITY_PROVIDER }}
          service_account: ${{ secrets.GCP_SERVICE_ACCOUNT_EMAIL }}

      - name: Set up Google Cloud SDK
        uses: google-github-actions/setup-gcloud@v2

      - name: Configure Docker for Artifact Registry
        run: gcloud auth configure-docker ${{ env.REGION }}-docker.pkg.dev

      - name: Download secrets from Doppler
        uses: dopplerhq/github-action@v4
        with:
          doppler_token: ${{ secrets.DOPPLER_TOKEN }}
          project: flowpos-workspace
          config: ${{ env.ENVIRONMENT }}

      - name: Build and Push Docker Image
        env:
          DOPPLER_TOKEN: ${{ secrets.DOPPLER_TOKEN }}
        run: |
          set -euo pipefail
          
          # Get image URI
          IMAGE_URI=${{ env.REGION }}-docker.pkg.dev/${{ env.PROJECT_ID }}/flowpos-backend/metabase:${{ github.sha }}
          
          # Build Docker image
          docker build \
            -t $IMAGE_URI \
            -f ${{ env.DOCKERFILE_PATH }} .
          
          # Push to Artifact Registry
          docker push $IMAGE_URI
          
          echo "IMAGE_URI=$IMAGE_URI" >> $GITHUB_ENV

      - name: Deploy Metabase to Cloud Run
        env:
          DOPPLER_TOKEN: ${{ secrets.DOPPLER_TOKEN }}
        run: |
          set -euo pipefail
          
          # Load Metabase secrets from Doppler
          export $(doppler secrets download --no-file --format env --project flowpos-workspace --config ${{ env.ENVIRONMENT }} | grep -E '^(MB_|METABASE_)')
          
          # Build deployment command
          GCLOUD_CMD=(
            gcloud run deploy ${{ env.SERVICE_NAME }}
            --image="${{ env.IMAGE_URI }}"
            --region="${{ env.REGION }}"
            --platform=managed
            --project="${{ env.PROJECT_ID }}"
            --memory=2Gi
            --cpu=1
            --timeout=300
            --concurrency=16
            --min-instances=1
            --max-instances=5
            --port=3000
            --allow-unauthenticated
            --set-env-vars="MB_DB_TYPE=postgres"
            --set-env-vars="MB_DB_DBNAME=${METABASE_DB_NAME}"
            --set-env-vars="MB_DB_USER=${METABASE_DB_USER}"
            --set-env-vars="MB_DB_PASS=${METABASE_DB_PASS}"
            --set-env-vars="MB_DB_HOST=/cloudsql/${METABASE_DB_CONNECTION}"
            --set-env-vars="MB_SITE_URL=${METABASE_SITE_URL}"
            --set-env-vars="MB_EMBEDDING_SECRET_KEY=${METABASE_EMBED_SECRET_KEY}"
            --set-env-vars="JAVA_TOOL_OPTIONS=-Xms512m -Xmx1536m"
            --add-cloudsql-instances="${METABASE_DB_CONNECTION}"
          )
          
          "${GCLOUD_CMD[@]}"

      - name: Deployment Summary
        run: |
          echo "✅ Metabase deployed successfully"
          echo "Environment: ${{ env.ENVIRONMENT }}"
          echo "Project: ${{ env.PROJECT_ID }}"
          echo "Image: ${{ env.IMAGE_URI }}"

🔑 Required Secrets (Doppler Configuration)​

Add these secrets to Doppler for staging and production configs:

🔐 GCP / Infra Secrets (already exist in your workflows)​

  • GCP_WORKLOAD_IDENTITY_PROVIDER - Already configured
  • GCP_SERVICE_ACCOUNT_EMAIL - Already configured
  • DOPPLER_TOKEN - Already configured

🔐 Metabase Metadata Database (Cloud SQL)​

These connect Metabase to its internal metadata database:

  • METABASE_DB_NAME - Database name (e.g., metabase)
  • METABASE_DB_USER - Database user (e.g., metabase_user)
  • METABASE_DB_PASS - Database password
  • METABASE_DB_CONNECTION - Cloud SQL connection name
    • Staging: barto-dev:us-central1:metabase-db
    • Production: barto-prod:us-central1:metabase-db

🔐 Metabase Application Config​

  • METABASE_SITE_URL - Public URL where Metabase is accessible
    • Staging: https://metabase-staging.your-domain.com
    • Production: https://metabase.your-domain.com
  • METABASE_EMBED_SECRET_KEY - CRITICAL: Must match the secret in your NestJS backend
    • Generate with: openssl rand -base64 32
    • Add to both backend and metabase Doppler configs

📝 Adding Secrets to Doppler​

# For staging
doppler secrets set METABASE_DB_NAME=metabase --project flowpos-workspace --config staging
doppler secrets set METABASE_DB_USER=metabase_user --project flowpos-workspace --config staging
doppler secrets set METABASE_DB_PASS="your-secure-password" --project flowpos-workspace --config staging
doppler secrets set METABASE_DB_CONNECTION="barto-dev:us-central1:metabase-db" --project flowpos-workspace --config staging
doppler secrets set METABASE_SITE_URL="https://metabase-staging.your-domain.com" --project flowpos-workspace --config staging
doppler secrets set METABASE_EMBED_SECRET_KEY="<generated-secret>" --project flowpos-workspace --config staging

# For production (repeat with production config)
doppler secrets set METABASE_DB_NAME=metabase --project flowpos-workspace --config production
# ... etc

Important: The METABASE_EMBED_SECRET_KEY must be identical in:

  1. Metabase Cloud Run service (used to validate JWT tokens)
  2. Your NestJS backend (used to sign JWT tokens)

This ensures embedded dashboards work correctly.

✅ Part 2 — SQL Template for RLS

(Safe to add gradually over time, without breaking embedding logic)

This template assumes:

  • All your tables have a business_id UUID column.
  • You will eventually create one PostgreSQL role per tenant.
  • RLS ensures that role X only sees business_id = X.

2.1 Enable RLS for a table​

Example: sale

ALTER TABLE sale ENABLE ROW LEVEL SECURITY;

2.2 Create a generic policy per table​

You will apply this template to every important table:

CREATE POLICY multitenant_policy ON sale
FOR SELECT USING (
  business_id = current_setting('app.current_business', true)::uuid
);

You can put this in a reusable SQL script:

packages/backend/database/sql/rls/sale.sql

2.3 Assign the tenant context (magic part)​

When you create a tenant, assign its business ID:

ALTER ROLE tenant_abc SET app.current_business = 'uuid-of-business';

Now PostgreSQL automatically filters by business.

CREATE POLICY multitenant_insert_policy ON sale
FOR INSERT WITH CHECK (
  business_id = current_setting('app.current_business', true)::uuid
);

2.5 Template to add RLS for ALL tables​

-- Enable RLS
ALTER TABLE {{table}} ENABLE ROW LEVEL SECURITY;

-- Select
CREATE POLICY {{table}}_select_policy ON {{table}}
  FOR SELECT USING (
    business_id = current_setting('app.current_business', true)::uuid
  );

-- Insert
CREATE POLICY {{table}}_insert_policy ON {{table}}
  FOR INSERT WITH CHECK (
    business_id = current_setting('app.current_business', true)::uuid
  );

-- Update
CREATE POLICY {{table}}_update_policy ON {{table}}
  FOR UPDATE USING (
    business_id = current_setting('app.current_business', true)::uuid
  );

-- Delete (optional!)
CREATE POLICY {{table}}_delete_policy ON {{table}}
  FOR DELETE USING (
    business_id = current_setting('app.current_business', true)::uuid
  );

🎯 Deployment Strategy for RLS

To avoid impact:

Step 1​

Enable RLS only on small / low-risk tables:

  • unit_of_measure
  • currency
  • style
  • brand

Step 2​

Enable RLS on operational tables:

  • product
  • inventory
  • location

Step 3 (final & most critical)​

Enable RLS on financial tables:

  • sale
  • purchase
  • accounts_receivable
  • accounts_payable

This approach ensures stability.

🎁 If you want, I can also generate

✔ Cloud Run Terraform for Metabase​

✔ Dockerfile for Metabase with healthcheck​

✔ A NestJS “MetabaseModule” (plug-and-play)​

✔ A PWA “ReportsPage” with dynamic params​

✔ A script to auto-provision tenants + PG roles + RLS policies​

Just tell me: “Generate the full Terraform + MetabaseModule + ReportsPage pack.”

Below is a full, deep, production-grade explanation of the recommended Metabase architecture on Google Cloud Platform, exactly adapted to your multi-tenant POS SaaS (flowpos-workspace).

I will break it into clear sections so you understand why, how, and when each component matters.

✅ 1. GCP Cloud Run — The Best Place to Run Metabase

Metabase runs as a single Docker container.

Cloud Run is ideal because:

✔ Fully managed​

No servers, no patching, no VM updates.

✔ Autoscaling​

  • If 1 user views a dashboard → it runs with minimum instances.
  • If 200 users hit reports → Cloud Run scales horizontally.

✔ Pay only for CPU time​

Metabase can sit idle (1 instance) or scale up automatically.

✔ Built-in Cloud SQL secure connector​

You don’t need VPC connectors or public IPs.

🔧 Cloud Run Settings for Metabase​

CPU & RAM recommendations​

For normal load:

CPU: 1 vCPU
RAM: 1–2 GB

For heavier dashboards:

CPU: 2 vCPU
RAM: 4 GB

Metabase is Java-based → memory matters.

Autoscaling parameters​

min-instances = 1   # avoid cold starts
max-instances = 10  # handles spikes
concurrency = 16    # Metabase works well with this

Network​

  • Attach Cloud SQL Instance Connections (very important)
  • Allow HTTPS ingress only
  • Optionally restrict IP access (only your backend)

🔑 2. Cloud SQL (PostgreSQL) — Your Multi-Tenant App Database

Metabase does not store customer data.

Your POS SaaS stores all business data in the app database, with:

  • Shared tables

  • Each table has business_id

  • Row isolation done either by:

    • JWT-locked parameters (embedding)
    • OR PostgreSQL RLS (Row-Level Security)

Metabase connects to this database to run analytics.

🗄 Why Cloud SQL is ideal for Metabase?​

✔ Fast connection via Cloud SQL Connector​

Cloud Run → Cloud SQL → secure, private, high-speed.

✔ Managed backups​

You never manage replicas or backups manually.

✔ HA & automatic failover​

Critical for reporting workloads.

✔ Scales vertically​

Add more CPU/RAM if reports become heavy.

🪣 3. GCS Bucket (optional but recommended)

A Google Cloud Storage bucket is optional, but useful for:

⭐ Metabase exports & caching​

Metabase OSS can export:

  • CSV
  • XLSX
  • JSON

These are downloaded by clients.

If you want to:

  • Keep logs
  • Store exports temporarily
  • Archive query results

…you can configure Metabase to use GCS as a storage backend.

⭐ Snapshot backups​

Sometimes you may want to store:

  • Saved queries artifacts
  • Daily dashboard snapshots
  • Export automation (cron jobs)

A GCS bucket is perfect for this.

🔐 4. Secret Manager or Doppler — Store All Sensitive Env Vars

Metabase requires several secrets:

MB_EMBEDDING_SECRET_KEY
MB_DB_USER
MB_DB_PASS
MB_DB_DBNAME
MB_DB_HOST
MB_SITE_URL

These SHOULD NOT live inside the repository.

You should store them in​

⭐ Doppler (recommended for your monorepo)​

You already use Doppler for all other services → keep consistency.

⭐ Google Secret Manager​

Alternative if you want infra-native secrets.

What each secret does​

MB_EMBEDDING_SECRET_KEY​

  • Used to sign/validate JWT embed tokens
  • MUST match your NestJS backend value
  • If changed → all embedded dashboards break

DB Connection Secrets​

  • Used by Metabase to connect to its metadata database
  • NOT to your app DB (although in your setup, they can be shared)

MB_SITE_URL​

  • Needed for embedding URLs
  • Metabase uses this in internal links

🧱 5. Full Architecture Diagram (Expanded)

           🔐 Doppler / Secret Manager
                     │
           provides secrets for:
           - MB_EMBEDDING_SECRET_KEY
           - DB credentials
           - site URL
                     ▼

            ┌──────────────────────┐
            │  Cloud Run (Metabase)│
            │  - autoscaling       │
            │  - token-based auth  │
            │  - public or private │
            └───────┬──────────────┘
                    │ Cloud SQL Connector
                    ▼
           ┌──────────────────────┐
           │ Cloud SQL (Postgres) │
           │  multi-tenant schema │
           │  business_id columns │
           └──────────────────────┘

                    ▲
                    │ Embedded dashboards
                    │ signed JWT tokens
                    │ with business_id lock
                    │
           ┌──────────────────────┐
           │   NestJS Backend     │
           │  (your flowpos-api)  │
           └───┬──────────────────┘
               │
               ▼
       Your Apps / Channels
       - PWA (React)
       - Web-App (Next.js)
       - Landing
       - Desktop (Flutter/RN)

🧠 6. Why This Architecture Works for Multi-Tenant SaaS

✔ Only one Metabase instance required​

Multi-tenancy is handled in the DB filtering, not by spinning containers.

✔ All dashboards embedded​

Users never access the Metabase UI → full control.

✔ Automatic tenant isolation​

Using either:

  • Embedding JWT lock (params.business_id), or
  • PostgreSQL RLS

✔ Very low cost​

Metabase only scales when needed.

✔ Minimal DevOps overhead​

Just like your backend/web-app deployments.

🚀 7. Scaling Behavior Explained

Metabase scaling works like this​

  1. A user requests a dashboard
  2. Cloud Run wakes the Metabase instance
  3. It connects to Cloud SQL via local Unix socket
  4. Runs the SQL with locked parameter business_id
  5. Results are cached temporarily
  6. If more users hit → Cloud Run creates more instances

Only ONE container image is needed​

Scaling is horizontal at the Cloud Run level.

No Kubernetes cluster needed No per-tenant deployments No VM load balancing No autoscaling headaches

🏗 Let me know if you want

✔ The exact Cloud Run YAML deployment template​

✔ Terraform for provisioning the whole Metabase stack​

✔ The Dockerfile you need to use​

✔ A NestJS MetabaseModule with JWT embed service​

✔ A PWA <MetabaseDashboard> component​

✔ SQL migrations for RLS on all your tables​

✔ GitHub Actions workflows ready to paste​

Just say "Generate the full deployment pack" and I’ll produce everything ready for your repo.

Use GitHub CLI

trigger it via the command line:

Make sure you're authenticated

gh auth login

Run the workflow (replace with your desired branch)

gh workflow run deploy-metabase.yml --ref main        # For production
gh workflow run deploy-metabase.yml --ref develop     # For staging
gh workflow run deploy-metabase.yml --ref metabase-phase-5-1  # For your current branch